CLASS12_3 Gather Oracle Database Statistics

In this lesson we will be discussing Database Statistics, why do we need to update or gather database Statistics?

Why it is necessary for database precisely for Oracle?

Let’s Dive into some of oracle’s basic concept behind the Database optimizer Statistics.  As we know, Oracle creates an execution plan before running any SQL query. It means that how oracle executes this query in a way that will reduce the CPU usage, memory usage and extract results rapidly. For efficient query execution plan, Oracle needs some estimation in order to determine.

Gather Oracle Database Statistics;

As we know that whenever we create a table all information of the table is available in DBA_Tables automatically. However, when we insert data into this particular table then the information is not quickly available to DBA_TABLES. Let’s make this concept more concrete by completing the following examples.

• I’ve created the table Called ‘DEPT2’ using following query.

CREATE TABLE DEPT2

(DEPTNO NUMBER(7) PRIMARY KEY,

DNAME VARCHAR2(27),

LOC VARCHAR2(5));

• Information of this table can be seen in DBA_TABLES.

o   select owner, table_name, num_rows from dba_tables

                        Where TABLE_NAME =’DEPT2′;

o   As you can see Num_ROWS Column contains a null value which means, table does not have any data or row available.

• Now I am going to insert some data into this newly created table.

• By performing the above activity, we have  observed that NUM_ROWS column is not updating rapidly. Although we have inserted the data into DEPT2 tables that can be seen but Information of this data is not available in DBA_TABLES.
• Hence, we’ve come to the conclusion that this information is static which we need to be automatic by using any tool. So, the question is what that mechanism we have for this?
• So, the answer is that Oracle has tool called ‘Managed optimizer Statistics.’
• There is an Oracle Managed maintenance Job, Who usually runs in the night and gather information regarding all statistics and update those into DBA_TABLES. Remember if your system or server is not 24/7 machine then most probably this job will not run on a regular basis that will lead the performance issues and static information in DBA_TABLES.
• If you are not in 27/7 environment, then you have to take manual stats or change the timing of this job.

We can gather optimizer statistics via enterprise manager. Log on to the EM and go to Server > Tab Click Manage Optimizer Statistics under Query optimizer.

After clicking Manage Optimizer Statistics, you will be redirected to the new page. Where you need to select operations, we have selected Gather Optimizer Statistics.

Gather Optimizer Statistics link redirect us into following new page. Where you are ask to select object type from which you can collect stats.

As you can see above Information that indicates,’ For 11, Oracle recommends you enable automated maintenance task (Optimizer Statistics Gathering) to generate optimizer statistics regularly within maintenance windows. This wizard should only be used for cases where the task is inappropriate or disabled. For example, you may want to gather optimizer statistics immediately, or the task failed to execute within a maintenance window, or you want to customize options to gather optimizer statistics.’

Gone through all five steps, and you will be able to pick your required status.

Click on Add button and you will get this.

Click ok to move next.

This table contains the table objects for which optimizer statistics will be gathered. Click Add to add table objects to the table.

Choose Job Name, Description, Select Immediately and press next.

Job has been completed successfully now click on submit button. This will run immediately as of now and will run when it was scheduled. This activity will update all the latest information in NUM_ROW column in DBA_TABLES.  We have covered how to gather db table states using GUI.

This was the GUI process to gather Optimizer statistics we can also gather stats from scripting as well. If I am going to start scripting in this post then, this post will become much longer. So, I have created another post for this purpose.You can get detailed information about Oracle Cost-Based Optimizer (CBO) and Database Statistics as well.