Oracle Optimizer: Moving to and working with CBO: Part 3 - Page 2
September 29, 2003
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.
Plan name: RESOURCE_SHARING 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. Support/Development 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'; VALUE __________ CHOOSE SQL> alter session set optimizer_mode = first_rows; Session altered. SQL> select value from v$parameter where name = 'optimizer_mode'; VALUE ___________ FIRST_ROWS
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'; NUM_ROWS BLOCKS AVG_ROW_LEN T USE ---------- ---------- ----------- - --- 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'; NUM_ROWS BLOCKS AVG_ROW_LEN T USE ---------- ---------- ----------- - --- 3000 300 50 Y YES