Home » RDBMS Server » Performance Tuning » Understanding Execution Plan of a query (Oracle 10.2.0.4)
Understanding Execution Plan of a query [message #548750] Sun, 25 March 2012 23:35 Go to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

I have queries on the execution plan of a sql statement

Please help me understand

Following is the example

create table t1 as select s1.nextval id,a.* from dba_objects a;
create table t2 as select s2.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t1 select s1.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
insert into t2 select s2.nextval id,a.* from dba_objects a;
commit;

create index i1 on t1(id);
create index i2 on t2(id);
create index i11 on t1(object_type);

exec dbms_stats.gather_table_stats(user,'T1',cascade=>true);
exec dbms_stats.gather_table_stats(user,'T2',cascade=>true);

select count(*) from t1 where object_type='VIEW';

  COUNT(*)
----------
      8934

set autotrace traceonly explain


Can we say in the following case, that,
(1) First index on object_type is accessed to get rowids - t1.object_type='VIEW'
(2) Then the filter on owner is applied - t1.owner='SYS'
(3) Then the table T1 is accessed to fetch data from the rowids returned by the index I11 and filer application - TABLE ACCESS BY INDEX ROWID

Though I am unable to understand how filter can be applied to the rowids retrieved from index, we can see from the plan below that
The rows accessed have reduced from 8550 to 1221 before we access the table...Thus filter "t1.owner='SYS'" is applied in between. Right?

another question is

Case 1 - do we retrieve a rowid from index for a given value, then retrieve required values from table for that rowid
Thus row at a time in both ... in loop
OR
Case 2 - we first fetch all rowids from index and then retrieve values from table one row at a time from the collection of rowids fetched?

Suppose Case 1 is what is happening then can we say, both the steps mentioned by IDS 2,3 in plan below are executed exactly equal number of times and the filter "t1.owner='SYS'" is applied at some later stage?
Of course in this case the values in ROWS stand misleading then

select * from t1,t2 where t1.id = t2.id and t1.object_type='VIEW' and t1.owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 26873579

-------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |  1221 |   233K|   915   (1)| 00:00:11 |
|*  1 |  HASH JOIN                   |      |  1221 |   233K|   915   (1)| 00:00:11 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| T1   |  1221 |   116K|   381   (1)| 00:00:05 |
|*  3 |    INDEX RANGE SCAN          | I11  |  8550 |       |    24   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | T2   |   161K|    15M|   533   (1)| 00:00:07 |
-------------------------------------------------------------------------------------

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

   1 - access("T1"."ID"="T2"."ID")
   2 - filter("T1"."OWNER"='SYS')
   3 - access("T1"."OBJECT_TYPE"='VIEW')


Thanks and Regards
OraPratap
Re: Understanding Execution Plan of a query [message #548754 is a reply to message #548750] Mon, 26 March 2012 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 67919
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Though I am unable to understand how filter can be applied to the rowids retrieved from index, we can see from the plan below that
The rows accessed have reduced from 8550 to 1221 before we access the table...Thus filter "t1.owner='SYS'" is applied in between. Right?


The filter "T1"."OWNER"='SYS' is applied during the access to the table by rowid (id 2): id 3 give a rowid, then access to the table is done to access the row then a test against owner is made, and so on for every rowid returned by id 3.

Regards
Michel




Re: Understanding Execution Plan of a query [message #548756 is a reply to message #548754] Mon, 26 March 2012 01:30 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Michel

Many Thanks for your reply

What I understand from your reply is
The filter is applied after "we retrieve data from index as well as table"
as follows(?)

say the data is as following
data in table T1

ID	ONWER	OBJECT_TYPE
1	SYS	VIEW
2	ABC	PROCEDURE
3	SYS	PROCEDURE
4	ABC	PACKAGE
5	ABC	VIEW

the data in index I1

ID	ROWID
1	AABBCCDA
2	AABBCCDB
3	AABBCCDC
4	AABBCCDD
5	AABBCCDE

Step 1
	access index I1 
	get rowid for ID = 1 i.e. AABBCCDA
	access table T1 with rowid, AABBCCDA	-- This step gets us (1,'SYS',VIEW")
	apply filter (owner = 'SYS')
	Return data 
Iterate Step 1
	This time for ID=2
Iterate Step 1
	This time for ID=3
....so on


Please confirm and advice

Also please suggest if index-to-table value retrieval is like loop as was mentioned in my ear;ier post or is like collection of rowids retrieved from index and then table?


Thanks and Regards
OraPratap
Re: Understanding Execution Plan of a query [message #548764 is a reply to message #548756] Mon, 26 March 2012 02:40 Go to previous messageGo to next message
Michel Cadot
Messages: 67919
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ The index is I11 in your case not I1, but it is the same (except that each value returns more than 1 rowid). The data are not returned to you in this case but used to build the hash table for the hash join.
2/ Rowids are retrieved per block, rows are retrieved row by row in a loop.

Regards
Michel
Re: Understanding Execution Plan of a query [message #548775 is a reply to message #548764] Mon, 26 March 2012 04:13 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Thanks for your reply

I am still not clear about Quote:

Rowids are retrieved per block, rows are retrieved row by row in a loop


Suppose a block has IDS as 1,1,2,2,3,4 and another block has rowids as 2,5,6,7
how the rowids will be retrived from index and then the data from the table T1?

Also does these rowid are stored in memory as a collection before values are retrieved from table row-by-row?

   loop	
		-- initiall i = 1 (i.e. ID = 1)
	access index I11
	get "all" rowids from block, in which for ID = i is stored
	--will it retrurn and use rowids for ID = 2,2,3 as well since it is in the same block
	access table T1 with -- this problem now as we have multiple rowids
	apply filter (owner = 'SYS')		** am I misunderstanding it?...will it apply filter row-by-row in loop
	Return data to hash table 
     i= i+1;
  end loop;



Thanks and Regards
OraPratap
Re: Understanding Execution Plan of a query [message #548778 is a reply to message #548775] Mon, 26 March 2012 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 67919
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
loop (on index structure)
  retrieve all rowid from current index block satifying the condition
  loop (on appropriate index block rowids)
    retrieve the row for one rowid, check if it satisfies the second condition
    if yes pass the row to the next operation
  end loop
end loop

Regards
Michel
Re: Understanding Execution Plan of a query [message #548784 is a reply to message #548778] Mon, 26 March 2012 04:46 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Michel

Many Thanks for your help and patience

loop (on index structure)
  retrieve all rowid from current index block satifying the condition
  loop (on appropriate index block rowids)
    retrieve the row for one rowid, check if it satisfies the second condition -- [b]Filter application here? e.g. owner = 'SYS'?[/b]
    if yes pass the row to the next operation
  end loop
end loop


This means we always access all index blocks, inspite of the access and filter critera as we first access index structure.
and
It may revisit a block if another value appears in the block already scanned or else it may have mechanism to flag the block so that it won't scan it twece
Am I right in concluding this?



Another related question is what is "rows" in the plan?
I have read that it is "number of rows" flowing through the step.
Now it is the count of rows flowing 'in' or 'out'?


Please consider the following example :
In both the cases the table T1 is full scanned;
In Case 2 - T1 has a filter (owner='SYS')
However to filter the data also first it would need to scan the whole data i.e. 120K then

1)why the 'rows' is showing 17527 in Case 2?

2) So in effect does the 'rows' indicate "result" or "outcome" of the current step?

Case 1
************************
select * from t1;

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   120K|    11M|   394   (2)| 00:00:05 |
|   1 |  TABLE ACCESS FULL| T1   |   120K|    11M|   394   (2)| 00:00:05 |
--------------------------------------------------------------------------

Case 2
************************

select * from t1 where owner='SYS';

Execution Plan
----------------------------------------------------------
Plan hash value: 838529891

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 17257 |  1651K|   393   (1)| 00:00:05 |
|*  1 |  TABLE ACCESS FULL| T1   | 17257 |  1651K|   393   (1)| 00:00:05 |
--------------------------------------------------------------------------

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

   1 - filter("OWNER"='SYS')


Thanks and Regards
OraPratap
Re: Understanding Execution Plan of a query [message #548787 is a reply to message #548784] Mon, 26 March 2012 04:58 Go to previous message
Michel Cadot
Messages: 67919
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
This means we always access all index blocks


No, In this case Oracle walks along its tree structure to find the first block with object_type='VIEW' then walks to the next blocks until it find an entry with another value.

Quote:
Another related question is what is "rows" in the plan?


Estimated rows returned by the operation.

Regards
Michel
Previous Topic: Query Tuning
Next Topic: Optimiser Cost Caclulation for nested loop join
Goto Forum:
  


Current Time: Tue Jul 27 11:23:23 CDT 2021