-
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.
-
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?
-
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
-
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
-
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.
-
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
-
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.
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|