Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 29, 2003

Oracle Optimizer: Moving to and working with CBO: Part 3 - Page 2

By Amar Kumar Padhi

8.5) Use Oracle Resource Manager to control adhoc queries and third party tools

It is understood that the development team must occasionally fire adhoc queries to extract data or check on facts and figures in Production. It may also be possible that third party tools are being extensively used.

Most of us do not spend time on tuning adhoc queries or check on how resource intensive third party tools are as it is not part of the main application. This may sometimes result in situations where a particular process affects all other sessions and slows down the server for a noticeable duration. With CBO, your chances of such scenarios may be increased.

You may do the following to avoid problems that are external to your application:

1. Avoid the use of third party tools in the initial stages. This will help the development team to concentrate on problems related to the core application and prevent other tools/utilities from diverting the tuning objectives.

2. Irrespective of whether the above point can be implemented or not, you may start using Oracle Resource Manager. This feature allows you to prioritize various processes and slice resources amongst various processes. For this, a resource plan needs to be created that directs Oracle to share the available resources in the given ratios. For example, I have enabled the following resource plan for a database on a server with 4 CPUs.

GROUP             USERS               DIRECTIVE
HIGH_PRIORITY     Online users        Allow 87% x 4 CPU usage. 
                                      This means 87% resource on all the 4 CPUs.

MEDIUM_PRIORITY   Online Report       Allow 10% x 4 CPU usage.
                  generation          This means 10% resource on all the 4 CPUs.
                  programs, batch
                  jobs, Business
                  Objects tool, 
                  Datastage tool, 
                  data upload and 
                  download interface.

LOW_PRIORITY      Adhoc online and    Allow 3% x 4 CPU usage.
                  batch reports,      This means 3% resource on all the 4 CPUs. 
                  Team sessions.
                  Maintenance activity.

The above plan states that a process in a low priority group will not affect high priority users as they have a major share of the CPU. Please note that if resources are not used completely in one group, they will be made available to other groups. For example if high priority group users are utilizing only 40% of the CPU, the remaining portion may be allocated to a medium or low priority process that is in need of it.

You may also consider having multiple resource plans, one for online processing, another for batch processing etc. that could be enabled at different times of the day or on different days as per the processing needs.

8.6) Using CBO modules in RBO setup

If your setup is very large, you might consider moving to CBO one module at a time. Some coding will have to be done explicitly for this. For example if there is an independent schema dedicated to interfacing data, statistics could be generated for tables and indexes in this schema. Optimizer mode for sessions connecting to this schema can be set to FIRST_ROWS(_nnn) or ALL_ROWS, at the session level. While the application runs in RBO, one set of modules can be tuned to run in CBO. However, this should not be permanent; the ultimate goal should be to move the complete application to CBO.

Please note that statistics can be generated for all objects. These will be ignored by RBO but will be taken into consideration by CBO sessions.

e.g.: Setting optimizer at session level.
SQL> select value from v$parameter where name = 'optimizer_mode';


SQL> alter session set optimizer_mode = first_rows;

Session altered.

SQL> select value from v$parameter where name = 'optimizer_mode';


Oracle also provides a session level parameter OPTIMIZER_GOAL that serves the same purpose as above. Setting OPTIMIZER_GOAL will affect that particular session only and the value set can be viewed from V$PARAMETER as in the above example.

8.7) Generate adequate statistics at proper intervals

Use DBMS_STATS for generating statistics on a periodic basis. You may also categorize objects under various groups depending on their frequency of data change. For example, a daily transaction table will always change and statistics may be generated more often. Master tables change subtly and statistics could be generated less often.

In the case of ESTIMATE statistics, arrive at an optimal sample size. A percentage of 5-10 gives adequate statistics.

Consider using COMPUTE statistics for indexes and index-organized tables.

The interval at which statistics are generated should not be too frequent. It will depend a lot on the extent of DML activities carried. Please note that statistics generation is not incremental and doing it too frequently does not add up to the existing statistics nor reduce the computation time. Statistics are always generated from scratch and the existing statistics are overwritten.

If queries on a particular set of tables always require more accurate statistics to behave properly, and otherwise fail to perform, consider using hints to direct the optimizer and avoid dependency on statistics generation.

Whenever a heavy upload of data is done, consider explicit generation of statistics on the concerned tables.

Please refer section 9 (Generating Statistics) and 10 (DML Monitoring) to be covered in subsequent part of this series.

8.8) Statistics for Global Temporary tables

No statistics are collected for Global Temporary Tables; handle these with care. Make use of hints to drive queries on these. Provide explicit statistics by using DBMS_STATS.SET_TABLE_STATS if your temporary table is used for processing huge amounts of data.


SQL> select num_rows, blocks, avg_row_len, temporary, user_stats
  2  from dba_tables
  3  where table_name = 'AM21';

---------- ---------- ----------- - ---
                                  Y NO

jaDA>exec dbms_stats.gather_table_stats('DATASTAGE', 'TMP_GL_VALUE_DIFF');
BEGIN dbms_stats.gather_table_stats('DATASTAGE', 'TMP_GL_VALUE_DIFF'); END; 
ERROR at line 1:
ORA-20000: Gathering statistics for a temporary table is not supported
ORA-06512: at "SYS.DBMS_STATS", line 4481
ORA-06512: at line 1

SQL> exec dbms_stats.set_table_stats(ownname => 'SYS', tabname => 'AM21', -
> numrows => 3000, numblks => 300, avgrlen => 50);

PL/SQL procedure successfully completed.

SQL> select num_rows, blocks, avg_row_len, temporary, user_stats
  2  from dba_tables
  3  where table_name = 'AM21';

---------- ---------- ----------- - ---
      3000        300          50 Y YES

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM