Home » RDBMS Server » Performance Tuning » bit confused about Execution Plan
bit confused about Execution Plan [message #206991] Sun, 03 December 2006 02:43 Go to next message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Hellow Everyone,
I am a bit confused about the topic which i red from Sybex Oracle 9i Performance & Tunning Book.
plz look at the SQL Statement,Execution Plan & Interpretation below.

Explain plan for
Select dist.distributor_id,
dist.city,
dist.state,
dist.Zipcode,
district.name,
emp.lastname from
distributer dist,district,employe emp where
emp.employee_id=dist.manager_id and
disttrict.district_id=dist.district_id;

Execution Plan

8 select Statement
7 Nested Loops
4 Nested Loops
1 Table Access Full Distributor
3 Table Access By Index Rowid Employee
2 Index Unique Scan Employee_id_pk
6 Table Access by Index Rowid District
5 Index Unique Scan District_id_pk

Explain Plan Interpretation

In General, Explain Plan output is interpretted by starting at the innermost operation in the Explain Plain. This operation is always executed first, Unless it is an index access.In this case the table operation directly above the index access would begin the execution Plan. From this Starting Point, you examine the explain plan's remaining operations, working your way up & out towards the left most operation in the plan.

Plz Clear the following doubts in the above Execution Plan:

plz explain how the statements will be executed in the above Sequence i.e;

Table Access Full Distributor will be executed first & the rest .


Thanks in Advance
Re: bit confused about Execution Plan [message #206994 is a reply to message #206991] Sun, 03 December 2006 05:42 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
8 select Statement
7 Nested Loops
4 Nested Loops
1 Table Access Full Distributor
3 Table Access By Index Rowid Employee
2 Index Unique Scan Employee_id_pk
6 Table Access by Index Rowid District
5 Index Unique Scan District_id_pk

#1 - For each row in Distributor table /* FULL table scan */ LOOP
# Get EMP_ID value
#2 - Access with that value Employee_id_pk index and get there a single ROWID
#3 - Use that ROWID to access a single row in Employee table
# Get DISTRICT_ID value
#5 - Access with that value District_id_pk and get there a single ROWID
#6 - Use that ROWID to access a single row in District table
# END LOOP

HTH


Re: bit confused about Execution Plan [message #207439 is a reply to message #206991] Tue, 05 December 2006 10:05 Go to previous message
it_me24
Messages: 167
Registered: March 2006
Location: delhi
Senior Member
Thanx a Lot for the wounderful Clearification
Previous Topic: Access to the database is very slow.
Next Topic: PGA_AGGREGATE_TARGET & SORT_AREA_SIZE
Goto Forum:
  


Current Time: Sun Dec 11 02:43:51 CST 2016

Total time taken to generate the page: 0.06853 seconds