This content has been marked as final. Show 10 replies
Eventhough, i have reduced from 50,000 to 10,000 and 1000reduced what?
Number of rows before COMMIT?
COMMIT inside LOOP causes ORA-01555; not fix it.
remove COMMIT from inside LOOP
visit above & do keyword search on ORA-01555
Reduced no of rows going to delete by changing "where id < 50000" to "where id < 1000"
I didnt removed any COMMIT from inside LOOP.
Which will delete about 50,000 rows daily as a batch processDid you commit often?
- don't commit frequently.
- increase "undo_retention" parameter
- reduce about running time (use execution plan to check your query) ... perhaps may create index. Or commit every 5000 rows
- Gather stats on table
Edited by: Surachart Opun (HunterX) on Jul 23, 2009 12:04 AM
Most folks, including me, can not debug code they can not see.
Go read ASKTOM
Read up on 'FETCH across COMMIT'. This might be your problem.
I second 'we cannot fix what we cannot see' response. Show the code, or at least a reproducible example if you are concerned about security.
It was resolved by creating indexes.
Now the below query encountered the same error,
SELECT DISTINCT PTP.SYNTAX_KEY, PTP.USER_DB_KEY FROM PLAN_TABLE_PREV PTP, SYNTAX_PLAN_LAST SPL WHERE PTP.INSTANCE_KEY = :instance_key AND SPL.INSTANCE_KEY = PTP.INSTANCE_KEY AND SPL.SYNTAX_KEY = PTP.SYNTAX_KEY AND NOT EXISTS (SELECT PTL.UNIQUE_ROW FROM PLAN_TABLE_LAST PTL
WHERE PTP.INSTANCE_KEY = PTP.INSTANCE_KEY AND
PTL.SYNTAX_KEY = PTP.SYNTAX_KEY AND
PTL.UNIQUE_ROW = PTP.UNIQUE_ROW);
Respective error message from alert_x.log :
ORA-01555 caused by SQL statement below (Query Duration=16088 sec, SCN: 0x0000.8a3d9c90):
Thu Jul 23 04:56:08 2009
Perhaps you may check execution plans again about your SQL Statement.
SQL> set autot trace explain
ORA-01555 caused by SQL statement below (Query Duration=16088 sec, SCN: 0x0000.8a3d9c90if you have many data and get a long time:
Can you change "undo_retention" initialization parameter ?
If you use SPFILE, you can change it anyway:
SQL> alter system set undo_retention=xxxxxxx;
By the way, you queried a long time... check about it;)
You may consider tuning undo retention:
Optimal undo retention=(Actual Undo Size) */* (DB Block Size x Undo Block Per Second)
Similarly you may check whether the size of your Undo tablespace is optimum or not:
Undo Size = Undo Retention * Db Block Size * Undo Block Per Second
Actual Undo Size:
SELECT SUM(a.bytes) "UNDO_SIZE"
FROM v$datafile a,
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;
Undo Blocks per Second:
DB Block Size
SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
WHERE name = 'db_block_size';
Thanks for query..
Can you please confirm the UNITS(ie., secs or mins ) for Optimal undo retention from below,
I have used Actual Undo Size "in KB". Bcoz of DB Block Size interms of KB.
Optimal undo retention = 14158848/(8192*454.135)=*3.80586169 ?* , My guess is 3.80586169 mins.
Optimal undo retention=(Actual Undo Size) / (DB Block Size x Undo Block Per Second).
For Undo Size = Undo Retention * Db Block Size * Undo Block Per Second,
I got Undo Size = (10800*8192*454.135)/1024/1024/1024 = 37.4195709 GB.
Code above is victim.
SELECT DISTINCT ptp.syntax_key, ptp.user_db_key FROM plan_table_prev ptp, syntax_plan_last spl WHERE ptp.instance_key = :instance_key AND spl.instance_key = ptp.instance_key AND spl.syntax_key = ptp.syntax_key AND NOT EXISTS (SELECT ptl.unique_row FROM plan_table_last ptl WHERE ptp.instance_key = ptp.instance_key AND ptl.syntax_key = ptp.syntax_key AND ptl.unique_row = ptp.unique_row);
ORA-01555 caused by other session doing DML & COMMIT against a table in FROM clause above