Month: January 2013

Real-Time database utilities grouped into a single script

In most of my previous posts I provided some perl scripts used to collect real-time information from the database based on cumulative views.

Those cumulative views provide a lot of useful information but are useless when real-time information is needed.

So, the idea of those utilities is more or less to take a snapshot each second (default interval) of the cumulative views and compute the differences with the previous snapshot to get real-time information.

I aggregated those perl scripts into a single one (real_time.pl) (Click on the link, and then on the view source button and then copy/paste the source code or download it from my shared directory here) :

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • This script is oracle RAC aware: you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.
  • The script has been tested on Linux, Unix and Windows.

Let’s have a look to the main help:

 ./real_time.pl -help

Usage: ./real_time.pl [-interval] [-count] [-type] [-help]
 Default Interval : 1 second.
 Default Count    : Unlimited

  Parameter      Value                        Comment
  ---------      -------                      -------
  -type          sysstat                      Real-Time snaps extracted from gv$sysstat
                 system_event                 Real-Time snaps extracted from gv$system_event
                 event_histogram              Real-Time snaps extracted from gv$event_histogram
                 sgastat                      Real-Time snaps extracted from gv$sgastat
                 enqueue_statistics           Real-Time snaps extracted from gv$enqueue_statistics
                 librarycache                 Real-Time snaps extracted from gv$librarycache
                 segments_stats               Real-Time snaps extracted from gv$segstat
                 sess_event                   Real-Time snaps extracted from gv$session_event and gv$session
                 sess_stat                    Real-Time snaps extracted from gv$sesstat and gv$session

  -help          Print the main help or related to a type (if not empty)                                                                                

  Description:
  -----------
  Utility used to display real time informations based on cumulative views
  It basically takes a snapshot each second (default interval) of the cumulative view and computes the differences with the previous snapshot
  It is oracle RAC aware: you can work on all the instances, a subset or the local one
  You have to set oraenv on one instance of the database you want to diagnose first
  You can choose the number of snapshots to display and the time to wait between snapshots

Example: ./real_time.pl -type=sysstat -help
Example: ./real_time.pl -type=system_event -help

So, as you can see it allows to deal with some type of snapshots, that is to say:

sysstat                  Real-Time snaps extracted from gv$sysstat
system_event             Real-Time snaps extracted from gv$system_event
event_histogram          Real-Time snaps extracted from gv$event_histogram
sgastat                  Real-Time snaps extracted from gv$sgastat
enqueue_statistics       Real-Time snaps extracted from gv$enqueue_statistics
librarycache             Real-Time snaps extracted from gv$librarycache
segments_stats           Real-Time snaps extracted from gv$segstat
sess_event               Real-Time snaps extracted from gv$session_event and gv$session
sess_stat                Real-Time snaps extracted from gv$sesstat and gv$session

Each type has its own help associated.

To get more help about a particular type, just call the help with the associated type:

./real_time.pl -type=sysstat -help

Usage: ./real_time.pl -type=sysstat [-interval] [-count] [-inst] [-top] [-statname] [-help]
 Default Interval : 1 second.
 Default Count    : Unlimited

  Parameter         Comment                                                      Default
  ---------         -------                                                      -------
  -INST=            ALL - Show all Instance(s)                                   ALL
                    CURRENT - Show Current Instance
                    INSTANCE_NAME,... - choose Instance(s) to display

  -TOP=             Number of rows to display                                    10
  -STATNAME=        ALL - Show all STATS (wildcard allowed)                      ALL

Example: ./real_time.pl -type=sysstat
Example: ./real_time.pl -type=sysstat -inst=BDTO_1,BDTO_2
Example: ./real_time.pl -type=sysstat -statname='bytes sent via SQL*Net to client'
Example: ./real_time.pl -type=sysstat -statname='%bytes%'

Conclusion:

There is no need anymore to use the “individuals” perl scripts provided so far, as they have been grouped into this main utility.

Two perl scripts still remain outside this main utility as they are not related to database cumulative views, that is to say:

Please do not hesitate to give your feedback and report any issues you may found.

Update: real_time.pl now contains asmiostat type (see this post)

Advertisement

Exadata : Direct path read and smart scan

To write this post I took my inspiration mainly from 2 sources:

  1. The Expert Oracle Exadata Book
  2. Frits Hoogland’s blog post

The book (as well as the oracle university course) states that Smart Scan prerequisites are :

  • There must be a full scan of an object.
  • The scan must use Oracle’s Direct Path Read mechanism.
  • The object must be stored on Exadata storage.

and the explanation is:

There is a simple explanation as to why these requirements exist. Oracle is a C program. The function that performs Smart Scans (kcfis_read) is called by the direct path read function (kcbldrget), which is called by one of the full scan functions. It’s that simple. You can’t get to the kcfis_read function without traversing the code path from full scan to direct read. And of course, the storage will have to be running Oracle’s software in order to process Smart Scans.

Here we are:  As oracle is a C program, I can try to figure out by my own what’s going on during Smart Scan thanks to the gdb debugger (Of course I have no doubt about the accuracy of the explanation mentioned above, the exercise is just for fun 🙂 )

To do this, I will use:

  •  One session with the trace 10046 enabled. This session is running a query that will produce the “cell smart table scan” wait event.
  •  One gdb session attached on the foreground process mentioned above (gdb -p <pid>)

First let’s create a break point on the write() function to trap the stack when the session is writing the “cell smart table scan” into the 10046 trace file.

(gdb) break write

Let’s run the query : The backtrace of all stack frames is the following when the “cell smart table scan” wait event has been written for the first time into the trace file.

#13 0x0ddb94a1 in kcfis_reap_ioctl ()
#14 0x0ddb8204 in kcfis_push ()
#15 0x0ddcc01a in kcfis_read ()
#16 0x0c800afc in kcbl_predpush_get ()
#17 0x0898c40c in kcbldrget ()
#18 0x0fadc5d2 in kcbgtcr ()

So, it looks like we are in the right direction, as we can see the kcbldrget and the kcfis_read functions into the frame.

We now just have to check which function is responsible of the “cell smart table scan” wait event.

To do so let’s put break points on the functions we discovered above that is to say:

(gdb) break kcbldrget
Breakpoint 1 at 0x898b103
(gdb) break kcbl_predpush_get
Breakpoint 2 at 0xc800a78
(gdb) break kcfis_read
Breakpoint 3 at 0xddcb371
(gdb) break kcfis_push
Breakpoint 4 at 0xddb78c0

and let’s continue:

(gdb) c
Continuing.

Now re-launch the sql.

First it breaks on:

Breakpoint 1, 0x0898b103 in kcbldrget ()

As no wait event “cell smart table scan” has been see so far into the trace file we can continue.

(gdb) c
Continuing.

Now it breaks on:

Breakpoint 2, 0x0c800a78 in kcbl_predpush_get ()

As no wait event “cell smart table scan” has been see so far into the trace file we can continue.

(gdb) c
Continuing.

Now it breaks on:

Breakpoint 3, 0x0ddcb371 in kcfis_read ()

As no wait event “cell smart table scan” has been see so far into the trace file we can continue.

(gdb) c
Continuing.

Now it breaks on :

Breakpoint 4, 0x0ddb78c0 in kcfis_push ()

Here we are: The wait event “cell smart table scan” appears into the trace file.

So the event comes from the function that has been called before the kcfis_push one. Let’s display the calling tree to figure out  which one it is:

Breakpoint 4, 0x0ddb78c0 in kcfis_push ()
(gdb) up
#1  0x0ddcc01a in kcfis_read ()
(gdb) up
#2  0x0c800afc in kcbl_predpush_get ()
(gdb) up
#3  0x0898c40c in kcbldrget ()

So it’s the kcfis_read function that produced the wait event “cell smart table scan”. Furthermore as we can see into the calling tree the kcfis_read function has been called by the kcbldrget one.

Conclusion:

The Smart Scan has been launched from the direct read code path.

But a question still remains: How can I check by myself (again just for fun) that the kcfis_read function (which is responsible of the smart scan) can not be called outside the direct read code path ? For the moment I have no clue on this 🙂

Update 2015/09/08:

As of 12.1.0.1, a more convenient way to display the stack linked to a wait event is:

 exec DBMS_MONITOR.SESSION_TRACE_ENABLE(waits => true, binds => false, plan_stat => 'NEVER');

alter session set events 'wait_event["cell smart table scan"] trace("from %s\n",shortstack())';

Enabling the session trace is not mandatory but is useful as it displays the wait event prior to the stack.
The wait event “cell smart table scan” has been used as an example (as it is the one of interest for this post), but you could use the one of your choice.

ASM Asynchronous or Synchronous I/O

As you know ASM is doing non (operating system) buffered I/O (also known as ‘DIO’ or Direct I/O) regardless of the oracle database filesystemio_options parameter.

But what’s about :  Asynchronous/Synchronous I/O ?

If you have a look to MOS note [ID 751463.1] you’ll see that ASM asynchronous/synchronous I/O is entirely controlled by the DISK_ASYNCH_IO parameter and not the FILESYSTEMIO_OPTIONS one.

At the time being, this note only deals with 10.2 databases, so I want to check if this is still the case with 11.2 databases (Let me tell you than I hope so 😉 ) :

For this test I will (at the database level):

  • create a tablespace of 10M (create tablespace bdt datafile ‘+DATA’ size 10m)
  • strace the DBW process (strace -cp <pid of dbw process>)

With differents values for filesystemio_options and disk_asynch_io.

The results are :

filesystemio_options=setall
disk_asynch_io=true

% time seconds usecs/call calls errors syscall

------ ----------- ----------- --------- --------- ----------------
41.39 0.001000 7 150 14 semtimedop
35.02 0.000846 7 126 io_submit
20.70 0.000500 4 126 io_getevents

As you can see the io_getevents and io_submit system calls have been used => Asynchronous I/O.

filesystemio_options=setall
disk_asynch_io=false

% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
75.38 0.007469 30 252 pwrite
20.19 0.002000 10 202 5 semtimedop
0.00 0.000000 0 8 close
0.00 0.000000 0 2 2 stat

As you can see the pwrite system call has been used => Synchronous I/O.

filesystemio_options=none
disk_asynch_io=true

% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
75.45 0.005055 40 126 io_submit
14.93 0.001000 6 157 2 semtimedop
9.10 0.000610 5 126 io_getevents

As you can see the io_getevents and io_submit system calls have been used => Asynchronous I/O.

filesystemio_options=none
disk_asynch_io=false

% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
56.54 0.005354 21 252 pwrite
42.23 0.003999 16 256 3 semtimedop
0.51 0.000048 0 268 semctl
0.49 0.000046 0 817 times

As you can see the pwrite system call has been used => Synchronous I/O.

Conclusion :

With 11.2 databases, ASM asynchronous/synchronous I/O is still entirely controlled by the DISK_ASYNCH_IO parameter.

Real-Time Wait Events and Statistics related to Exadata : Part II

Into my first blog entry on this topic I used 3 scripts to get real-time statistics at the database and session/sql_id level and wait events at the database level:

system_event.pl
sysstat.pl
sqlidstat.pl

This new post entry add a new one :

sqlidevent.pl (click on the link and then on the view source button to copy/paste the source code) that is usefull to diagnose more in depth real-time relationship between sql_id, sessions and wait events.

It basically takes a snapshot each second (default interval) of the v$session_event cumulative view (values are gathered since the session started up) and computes the differences with the previous snapshot.

For example, let’s check sql_id in relation with smart table scan:

./sqlidevent.pl event='%cell%'
02:00:02 INST_NAME SID	SQL_ID	        EVENT	                NB_WAITS	TIME_WAITED	ms/Wait
02:00:02 BDT1	   ALL	100dajbkzu295	cell smart table scan	9	        408816	        45.424
--------------------------------------> NEW
02:00:03 INST_NAME SID	SQL_ID	        EVENT	                NB_WAITS	TIME_WAITED	ms/Wait
02:00:03 BDT1	   ALL	100dajbkzu295	cell smart table scan	4	        273434          68.359

As you can see during the last second 4 “cell smart table scan” wait events occurred to sql_id “100dajbkzu295”.

By default all the SID have been aggregated but you could also filter on a particular sid and/or on a particular sql_id (see the help).

Remarks:

  • Those 4 scripts are not exclusively related to Exadata as you can use them on all the wait events or statistics, I simply used them with filters to focus on ‘%cell%’ .
  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • The scripts are oracle RAC aware : you can work on all the instances, a subset or the local one.
  • You have to set oraenv on one instance of the database you want to diagnose first.

Conclusion:

We now have 4 scripts at our disposal to diagnose real-time wait events and statistics at the database level and at the session/sql_id level.