Difference between revisions of "Performance"

Revision as of 12:21, 3 February 2016

• Metalink AMM Note 443746.1
• Oracle 12c documentation: Memory Management
• Memory Management through the versions
• Automatic Memory Management discussion with Tom Kyte
• Automatic Memory Management from ORACLE_BASE
• How Parsing in Oracle Causes Contention for Shared Pool and Library Cache Latches
• Tuning

Check for stale statistics

select m.table_owner
,      m.table_name
,      m.timestamp
,      sum(m.inserts)
,      sum(m.updates)
,      sum(m.deletes)
,      t.num_rows
,      t.last_analyzed
from   sys.dba_tab_modifications m
,      dba_tables t
where  m.table_owner = t.owner
and    m.table_name  = t.table_name
group  by m.table_owner
,      m.table_name
,      m.timestamp
,      t.num_rows
,      t.last_analyzed
order  by 1,2,3
/

or

select dt.owner||'.'||dt.table_name owner_table
,      dtm.partition_name
,      inserts+updates+deletes modified_rows, num_rows, last_analyzed
,      case when num_rows = 0 then null
            else (inserts+updates+deletes) / num_rows * 100
       end percent_modified
from   dba_tab_modifications dtm
join   dba_tables dt
on     dtm.table_name = dt.table_name
order  by 6 desc
/

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

Gather schema statistics

begin
dbms_stats.gather_schema_stats ( ownname          => 'FEUK_SCR'
                               , options          => 'gather auto'
                               , estimate_percent => 100
                               , degree           => 6
                               , method_opt       => 'for all indexed columns'
                               , cascade          => true
                               );
end;
/

Gather table statistics

BEGIN dbms_stats.gather_table_stats(ownname =>'FEUK_PRF',tabname=>'TD_NOTE',estimate_percent=>20,cascade=>true,degree=>6,METHOD_OPT=>'FOR ALL COLUMNS SIZE 100' ); END; 

Top memory consumers in AIX

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

Show all Oracle hidden parameters

set pages 100 lines 300 
col ksppinm  for a50
col ksppstvl for a50
 
select ksppinm
,      ksppstvl
from   x$ksppi  a
,      x$ksppsv b
where  1=1
and    a.indx=b.indx 
and    substr(ksppinm,1,1) = '_'
order  by ksppinm
/

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
/

Enable Automatic Memory Management (AMM)

AMM is enabled by setting one or both of the following memory parameters:

alter system set memory_max_target=2g scope=spfile;
alter system set memory_target=2g scope=spfile;

With these memory parameters set, AMM is enabled.
Memory will now be allocated automatically to where it is needed.

alter system set sga_target=0 scope=spfile;
alter system set pga_aggregate_target=0 scope=spfile;

If sga_target and/or pga_aggregate_target are set, these will be treated as minimum values.
If only one of sga_target or pga_aggregate_target is set, the other will be set to (memory_target - the value set).

SGA tuning

Problem with 12c SGA growing too high. Database keeps crashing due to memory issues. Started at 2G, then to 4G and now at 5G.
Raised SR with Oracle. This is the response

You need to query the advice views like V$MEMORY_TARGET_ADVICE, V$SGA_TARGET_ADVICE, V$SHARED_POOL_ADVICE etc.
Interpret the results and then make the changes to memory components as indicated by the SIZE column.
Please review
Note 1323708.1 : Tuning the SGA_TARGET using V$SGA_TARGET_ADVICE then follow the version specific advice.

Please note that Oracle recommend to specify a value for the each of the tunable parameters
i.e Shared Pool, Database Buffer Cache, Large Pool, Java Pool and Streams Pool even when AMM or ASMM is enabled (you are using AMM).
When minimum value for each of the tunable parameters is set, Oracle will make sure that the amount of memory being allocated for the corresponding pool will not shrink below the specified amount.
Best practices with auto-tuning are to include explicit, minimum settings for the various auto-tuned components.
This will help the auto-tuner make "smarter" choices about moving memory within the SGA.
While it is not required to set explicit settings, the auto-tuner can get over aggressive with memory moves when auto-tuned components are set to 0.

The parameter _kghdsidx_count controls the number of subpools used. Setting this parameter to 2 will be the best. Currently you have this parameter set to 5.

SQL> alter system set "_kghdsidx_count"=2 scope=spfile;
or add this in the pfile
"_kghdsidx_count"=2

As a best practice for SGA components you can use the following recommendations:

- SHARED_POOL_SIZE >= 2G (because minimum 1G/subpool is recommended)
- LARGE_POOL_SIZE >= 150M (used for parallel executions)
- STREAMS_POOL_SIZE >= 150M (if you use datapump regularly)
- JAVA_POOL_SIZE >= 150M (if you use java)
- SGA_TARGET >= 3G (because it should be greater by 10% of the sum of it's components)

From 1323708.1

In Oracle11g (or higher), the information in the V$SGA_TARGET_ADVICE view is similar to that provided in the V$MEMORY_TARGET_ADVICE view for Automatic Shared Memory Management.
If MEMORY_TARGET is set, then SGA_TARGET should be set to 0. See Document 443746.1 for more details on this.
If MEMORY_TARGET is not set, use the same steps as for 10g.

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 PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

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