Home » RDBMS Server » Performance Tuning » Explain Plan (Query Transformation)
Explain Plan (Query Transformation) [message #149827] Mon, 05 December 2005 03:39 Go to next message
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?
__________________
Re: Explain Plan (Query Transformation) [message #149906 is a reply to message #149827] Mon, 05 December 2005 12:07 Go to previous messageGo to next message
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.
Re: Explain Plan (Query Transformation) [message #150034 is a reply to message #149906] Tue, 06 December 2005 01:20 Go to previous messageGo to next message
dhar_kiran
Messages: 7
Registered: November 2005
Location: Delhi
Junior Member

What is trace Event ? How to use it?
Re: Explain Plan (Query Transformation) [message #150044 is a reply to message #150034] Tue, 06 December 2005 01:34 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10706
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
http://www.oracle.com/pls/db102/portal.portal_db?selected=1
Use the search button.
Re: Explain Plan (Query Transformation) [message #150215 is a reply to message #149827] Tue, 06 December 2005 15:03 Go to previous messageGo to next message
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.
Re: Explain Plan (Query Transformation) [message #150220 is a reply to message #149827] Tue, 06 December 2005 15:38 Go to previous messageGo to next message
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.
Re: Explain Plan (Query Transformation) [message #150225 is a reply to message #150220] Tue, 06 December 2005 16:42 Go to previous messageGo to next message
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.
Re: Explain Plan (Query Transformation) [message #150351 is a reply to message #149827] Wed, 07 December 2005 08:20 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Oh i get it...sorry misunderstood the intent.
Re: Explain Plan (Query Transformation) [message #150384 is a reply to message #150351] Wed, 07 December 2005 11:16 Go to previous message
mchadder
Messages: 224
Registered: May 2005
Location: UK
Senior Member
Hi.

No problem. To be fair, I don't think I really conveyed my meaning properly anyway. Wink

Rgds
Previous Topic: Database Performance Tuning
Next Topic: Performance of the sql due to group by
Goto Forum:
  


Current Time: Mon Nov 28 06:15:04 CST 2022