| Explain plan of a query [message #565538] |
Wed, 05 September 2012 03:13  |
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 #565547 is a reply to message #565545] |
Wed, 05 September 2012 03:46  |
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 #565555 is a reply to message #565550] |
Wed, 05 September 2012 04:19 |
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 #565560 is a reply to message #565549] |
Wed, 05 September 2012 04:31 |
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 #565569 is a reply to message #565567] |
Wed, 05 September 2012 05:21 |
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 #565868 is a reply to message #565572] |
Fri, 07 September 2012 07:48 |
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
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
