OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Performance issue (10G/Unix)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Performance issue [message #311952]

Mon, 07 April 2008 08:53

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.

Report message to a moderator

Re: Performance issue [message #312070 is a reply to message #311952]

Mon, 07 April 2008 22:16

rleishman
Messages: 3728
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

Report message to a moderator

Re: Performance issue [message #312170 is a reply to message #311952]

Tue, 08 April 2008 02:33

michael_bialik
Messages: 621
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

Report message to a moderator

Previous Topic:	Query Tuning
Next Topic:	Reg Hash Join in Performance tunning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 17:50:14 CST 2024