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.