Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Infinite looping in running SQL Query followed by increasing Oracle temporary space

Posted on 2009-04-20
13
Medium Priority
?
529 Views
Last Modified: 2013-12-18
I just don't know why the query below (launched by Crystal Report) takes so much temporary space and will never terminate. Even though the execution plan shows that it only needs 12300 bytes temporary space, (Previously it requires 37 TB temporary space which is really nonsense)
it has used around 35 gb temporary space.
SELECT 
 "V_COMMISSION"."SERVICE", "V_COMMISSION"."WORKS_REFERRED", "V_TASK"."DESCRIPTION", "V_PROJECT"."DESCRIPTION", 
 "V_CON_CLIENT"."NAME", "V_TIMECOST_ALL"."COMMISSION", "V_TIMECOST_ALL"."ACTIVITY", "V_TIMECOST_ALL"."PROJECT", 
 "V_TIMECOST_ALL"."PERIOD", "V_TIMECOST_ALL"."HOURS", "V_TIMECOST_ALL"."OTHOURS", "V_TIMECOST_ALL"."AMOUNT", 
 "V_TIMECOST_ALL"."DETAILS", "V_EMPLOYEE"."SURNAME", "V_EMPLOYEE"."GIVEN_NAMES", "V_TASK"."CHARGE_RATE" 
 FROM   
    "MANAGE"."V_TIMECOST_ALL" V_TIMECOST_ALL,
    "MANAGE"."V_COMMISSION" V_COMMISSION,
    "MANAGE"."V_TASK" V_TASK,
    "MANAGE"."V_PROJECT" V_PROJECT,
    "MANAGE"."V_EMPLOYEE" V_EMPLOYEE,
    "MANAGE"."V_CON_CLIENT" V_CON_CLIENT  
WHERE  ("V_TIMECOST_ALL"."PERIOD">=TO_DATE ('01-04-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS') 
AND "V_TIMECOST_ALL"."PERIOD"<TO_DATE ('21-04-2009 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) 
AND "V_TIMECOST_ALL"."PROJECT"=7000 AND "V_TIMECOST_ALL"."COMMISSION"=29 
ORDER BY "V_TIMECOST_ALL"."COMMISSION", "V_TIMECOST_ALL"."ACTIVITY", "V_TIMECOST_ALL"."PERIOD"

Open in new window

0
Comment
Question by:jjoz
  • 6
  • 5
  • 2
13 Comments
 
LVL 25

Accepted Solution

by:
lwadwell earned 1500 total points
ID: 24182433
Hi jjoz,

Did you post the full SQL?  The reason I ask is I see 6 tables in the FROM but I do not see them being joined in any way in the WHERE clause.  All I see are predicates on the V_TIMECOST_ALL table.  If there are no joins, then you will get a cartesian product on all of the other table and if they are not very small ... will use a LOT of TEMP space.


lwadwell
0
 
LVL 1

Author Comment

by:jjoz
ID: 24182466
Hi,
Thanks for the reply. You are right, V_TIMECOST_ALL is the only view that appears in the predicate. This signifies that there might be cartesian joins which are supposed to be performance killer.

However the execution plan shows a low cost. ....

SELECT STATEMENT, GOAL = ALL_ROWS        53889669    515            568    81    53460
 SORT ORDER BY    123000    53889669    515            568    81    53460
  HASH JOIN RIGHT OUTER        52656460    497            549    81    53460
   TABLE ACCESS FULL        52143    3    MANAGE    MAN_ATTRIB    3    5    40
   MERGE JOIN CARTESIAN        52088835    494            545    81    52812
    NESTED LOOPS OUTER        39992928    352            391    1    622
     NESTED LOOPS OUTER        39991878    352            391    1    618
      NESTED LOOPS OUTER        39990828    352            391    1    614
       NESTED LOOPS OUTER        39989778    352            391    1    610
        NESTED LOOPS OUTER        39988728    352            391    1    606
         NESTED LOOPS OUTER        39987678    352            391    1    602
          NESTED LOOPS OUTER        39986628    352            391    1    598
           NESTED LOOPS OUTER        39984728    352            391    1    593
            NESTED LOOPS OUTER        39976336    351            390    1    585
             NESTED LOOPS OUTER        39967945    350            389    1    577
              NESTED LOOPS OUTER        39966895    350            389    1    573
               NESTED LOOPS OUTER        39958503    349            388    1    565
                NESTED LOOPS OUTER        39950112    348            387    1    557
                 NESTED LOOPS OUTER        39941720    347            386    1    549
                  NESTED LOOPS OUTER        39933329    346            385    1    541
                   NESTED LOOPS OUTER        39924938    345            384    1    533
                    NESTED LOOPS OUTER        39916546    344            383    1    525
                     MERGE JOIN CARTESIAN        39908073    343            382    1    517
                      MERGE JOIN CARTESIAN        38671356    320            358    1    427
                       NESTED LOOPS        38537966    314            352    1    403
                        NESTED LOOPS        38536916    314            352    1    399
                         NESTED LOOPS        38528524    313            351    1    392
                          NESTED LOOPS        38520133    312            350    1    385
                           NESTED LOOPS        38511741    311            349    1    378
                            NESTED LOOPS        38503350    310            348    1    371
                             HASH JOIN        38494959    309            347    1    364
                              MERGE JOIN CARTESIAN        37867976    303            340    1    322
                               MERGE JOIN CARTESIAN        37835074    300            337    1    315
                                NESTED LOOPS        562404    5            6    1    98
                                 HASH JOIN        561354    5            6    1    94
                                  TABLE ACCESS BY INDEX ROWID        18446    1    MANAGE    MAN_PROJECT    1    7    406
                                   NESTED LOOPS        36889    3            3    4    304
                                    TABLE ACCESS BY INDEX ROWID        18443    2    MANAGE    MAN_ATTRIB    2    1    18
                                     INDEX RANGE SCAN        9121    1    MANAGE    PK_MANATTRIB_ATTRIBID    1    10    
                                    INDEX RANGE SCAN        5050    0    MANAGE    I_MANPROJECT_DISCIPLINEID    0    14    
                                  TABLE ACCESS BY INDEX ROWID        17133    2    MANAGE    MAN_ATTRIB    2    1    18
                                   INDEX RANGE SCAN        8521    1    MANAGE    PK_MANATTRIB_ATTRIBID    1    6    
                                 INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
                                BUFFER SORT        37834024    300            337    3    651
                                 VIEW        37272669    295    MANAGE    V_TSH_TIMECOST    332    3    651
                                  UNION-ALL                                
                                   TABLE ACCESS BY INDEX ROWID        37722    4    MANAGE    TSH_ACTIVITY    4    1    63
                                    NESTED LOOPS        12423409    98            111    1    105
                                     HASH JOIN        12274886    83            95    4    168
                                      MERGE JOIN CARTESIAN        1638875    15            17    2    60
                                       TABLE ACCESS BY INDEX ROWID        15803    2    MANAGE    MAN_TASK    2    1    15
                                        INDEX RANGE SCAN        8171    1    MANAGE    PK_MANTASK_PIDCOMMTASK    1    1    
                                       BUFFER SORT        1623072    13            15    4    60
                                        TABLE ACCESS FULL        1623072    13    MANAGE    RES_PERIOD    15    4    60
                                      VIEW        9053579    68    MANAGE    index$_join$_062    76    10755    129060
                                       HASH JOIN                                
                                        INDEX FAST FULL SCAN        2893491    29    MANAGE    PK_TIMESHEET_TSID    32    10755    129060
                                        INDEX FAST FULL SCAN        2964706    39    MANAGE    UQ_TIMESHEET_TS    42    10755    129060
                                     INDEX RANGE SCAN        10171    1    MANAGE    I_TSHACTIVITY_TIMESHEETID    1    11    
                                   TABLE ACCESS BY INDEX ROWID        38142    4    MANAGE    TSH_ACTIVITY    4    1    63
                                    NESTED LOOPS        12425053    98            111    1    105
                                     HASH JOIN        12274886    83            95    4    168
                                      MERGE JOIN CARTESIAN        1638875    15            17    2    60
                                       TABLE ACCESS BY INDEX ROWID        15803    2    MANAGE    MAN_TASK    2    1    15
                                        INDEX RANGE SCAN        8171    1    MANAGE    PK_MANTASK_PIDCOMMTASK    1    1    
                                       BUFFER SORT        1623072    13            15    4    60
                                        TABLE ACCESS FULL        1623072    13    MANAGE    RES_PERIOD    15    4    60
                                      VIEW        9053579    68    MANAGE    index$_join$_067    76    10755    129060
                                       HASH JOIN                                
                                        INDEX FAST FULL SCAN        2893491    29    MANAGE    PK_TIMESHEET_TSID    32    10755    129060
                                        INDEX FAST FULL SCAN        2964706    39    MANAGE    UQ_TIMESHEET_TS    42    10755    129060
                                     INDEX RANGE SCAN        10171    1    MANAGE    I_TSHACTIVITY_TIMESHEETID    1    11    
                                   TABLE ACCESS BY INDEX ROWID        37926    4    MANAGE    TSH_ACTIVITY    4    1    63
                                    NESTED LOOPS        12424208    98            111    1    105
                                     HASH JOIN        12274886    83            95    4    168
                                      MERGE JOIN CARTESIAN        1638875    15            17    2    60
                                       TABLE ACCESS BY INDEX ROWID        15803    2    MANAGE    MAN_TASK    2    1    15
                                        INDEX RANGE SCAN        8171    1    MANAGE    PK_MANTASK_PIDCOMMTASK    1    1    
                                       BUFFER SORT        1623072    13            15    4    60
                                        TABLE ACCESS FULL        1623072    13    MANAGE    RES_PERIOD    15    4    60
                                      VIEW        9053579    68    MANAGE    index$_join$_071    76    10755    129060
                                       HASH JOIN                                
                                        INDEX FAST FULL SCAN        2893491    29    MANAGE    PK_TIMESHEET_TSID    32    10755    129060
                                        INDEX FAST FULL SCAN        2964706    39    MANAGE    UQ_TIMESHEET_TS    42    10755    129060
                                     INDEX RANGE SCAN        10171    1    MANAGE    I_TSHACTIVITY_TIMESHEETID    1    11    
                               BUFFER SORT        595307    8            9    3    21
                                TABLE ACCESS FULL        32903    3    MANAGE    ADM_ATTRIB    3    3    21
                              TABLE ACCESS FULL        109700    6    MANAGE    ADM_EMPLOYEE    6    105    4410
                             TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    ADM_ATTRIB    1    1    7
                              INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMATTRIB_ATTRIBID    0    1    
                            TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    ADM_ATTRIB    1    1    7
                             INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMATTRIB_ATTRIBID    0    1    
                           TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    ADM_ATTRIB    1    1    7
                            INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMATTRIB_ATTRIBID    0    1    
                          TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    ADM_ATTRIB    1    1    7
                           INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMATTRIB_ATTRIBID    0    1    
                         TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    ADM_ATTRIB    1    1    7
                          INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMATTRIB_ATTRIBID    0    1    
                        INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
                       BUFFER SORT        38670306    320            358    82    1968
                        TABLE ACCESS FULL        133390    6    MANAGE    CON_CORPORATE    6    82    1968
                      BUFFER SORT        39774683    337            376    1046    94140
                       TABLE ACCESS FULL        1236718    23    MANAGE    MAN_COMMISSION    24    1046    94140
                     TABLE ACCESS BY INDEX ROWID        8473    1    MANAGE    MAN_ATTRIB    1    1    8
                      INDEX UNIQUE SCAN        1131    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
                    TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
                     INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
                   TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
                    INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
                  TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
                   INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
                 TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
                  INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
                TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
                 INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
               TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
                INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
              INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
             TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
              INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
            TABLE ACCESS BY INDEX ROWID        8391    1    MANAGE    MAN_ATTRIB    1    1    8
             INDEX UNIQUE SCAN        1050    0    MANAGE    PK_MANATTRIB_ATTRIBID    0    1    
           INDEX UNIQUE SCAN        1900    0    MANAGE    PK_CONCONTACT_ID    0    1    5
          INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
         INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
        INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
       INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
      INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
     INDEX UNIQUE SCAN        1050    0    MANAGE    PK_ADMEMPLOYEE_EMPID    0    1    4
    BUFFER SORT        52087785    494            545    20059    601770
     TABLE ACCESS FULL        12095907    142    MANAGE    MAN_TASK    154    20059    601770



Regards,

0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24182499
jjoz,

cartesian joins are time and temp space killers. I see 8 "MERGE JOIN CARTESIAN"'s in the explain plan ... I may have missed a few.

Oracle's optimizer is far from being a perfect beast.  It makes some bad assumptions from time to time.

lwadwell
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:jjoz
ID: 24182558
Thanks lwadwell.

The problem is we can't change the code -- it is generated automatically by Crystal Report. In generation situation we won't write sql in that complicated and inefficient format.   I am kinda hope by tuning server side rather than application/sql code side we can possibly make it faster? Like tuning pga_aggregate_target ... work_size_area_policy and etc.

Regards,
jjoz
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24182619
better create the "optimized" sql as (materialized) view in your oracle database, and let the crystal report just take that view as input.
problem solved.
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24182645
jjoz,

Not really ... sorry.  If it is doing cartesian joins, the number of rows are the number of rows.  Even if you change the server options - there sheer volume of data being generated will spill over into TEMP.

I think the definition of the report is wrong (my guess) - this could not possibly be the desired result.  I would question the report developer to confirm the requirement.  Did they really row being returned from V_TIMECOST_ALL ('a' rows) being joined to every row in V_COMMISSION ('b' rows), then that ('a'x'b' rows) being joined to V_TASK ('c' rows), then that ('a'x'b'x'c' rows) being joined ... and on.

lwadwell
0
 
LVL 1

Author Comment

by:jjoz
ID: 24182679
Hi lwadwell,

That crystal report was written many years ago, i.e. has been in use for many years. But I pretty much agree there must be something wrong with crystal report itself, I just can hardly figure out why it worked before for many years.

Regards,
jjoz
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24182713
jjoz,

There is possibly more going on in the report than we are aware of.  Why has it become a problem now - possibly because your tables are getting bigger ... the more rows in the tables, the more rows returned from the SQL.  When the tables were smaller, the query could work more efficiently.

lwadwell
0
 
LVL 1

Author Comment

by:jjoz
ID: 24182827
To All,

There were no problem at all when this report is executed using the Oracle 8i but since I've successfully migrated into Oracle 10g, this report somehow refused to execute :-(

Please see the following Error message generated.

from: http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_22122595.html#a18314495

ORA-01652: unable to extend temp segment by 2560 in tablespace TEMP
 
"Failed to retrieve data from the database. Details: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP. [Database Vendor Code: 1652]" 

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24182893
did you update the statistics on the tables and indexes?
0
 
LVL 1

Author Comment

by:jjoz
ID: 24183514
Yes, I'd done that today with no avail, though execution plan did have some changes.

Regards,
jjoz
0
 
LVL 25

Expert Comment

by:lwadwell
ID: 24189293
Would you have the explain plan from the 8i database (or a way to generate one)?
0
 
LVL 1

Author Comment

by:jjoz
ID: 24189806
lwadwell,

Thanks a lot for your assistance. The problem actually comes from Crystal Report which doesn't implement join operation properly. After we forced Crystal Report to generate the desired SQL, the problem went away immediately. Thanks again!

Regards,
jjoz
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses
Course of the Month11 days, 4 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question