Being a regular visitor to http://www.jlcomp.demon.co.uk, today, I have came across a good explanation/article by Mr. Jonathan Lewis, about measuring/gussing query performance using tkprof output.
http://www.jlcomp.demon.co.uk/tkprof_01.html
Happy reading,
Jaffar
4.23.2006
4.01.2006
Auto Undo Management Pefromance Degradation
Auto Undo Management + Fast Ramp-up [Oracle 10g Rel.2, a new feature]
The Auto Undo Management feature brings 10 undo segments online during the database startup,
on most of the SYSTEMS.
Created Undo Segment _SYSSMU1$
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
and the strategy with Automatic Undo is to have no more than one transaction per undo segment if possible,
therefore, Oracle keeps creating new ones if there is space in the undo tablespace.
There is performance degradation or sometimes database goes in hung stage when undo segments comes online, this
can be mostly seen on highly oltp database systems. I have experienced the problem on a databse (9205) which is
extremly oltp and number of transaction per second are 400+. Refer to metalink note 301432.1.
Setting on instance level 'event=10511 trace name context forever, level 2', would prevent
going undo segment offline.
But, the problem is, when the database shutdown and startup again, it again comes up with 10 undo segmnets online
and repeat the same functionality, i.e. bringing more undo segments online upon requirements.
Oracle 10g Rel.2 Comes up with a solution for the above discussed undo segments issues. Fast Ramp-Up
In previous versions, when the instance was restarted or when you switched undo tablespaces,
the ramp-up time for the system to grow the number of online undo segments could be several minutes.
Generally, this time delay was unacceptable.
After an instance startup or a switch of undo tablespaces in Oracle Database 10g, the database decides how many
undo segments to place online, based on existing data stored in AWR.
Conclusion:
I have not tested the Fast Ramp-up feature. However, waiting for the opportunity to upgrade our
database to Oracle 10g Rel.2 to test the Sast Ramp-up feature.
References :
Metalink Note : 301432.1 Performance Slowdown During Heavy Undo Segment Onlining
Metalink Note : 266159.1 SMON: ABOUT TO RECOVER UNDO SEGMENT %s messages in alert log
The Auto Undo Management feature brings 10 undo segments online during the database startup,
on most of the SYSTEMS.
Created Undo Segment _SYSSMU1$
Created Undo Segment _SYSSMU2$
Created Undo Segment _SYSSMU3$
Created Undo Segment _SYSSMU4$
Created Undo Segment _SYSSMU5$
Created Undo Segment _SYSSMU6$
Created Undo Segment _SYSSMU7$
Created Undo Segment _SYSSMU8$
Created Undo Segment _SYSSMU9$
Created Undo Segment _SYSSMU10$
and the strategy with Automatic Undo is to have no more than one transaction per undo segment if possible,
therefore, Oracle keeps creating new ones if there is space in the undo tablespace.
There is performance degradation or sometimes database goes in hung stage when undo segments comes online, this
can be mostly seen on highly oltp database systems. I have experienced the problem on a databse (9205) which is
extremly oltp and number of transaction per second are 400+. Refer to metalink note 301432.1.
Setting on instance level 'event=10511 trace name context forever, level 2', would prevent
going undo segment offline.
But, the problem is, when the database shutdown and startup again, it again comes up with 10 undo segmnets online
and repeat the same functionality, i.e. bringing more undo segments online upon requirements.
Oracle 10g Rel.2 Comes up with a solution for the above discussed undo segments issues. Fast Ramp-Up
In previous versions, when the instance was restarted or when you switched undo tablespaces,
the ramp-up time for the system to grow the number of online undo segments could be several minutes.
Generally, this time delay was unacceptable.
After an instance startup or a switch of undo tablespaces in Oracle Database 10g, the database decides how many
undo segments to place online, based on existing data stored in AWR.
Conclusion:
I have not tested the Fast Ramp-up feature. However, waiting for the opportunity to upgrade our
database to Oracle 10g Rel.2 to test the Sast Ramp-up feature.
References :
Metalink Note : 301432.1 Performance Slowdown During Heavy Undo Segment Onlining
Metalink Note : 266159.1 SMON: ABOUT TO RECOVER UNDO SEGMENT %s messages in alert log
Subscribe to:
Posts (Atom)