Oracle 10g includes many performance tuning enhancements including:
Oracle 10g includes several features related to automatic performance diagnostics and tuning.
Most of these features are beyond the scope of this article and as such will be dealt with in separate articles.
Automatic Shared Memory Management puts Oracle in control of allocating memory within the SGA. The SGA_TARGET
parameter sets the amount of memory available to the SGA. This parameter can be altered dynamically up to a maximum of the SGA_MAX_SIZE
parameter value. Provided the STATISTICS_LEVEL
is set to TYPICAL
or ALL
and the SGA_TARGET
is set to a value other than "0" Oracle will control the memory pools which would otherwise be controlled by the following parameters.
DB_CACHE_SIZE
(default block size)SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
If these parameters are set to a non-zero value they represent the minimum size for the pool. These minimum values may be necessary if you experience application errors when certain pool sizes drop below a specific threshold.
The following parameters must be set manually and take memory from the quota allocated by the SGA_TARGET
parameter.
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_nK_CACHE_SIZE
(non-default block size)STREAMS_POOL_SIZE
LOG_BUFFER
A number of views have been updated and added to improve the wait model. The updated views include the following.
V$EVENT_NAME
V$SESSION
V$SESSION_WAIT
The new views include the following.
V$ACTIVE_SESSION_HISTORY
V$SESSION_WAIT_HISTORY
V$SESS_TIME_MODEL
V$SYS_TIME_MODEL
V$SYSTEM_WAIT_CLASS
V$SESSION_WAIT_CLASS
V$EVENT_HISTOGRAM
V$FILE_HISTOGRAM
V$TEMP_HISTOGRAM
The following are some examples of how these updates can be used.
The V$EVENT_NAME
view has had three new columns added (WAIT_CLASS_ID
, WAIT_CLASS#
and WAIT_CLASS
) which indicate the class of the event. This allows easier aggregation of event details.
-- Display time waited for each wait class. SELECT a.wait_class, sum(b.time_waited)/1000000 time_waited FROM v$event_name a JOIN v$system_event b ON a.name = b.event GROUP BY a.wait_class; WAIT_CLASS TIME_WAITED --------------------------- ----------- Application .013388 Commit .003503 Concurrency .009891 Configuration .003489 Idle 232.470445 Network .000432 Other .025698 System I/O .095651 User I/O .109552 9 rows selected.
The V$SESSION
view has had several columns added that include blocking session and wait information. The wait information
means it's no longer necessary to join to V$SESSION_WAIT
to get wait information for a session.
-- Display blocked session and their blocking session details. SELECT sid, serial#, blocking_session_status, blocking_session FROM v$session WHERE blocking_session IS NOT NULL; no rows selected -- Display the resource or event the session is waiting for. SELECT sid, serial#, event, (seconds_in_wait/1000000) seconds_in_wait FROM v$session ORDER BY sid; SID SERIAL# EVENT SECONDS_IN_WAIT ---------- ---------- ----------------------------------- --------------- 131 20 SQL*Net message from client .000015 133 501 wakeup time manager .000138 134 28448 SQL*Net message to client 0 135 4 queue messages .000003 137 8 SQL*Net message from client .000132 .... 167 1 rdbms ipc message 0 168 1 rdbms ipc message 0 169 1 rdbms ipc message .079485 170 1 pmon timer .092645 29 rows selected.
The V$SESSION_WAIT_CLASS
view allows you to see the session wait information broken down by wait class for each session.
-- Display session wait information by wait class. SELECT * FROM v$session_wait_class WHERE sid = 134; SID SERIAL# WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS TOTAL_WAITS TIME_WAITED ---------- ---------- ------------- ----------- ------------------- ----------- ----------- 134 28448 4217450380 1 Application 2 0 134 28448 3875070507 4 Concurrency 1 2 134 28448 2723168908 6 Idle 57 392127 134 28448 2000153315 7 Network 68 5 4 rows selected.
The V$SESSION_WAIT_HISTORY
view shows historical wait information which allows you to identify issues after the session has ended.
By default Oracle 10g automatically gathers optimizer statistics using a scheduled job called GATHER_STATS_JOB
. By default this
job runs within a maintenance windows between 10 P.M. to 6 A.M. week nights and all day on weekends. The job calls the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC
internal procedure which gathers statistics for tables with either empty or stale statistics, similar to the DBMS_STATS.GATHER_DATABASE_STATS
procedure using the GATHER AUTO
option. The main difference is that the internal job prioritises the work such that tables most urgently requiring statistics updates are processed first.
In some cases automatically gathering statistics can cause problems. Highly volatile tables and load tables may have their statistics gathered when there is an unrepresentative number of rows present. These situations can be avoided by using one of two methods.
The current statistics can be deleted and locked to prevent DBMS_STATS
from gathering new statistics. If the OPTIMIZER_DYNAMIC_SAMPLING
parameter is set to 2 (the default) or higher the necessary statistics will be gathered as part of the query optimization stage (See Dynamic Sampling).
BEGIN DBMS_STATS.delete_table_stats('MY_SCHEMA','LOAD_TABLE'); DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE'); END; /
The statistics can be gathered then locked at a time when the table contains the appropriate data.
BEGIN DBMS_STATS.gather_table_stats('MY_SCHEMA','LOAD_TABLE'); DBMS_STATS.lock_table_stats('MY_SCHEMA','LOAD_TABLE'); END; /
System statistics and statistics for fixed object, such as dynamic performance tables, are not gathered automatically.
The default behaviour of the procedures in the DBMS_STATS package, and therefore the automatic stats collection job, can be altered using the SET_DATABASSE_PREFS
, SET_SCHEMA_PREFS
and SET_TABLE_PREFS
procedures.
Dynamic sampling enables the server to improve performance by:
Dynamic sampling is controlled by the OPTIMIZER_DYNAMIC_SAMPLING
parameter which accepts values from "0" (off) to "10" (aggressive sampling) with a default value of "2". At compile-time Oracle determines if dynamic sampling would improve query performance. If so it issues recursive statements to estimate the necessary statistics. Dynamic sampling can be beneficial in the following ways.
In addition to the OPTIMIZER_DYNAMIC_SAMPLING
system parameter the dynamic sampling level can be set using the DYNAMIC_SAMPLING
optimizer hint for specific queries like the following.
SELECT /*+ dynamic_sampling(emp 10) */ empno, ename, job, sal FROM emp WHERE deptno = 30;
The results of dynamic sampling are repeatable provided no rows are inserted, updated or deleted from the sampled table. The OPTIMIZER_FEATURES_ENABLE
parameter will turns off dynamic sampling if it is set to a version earlier than 9.2.0.
By default the cost model for the optimizer is now CPU+I/O, with the cost unit as time.
New hints:
SPREAD_MIN_ANALYSIS
- Specifies analysis options for spreadsheets.USE_NL_WITH_INDEX
- Specifies a nested loops join.QB_NAME
- Specifies a name for a query block.NO_QUERY_TRANSFORMATION
- Prevents the optimizer performing query transformations.NO_USE_NL
, NO_USE_MERGE
, NO_USE_HASH
, NO_INDEX_FFS
, NO_INDEX_SS
and
NO_STAR_TRANSFORMATION
- Excludes specific operations from the query plan.INDEX_SS
, INDEX_SS_ASC
, INDEX_SS_DESC
- Excludes range scans from the query plan.Updated hints:
Renamed hints:
NO_PARALLEL
- Formally NOPARALLEL
.NO_PARALLEL_INDEX
- Formally NOPARALLEL_INDEX
.NO_REWRITE
- Formally NOREWRITE
.Deprecated hints:
AND_EQUAL
HASH_AJ
MERGE_AJ
NL_AJ
HASH_SJ
NL_SJ
EXPAND_GSET_TO_UNION
ORDERED_PREDICATES
ROWID
STAR
The Rule Based Optimizer (RBO) is now obsolete in Oracle 10g. The functionality is still present but no new functionality has been included in it and it is no longer supported by Oracle. It is only present to provide backwards compatibility during the migration to the query optimizer (Cost Based Optimizer). The results of this obsolescence are as follows.
CHOOSE
and RULE
options for the OPTIMIZER_MODE
parameter still exist but are no longer supported.OPTIMIZER_MODE
parameter is ALL_ROWS
.CHOOSE
and RULE
optimizer hints still exist but are no longer supported.The Oracle Trace functionality has been removed from Oracle 10g. Instead the SQL Trace and TKPROF functionality should be used.
In multi-tier environments where statements are passed to different sessions by the application server it can become difficult to trace an individual process from start to finish. To solve this problem Oracle have introduced End to End Application Tracing which allows a client process to be identified via the client identifier rather than the typical session id. Each piece of trace information is linked to the following information.
DBMS_SESSION.SET_IDENTIFIER
procedure.DBMS_SERVICE.CREATE_SERVICE
procedure.DBMS_APPLICATION_INFO.SET_MODULE
procedure.DBMS_APPLICATION_INFO.SET_ACTION
procedure.End to end tracing can be managed via Enterprise Manager or a set of APIs and views. Here are some examples of how to enable and disable to various types of tracing.
BEGIN -- Enable/Disable Client Identifier Trace. DBMS_MONITOR.client_id_trace_enable (client_id => 'my_id'); DBMS_MONITOR.client_id_trace_disable (client_id => 'my_id'); -- Enable/Disable Service, Module and Action Trace (various overloads). DBMS_MONITOR.serv_mod_act_trace_enable ( service_name => 'my_service'); DBMS_MONITOR.serv_mod_act_trace_enable ( service_name => 'my_service', module_name => 'my_module'); DBMS_MONITOR.serv_mod_act_trace_enable ( service_name => 'my_service', module_name => 'my_module', action_name => 'INSERT'); DBMS_MONITOR.serv_mod_act_trace_disable ( service_name => 'my_service', module_name => 'my_module', action_name => 'INSERT'); -- Enable/Disable Session Trace (various overloads). DBMS_MONITOR.session_trace_enable; DBMS_MONITOR.session_trace_enable ( session_id => 15, serial_num => 1234); DBMS_MONITOR.session_trace_disable ( session_id => 15, serial_num => 1234); END; /
Once the trace files are produced the trcsess
command line utility can be used to filter out the relevant data from multiple files. The utility accepts the following parameters:
OUTPUT
- Specifies the name of the consolidated trace file.SESSION
- Consolidates the file based on the specified session id (SID.SERIAL#
columns from V$SESSION
).CLIENT_ID
- Consolidates the file based on the specified client identifier (CLIENT_IDENTIFIER
column from V$SESSION
).SERVICE
- Consolidates the file based on the specified service (SERVICE_NAME
column from V$SESSION
).MODULE
- Consolidates the file based on the specified module (MODULE
column from V$SESSION
).ACTION
- Consolidates the file based on the specified action (ACTION
column from V$SESSION
).TRACE_FILES
- A space separated list of trace files to be searched. If omitted all files in the local directory are searched.At lease one of the search criteria must be specified. If more than one is specified only trace that matches all the criteria is consolidated. Examples of trcsess
usage are shown below.
# Search all files for this session. trcsess output=session.trc session=144.2274 # Search the specified files for this client identifier. trcsess output=client.trc client_id=my_id db10g_ora_198.trc db10g_ora_206.trc # Search the specified files for this service, module and action combination. trcsess output=client.trc service=my_service module=my_module action=INSERT db10g_ora_198.trc db10g_ora_206.trc
Once the consolidated trace file is produced it can be processed by the TKPROF
utility like any other SQL Trace file.
By default statistics are gathered at the session level. The DBMS_MONITOR
package allows this to be altered to follow the client identifier, service or combinations of the service, module and action.
BEGIN -- Enable/Disable Client Identifier Statistics. DBMS_MONITOR.client_id_stat_enable (client_id => 'my_id'); DBMS_MONITOR.client_id_stat_disable (client_id => 'my_id'); -- Enable/Disable Service, Module and Action Statistics (various overloads). DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => 'my_service'); DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => 'my_service', module_name => 'my_module'); DBMS_MONITOR.serv_mod_act_stat_enable ( service_name => 'my_service', module_name => 'my_module', action_name => 'INSERT'); DBMS_MONITOR.serv_mod_act_stat_disable ( service_name => 'my_service', module_name => 'my_module', action_name => 'INSERT'); END; /
The gathered statistics can be displayed using the following views.
DBA_ENABLED_AGGREGATIONS
- Accumulated global statistics.V$CLIENT_STATS
- Accumulated statistics for the specified client identifier.V$SERVICE_STATS
- Accumulated statistics for the specified service.V$SERV_MOD_ACT_STATS
- Accumulated statistics for the specified service, module and action combination.V$SVCMETRIC
- Accumulated statistics for elapsed time of database calls and CPU usage.The SAMPLE
clause allows a query to return a limited sample of data by specifying a percentage of rows or blocks to scan. This clause can now be present in complex queries.
-- Query 10% or rows. SELECT e.empno, e.ename, d.dname FROM emp SAMPLE (10) e JOIN dept d ON e.deptno = d.deptno; -- Query 10% of blocks. SELECT e.empno, e.ename, d.dname FROM emp SAMPLE BLOCK (10) e JOIN dept d ON e.deptno = d.deptno;
Support for hash partitioned global indexes has been added in Oracle 10g which can improve performance when a small number of leaf blocks are experiencing high levels of contention. The syntax for creating of a hash partitioned global index is shown below.
CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARTITION BY HASH (c1,c2) (PARTITION p1 TABLESPACE tbs_1, PARTITION p2 TABLESPACE tbs_2, PARTITION p3 TABLESPACE tbs_3, PARTITION p4 TABLESPACE tbs_4);
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/10g/performance-tuning-enhancements-10g