Home » RDBMS Server » Performance Tuning » Explain plan of a query (Oracle 11g)
Explain plan of a query [message #565538] Wed, 05 September 2012 03:13 Go to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Hi,

I have taken the explain plan of a query through the below methods. But both are not similar. Which one is correct? Also in the 1st method, I am seeing the multiple plan. is it becasue of version count of this query?

1. select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
2. select * from TABLE(DBMS_XPLAN.DISPLAY);

METHOD 1:
========

SQL> select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'));
Enter value for sqlid: 91pkd4n178zfr
old   1: select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('&sqlid'))
new   1: select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('91pkd4n178zfr'))

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
-------------------------------------------
SQL_ID 91pkd4n178zfr
--------------------
SELECT * FROM CUSTMODEL.HECTOR_CIDB_IN WHERE PROCESS_FLAG = 'N' AND
DATA_SOURCE_FEED = :B2 AND ROWNUM < :B1 ORDER BY HC_ID

Plan hash value: 1889556014

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |       |       | 10634 (100)|          |
|   1 |  COUNT STOPKEY               |                   |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| HECTOR_CIDB_IN    |  2677K|   273M| 10634   (1)| 00:02:29 |
|   3 |    INDEX FULL SCAN           | PK_HECTOR_CIDB_IN | 43816 |       |    52   (2)| 00:00:01 |
--------------------------------------------------------------------------------------------------

SQL_ID 91pkd4n178zfr
--------------------
SELECT * FROM CUSTMODEL.HECTOR_CIDB_IN WHERE PROCESS_FLAG = 'N' AND
DATA_SOURCE_FEED = :B2 AND ROWNUM < :B1 ORDER BY HC_ID

Plan hash value: 2508618195

--------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |       |       |     9 (100)|          |
|   1 |  SORT ORDER BY                |                        |    42 |  4452 |     9  (12)| 00:00:01 |
|   2 |   COUNT STOPKEY               |                        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| HECTOR_CIDB_IN         |    42 |  4452 |     8   (0)| 00:00:01 |
|   4 |     INDEX RANGE SCAN          | INDX_HECTOR_CIDB_IN_PF |   673 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

SQL_ID 91pkd4n178zfr
--------------------
SELECT * FROM CUSTMODEL.HECTOR_CIDB_IN WHERE PROCESS_FLAG = 'N' AND
DATA_SOURCE_FEED = :B2 AND ROWNUM < :B1 ORDER BY HC_ID

Plan hash value: 3242519344

----------------------------------------------------------------------------------------------
| Id  | Operation           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |                |       |       |       |   115K(100)|          |
|   1 |  SORT ORDER BY      |                |  9012 |   871K|  1968K|   115K  (2)| 00:27:02 |
|   2 |   COUNT STOPKEY     |                |       |       |       |            |          |
|   3 |    TABLE ACCESS FULL| HECTOR_CIDB_IN |  9012 |   871K|       |   115K  (2)| 00:27:00 |
----------------------------------------------------------------------------------------------


METHOD 2:
========

SQL> explain plan for
  2  SELECT * FROM CUSTMODEL.HECTOR_CIDB_IN WHERE PROCESS_FLAG = 'N' AND DATA_SOURCE_FEED = :B2 AND ROWNUM < :B1 ORDER BY HC_ID;

Explained.

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

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------
Plan hash value: 2508618195

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |  1343K|   126M|       |   170K  (3)| 00:39:46 |
|   1 |  SORT ORDER BY                |                        |  1343K|   126M|   283M|   170K  (3)| 00:39:46 |
|*  2 |   COUNT STOPKEY               |                        |       |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| HECTOR_CIDB_IN         |  1343K|   126M|       |   151K  (3)| 00:35:19 |
|*  4 |     INDEX RANGE SCAN          | INDX_HECTOR_CIDB_IN_PF |    17M|       |       | 16137   (4)| 00:03:46 |
----------------------------------------------------------------------------------------------------------------

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

   2 - filter(ROWNUM<TO_NUMBER(:B1))
   3 - filter("DATA_SOURCE_FEED"=:B2)
   4 - access("PROCESS_FLAG"='N')
Re: Explain plan of a query [message #565543 is a reply to message #565538] Wed, 05 September 2012 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first one gives the plan(s) that were used during the period AWR covers.
The second one gives the plan(s) currently in SGA.

Regards
Michel
Re: Explain plan of a query [message #565544 is a reply to message #565543] Wed, 05 September 2012 03:23 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
if I run this query now, which plan it will use?
Re: Explain plan of a query [message #565545 is a reply to message #565544] Wed, 05 September 2012 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Who knows?
It depends on many things, starting with your complete version number.

Regards
Michel
Re: Explain plan of a query [message #565547 is a reply to message #565545] Wed, 05 September 2012 03:46 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
I generated AWR report now. There also I am seeing this query in top 1 under sql ordered by elapsed time. it is taking 1046 seconds per execution. I am going to suggest to create a index on DATA_SOURCE_FEED column. Already an index exist on PROCESS_FLAG column.

This table has 70 Million records. What else you suggest to improve the performance of this query?


Re: Explain plan of a query [message #565549 is a reply to message #565547] Wed, 05 September 2012 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your query is wrong, ORDER should be done before ROWNUM, in your case it is done after.
This is a classical TOP-N query.
You must use an inner query with ORDER and the outer one restricts on ROWNUM.
This will not speed up your query (it is the opposite) but it will give you the correct result.

Regards
Michel
Re: Explain plan of a query [message #565550 is a reply to message #565549] Wed, 05 September 2012 03:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also your statistics are not up to date, you must first regather them.

Regards
Michel
Re: Explain plan of a query [message #565555 is a reply to message #565550] Wed, 05 September 2012 04:19 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
How you are saying this statistics is not up to date?

SQL> select TABLE_NAME,LAST_ANALYZED,NUM_ROWS,AVG_ROW_LEN,PARTITIONED from dba_tables where TABLE_NAME='HECTOR_CIDB_IN';

TABLE_NAME                     LAST_ANAL   NUM_ROWS AVG_ROW_LEN PAR
------------------------------ --------- ---------- ----------- ---
HECTOR_CIDB_IN                 04-SEP-12   69855980          99 NO
Re: Explain plan of a query [message #565558 is a reply to message #565555] Wed, 05 September 2012 04:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
On those on the index(es); see the numbers given in your plans.

Regards
Michel
Re: Explain plan of a query [message #565560 is a reply to message #565549] Wed, 05 September 2012 04:31 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Are you asking me to change the query like below? will it be fast? if not, how can I make it fast?

SELECT * FROM ( select * from CUSTMODEL.HECTOR_CIDB_IN WHERE PROCESS_FLAG = 'N' AND DATA_SOURCE_FEED ='SR_HECTOR' ORDER BY HC_ID) where ROWNUM < 10001 ;
Re: Explain plan of a query [message #565562 is a reply to message #565560] Wed, 05 September 2012 04:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.

Regards
Michel
Re: Explain plan of a query [message #565567 is a reply to message #565562] Wed, 05 September 2012 04:59 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Thanks for the input Michel. Also I need your suggestion to speed up the rownum here
Re: Explain plan of a query [message #565569 is a reply to message #565567] Wed, 05 September 2012 05:21 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Currently we have B-tree index on PROCESS_FLAG column. But it contains only 2 distinct values (Y and N). And DATA_SOURCE_FEED column doesn't have index. So I am planning to create an index. which method will be good in the below?

1. Drop existing index which is created on PROCESS_FLAG column.
Create a bitmap index on PROCESS_FLAG column. And then create another index on DATA_SOURCE_FEED column.

2. create a composite index on this two columns (PROCESS_FLAG,DATA_SOURCE_FEED).
Re: Explain plan of a query [message #565572 is a reply to message #565569] Wed, 05 September 2012 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check all of them, it is the best way to get the best solution.
I don't think an index on DATA_SOURCE_FEED has any interest unless you have a big difference on number of occurences of the values and search for the one that has the less number of rows.

Regards
Michel
Re: Explain plan of a query [message #565868 is a reply to message #565572] Fri, 07 September 2012 07:48 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
sorry for asking this question again.

Table count = 6.25 millions

we have 2 distinct values (Y and N) in process_flag column 13 distinct values in data_source_feed column.I know that bitmap index will be definitely helpful here to speedup the above query. But I want to know which one will be very good among the below.

1. Create composite bitmap index.
2. Bitmap index in each column.


Table count = 6.25 Millions
Re: Explain plan of a query [message #565869 is a reply to message #565868] Fri, 07 September 2012 07:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Check all of them, it is the best way to get the best solution.


Note that if you did 2 days ago when I suggested it, you'd have your answer.

Regards
Michel

[Updated on: Fri, 07 September 2012 07:52]

Report message to a moderator

Re: Explain plan of a query [message #565870 is a reply to message #565868] Fri, 07 September 2012 07:52 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
run benchmark test for both cases & compare results
Previous Topic: Slow running query (3 Merged)
Next Topic: Tuning issue
Goto Forum:
  


Current Time: Thu Mar 28 03:15:46 CDT 2024