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

;

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
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>
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’);

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;
  
  /
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

Repartitioning table “SH”.”CUSTOMERS”

Rem

SET SERVEROUTPUT ON

SET ECHO

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

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

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 gjilevski | oracle

No comments yet.

About

Guenadi Jilevski is a Senior Oracle Professional (OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and OCM 9i, OCM 11g) with more than 20 years of industrial experience in high end Oracle technology starting with Oracle RDBMS version 6 up to 11g. He has been involved with multi platform High Availability Solutions starting with OPS and Veritas and Sun cluster as well as complex multiple node RAC 9i, 10g and 11g implementations on AIX, Linux, Solaris and HP. He has experience in deploying disaster recovery solutions with standby databases compliant to Oracle MAA and third party solutions such as Shareplex and Goldengate. As a former employee of Oracle Corporation Advanced Technology Solutions Consulting Practice in Americas, Guenadi has been involved in multiple visible high end projects for RAC enabling EBS, Peoplesoft, Siebel ERP applications, end to end performance tuning, architectured High Availability and Disaster Recovery solutions to name a few. Presently he is working as A Senior Oracle Consultant in EMEA.

Guenadi Jilevski is Oracle OCP 7, 8, 8i, 9i, 10g, 11g, 12c OCE 10gR2 RAC, OCE Oracle RAC 11gR2 and Grid Infrastructure Administrator, Oracle Exadata 11g Certified Implementation Specialist,Oracle GoldenGate 10g/11g Certified Implementation Specialist and Oracle OCM9i , OCM 11g, Teradata Certified DBA, Informix Certified DBA.

The views expressed on this blog are my own and do not necessarily reflect the views of Current/Past/Future Employers. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect mine.

Archives
Archives
Blog Stats
- 1,164,026 hits
Top Rated
Blogroll
- WordPress.com 0
- WordPress.org 0
Calendar

March 2016

M T W T F S S

1 2 3 4 5 6

7 8 9 10 11 12 13

14 15 16 17 18 19 20

21 22 23 24 25 26 27

28 29 30 31

« Dec May »
Search

Search for:
Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 1,112 other followers

Email Address:
OraNA

Guenadi N Jilevski's Oracle BLOG

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