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.
e.g.:
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
» 
Comment and Contribute
