Search My Oracle Blog

Custom Search

14 April, 2008

The Worst Ever SQL Rewrite


What is the worst ever re-write you've done in attempting to tune an SQL statement ?

This is what I did a few weeks ago. I took a complicated (it had 3 correlated complex sub-queries) of the form :



SELECT A.column_1
, A.column_2
...
FROM table_A A
,table_P P
...
WHERE A.column_1=E.column_1
...
AND D.column_15 = (
SELECT /*+ NO_MERGE */ MAX(column_25)
FROM
....
.. 71 line SQL statement

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 888888888

------------------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 47M (1)159:03:36
* 1 FILTER
* 2 HASH JOIN RIGHT OUTER 48M 15G 177K (1) 00:35:26
3 INDEX FAST FULL SCAN A_TABLE 2687 67175 5 (0) 00:00:01


so you can see that it had already been hinted during the course of a tuning exercise ...

and I transformed it with



SELECT /*+ ORDERED */ A.column_1
...
-- FROM table_A A
-- , table_P P
FROM
table_E E
, table_D D
... 74lines

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 999999999

------------------------------------------------------------------------------------------------------------------------
Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
------------------------------------------------------------------------------------------------------------------------
0 SELECT STATEMENT 1 337 250T (2)999:59:59
* 1 FILTER
* 2 HASH JOIN RIGHT OUTER 48M 15G 250T (2)999:59:59
3 INDEX FAST FULL SCAN A_TABLE 2687 67175 5 (0) 00:00:01




I managed to increase the "COST" from 47million to 250trillion.
Oracle's projected query runtime went up from 159hours to to in excess of 1000 hours. !

And my mistake ? Wrongly ordering the tables in the FROM clause after putting in an ORDERED Hint ! Very fortunately, I hadn't run the query else I would have waited too long before realising that I had forced Oracle into an atrocious execution plan.

Moral of the story ? Hints do work as Directives ! Be careful with Hints.

No comments:

Aggregated by orafaq.com

Aggregated by orafaq.com
This blog is being aggregated by orafaq.com

Top 50 Oracle SQL Blogs 2016

Top 50 Oracle SQL Blogs 2016
Top 50 Oracle SQL Blogs 2016