Using PL/SQL to run the SQL tuning Advisor in 11gR2
Using PL/SQL to run the SQL tuning Advisor in 11gR2
In the article we will have a look at the steps to invoke and run the SQL Tuning Advisor using the interface of the PL/SQL packages provided by Oracle. The SQL Tuning Advisor can be run in an alternative way using the OEM 12c Cloud Control interface. We will not cover the OEM 12c CC interface.
The approach is simple we run a single SQL or a set of SQL statements and will stored them into a SQL tuning set. The created SQL Tuning set will be passed as an argument to the SQL Tuning Advisor.
For the analysis we will be using the SH sample schema in Oracle 11gR2.
- We run the following SQL Statements in the sh schema as sh user.
SELECT /*+ monitor */ /* Catch_monitor */ /* test SQL Tuning */
products.prod_category,
products.prod_subcategory,
times.calendar_year,
SUM( sales.quantity_sold )
AS total_quantity_sold,
SUM( sales.amount_sold )
AS total_amount_sold
FROM
sales
INNER JOIN
products
USING( prod_id )
INNER JOIN
customers
USING( cust_id )
INNER JOIN
countries
USING( country_id )
INNER JOIN
channels
USING( channel_id )
INNER JOIN
times
USING( time_id )
INNER JOIN
times
USING( time_id )
WHERE
products.prod_valid = ‘A’
AND
channels.channel_class = ‘Direct’
AND
customers.cust_valid = ‘A’
AND
countries.country_name = ‘United States of America’
GROUP BY
products.prod_category,
products.prod_subcategory,
times.calendar_year
ORDER BY
products.prod_category,
products.prod_subcategory,
times.calendar_year
;
- Capture the SQL_ID.
select sql_id, sql_text from v$sql where sql_text like ‘%test SQL Tunin%’;
SQL> select sql_id, sql_text from v$sql where sql_text like ‘%test SQL Tunin%’;
SQL_ID
————-
SQL_TEXT
—————————————————————————————————-
5unjqtr8ffkfh
select sql_id, sql_text from v$sql where sql_text like ‘%test SQL Tunin%’
5camygz16b9b1
SELECT /*+ monitor */ /* Catch_monitor */ /* test SQL Tuning */ products.prod_category, prod
ucts.prod_subcategory, times.calendar_year, SUM( sales.quantity_sold ) AS total_quanti
ty_sold, SUM( sales.amount_sold ) AS total_amount_sold FROM sales INNER JOIN
products USING( prod_id ) INNER JOIN customers USING( cust_id ) INNER JOI
N countries USING( country_id ) INNER JOIN channels USING( channel_id )
SQL_ID
————-
SQL_TEXT
—————————————————————————————————-
INNER JOIN times USING( time_id ) INNER JOIN times USING( time_id ) WHERE
products.prod_valid = ‘A’ AND channels.channel_class = ‘Direct’ AND custom
ers.cust_valid = ‘A’ AND countries.country_name = ‘United States of America’ GROUP BY
products.prod_category, products.prod_subcategory, times.calendar_year ORDER BY prod
ucts.prod_category, products.prod_subcategory, times.calendar_year
- Create a SQL Tuning Set and populate the SQL Tuning set.
BEGIN
dbms_sqltune.create_sqlset(sqlset_name => ‘SQL_TUNE_JAN1′, sqlset_owner =>’SYS’);
END;
/
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE (DBMS_SQLTUNE.SELECT_CURSOR_CACHE( basic_filter=>’SQL_ID=”5camygz16b9b1”’)) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => ‘SQL_TUNE_JAN1’,
populate_cursor => baseline_cursor);
END;
/
- Create a SQL Tuning Task.
SQL> VARIABLE task_name VARCHAR2(30)
EXEC :task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK( sqlset_name => ‘SQL_TUNE_JAN1’ );
PRINT task_name
SQL>
PL/SQL procedure successfully completed.
SQL>
TASK_NAME
—————————————————————————————————-
TASK_122
SQL> SQL>
- Set the SQL Tuning Task parameters.
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => ‘TASK_122’,
parameter => ‘TIME_LIMIT’, value => 30000);
END;
/
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => ‘TASK_122’,
parameter => ‘LOCAL_TIME_LIMIT’, value => 30000);
END;
/
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => ‘TASK_122’,
parameter => ‘MODE’, value => ‘COMPREHENSIVE’);
END;
/
BEGIN
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
task_name => ‘TASK_122’,
parameter => ‘TEST_EXECUTE’, value => ‘FULL’);
END;
- Execute the SQL Tuning Task.
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => ‘TASK_122’ );
- Report the SQL Tuning Task findings.
SQL>
SET LONG 1000000
SET LONGCHUNKSIZE 1000000
SET LINESIZE 100
SET PAGESIZE 1000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( ‘TASK_122’) FROM DUAL;
SQL> SQL> SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TASK_122’)
—————————————————————————————————-
GENERAL INFORMATION SECTION
——————————————————————————-
Tuning Task Name : TASK_122
Tuning Task Owner : SYS
Workload Type : SQL Tuning Set
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 30000
Per-SQL Time Limit(seconds) : 30000
Completion Status : COMPLETED
Started at : 05/06/2016 09:35:24
Completed at : 05/06/2016 09:36:19
SQL Tuning Set (STS) Name : SQL_TUNE_JAN1
SQL Tuning Set Owner : SYS
Number of Statements in the STS : 1
——————————————————————————-
SUMMARY SECTION
——————————————————————————-
Global SQL Tuning Result Statistics
——————————————————————————-
Number of SQLs Analyzed : 1
Number of SQLs in the Report : 1
Number of SQLs with Findings : 1
Number of SQLs with Statistic Findings : 1
Number of SQLs with SQL profiles recommended : 1
——————————————————————————-
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
——————————————————————————-
object ID SQL ID statistics profile(benefit) index(benefit) restructure
———- ————- ———- —————- ————– ———–
2 5camygz16b9b1 1 98.76%
——————————————————————————-
Objects with Missing/Stale Statistics (ordered by schema, object, type)
——————————————————————————-
Schema Name Object Name Type State Cascade
—————————- —————————- —– ——- ——-
SH SALES TABLE MISSING NO
——————————————————————————-
DETAILS SECTION
——————————————————————————-
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
——————————————————————————-
Object ID : 2
Schema Name: SH
SQL ID : 5camygz16b9b1
SQL Text : SELECT /*+ monitor */ /* Catch_monitor */ /* test SQL Tuning */
products.prod_category,
products.prod_subcategory,
times.calendar_year,
SUM( sales.quantity_sold )
AS total_quantity_sold,
SUM( sales.amount_sold )
AS total_amount_sold
FROM
sales
INNER JOIN
products
USING( prod_id )
INNER JOIN
customers
USING( cust_id )
INNER JOIN
countries
USING( country_id )
INNER JOIN
channels
USING( channel_id )
INNER JOIN
times
USING( time_id )
INNER JOIN
times
USING( time_id )
WHERE
products.prod_valid = ‘A’
AND
channels.channel_class = ‘Direct’
AND
customers.cust_valid = ‘A’
AND
countries.country_name = ‘United States of America’
GROUP BY
products.prod_category,
products.prod_subcategory,
times.calendar_year
ORDER BY
products.prod_category,
products.prod_subcategory,
times.calendar_year
——————————————————————————-
FINDINGS SECTION (2 findings)
——————————————————————————-
1- Statistics Finding
———————
Table “SH”.”SALES” was not analyzed.
Recommendation
————–
– Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => ‘SH’, tabname =>
‘SALES’, estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);
Rationale
———
The optimizer requires up-to-date statistics for the table in order to
select a good execution plan.
2- SQL Profile Finding (see explain plans section below)
——————————————————–
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.76%)
——————————————
– Consider accepting the recommended SQL profile. A SQL plan baseline
corresponding to the plan with the SQL profile will also be created.
execute dbms_sqltune.accept_sql_profile(task_name => ‘TASK_122’,
object_id => 2, task_owner => ‘SYS’, replace => TRUE);
Validation results
——————
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
————- —————- ———-
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .877908 .53621 38.92 %
CPU Time (s): .8755 .53 39.46 %
User I/O Time (s): 0 0
Buffer Gets: 260998 3171 98.78 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 88 88
Fetches: 88 88
Executions: 1 1
Notes
—–
- Statistics for the original plan were averaged over 2 executions.
- Statistics for the SQL profile plan were averaged over 2 executions.
——————————————————————————-
EXPLAIN PLANS SECTION
——————————————————————————-
1- Original With Adjusted Cost
——————————
——————————————————————————-
Error: cannot fetch explain plan for object: 2
——————————————————————————-
2- Original With Adjusted Cost
——————————
Plan hash value: 3988380047
—————————————————————————————————-
———–
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Psta
rt| Pstop |
—————————————————————————————————-
———–
| 0 | SELECT STATEMENT | | 263 | 27089 | | 4636 (2)| 00:00:56 |
| |
| 1 | SORT GROUP BY | | 263 | 27089 | | 4636 (2)| 00:00:56 |
| |
|* 2 | HASH JOIN | | 321K| 31M| 1952K| 4622 (1)| 00:00:56 |
| |
|* 3 | HASH JOIN | | 25607 | 1650K| 29M| 4521 (1)| 00:00:55 |
| |
|* 4 | HASH JOIN | | 486K| 23M| 23M| 3063 (2)| 00:00:37 |
| |
|* 5 | HASH JOIN | | 486K| 18M| 18M| 1862 (2)| 00:00:23 |
| |
|* 6 | HASH JOIN | | 486K| 12M| | 539 (4)| 00:00:07 |
| |
|* 7 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | | 3 (0)| 00:00:01 |
| |
| 8 | PARTITION RANGE ALL| | 973K| 14M| | 531 (3)| 00:00:07 |
1 | 28 |
| 9 | TABLE ACCESS FULL | SALES | 973K| 14M| | 531 (3)| 00:00:07 |
1 | 28 |
|* 10 | TABLE ACCESS FULL | CUSTOMERS | 10417 | 122K| | 406 (1)| 00:00:05 |
| |
| 11 | TABLE ACCESS FULL | TIMES | 1826 | 21912 | | 18 (0)| 00:00:01 |
| |
|* 12 | TABLE ACCESS FULL | COUNTRIES | 1 | 15 | | 3 (0)| 00:00:01 |
| |
|* 13 | TABLE ACCESS FULL | PRODUCTS | 72 | 2664 | | 3 (0)| 00:00:01 |
| |
—————————————————————————————————-
———–
Predicate Information (identified by operation id):
—————————————————
2 – access(“SALES”.”PROD_ID”=”PRODUCTS”.”PROD_ID”)
3 – access(“CUSTOMERS”.”COUNTRY_ID”=”COUNTRIES”.”COUNTRY_ID”)
4 – access(“SALES”.”TIME_ID”=”TIMES”.”TIME_ID”)
5 – access(“SALES”.”CUST_ID”=”CUSTOMERS”.”CUST_ID”)
6 – access(“SALES”.”CHANNEL_ID”=”CHANNELS”.”CHANNEL_ID”)
7 – filter(“CHANNELS”.”CHANNEL_CLASS”=’Direct’)
10 – filter(“CUSTOMERS”.”CUST_VALID”=’A’)
12 – filter(“COUNTRIES”.”COUNTRY_NAME”=’United States of America’)
13 – filter(“PRODUCTS”.”PROD_VALID”=’A’)
3- Using SQL Profile
——————–
Plan hash value: 4242716298
—————————————————————————————————-
———-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstar
t| Pstop |
—————————————————————————————————-
———-
| 0 | SELECT STATEMENT | | 263 | 27089 | 991 (5)| 00:00:12 |
| |
| 1 | SORT GROUP BY | | 263 | 27089 | 991 (5)| 00:00:12 |
| |
|* 2 | HASH JOIN | | 321K| 31M| 977 (3)| 00:00:12 |
| |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1826 | 21912 | 18 (0)| 00:00:01 |
| |
| 4 | TABLE ACCESS FULL | TIMES | 1826 | 21912 | 18 (0)| 00:00:01 |
| |
|* 5 | HASH JOIN | | 321K| 27M| 957 (3)| 00:00:12 |
| |
|* 6 | TABLE ACCESS FULL | PRODUCTS | 72 | 2664 | 3 (0)| 00:00:01 |
| |
|* 7 | HASH JOIN | | 321K| 16M| 952 (3)| 00:00:12 |
| |
|* 8 | TABLE ACCESS FULL | CHANNELS | 2 | 22 | 3 (0)| 00:00:01 |
| |
|* 9 | HASH JOIN | | 558K| 22M| 946 (3)| 00:00:12 |
| |
|* 10 | HASH JOIN | | 6284 | 165K| 410 (1)| 00:00:05 |
| |
|* 11 | TABLE ACCESS FULL | COUNTRIES | 1 | 15 | 3 (0)| 00:00:01 |
| |
|* 12 | TABLE ACCESS FULL | CUSTOMERS | 10417 | 122K| 406 (1)| 00:00:05 |
| |
| 13 | PARTITION RANGE JOIN-FILTER| | 973K| 14M| 531 (3)| 00:00:07 |:BF000
0|:BF0000|
| 14 | TABLE ACCESS FULL | SALES | 973K| 14M| 531 (3)| 00:00:07 |:BF000
0|:BF0000|
—————————————————————————————————-
———-
Predicate Information (identified by operation id):
—————————————————
2 – access(“SALES”.”TIME_ID”=”TIMES”.”TIME_ID”)
5 – access(“SALES”.”PROD_ID”=”PRODUCTS”.”PROD_ID”)
6 – filter(“PRODUCTS”.”PROD_VALID”=’A’)
7 – access(“SALES”.”CHANNEL_ID”=”CHANNELS”.”CHANNEL_ID”)
8 – filter(“CHANNELS”.”CHANNEL_CLASS”=’Direct’)
9 – access(“SALES”.”CUST_ID”=”CUSTOMERS”.”CUST_ID”)
10 – access(“CUSTOMERS”.”COUNTRY_ID”=”COUNTRIES”.”COUNTRY_ID”)
11 – filter(“COUNTRIES”.”COUNTRY_NAME”=’United States of America’)
12 – filter(“CUSTOMERS”.”CUST_VALID”=’A’)
——————————————————————————-
SQL> SQL> SQL>
- The SQL Tuning advisor recommended:
- Gathering statistics
- Accepting a SQL Profile
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