Oracle Technologies Blog

By ASKM

Archive for the ‘Performance Tuning’ Category

Modifying AWR snapshot settings

Posted by Srikrishna Murthy Annam on March 1, 2010

SQL> select
2        extract( day from snap_interval) *24*60+
3        extract( hour from snap_interval) *60+
4        extract( minute from snap_interval ) “Snapshot Interval”,
5        extract( day from retention) *24*60+
6        extract( hour from retention) *60+
7        extract( minute from retention ) “Retention Interval”
8  from dba_hist_wr_control;

Snapshot Interval Retention Interval
—————– ——————
60              10080

SQL> set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;SQL> SQL> SQL> SQL>

DBID SNAP_INTERVAL        RETENTION            TOPNSQL
———- ——————– ——————– ——————–
3438851159 +00000 01:00:00.0    +00007 00:00:00.0    DEFAULT

SQL> execute dbms_workload_repository.modify_snapshot_settings( interval => 30,retention => 43200);

PL/SQL procedure successfully completed.

NOTE : all the values are specified in minutes

SQL> select
2        extract( day from snap_interval) *24*60+
3        extract( hour from snap_interval) *60+
4        extract( minute from snap_interval ) “Snapshot Interval”,
5        extract( day from retention) *24*60+
6        extract( hour from retention) *60+
7        extract( minute from retention ) “Retention Interval”
8  from dba_hist_wr_control;

Snapshot Interval Retention Interval
—————– ——————
30              43200

SQL> set linesize 100
col snap_interval format a20
col retention format a20
col topnsql format a20
select * from dba_hist_wr_control;SQL> SQL> SQL> SQL>

DBID SNAP_INTERVAL        RETENTION            TOPNSQL
———- ——————– ——————– ——————–
3438851159 +00000 00:30:00.0    +00030 00:00:00.0    DEFAULT

SQL>

==> SYSAUX tablespace size can be estimated using the script utlsyxsz.sql

SQL> @$ORACLE_HOME/rdbms/admin/utlsyxsz.sql

This script estimates the space required for the SYSAUX tablespace.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Specify the Report File Name
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is utlsyxsz.txt.  To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: askm

Using the report name askm
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SYSAUX Size Estimation Report
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Estimated at
12:10:21 on Mar 01, 2011 ( Tuesday ) in Timezone -05:00

DB_NAME     HOST_PLATFORM                             INST STARTUP_TIME      PAR
———– —————————————- —– —————– —
* EPPERF    atlsdbrfl01.seo.int – Linux x86 64-bit       1 09:58:44 (02/28)  NO

~~~~~~~~~~~~~~~~~~~~
Current SYSAUX usage
~~~~~~~~~~~~~~~~~~~~
| Total SYSAUX size:                         119.3 MB
|
| Total size of SM/AWR                        71.1 MB (  59.6% of SYSAUX )
| Total size of SM/OPTSTAT                    18.8 MB (  15.7% of SYSAUX )
| Total size of SM/ADVISOR                     6.4 MB (   5.3% of SYSAUX )
| Total size of LOGMNR                         6.0 MB (   5.0% of SYSAUX )
| Total size of SM/OTHER                       4.8 MB (   4.0% of SYSAUX )
| Total size of EM_MONITORING_USER             1.6 MB (   1.3% of SYSAUX )
| Total size of LOGSTDBY                       0.9 MB (   0.7% of SYSAUX )
| Total size of XSOQHIST                       0.8 MB (   0.6% of SYSAUX )
| Total size of AO                             0.8 MB (   0.6% of SYSAUX )
| Total size of STREAMS                        0.5 MB (   0.4% of SYSAUX )
| Total size of JOB_SCHEDULER                  0.4 MB (   0.3% of SYSAUX )
| Total size of TSM                            0.3 MB (   0.2% of SYSAUX )
| Total size of Others                         7.1 MB (   6.0% of SYSAUX )
|

~~~~~~~~~~~~~~~~~~~~
AWR Space Estimation
~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Automatic Workload Repository (AWR)
| in SYSAUX, we need the following values:
|
|     – Interval Setting (minutes)
|     – Retention Setting (days)
|     – Number of Instances
|     – Average Number of Active Sessions
|     – Number of Datafiles

|
| For ‘Interval Setting’,
|   Press <return> to use the current value:     30.0 minutes
|   otherwise enter an alternative
|
Enter value for interval:

**   Value for ‘Interval Setting’: 30

|
| For ‘Retention Setting’,
|   Press <return> to use the current value:     30.0 days
|   otherwise enter an alternative
|
Enter value for retention:

**   Value for ‘Retention Setting’: 30

|
| For ‘Number of Instances’,
|   Press <return> to use the current value:   1.00
|   otherwise enter an alternative
|
Enter value for num_instances: 1

**   Value for ‘Number of Instances’: 1

|
| For ‘Average Number of Active Sessions’,
|   Press <return> to use the current value:   0.06
|   otherwise enter an alternative
|
Enter value for active_sessions: 50

**   Value for ‘Average Number of Active Sessions’: 50

| ***************************************************
| Estimated size of AWR:                   3,921.8 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval –        30 minutes
|           Retention –     30.00 days
|       Num Instances –         1
|     Active Sessions –     50.00
|           Datafiles –        79
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Optimizer Stat History Space Estimation
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

| To estimate the size of the Optimizer Statistics History
| we need the following values:
|
|     – Number of Tables in the Database
|     – Number of Partitions in the Database
|     – Statistics Retention Period (days)
|     – DML Activity in the Database (level)

|
| For ‘Number of Tables’,
|   Press <return> to use the current value:  4,488.0
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_tables:

**   Value for ‘Number of Tables’: 4488

|
| For ‘Number of Partitions’,
|   Press <return> to use the current value:   0.00
|   otherwise enter an alternative <a positive integer>
|
Enter value for number_of_partitions:

**   Value for ‘Number of Partitions’: 0

|
| For ‘Statistics Retention’,
|   Press <return> to use the current value:     31.0 days
|   otherwise enter an alternative <a positive integer>
|
Enter value for stats_retention:

**   Value for ‘Statistics Retention’: 31

|
| For ‘DML Activity’,
|   Press <return> to use the current value:        2 <medium>
|   otherwise enter an alternative <1=low, 2=medium, 3=high>
|
Enter value for dml_activity:

**   Value for ‘DML Activity’: 2

| ***************************************************
| Estimated size of Stats history            102.2 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables –   4,488
|                        Indexes –   6,282
|                        Columns –  37,471
|                     Partitions –       0
|          Indexes on Partitions –       0
|          Columns in Partitions –       0
|        Stats Retention in Days –      31
|          Level of DML Activity –  Medium
| ***************************************************

~~~~~~~~~~~~~~~~~~~~~~
Estimated SYSAUX usage
~~~~~~~~~~~~~~~~~~~~~~

| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of AWR:                   3,921.8 MB
|
|   The AWR estimate was computed using
|   the following values:
|
|            Interval –        30 minutes
|           Retention –     30.00 days
|       Num Instances –         1
|     Active Sessions –     50.00
|           Datafiles –        79
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Estimated size of Stats history            102.2 MB
|
|   The space for Optimizer Statistics history was
|   estimated using the following values:
|
|                         Tables –   4,488
|                        Indexes –   6,282
|                        Columns –  37,471
|                     Partitions –       0
|          Indexes on Partitions –       0
|          Columns in Partitions –       0
|        Stats Retention in Days –      31
|          Level of DML Activity –  Medium
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|   For all the other components, the estimate
|   is equal to the current space usage of
|   the component.
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
|
|
| ***************************************************
| Summary of SYSAUX Space Estimation
| ***************************************************

| Est size of SM/ADVISOR                       6.4 MB
| Est size of LOGMNR                           6.0 MB
| Est size of SM/OTHER                         4.8 MB
| Est size of EM_MONITORING_USER               1.6 MB
| Est size of LOGSTDBY                         0.9 MB
| Est size of XSOQHIST                         0.8 MB
| Est size of AO                               0.8 MB
| Est size of STREAMS                          0.5 MB
| Est size of JOB_SCHEDULER                    0.4 MB
| Est size of TSM                              0.3 MB
| Est size of Others                           7.1 MB

| Est size of SM/AWR                       3,921.8 MB
| Est size of SM/OPTSTAT                     102.2 MB
|
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| Total Estimated SYSAUX size:             4,053.4 MB
| ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| ***************************************************

End of Report
SQL>

NOTE :

STATSPACK has many more settings that can be configured.  These are kept in the stats$statspack_parameter table. This table stores a single row for the database parameters with the corresponding STATSPACK settings.  These settings influence the amount of information STATSPACK gathers from the v$ views.

Hope It helps

SRI


Advertisements

Posted in Database Articles, Performance Tuning | Tagged: , , , , , , , | Leave a Comment »

11g – SQL Access Advisor

Posted by Srikrishna Murthy Annam on October 1, 2009

11g – SQL Access Advisor:

=====================================================================
DEMO               DEMO                DEMO              DEMO              DEMO                  DEMO                      DEMO
=====================================================================

Make sure that the user SH has been granted the following privileges.
SQL> sho user
USER is “SYS”
SQL> grant all on dbms_advisor to sh;

Grant succeeded.

SQL> grant advisor to sh;

Grant succeeded.

SQL> grant ADMINISTER SQL TUNING SET to sh;

Grant succeeded.

SQL> conn sh/sh
Connected

SQL> sho user
USER is “SH”
SQL> execute dbms_advisor.create_task ( ‘SQL Access Advisor’,’SQL_ACC_TASK1′,’New SQLAccess  Task’);

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.reset_task(‘SQL_ACC_TASK1’);

PL/SQL procedure successfully completed.

SQL> create table temp_table AS SELECT * FROM SYS.WRI$_ADV_SQLW_STMTS WHERE NULL IS NOT NULL;

Table created.

SQL> create table sql_access_temp_table (c number, d varchar2(1000));

Table created.

SQL> begin
for i in 1..20000 loop
insert into sql_access_temp_table values(-i,’aoiejnflamnskdfjnsijndfklsjpoakneflkajsdfkjankdsnflkasjdnfkjasndjkfnjdklfsbkbsdnbaiuebdfiausdybfaouisdbflkjabdoiufbaklsdjfaksdiufakjsdofiuasdjfalkjsdfdjkhfakjshsjdfkjasksdfkjalkaksdjhfkjaakjdsfsdjfklasjdlkadfoiuaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklksdnflkadsnflaknsdfoiweuroiamsdflkamsalksdmffalkklvmklakmdvlkamsdlkmvlksdmvlksdmvkllsdkfmasdfmalksdmfaksmdnflkanmdlfknalkksdnflkansdflklk’);
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.

SQL> create table customers_askm as select * from customers;

Table created.

SQL> @askm_sts.sql

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.add_sqlwkld_ref(‘SQL_ACC_TASK1′,’SQLSET_MY_SQLACCESS_WORKLOAD’,1);

PL/SQL procedure successfully completed.

SQL> @askm_param.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL> exec dbms_advisor.execute_task(‘SQL_ACC_TASK1’);

PL/SQL procedure successfully completed.

Scripts used :

$ cat askm_sts.sql
DECLARE
sql_stmt   varchar2(2000);
sqlsetname  VARCHAR2(30);
sqlsetcur   dbms_sqltune.sqlset_cursor;
refid    NUMBER;
k NUMBER := 0;
num_queries NUMBER := 500;
BEGIN

sql_stmt := ‘SELECT /* QueryASKM 2 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ”CA” AND ch.channel_desc in (”Internet”,”Catalog”) AND t.calendar_quarter_desc IN (”1999-01”,”1999-02”) GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘SELECT /* QueryASKM 3 */ ch.channel_class, c.cust_city, t.calendar_quarter_desc, SUM(s.amount_sold) sales_amount FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id AND s.channel_id = ch.channel_id AND c.cust_state_province = ”CA” AND ch.channel_desc in (”Internet”,”Catalog”) AND t.calendar_quarter_desc IN (”1999-03”,”1999-04”) GROUP BY ch.channel_class, c.cust_city, t.calendar_quarter_desc’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘SELECT /* QueryASKM 4 */ c.country_id, c.cust_city, c.cust_last_name FROM sh.customers c WHERE c.country_id in (52790, 52798) ORDER BY c.country_id, c.cust_city, c.cust_last_name’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘select /* func_indx */ count(*) from sql_access_temp_table where abs(c)=5′;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sql_stmt := ‘SELECT /* QueryASKM 5 */ * FROM sh.customersjfv WHERE cust_state_province = ”CA”’;

insert into temp_table values(1,1,NULL,0,’SH’,’Access Advisor’,’Workload’,0,0,0,0,1,100,2,to_date(’02-FEB-2007′),3,0,sql_stmt,1);

sqlsetname := ‘SQLSET_MY_SQLACCESS_WORKLOAD’;

dbms_sqltune.create_sqlset(sqlsetname, ‘Generated STS’);

OPEN sqlsetcur FOR
SELECT
SQLSET_ROW(null,null, sql_text, null, null, username, module,
action, elapsed_time, cpu_time, buffer_gets, disk_reads,
0,rows_processed, 0, executions, 0, optimizer_cost, null,
priority, command_type,
to_char(last_execution_date,’yyyy-mm-dd/hh24:mi:ss’),
0,0,NULL,0,NULL,NULL
)
FROM temp_table;

dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
END;
/

$

$ cat askm_param.sql
/* Set STS Workload Parameters */
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_ACTION_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_MODULE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’SQL_LIMIT’,’25’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_USERNAME_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_TABLE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_TABLE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_ACTION_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_USERNAME_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_MODULE_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’VALID_SQLSTRING_LIST’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’INVALID_SQLSTRING_LIST’,'”@!”‘);

/* Set Task Parameters */
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’ANALYSIS_SCOPE’,’ALL’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’RANKING_MEASURE’,’PRIORITY,OPTIMIZER_COST’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_PARTITION_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’TIME_LIMIT’,10000);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’MODE’,’LIMITED’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’STORAGE_CHANGE’,DBMS_ADVISOR.ADVISOR_UNLIMITED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DML_VOLATILITY’,’TRUE’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’WORKLOAD_SCOPE’,’PARTIAL’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_INDEX_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_INDEX_OWNER’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_MVIEW_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_MVIEW_OWNER’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DEF_MVLOG_TABLESPACE’,DBMS_ADVISOR.ADVISOR_UNUSED);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’CREATION_COST’,’TRUE’);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’JOURNALING’,’4′);
exec dbms_advisor.set_task_parameter(‘SQL_ACC_TASK1′,’DAYS_TO_EXPIRE’,’30’);

$

SQL Access Recommendations :

SQL> SET LONG 100000
SQL> SET PAGESIZE 50000
SQL> SELECT DBMS_ADVISOR.get_task_script(‘SQL_ACC_TASK1’) AS script FROM   dual;

SCRIPT
——————————————————————————–
Rem  SQL Access Advisor: Version 11.2.0.1.0 – Production
Rem
Rem  Username:        SH
Rem  Task:            SQL_ACC_TASK1
Rem  Execution date:
Rem

Rem
Rem  Repartitioning table “SH”.”CUSTOMERS”
Rem

SET SERVEROUTPUT ON
SET ECHO ON

Rem
Rem Creating new partitioned table
Rem
CREATE TABLE “SH”.”CUSTOMERS1″
(    “CUST_ID” NUMBER,
“CUST_FIRST_NAME” VARCHAR2(20),
“CUST_LAST_NAME” VARCHAR2(40),
“CUST_GENDER” CHAR(1),
“CUST_YEAR_OF_BIRTH” NUMBER(4,0),
“CUST_MARITAL_STATUS” VARCHAR2(20),
“CUST_STREET_ADDRESS” VARCHAR2(40),
“CUST_POSTAL_CODE” VARCHAR2(10),
“CUST_CITY” VARCHAR2(30),
“CUST_CITY_ID” NUMBER,
“CUST_STATE_PROVINCE” VARCHAR2(40),
“CUST_STATE_PROVINCE_ID” NUMBER,
“COUNTRY_ID” NUMBER,
“CUST_MAIN_PHONE_NUMBER” VARCHAR2(25),
“CUST_INCOME_LEVEL” VARCHAR2(30),
“CUST_CREDIT_LIMIT” NUMBER,
“CUST_EMAIL” VARCHAR2(30),
“CUST_TOTAL” VARCHAR2(14),
“CUST_TOTAL_ID” NUMBER,
“CUST_SRC_ID” NUMBER,
“CUST_EFF_FROM” DATE,
“CUST_EFF_TO” DATE,
“CUST_VALID” VARCHAR2(1)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
TABLESPACE “EXAMPLE”
PARTITION BY RANGE (“CUST_ID”) INTERVAL( 3000) ( PARTITION VALUES LESS THAN (3000));

Rem
Rem Copying comments to new partitioned table
Rem
COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_ID” IS ‘primary key’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_FIRST_NAME” IS ‘first name of the customer’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_LAST_NAME” IS ‘last name of the customer’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_GENDER” IS ‘gender; low cardinality attribute’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_YEAR_OF_BIRTH” IS ‘customer year of birth’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_MARITAL_STATUS” IS ‘customer maritalstatus; low cardinality attribute’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_STREET_ADDRESS” IS ‘customer street address’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_POSTAL_CODE” IS ‘postal code of the customer’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_CITY” IS ‘city where the customer lives’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_STATE_PROVINCE” IS ‘customer geography: state or province’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”COUNTRY_ID” IS ‘foreign key to the countries table (snowflake)’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_MAIN_PHONE_NUMBER” IS ‘customer mainphone number’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_INCOME_LEVEL” IS ‘customer income level’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_CREDIT_LIMIT” IS ‘customer credit limit’;

COMMENT ON COLUMN “SH”.”CUSTOMERS1″.”CUST_EMAIL” IS ‘customer email id’;

COMMENT ON TABLE “SH”.”CUSTOMERS1″  IS ‘dimension table’;

Rem
Rem Copying constraints to new partitioned table
Rem
ALTER TABLE “SH”.”CUSTOMERS1″ ADD CONSTRAINT “CUSTOMERS_PK1” PRIMARY KEY (“CUST_ID”)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS  TABLESPACE “EXAMPLE”  ENABLE NOVALIDATE;

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_FIRST_NAME” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_LAST_NAME” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_GENDER” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_YEAR_OF_BIRTH” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_STREET_ADDRESS” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_POSTAL_CODE” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_CITY” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_CITY_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_STATE_PROVINCE” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_STATE_PROVINCE_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“COUNTRY_ID” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_MAIN_PHONE_NUMBER” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_TOTAL” NOT NULL ENABLE);

ALTER TABLE “SH”.”CUSTOMERS1″ MODIFY (“CUST_TOTAL_ID” NOT NULL ENABLE);

Rem
Rem Copying referential constraints to new partitioned table
Rem
ALTER TABLE “SH”.”CUSTOMERS1″ ADD CONSTRAINT “CUSTOMERS_COUNTRY_FK1” FOREIGN KEY (“COUNTRY_ID”)
REFERENCES “SH”.”COUNTRIES” (“COUNTRY_ID”) ENABLE NOVALIDATE;

Rem
Rem Copying indexes to new partitioned table
Rem
CREATE UNIQUE INDEX “SH”.”CUSTOMERS_PK1″ ON “SH”.”CUSTOMERS1″ (“CUST_ID”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” ;

CREATE BITMAP INDEX “SH”.”CUSTOMERS_GENDER_BIX1″ ON “SH”.”CUSTOMERS1″ (“CUST_GENDER”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” LOCAL;

CREATE BITMAP INDEX “SH”.”CUSTOMERS_MARITAL_BIX1″ ON “SH”.”CUSTOMERS1″ (“CUST_MARITAL_STATUS”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” LOCAL;

CREATE BITMAP INDEX “SH”.”CUSTOMERS_YOB_BIX1″ ON “SH”.”CUSTOMERS1″ (“CUST_YEAR_OF_BIRTH”)
PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
TABLESPACE “EXAMPLE” LOCAL;

Rem
Rem Copying object grants to new partitioned table
Rem
GRANT SELECT ON “SH”.”CUSTOMERS1″ TO “BI”;

Rem
Rem Populating new partitioned table with data from original table
Rem
INSERT /*+ APPEND */ INTO “SH”.”CUSTOMERS1″
SELECT * FROM “SH”.”CUSTOMERS”;
COMMIT;

begin
dbms_stats.gather_table_stats(‘”SH”‘, ‘”CUSTOMERS1″‘, NULL, dbms_stats.auto_sample_size);
end;
/

Rem
Rem Renaming tables to give new partitioned table the original table name
Rem
ALTER TABLE “SH”.”CUSTOMERS” RENAME TO “CUSTOMERS11”;
ALTER TABLE “SH”.”CUSTOMERS1″ RENAME TO “CUSTOMERS”;

Rem
Rem Revalidating dimensions for use with new partitioned table
Rem
ALTER DIMENSION “SH”.”CUSTOMERS_DIM” COMPILE;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”CUSTOMERS”
WITH ROWID, SEQUENCE(“CUST_ID”,”CUST_CITY”,”CUST_STATE_PROVINCE”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”CHANNELS”
WITH ROWID, SEQUENCE(“CHANNEL_ID”,”CHANNEL_DESC”,”CHANNEL_CLASS”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”TIMES”
WITH ROWID, SEQUENCE(“TIME_ID”,”CALENDAR_QUARTER_DESC”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
“SH”.”SALES”
WITH ROWID, SEQUENCE(“CUST_ID”,”TIME_ID”,”CHANNEL_ID”,”AMOUNT_SOLD”)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW “SH”.”MV$$_004D0000″
REFRESH FAST WITH ROWID
ENABLE QUERY REWRITE
AS SELECT SH.CUSTOMERS.CUST_STATE_PROVINCE C1, SH.CUSTOMERS.CUST_CITY C2, SH
.CHANNELS.CHANNEL_CLASS
C3, SH.CHANNELS.CHANNEL_DESC C4, SH.TIMES.CALENDAR_QUARTER_DESC C5, SUM(”
SH”.”SALES”.”AMOUNT_SOLD”)
M1, COUNT(“SH”.”SALES”.”AMOUNT_SOLD”) M2, COUNT(*) M3 FROM SH.CUSTOMERS,
SH.CHANNELS, SH.TIMES, SH.SALES WHERE SH.SALES.CHANNEL_ID = SH.CHANNELS.C
HANNEL_ID
AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID = SH.CUSTOME
RS.CUST_ID
AND (SH.TIMES.CALENDAR_QUARTER_DESC IN (‘1999-04’, ‘1999-03’, ‘1999-02’
, ‘1999-01’)) AND (SH.CHANNELS.CHANNEL_DESC IN (‘Internet’, ‘Catalog’
)) AND (SH.CUSTOMERS.CUST_STATE_PROVINCE = ‘CA’) GROUP BY SH.CUSTOMERS.CU
ST_STATE_PROVINCE,
SH.CUSTOMERS.CUST_CITY, SH.CHANNELS.CHANNEL_CLASS, SH.CHANNELS.CHANNEL_DESC,
SH.TIMES.CALENDAR_QUARTER_DESC;

begin
dbms_stats.gather_table_stats(‘”SH”‘,'”MV$$_004D0000″‘,NULL,dbms_stats.auto_sa
mple_size);
end;
/

CREATE MATERIALIZED VIEW “SH”.”MV$$_004D0001″
REFRESH FORCE WITH ROWID
ENABLE QUERY REWRITE
AS SELECT “SH”.”CUSTOMERS”.”COUNTRY_ID” M1, “SH”.”CUSTOMERS”.”CUST_CITY” M2,
“SH”.”CUSTOMERS”.”CUST_LAST_NAME”
M3 FROM SH.CUSTOMERS WHERE (SH.CUSTOMERS.COUNTRY_ID IN (52798, 52790));

begin
dbms_stats.gather_table_stats(‘”SH”‘,'”MV$$_004D0001″‘,NULL,dbms_stats.auto_sample_size);
end;
/

CREATE INDEX “SH”.”CUSTOMERS_IDX$$_004D0000″ ON “SH”.”CUSTOMERS” (“COUNTRY_ID”,”CUST_CITY”,”CUST_LAST_NAME”) COMPUTE STATISTICS;

SQL>

11g – SQL Access Advisor

Posted in 11g New Features, Performance Tuning | Leave a Comment »

SQL Plan Management(SPM) – Theory

Posted by Srikrishna Murthy Annam on September 26, 2009

SPM – SQL Plan Management

How are we evolved to 11g SQL plan management (SPM) ?

Outlines -> Stored Outlines are very difficult to manage and also very difficult to swap the execution plans with plan stability.
10g SQL Profiles -> Starting from 10g we used the SQL profile concept ie SQL tuning sets and also we can change the execution plans.
11g SQL Plan management (SPM) -> It is an easy to use tool to lock the best execution plans.

The major difference between Outlines and SPM is that Outlines are fixed and we cant override execution plan, But SPM uses baselines that can be evaluated for better plans and activated in place of original plans.

The difference between SQL Profiles and SPM is that the query plan changes based on the predicate used, But with SQL Plan Baselines,  the plan would be the same regardless of the value in the predicate.
For example queries with “Where cust_id= “, “where book_id= ” will have different execution plans in SQL profiles.

What is SQL Baseline ?
Baselines are the latest evolution in Oracle’s efforts to allow a plan to be locked. Baselines stores the plan_hash_value, so they know if they are reproducing the correct plan or not.
— Baselines will be used by default in 11gR1, if they exist. There is a parameter to control whether they are used or not (OPTIMIZER_USE_SQL_PLAN_BASELINE). It is set to TRUE by default.
— Baselines will not be created by default in 11gR1. So, much like with the older Outlines or SQL Profiles, you must do something to create them.
— There is a view called DBA_SQL_PLAN_BASELINES that exposes the Baselines that have been created.
— Just like Outlines and SQL Profiles, Baselines apply to all instances in a RAC environment (they are not localized to a specific instance).

How to create Base lines ?
— Baselines are created automatically by setting the parameter OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES
— From the cursor cache using dbms_spm.load_plans_from_cursor_cache
— From the tuning sets using dbms_spm.load_plans_from_sqlset

These created baselines are applied to any SQL statement where the normalized text matches ( means same execution plan ) ie the baseline created for one statement can be attached to a different statement. You dont need to do extra , but call the procedure directly and it’s done. Of course the optimizer will have to verify that the plan will work for the statement you attach it to. If it fails this validation step, then the optimizer will ignore it and go on about it’s normal business.

Well … we discussed about baselines and how they can be created  …..But how are these baselines related to SQL Plan Management (SPM). What is the relationship between them ? how can they choose the best execution plan ?
When a SQL statement is hard parsed, the cost based optimizer produces several execution plans and selects the one with the lowest cost. If a SQL plan baseline is present, the optimizer tries to find a matching plan in the SQL plan baseline that is flagged as accepted. If a matching plan is found, the optimizer will uses the plan. If the SQL plan baseline doesn’t contain a matching plan, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost. If the execution plan originally produced by the optimizer has a lower cost than those present in the SQL plan baseline, it is added to the baseline as a not-accepted plan, so it is not used until it is verified not to cause a reduction in performance. If a system change affects all existing accepted plans, the optimizer will use the original plan with the lowest cost and mark this as accepted.
The final conclusion steps are  load , evolve , test and accept.

Are the baselines fully controlled by oracle internally or can we do some maintenance tasks with SQL Plan Baselines ?
1) We can alter the attributes of SQL Plan Baselines with ALTER_SQL_PLAN_BASELINE function
enabled (YES/NO) : If YES, the plan is available for the optimizer if it is also marked as accepted.
fixed (YES/NO) : If YES, the SQL plan baseline will not evolve over time. Fixed plans are used in preference to non-fixed plans.
autopurge (YES/NO) : If YES, the SQL plan baseline is purged automatically if it is not used for a period of time.
plan_name : Used to amend the SQL plan name, up to a maximum of 30 character.
description : Used to amend the SQL plan description, up to a maximum of 30 character.
2) Diplay the baseline information using
DBA_SQL_PLAN_BASELINES
DBMS_XPLAN.display_sql_plan_baseline
3) Transferring SQL Plan Baselines – DBMS_SPM.pack_stgtab_baseline
4) Dropping SQL Plan Baselines

References :
SQL Plan Management(SPM) – Theory

Posted in 11g New Features, Performance Tuning | Leave a Comment »

 
%d bloggers like this: