| Need help [message #240006] |
Wed, 23 May 2007 17:44  |
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 #240126 is a reply to message #240106] |
Thu, 24 May 2007 03:35  |
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 |
michael_bialik
Messages: 621
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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
