Who is using your Undo space? - Improved Script

Ver este articulo en Español

Hi folks!
I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks

and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.

Then the script for Oracle 11g is as follows:

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value > 0
ORDER BY a.value DESC

If you want to run this script on versions 10g1 and 10g2, just replace the statistic# with 176; 216 if your database is 11gR1... or use the following version independent script!!! (Hope we don't change the statistic name).

set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND b.name = 'undo change vector size'
AND a.value > 0
ORDER BY a.value DESC

Read the popular 2008 article Who is using your UNDO space?

Interested on MySQL Performance? Read  Book Review - High Performance MySQL


Subscribe to Oracle Database Disected by Email


Bookmark this on Delicious

Latest storage Adventure

Ver este articulo en Español

Datamarts/Datawarehouse performance issues can be scaring events, taking into account the massive amount of data they handle; the tuning task is even more daunting if they're used for operational day-to-day decision making.

During the last week and days, I've been involved on a 15 Tb datamart with performance issues on dataloading, not querying. Tablespaces for indexes and data share filesystems, nowadays this is not a concern anymore, however I prefer to encourage customers to allocate independent storage for each type, that way they are able to discriminate the source of IO.

They have concurrent schedules for dataloading processes and parallelism degree for tables and indexes involved; a very good meassure to speed up data loading is to use /*+ APPEND */ hint on INSERT ... AS SELECT, which they're using also with PARALLEL. The parallelism has a multiplying factor and if you've experienced the downsides of it you may be suspecting when the wall is going to be hitted: at the storage level.

I generated a heat map for filesystems and focusing on write statistics found the correlation expected: very high average write times on filesystems starting at the time dataloading processes were scheduled... huge avg times around seconds!

After a quick chat with the Sysadmin and Storage guys, something very important arose: the striping factor for all those overheated filesystems was 2, said in other words: "storage you have, performance ... what's that?" That is a common mistake and results from working on almost isolated process, then is when I say: "Guys, you need to talk to each other, learn the jargon, the working of the "machines" the other teams deal with... end even the inner workings, why not?!"

After identified a set of objects that participated heavily on the filesystem overheating, brand new filesystems with striping factor of 8 were allocated and new tablespaces created to receive this objects. As expected, just the maintenance task of moving all tables/partitions/subpartitions took a breeze. Timings for uploading processes even on high load days improved dramatically... and all this for a subtle "configuration number" on the OS storage options.


Subscribe to Oracle Database Disected by Email

Bookmark this on Delicious

When your archive log destination is full

Have you experienced a sudden transaction blast, which fills your archive log destinations within minutes? Do you remember what your first reaction is? I'll try to help you:
a) Erase the archive logs and later crosscheck them
b) Move previous archive logs to free-up some space
c) Close your application and trigger an archive log backup

But, and this is an awareness-but: What if you're using Flashback recovery? What if this archive activity is the result of a very important process? think, that you may be in risk of losing a vital period of activity.

Well, you don't have to worry anymore: the archive log destinations provide the flexibility of dynamic change, therefore you may issue an ALTER SYSTEM and redirect the output generated by the archive writer process to another directory. The statement is really simple:

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST='your new filesystem' SCOPE=MEMORY;

The best part of all, you don't have to worry about operations on new archive log generated files or previous ones: the database keeps track of every file, saving the full path at the time of writing, therefore recovery and backup operations will have the right information to proceed successfully. You only have to worry about space availability.

I hope this post help you, please leave your comments and suggestions.


Undo Problems?: please read Who is using your UNDO space? Improved Script"
 


Subscribe to Oracle Database Disected by Email

2 minute guide for Statspack sample scheduling

Now that you have installed the Statspack tool, you need to start taking samples or snapshots. This is a task you may easily automate, and is recommended to do so, because snapshots are evenly and uniformly spaced.

You may schedule this task using cron, at or any OS or 3rd party scheduler, but I would suggest a better way: database jobs.

Advantages:
-Database Contained
-Don't breach security exposing users/passwords

The Statspack set of tools provides a script that automatically creates the job for you: spauto.sql. It's located at $ORACLE_HOME/rdbms/admin and you may easily customize the NEXT_DATE parameter for dbms_job.submit (below, red color) and adjust the time interval . You will need to change TRUNC rounding precision if you go from hours to minutes.

dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/48,'MI'), 'trunc(SYSDATE+1/48,''MI'')', TRUE, :instno);

Then you need to run the spauto script as the user perfstat or the Statspack owner you have:

SQL> conn perfstat
Password:

Connect
SQL> @?/rdbms/admin/spauto

You will get an output like this, showing the job number created by spauto and information regarding the next execution time. At this point, snapshot taking has been scheduled succesfuly.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:


     JOBNO
----------
        23


Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     10


Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:


       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
        23 23-MAY-08 17:52:00

How to stop taking snapshots

After you finish taking samples or if you ran out of space for the Statspack tablespace, you'll need to stop the snapshot job. This is very easy, as you may see next:

SQL> conn perfstat    -- the Statspack owner
Password:

Connected
SQL> select job, what from user_jobs;

       JOB                      WHAT
----------  ------------------------
         2
        23  statspack.snap(10);

SQL> exec rdbms_job.remove(23);

PL/SQL procedure successfully completed.

SQL> select job, what from user_jobs;

       JOB                      WHAT
----------  ------------------------
         2

I hope this tip is useful for you, as it has been very helpful for me.

Please leave your comments, your feedback is vital to improve this content



Subscribe to Oracle Database Disected by Email

Who is using your UNDO space?

Ver este articulo en Español

Sure you've faced this situation: a growing undo tablespace, that seems it could engulf your entire disk space... until finally stops demanding additional space, and within some minutes (or hours, depends on your UNDO_RETENTION setting) you start to see more and more free space in your tablespace. If you scratched your head wondering 'what happened?' or 'who the User did this?', this post may be helpful.

There are some views that show information related to undo activity:

* V$UNDOSTAT: histogram-like view that shows statistics for 10-minute intervals.
* V$TRANSACTION: present time view providing information on current transactions.
* V$SESSTAT: individual session statistics, which includes one for undo usage.

V$UNDOSTAT will provide a who did hint, recording the longest running query for that 10-interval, through the MAXQUERYID column which may be linked to V$SQL and use columns PARSING_USER_ID or PARSING_SCHEMA_NAME the get a grip on the suspect.

V$TRANSACTION linked with V$SESSION will show current used undo blocks for ongoing transactions. This query may help:

SELECT  a.sid, a.username, b.used_urec, b.used_ublk
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
ORDER BY b.used_ublk DESC

V$SESSTAT provides another view, a who uses the undo kind of view, but we must avoid to get lost in the maze of Oracle statistics and focus on just one: Undo change vector size, which will accumulate the bytes of undo used during the session lifetime. Following query is designed to pinpoint who is having a high undo activity.

SELECT a.sid, b.name, a.value
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic# = 176    <-- Which stands for undo change vector size
ORDER BY a.value DESC 

Good luck with your UNDO-eating monsters...

Nov 25th 2011: I have updated these scripts, please read Who is using your UNDO space? Improved Script"

You may be interested on Exadata related posts, if so then below is a list of them:

My steps to implement Exadata Hybrid Columnar Compression (EHCC)



Subscribe to Oracle Database Disected by Email