The DB Time Model - Oracle

The DB time model is a way of looking at performance based on the overall time spent in the database. Time-based statistics are kept in two new views in Oracle Database 10g—V$SYS_TIME_MODEL and V$SESS_TIME_MODEL. While these views contain various different time-related stats, the most significant of these is DB time. The goal of any tuning activity should be to reduce that overall time. If the overall time spent in the database, in database calls, is reduced, then the tuning has been effective. ADDM will use the DB time model as a basis for findings and recommendations. In addition, automatic tuning of the SGA (discussed later in this chapter, in “Memory Advisor and ASMM”) will take DB time into consideration when deciding where to allocate SGA resources, with the goal being to reduce overall DB time.

Now that we are on the topic of the advisors, let’s go back and take a more detailed look at each of the advisors in the Advisor Central page. This page offers links to the following advisors, along with the ability to create a task for these advisors:

The SQL Tuning Advisor gives recommendations on changing SQL statements, to be rewritten so that they are more efficient. The SQL statements are referenced by a new column that is part of V$ACTIVE_SESSION_HISTORY, called SQL_ID. ADDM will detect statements that appear to be inefficient, and/or are leading to a disproportionate number of waits, and may suggest that you run the SQL Tuning Advisor against a given SQL statement. The SQL Tuning Advisor, when accessed from Advisor Central, will allow you to look at the top SQL for a specific period of time, create SQL tuning sets, or choose to view/create snapshots and/or baselines.

If a high-usage SQL statement shows up in an ADDM run, that statement will be displayed in the ADDM Analysis section of the EM database home page. Clicking on the statement will take you into a slightly different look, in the ADDM Finding Details view. From here, you will be able to run the SQL Tuning Advisor against the particular SQL statement.

The Top SQL link shows us by default the Spot SQL view, which shows five-minute intervals over the past hour. The highlighted section can be moved to display any five-minute interval over the past hour. The top SQL statements will be ordered by activity percentage, CPU percentage, and wait percentage, respectively, with the SQL_ID being the identifier for a given statement. The Period SQL link will allow a look at SQL statements over a longer period of time (a week), with the highlighted section giving you a look at a given 24-hour period within that week.

Clicking on the SQL_ID will give you an in-depth look at that particular statement, including the actual statement itself, the execution plan, current statistics, and execution history. The Current Statistics link has valuable information on the amount of space the statement takes in the shared_pool, as well as loads (parses) and invalidations, referring to the number of times this particular statement has been aged out of the shared_pool. A statement with a high number of loads and/or invalidations may be a candidate for pinning in the shared_pool.

SQL tuning sets can be created from within this page to allow a group of statements, either by type or by time period, to be analyzed. You may even want to create a tuning set based on just a single statement. Once the set is created, you can view the SQL statements by cost, buffer gets, disk reads, and so on. You can then choose to run either the SQL Tuning Advisor or the SQL Access Advisor against the tuning set. When choosing the criteria to create a tuning set, you might want to look at all of the statements in a given period of time, you may look at all SELECT statements within a period of time, or you may want to just grab the top one or two statements to create a tuning set.

The SQL Tuning Advisor will not give suggestions on adding or removing indexes, or making other physical changes. Changes like this would change the access method of a statement without changing the actual statement itself. For those types of recommendations, we refer you to the SQL Access Advisor. The SQL Access Advisor gives recommendations on either creating materialized views or creating indexes, or both, in order to give more efficient access for SQL statements. As input, you can use current and/or recent SQL activity that is still cached in memory. In addition, you can use a workload from the SQL Repository (requires that a SQL tuning set be created), or you can get a user-defined workload from a table. In addition, you can create a hypothetical workload, given certain inputs.

In Oracle 9i, the concept of a dynamically resizable SGA was introduced, giving a DBA the ability to adjust one SGA parameter downward and then increase another value upward in its place. The Memory Advisor in Oracle Database 10g allows shared memory management to be enabled, which essentially automates this same process—hence the term automatic shared memory management, or ASMM. When shared memory management is enabled, a new parameter, SGA_TARGET, is set to a numeric value that becomes the ceiling on the total SGA size. Once shared memory management is enabled, you are essentially allowing Oracle to automatically adjust the values that define the total SGA size, on the fly, up to the total for SGA_TARGET.

As an oversimplified example, consider if your total SGA size is 1GB, divided equally between db_cache, large_pool, shared_pool, and java_pool. This would essentially give you values of around 250MB for each of these parameters. With shared memory management enabled, Oracle will monitor the stats associated with these SGA values, and may decide to reduce one parameter in favor of increasing another. For example, suppose Oracle detects that during a certain interval, the buffer cache hit ratio is lower than 80 percent, while at the same time the amount of free memory in the large_pool is relatively high. Oracle may decide to reduce the large_pool_size by 50 percent (to 125MB), and then dynamically increase the db_cache_size by 50 percent to 375MB. The overall memory used for the SGA is still the same, meaning that we have not exceeded our ceiling (as defined by SGA_ TARGET). However, the distribution of that memory has been altered, with the overall effect of increasing performance by using that memory where it is most needed.

When ASMM is enabled (by setting the SGA_TARGET), you should explicitly set the automatically managed pieces of the SGA to 0. In Oracle Database 10g Release 1, this includes the four parameters mentioned above: shared_pool_size, large_pool_size, java_pool_size, and db_cache_size (only the default db_cache_ size is automatically tuned). All other SGA-related parameters should still be manually set. If you set one of the above parameters, defined as auto-tunable, to an explicit value in the spfile or init file, that value will be used as the floor for that parameter—meaning that while Oracle may be allowed to increase the amount of memory allocated to that particular piece of the SGA, it will not be allowed to drop below the explicitly set value. So, continuing with our example, if we set SGA_TARGET to 1GB, and then set each of the above parameters to an explicit value of 250MB, no automatic tuning can be done because the floor for each parameter adds up to the total of SGA_TARGET, which is our ceiling. This leaves no room to increase or decrease anything. For this reason, when SGA_TARGET is set via the Memory Advisor in Enterprise Manager, Enterprise Manager will automatically unset all of the other SGA parameters (that is, set them to 0 values), thus giving Oracle the freedom to modify all parameters on the fly as it sees fit (whether to higher values or lower values). You can view the current settings that Oracle has arrived at for each of the SGA components by querying the view V$SGA_DYNAMIC_COMPONENTS, and these values can also be viewed in the Memory Advisor on EM.

Non-ASMM Tunable SGA Components 

The above is an oversimplified example, as the SGA is, of course, comprised of more than just those four parameters. SGA_ TARGET must actually account for the entire SGA, including a new parameter in Oracle Database 10g: STREAMS_POOL_SIZE. Aside from that, SGA_TARGET must account for values for the LOG_BUFFER, any KEEP pools, the RECYCLE pool, and any nondefault buffer caches. However, the KEEP pools, the RECYCLE pool, nondefault buffer caches and Streams_Pool values are not currently auto-managed, so these parameters must be set explicitly, even while the other main components of the SGA should be set to 0.

---

NOTE

Oracle cannot increase the total SGA beyond the value for SGA_MAX_SIZE, which by default is set exactly equal to the total SGA size. If you attempt to increase the total SGA size (using the Memory Advisor Wizard) to a value greater than SGA_MAX_SIZE, you will receive an error.

---

Memory Advice Parameters

If you choose not to enable shared memory management, the Memory Advisor will still be able to give advice on sizes for parameters within the SGA, such as DB_ CACHE_SIZE and SHARED_POOL_SIZE, as well as provide advice on the setting of PGA_AGGREGATE_TARGET. This advice is enabled automatically if STATISTICS_ LEVEL is set to either TYPICAL or ALL. For advice on the buffer cache, you must also have the parameter DB_CACHE_ADVICE=ON (this will default to ON if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL).

The following views are populated when the above parameters are set to their default values:

v$db_cache_advice
v$shared_pool_advice
v$java_pool_advice
v$mttr_target_advice
v$pga_target_advice

This information is formatted and available in graphical format via the Memory Advisor on Enterprise Manager. By clicking on the Advice button next to the Buffer Cache values in the Memory Advisor, you will see calculations of what the difference would be in number of block reads for various values of DB_CACHE_SIZE. Similarly, the Advice button next to the Shared Pool value (see Figure 3-3) will give you estimates of parse times under different values of SHARED_POOL_SIZE.

By the same token, within the Memory Advisor, you can view estimates on the PGA workload by going to the PGA link (see Figure 3-3 again) and clicking on the Advice button there. This value, of course, depends on PGA_AGGREGATE_TARGET being set to a nonzero value in the init.ora (or spfile).

---

NOTE

Many of these parameters can be changed dynamically, both in memory and for future startups, if using an spfile. For example, SGA_TARGET, DB_CACHE_ADVICE, and STATISTICS_LEVEL can all be changed on the fly, and SGA parameters such as DB_CACHE_SIZE and SHARED_POOL_SIZE can all be lowered (set to 0) dynamically via an ALTER SYSTEM command.

---

Figure 3-3.  Memory Advisor