Statspack Analysis

Sample Queries for analysing Statspack base tables over time.

These Queries can be used to graph details via sql developer or excel.

OS Stats over time

SELECT sn.snap_time time , nm.stat_name , os.value FROM stats$osstat os , stats$osstatname nm , stats$snapshot sn WHERE os.osstat_id = nm.osstat_id AND nm.stat_name ='LOAD' --See stats$osstatname.stat_name for other os stats. AND sn.snap_id = os.snap_id AND sn.instance_number = ( SELECT instance_number FROM v$instance ) AND os.instance_number = sn.instance_number AND sn.snap_time between '06-FEB-17' and '11-FEB-17' order by 1;

Database time (DB Time)

SELECT to_char(sn.snap_time,'YYYY-MM-DD HH24:MI:SS') time , nm.stat_name , ( LEAD( tm.value, 1 ) OVER( ORDER BY tm.snap_id ) - tm.value ) /1000000/60 AS DB_TIME FROM stats$sys_time_model tm , stats$time_model_statname nm , stats$snapshot sn WHERE tm.stat_id = nm.stat_id AND nm.stat_name = 'DB time' AND sn.snap_id = tm.snap_id AND sn.instance_number = ( SELECT instance_number FROM v$instance ) AND tm.instance_number = sn.instance_number AND sn.snap_time > sysdate -10 order by sn.snap_time;

System Event Wait(ms) over time
Edit the query to select a particular event (select distinct (event) from stats$system_event order by 1;)

select to_char(snap_time,'Mon-dd hh24:mi:ss') snap_time, event event_name, round((time_waited_ms - prv_time_waited_ms) / decode((total_waits - prv_waits), 0,null, (total_waits - prv_waits)),2) wait_average_ms from ( select ss.snap_time, se.event, se.total_waits, lag(se.total_waits,1) over (order by se.snap_id) prv_waits, se.time_waited_micro/1000 time_waited_ms, lag(se.time_waited_micro/1000,1) over (order by se.snap_id) prv_time_waited_ms from perfstat.stats$snapshot ss, perfstat.stats$system_event se where ss.snap_time > sysdate -7 and se.snap_id = ss.snap_id and se.instance_number = ss.instance_number and se.dbid = ss.dbid -- select the event you want --and se.event = 'db file sequential read' --and se.event = 'db file scattered read' and se.event = 'log file sync' ) order by snap_time

Wait Class

select sh.snap_time , en.wait_class ,sse.total_waits from stats$system_event sse, STATS$SNAPSHOT SH , v$event_name en where sh.snap_id = sse.snap_id and sh.dbid = sse.dbid and sse.event_id = en.event_id --and en.wait_class = 'System I/O' --AND sh.snap_time between '27-FEB-17' and '5-MAR-17' AND trunc(sh.snap_time) > sysdate -7 order by sh.snap_time, en.wait_class

SQL over snaps

select ss.snap_id, ss.snap_time, sql_id, executions, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, disk_reads/decode(nvl(executions,0),0,1,executions) avg_pio, elapsed_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_elap, cpu_time/decode(nvl(executions,0),0,1,executions)/1000000avg_cpu , rows_processed/decode(nvl(executions,0),0,1,executions) rows_per_exec, application_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_app_wait, concurrency_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_con_wait, user_io_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_io_wait from STATS$SQL_SUMMARY sql , stats$snapshot ss where ss.snap_id = sql.snap_id and sql_id = '8dfk0qpzvdcbc' and ss.snap_time > sysdate -2;

******************************************
 keywords: stats pack statspack
******************************************
 rdbms version:
******************************************

Tracing events

Most things that happen in a database is an 'event'. Oracle lets us trace events.

When development / testing teams are receiving an oracle warning and they dont know where the warning is occuring we can trace it.

 Eg: ORA-29261: bad argument

alter system set events '29261 trace name errorstack level 1';

This will produce a trace file every time the event ORA-29261 happens.

----- Error Stack Dump ----- ORA-29261: bad argument ----- Current SQL Statement for this session (sql_id=cz0fcp412ms20) ----- begin DECLARE l_page VARCHAR2(10); BEGIN IF :P203_NEWCLIENT = 'Y' THEN l_page := '207'; ELSE l_page := '205'; END IF; rsv.rsv_user.send_apex_mail_register(p_user_email => :P203_EMAIL_ADDRESS, p_password => :P203_password, p_from_email => :P203_EMAIL_ADDRESS, p_page_nav => l_page, p_request => 'INITIALISE'); END; end;

Turn off the tracing for the event.

alter system set events '29261 trace name context off';

******************************************
keywords: trace tracing events event
******************************************
rdbms version: 12c
******************************************

Solaris process information

Helping to find out what a process on Solaris is doing. Using the prstat command to see.

$ prstat -mL -p 1660 PID USERNAME USR SYS TRP TFL DFL LCK SLP LAT VCX ICX SCL SIG PROCESS/LWP 1660 oracle 99 0.0 0.1 0.0 0.0 0.0 0.0 0.6 3 960 1K 0 oracle/1

USR The percentage of time the process has spent in user mode.
SYS The percentage of time the process has spent in system mode.
TRP The percentage of time the process has spent in processing system traps.
TFL The percentage of time the process has spent processing text page faults.
DFL The percentage of time the process has spent processing data page faults.
LCK The percentage of time the process has spent waiting for user locks.
SLP The percentage of time the process has spent sleeping.
LAT The percentage of time the process has spent waiting for CPU.
VCX The number of voluntary context switches.
ICX The number of involuntary context switches.
SCL The number of system calls.
SIG The number of signals received.

We can run dtrace to see is a process is doing anything

# dtrace -n :::entry'/pid == 1437/{ @syscalls[probefunc] = count(); }' dtrace: description ':::entry' matched 256026 probes

After a few seconds you can control-C the command. If the process if doing nothing, the command will print nothing to the screen. If the process is doing something you will see a count of the providers.

******************************************
keywords: prstat process
******************************************
rdbms version: solaris
******************************************

Looking for load and bottlenecks on a single instance database

When people give you no information on what and where the performance problem is... Sit down with them and keep asking. And if you still get nothing, lets take a look at some guess work.

These sql statements don't use ASH or the AWR.

We can use the V$WAITCLASSMETRIC_HISTORY to see time waited for wait class for the last hour. The below query determines the average time waited for the wait classes. You might be able to see in the last hour if there has been a change in wait class response times

select begin_time , n.wait_class, 10*m.time_waited/nullif(m.wait_count,0) avgms from V$WAITCLASSMETRIC_HISTORY m, v$system_wait_class n where m.wait_class_id=n.wait_class_id and n.wait_class != 'Idle';

As for what is happening right now. We can count the sessions and group them by the wait events. This will tell us where sessions are spending the time right now.

select count(*) count, CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END state, CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END event FROM v$session_wait GROUP BY CASE WHEN state != 'WAITING' THEN 'WORKING' ELSE 'WAITING' END , CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue' ELSE event END ORDER BY 1 DESC, 2 DESC;

If we want to see what sessions are working in the database, we could use V$SESSTAT and DB time. Below will show us the top 10 sessions based on the DB Time statistic. The query ignores background sessions and only shows sessions active in the last 60 minutes.

select * from ( select ss.sid, s.serial#, s.username, n.name, ss.value from v$sesstat ss, v$statname n, v$session s where ss.statistic# = n.statistic# and n.name = 'DB time' and ss.sid = s.sid and s.username is not null AND s.last_call_et < 3600 order by value desc ) where rownum <=10;

******************************************
keywords: load
******************************************
rdbms version: 12c
******************************************

Oracle Storage Cloud Service - Using cURL to creating storage containers

A container is a storage compartment that provides a way to organize the data stored in Oracle Storage Cloud Service. Containers are similar to directories, but with a key distinction; unlike directories, containers cannot be nested.

Requesting an Authentication Token

curl -v -X GET \
-H "X-Storage-User: Storage-identityDomain:fname.lname@identityDomain.com" \
-H "X-Storage-Pass: password" \
https://IdentityDomain.storage.oraclecloud.com/auth/v1.0


Example

curl -v -X GET -H "X-Storage-User: Storage-identityDomain:service.admin@identityDomain.com" -H "X-Storage-Pass: password" https://identityDomain.storage.oraclecloud.com/auth/v1.0

You need to record the X-Auth-Token returned in the output.

Creating a Container

curl -v -X PUT \
-H "X-Auth-Token: token" \
accountURL/containerName


Example

curl -v -X PUT -H "X-Auth-Token: AUTH_tk1d16see6f140635ffe0d05c64bf8b022" https://identityDomain.storage.oraclecloud.com/v1/Storage-identityDomain/new_container

You should see the "HTTP/1.1 201 Created" in the output returned.

Listing Containers

curl -v -X GET \
-H "X-Auth-Token: AUTH_tk1d162f96f140635ffe0d05c64bf8b022" \
https://identityDomain.storage.oraclecloud.com/v1/Storage-identityDomain


Example

curl -v -X GET -H "X-Auth-Token: AUTH_tk1d162f96f140635ffe0d05c64bf8b022" https://identityDomain.storage.oraclecloud.com/v1/Storage-identityDomain

Deleting a container

curl -v -X DELETE \
-H "X-Auth-Token: AUTH_tkb4fdf39c92e9f62cca9b7c196f8b6e6b" \
https://identityDomain.storage.oraclecloud.com/v1/Storage-identityDomain/new_container


Example

curl -v -X DELETE -H "X-Auth-Token: AUTH_tkb4fdf39c92e9f62cca9b7c196f8b6e6b" https://identityDomain.storage.oraclecloud.com/v1/Storage-identityDomain/new_container

******************************************
keywords: Cloud curl
******************************************
rdbms version:
******************************************