Difference between revisions of "Performance"

Revision as of 11:51, 7 January 2015

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

Gather database stats

Some helpful stuff on statistics gathering here

#!/usr/bin/ksh
sqlplus / as sysdba << EOSQL
exec dbms_stats.gather_database_stats( estimate_percent=>dbms_stats.auto_sample_size, degree => 8 );
EOSQL

Top memory consumers in AIX

svmon -U -t 10 -O process=on -O sortentity=pgsp

How much memory is being used by processes?

col PGA_USED_MEM for 999,999,999,990
col PGA_ALLOC_MEM for 999,999,999,990
col PGA_FREEABLE_MEM for 999,999,999,990
col PGA_MAX_MEM for 999,999,999,990
compute sum of PGA_USED_MEM on report
compute sum of PGA_ALLOC_MEM on report
compute sum of PGA_MAX_MEM on report
break on report
SELECT PROGRAM, PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
FROM V$PROCESS
order by 2
/

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

set lines 200
col "00" for a5
col "01" for a5
col "02" for a5
col "03" for a5
col "04" for a5
col "05" for a5
col "06" for a5
col "07" for a5
col "08" for a5
col "09" for a5
col "10" for a5
col "11" for a5
col "12" for a5
col "13" for a5
col "14" for a5
col "15" for a5
col "16" for a5
col "17" for a5
col "18" for a5
col "19" for a5
col "20" for a5
col "21" for a5
col "22" for a5
col "23" for a5
select * from (
    select trunc(first_time) "Date\Hour",
       to_char(sum(decode(to_char(first_time,'HH24'),'00',1,0)),'9999') "00",
       to_char(sum(decode(to_char(first_time,'HH24'),'01',1,0)),'9999') "01",
       to_char(sum(decode(to_char(first_time,'HH24'),'02',1,0)),'9999') "02",
       to_char(sum(decode(to_char(first_time,'HH24'),'03',1,0)),'9999') "03",
       to_char(sum(decode(to_char(first_time,'HH24'),'04',1,0)),'9999') "04",
       to_char(sum(decode(to_char(first_time,'HH24'),'05',1,0)),'9999') "05",
       to_char(sum(decode(to_char(first_time,'HH24'),'06',1,0)),'9999') "06",
       to_char(sum(decode(to_char(first_time,'HH24'),'07',1,0)),'9999') "07",
       to_char(sum(decode(to_char(first_time,'HH24'),'08',1,0)),'9999') "08",
       to_char(sum(decode(to_char(first_time,'HH24'),'09',1,0)),'9999') "09",
       to_char(sum(decode(to_char(first_time,'HH24'),'10',1,0)),'9999') "10",
       to_char(sum(decode(to_char(first_time,'HH24'),'11',1,0)),'9999') "11",
       to_char(sum(decode(to_char(first_time,'HH24'),'12',1,0)),'9999') "12",
       to_char(sum(decode(to_char(first_time,'HH24'),'13',1,0)),'9999') "13",
       to_char(sum(decode(to_char(first_time,'HH24'),'14',1,0)),'9999') "14",
       to_char(sum(decode(to_char(first_time,'HH24'),'15',1,0)),'9999') "15",
       to_char(sum(decode(to_char(first_time,'HH24'),'16',1,0)),'9999') "16",
       to_char(sum(decode(to_char(first_time,'HH24'),'17',1,0)),'9999') "17",
       to_char(sum(decode(to_char(first_time,'HH24'),'18',1,0)),'9999') "18",
       to_char(sum(decode(to_char(first_time,'HH24'),'19',1,0)),'9999') "19",
       to_char(sum(decode(to_char(first_time,'HH24'),'20',1,0)),'9999') "20",
       to_char(sum(decode(to_char(first_time,'HH24'),'21',1,0)),'9999') "21",
       to_char(sum(decode(to_char(first_time,'HH24'),'22',1,0)),'9999') "22",
       to_char(sum(decode(to_char(first_time,'HH24'),'23',1,0)),'9999') "23"
    from   v$log_history
    group  by trunc(first_time)
    order  by trunc(first_time) 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

prompt Enter CPU, READS or EXECS
prompt (Press Enter for CPU default)

select nvl(a.username, '(oracle)') as username
,      a.osuser
,      a.sid||','||a.serial# killer
,      c.value as &&TYPE
,      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('&TYPE'), 'READS', 'session logical reads',
                                             'EXECS', 'execute count',
                                             'CPU',   'CPU used by this session',
                                                      'CPU used by this session'
                      )
order by c.value desc
/

undef TYPE

Top SQL

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

set lines 1000 pages 1000
set verif off
col sql_text for a150

select *
from   (
       select substr(a.sql_text,1,200) 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
/
set lines 80

Top ten SQL statements with the greatest aggregate elapsed time

set lines 1000 pages 1000
col sql_text for a150
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
/
set lines 80

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

Two most useful DBMS_XPLAN functions
format can be 'typical','basic' or 'all'

DBMS_XPLAN.display (
table_name      VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id    VARCHAR2 DEFAULT NULL,
format          VARCHAR2 DEFAULT 'TYPICAL',
filter_preds    VARCHAR2 DEFAULT NULL
)

and

DBMS_XPLAN.display_cursor (
sql_id            VARCHAR2 DEFAULT NULL,
cursor_child_no   INTEGER  DEFAULT 0,
format            VARCHAR2 DEFAULT 'TYPICAL'
)

Basic use

SELECT * FROM TABLE(dbms_xplan.display());

Virtual Indexes

An index created to see if the optimiser would use it without actually having to build it

SQL> ALTER SESSION SET "_use_nosegment_indexes"=TRUE;
Session altered.

SQL> CREATE INDEX sh.sales_vi1 ON sh.sales(quantity_sold) NOSEGMENT;
Index created.

Now re-run the explain plan and see the difference.

References

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