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.
-
Use the below PL/SQL code
set serveroutput on
exec dbms_output.put_line(dbms_advisor.get_task_script(‘MYTASK_NAME’));
-
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
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>
No comments yet.
-
Archives
- February 2017 (1)
- November 2016 (1)
- October 2016 (1)
- May 2016 (2)
- March 2016 (3)
- December 2014 (2)
- July 2014 (1)
- June 2014 (6)
- May 2014 (5)
- February 2014 (1)
- December 2012 (2)
- November 2012 (8)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply