Come along and see how Oracle has helped us in alleviating the pain associated with tuning SQL.
Gone are the days of staring at countless structures and statistics just attempting to tune a SQL statement. Oracle has once again given us a great tool that will assist us with manual tuning of SQL statements. This tool is called the SQL tuning advisor and it will eat SQL statements for lunch and spit out optimizing techniques and recommendations we can use in our efforts. Who better to tune a SQL statement than the Oracle engine itself? Now, whether it produces the best suggestions all of the time will be up for debate as we all get into the habit of using this tool. However, in theory, since Oracle knows the optimizer, and we can still throw out "intelligent" hints, the advisor should be able to tune better than anyone else should. Well, I suggest we look at it and then you can experiment and let me know the instances where it did not decide the proper execution path or didn't re-write the query any better.
If you were to use Oracle's method of providing the SQL Tuning Advisor with SQL statements, you would have to capture them through the Automatic Database Diagnostic Monitor (ADDM) or through the Automatic Workload Repository AWR. Others of us, who do not want to use OEM or actually want to generate a tuning effort on statements that are working their way through development, will want to provide a user defined tuning set. The interface to the tuning advisor is through a package call DBMS_SQLTUNE. The best way to show its capabilities is to actually just step through the procedures required to produce and analyze a tuning set. The example I am showing here is definitely the simplest to get you up and running with this advisor but should give you a firm jumping off spot to further you investigation.
Creation of a Tuning Task
The first step in using the Tuning Advisor is the creation of a tuning task. The particular example I will be taking you through will only create a tuning task for a single SQL statement, but in theory, you can create tuning sets composed of many SQL statements that can be supplied by you or selected out of the shared pool. For this article, we will follow a very simple example that will take you through the use of the tuning advisor in order to get familiar with it and see how it works and if it provides us with adequate information to assist us in our tuning efforts.
Our Particular Example
The Purpose of our SQL is to join two tables, CUSTOMER & CUST_ORDER to get a count of the number of orders a company has.
These two tables were created with
NO indexes
NO statistics
NO referential integrity
The desired outcome is to have Oracle's Tuning Advisor step us through making modifications to the table structures and SQL to get an optimal execution
After taking the advice of the tuning advisor, we will execute the SQL statement to see if we actually improved the execution of the SQL.
We will iterate through changes to the structures and SQL until we are happy with the outcome.
The first step in using the advisor is to define the tuning task we desire. For our purposes here, we will just be supplying a single SQL statement into the tuning task and thus we should create the following procedure and execute it. The only in-puts to the CREATE_TUNING_TASK that may need explanation are the scope and time_limit variables. Scope can take the form of two values LIMITED & COMPREHENSIVE. The limited scope, basically, only produces a reasonable explain plan so if you want anything more you will find yourself running this advisor in the comprehensive scope mode. Comprehensive will go through iterations and different analysis of the SQL statement looking at statistics and structures to determine alternate access methodologies that can be used as well as point out deficiencies in the structures or SQL provided. The time_limit variable tells the tuning advisor how long to run (in seconds) and analyze your SQL statement.
create_tuning_task_comprehensive
SQL > CREATE or REPLACE PROCEDURE create_tuning_task IS
2 tuning_task VARCHAR2(30);
3 sqltext CLOB;
4 BEGIN
5 sqltext := 'select cust_name,count(*)'
6 ||' from customer, cust_order'
7 ||' where customer.cust_no = cust_order.cust_no'
8 ||' and customer.cust_no = 8'
9 ||' group by cust_name';
10 tuning_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
11 sql_text => sqltext,
12 user_name => 'SYS',
13 scope => 'COMPREHENSIVE',
14 time_limit => 30,
15 task_name => 'CUST_ORDERS',
16 description => 'Tuning effort for counting customer orders');
17 END create_tuning_task;
18 /
SQL > exec create_tuning_task
PL/SQL procedure successfully completed.
Execution of a Tuning Task
Once you have created the tuning task you need only execute it through the EXECUTE_TUNING_TASK function. This should run for the designated time_limit you specified in the tuning task.
execute_tuning_task
SQL > BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'CUST_ORDERS' );
3 END;
4 / PL/SQL procedure successfully completed.
Reporting on the tuning task
After you have executed the tuning task, you need only use the REPORT_TUNING_TASK function to get a comprehensive display of what the advisor has found. After looking over a few of these reports, you will soon develop an eye for their format. A header section tells you about the when and how you executed the tuning task. Following that, Oracle then produces a "Findings" section where it details what it has found and supplies recommendations that it may have for you. Then it produces an explain area showing the explain path it has for the current SQL.
report_tuning_task
SQL > SET LONG 1000
SQL > SET LONGCHUNKSIZE 1000
SQL > SET LINESIZE 100
SQL > SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'CUST_ORDERS') FROM DUAL;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : CUST_ORDERS
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 07/18/2004 17:17:52
Completed at : 07/18/2004 17:18:11
-------------------------------------------------------------------------------
SQL ID : a1s4nzcnjc70f
SQL Text: select cust_name,count(*) from customer, cust_order where
customer.cust_no = cust_order.cust_no and customer.cust_no = 8
group by cust_name
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
Table "SYS"."CUST_ORDER" was not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'CUST_ORDER', 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- Statistics Finding
---------------------
Table "SYS"."CUSTOMER" was not analyzed.
Recommendation
--------------
Consider collecting optimizer statistics for this table.
execute dbms_stats.gather_table_stats(ownname => 'SYS', tabname =>
'CUSTOMER', 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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 183K| 13M| 49567 (2)| 00:09:55 |
| 1 | SORT GROUP BY | | 183K| 13M| 49567 (2)| 00:09:55 |
| 2 | MERGE JOIN CARTESIAN| | 183K| 13M| 49553 (1)| 00:09:55 |
| 3 | TABLE ACCESS FULL | CUSTOMER | 177 | 11505 | 49 (0)| 00:00:01 |
| 4 | BUFFER SORT | | 1034 | 13442 | 49518 (2)| 00:09:55 |
| 5 | TABLE ACCESS FULL | CUST_ORDER | 1034 | 13442 | 279 (2)| 00:00:04 |
------------------------------------------------------------------------------------
Initial Performance
This is the current real-time experienced performance of our example SQL that we are providing to the tuning advisor. As you can see, the execution plan is what the advisor spit out and we are doing quit a bit of physical and logical reads. Moreover, the recursive calls and sorts could hopefully be tuned.
Initial Performance
SQL > select cust_name,count(*)
2 from customer, cust_order
3 where customer.cust_no = cust_order.cust_no
4 and customer.cust_no = 8
5 group by cust_name;
Elapsed: 00:00:02.81
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=649 Card=171805 Bytes=13400790)
1 0 SORT (GROUP BY) (Cost=649 Card=171805 Bytes=13400790)
2 1 MERGE JOIN (CARTESIAN) (Cost=635 Card=171805 Bytes=13400790)
3 2 TABLE ACCESS (FULL) OF 'CUSTOMER' (TABLE) (Cost=51 Card=2 Bytes=130)
4 2 BUFFER (SORT) (Cost=598 Card=71961 Bytes=935493)
5 4 TABLE ACCESS (FULL) OF 'CUST_ORDER' (TABLE) (Cost=292 Card=71961 Bytes=935493)
Statistics
----------------------------------------------------------
895 recursive calls
0 db block gets
1772 consistent gets
1160 physical reads
0 redo size
465 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
12 sorts (memory)
0 sorts (disk)
1 rows processed
Continuation of Tuning Task
The first suggestion given by the tuning advisor was to gather statistics on the two structures that we are joining as it can only produce valid analysis and suggestions if it has them. We subsequently cut and past the calls to GATHER_TABLE_STATS, it has given us.
SQL > execute dbms_stats.gather_table_stats(
ownname => 'SYS',
tabname => 'CUST_ORDER',
estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt =>
'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
SQL > execute dbms_stats.gather_table_stats(
ownname => 'SYS',
tabname => 'CUSTOMER',
estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE,
method_opt =>
'FOR ALL COLUMNS SIZE AUTO');
PL/SQL procedure successfully completed.
In order to re-execute the tuning task, we must first reset the results we have gathered. This is done by a simple call to RESET_TUNING_TASK.
reset_tuning_task
SQL> BEGIN
2 DBMS_SQLTUNE.RESET_TUNING_TASK
( task_name => 'CUST_ORDERS' );
3 END;
4 /
PL/SQL procedure successfully completed.
We then call the EXECUTE_TUNING_TASK function.
execute_tuning_task
SQL > BEGIN
2 DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'CUST_ORDERS' );
3 END;
4 /
PL/SQL procedure successfully completed.
Then we produce the report to see if anything has gotten better.
report_tuning_task
SQL > SET LONG 1000
SQL > SET LONGCHUNKSIZE 1000
SQL > SET LINESIZE 100
SQL > SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'CUST_ORDERS') FROM DUAL;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : CUST_ORDERS
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 07/18/2004 18:13:51
Completed at : 07/18/2004 18:13:55
-------------------------------------------------------------------------------
SQL ID : a1s4nzcnjc70f
SQL Text: select cust_name,count(*) from customer, cust_order where
customer.cust_no = cust_order.cust_no and customer.cust_no = 8
group by cust_name
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 2 of the
execution plan.
Recommendation
--------------
Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.
Rationale
---------
A cartesian product should be avoided whenever possible because it is an
expensive operation and might produce a large amount of data.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 347 (6)| 00:00:05 |
| 1 | SORT GROUP BY | | 1 | 18 | 347 (6)| 00:00:05 |
| 2 | MERGE JOIN CARTESIAN| | 68508 | 1204K| 342 (5)| 00:00:05 |
| 3 | TABLE ACCESS FULL | CUSTOMER | 1 | 15 | 50 (2)| 00:00:01 |
| 4 | BUFFER SORT | | 68508 | 200K| 297 (7)| 00:00:04 |
| 5 | TABLE ACCESS FULL | CUST_ORDER | 68508 | 200K| 291 (5)| 00:00:04 |
Performance of putting statistics on the tables
SQL > select cust_name,count(*)
2 from customer, cust_order
3 where customer.cust_no = cust_order.cust_no
4 and customer.cust_no = 8
5 group by cust_name;
Elapsed: 00:00:01.67
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=348 Card=1 Bytes=18)
1 0 SORT (GROUP BY) (Cost=348 Card=1 Bytes=18)
2 1 MERGE JOIN (CARTESIAN) (Cost=343 Card=64969 Bytes=1169442)
3 2 TABLE ACCESS (FULL) OF 'CUSTOMER' (TABLE) (Cost=51 Card=1 Bytes=15)
4 2 BUFFER (SORT) (Cost=298 Card=64969 Bytes=194907)
5 4 TABLE ACCESS (FULL) OF 'CUST_ORDER' (TABLE) (Cost=292 Card=64969 Bytes=194907)
Statistics
----------------------------------------------------------
328 recursive calls
0 db block gets
1530 consistent gets
1487 physical reads
0 redo size
465 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
7 sorts (memory)
0 sorts (disk)
1 rows processed
In this particular report, you can see that our FINDINGS section has been reduced and now we only have one. Also, note that while the performance of the SQL statement changes slightly it is doing the same amount of work as without the statistics. The tuning advisor report is telling us that we have a Cartesian product within the explain plan and that we should try to reduce it. I was really hoping that the advisor would make suggestions and point out that these two structures did not have a primary key or where not indexed. Just by looking at the SQL statement, a common observer would think there would be indexes on the CUST_NO field. Since I know there are not any indexes on these structures, I decide to put them in place by the following DDL and see what the advisor tells me.
Creation of indexes for our two tables
SQL > create index customer_ix01 on customer (cust_no);
Index created.
SQL > create index cust_order_ix01 on cust_order (cust_no);
Index created.
After creating the indexes and running the RESET_TUNING_TASK, EXECUTE_TUNING_TASK and REPORT_TUNING_TASK, we get the following tuning report. I was really hoping to get rid of that Cartesian product operation as I felt that Oracle would know now that I was in fact joining the tables properly. In addition, if you look at the performance statistics generated after running the SQL statement you can see that the performance is quite good and most of us would stop at this point. But hey, Oracle knows better, right?
report_tuning_task
SQL > SET LONG 1000
SQL > SET LONGCHUNKSIZE 1000
SQL > SET LINESIZE 100
SQL > SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'CUST_ORDERS') FROM DUAL;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : CUST_ORDERS
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 07/18/2004 19:47:34
Completed at : 07/18/2004 19:47:38
-------------------------------------------------------------------------------
SQL ID : a1s4nzcnjc70f
SQL Text: select cust_name,count(*) from customer, cust_order where
customer.cust_no = cust_order.cust_no and customer.cust_no = 8
group by cust_name
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive cartesian product operation was found at line ID 2 of the
execution plan.
Recommendation
--------------
Consider removing the disconnected table or view from this statement or
add a join condition which refers to it.
Rationale
---------
A cartesian product should be avoided whenever possible because it is an
expensive operation and might produce a large amount of data.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 136 (5)| 00:00:02 |
| 1 | SORT GROUP BY | | 1 | 18 | 136 (5)| 00:00:02 |
| 2 | MERGE JOIN CARTESIAN | | 64969 | 1142K| 131 (1)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| CUSTOMER | 1 | 15 | 2 (0)| 00:00:01 |
| 4 | INDEX RANGE SCAN | CUSTOMER_IX01 | 1 | | 1 (0)| 00:00:01 |
| 5 | BUFFER SORT | | 64969 | 190K| 134 (5)| 00:00:02 |
| 6 | INDEX RANGE SCAN | CUST_ORDER_IX01 | 64969 | 190K| 129 (1)| 00:00:02 |
Performance after adding indexes
SQL > select cust_name,count(*)
2 from customer, cust_order
3 where customer.cust_no = cust_order.cust_no
4 and customer.cust_no = 8
5 group by cust_name;
Elapsed: 00:00:01.16
Execution Plan
-------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=137 Card=1 Bytes=18)
1 0 SORT (GROUP BY) (Cost=137 Card=1 Bytes=18)
2 1 MERGE JOIN (CARTESIAN) (Cost=131 Card=64969 Bytes=1169442)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER' (TABLE) (Cost=2 Card=1 Bytes=15)
4 3 INDEX (RANGE SCAN) OF 'CUSTOMER_IX01' (INDEX) (Cost=1 Card=1)
5 2 BUFFER (SORT) (Cost=135 Card=64969 Bytes=194907)
6 5 INDEX (RANGE SCAN) OF 'CUST_ORDER_IX01' (INDEX) (Cost=129 Card=64969 Bytes=194907)
Statistics
----------------------------------------------------------
378 recursive calls
0 db block gets
193 consistent gets
137 physical reads
0 redo size
465 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
1 rows processed
The only way I could think of to eliminate that annoying Cartesian message was to give Oracle a bit more information in the SQL statement. I did this by adding "and cust_order.cust_no = 8" to the SQL statement. This should take away all doubt about what I am trying to do even though I have linked the CUST_ORDER.CUST_NO to CUSTOMER.CUST_NO columns. Just remember that if you want to change the SQL, you must go back and re-do all the steps here from creation, execution, and report with the added call to a DROP_TUNING_TASK.
drop_tuning_task
exec dbms_sqltune.drop_tuning_task('CUST_ORDERS')
After recreating the tuning task and producing the report, here is what we got. Good, we got rid of the Cartesian warning and the advisor tells us that there are no more recommendations we can do. Looking at the actual performance of the SQL statement, we can see that the only change was a reduction of the number of sorts.
report_tuning_task
SQL > SET LONG 1000
SQL > SET LONGCHUNKSIZE 1000
SQL > SET LINESIZE 100
SQL > SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'CUST_ORDERS') FROM DUAL;
GENERAL INFORMATION SECTION
------------------------------------------------------
Tuning Task Name : CUST_ORDERS
Scope : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status : COMPLETED
Started at : 07/18/2004 19:56:06
Completed at : 07/18/2004 19:56:09
------------------------------------------------------
SQL ID : 9xdx7wamdfqd6
DBMS_SQLTUNE.REPORT_TUNING_TASK('CUST_ORDERS')
------------------------------------------------------
SQL Text: select cust_name,count(*) from customer, cust_order where
customer.cust_no = cust_order.cust_no and customer.cust_no = 8
and cust_order.cust_no = 8 group by cust_name
-------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------
Performance after addition of "and cust_order.cust_no = 8
SQL > select cust_name,count(*)
2 from customer, cust_order
3 where customer.cust_no = cust_order.cust_no
4 and customer.cust_no = 8
5 and cust_order.cust_no = 8
6 group by cust_name;
Elapsed: 00:00:00.92
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=132 Card=1 Bytes=18)
1 0 SORT (GROUP BY) (Cost=132 Card=1 Bytes=18)
2 1 NESTED LOOPS (Cost=131 Card=1 Bytes=18)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER' (TABLE) (Cost=2 Card=1 Bytes=15)
4 3 INDEX (RANGE SCAN) OF 'CUSTOMER_IX01' (INDEX) (Cost=1 Card=1)
5 2 INDEX (RANGE SCAN) OF 'CUST_ORDER_IX01' (INDEX) (Cost=129 Card=1 Bytes=3)
Statistics
----------------------------------------------------------
378 recursive calls
0 db block gets
193 consistent gets
137 physical reads
0 redo size
465 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
1 rows processed
While it may have seemed a bit long-winded to get to our conclusion and optimal SQL statement tuned, we did get there. Moreover, Oracle helped us in a way that was not available to us before. Now even if you are not the greatest SQL coder on the planet, you can get recommendations from the expert, Oracle.
By James Koopmann
No comments:
Post a Comment