Home » RDBMS Server » Performance Tuning » Need help
Need help [message #240006] Wed, 23 May 2007 17:44 Go to next message
rmoturi
Messages: 1
Registered: May 2007
Junior Member
Hi ,

I have a query to be optimized. The total cpu time is very high ,which is 79.78s. Can any one explain how i can bring down to lesser value. Any help would be appreciated.

The ebt table contains around 10000 records and the cpt table contains 18209835 rows.

SELECT COUNT(EB.EMP_INTID)
--INTO v_EmpPunchCount
FROM ebt EB
INNER JOIN cpt CP
ON (EB.BADG_INTID = CP.BADG_INTID)
WHERE EB.STRT_DT < CP.PUNCH_DT
AND EB.END_DT > CP.PUNCH_DT
AND CP.PUNCH_DT > trunc(SYSDATE) - 7
AND CP.PUNCH_DT < trunc(SYSDATE)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 79.77 80.27 83366 163959 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 79.78 80.29 83366 163959 0 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 81 (LMS_TRACE_OPER_RM)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE
2 FILTER
2 NESTED LOOPS
49019 TABLE ACCESS BY INDEX ROWID OBJ#(27926)
49019 INDEX RANGE SCAN OBJ#(27929) (object id 27929)
2 INDEX RANGE SCAN OBJ#(29194) (object id 29194)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
1 SORT (AGGREGATE)
2 FILTER
2 NESTED LOOPS
49019 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'EBT'
49019 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'EMPB_STRT_DT_END_DT_IX' (NON-UNIQUE)
2 INDEX GOAL: ANALYZED (RANGE SCAN) OF 'CLPU_UK1' (UNIQUE)
Re: Need help [message #240060 is a reply to message #240006] Thu, 24 May 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First read and apply Performance Tuning Sticky and related.

Regards
Michel
Re: Need help [message #240106 is a reply to message #240060] Thu, 24 May 2007 02:40 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
This article might help.

Ross Leishman
Re: Need help [message #240126 is a reply to message #240106] Thu, 24 May 2007 03:35 Go to previous messageGo to next message
sriram717
Messages: 48
Registered: February 2007
Location: UNITED KINGDOM
Member
Try to use the USE_HASH Hint

Validate the buffer gets for nested loops and Hash Joins.

Do you have the histograms collected for indexed columns?

Thanks
Re: Need help [message #241287 is a reply to message #240006] Tue, 29 May 2007 01:56 Go to previous message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Are your stats current?

You wrote that
Quote:
ebt table contains around 10000 records
, however tkprof shows 49019 rows retrieved from it via index.

I would like to know some data concerning both involved tables:
1. How many rows from CPT table correspond to
CP.PUNCH_DT > trunc(SYSDATE) - 7 AND CP.PUNCH_DT < trunc(SYSDATE)
?
2. How many distinct values exist for BADG_INTID column in both tables?
3. What indexes exists ( and what columns they contain)?


Michael

[Updated on: Tue, 29 May 2007 02:01]

Report message to a moderator

Previous Topic: Improving performance of a query using order by clause
Next Topic: HWM in table
Goto Forum:
  


Current Time: Fri Dec 02 16:58:48 CST 2016

Total time taken to generate the page: 0.43340 seconds