Home » RDBMS Server » Performance Tuning » Understanding Explain Plan (10g)
| Understanding Explain Plan [message #341938] |
Wed, 20 August 2008 15:53  |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
hi,
i'm just confused on reading a explain plan on what it means for each step. i know what some of the step means, but just this one question below:
create table t as
select object_id, object_name
from all_objects
where rownum <= 1000
alter table t add constraint t_pk primary key (object_id)
exec dbms_stats.gather_table_stats(user, 'T', cascade => true);
SQL> select object_id, object_name
2 from t
3 where object_id between 258 and 300
4 /
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=27 Bytes=56
7)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (TABLE) (Cost=3 Card=
27 Bytes=567)
--does the step below means that it already fetch all the blocks in the index?
-- or does it mean it fetch one "record/rowid" at a time then move to next step above?
2 1 INDEX (RANGE SCAN) OF 'T_PK' (INDEX (UNIQUE)) (Cost=2 Ca
rd=27)
thank you very much!
|
|
|
|
| Re: Understanding Explain Plan [message #341959 is a reply to message #341938] |
Wed, 20 August 2008 21:58  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
RANGE SCAN means that it may fetch many rows from the index.
As each row is fetched from the index, it is passed back to the parent step for further processing - in this case a table lookup.
It does NOT get all the matching rows from the index and THEN lookup the table. That would require intermediate storage of the matching rows.
Ross Leishman
|
|
|
|
|
|
| Re: Understanding Explain Plan [message #342120 is a reply to message #341959] |
Thu, 21 August 2008 07:23 |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
thanks rleishman & michel, so that means for such a query the optimizer have "returned" to the index and table many times. does the "putting blocks to be read in memory and then process it" already take place in here? sorry for the newbie questions. im continually reading the docu, just having a hard time translating the english =(
regards,
rhani
|
|
|
|
| Re: Understanding Explain Plan [message #342759 is a reply to message #342120] |
Sun, 24 August 2008 21:55 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
I sense a complex question/answer/counter-question pattern forming here.
To understand what is happening, you really need to understand some fundamentals.
This is an excellent primer on the internals of Oracle.
Ross Leishman
|
|
|
|
| Re: Understanding Explain Plan [message #342878 is a reply to message #342759] |
Mon, 25 August 2008 07:46 |
ehegagoka
Messages: 493
Registered: July 2005
|
Senior Member |
|
|
|
Thank you very much! I was also reading this type of article like summarize oracle concepts, before I was reading this on Dizwell/Howard's site but it was removed, don't know why =(
|
|
|
|
| Re: Understanding Explain Plan [message #389455 is a reply to message #341938] |
Mon, 02 March 2009 03:07 |
oyaryn
Messages: 1
Registered: March 2009
Location: malaysia
|
Junior Member |
|
|
hi all
I need help to understand the following scenarios.  I have tried to understand how it works but still I couldn't find the explanation.
I have created 10 partitions for TRACKING_EVENT table. And there are 3 global indexes: TRK_LOCATION, MSG_CORREL_ID and TRANSACTION CODE
Below is the query that I've used to create the table partition:
CREATE TABLE TRACKING_EVENT (
TRK_LOCATION VARCHAR2 (64) NOT NULL,
TRK_DATETIME DATE NOT NULL,
TRK_SEQ NUMBER,
LOG_DATETIME DATE NOT NULL,
MSG_CORRELID VARCHAR2 (48) NOT NULL,
TRANSACTION_CODE VARCHAR2 (12) NOT NULL )
PARTITION BY RANGE (LOG_DATETIME)
(
PARTITION par03012009 VALUES LESS THAN (TO_DATE('04/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par04012009 VALUES LESS THAN (TO_DATE('05/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par05012009 VALUES LESS THAN (TO_DATE('06/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par06012009 VALUES LESS THAN (TO_DATE('07/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par07012009 VALUES LESS THAN (TO_DATE('08/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par08012009 VALUES LESS THAN (TO_DATE('09/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par09012009 VALUES LESS THAN (TO_DATE('10/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par10012009 VALUES LESS THAN (TO_DATE('11/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par11012009 VALUES LESS THAN (TO_DATE('12/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata,
PARTITION par12012009 VALUES LESS THAN (TO_DATE('13/01/2009', 'DD/MM/YYYY')) TABLESPACE mtdata
);
Scenario 1
When I run below query by specifying the equivalent date range the partition pruning is happening. It only scanned the partition in the range 2 -> 2. Refer to the plan table output below:
SELECT *
FROM TRACKING_EVENT
WHERE TRANSACTION_CODE = 'AAA'
AND TRK_LOCATION LIKE 'QM1.QM2.EN\Sender'
AND LOG_DATETIME >= TO_DATE( '2009-01-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AND LOG_DATETIME <= TO_DATE( '2009-01-04 23:59:59', 'YYYY-MM-DD HH24:MI:SS' )
ORDER BY LOG_DATETIME DESC, MSG_CORRELID DESC, TRK_SEQ DESC, TRANSACTION_CODE, TRK_LOCATION
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 99 | 4 | | |
| 1 | SORT ORDER BY | | 1 | 99 | 4 | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TRACKING_EVENT | 1 | 99 | 2 | 2 | 2 |
|* 3 | INDEX RANGE SCAN | TRANSACTION_CODE | 14132| | 1 | | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("TRACKING_EVENT"."TRK_LOCATION" LIKE 'QM1.QM2.EN\Sender' AND "TRACKING_EVENT".
"LOG_DATETIME">=TO_DATE('2009-01-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "TRACKING_EV
ENT"."LOG_DATETIME"<=TO_DATE('2009-01-04 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
3 - access("TRACKING_EVENT"."TRANSACTION_CODE"='AAA')
Scenario 2
Now I run the same query but this time I specified different date range from 04-01-2009 to 08-01-2009, oracle doesn't go to the specific partition like it supposed to be. Instead it is using PStart=ROWID and PStop=ROWL. I thought it should be PStart=2 and PStop=6. Refer to the plan table output below:
SELECT *
FROM TRACKING_EVENT
WHERE TRANSACTION_CODE = 'AAA'
AND TRK_LOCATION LIKE 'QM1.QM2.EN\Sender'
AND LOG_DATETIME >= TO_DATE( '2009-01-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AND LOG_DATETIME <= TO_DATE( '2009-01-08 23:59:59', 'YYYY-MM-DD HH24:MI:SS' )
ORDER BY LOG_DATETIME DESC, MSG_CORRELID DESC, TRK_SEQ DESC, TRANSACTION_CODE, TRK_LOCATION
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 396 | 4 | | |
| 1 | SORT ORDER BY | | 4 | 396 | 4 | | |
|* 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TRACKING_EVENT | 4 | 396 | 2 | ROWID | ROWL |
|* 3 | INDEX RANGE SCAN | TRANSACTION_CODE | 70664| | 1 | | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - filter("TRACKING_EVENT"."TRK_LOCATION" LIKE 'QM1.QM2.EN\Sender' AND "TRACKING_EVENT".
"LOG_DATETIME">=TO_DATE('2009-01-04 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "TRACKING_EV
ENT"."LOG_DATETIME"<=TO_DATE('2009-01-08 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
3 - access("TRACKING_EVENT"."TRANSACTION_CODE"='AAA')
In Scenario 1 oracle managed to visit specific partition range but it is not happening in Scenario 2. I'm not sure whether this is correct or wrong.
Is it because of the global indexes that I've used in the select statement? I'm getting confused because I'm using the same SQL query, but with the different date range.
Or maybe because of the way I created the partition predicate is not accurate like converting the date or something?
-> PARTITION par09012009 VALUES LESS THAN (TO_DATE('10/01/2009', 'DD/MM/YYYY'))
Then I modified the query for testing purpose:
- First by replacing >= and <= operator with BETWEEN in the SELECT statement
- Second by only specifying LOG_DATETIME > TO_DATE( '2009-01-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS' ) in the SELECT statement, still I got the same plan table output. PStart=ROWID and PStop=ROWL
Scenario 3
Then I did some further investigation and found that if I run the SQL statement without TRANSACTION_CODE and TRK_LOCATION field which are global indexes, oracle managed to go to the specific partitions range. The PARTITION RANGE ITERATOR indicates that Oracle is performing a partition prune. It only visits the partitions in the range 6 to 2 which is agreed. Refer to the plan table output below:
But why PStart=6 and PStop=2? From what I understand it should be from PStart=2 and PStop=6.
SELECT *
FROM TRACKING_EVENT
WHERE LOG_DATETIME >= TO_DATE( '2009-01-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS' )
AND LOG_DATETIME <= TO_DATE( '2009-01-08 23:59:59', 'YYYY-MM-DD HH24:MI:SS' )
ORDER BY LOG_DATETIME DESC, MSG_CORRELID DESC, TRK_SEQ DESC, TRANSACTION_CODE, TRK_LOCATION
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | TmpSpc | Cost | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 44165 | 4269K | | 22573 | | |
| 1 | PARTITION RANGE ITERATOR | | | | | | 6 | 2 |
| 2 | SORT ORDER BY | | 44165 | 4269K | 9M | 22573 | | |
|* 3 | TABLE ACCESS FULL | TRACKING_EVENT | 44165 | 4269K | | 20802 | 6 | 2 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
---------------------------------------------------
3 - filter("TRACKING_EVENT"."LOG_DATETIME"<=TO_DATE('2009-01-08 23:59:59', 'yyyy-mm-dd hh24:mi:ss'))
Another question is what does Rows and TempSpc column in plan table output means? I compared the value of Rows column with the actual number of record return by the select statement but it doesn't show the same.
For you information the Oracle version is 9.2.0 and running on AIX server.
Many thanks in advance. Your help is highly appreciated.
Cheers
|
|
|
|
Goto Forum:
Current Time: Sun Jun 30 13:28:36 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
