Oracle Performance Tuning

Statistics

The Oracle Optimizer examines all of the possible plans for a SQL statement and picks the one with the lowest cost, where cost represents the estimated resource usage for a given plan. In order for the optimizer to accurately determine the cost for an execution plan it must have information about all of the objects (table and indexes) accessed in the SQL statement as well as information about the system on which the SQL statement will be run.

This necessary information is commonly referred to as optimizer statistics. Understanding and managing optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to maintaining acceptable performance.

» How to gather statistics 

» When to gather statistics 

» Improving the quality of statistics 

» Gathering statistics more quickly 

» When not to gather statistics 

» Gathering other types of statistics

select table_name,num_rows,last_analyzed from dba_tables where owner='SCOTT';

Automatic Statistics Gathering

The Oracle database collects statistics for database objects that are missing statistics or have “stale” (out of date) statistics. This is done by an automatic task that executes during a predefined maintenance window. Oracle internally prioritizes the database objects that require statistics, so that those objects, which most need updated statistics, are processed first. 

The automatic statistics-gathering job uses the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure, which uses the same default parameter values as the other DBMS_STATS.GATHER_*_STATS procedures. The defaults are sufficient in most cases. However, it is occasionally necessary to change the default value of one of the statistics gathering parameters, which can be accomplished by using the DBMS_STATS.SET_*_PREF procedures. Parameter values should be changed at the smallest scope possible, ideally on a per-object basis. For example, if you want to change the staleness threshold for a specific table, so its statistics are considered stale when only 5% of the rows in the table have changed rather than the default 10%, you can change the STALE_PERCENT table preference for that one table using the DBMS_STATS.SET_TABLE_PREFS procedure. By changing the default value at the smallest scope you limit the amount of non-default parameter values that need to be manually managed. For example, here’s how you can change STALE_PRECENT to 5% on the SALES table:

exec dbms_stats.set_table_prefs(user,'SALES','STALE_PERCENT','5');

To check what preferences have been set, you can use the DBMS_STATS.GET_PREFS function. It takes three arguments; the name of the parameter, the schema name, and the table name:

SQL> select dbms_stats.get_prefs('STALE_PERCENT','SCOTT','EMP') stale_percent from dual;

STALE_PERCENT

--------------------

10

Setting DBMS_STATS Preferences

It is possible to set DBMS_STATS preferences to target specific objects and schemas to modify the behavior of auto statistics gathering where necessary. You can specify a particular non-default parameter value for an individual DBMS_STATS.GATHER_*_STATS command, but the recommended approach is to override the defaults where necessary using “targeted” DBMS_STATS.SET_*_PREFS procedures.

A parameter override can be specified at a table, schema, database, or global level using one of the following procedures (noting that AUTOSTATS_TARGET and CONCURRENT can only be modified at the global level): 

SET_TABLE_PREFS 

SET_SCHEMA_PREFS 

SET_DATABASE_PREFS 

SET_GLOBAL_PREFS 

Traditionally, the most commonly overridden preferences have been ESTIMATE_PERCENT (to control the percentage of rows sampled) and METHOD_OPT (to control histogram creation), but estimate percent is now better left at its default value for reasons.

The SET_TABLE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for the specified table only.

The SET_SCHEMA_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the existing tables in the specified schema. This procedure actually calls the SET_TABLE_PREFS procedure for each of the tables in the specified schema. Since it uses SET_TABLE_PREFS, calling this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL preference values for all parameters.

The SET_DATABASE_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for all of the user-defined schemas in the database. This procedure actually calls the SET_TABLE_PREFS procedure for each table in each user-defined schema. Since it uses SET_TABLE_PREFS, this procedure will not affect any new objects created after it has been run. New objects will pick up the GLOBAL preference values for all parameters. It is also possible to include the Oracle owned schemas (sys, system, etc) by setting the ADD_SYS parameter to TRUE.

The SET_GLOBAL_PREFS procedure allows you to change the default values of the parameters used by the DBMS_STATS.GATHER_*_STATS procedures for any object in the database that does not have an existing table preference. All parameters default to the global setting unless there is a table preference set, or the parameter is explicitly set in the GATHER_*_STATS command. Changes made by this procedure will affect any new objects created after it has been run. New objects will pick up the GLOBAL_PREFS values for all parameters.

The DBMS_STATS.GATHER_*_STATS procedures and the automated statistics gathering task obeys the following hierarchy for parameter values; parameter values explicitly set in the command overrule everything else. If the parameter has not been set in the command, we check for a table level preference. If there is no table preference set, we use the GLOBAL preference.

 

Oracle Database 12 Release 2 includes a new DBMS_STATS preference called PREFERENCE_OVERRIDES_PARAMETER. When this preference is set to TRUE, it allows preference settings to override DBMS_STATS parameter values. For example, if the global preference ESTIMATE_PERCENT is set to DBMS_STATS.AUTO_SAMPLE_SIZE, it means that this best-practice setting will be used even if existing manual statistics gathering procedures use a different parameter setting (for example, a fixed percentage sample size such as 10%).

Ahmad Barakah

===========

9.

Performance Issue Attributes

• Scope (entire db or part of it, specific functionality, session, user, batch, action etc.)
• Very slow or hang
• Permanent or intermittent
• Is it reproducible
• Specific Errors :
  ORA-00257: archiver error, connect internal only, until freed
  ORA-04031: unable to allocate n bytes of shared memory (due to small shared pool)

10. Performance Tuning Methodology

    1. Set a mesurable target (ask questions to the user)

    2. Discover the symptoms (generate comparison awr reports) (awr, addm, ash, v$, os level)

    3. Determine possible causes

    4. Develop a trial solution (follow addm recom, create index etc)

    5. Test the solution - Target Hit - End - No then go to step 3 and repeat

11. A Typical System Workload Example

    Number of users         : 100

    Transaction rate          : 1 trx per 5 min per user

    Business hours            : 8 hours in 5 working days (5x8)

    Calculated rate            : 20 trx per min, 9600 transactions daily 

    Peak rate                      : 120 trx per minute

12. 

    Server Performance Measurement Aspects

    1. CPU

    2. Memory

    3. Storage

    4. Network

    All above are physical specifications

14. Performance Tuning Tools

• v$ views and data dictionary views
• Automatic Workload Repository (AWR)
• Statspack reports
• Automatic Database Diagnostic Monitor (ADDM)
• Performance tuning advisors
• alertlog file
• Trace files
• Enterprise Manager Cloud Control and EM Database Express

Types of Performance Tuning Statistics

• Cumulative statistics: mostly have no meaning without the time factor 
• Time model
• Wait events
• Metrics: statistic rates
• Sampled statistics
• ADDM, AWR, Active Session History (ASH)

Setting Statistics Level

• STATISTICS_LEVEL controls the level of collecting database and operating system statistics. 
• BASIC: Most important performance statistics are not collected
• TYPICAL: All major statistics are collected
• ALL: Timed operating system statistics and execution plan statistics are added
• Can be altered at the system and session levels
• To know which parameter is affected by setting this parameter, query the view V$STATISTICS_LEVEL

Section: 6 Using Time Model 

15. 

DB Time

        Idle waiting

        Perform processing (DB CPU) once receive request from the clients by using cpu, ram, disk

        Waiting for a resource (DB waits)

DB Time = performing processing + Waiting for a resource

DB Time = DB CPU + total DB waits

Time Mode = DB Time = SQL + Connect + Java + PLSQL + RMAN + Parse

16. About Time Model 

• Is a set of statistics that give an overview on which operation types the DB time is spent inside the Oracle database
• Available in: V$SYS_TIME_MODEL and V$SESS_TIME_MODEL
• DB time represents the time the database spent on executing the user calls (DB CPU and DB waits)
• Statistics are accumulated since instance startup
• Benefits:
• Single indicator on the entire database workload and performance
• Study the impact of a change on the entire database performance
• May trigger investigating into performance issues

17. Time Model Hierarchy

DB time

    DB CPU

    connection management call elapsed time

    sequence load elapsed time

    sql execute elapsed time

    parse time elapsed

        hard parse elapsed time

            hard parse (sharing criteria) elapsed time

                hard parse (bind mismatch) elapsed time

        failed parse elapsed time

            failed parse (out of shared memory) elapsed time

    PL/SQL execution elapsed time

    inbound PL/SQL rpc elapsed time

    PL/SQL compilation elapsed time

    repeated bind elapsed time

background elapsed time

    background cpu time

        RMAN cpu time (backup/restore)

Database Time Model Query Example

select stat_name, to_char(value/10000000, '999,999') time_s
from v$sys_time_model

where value <> 0 and stat_name not in ('background elapsed time', 'background cpu time')

order by value desc;

Sessions Time Model Query Example

select s.sid, s.username, t.stat_name, 

round(t.value/1000000,2) "Time (SEC)"

from v$sess_time_model t, v$session s

where t.sid = s.sid and t.stat_name in ('DB time', 'DB CPU')

and s.username is not null

order by t.value desc;

Using Time Model to Measure System Scalability

select to_char(dbtime.value/1000000,'999,999') dbtime,

to_char(dbcpu.value/1000000,'999,999') dbcpu,

to_char((dbtime.value-dbcpu.value)/1000000,'999,999') wait_time,

to_char((dbtime.value-dbcpu.value)/dbtime.value*100,'99.99') || '%' wait_pct,

(select count(*) from v$session where username is not null) users_cnt

 from v$sys_time_model dbtime, v$sys_time_model dbcpu

 where dbtime.stat_name = 'DB time' and dbcpu.stat_name = 'DB CPU';

Historical System Time Model Statistics

• Can be retrieved from DBA_HIST_SYS_TIME_MODEL
• Has an advantage over reading from the V$ views.

18. Using Time Model

Lot of good commands will be created in the scripts folder

Note: We got scripts folder and .pdf file to help this lesson

19. Using Time Model

Section 7: Using Instance Activity Statistics

20. Using Instance Activity Statistics 1

About Instance Activity and Wait Events

• Instance activity statistics:
• Quantitative measures on the instance activities
• Their figures (at the system level) can be obtained from V$SYSSTAT
• Each statistic can belong to one or more from 8 statistic classes
• Their names are described in V$STATNAME

Instance Activity Statistic Views

• System:
• V$SYSSTAT
• Services:
• V$SERVICE_STATS
• Sessions:
• V$SESSTAT
• V$MYSTAT
• Note: Usually linked with V$STATNAME
• Segment:
• V$SEGMENT_STATISTICS

21. Using Instance Activity Statistics 2

System Statistic Classes

Class Number    Class Name

-----------------------------------

1        User

2        Redo

4        Enqueue

8        Cache

16      OS

32      Real Application Cluster

64      SQL

128    Debug

• Can be retrieved from the CLASS column in V$SYSSTAT and V$STATNAME views (V$SESSTAT view does not have it)

Instance Activity Query Example

select name, class, value from v$sysstat;

Obtaining Activity Statistics of Specific Statements

---(1) obtain the current statistics value from v$mystat:

select s.value into :value1

from v$mystat s, v$statname n

where s.statistics# = n.statistic#

and name = 'CPU used by this session';

--(2) execute the statements:

--(3) obtain the current statistics value from v$mystat:

select s.value into :value2

from v$mystat s, v$statname n

where s.statistic# = n.statistic#

and name = 'CPU used by this session';

--(4) substract values obtained in (3) from (1)

:delta := :value2 - :value1

Section 8: Wait Events

=================