Problem SQL - cartesian join & LIOs !

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

   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.

--
http://www.freelists.org/webpage/oracle-l