Sql statement taking long time when executed in large database.

[mzahid]

Hello All,

This thread is related to the sql performance. When I execute the sql statement pasted below in a large database with TIMING ON in Oracle sqlplus statement takes 4.9 minutes to complete. However, when I execute the same sql statement in small Oracle instance sql statemetn completes in less then 10 seconds.

I need to know why my sql statement is taking long time in large database comparing with small instance? And, how can I improve response time of this sql in large database.

Please note that the sql statement is using Oracle views which are created using Oracle base tables. Therefore, adding an index is not an option here.

Thanks in advance for your help and time :-)

SELECT /*+ RULE */ (SUM((E.BYTES)/1024)), D.FILE_NAME, D.FILE_ID
FROM DBA_EXTENTS E, DBA_DATA_FILES D WHERE E.SEGMENT_NAME='T1'
AND E.OWNER='ZAHMO01' AND D.FILE_ID=E.FILE_ID GROUP BY D.FILE_ID,
D.FILE_NAME
/

[abhaysk]

plans and amount of data in BIG/SMALL DBs??????

try and read the sticky post (first one) of the forum ..

[hacketta1]

Try comparing a record of SYS.UET$ on each database. If the difference is very large then that may be your answer (DBA_EXTENTS uses UET$). Migrating to locally managed tablespaces may be helpful here.

[tamilselvan]

=========
SELECT /*+ RULE */ (SUM((E.BYTES)/1024)), D.FILE_NAME, D.FILE_ID
FROM DBA_EXTENTS E, DBA_DATA_FILES D WHERE E.SEGMENT_NAME='T1'
AND E.OWNER='ZAHMO01' AND D.FILE_ID=E.FILE_ID GROUP BY D.FILE_ID,
D.FILE_NAME
/
============
The optimizer will choose merge join for the some of oracle internal tables.

Whenever you join oracle views or internal tables, use ordered hint.
You will HASH JOIN in the plan which is faster than MERGE JOIN.

Try this:

SELECT /*+ CHOOSE ORDERED */ (SUM((E.BYTES)/1024)), D.FILE_NAME, D.FILE_ID
FROM DBA_EXTENTS E, DBA_DATA_FILES D WHERE E.SEGMENT_NAME='T1'
AND E.OWNER='ZAHMO01' AND D.FILE_ID=E.FILE_ID GROUP BY D.FILE_ID,
D.FILE_NAME
/

This above code works faster than the previous one.

Tamil

[abhaysk]

Originally posted by abhaysk
plans and amount of data in BIG/SMALL DBs??????

try and read the sticky post (first one) of the forum ..

Ohh my ... am sorry i didnt even look at what the SQL was for.. i only saw after Tamil's post that its gotta do with SYS tables...

My bad i didnt read the post properly ..

[DaPi]

Couldn't you use DBA_SEGMENTS instead of DBA_EXTENTS ? Should be fewer rows.

[mzahid]

I cannot use DBA_SEGMENTS because it does not have FILE_ID column.

[DaPi]

OOOPS!