Performance

Contents

• Introduction
• Explain Plan 
• Input / Output 
• Optimizer, and hints
• Parameters linked to optimizer and tracing
• Analyze / statistics
• Event Contention 

Introduction

Basic steps for tuning

• Operating system running?
• Application Workload
• Tweak SQL
• Memory issues
• Physical I/O
• Contention for resources, in particular rollback segments or tables that are locked by another process
• Operating system issues
• Best Practices

Define the objective of the improvement, such as measurable response time, database availability, access rate to the database and memory usage.

Some things to look for in case of bad performance:

• locking/blocking/waiting: see locks. For waits: select sid, event, seconds_in_wait from v$session_wait where sid in (select sid from v$session where username is not NULL). See section on events
• high CPU utilization:
  ps x | grep ora (the PID in UNIX is the SPID in v$process, the PID in Windows is the first part of the process column)
• undo or rollback issues
• wait issues
• IO issues: select * from v$sess_io where sid in (select sid from v$session where username is not NULL) order by physical_reads
• Top-n SQL: SELECT * FROM (SELECT cpu_time, elapsed_time, disk_reads, buffer_gets, rows_processed, executions, sql_text from v$sql order by cpu_time desc) WHERE ROWNUM < 6

Tips

• NOT EXISTS is better than NOT IN
• UNION ALL is better than UNION
• OR with indexed columns may cause full table scan: use IN or UNION
• Referencing by ROWID of a record is the fastest method of access
• DECODE in output is quite efficient, except when it prevents the use of an index.
• Functions in conditions may not allow the use of indexes
• Use the "dbms_stats" package instead of the analyze command because the dbms_stats package generates more statistics (especially in 9i). Note that statistics stored in the user's schema are not useful for the cost-based optimizer (i.e. stattab is null).

Explain Plan and Statistics

Setup explain plan:

• Run the script $ORACLE_HOME/admin/UTLXPLAN.SQL --> creates the table PLAN_TABLE
• Grant INSERT, SELECT, DELETE to users that will use the utility

Setup statstics:

• Run the script $ORACLE_HOME/admin/PLUSTRCE.SQL as SYS --> creates the role PLUSTRACE
• Grant role PLUSTRACE to users that will use autotrace

Use:

EXPLAIN PLAN
  [SET STATEMENT_ID = 'label']    -- Use to flag rows
  [INTO schema.table@db_link]      -- if not using the default table PLAN_TABLE
  FOR statement

Some of the columns:
OPTIMIZER: Optimizer mode
POSITION: For the first row of output, indicates the CBO’s cost of the statement. For the other rows, indicates the position relative to the other children of the same parent.
COST: cost of the operation.
CARDINALITY: number of rows accessed
BYTES: number of bytes accessed
CPU_COST: CPU cost
IO_COST: IO cost
TEMP_SPACE: temp space used

A query to see results:

ACCEPT QUERY CHAR PROMPT 'QUERY NAME: '
TTITLE 'QUERY PLAN'
COL PLAN FORMAT A80
SELECT LPAD( ' ', 2 * ( LEVEL - 1 ) ) || OPERATION || ' ' || OPTIONS || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE PLAN,
    COST
  FROM PLAN_TABLE
  WHERE STATEMENT_ID = '&&QUERY'
  CONNECT BY PRIOR ID = PARENT_ID
         AND PRIOR STATEMENT_ID = '&QUERY'
  START WITH ID =1;
UNDEFINE QUERY

SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

Off --> no trace
On --> trace and data
traceonly --> no data, just trace
explain --> explain plan, but no statistics
statistics --> statistics but no trace command

Autotrace

set autotrace trace

Explain plan and stat (waits for execution but does not show results)

set autotrace trace exp

Only explain plan (no statistics)

set autotrace on

Show results, in addition to explain plan and statistics

set autotrace on explain

Show results, in addition to explain plan

set autotrace off

Turn off 

Script for creating table is in rdbms/admin/utlxplan.sql

 

Oracle 9i: select * from table(dbms_xplan.display);

Note that Oracle can combine severs bitmap indexes and can only use one non-bitmap index.

See Optimizer and Hints

Input / Output

Excessive I/O on SYSTEM tablespace may indicate:

• Too much access to data dictionary because shared pool size too small, i.e. Oracle is constantly re-loading definitions into the shared poo
• No user default tablespace, i.e. the data is being written to the SYSTEM tablespace
• No temporary tablespace for user, i.e. the SYSTEM tablespace is being used as a temporary tablespace

Optimizer

optimizer_mode initialization parameter --> rule-based (RBO) or cost-based (CBO)

The optimizer mode may be set for the instance (at start-up), for the session (ALTER SESSION SET OPTIMIZER_MODE=...) or for the statement using a hint (SELECT /*+ ALL_ROWS */ )

OPTIMIZER_MODE=RULE

Rule based, even if statistics are available. Not a good idea to use because cost-based is now generally much better.

OPTIMIZER_MODE=ALL_ROWS

Minimize response time

OPTIMIZER_MODE=FIRST_ROWS_n
OPTIMIZER_MODE=FIRST_ROWS

Minimize response time only for the first n rows, or the first few rows.

OPTIMIZER_MODE=CHOOSE

Choose rule-based if statistics are not available. "choose" is default.

Hints (if you have to...)

SELECT /*+ ordered */

Access the tables in the FROM clause in the order that they are shown.

/*+ LEADING (table_name) */

Use table as the first in the join order

/*+ ORDERED */

Join tables in the order in wich they appear in the FROM clause

/*+ STAR */

Largest table last in join order (at least three tables)

/*+ index(tab_alias, index_name) index(tab_alias2, index_name2) */

Use index.
Remeber comma after table name.
Use alias if the alias is in the statement.
No commas between the index statements.

/*+ index(my_tab my_tab(col1, col2)) */

Use index that contains the columns col1 and col2

 

 

 

 

 

 

 

 

 

 

See René Nyffenegger's collection of things on the web

Hint discouraged in or on views

Initialization Parameters Linked to Optimizer

BITMAP_MERGE_AREA_SIZE

default: 1MB

CURSOR_SHARING

Default: exact. N.B. Bugs reported for this parameter.

DB_FILE_MULTIBLOCK_READ_COUNT

Number of blocks read in a single I/O operation. Default 8. For data warehouse, best is high. For OLTP, best is 4 to 16.
Dynamic: ALTER SESSION, ALTER SYSTEM

HASH_AREA_SIZE

Derived: 2 * SORT_AREA_SIZE

HASH_JOIN_ENABLED

Allow hash joins. Default: TRUE. Otherwise false.

OPTIMIZER_FEATURES_ENABLE Parameter

Static parameter. Default: 9.2.0

OPTIMIZER_DYNAMIC_SAMPLING

Dynamically sample tables if statistics are not available when building the query. 0=no dynamic sampling, 10=sample all tables.
Dynamic: ALTER SESSION, ALTER SYSTEM.
Default: 1 (9.2.0 or higher) or 0 (before 9.2.0).

OPTIMIZER_INDEX_CACHING

None zero value makes the optimizer think that there are index values in the cache.
Dynamic: ALTER SESSION
Default: 0

OPTIMIZER_INDEX_COST_ADJ

Adjustement of index cost. Less than 100 favors index access path, more than 100 favors full table scan.
Dynamic: ALTER SESSION
Default: 100.

OPTIMIZER_MAX_PERMUTATIONS

Dynamic: ALTER SESSION
Default: OPTIMIZER_FEATURES_ENABLE=9.0.0 or higher, then 2000
OPTIMIZER_FEATURES_ENABLE=8.1.7 or lower, then 80000

OPTIMIZER_MODE

See section "OPTIMIZER_MODE"

PARALLEL_ADAPTIVE_MULTI_USER

Used for parallel processing. TRUE or FALSE
Default: Derived

PARALLEL_AUTOMATIC_TUNING

Used for parallel processing. Default: FALSE

PARTITION_VIEW_ENABLED

Used for partitions.
Dynamic: ALTER SESSION
Default: FALSE

QUERY_REWRITE_ENABLED

ALTER SESSION/SYSTEM QUERY_REWRITE_ENABLED = {force | true | false}
"True" means that rewriting and not rewriting are compared; "force" means that the query is always rewritten.
Dynamic: ALTER SESSION, ALTER SYSTEM

QUERY_REWRITE_INTEGRITY

Dynamic: ALTER SESSION, ALTER SYSTEM
Default: ENFORCED

SORT_AREA_RETAINED_SIZE

Size of sort area after a sort. The parameter is kept for backwards compatibility.
Dynamic: ALTER SESSION, ALTER SYSTEM ...DEFERRED
Default: Derived from SORT_AREA_SIZE

SORT_AREA_SIZE

Size of sort area in memory. If the sort is too big, the temporary tablespace is used. Increase this value if V$SYSTEM_EVENT indicates "Sorts to Disk" wait events, or if V$SYSSTAT (sorts-in-memory)/(sorts-in-memory + sorts-on-disk) ratio is < 99%.
Better to control size with PGA_AGGREGATE_TARGET
Dynamic: ALTER SESSION, ALTER SYSTEM ...DEFERRED
Default: 65536

STAR_TRANSFORMATION_ENABLED

Values: TRUE, FALSE, TEMP_DISABLE.
Dynamic: ALTER SESSION
Default: FALSE

Tracing

A trace file is created in the sub-directory indicated by USER_DUMP_DEST parameter. The maximum size of the dump file is limited by the parameter MAX_DUMP_FILE_SIZE. Timing results are available by changing the parameter TIMED_STATISTICS. Process the dump file with TKPROF or another utility.

For the whole database:

ALTER SYSTEM set sql_trace=TRUE scope=spfile; (9i)
Or change the parameter in init.ora.
Note that this is not dynamic --> restart the database.

For the current session (dynamic):

ALTER SESSION set sql_trace=true;

Start tracing

ALTER SESSION set events '10046 trace name context forever, level 8';

Levels:
1 Normal Trace File
4 Dump bind variable values
8 Dump Wait Information (especially useful)
12 Dump all information

ALTER SESSION set events '10046 trace name context off';

Turn off tracing

For another session:

First get the sid and serial number:
select sid, serial#, username, osuser, machine
from v$session
where username is not null;

Then start the tracing (note that granting permission may be necessary):
exec sys.dbms_system.set_ev(<sid number>,<serial number>,10046,<level (see above)>,' ');

Views

V$SESSION

Basic information about the sessions, espcially columns SID, SERIAL#, USERNAME, OSUSER, MACHINE, PROGRAM, LOGON_TIME, CLIENT_INFO

V$OPEN_CURSOR

Open and parsed cursors

V$PROCESS

Lists the processes

V$SEGSTAT

(9i2) Segment statistics, usefule for buffer busy waits

V$SESS_IO

Input / output. Especially column PHYSICAL_READS

V$SESSION_LONGOPS

Operations that last more than 6 seconds

V$SQL

Use V$SQLAREA

V$SQLAREA

Parsed SQL in memory

V$SQLTEXT

Parsed SQL text

V$SQLTEXT_WITH_NEWLINES

Reformatted from V$SQLTEXT

V$SQL_PLAN - V$SQL_PLAN_STATISTICS - V$SQL_PLAN_STATISTICS_ALL

Execution plans

V$SYSSTAT

System statistics: look for high values in "select name, value from v$sysstat order by value asc;"

V$SESSTAT

System statistics. Join with V$STATNAME:
select b.sid, a.name, b.value from v$statname a, v$sesstat b where a.statistic#=b.statistic#

V$SYSTEM_EVENT

Especially time_waited column (see events)

V$SESSION_WAIT

Especially SID, EVENT, and SECONDS_IN_WAIT (see events)

 

Analyze / Statistics

Use DBMS_STATS package (recommended) or the ANALYZE statement

ANALYZE TABLE emp COMPUTE STATISTICS;

ANALYZE TABLE emp ESTIMATE STATISTICS SAMPLE { n ROWS | n PERCENT } ;

DBMS_UTILITY.ANALYZE_SCHEMA
DBMS_UTILITY.ANALYZE_DATABASE
DBMS_DDL.ANALYZE_OBJECT

PROCEDURE DBMS_UTILITY.ANALYZE_SCHEMA
  (schema IN VARCHAR2
   ,method IN VARCHAR2
   ,estimate_rows IN NUMBER DEFAULT NULL
   ,estimate_percent IN NUMBER DEFAULT NULL
   ,method_opt IN VARCHAR2 DEFAULT NULL);

Schema

For current schema, put "user"

method

'COMPUTE': exact statistics; 'ESTIMATE': either rows or percent; 'DELETE': delete the statistics

estimate rows

number of rows to use

estimate percent

ignored is estimate_rows is non-null

method_opt

'FOR TABLE', 'FOR ALL COLUMNS [SIZE n]', 'FOR ALL INDEXED COLUMNS [SIZE n]', 'FOR ALL INDEXES'

Examples

BEGIN
DBMS_UTILITY.ANALYZE_SCHEMA ( USER, 'ESTIMATE', 100, 50, 'FOR ALL INDEXES');
END;
/

Estimate statistics for all indexes

BEGIN
DBMS_UTILITY.ANALYZE_SCHEMA ( user, 'COMPUTE', null, null, null);
END;
/

Calculate for all

Event Contention

List of event names: select * from v$event_name

Show events since startup:
   V$SYSTEM_EVENT
  V$SESSION_EVENT (join with V$SESSION)
  V$SESSION_WAIT: current waits.

Values for wait time in V$SESSION_WAIT:
  > 0  Last wait time
  = 0  Session is waiting
  =-1  Value was less than 10 msec
  =-2  No information available
Also see parameter TIMED_STATISTICS