SQL execution plan [message #360616] |
Fri, 21 November 2008 11:23 |
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 |
joy_division
Messages: 4963 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 |
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 #360650 is a reply to message #360638] |
Fri, 21 November 2008 15:16 |
joy_division
Messages: 4963 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.
|
|
|