Cost Based Optimizer (CBO)

Analyze is required to gather information about below subjects:

 Row Number in a table

 Block situation where data is stored

 Index Information

There are two methods to analyze a table:

1. ANALYZE

Analyze command is used to gather statistics about a table, an index or a cluster and also user can specify the number of the rows or the percentage of the table to be analyzed. Example usages of analyze command are:

1. ANALYZE TABLE employees COMPUTE STATISTICS; 2. ANALYZE INDEX employees_pk COMPUTE STATISTICS;

3. ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS; 4. ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;

Table B.2: ANALYZE command usages

2. DBMS_STATS

Dbms_stats package is an analyzer which has ability of parallel execution, copying statistics from one database to another and deleting gathered statistics from database. Copying statistics from one server to another is a great feature which gives you the chance to prepare statistics of your database on a copy database and then carry these information to your live database. Example usages of dbms_stats command are:

1. EXEC DBMS_STATS.gather_database_stats;

2. EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15); 3. EXEC DBMS_STATS.gather_schema_stats(’SCOTT’);

4. EXEC DBMS_STATS.gather_schema_stats(’SCOTT’, estimate_percent => 15); 5. EXEC DBMS_STATS.gather_table_stats(’SCOTT’, ‘EMPLOYEES’);

6. EXEC DBMS_STATS.gather_table_stats(’SCOTT’, ‘EMPLOYEES’, estimate_percent => 15); 7. EXEC DBMS_STATS.gather_index_stats(’SCOTT’, ‘EMPLOYEES_PK’);

8. EXEC DBMS_STATS.gather_index_stats(’SCOTT’, ‘EMPLOYEES_PK’, estimate_percent => 15); 9. EXEC DBMS_STATS.delete_database_stats;

10. EXEC DBMS_STATS.delete_schema_stats(’SCOTT’);

11. EXEC DBMS_STATS.delete_table_stats(’SCOTT’, ‘EMPLOYEES’); 12. EXEC DBMS_STATS.delete_index_stats(’SCOTT’, ‘EMPLOYEES_PK’);

Table B.3: DBMS_STATS command usages

The table order still makes a difference in execution time, even when using the cost-based optimizer. The driving table is the table that will initiate the query and should be the table with the smallest number of rows. Ordering the tables in the FROM clause can make a huge difference in execution time.

Hint #2

Cost-based optimization – The driving table is first after FROM clause - place smallest table first after FROM, and list tables from smallest to largest.

Rule-based optimization – The driving table is last in FROM clause - place smallest table last in FROM clause, and list tables from largest to smallest.

SOURCE

1. Execution Plan, Optimizer ve çeþitleri, http://www.ceturk.com/makaleoku.asp?id=224 2. Oracle's explain plan, http://www.adp-gmbh.ch/ora/explainplan.html

3. EXPLAIN PLAN Usage, http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php

4. Tuning with Rule-Based Optimization, http://www.remote-dba.net/t_tuning_rule_based_optimization.htm

Introduction to EXPLAIN PLAN

An EXPLAIN PLAN is a tool that you can use to have Oracle explain to you how it plans on executing your query. This is useful in tuning queries to the database to get them to perform better. Once you know how Oracle plans on executing your query, you can change your environments to run the query faster. The components of the execution plan includes.

* An ordering of the tables referenced by the statement. * An access method for each table mentioned in the statement. * A join method for tables affected by join operations in the statement. Explain Plan output shows how Oracle executes SQL statements.

Creating the Output Table

Before issuing an EXPLAIN PLAN statement, create a table to hold its output. Use one of the following approaches: * Run the SQL script UTLXPLAN.SQL to create a sample output table called PLAN_TABLE in your schema. The exact name and location of this script depends on your operating system. PLAN_TABLE is the default table into which the EXPLAIN_PLAN statement inserts rows describing execution plans.

* Issue a CREATE TABLE statement to create an output table with any name you choose. When you issue an EXPLAIN PLAN statement you can direct its output to this table.

Any table used to store the output of the EXPLAIN PLAN statement must have the same columns and datatypes as the PLAN_TABLE

CREATE TABLE plan_table (statement_id VARCHAR2(30), timestamp DATE, remarks VARCHAR2(80), operation VARCHAR2(30), options VARCHAR2(30), object_node VARCHAR2(128), object_owner VARCHAR2(30), object_name VARCHAR2(30), object_instance NUMERIC, object_type VARCHAR2(30), optimizer VARCHAR2(255), search_columns NUMERIC, id NUMERIC, parent_id NUMERIC, position NUMERIC, cost NUMERIC, cardinality NUMERIC, bytes NUMERIC, other_tag VARCHAR2(255) other LONG);

Issue an EXPLAIN PLAN for the query you are intrested in tunning. The command is of the form: EXPLAIN PLAN SET STATEMENT_ID='X' FOR some SQL statement;

You need to use the statement_id and then give your SQL

SQL> explain plan set statement_id = 'q1' for

2 select object_name from test where object_name like 'T%'; Explained.

I used T1 for my statement id. But you can use anything you want. My SQL statement is the second line. Now I query the PLAN_TABLE to see how this statement is executed. This can be done in the following ways.

* A simple select query to see the contents of the PLAN_TABLE (SELECT * FROM PLAN_TABLE) or * Selecting the PLAN_TABLE output in the Nested Format.

SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '

||

2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query

Plan",other

3 FROM plan_table

4 START WITH id = 0

5 AND statement_id='T1'

6 CONNECT BY PRIOR ID = PARENT_ID

7 AND statement_id = 'T1'

---

SELECT STATEMENT Cost =

TABLE ACCESS FULL TEST

This tells me that my SQL statement will perform a FULL table scan on the TEST table (TABLE ACCESS FULL TEST). Now lets add an index on that table and see how the things differ:

SQL> create index test_name_idx on test(object_name);

Index created.

SQL> truncate table plan_table;

Table truncated.

SQL> explain plan set statement_id = 'T1' for

2 select object_name from test where object_name like 'T%';

Explained.

SQL> SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options || ' '

||

2 object_name || ' ' || DECODE(id,0,'Cost = ' || position) AS "Query

Plan",other

4 START WITH id = 0

5 AND statement_id='T1'

6 CONNECT BY PRIOR ID = PARENT_ID

7* AND statement_id = 'T1'

Query Plan OTHER

--- SELECT STATEMENT Cost =

INDEX RANGE SCAN TEST_NAME_IDX

As I added an index to the table. Before I issue another EXPLAIN PLAN, I truncate the contents of my PLAN_TABLE to prepare for the new plan. Then I query the PLAN_TABLE to prepare for the new plan. Notice that this time I'm using an index (TEST_TIME_IDX) that was created on the table. Hopefully the query faster now that it has an index to use instead of FULL TABLE SCAN the earlier.

The row source count values in EXPLAIN PLAN output identify the number of rows processed by each step in the plan. This helps us to identify inefficiencies in the query.

Note: When evaluating the plan, always examine the statements actual resource consumption. For better results, use the Oracle Trace or SQL trace facility and TKPROF to examine individual SQL statement performance.

EXPLAIN PLAN Restrictions

* Oracle does not support EXPLAIN PLAN for statements performing implicit type conversion of date bind variables. With bind variables in genral, the EXPLAIN PLAN output may not represent real execution Plan.

* From the text to a SQL statement, TKPROF cannot determine the types of the bind variables. It assumes that the type is CHARACTER, and gives an error message if this is not the case. We can avoid this limitation by putting appropriate type conversions in the SQL statement.