Home » RDBMS Server » Performance Tuning » Performance issue (10G/Unix)
Performance issue [message #311952] Mon, 07 April 2008 08:53 Go to next message
liamadherne
Messages: 40
Registered: March 2008
Location: Philadelphia
Member
Good Morning Everybody,

I have the below query which is running from past 12 hrs but does not produce results.


CREATE TABLE SALES
NOLOGGING
AS
SELECT PLACE,ORDER_ID,ORDER_TYPE,PRTYNO, NST_CRG
FROM
nester.dw_nester a, nester.t&d_nester b
WHERE
type = 'C1'
AND
Dispatch >='25-mar-2008'
AND
TO_CHAR(TO_DATE(NST_CRG,'hh24:mi:ss'),'hh24')
BETWEEN
'10' AND '17'
AND
(NESTER.to_dec(SUBSTR(CHARGING_IST,-4)) =b.ID
OR
(NESTER.to_dec(SUBSTR(CHARGING_LAST,-4)) =b.ID))




Explain Plan
==============



SQL> @?/rdbms/admin/utlxpls.sql


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
Plan hash value: 3487654736

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32094 | 2162K| 312K (1)| 01:02:32 |
| 1 | NESTED LOOPS | | 32094 | 2162K| 312K (1)| 01:02:32 |
| 2 | TABLE ACCESS BY INDEX ROWID| NESTER | 16059 | 784K| 297K (1)| 00:59:29 |
|* 3 | INDEX RANGE SCAN | NE_UNIQ | 16197 | | 281K (1)| 00:56:14 |
|* 4 | TABLE ACCESS FULL | ID | 2 | 38 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------

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

3 - access("ORDER_TYPE"='A1' AND "NST_CRG">=TO_DATE('2008-03-25 00:00:00', 'yyyy-mm-dd
hh24:mi:ss'))
filter("NST_CRG">=TO_DATE('2008-03-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND
TO_CHAR(TO_DATE("NST_CRG",'hh24:mi:ss'),'hh24')>='10' AND
TO_CHAR(TO_DATE("NST_CRG",'hh24:mi:ss'),'hh24')<='17')
4 - filter("NESTER"."TO_DEC"(SUBSTR("CHARGING_IST",-4))=TO_NUMBER("B"."ORDER_ID")
OR "NESTER"."TO_DEC"(SUBSTR("CHARGING_LAST",-4))=TO_NUMBER("B"."ORDER_ID"))

22 rows selected.



Please help in solving this mystry.

Thanks for your time.
Re: Performance issue [message #312070 is a reply to message #311952] Mon, 07 April 2008 22:16 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you had formatted your code and plan, it would have been easier to read and easier to give you an answer.

Please enclose code and plans in [code]..[/code] tags.

According to your SQL, the two tables are named dw_nester and t&d_nester, but according to the plan they are named NESTER and ID. Perhaps you are using views or synonyms, but this sort of thing does not help if you do not explain.

Your plan shows a Nested Loops join. NL means "For each A do B". In this case, "A" is rows from NESTER that match the access predicate:
    "ORDER_TYPE"='A1' 
AND "NST_CRG">=TO_DATE('2008-03-25 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
AND TO_CHAR(TO_DATE("NST_CRG",'hh24:mi:ss'),'hh24')>='10' 
AND TO_CHAR(TO_DATE("NST_CRG",'hh24:mi:ss'),'hh24')<='17')
Note that your SQL shows "C1", not "A1"; and the column names are "type" and "dispatch" rather than "ORDER_TYPE" and "NST_CRG". This also make it harder to help you.

The second part of the Nested Loops joins - or "B" - is a Full Table Scan of ID. This means that if we return 10,000 rows from the scan of NESTER, then we will fully scan the ID table 10,000 times.

Furthermore, the join predicate involves what appears to be a user-defined function TO_DEC(). This function will be run once for every row scanned in ID, and will occur each time ID is fully scanned.

Solution:
- Eliminate redundant scans of the ID table.
- Eliminate redundant calls to the TO_DEC() function

Rather than a NESTED LOOPS join, you should be using a HASH join. It's surprising Oracle did not choose this itself. Try logging in as the owner of these tables an gathering statistics with
DBMS_STATS.GATHER_TABLE_STATS(USER, 'NESTER');
DBMS_STATS.GATHER_TABLE_STATS(USER, 'ID');


If that doesn't help, add hints to the query:
SELECT /*+ ORDERED USE_HASH(a,b)*/ ...


Once you are performing a HASH join, you will scan ID once only and execute TO_DEC() once for each row in that table.

Ross Leishman
Re: Performance issue [message #312170 is a reply to message #311952] Tue, 08 April 2008 02:33 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Why you are using TO_DEC at all? Look at:

4 - filter("NESTER"."TO_DEC"(SUBSTR("CHARGING_IST",-4))=TO_NUMBER("B"."ORDER_ID")
OR "NESTER"."TO_DEC"(SUBSTR("CHARGING_LAST",-4))=TO_NUMBER("B"."ORDER_ID"))


ORDER_ID column is probably VARCHAR2 type, so TO_DEC is causing
an implicit convertion TO_NUMBER and suppresses possible index usage.

And try HASH_JOIN instead of NL, as Ross already wrote.

HTH.
Michael
Previous Topic: Query Tuning
Next Topic: Reg Hash Join in Performance tunning
Goto Forum:
  


Current Time: Sun Dec 11 04:24:39 CST 2016

Total time taken to generate the page: 0.09444 seconds