Performance

  

These are my personal notes that I use as a quick help in my work.
You are welcome to read them.

Contents of current page Top-level home page
 
Index  Java Internet Oracle Notes
Linux Basics Web Basics SQL Notes
Informatica Servlets Apache BkpRstore SQL*Plus
Teradata   LDAP Storage PL/SQL
Windows     Tables OEM
UML   Net8 Portal
SQL Server Python perl Performance OLAP
Vmware Visual Basic PHP/MySQL User Mgmt  
Git        
More technical pages here

Contents

 


Introduction

  

Basic steps for tuning

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:

 

Tips

 

 


Explain Plan and Statistics

Setup explain plan:

Setup statstics:

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:

 


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