Problem SQL - cartesian join & LIOs !
- From: "Prem Khanna J" <premj@xxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: 15 Oct 2004 05:53:50 -0000
Hi all ,
it's oracle 9.2.0.4/Aix5.2L.
i have got a problem SQL whose plan is as below.
the response comes back immediately , but it overflows
with the number of records and goes on for more than
2 hours .i concluded that "merge join (cartesian)" is the
reason for this.This means that there is some proper join
missing (joining 11 tables).that is point to be looked into.
Restricting the records selected with more appropriate joins
is the only way to reduce the LIOs and make it get the right
resultset.
This is what i explained to my manager.But he doesn't accept it.
am i right ? kindly let me know friends.
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
147991 NESTED LOOPS
58085810 HASH JOIN
5534 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PAY_BALANCE_FEEDS_F'
4061275 HASH JOIN
62 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PAY_BALANCE_DIMENSIONS'
4061275 HASH JOIN
494 INDEX GOAL: ANALYZED (FULL SCAN) OF
'PAY_DEFINED_BALANCES_UK2' (UNIQUE)
3715981 MERGE JOIN (CARTESIAN)
8222 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PAY_RUN_RESULTS'
8304 NESTED LOOPS
82 NESTED LOOPS
82 NESTED LOOPS
82 NESTED LOOPS
1 NESTED LOOPS
1 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PAY_ALL_PAYROLLS_F'
1 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'PAY_PAYROLL_ACTIONS'
1 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PAY_PAYROLL_ACTIONS_N51' (NON-UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX
ROWID) OF 'PAY_ASSIGNMENT_ACTIONS'
82 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PAY_ASSIGNMENT_ACTIONS_N50' (NON-UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'PER_ALL_ASSIGNMENTS_F'
82 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PER_ASSIGNMENTS_F_PK' (UNIQUE)
82 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID)
OF 'PER_ALL_PEOPLE_F'
82 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PER_PEOPLE_F_PK' (UNIQUE)
8222 INDEX GOAL: ANALYZED (RANGE SCAN) OF
'PAY_RUN_RESULTS_N50' (NON-UNIQUE)
3715981 BUFFER (SORT)
452 TABLE ACCESS GOAL: ANALYZED (FULL) OF
'PAY_BALANCE_TYPES'
147991 TABLE ACCESS GOAL: ANALYZED (BY INDEX ROWID) OF
'PAY_RUN_RESULT_VALUES'
147991 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF
'PAY_RUN_RESULT_VALUES_PK' (UNIQUE)
Response Time Component Duration # Calls
---------------------------------- ------------------- ---------
CPU service 839.680000s 48.0% 9,870
SQL*Net message from client 622.846036s 35.6% 9,868
unaccounted-for 273.171419s 15.6%
latch free 7.544944s 0.4% 1,026
db file sequential read 5.001444s 0.3% 328
SQL*Net message to client 0.031981s 0.0% 9,868
db file scattered read 0.009618s 0.0% 1
SQL*Net more data from client 0.000035s 0.0% 1
------------------------------------------------- --------------
Total 1,748.285477s 100.0%
Cursor Rows ---------------- Response Time -- LIO Blocks
Action Processed Elapsed CPU Consistent
------- ------------- --------------- --------------- --------------
Fetch 147,991 1,117.345573 839.680000 174,432,911
------- ------------- --------------- --------------- --------------
Total 147,991 1,117.346185 839.680000 174,432,911
Per Row 1.0 0.007550 0.005674 1,178.7
Regards,
Prem.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Problem SQL - cartesian join & LIOs !
