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 |
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.
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.
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.
» 
IE 7
Firefox 2.0
Comment and Contribute
