DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: TEMP tablespace eats 32GB of diskspace. still needs more!

  1. #1
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620

    Angry

    Hi all,

    Since yesterday, I am having wiered problem.

    I am running one script, which has INSERT statement. It consumes 32GB of my diskspace and still needs more and than fails with 'Unable to extend TS' error.

    Database is 8173 on Windows platform. TEMP T.S originally was LMT, TEMPORARY but than on Oracle's advise, I dropped it and recreated as DMT and PERMANENT but still gives me same problem.

    Foll. is the query on which it fails.

    SELECT pra.resource_assignment_id, pra.budget_version_id, pra.project_id,
    pra.task_id, pra.resource_list_member_id, pra.last_update_date,
    pra.last_updated_by, pra.creation_date, pra.created_by,
    pra.last_update_login, pra.unit_of_measure, pra.track_as_labor_flag
    FROM pa_budget_types pbt,
    gms_awards_all gaa,
    pa_budget_versions pbv,
    pa_resource_assignments pra
    WHERE pbt.budget_type_code = gaa.award_id
    AND pbt.award_flag = 'Y'
    AND pbt.budget_type_code = pbv.budget_type_code
    AND gaa.award_id BETWEEN :b1 AND :b1
    + :b3
    - 1
    AND pra.budget_version_id = pbv.budget_version_id

    Foll. is the explain plan.

    Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop

    SELECT STATEMENT Hint=CHOOSE 17 K 2700
    FILTER
    HASH JOIN 17 K 1 M 2700
    TABLE ACCESS FULL PA_BUDGET_TYPES 1 10 2
    MERGE JOIN 1 M 131 M 2693
    SORT JOIN 1 M 110 M 2455
    MERGE JOIN CARTESIAN 1 M 110 M 2455
    INDEX RANGE SCAN GMS_AWARDS_U1 1 6 1
    SORT JOIN 1 M 101 M 2454
    TABLE ACCESS FULL PA_RESOURCE_ASSIGNMENTS 1 M 101 M 2454
    SORT JOIN 39 K 344 K 238
    TABLE ACCESS FULL PA_BUDGET_VERSIONS 39 K 344 K 133


    What should I do to complete this INSERT statement without error.

    Thanks in Adv.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You have a cartesian product in your query that MERGE JOIN has to sort, but the cartesian product returns so many records that you run out of disk space for sort operation.

    Cartesian product - your table PA_RESOURCE_ASSIGNMENTS has no relation to any of other three tables in your WHERE clause!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by jmodic
    Cartesian product - your table PA_RESOURCE_ASSIGNMENTS has no relation to any of other three tables in your WHERE clause!
    Whoops, sorry, I owerlooked the last line in your WHERE clause, where you have PA_RESOURCE_ASSIGNMENTS related to PA_BUDGET_VERSIONS.

    Anyhow, are your statistics of the involved tables/indexes accurate? Can you provide us the number of rows in tables that satisfy the "standalone" conditions. For example, how many rows satisfy the following condition:

    AND gaa.award_id BETWEEN :b1 AND :b1+ :b3- 1

    According to optimizer, he expects to find only one row that will satisfy this condition, that is why it chooses CARTESIAN MERGE JOIN. Is optimizer assumption correct?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    This is Oracle supplied standard script. So, I don't know internally which values are passed to those variables.

    Is there any way to find that?

    Thanks for your time.

    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Given it a second thought, optimizer can't know it either because you are using bind variables. So I think your statistics for that table (actually for its index GMS_AWARDS_U1) indicate that there is only one row in that table. Is this true? And how many rows are realy in GMS_AWARDS_ALL that has non-null AWARD_ID?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Dec 2001
    Location
    USA
    Posts
    620
    Hi,

    There are 201 rows in GMS_AWARDS_ALL table and all have unique NON-NULL award_id.

    Thanks,
    Sam
    ------------------------
    To handle yourself, use your head. To handle others, use your heart

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width