OraFAQ Forum: Performance Tuning » Explain Plan (Query Transformation)

Home » RDBMS Server » Performance Tuning » Explain Plan (Query Transformation)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Explain Plan (Query Transformation) [message #149827]

Mon, 05 December 2005 03:39

dhar_kiran
Messages: 7
Registered: November 2005
Location: Delhi

Junior Member

Is it possible to find out the query transformed by optimizer, that generates the best plan.

e.g:- select * from emp
where empno ='E001' or job ='CLERK'

if transformed into
select * from emp where empno='E001'
union all
select * from emp where job='CLERK' and empno <> 'E001'

How to find out transformed query, like is there any view?
__________________

Report message to a moderator

Re: Explain Plan (Query Transformation) [message #149906 is a reply to message #149827]

Mon, 05 December 2005 12:07

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

I don't know the answer to your specific question, but the 10053 trace event is used to provide more inoformation on optimizer choices, so you might start there.

Report message to a moderator

Re: Explain Plan (Query Transformation) [message #150034 is a reply to message #149906]

Tue, 06 December 2005 01:20

dhar_kiran
Messages: 7
Registered: November 2005
Location: Delhi

Junior Member

What is trace Event ? How to use it?

Report message to a moderator

Re: Explain Plan (Query Transformation) [message #150044 is a reply to message #150034]

Tue, 06 December 2005 01:34

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://www.oracle.com/pls/db102/portal.portal_db?selected=1
Use the search button.

Report message to a moderator

Re: Explain Plan (Query Transformation) [message #150215 is a reply to message #149827]

Tue, 06 December 2005 15:03

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hello.

Not sure about the EXACT SQL statement that the optimiser picks (i'm not entirely sure that such a thing exists, it'll almost certainly transform it to some form of internal representation of your SQL, but that's another matter), but since 9i and above EXPLAIN PLANs / tkprofs show predicate information, in conjunction with the plan itself, it's fairly easy to work it out :

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM t WHERE a = 'X' OR b = 'Y';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1990 | 27860 |    29  (18)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |  1990 | 27860 |    29  (18)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("A"='X' OR "B"='Y')

13 rows selected.

The "filter" operation proves that no real "transformation" has occurred, it's evaluated using a literal OR, compared with

SQL> EXPLAIN PLAN FOR
  2  SELECT * FROM t WHERE a = 'X'
  3  UNION
  4  SELECT * FROM t WHERE b = 'Y';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 3004542453

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |  1001 | 14014 |    32  (94)| 00:00:01 |
|   1 |  SORT UNIQUE                  |             |  1001 | 14014 |    32  (94)| 00:00:01 |
|   2 |   UNION-ALL                   |             |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T           |     1 |    14 |     2   (0)| 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN         | SYS_C005584 |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    TABLE ACCESS FULL          | T           |  1000 | 14000 |    28  (15)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"='X')
   5 - filter("B"='Y')

18 rows selected.

Showing that two seperate operations on "t" has occurred.

Unless there is some way of getting the exact SQL (and I assume it's because you want to use this generated SQL?), I think you're going to have to evaluate your plans.

Rgds.

Report message to a moderator

Re: Explain Plan (Query Transformation) [message #150220 is a reply to message #149827]

Tue, 06 December 2005 15:38

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Quote:

Showing that two seperate operations on "t" has occurred

I don't understand how this shows that a query transformation happened? In your second example it looked as though you wrote a seperate query, and the explain plan and predicate reported was just a reflection of your completely new query.

Report message to a moderator

Re: Explain Plan (Query Transformation) [message #150225 is a reply to message #150220]

Tue, 06 December 2005 16:42

mchadder
Messages: 224
Registered: May 2005
Location: UK

Senior Member

Hi there.

No, I wasn't showing that a query transformation was occurring, I rewrote the query to how you would think one of the ways that Oracle could rewrite the query, to show that it would be the plan and / or the predicate information to deduce what "SQL" had been executed.

Rgds.

Report message to a moderator