ORACLE FREQUENTLY ASKED QUESTIONS - V
When is cost based
optimization triggered? (For DBA)
It's important to have
statistics on all tables for the CBO (Cost Based Optimizer) to work correctly.
If one table involved in a statement does not have statistics, Oracle has to
revert to rule-based optimization for that statement. So you really want for
all tables to have statistics right away; it won't help much to just have the
larger tables analyzed.
Generally, the CBO can change the execution plan when you:
1. Change statistics of objects by doing an ANALYZE;
2. Change some initialization parameters (for example: hash_join_enabled,
sort_area_size, db_file_multiblock_read_count).
How can one optimize
%XYZ% queries? (For DBA)
It is possible to improve %XYZ% queries by forcing the optimizer to scan all
the entries from the index instead of the table. This can be done by specifying
hints. If the index is physically smaller than the table (which is usually the
case) it will take less time to scan the entire index than to scan the entire
table.
What Enter package
procedure does?
Enter Validate-data in the current validation unit.
Where can one find I/O
statistics per table? (For DBA)
The UTLESTAT report shows I/O per tablespace but one cannot see what tables in
the tablespace has the most I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script
creates a sample_io procedure and table to gather the required information.
After executing the procedure, one can do a simple SELECT * FROM io_per_object;
to extract the required information. For more details, look at the header
comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.
My query was fine last
week and now it is slow. Why? (For DBA)
The likely cause of this is because the execution plan has changed. Generate a
current explain plan of the offending query and compare it to a previous one
that was taken when the query was performing well. Usually the previous plan is
not available.
Some factors that can cause a plan to change are:
-
Which
tables are currently analyzed? Were they previously analyzed? (i.e. was the query
using RBO and now CBO?)
-
Has
OPTIMIZER_MODE been changed in INIT.ORA?
-
Has the
DEGREE of parallelism been defined/changed on any table?
-
Have
the tables been re-analyzed? Were the tables analyzed using estimate or
compute? If estimate, what percentage was used?
-
Have
the statistics changed?
-
Has the
INIT.ORA parameter DB_FILE_MULTIBLOCK_READ_COUNT been changed?
-
Has the
INIT.ORA parameter SORT_AREA_SIZE been changed?
-
Have
any other INIT.ORA parameters been changed?
-
What do
you think the plan should be? Run the query with hints to see if this produces
the required performance.
-
