Guenadi N Jilevski's Oracle BLOG

Oracle RAC, DG, EBS, DR and HA DBA BLOG

Using the SQL ACCESS Advisor PL/SQL interface

Using the SQL ACCESS Advisor PL/SQL interface

In the article you will have a look at an example of using the SQL Access Advisor PL/SQL interface to obtain the access path and object structures change recommendations.

Let’s look at the SQL query example:

SELECT /*+ monitor */ /* test SQL Tuning */

p.prod_category,

p.prod_subcategory,

t.calendar_year,

SUM( s.quantity_sold ) AS total_quantity_sold,

SUM( s.amount_sold ) AS total_amount_sold

FROM

sh.sales s, sh.products p, sh.customers c, sh.countries co, sh.channels cha, sh.times t /*–, customers cu, countries co1, channels cha1*/

WHERE

s.prod_id = p.prod_id

and s.cust_id = c.cust_id

and c.country_id = co.country_id

and s.channel_id = cha.channel_id

and s.time_id = t.time_id

and

p.prod_valid = ‘A’

AND

cha.channel_class = ‘Direct’

AND

c.cust_valid = ‘A’

AND

co.country_name = ‘Australia’

GROUP BY

p.prod_category,

p.prod_subcategory,

t.calendar_year

ORDER BY

p.prod_category,

p.prod_subcategory,

t.calendar_year

;

  1. We will put the SQL in a SQL Tuning set. First we will determine the SQL_ID.

    SQL> select sql_text, sql_id from v$sql where sql_text like ‘%test SQL Tuning%’;

    SQL_TEXT

    ——————————————————————————–

    SQL_ID

    ————-

    select sql_text, sql_id from v$sql where sql_text like ‘%test SQL Tuning%’

    ckm6vzt5d0hng

    SELECT /*+ monitor */ /* test SQL Tuning */    p.prod_category, p.prod_subc

    ategory, t.calendar_year,     SUM( s.quantity_sold ) AS total_quantity_sold,

    SUM( s.amount_sold ) AS total_amount_sold     FROM     sh.sales s, sh.produ

    cts p, sh.customers c,    sh.countries co, sh.channels cha, sh.times t /*–, custo

    mers cu, countries co1, channels cha1*/ WHERE s.prod_id = p.prod_id and

    s.cust_id = c.cust_id and c.country_id = co.country_id and     s.channel_id =

    SQL_TEXT

    ——————————————————————————–

    SQL_ID

    ————-

    cha.channel_id and s.time_id = t.time_id and     p.prod_valid = ‘A’ AND

    cha.channel_class = ‘Direct’ AND     c.cust_valid = ‘A’    AND

    co.country_name = ‘Australia’ GROUP BY p.prod_category, p.prod_subca

    tegory, t.calendar_year ORDER BY     p.prod_category,    p.prod_subcatego

    ry,    t.calendar_year

    b445892v7zc3k

    The SQL_ID=b445892v7zc3k

  2. Crete a SQL tuning SET and populate it with the SQL_ID

    exec dbms_sqltune.create_sqlset(‘STS_NAME’,’Some STS description’,’SQL_TUNE’);

    declare

    cur dbms_sqltune.sqlset_cursor;

    begin

    open cur for

    select value(P) from table(dbms_Sqltune.select_cursor_cache(‘sql_id= ”b445892v7zc3k”’))P;

    dbms_sqltune.load_sqlset(sqlset_name=>’STS_NAME’, populate_cursor=>cur, sqlset_owner=>’SQL_TUNE’);

    close cur;

    end;

    /

    SQL> select * from dba_sqlset where NAME=’STS_NAME’;

        ID NAME              OWNER

    ———- —————————— ——————————

    DESCRIPTION

    ——————————————————————————–

    CREATED LAST_MODI STATEMENT_COUNT

    ——— ——— —————

        54 STS_NAME             SQL_TUNE

    Some STS description

    03-JAN-14 03-JAN-14         1

    SQL>

  3. Execute the SQL Access Advisor using the PL/SQL interface

exec dbms_advisor.set_task_parameter(‘MYTASK_NAME’,’ANALYSIS_SCOPE’,’ALL’);

exec dbms_advisor.set_task_parameter(‘MYTASK_NAME’,’MODE’,’COMPREHENSIVE’);

exec dbms_advisor.set_task_parameter(‘MYTASK_NAME’,’WORKLOAD_SCOPE’,’FULL’); exec dbms_advisor.add_sts_ref(‘MYTASK_NAME’,’SQL_TUNE’,’STS_NAME’);

exec dbms_advisor.execute_task(‘MYTASK_NAME’);

  1. Display the results. There are two ways to achieve it.
    1. Use the below PL/SQL code

      set serveroutput on

      exec dbms_output.put_line(dbms_advisor.get_task_script(‘MYTASK_NAME’));

    2. Get the result into a file

      create or replace directory t_dir as ‘/tmp’;

      grant read , write on directory t_Dir to public;

      begin

      dbms_advisor.create_file(dbms_advisor.get_task_script(‘MYTASK_NAME’),’T_DIR’,’sql_Access_r.sql’);

      end;

      /

  2. Further analysis can be obtain using the following views.

    SQL> SELECT REC_ID, RANK, BENEFIT FROM dba_ADVISOR_RECOMMENDATIONS WHERE TASK_NAME = ‘MYTASK_NAME’;

    REC_ID     RANK     BENEFIT

    ———- ———- ———-

         1     1     1003

    SQL>

    SQL> set linesize 300

    SQL> SELECT sql_id, rec_id, precost, postcost,

    (precost-postcost)*100/precost AS percent_benefit, workload_name

    FROM dba_ADVISOR_SQLA_WK_STMTS

    WHERE TASK_NAME = ‘MYTASK_NAME’; 2 3 4

    SQL_ID         REC_ID PRECOST POSTCOST PERCENT_BENEFIT WORKLOAD_NAME

    ————- ———- ———- ———- ————— ——————————

    b445892v7zc3k     1 1008     5     99.5039683 STS_NAME

    SQL>

    SELECT ‘Action Count’, COUNT(DISTINCT action_id) cnt

    FROM DBA_ADVISOR_ACTIONS WHERE task_name = ‘MYTASK_NAME’;

    SQL> SELECT ‘Action Count’, COUNT(DISTINCT action_id) cnt

    FROM DBA_ADVISOR_ACTIONS WHERE task_name = ‘MYTASK_NAME’;

    2

    ‘ACTIONCOUNT     CNT

    ———— ———-

    Action Count     9

    SQL>

    SQL> SELECT rec_id, action_id, SUBSTR(command,1,30) AS command

    FROM dba_advisor_actions WHERE task_name = ‘MYTASK_NAME’

    ORDER BY rec_id, action_id; 2 3

    REC_ID ACTION_ID COMMAND

    ———- ———- ————————————————————————————————————————

         1     1 PARTITION TABLE

         1     2 CREATE MATERIALIZED VIEW LOG

         1     4 ALTER MATERIALIZED VIEW LOG

         1     5 CREATE MATERIALIZED VIEW LOG

         1     7 ALTER MATERIALIZED VIEW LOG

         1     8 CREATE MATERIALIZED VIEW LOG

         1     10 CREATE MATERIALIZED VIEW LOG

         1     12 CREATE MATERIALIZED VIEW

         1     13 GATHER TABLE STATISTICS

    9 rows selected.

    SQL>

    Summary

    We looked at a way to retrieve the SQL Access recommendations using the PL/SQL interface.

    Appendix:

serveroutput======================================

SQL> set serveroutput on

exec dbms_output.put_line(dbms_advisor.get_task_script(‘MYTASK_NAME’));

SQL>

Rem SQL Access Advisor: Version 11.2.0.3.0 – Production

Rem

Rem Username:

SYS

Rem Task:     MYTASK_NAME

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 LOGGING

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 marital status; 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 main phone 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 COMPUTE STATISTICS

TABLESPACE “EXAMPLE”    ENABLE;

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

ALTER

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

ALTER TABLE

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

ALTER

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

ALTER TABLE

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

ALTER

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

ALTER

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

ALTER TABLE

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

ALTER TABLE

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

ALTER TABLE

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

ALTER TABLE

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

ALTER TABLE

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

ALTER TABLE

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

ALTER TABLE

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

ALTER TABLE

“SH”.”CUSTOMERS1″ MODIFY (“CUST_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;

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 COMPUTE STATISTICS

TABLESPACE “EXAMPLE” ;

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 Dropping

materialized view log on original table

Rem

DROP MATERIALIZED VIEW LOG ON

“SH”.”CUSTOMERS”;

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

Creating materialized view log on new partitioned table

Rem

CREATE

MATERIALIZED VIEW LOG ON “SH”.”CUSTOMERS”

PCTFREE 10 PCTUSED 30 INITRANS 1

MAXTRANS 255 LOGGING

TABLESPACE “EXAMPLE”

WITH ROWID, SEQUENCE ( “CUST_ID”

) INCLUDING NEW VALUES;

Rem

Rem Revalidating dimensions for use with new

partitioned table

Rem

ALTER DIMENSION “SH”.”CUSTOMERS_DIM” COMPILE;

CREATE

MATERIALIZED VIEW LOG ON

“SH”.”PRODUCTS”

WITH ROWID,

SEQUENCE(“PROD_ID”,”PROD_SUBCATEGORY”,”PROD_CATEGORY”,”PROD_VALID”)

INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON

“SH”.”SALES”

ADD ROWID,

SEQUENCE(“PROD_ID”,”CUST_ID”,”TIME_ID”,”CHANNEL_ID”,”QUANTITY_SOLD”,”AMOUNT_SOLD

“)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON

“SH”.”COUNTRIES”

WITH ROWID, SEQUENCE(“COUNTRY_ID”,”COUNTRY_NAME”)

INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON

“SH”.”CUSTOMERS”

ADD ROWID, SEQUENCE(“CUST_ID”,”COUNTRY_ID”,”CUST_VALID”)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON

“SH”.”CHANNELS”

WITH ROWID, SEQUENCE(“CHANNEL_ID”,”CHANNEL_CLASS”)

INCLUDING NEW

VALUES;

CREATE MATERIALIZED VIEW LOG ON

“SH”.”TIMES”

WITH ROWID,

SEQUENCE(“TIME_ID”,”CALENDAR_YEAR”)

INCLUDING NEW VALUES;

CREATE

MATERIALIZED VIEW “SYS”.”MV$$_04D10000″

REFRESH FAST WITH ROWID

ENABLE

QUERY REWRITE

AS SELECT SH.COUNTRIES.COUNTRY_NAME C1,

SH.CHANNELS.CHANNEL_CLASS C2, SH.PRODUCTS.PROD_VALID

C3,

SH.PRODUCTS.PROD_CATEGORY C4, SH.PRODUCTS.PROD_SUBCATEGORY C5,

SH.TIMES.CALENDAR_YEAR

C6, SUM(“SH”.”SALES”.”AMOUNT_SOLD”) M1,

COUNT(“SH”.”SALES”.”AMOUNT_SOLD”)

M2, SUM(“SH”.”SALES”.”QUANTITY_SOLD”)

M3, COUNT(“SH”.”SALES”.”QUANTITY_SOLD”)

M4, COUNT(*) M5 FROM SH.PRODUCTS,

SH.SALES, SH.COUNTRIES, SH.CUSTOMERS,

SH.CHANNELS, SH.TIMES WHERE

SH.SALES.PROD_ID = SH.PRODUCTS.PROD_ID AND

SH.CUSTOMERS.COUNTRY_ID =

SH.COUNTRIES.COUNTRY_ID AND SH.PRODUCTS.PROD_VALID

=

SH.CUSTOMERS.CUST_VALID AND SH.SALES.CHANNEL_ID = SH.CHANNELS.CHANNEL_ID

AND SH.SALES.TIME_ID = SH.TIMES.TIME_ID AND SH.SALES.CUST_ID =

SH.CUSTOMERS.CUST_ID

AND (SH.PRODUCTS.PROD_VALID = ‘A’) AND

(SH.CHANNELS.CHANNEL_CLASS = ‘Direct’

) AND (SH.CUSTOMERS.CUST_VALID =

‘A’) AND (SH.COUNTRIES.COUNTRY_NAME =

‘Australia’) GROUP BY

SH.COUNTRIES.COUNTRY_NAME, SH.CHANNELS.CHANNEL_CLASS,

SH.PRODUCTS.PROD_VALID, SH.PRODUCTS.PROD_CATEGORY,

SH.PRODUCTS.PROD_SUBCATEGORY,

SH.TIMES.CALENDAR_YEAR;

begin

dbms_stats.gather_table_stats(‘”SYS”‘,'”MV$$_04D10000″‘,NULL,dbms_stats.auto_sam

ple_size);

end;

/

PL/SQL procedure successfully completed.

SQL> SQL>


March 2, 2016 - Posted by | oracle

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: