Difference between revisions of "Performance"

Revision as of 22:28, 18 February 2014

• Memory Management through the versions
• Automatic Memory Management discussion with Tom Kyte
• Automatic Memory Management from ORACLE_BASE
• Tuning

How many log switches have ocurred per hour over the past week?

set lines 200
select * from (
select TO_DATE(first_time,'DD-MON-RR') "Date",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'999') "00",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'999') "01",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'999') "02",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'999') "03",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'999') "04",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'999') "05",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'999') "06",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'999') "07",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'999') "08",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'999') "09",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'999') "10",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'999') "11",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'999') "12",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'999') "13",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'999') "14",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'999') "15",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'999') "16",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'999') "17",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'999') "18",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'999') "19",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'999') "20",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'999') "21",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'999') "22",
       TO_CHAR(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'999') "23"
from   v$log_history
group  by to_date (first_time,'DD-MON-RR')
order  by 1 desc
)
where  rownum < 8;

Top session activity

Enter READS, EXECS or CPU to order session activity by that column

set lines 500 pages 1000 verif off 

col username   for a15
col machine    for a26
col module     for a30
col logon_time for a20
col program    for a30
col killer     for a12
col osuser     for a10

select nvl(a.username, '(oracle)') as username
,      a.osuser
,      a.sid||','||a.serial# killer
,      c.value as &&1
,      a.lockwait
,      a.status
,      a.module
,      a.machine
,      a.program
,      to_char(a.logon_time,'dd-mon-yyyy hh24:mi:ss') as logon_time
from   v$session a
,      v$sesstat c
,      v$statname d
where  1=1
and    a.sid        = c.sid
and    c.statistic# = d.statistic#
and    d.name       = decode(upper('&1'), 'READS', 'session logical reads',
                                          'EXECS', 'execute count',
                                          'CPU',   'cpu used by this session',
                                                   'cpu used by this session')
order by c.value desc
/

undef 1

Top SQL

Shows the SQL statements that have caused the most disk reads per execution since the instance was last started

set lines 500 pages 1000
set verif off 

select *
from   (
       select substr(a.sql_text,1,50) sql_text
       ,      trunc(a.disk_reads/decode(a.executions,0,1,a.executions)) reads_per_execution
       ,      a.buffer_gets
       ,      a.disk_reads
       ,      a.executions
       ,      a.sorts
       ,      a.address
       from   v$sqlarea a
       order  by 2 desc
       )
where  rownum <= 11
/

Top waits

Displays a list of the events currently being waited on by active sessions.
The meaning of the wait_time and seconds_in_wait columns varies depending on their values follows:

• wait_time - A non-zero value represents the session’s last wait time, while a zero value indicates that the session is currently waiting.
  
• seconds_in_wait - When the wait_time is zero, the seconds_in_wait value represents the seconds spent in the current wait condition.
  

When the wait_time is greater than zero, the seconds_in_wait value represents the seconds since the start of the last wait,
and (seconds_in_wait - wait_time / 100) is the active seconds since the last wait ended.

set lines 200 pages 1000 
col username   for a20
col event      for a30
col wait_class for a15

select nvl(s.username, '(oracle)') as username
,      s.sid
,      s.serial#
,      sw.event
,      sw.wait_class
,      sw.wait_time
,      sw.seconds_in_wait
,      sw.state
from   v$session_wait sw
,      v$session s
where  1=1
and    s.sid = sw.sid
order  by sw.seconds_in_wait desc
/

Session history

Once a session of interest has been identified, we can display the history of events associated with that session

set lines 200 pages 1000
set verif off
col username for a20
col event    for a40 

select nvl(s.username, '(oracle)') as username
,      s.sid
,      s.serial#
,      se.event
,      se.total_waits
,      se.total_timeouts
,      se.time_waited
,      se.average_wait
,      se.max_wait
,      se.time_waited_micro
from   v$session_event se
,      v$session s
where  1=1
and    s.sid = se.sid
and    s.sid = &1
order  by se.time_waited desc
/

System waits

select event
,       total_waits
,       total_timeouts
,       time_waited
,       average_wait
,       time_waited_micro
from v$system_event
order by event
/

References

• http://www.dba-oracle.com/plsql/t_plsql_v$.htm