To create a SQL Tuning Set simply select one or more statements from the table in Figure 10.2, and press the Create SQL Tuning Set button to bring up the screen shown in Figure 10.4. Here you can provide a name and description for the SQL Tuning Set and then press OK to create it.

Figure 10.4. Creating a SQL Tuning Set

The available SQL Tuning Sets will then be listed in the screen shown in Figure 10.5. From this page, we can manage the SQL Tuning Sets and also run the SQL Access and SQL Tuning Advisors.

Figure 10.5. Listing of SQL Tuning Sets

SQL Tuning Sets can also be created using the DBMS_SQLTUNE package. The DBMS_SQLTUNE package allows you to create SQL Tuning Sets from the SQL Cache, or any user table as long as it includes some specific columns. In Chapter 5, we discussed how SQL Table functions can be used in the FROM clause instead of an actual table. The SQL Tuning Set procedures provide built-in table functions that allow you to load a workload from the SQL Cache (also called the Cursor Cache), from the Automatic Workload Repository (described in Chapter 11), or from any user table with the SQL statements.

To avoid creating the SQL Tuning Set with too many statements, you can specify a filter condition restricting the SQL statements to be considered. You can also specify up to three ranking measures used to prioritize (i.e., order) the SQL statements and then request that only the top-N statements or only the ones contributing up to a certain percentage of the ranking measures should be included. When creating a SQL Tuning Set from the SQL Cache, you can use any column from the V$SQL view as a filter condition, and the numeric ones can be used as ranking measures. This should become clearer from the following example, which shows how to create a SQL Tuning Set from the SQL Cache:

DECLARE
   sqlsetname VARCHAR2(30);
   sqlsetcur dbms_sqltune.sqlset_cursor;
BEGIN
   sqlsetname := 'MY_STS_WORKLOAD';
   dbms_sqltune.create_sqlset(sqlsetname, 'SQL Cache STS'),
   OPEN sqlsetcur FOR
     SELECT VALUE(P)
     FROM TABLE(
      dbms_sqltune.select_cursor_cache(
              'SQL_TEXT like ''%purchases%''',
              NULL,
              'CPU_TIME', NULL, NULL, -- ranking measures
              NULL, 10) -- limit to 10
      ) P;
   dbms_sqltune.load_sqlset(sqlsetname, sqlsetcur);
end;
/

In this example, the CREATE_SQLSET procedure creates a SQL Tuning Set with the given name (MY_STS_WORKLOAD) and description (SQL Cache STS). The OPEN statement opens a cursor using the built-in table function, SELECT_CURSOR_CACHE. We have specified a filter, which says that the SQL_TEXT must include the word “purchases,” a ranking measure as CPU_TIME, and a maximum limit of 10 statements.

You can now view the SQL Tuning Set by either using the list in Oracle Enterprise Manager (Figure 10.5) or by using the dictionary views, DBA_SQL_SET and DBA_SQLSET_STATEMENTS, as follows:

select substr(name,1,15) name,substr(owner,1,6) owner,
       substr(sql_text,1,30) description, sql_id
from dba_sqlset d, dba_sqlset_statements s
where id = sqlset_id and name = 'MY_STS_WORKLOAD';

NAME            OWNER  SQL_TEXT                   SQL_ID
--------------- ------ -------------------------  -------------
MY_STS_WORKLOAD EASYDW SELECT t.month, t.year, p  4bw858yjjb11x
MY_STS_WORKLOAD EASYDW SELECT count(distinct pro  4rt9sqw6tnrzz
MY_STS_WORKLOAD EASYDW SELECT sqlset_row (sql_id  5ts120q1a40b5
MY_STS_WORKLOAD EASYDW SELECT VALUE(P) FROM TABL  5z0pw0x58fj88
MY_STS_WORKLOAD EASYDW SELECT VALUE(P) FROM TABL  5zs8b269g338s
...
10 rows selected.

This is a very powerful interface and can be used when you need more functionality and flexibility than are available in the graphical interface in Oracle Enterprise Manager.

If the SQL Access Advisor is launched from Advisor Central, you will first see the screen shown in Figure 10.8, where you must choose a workload source. (When the SQL Access Advisor is launched directly on a SQL Tuning Set, this screen is skipped.)

Figure 10.8. SQL Access Advisor: Choosing a Workload Source

In Figure 10.8, we have decided to specify a user-defined workload that is contained in the table EASYDW.USER_WORKLOAD. The table used for a SQL Access Advisor workload can be any user table but must have columns, as shown in the following example:

CREATE TABLE user_workload
( MODULE                VARCHAR2(48),
  ACTION                VARCHAR2(32),
  BUFFER_GETS           NUMBER,
  CPU_TIME              NUMBER,
  ELAPSED_TIME          NUMBER,
  DISK_READS            NUMBER,
  ROWS_PROCESSED        NUMBER,
  EXECUTIONS            NUMBER,
  OPTIMIZER_COST        NUMBER,
  LAST_EXECUTION_DATE   DATE,
  PRIORITY              NUMBER,
  SQL_TEXT              CLOB,
  STAT_PERIOD           NUMBER,
  USERNAME              VARCHAR2(30) )

Although this table has many columns, only a couple of them are mandatory columns—namely, the sql_text, which is the complete text of the SQL statement, and the username, which is the name of the user who will execute that SQL statement. The PRIORITY column is a user-settable priority for each SQL statement (1 = HIGH, 2 = MEDIUM, or 3 = LOW). The analysis will make trade-offs in favor of the high-priority SQL statements. If not specified, all statements are treated on an equal footing. All other columns are optional but provide useful statistics for the analysis and will improve the quality of the recommendations.

Alternatively, we can choose the workload source to be a SQL Tuning Set, the current contents of the SQL Cache, or a hypothetical workload generated from a given set of schemas and tables.

At the bottom of Figure 10.8 is a link named Show Advanced Options. Clicking on this link will allow you to set additional options, as shown in Figure 10.9.

Figure 10.9. SQL Access Advisor: Specifying Workload Filtering Options

Here, you can indicate whether the workload is for a primarily read-only application; in this case, the analysis will ignore the impact of index and materialized view maintenance. Otherwise, the SQL Access Advisor will try to identify the maintenance impact from the DML statements in the workload. Unless you are in the initial design phase of the application, it is wise to allow the Advisor to consider the maintenance impact.

It is also possible to specify if the SQL Access Advisor should consider dropping existing access structures and replacing them with new ones. If you are unsure if your workload covers all relevant SQL for the application, you should choose not to recommend dropping any access structures.

When using a workload source such as the SQL cache, it is possible to have a large number of SQL statements not interesting to the application you are trying to tune. In order to achieve a more focused tuning, it is advisable that you should specify various criteria to narrow down the relevant SQL statements in the workload. By restricting the workload in this manner, the SQL Access Advisor can work in a more focused manner and thereby its analysis would be faster.

In the example, shown in Figure 10.9, we have specified that only SQL statements executed by the user EASYDW and containing tables in the EASYDW schema should be considered for analysis. You could also specify that the top-N most resource consuming SQL be analyzed, or that only SQL from a certain application (specified using the MODULE and ACTION attributes) should be considered.

Once the workload has been chosen, press the Next button to proceed to the screen shown in Figure 10.10, where you can pick various recommendation options for the SQL Access Advisor, as follows.

Figure 10.10. SQL Access Advisor: Recommendation Options

Recommendation Types: The SQL Access Advisor can recommend Indexes, Materialized Views, or both. If the Indexes option is chosen, it will recommend either B*tree or bitmap indexes. If the Materialized Views option is chosen, it will recommend materialized views and also any materialized view logs required for fast refresh. If both Indexes and Materialized Views are chosen, it will recommend all of these, as well as indexes on the materialized views.

Advisor Mode: The SQL Access Advisor has two modes: Limited and Comprehensive. In the comprehensive mode, it will perform an exhaustive analysis of the entire workload; however, this can take significantly longer to run, depending on the size of the workload. If you would like a quick analysis of the workload, you can specify the limited mode; however, you may not get the best possible recommendations. Use the comprehensive mode as much as possible, unless you have a really large workload.

If you click the Show Advanced Options link in Figure 10.10, you can set some additional recommendation options, as shown in Figure 10.11. The advanced options allow you to specify a storage limit (in megabytes) that the recommended access structures should fit within (if created) and also to pick the tablespace and schema where any recommended materialized views and indexes should reside. Note that this information is used when implementing the recommendations, as we will see later.

Figure 10.11. SQL Access Advisor: More Recommendation Options

Pressing the Next button will bring up the screen shown in Figure 10.12, where you can name the advisor task that will contain the results of the analysis. It’s worth choosing a sensible name, because you will need this to retrieve your recommendations from Advisor Central, especially if you wish to come back at a later date and review them. The analysis can be scheduled to execute immediately or at a specified later date.

Figure 10.12. SQL Access Advisor: Schedule

On pressing the Next button, you will see the review screen shown in Figure 10.13, where you can see all your choices at a glance. To make any changes, go back to the previous screens by using the Back buttons.

Figure 10.13. SQL Access Advisor: Review Page

Once you are satisfied with the settings, you click the Submit button, which will bring you back to Advisor Central; from here you can monitor the status of your job (see Figure 10.6).

Once the status of the task in Advisor Central indicates Completed, you can select the task and click the View Result button, which will bring you to the recommendations page, shown in Figure 10.14.

Figure 10.14. SQL Access Advisor: Recommendations

There are two ways to view the results of the SQL Access Advisor:

Figure 10.14 shows the recommendations view, where you can see a bar graph of the estimated improvement in your workload for each recommendation. You can also see the estimated space required to implement that recommendation and the number of SQL statements benefited by that recommendation.

Each recommendation consists of a series of actions to CREATE new materialized views or indexes or to DROP or RETAIN existing ones. There may also be actions to CREATE or ALTER materialized view logs for the recommended materialized views. You can see the actions for each recommendation by clicking on the link in the Recommendation ID column for that recommendation. For example, recommendation ID 4, shown in Figure 10.15, consists of a materialized view and two associated materialized view logs. There is also an auxiliary action to gather statistics on the materialized view once it is created. You can also see the SQL statement(s) that it benefits.

Figure 10.15. SQL Access Advisor: Recommendation Details

The text of the materialized view can be seen by clicking on the CREATE MATERIALIZED VIEW link in the Action column. Or you can see the SQL for all the actions in a recommendation by clicking the Show SQL button in Figure 10.14. The SQL for Recommendation ID 4 is shown in Figure 10.16.

Figure 10.16. SQL Access Advisor: Recommendation SQL

Recommendation ID 3 consists of an index, as shown in Figure 10.17. Clicking on the CREATE INDEX link will show the detailed columns in the index key.

Figure 10.17. SQL Access Advisor: Index Recommendation

In both Figure 10.15 and Figure 10.17, you can edit the names of the recommended structures and the tablespace and schema where they reside.

The alternate way to view the recommendations is by the SQL statements in the workload, as shown in Figure 10.18.

Figure 10.18. Recommendation: SQL View

This view shows the SQL statements that contribute the most improvement in the overall performance of the workload. It also shows the original optimizer cost (as shown by an EXPLAIN PLAN) and the estimated reduction in cost (and thereby increase in performance), if the recommendation were implemented. For example, SQL statement ID 95 was improved by 75 percent. Just as in the recommendations view, you can see the detailed actions in the recommendation by clicking on the links in the Recommendation ID column.

To choose the recommendation(s) you would like to implement simply click on the check boxes in the Select column in Figure 10.14. Creating the access structures can be time consuming and therefore you may want to schedule the implementation during an appropriate maintenance window. Pressing the Schedule Implementation button will bring you to the page shown in Figure 10.19, where you can specify parameters to schedule the implementation. Once you press the Submit button, the implementation job will be scheduled and you will be returned to the Advisor Central page where the status of the job can be monitored.

Figure 10.19. SQL Access Advisor: Schedule Implementation

As we have seen, the SQL Access Advisor wizard is extremely simple to use, and, with a few simple choices, you can tune your application for materialized views and indexes. Next, we will take a brief look at the command-line interface for the SQL Access Advisor.

The first step is to invoke the CREATE_SQLWKLD procedure to create a workload object, which will store the SQL statements in the workload. In this example we are creating a workload object named MY_WORKLOAD.

variable workload_name varchar2(255);
execute :workload_name := 'MYWORKLOAD';
execute dbms_advisor.create_sqlwkld(:workload_name);

You can also specify various criteria to narrow down the workload using the SET_SQLWKLD_PARAMETER procedure. For example, you can specify that the statements in the SQL Cache should be ordered using their ELAPSED_TIME, and only the top-10 statements should be considered.

execute dbms_advisor.set_sqlwkld_parameter(:workload_name,
                                           'ORDER_LIST',
                                           'ELAPSED_TIME'),
execute dbms_advisor.set_sqlwkld_parameter(:workload_name,
                                           'SQL_LIMIT', 10);

The next step is to load the SQL statements into the workload. We are loading the workload from the SQL Cache. The filter parameters described in step 2 will be used to determine the statements being loaded.

variable saved_stmts number;
variable total_stmts number;
variable failed_stmts number;

execute dbms_advisor.import_sqlwkld_sqlcache(:workload_name,
                                             'APPEND', 2,
                                             :total_stmts,
                                             :saved_stmts,
                                             :failed_stmts);

The next step is to use the CREATE_TASK procedure to create a SQL Access Advisor Task to store the analysis parameters and the recommendations. This is the time to set various parameters such as whether the execution should be INDEX_ONLY and whether the analysis should be limited or comprehensive.

variable task_id number;
variable task_name varchar2(255);

execute :task_name := 'MYTASK';
execute dbms_advisor.create_task('SQL Access Advisor',
                                :task_id, :task_name);

execute dbms_advisor.set_task_parameter(:task_name,
                                        'EXECUTION_TYPE',
                                        'INDEX_ONLY'),

Once both the workload and the task are created, they must be linked using the ADD_SQLWLKD_REF procedure.

execute dbms_advisor.add_sqlwkld_ref(:task_name, :workload_name);

Finally, execute the task to generate the recommendations. To obtain a SQL script for the recommended access structures, use the GET_TASK_SCRIPT procedure. This can be run later to create the access structures. Note that you must specify a DIRECTORY object (in this example, ADVISOR_RESULTS) where the script will be placed and you must have write permissions on this directory object.

execute dbms_advisor.execute_task(:task_name);

execute dbms_advisor.create_file(
   dbms_advisor.get_task_script(:task_name),
  'ADVISOR_RESULTS', 'advisor_script.sql'),

The following is an excerpt from a SQL Access Advisor script:

Rem  SQL Access Advisor: Version 10.1.0.1 - Production
Rem
Rem  Username:        EASYDW
Rem  Task:            MYTASK
Rem  Execution date:  19/06/2004 23:59
Rem
...
CREATE BITMAP INDEX "EASYDW"."_IDX$$_0188000E"
    ON "EASYDW"."CUSTOMER"
    ("GENDER")
    COMPUTE STATISTICS;
...

To create a template, use the CREATE_TASK procedure but set the is_template parameter to TRUE. In this example, the template sets up the default naming conventions for the recommended materialized views and indexes.

variable template_name varchar2(30);
execute :template_name := 'MY_TEMPLATE';
execute dbms_advisor.create_task
    ('SQL Access Advisor',:template_id,
     :template_name,is_template=>'TRUE'),

execute dbms_advisor.set_task_parameter(:template_name,
                                        'INDEX_NAME_TEMPLATE',
                                        'SH_IDX$$_<SEQ>'),

execute dbms_advisor.set_task_parameter(:template_name,
                                        'MVIEW_NAME_TEMPLATE',
                                        'SH_MV$$_<SEQ>'),

Now we can use this template to create a task by specifying the template parameter in the CREATE_TASK procedure.

variable task_id number;
execute dbms_advisor.create_task
      ('SQL Access Advisor', :task_id,
       'MY_TASK', template=>'MY_TEMPLATE'),

Another type of recommendation produced by the SQL Tuning Advisor is a profile, which keeps track of various statistics on the table, as well as predicates and joins in a SQL statement, to assist the optimizer in producing a better execution plan. These statistics are collected by actually running the query on a sample of the data, and by verifying if the optimizer’s estimates match the values found during actual execution. There could be many reasons why the optimizer might produce a bad plan—for example, data skew or stale statistics. The SQL profile will provide information that will allow the optimizer to correct its past mistakes. Once a profile is implemented by pressing the Implement button in Figure 10.23, the optimizer will then automatically use the improved estimates to produce a more efficient execution plan.

Figure 10.23. SQL Tuning Recommendation: Create Profile

Note that sometimes the SQL Tuning Advisor may indicate that a very critical index is missing on a SQL statement. However, this is a very limited analysis and should not be considered as advice to implement the index right away. You should instead run the SQL Access Advisor on that SQL Tuning Set to obtain the comprehensive access structure advice.

The amount of PGA memory allocated and used by each Oracle server process can be seen in the V$PROCESS view, as follows. The PGA_USED_MEM column represents the memory currently in use, PGA_ALLOC_MEM column is total memory currently allocated by the process (some of it may be freed but not yet returned to the operating system), and PGA_MAX_MEM is the maximum ever allocated by that process. In a well-tuned system, every process should be allocating adequate memory but no more than necessary.

SELECT spid, pga_used_mem, pga_alloc_mem, pga_max_mem
FROM v$process;
SPID         PGA_USED_MEM PGA_ALLOC_MEM PGA_MAX_MEM
------------ ------------ ------------- -----------
340                132552        198880      198880
341                135056       3371624     3371624
343               4349880       7570468     7570468
...

The memory used by the work area of SQL statements is seen in the V$SQL_WORKAREA view. You can join to V$SQL to get the complete text for the SQL statements. (We have edited the following output to show only part of the text for lack of space.)

SELECT sql_text, operation_type,
       estimated_optimal_size estsize, last_memory_used
FROM v$sql_workarea w, v$sql s
WHERE w.address = s.address
AND parsing_schema_id = USERENV('SCHEMAID') ;


SQL_TEXT        OPERATION_TYPE    ESTSIZE LAST_MEMORY_USED
--------------- --------------    ------- ----------------
SELECT t.month, GROUP BY (SORT)     56320            49152
SELECT t.month, HASH-JOIN          874496           628736
...

While a query execution is in progress, you can monitor the work area usage in the V$SQL_WORKAREA_ACTIVE view.

-- shows the current workarea usage during an execution
SELECT operation_type, work_area_size, expected_size,
       actual_mem_used
FROM v$sql_workarea_active;

OPERATION_TYPE WORK_AREA_SIZE EXPECTED_SIZE ACTUAL_MEM_USED
-------------- -------------- ------------- ---------------
HASH-JOIN             1087488       1086464          628736

Complex SQL operations need adequate work area memory; otherwise, the operation may need to spill over to temporary segments on disk. The optimal memory size is one that allows the entire operation to be performed entirely in memory. If memory is somewhat less than optimal, then one or more extra passes over the data may be required. A one-pass operation is the next best to the optimal and will perform reasonably well and as work area sizes get larger, may be the typical case. However, a multipass operation will severely degrade performance and should be avoided as much as possible.

The view V$SQL_WORKAREA_HISTOGRAM can be used to find the distribution of optimal, one-pass, and multipass query executions in your system. The view shows the number of optimal, one-pass, and multi-pass executions for different ranges of work area sizes. For example, in the following query, most executions use work area sizes under 2MB and are able to execute with the optimal amount of PGA memory. There is one query with work areas between 4 and 8 MB, which needed a one-pass execution and two queries with work areas between 8 and 16 MB, which needed a multi-pass execution.

SELECT LOW_OPTIMAL_SIZE LOW, HIGH_OPTIMAL_SIZE HIGH,
       OPTIMAL_EXECUTIONS OPT, ONEPASS_EXECUTIONS Onepass,
       MULTIPASSES_EXECUTIONS multipass
FROM V$SQL_WORKAREA_HISTOGRAM
WHERE TOTAL_EXECUTIONS != 0;

       LOW       HIGH        OPT    ONEPASS  MULTIPASS
---------- ---------- ---------- ---------- ----------
      2048       4095       4712          0          0
     65536     131071         44          0          0
    131072     262143         23          0          0
    262144     524287         20          0          0
    524288    1048575        178          0          0
   1048576    2097151         34          0          0
   4194304    8388607          0          1          0
   8388608   16777215          0          0          2

Now that we understand how to monitor PGA memory usage and identify any multipass executions, let us see how we go about tuning it.

As we mentioned earlier, available physical memory on a system running Oracle must be distributed among the Operating System, SGA, and PGA. For a data warehouse, a good rule of thumb is to set the PGA_AGGREGATE_TARGET initially to about 40 percent to 50 percent of available physical memory and then tune it based on execution of a real workload.

Oracle Database 10g will continuously monitor how much PGA memory is being used by the entire instance by collecting statistics during executions of queries. This information can be seen in the V$PGASTAT view. Using these statistics, it estimates how the performance would vary if the PGA_AGGREGATE_TARGET were set to different values. This information is published in the V$PGA_TARGET_ADVICE view and can be used to tune the PGA_AGGREGATE_TARGET parameter.

The following query shows the various statistics in V$PGASTAT:

SELECT  * FROM V$PGASTAT;
NAME                                       VALUE UNIT
------------------------------------------------ ------------
aggregate PGA target parameter          15728640 bytes
aggregate PGA auto target                4194304 bytes
global memory bound                       786432 bytes
total PGA inuse                         16775168 bytes
total PGA allocated                     50482176 bytes
maximum PGA allocated                   59011072 bytes
total freeable PGA memory                6094848 bytes
PGA memory freed back to OS             23986176 bytes
total PGA used for auto workareas         460800 bytes
maximum PGA used for auto workareas      1323008 bytes
total PGA used for manual workareas            0 bytes
maximum PGA used for manual workareas     529408 bytes
over allocation count                        426      <- non zero
bytes processed                         69940224 bytes
extra bytes read/written                62337024 bytes
cache hit percentage                     60.87 percent <- too low

16 rows selected.

The first step is to look at the top two lines of this output: the aggregate PGA target parameter, which is the current setting of PGA_AGGREGATE_TARGET, and the aggregate PGA auto target, which Oracle has calculated as the total memory it can use for SQL work areas. The difference is the estimated memory needed for other processing, such as PL/SQL, and is not tuned by the automatic memory management feature. In our example, the PGA_AGGREGATE_TARGET is 15.7MB, and the total amount available for work areas is 4.2MB. Note that if you find the auto target is much smaller than the PGA_AGGREGATE_TARGET, as is the case in our example, this is one indication that there is not enough PGA memory for work areas and you may need to increase it.

To confirm whether your PGA_AGGREGATE_SETTING is too small, you should look at two quantities - the cache hit percentage and the overallocation count, underlined in the preceding output. The cache hit percentage indicates the percentage of work areas that operated with an optimal allocation of memory. The overallocation count indicates how many times Oracle had to step over the user-defined limit for PGA_AGGREGATE_TARGET, because there was not enough memory available. In a well-tuned system, the overallocation count should be zero, meaning the available PGA memory was sufficient, and the cache hit percentage should be over 80 percent, meaning most queries execute with the optimal amount of memory. If you find that your cache hit percentage is too low or the overallocation count is nonzero, you have insufficient PGA memory. In our example, the cache hit ratio is 61 percent, which is low, and the overallocation count is 426, which is not good.

In these cases you should look at the V$PGA_TARGET_ADVICE view for advice. The view shows the projected values of the cache hit percentage and the overallocation count for various memory sizes. For each memory size, the FACTOR column shows which factor of the current memory setting it is. For example, the row where the FACTOR column is 1 is the current setting, in our example 15.7MB.

select PGA_TARGET_FOR_ESTIMATE PGA_TARGET, PGA_TARGET_FACTOR
FACTOR,
       ESTD_PGA_CACHE_HIT_PERCENTAGE CACHE_HIT_PCT,
       ESTD_OVERALLOC_COUNT OVERALLOC_CNT
from v$pga_target_advice;

PGA_TARGET   FACTOR CACHE_HIT_PCT OVERALLOC_CNT
----------  ------- ------------- -------------
  11796480      .75            50            23
  15728640        1            61            23 <-current setting
  18874368     1.2             61            23
  22020096     1.4             65             2
  25165824     1.6             70             1
  28311552     1.8             80             0 <-minimal needed
  3145728      2               85             0
  47185920     3               87             0
  62914560     4               88             0 <-optimal
  94371840     6               88             0
 125829120     8               89             0

11 rows selected.

When tuning PGA memory, you must ensure that the PGA_AGGREGATE_TARGET is at least set to a value where the overallocation count is zero. Otherwise, there is simply not enough memory for all the work areas. In this example, the minimum memory setting where overallocation count goes to zero is around 28MB. Further, notice that as you increase memory size, the cache hit ratio value increases rapidly up to a point (88 percent for around 63MB in the previous output), and after that it starts to increase more slowly. This point is the optimal value of PGA memory. You must ideally set your PGA memory at or close to this optimal value.

You can see a graphical representation of this view in Oracle Enterprise Manager. From the Advisor Central page (Figure 10.6), if you follow the Memory Advisor link and then click on PGA link, you will see the screen shown in Figure 10.24, which shows the current PGA settings and usage.

Figure 10.24. PGA Memory Advisor

From this page, if you click on the Advice button, you will see a line graph as shown in Figure 10.25 with the memory size setting on the X-axis and the cache hit percentage on the Y-axis. You will find that the initial part of the line graph indicates the threshold below which you will see nonzero overallocation count. The optimal value of memory is where this line starts to taper off. The vertical line shows the current setting of the PGA_AGGREGATE_TARGET parameter and can be moved to choose a new setting for this parameter. Once you have chosen a new value, you simply press the OK button and the change will be made.

Figure 10.25. PGA Target Advice in Oracle Enterprise Manager

In the next section, we will discuss how to set the value for the SGA memory.

Increasing the shared pool size will improve the time taken to compile a SQL statement. Hence, the shared pool size advice is in the form of a graph, shown in Figure 10.27, with the shared pool size on the X-axis and the expected savings in parse time on the Y-axis. The optimal value is the knee of this graph (i.e., where the graph tapers off). The vertical line shows the current setting of the shared pool, and you can click on the curve to change this setting.

Figure 10.27. Shared Pool Size Advice

The buffer cache is used to cache frequently used data blocks from disk into memory, thereby speeding up queries. In a data warehouse, many queries involve scanning entire tables, and increasing the size of the buffer cache does not usually speed up these scans. (This is because as new blocks get loaded they displace the earlier blocks and so the next query that needs that earlier block will need to get it from the disk again.) Also, loading of data using SQL*Loader or Parallel DML uses PGA memory and not SGA. So, typically, the size of the buffer cache in a data warehouse would be small compared with that in an OLTP system. You can use the buffer cache advice shown in Figure 10.28, to correctly size your buffer cache so you do not waste too much memory. In this graph, the Y-axis shows the decrease in physical disk blocks read as you increase the buffer cache size shown on the X-axis. Again, the optimal value is around the knee of the graph (i.e., where it starts to flatten out) and you can choose the new setting by clicking on the graph.

Figure 10.28. Buffer Cache Advice

It must be said that, as with all tuning tasks, memory configuration is an iterative process and depends on the workload; therefore, you may need to repeat this process a few times before you get to the optimal settings. However, once you start using automatic PGA and SGA advisors, this process is very straightforward and will ensure that you get the best performance out of your queries.