Performance
From dbawiki
- Memory Management through the versions
- Automatic Memory Management discussion with Tom Kyte
- Automatic Memory Management from ORACLE_BASE
- Tuning
Contents
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 ten SQL statements with the greatest aggregate elapsed time
SELECT sql_id,child_number,sql_text, elapsed_time
FROM (SELECT sql_id, child_number, sql_text, elapsed_time,
cpu_time,disk_reads,
RANK () OVER (ORDER BY elapsed_time DESC) AS elapsed_rank
FROM v$sql)
WHERE elapsed_rank <= 10
See the explain plan of these SQL's by feeding the sql_id and child_no into this cracker!
SELECT * FROM TABLE (DBMS_XPLAN.display_cursor (&sql_id, &child_no,'TYPICAL -BYTES'));
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 wait 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 /
EXPLAIN PLAN
It should not be necessary to bold the plan_table as it is a global temporary table by default.
To create your own (to keep the plans longer than a session), run $ORACLE_HOME/rdbms/admin/utlxplan.sql
Usage
EXPLAIN PLAN [SET STATEMENT_ID = 'statement_id'] [INTO table_name ] FOR sql_statement
See the results
SELECT RTRIM (LPAD (' ', 2 * LEVEL) || RTRIM (operation) || ' ' || RTRIM (options) || ' ' || object_name) query_plan
, cost
, cardinality
FROM plan_table
CONNECT BY PRIOR id = parent_id
START WITH id = 0
or
SELECT * FROM TABLE(dbms_xplan.display());
