Set - 4

Question 81 :

When is cost based optimization triggered? (for DBA)

Answer :

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).

Question 82 :

How can one optimize %XYZ% queries? (for DBA)

Answer :

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.

Question 83 :

What Enter package procedure does ?

Answer :

Enter Validate-data in the current validation unit.

Question 84 :

Where can one find I/O statistics per table? (for DBA)

Answer :

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.

Question 85 :

My query was fine last week and now it is slow. Why? (for DBA)

Answer :

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? (ie. 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.