Home » SQL & PL/SQL » SQL & PL/SQL » SQL execution plan (Oracle 9i)
SQL execution plan [message #360616] Fri, 21 November 2008 11:23 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
Hi all,

I have to two schema's A and B in the same database. Both the schema has tables DP_OBJECT_COMPONENT and DP_OBJECT_DISK_INSTANCE. These two tables has very same structure, index, and constrains in both the schema's A and B.

In schema A
DP_OBJECT_COMPONENT has row count 492599
DP_OBJECT_DISK_INSTANCE has row count 29564

In schema B
DP_OBJECT_COMPONENT has row count 1751305
DP_OBJECT_DISK_INSTANCE has row count 379460

I tried executing the following query in both the schema's

SELECT SUM(c.ComponentSize)/1024/1024 AS Used_Size_In_GB,
'DISK_INSTANCES' AS Origin
FROM DP_OBJECT_COMPONENT C, DP_OBJECT_DISK_INSTANCE DI
WHERE (C.ObjectName = DI.ObjectName)
AND (C.Category = DI.Category)



In schema A : -

Elapsed: 00:00:08.04

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'DP_OBJECT_COMPONENT'
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'DP_OBJECT_DISK_INSTANCE'
5 3 INDEX (RANGE SCAN) OF 'DP_IDX_COMPONENT__NAME_CTG' (
NON-UNIQUE)





Statistics
----------------------------------------------------------
134 recursive calls
0 db block gets
74107 consistent gets
3142 physical reads
0 redo size
494 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed


In schema B :-



Elapsed: 00:00:18.09

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5242 Card=1 Bytes=51
)

1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=5242 Card=34597 Bytes=1764447)
3 2 INDEX (FAST FULL SCAN) OF 'DP_IDX_DINSTANCE__NAME_CTG'
(NON-UNIQUE) (Cost=375 Card=379297 Bytes=8344534)

4 2 TABLE ACCESS (FULL) OF 'DP_OBJECT_COMPONENT' (Cost=279
2 Card=1750219 Bytes=50756351)





Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
20027 consistent gets
28180 physical reads
0 redo size
494 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


Can anyone help me understand the following :-

1) why the execution plan in schema B is different from execution plan in schema A?

2) A range scan of Index DP_IDX_COMPONENT__NAME_CTG on table DP_OBJECT_COMPONENT is done on schema A, where as this index is not getting used at all in Schema B causing a full table scan of DP_OBJECT_COMPONENT. ?

3) Is there any way to optimize this query? I have already created index for columns (objectname,category) on both the tables DP_OBJECT_COMPONENT and DP_OBJECT_DISK_INSTANCE.



Re: SQL execution plan [message #360632 is a reply to message #360616] Fri, 21 November 2008 13:12 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Are statistics up to date?

Sometimes a FULL TABLE SCAN is more efficient than an index. FULL TABLES SCANS are not always bad.

p.s. Your messages is very hard to read. Please use code tags.
Re: SQL execution plan [message #360638 is a reply to message #360616] Fri, 21 November 2008 13:41 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
Joy_division,

Thanks for your reply. My bad , I will make sure I use the code tag from next time.

I did not understand you question.

"Are statistics up to date?"

how to check this ?


Do you think the time taken (18 secs) for this query in schema B is reasonable to join two tables with row count 1,751,305 and 379460 or is there way to optimize this query.

[Updated on: Fri, 21 November 2008 14:25] by Moderator

Report message to a moderator

Re: SQL execution plan [message #360646 is a reply to message #360638] Fri, 21 November 2008 14:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use code tags when it is useful: in your first post, not here.
Remember when you use code tags your lines are not split so limit them to 80 characters.

Regards
Michel
Re: SQL execution plan [message #360650 is a reply to message #360638] Fri, 21 November 2008 15:16 Go to previous message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
prax_14 wrote on Fri, 21 November 2008 14:41

"Are statistics up to date?"

how to check this ?



Do a search for dbms_stats.gather_table_stats or dbms_stats.gather_schema_stats to get statistics, but to know if you have or not, check the LAST_ANALYZED column of the USER_TABLES and USER_INDEXES views.

Quote:

Do you think the time taken (18 secs) for this query in schema B is reasonable to join two tables with row count 1,751,305 and 379460 or is there way to optimize this query.



Too hard to tell. Maybe, maybe not. I know that doesn't really offer you much.
Previous Topic: System Event Trigger vs Database Event Trigger
Next Topic: SQL statement - think I need to use joins
Goto Forum:
  


Current Time: Tue Dec 06 06:34:21 CST 2016

Total time taken to generate the page: 0.20551 seconds