DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Snapshot too Old

  1. #1
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50

    Snapshot too Old

    I am running the query given below.
    SELECT /*+RULE */
    TRUNC (a.trans_dt) trans_dt, a.store_id,
    NVL (NVL (c.cust_id, d.cust_id), 0) cust_id,
    b.region_id || LPAD (a.prod_grp, 3, 0) prod_grp, a.amount
    FROM edeka.trans e,
    edeka.trans_detail a,
    edeka.d_segment b,
    edeka.d_card c,
    edeka.d_application d
    WHERE a.store_id = b.store_id
    AND a.card_id = c.card_id(+)
    AND a.card_id = d.antragsnummer(+)
    AND a.trans_id = e.trans_id
    ORDER BY 3, 4, 2

    This is a long running query. Usually this query ends up in 6-7 hours.
    But from past few runs this is ending up with Ora-1555 Error.

    There are no insert,update, delete running, only few other sql queries are running against the database along with the above query.

    My undo_retention setting is set to 900.

    Is it possible that this might occur because of some temporary segment conflict as my query is using lot of temporary space approx. 10 GB?
    An elephant is a mouse with an operating system.

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    ORA-1555 has nothing to do with temp segments.

    If you are 100% sure that no other DML (INSERT, UPDATE, DELETE) are happening during the query on any table involved in a query, then you are getting ORA-1555 because of the delayed blocks cleanout. You could probably avoid this by executing full table scan on each of the the tables involved in a query before you actualy run the query. Or you should at least perform FTS on those tables involved that have previously been batch loaded/modified.

    But the main question here is why your query is running so long. can you supply the expecution plan for this query, along with available indexes and number of rows in each table? Maybe we culd help you to cure the causes of the problem instead of eliminating the symptoms.
    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
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi,
    Also set you session sort_area_size=300MB
    alter session set sort_area_size=300000000; as ur doing a order by clause which would doing sorting in Memory rather than temp segment.
    You can also assign a large rollback segment to this sql before executing the SQL

    regards
    anandkl
    anandkl

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by anandkl
    Hi,
    Also set you session sort_area_size=300MB
    alter session set sort_area_size=300000000; as ur doing a order by clause which would doing sorting in Memory rather than temp segment.
    You can also assign a large rollback segment to this sql before executing the SQL

    regards
    anandkl
    I'd be cautious setting your sort_area_size that big and constantly monitor for swap activity.
    Jeff Hunter

  5. #5
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    I am sending 2 plans .
    First one will use RULE, Nested Loops and query is executed using this plan.

    Second one is with CBO,HASH JOINS and sencond plan is only to display the number of rows and sizes of the tables.
    Number of rows for Trans table is not shown in the plan but the num of rows in Trans is 26150319.

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

    SELECT STATEMENT Optimizer Mode=HINT: RULE
    NESTED LOOPS
    NESTED LOOPS OUTER
    NESTED LOOPS OUTER
    NESTED LOOPS
    TABLE ACCESS FULL TRANS
    TABLE ACCESS BY INDEX ROWID TRANS_DETAIL
    INDEX RANGE SCAN IX_ID_LN_SEQ_TRANS_DETAIL
    TABLE ACCESS BY INDEX ROWID D_APPLICATION
    INDEX UNIQUE SCAN PK_APPLICATION_APP_ID
    TABLE ACCESS BY INDEX ROWID D_CARD
    INDEX UNIQUE SCAN PK_D_CARD_CARD_ID
    TABLE ACCESS BY INDEX ROWID D_SEGMENT
    INDEX UNIQUE SCAN PK_D_SEGMENT_STORE_ID

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

    SELECT STATEMENT Optimizer Mode=CHOOSE 28 M 470743
    NESTED LOOPS 28 M 3G 470743
    HASH JOIN 28 M 3G 470743
    TABLE ACCESS FULL D_SEGMENT 4 K 120 K 5
    HASH JOIN OUTER 28 M 2G 380322
    HASH JOIN OUTER 28 M 2G 218942
    TABLE ACCESS FULL TRANS_DETAIL 28 M 1G 84999
    TABLE ACCESS FULL D_APPLICATION 379 K 5 M 397
    TABLE ACCESS FULL D_CARD 400 K 6 M 466
    INDEX UNIQUE SCAN PK_TRANS 1 13
    An elephant is a mouse with an operating system.

  6. #6
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    No doubt, your query runs for 6 hrs, for 2.6M records a full tablescan its very expensive.

    can u please give index details on TRANS table.
    If your query is not able to user indexes present on TRANS table, trying using HINTS i.e INDEX(TRANS (index_name) and check for the execution plan before executing the query.

    regards
    anandkl
    anandkl

  7. #7
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    That is why I am using RULE hint.
    So that it can have full scan of Trans Table and index scan of Trans_Detail table because in Trans_Detail Table number of records is arouns 200 M.

    Others table are used in outer joins so cannot be used as driving table in the nested loop so trans is the only table left for Full Scan.

    Now I am trying to run the query with the other plan i hope it will not give Snapshot too old error as i am doing full scan on most of the tables as recomended by you.
    An elephant is a mouse with an operating system.

  8. #8
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    But..this would take ages for you to get the output.

    Of these table which is the table with max no. of records


    regards
    anandkl
    anandkl

  9. #9
    Join Date
    Dec 2001
    Location
    Edmonton, Canada
    Posts
    50
    Trans_Detail is the largest table among all.

    I want to create a fact table using this query. The output if the query will be forwarded to a tool which will generate the actual fact table on it.
    An elephant is a mouse with an operating system.

  10. #10
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    In your from clause place the the small table in the right most side i.e the last table and the Biggest table in the left most table i.e the first table and check the execution plan

    regards
    anandkl
    anandkl

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