8.3. Using Automatic Routine Administration Tasks

The third element in the CMI is the automatic routine administration functionality designed into Oracle 10g. Recent surveys of database administrators show that most of them spend over 50 percent of their time on routine administration functions. In response, Oracle automated many of the most time-consuming tasks, leaving you with more time to focus on other issues.

In this section, we'll discuss the automatic statistics collection feature, which automates optimizer statistics collection and maintenance. You'll also be introduced to the automatic Database Manipulation Language (DML) monitoring functionality that detects stale statistics on database objects.

8.3.1. Automatic Statistics Collection

Though optimizer statistics can be generated manually using the DBMS_STATS package, Oracle can do the job for you automatically through the use of its automatic statistics collection functionality. This is Oracle's recommended method for optimizer statistics collection.

Successful Oracle performance tuning relies heavily on the availability of accurate optimizer statistics. Optimizer statistics store detailed information about tables and indexes in your database. They provide the data that Oracle's query optimizer needs to generate efficient execution plans for SQL statements.

The different types of optimizer statistics include the following:

Dictionary statistics Dictionary statistics, new to Oracle 10g, are statistics that can be captured on Oracle's data dictionary objects. Because data dictionary tables are queried heavily by Oracle, having up-to-date statistics on these tables can dramatically improve overall performance.

System statistics System statistics provide the query optimizer with information on hardware characteristics such as CPU performance and disk I/O. This enables the query optimizer to estimate hardware costs more accurately when generating execution plans.

Operating system statistics Operating system statistics, new to Oracle 10g, offer operating system–level statistics, which were previously unavailable inside Oracle.

User-defined statistics User-defined statistics provide the optimizer with statistics on non-standard objects such as user-defined functions and packages, domain indexes, index types, datatypes, and others. User-defined statistics are used in conjunction with Oracle data cartridges and the Oracle Extensibility Services framework.

In the following sections, we'll provide an overview of the many types of optimizer statistics and their purpose. You'll look at the automatic collection process and the automatic DML monitoring process. To finish, we'll discuss the STATISTICS_LEVEL initialization parameter and how it affects automatic statistics collection.

8.3.1.1. Optimizer Statistics

Oracle's query optimizer is tasked with choosing the optimum execution plan for SQL statements executed against the database. To do this, it needs accurate optimizer statistics for all objects that may be used to satisfy a SQL statement. These objects include tables, columns, and indexes.

The optimizer also needs information on the system itself, such as CPU configuration and I/O speed, to apply weights to different execution methods. This information is supplied through the collection of system statistics.

Optimizer statistics differ based on the type of object on which they are collected, as shown here:

• Tables

  • Number of rows

  • Number of blocks

  • Average row length

• Columns

  • Number of distinct values in the column

  • Number of nulls in the column

  • Histograms (distribution of data)

• Indexes

  • Number of leaf blocks in the index

  • Levels

  • Clustering factor

• System

  • I/O performance/utilization

  • CPU performance/utilization

When optimizer statistics are collected on an object, Oracle will invalidate all previously parsed SQL statements that access the object. This will force the optimizer to generate a new execution plan based on the updated statistics.

The availability of accurate and up-to-date optimizer statistics significantly increases the chances that the query optimizer will generate an efficient execution plan. However, missing or stale optimizer statistics will often result in the optimizer choosing inefficient (or downright stupid) execution plans.

Let's look at a simple example of this. First, create a table called SALES and insert five rows into it:

SQL> create table sales
as select * from sh.sales
where rownum < 6 ;

Table created.

Next, use DBMS_STATS to gather statistics on the table:

SQL> exec dbms_stats.gather_table_stats(null, 'SALES'),

PL/SQL procedure successfully completed.

Now, insert 40,000 rows into the table and create an index on the CUST_ID column:

SQL> insert into sales
select * from sh.sales
where rownum <= 40000;

40000 rows inserted.

SQL> commit;

Commit complete.

SQL> create index sales_idx on sales(cust_id)
   tablespace index;

Index created.

Now, suppose you want to execute the following query:

select *
From sample_table
Where client_id = 1234567;

Before executing this query, run EXPLAIN PLAN on it and view the results (note that the EXPLAIN PLAN output has been trimmed for space):

SQL>   explain plan
2  for
3  select * from sales
4  where cust_id = 123;

Explained.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------

Plan hash value: 3099465693

------------------------------------------------
| Id | Operation        | Name  | Rows | Bytes |
------------------------------------------------
|  0 | SELECT STATEMENT |       |    1 |    30 |
|* 1 | TABLE ACCESS FULL| SALES |    1 |    30 |
------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
-------------------------------------------------------
   1 - filter("CUST_ID"=123)

13 rows selected.

You can see that the query optimizer decided to ignore the index and to use a full table scan to fulfill this query because you didn't update the statistics after inserting the new rows. Even though you had drastically increased the number of rows, according to the statistics, the table still only has five rows in it. Therefore, it decided that a full table scan would be quicker than using the index (which would be true for a table with only five rows).

To verify this, gather the statistics and try it again:

SQL> exec dbms_stats.gather_table_stats(null, 'SALES'),

PL/SQL procedure successfully completed.

SQL> delete from plan_table;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> explain plan
2  for
3  select * from sales
4  where cust_id = 123;

Explained.

SQL> @utlxpls

PLAN_TABLE_OUTPUT
-------------------------------------------------------------

Plan hash value: 3208976390

-------------------------------------------------------------
| Id | Operation                  | Name       | Rows
-------------------------------------------------------------
|  0 | SELECT STATEMENT           |            |
|  1 | TABLE ACCESS BY INDEX ROWID| SALES      |
|* 2 | INDEX RANGE SCAN           | SALES_CUST |
-------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
-------------------------------------------------------------
2 - access("CUST_ID"=123)

14 rows selected.

This time, armed with accurate statistics, the optimizer correctly chose to make use of the index over the full table scan.

Optimizer statistics are stored in data dictionary tables and are therefore static in the sense that they will not be lost if the instance shuts down. They can be viewed using a wide variety of data dictionary views (DBA_*, ALL_*, and USER_*). The specific views are covered later in this chapter.

8.3.1.2. Dictionary Statistics

Oracle 10g is the first Oracle release to allow the collection of statistics on dictionary tables, both real and fixed. Because dictionary tables are heavily accessed by Oracle, the availability of these statistics can have a great impact on overall system performance. Note that because fixed tables reside only in memory, they don't have an I/O cost associated with them. Instead, the CPU cost of accessing these rows is gathered.

8.3.1.3. System Statistics

In order for the query optimizer to build optimal SQL execution plans, it needs more than just object statistics. It also needs to know something about the hardware on which it is running (in other words, CPU and I/O performance). It needs this information in order to assign weights to specific resources for comparison purposes.

This information is made available to the query optimizer through the use of system statistics. Armed with this information, Oracle can make more accurate resource estimates and, subsequently, more efficient execution plans.

As mentioned previously, system statistics are classified as optimizer statistics. Unlike other optimizer statistics, however, Oracle does not invalidate previously parsed SQL statements (in the library cache) when system statistics get updated. Only new SQL statements will be parsed using the new statistics.

System statistics can be viewed using either the V$SYSSTAT or V$SESSTAT views, as shown here:

SQL> select statistic#, name, value from v$sysstat
where rownum <=20;

STATISTIC# NAME                      VALUE
------------------------------------------
0 logons cumulative                   7811
1 logons current                       964
2 opened cursors cumulative        2115614
3 opened cursors current               653
4 user commits                       52290
5 user rollbacks                      3965
6 user calls                        801463
7 recursive calls                 74932775
8 recursive cpu usage              1090165
9 session logical reads           39069641
10 session stored procedure space        0
11 CPU used when call started      1284787
12 CPU used by this session        1281547
13 DB time                        91184061
14 cluster wait time                     0
15 concurrency wait time              4139
16 application wait time              3569
17 user I/O wait time                88034
18 session connect time         7.1900E+12
19 process last non-idle time   7.1900E+12
20 rows selected.

As you can see, the V$SYSSTAT view allows you to access system statistics values stored in dynamic performance tables.

8.3.1.4. Operating System Statistics

Oracle 10g has also added a new category of system statistics known as operating system statistics. Operating system statistics provide CPU, memory, and file system utilization data from the operating system. Many of these statistics were not available in previous versions of Oracle, making it difficult to investigate hardware-related issues. Instead, you had to rely on operating system–specific tools called from outside of Oracle.

Operating system statistics are described in Table 8.10 and can be seen through the V$OSSTAT view.

Table 8.10. V$OSSTAT View Operating System Statistics

Statistic Name	Description
NUM_CPUS	Number of CPUs
IDLE_TICKS	Hundredths of a second that a processor has been idle, over all
processors
BUSY_TICKS	Hundredths of a second that a processor has been busy executing user or kernel code, over all processors
USER_TICKS	Hundredths of a second that a processor has been busy executing user code, over all processors
SYS_TICKS	Hundredths of a second that a processor has been busy executing kernel code, over all processors
IOWAIT_TICKS	Hundredths of a second that a processor has been waiting for I/O to complete, over all processors
NICE_TICKS	Hundredths of a second that a processor has been busy executing low-priority user code, over all processors
AVG_IDLE_TICKS	Hundredths of a second that a processor has been idle, averaged over all processors
AVG_BUSY_TICKS	Hundredths of a second that a processor has been busy executing user or kernel code, averaged over all processors
AVG_USER_TICKS	Hundredths of a second that a processor has been busy executing user code, averaged over all processors
AVG_SYS_TICKS	Hundredths of a second that a processor has been busy executing kernel code, averaged over all processors
AVG_IOWAIT_TICKS	Hundredths of a second that a processor has been waiting for I/O to complete, averaged over all processors
AVG_NICE_TICKS	Hundredths of a second that a processor has been busy executing low-priority user code, averaged over all processors
OS_CPU_WAIT_TIME	Hundredths of a second that processes have been in a ready state (waiting to run)
RSRC_MGR_CPU_WAIT_TIME	Hundredths of a second that Oracle processes have waited for CPU allocation for their consumer group (in the currently active resource plan)
IN_BYTES	Total number of bytes that have been paged in
OUT_BYTES	Total number of bytes that have been paged out
FS_IN_BYTES	Total number of bytes that have been paged in due to the file system
FS_OUT_BYTES	Total number of bytes that have been paged out due to the file system
AVG_IN_BYTES	Number of bytes that have been paged in, averaged over all processors
AVG_OUT_BYTES	Total number of bytes that have been paged out, averaged over all processors
AVG_FS_IN_BYTES	Total number of bytes that have been paged in due to the file system, averaged over all processors
AVG_FS_OUT_BYTES	Total number of bytes that have been paged out due to the file system, averaged over all processors

8.3.1.5. User-Defined Statistics

Oracle 10g can be described as an object-relational database. This means that, in addition to the standard relational model, Oracle supports data organized under the object model. The object model allows users the flexibility to define their own objects for data storage and manipulation. It also allows them to define new datatypes to supplement the native types.

Obviously, most standard functions and index types won't know how to deal with data stored in a non-native format. Therefore, Oracle allows the creation of functions, indexes, and so on to support these new datatypes. For example, you can create your own set of aggregate functions, such as min() and max(), that will operate on custom data stored in a character large object (CLOB).

To support these user-defined objects, Oracle also allows the creation of user-defined statistics to support the optimizer in generating efficient execution plans.

8.3.1.6. Collecting Optimizer Statistics

Automated statistics collection is automatic in the truest sense of the term. No action is required on your part at all. When a database is created in Oracle 10g, Oracle creates a job called GATHER_STATS_JOB in the scheduler. This job runs whenever the Maintenance window (defined in the scheduler) is opened.

If the job is still running when the Maintenance window closes, it will continue to run until completion. By default, the Maintenance window is open weeknights from 10:00 P.M. until 6:00 A.M., and all day long on the weekends.

The GATHER_STATS_JOB job utilizes an internal procedure named DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC to gather the statistics. This procedure detects objects that have either no statistics or statistics that it deems stale. It then prioritizes them (so that those objects most in need will get processed first) and begins collecting statistics.

The DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure is similar to the DBMS_STATS.GATHER_DATABASE_STATS procedure when called with the GATHER AUTO option. The major difference is that the latter performs no prioritization.

To verify that the job exists, query the DBA_SCHEDULER_JOBS view:

SELECT *
FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME = 'GATHER_STATS_JOB';

If you need to turn off automated statistics collection, simply disable the job by using the DBMS_SCHEDULER.DISABLE function:

BEGIN
  DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB'),
END;
/

8.3.1.7. Managing Volatile Object Statistic

While automatic statistics collection works well for most tables, it falls short when dealing with volatile objects. Volatile objects are objects that can drastically change in size over the course of a day. For example, tables that are the target of a bulk-load operation (where the number of rows increases by 10 percent or more) would be considered volatile. Also, tables that are truncated or dropped, and then rebuilt, would also be considered volatile. Volatile objects run the risk of having no statistics (if they were dropped and rebuilt) or having inaccurate statistics.

Oracle 10g offers several options for dealing with volatile objects. The first, and most obvious, is to simply gather the statistics manually using DBMS_STATS. Beyond that however, there are two other options.

The first option is to set statistics to NULL. As part of Oracle's query optimization, any table with no statistics will have them generated dynamically via the dynamic sampling feature. This "just-in-time" statistics generation ensures that no query will be executed without statistics. The parameter OPTIMIZER_DYNAMIC_SAMPLING needs to be set to a value of 2 (the default) or higher to enable this feature.

To set statistics for a table to NULL, delete and lock the statistics as shown:

SQL> exec DBMS_STATS.DELETE_TABLE_STATS('BUTERTB', 'VOLATILE_TABLE'),

PL/SQL procedure successfully completed

SQL> exec DBMS_STATS.LOCK_TABLE_STATS('BUTERTB', 'VOLATILE_TABLE'),

PL/SQL procedure successfully completed

The second option is to set statistics to values that are typical for the table and lock them. To achieve this, gather statistics when the table is at a typical size. When complete, lock the table's statistics, as shown in the preceding example.

8.3.1.8. Monitoring DML Table Changes

Oracle 10g changes the method in which Oracle monitors tables for DML changes. Instead of using the [NO]MONITORING clause in the CREATE TABLE or ALTER TABLE statements, the STATISTICS_LEVEL initialization parameter now acts as a global switch to control monitoring for all objects. It is no longer necessary to enable/disable DML monitoring at the object level.

The DML monitoring feature keeps track of all DML activity (INSERT, UPDATE, DELETE) against a table since statistics were last gathered. The System Monitor (SMON) process will periodically (approximately every three hours) update the data dictionary with this information. Oracle can then use this data to identify when statistics are stale.

Monitoring data can be viewed using the DBA_TAB_MODIFICATIONS, ALL_TAB_MODIFICATIONS, or USER_TAB_MODIFICATIONS views. The ALL_TAB_MODIFICATIONS view is described in Table 8.11.

Table 8.11. ALL_TAB_MODIFICATIONS View Columns

Column	Description
TABLE_OWNER	Owner of the modified table
TABLE_NAME	Name of the modified table
PARTITION_NAME	Name of the modified partition
SUBPARTITION_NAME	Name of the modified subpartition
INSERTS	Approximate number of inserts since the last time statistics were gathered
UPDATES	Approximate number of updates since the last time statistics were gathered
DELETES	Approximate number of deletes since the last time statistics were gathered
TIMESTAMP	The last time the table was modified
DROP_SEGMENTS	Number of partition and subpartition segments dropped since the last analyze

8.3.1.9. Altering the STATISTICS_LEVEL Parameter

The automatic statistics collection functionality is controlled by the setting of the STATISTICS_LEVEL initialization parameter. This parameter can be set to one of three values: BASIC, TYPICAL (the default), and ALL.

BASIC This setting disables the collection of many important statistics that Oracle requires in order to provide the following functionality:

• ADDM

• Automatic optimizer statistics collection

• Automatic SGA memory management

• AWR snapshots

• Buffer cache advisory

• Database time distribution statistics

• End-to-end application tracing

• Monitoring of statistics

• MTTR advisory

• Object-level statistics

• PGA target advisory

• Segment-level statistics

• Server-generated alerts

• Service-level statistics

• Shared Pool sizing advisory

• Timed statistics

TYPICAL This setting ensures collection of all major statistics that the database needs for self-management. It also provides the best performance overall. Oracle suggests that the default value of TYPICAL should be adequate for most environments.

ALL This setting results in the gathering of all of the TYPICAL statistics, as well as the operating system and plan execution statistics.

The STATISTICS_LEVEL setting can be dynamically altered at the system level using the ALTER SYSTEM statement or at the session level using the ALTER SESSION statement. However, the ALTER SESSION statement only affects the following statistics within the specified session:

• Timed statistics

• Timed operating system statistics

• Plan execution statistics

The rest of the statistics for the session are gathered as always, because they are needed by Oracle.

8.3.1.10. Viewing the Statistics Level

The V$STATISTICS_LEVEL view can be used to see which statistics are captured at each level. Table 8.12 lists the columns available in the V$STATISTICS_LEVEL view.

Table 8.12. V$STATISTICS_LEVEL View

Column Name	Description
DESCRIPTION	Description of statistics/advisory. May also list available views for the category (see STATISTICS_VIEW_NAME for details).
SESSION_STATUS	Status of the current session for the statistic/advisory (ENABLED or DISABLED).
SYSTEM_STATU	System-wide status for the statistic/advisory (ENABLED or DISABLED).
ACTIVATION_LEVEL	STATISTICS_LEVEL setting at which the statistic/advisory is activated (BASIC, TYPICAL, or ALL).
STATISTICS_VIEW_NAME	If a single view exists for the statistic/advisory, it will be listed here. If more than one view exists, they will be listed in the DESCRIPTION column. If none exists, the column will be NULL.
SESSION_SETTABLE	Can the statistic/advisory be modified at the session level (YES or NO)?