Performance Tuning :: ORA-01555 - Increase Undo Size Or Retention?

Apr 22, 2013

I have got the following error yesterday

ORA-01555 caused by SQL statement below (SQL ID: fdxcyoin67ty8t, Query Duration=380128 sec, SCN: 0x0229.ff00afd0):

following are the existing settings

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 96000
undo_tablespace string undo

[code]....

following are the details from v$undostat

select begin_time, end_time, undotsn, undoblks, maxquerylen, maxqueryid, activeblks, unexpiredblks, expiredblks, tuned_undoretention from v$undostat
where trunc(begin_time)=trunc(sysdate)-1 order by begin_time;
BEGIN_TIME END_TIME UNDOTSN UNDOBLKS MAXQUERYLEN MAXQUERYID ACTIVEBLKS UNEXPIREDBLKS EXPIREDBLKS TUNED_UNDORETENTION
-------------- -------------- ---------- ---------- ----------- ------------- ---------- ------------- ----------- -------------------
21-04-13 00:08 21-04-13 00:18 1 12733 378446 duqnawh32hp4u 91152 7068448 225440 345600
21-04-13 00:18 21-04-13 00:28 1 8951 379047 duqnawh32hp4u 99344 7072800 225440 345600
21-04-13 00:28 21-04-13 00:38 1 14073 379650 duqnawh32hp4u 90128 7075872 234656 345600

[code]....

Following are the details in AWR report (00:00 til 01:00 of 21-Apr-2013) .... not thet the error was produced at 00:42

Undo Segment Summary DB/Inst: DBCPY/dbcpy01 Snaps: 18853-18854
-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
-> STO - Snapshot Too Old count, OOS - Out of Space count
-> Undo segment block stats:
-> uS - unexpired Stolen, your - unexpired Released, uU - unexpired reUsed

[code]....

Undo Advisor information taken 'now' is as following

SQL> select dbms_undo_adv.longest_query(sysdate-2,sysdate) from dual;
DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
379650
SQL> select dbms_undo_adv.required_retention from dual;

[code]....

In above situation what should be my first choice (assuming increasing space is not an issue) - increase undo tablespace or increase undo retention?

If latter is the choice then what should be the value? Because as I understand present 96000 value is taken as lower limit and because of auto tuning the actual value (TUNED_UNDORETENTION) being used was 345600 In that case shall I set it to something > max(maxquerylen) i.e 379,650 + X?Or I shall increase the undo tablespace size?

From Undo Advisor output it looks to me that even if I increase the undo retention to 379650 current undo size will be able to support it (may be at the expense of DMLs)Is that right?

View 13 Replies


ADVERTISEMENT

Performance Tuning :: ORA-01555 Required Old Image Is Not In Undo

Sep 22, 2011

I am getting the below error in alert log file,when my application calling a procedure.

ORA-01555 caused by SQL statement below (Query Duration=1576 sec, SCN: 0x09a2.5dda3165):
Fri Sep 16 16:33:40 2011
UPDATE SSPT_NETWORK_DETAILS SET INCLUDE_OFFERS = 'Yes' WHERE SESS_ID = SESS_ID

There is no ROLLBACK statement in my procedure. As per my understanding, the ORA-1555 error will occur,

1. The required old image is not in the undo,when we rollback the trasaction.
2. the select query may face this error because of delayed block cleanout concept.

But I don't know why this update statement causing this 1555 error?

View 6 Replies View Related

Performance Tuning :: Increase Few Of Columns Size In The Table

Feb 14, 2013

We have a table emp_details with 23772889 records. Our requirement is to increase few of the columns size in the table emp_details. We are following the below alter statement which is taking around 2 hours of time.

ALTER TABLE emp_details
MODIFY
(
address char(90)
,department char(30)
)
/

Is there any way to improve the above query performance?

View 2 Replies View Related

Increase Undo_retention Value Or Undo Tablespace Size?

Aug 18, 2010

When i takeind export i got error like this..

ORA-39125: Worker unexpected fatal error in KUPW$WORKER.GET_TABLE_DATA_OBJECTS while calling DBMS_METADATA.FETCH_XML_CLOB [TABLE_DATA:"POS"."TBK_POS_FACT":"KROATL200404"]

ORA-01555: snapshot too old: rollback segment number 1 with name "_SYSSMU1$" too small"

from this can we increase the undo_retention value or undo tablespace size?

View 2 Replies View Related

Undo Retention And DB-flashback Retention Target Parameters?

Mar 13, 2011

The undo_retention is used for read consistency, to avoid snapshot too old.Flashback database is using files in the db_recovery_file_dest.But whether undo_retention has any influence on Flashback drop; Flashback table or Flashback query?Let's say we have set undo_retention = 3600 = 1 hour.And

db_flashback_retention_target = 1440 = 24 hours.

Will it work Flashback drop; Flashback table; Flashback query to get 12 hours back?Additional question. Why Oracle sets undo_retention in seconds and db_flashback_retention_target in minutes?To use the same measure would be more user-friendly to DBAs .

View 2 Replies View Related

Server Administration :: Sizing Undo Tablespace And Setting Undo-retention?

Jan 30, 2004

regarding sizing undo tablespace and undo_retention parameter.we have to implement the database in production system with 40 users but how much space should be allocated to undo tablespace is there any propotions related to virtual memory and the
parameter.i have gone thru oracle doc's and some related sites.its an ERP aplications that contains 20 modules .I am an new one to this dba level

View 8 Replies View Related

Performance Tuning :: ORA-01555 - No Unexpired Or Expired Blocks Were Over Written?

May 10, 2011

I have encountered ORA-01555 and trying to find what caused the issue.

#UNDO parameters
undo_management AUTO
undo_retention 0
undo_tablespace UNDOTBS1
set pagesize 25
set linesize 120
select inst_id,tuned_undoretention,to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,

[code]...

at the end you can see there is one occurance of ORA-555, but no Unexpired or expired blocks were over written .why Oracle didnt try to use them ?

View 10 Replies View Related

Performance Tuning :: One Job Is Hanging After SGA Increase?

Mar 11, 2013

Additional Information

Step 1: Increased Physical Memory on one Node from 32 G to 48 G.
Step 1 Impact : DB was running same as before
Step 2 : Increased SGA from 12 G to 15 G.
Step2 Impact : DB was running same as before for 1 day next day one reporting job was hanging.
Step 3 : Increased DB_CACHE_SIZE from 5G to 7G.
Step 3 Impact : Over all CPU Utilization was high and no effect on reporting job.
Step 4 : Decreased DB_CACHE_SIZE from 7 G to 5 G.
Step 4 Impact : CPU Utilization came down little bit but no effect on reporting job.

Now our main concern is why CPU Utilization is going high. Because same thing we did last time and we got positive results.

View 15 Replies View Related

Performance Tuning :: Undo Space?

Jan 15, 2012

Query to find out who is eating up most of the undo space and what query he is running..

View 3 Replies View Related

Performance Tuning :: Restart Database After Increase Db_cache_size?

Aug 23, 2012

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1152M
sga_target big integer 0

[code]....

in scenario above, the database do not using ASMM, and spfile If I wan to increase db_cache_size parameter, do i need to rebounce instance?

View 6 Replies View Related

Performance Tuning :: How To Increase Data Retrieval / Insertion Speed

Oct 24, 2013

How To Increase Data Retrieval / Insertion Speed my data base has more than 0.5 million records Forms Some Time Respond Very Slow .

View 8 Replies View Related

Performance Tuning :: Undo Tablespace Full / Which Is Rectified But Now Having Big WAIT

Mar 7, 2012

we have a situation where both undo tablespaces were almost filled i.e UNDOTBS1 99% and UNDOTBS2 100% filled so i add data files to it and then i found a lot of blocking session and was just killing them through EM then i stop my front end listener and also down the service, now i don't have any blocking session but on EM a big WAIT is coming. alert log shows nothing serious, it was showing deadlock but now it is over as well.

View 8 Replies View Related

Performance Tuning :: SGA-MAX-SIZE To More Than 2G?

Aug 10, 2011

I am running Oracle 10.2.0.1.0 on MS Windows 2003 server 64-bit with 16G RAM.

Here is the findings for my Oracle database.

SQL> select * * from v$sgainfo;
NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1293560 No
Redo Buffers 7094272 No
Buffer Cache Size 830472192 Yes

[code]...

I find that the SGA component "Buffer Cache" is decreasing from the start "1.8G" and down to now 0.8G. On the other hand, the component "Shared Pool" is increasing from the start 0.3G to now 1.2G. I noticed that there are 100 operations of shrinking of "Buffer cache" and growth of "Shared Pool" in Oracle every day.Is it a indicator that I should raise up the SGA_MAX_SIZE?

I tried to increase the SGA_MAX_SIZE to 4G. But I cannot start the Oracle afterward.Is it a limitation of MS Windows(OS) or Oracle?I set the SGA_MAX_SIZE to 3G. This time, I can startup Oracle.What is the optimum/maximum I can set to SGA_MAX_SIZE?Is there any adverse effect/concern when setting the SGA_MAX_SIZE more than 2G?

View 6 Replies View Related

Performance Tuning :: Redo Size?

Jun 18, 2012

In one of our envirnoment i could see the redo size is high.Trying to understand why this is more

View 18 Replies View Related

Performance Tuning :: Table Occupying Max Size

May 9, 2011

Objective : To find solution to archieve data from 2 big tables which is occupying maximum size in the data base. With current data (From Jan 2005 to Sept 2011) it has records as mentioned below:

transaction - 41687927
trnansaction_dtl - 83945934

We need to load data and run monthly batches from October 2011 to current month which will increase this space.

1. Issue is there will not be having so much space.

2. Maintenance of such table is diffcult now.Also there is huge impact on performance. Can we think of partitioning the table base on date aswe query 1st table based on certain date range?

3. Most of reports use this table and creating performances issues

View 30 Replies View Related

Performance Tuning :: Redo Log File Size

Apr 6, 2011

The REDO log file size is important DB performance issues when DB is run archivelog mode.If DB run noarchivelog mode, REDO log file size not impact to DB performance.

View 3 Replies View Related

Performance Tuning :: Optimal REDO Log File Size

Feb 10, 2011

I would like to make a change on the live system!I have read a book and found a information about REDO log file size is impact on DB performance.My DB current log file size is 100 MB. But, Oracle 10g's Redo Logfile Sizing Advisor offer the optimal log file size is 1845 MB.What REDO log file size is best for my Oracle database?

#Optimal log file size:
select optimal_logfile_size
from v$instance_recovery
----------------------------
OPTIMAL_LOGFILE_SIZE
1842
[code]....

View 9 Replies View Related

Performance Tuning :: Tablespace With Different Block Size Inside Same Database?

Nov 25, 2011

All the analysis till now on our system proves that our system is clearly I/O bound and db sequential read is the biggest culprit.

We have even identified the index which is being affected by sequential read. I am thinking of creating a new tablespace with 32K blocksize (currently all table spaces are 8k) and migrate this index to the new space. That way, Oracle will have to do less number of reads to get the required data.

But is there anything wrong in having just one tablespace with a differnt block size? Or is there anything that I have to be watchful about while doing it?

View 14 Replies View Related

Performance Tuning :: Correct Method To Determine Table Actual Size

Aug 9, 2012

Which is the correct method to calculate actual data size in a table? becaue when I serach in google, I saw the below line.

"Oracle thumb rule says (actual space required for a table + 30 % space) will calculate the original space requirement for a table."

Method 1:

actual space = num_rows*avg_row_len

Method 2:

actual space = (Num of rows in a table) * (Avg_row_len) + ((Num of rows in a table) * (Avg_row_len)* 0.3)

View 8 Replies View Related

How To Increase Table Size

Oct 14, 2012

I have table and it's size is full when i'm inserting records , records are not inserting , How can i increase table size

View 8 Replies View Related

Undo Tablespace Size

Jun 6, 2012

At a time my 20 GB undo tablespace was full. So i increased the tablespace size upto 48 GB. Then i saw 45 GB was used. Then i changed undo_retention=60. After that am seeing that 48 GB is full.

1) why it's happened?
2) Here what is the effect of undo_retention=60
3) How to resolve?.

View 15 Replies View Related

Server Administration :: How To Know DB Size Increase Per Day

Jan 31, 2011

How to know DB size increase per hour or day on the Oracle?

View 3 Replies View Related

Server Administration :: Unable To Increase Size Of Sga

Feb 2, 2012

I am trying to increase the size of sga or you can say that i want to make my sga in automatic memory management...Following is the steps i am trying

SQL> show parameter sga_max_size;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 96M
SQL>

after that i am trying to increase the size

SQL> alter system set sga_max_size = 200m;
alter system set sga_max_size = 200m
*
ERROR at line 1: ORA-02095: specified initialization parameter cannot be modified

View 1 Replies View Related

Server Administration :: Increase Size Of Database To 50 GB

Jan 31, 2013

I have Oracle 11gR2 running on windows xp machine. Windows xp has total size of 150 GB and free space of 95 GB.

I checked the size of the database that I created. It showed the total size of the database as 2 GB and used space as 2 GB. If I want to increase the total size of the database to 50 GB, what should i do? Now which is the disk space size? Windows or Oracle?

View 4 Replies View Related

PL/SQL :: Increase Buffer Size Of Dbms_output.put_line

Feb 28, 2013

I am executing a plsql procedure and trying to increase buffer size to display all characters, procedure is given below:

create or replace procedure prc_p(prm_t1 in VARCHAR2,
prm_t2      in VARCHAR2,
prm_tab           in varchar2
)
AUTHID CURRENT_USER
as
str_sql   VARCHAR2 (4000);
[code]..........  

View 1 Replies View Related

Server Administration :: How To Undo Tablespace Size

Feb 7, 2011

As the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?

As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?

In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table.

As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?

SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space

[code]....

View 12 Replies View Related

Size Of Undo Tablespace Datafile Different From Same Files In OS

Jan 22, 2013

why total size for undotbs1 is different from the acutal data file size in Operating system.

select tablespace_name, sum(bytes/1024/1024) from dba_data_files
where tablespace_name like 'UNDO%'
group by tablespace_name;

tablespacename total size
UNDOTBS1                      2000
UNDOTBS2     7284
[code]....

View 7 Replies View Related

TUNED_UNDORETENTION Undo Tablespace Is Not Fixed Size

Apr 14, 2013

rdbms 11gr2 undo tablespace is not fixed size. and the undo guarantee is not set.should we set undo_retention to

select max(tuned_undoretention) from v$undostat ; to avoid 1555 error?

View 22 Replies View Related

Server Administration :: Sudden Increase In Archive Log Size

Mar 26, 2010

We had a database (DB A)that is having version 9.2.0.6.0.This DB is having multiple tables and volume of 6 million in individual tables.Another database is also 9.2.0.6.0 (DB B), this DB has Mviews pointing to DB A. Mviews are refreshed in every 15 mins, with fast refresh option in 90% cases and remaining having complete refresh.

Last weekend we have migrated DB 2 to version 10.2.0.4.0 - 64bi and on another server.After version upgrade and DB migration complete refresh was done once for all mviews.

Now DB A is generating huge amount of archive log and also it's UNDO space is getting fully consumed causing performance issue and DB hang.what has gone wrong and what we can do to improve response of DB A and also to reduce size of Archive log ?

View 3 Replies View Related

Server Administration :: SGA SIZE (Increase Shared Pool)

Jun 4, 2013

I want to increase my shared pool. my SGA values are as under below.

SELECT NAME,SUM(VALUE)/1024/1024/1024 gb FROM V$SGA
GROUP BY NAME;

Database Buffer 4.8125
Redo Buffers 0.0782623291015625
Variable Size 19.0000001564622
Fixed Size 0.00208648294210434

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved