In this lesson we will be discussing Database Statistics, why do we need to update or gather database Statistics?
How to gather oracle 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,
Creating table in oracle database.
- 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.
select num_rows from dba_tables
- Now I am going to insert some data into this newly created table.
oracle database statistics demo
- 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.
Manage Optimizer Statistics In Oracle 11g
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 in Oracle11g
Gather Optimizer Statistics link redirect us into following new page. Where you are ask to select object type from which you can collect stats.
Gather Optimizer Statistics wizard in oracle11g
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.
Gather table optimizer statistics in oracle11g
Click on Add button and you will get this.
select table from oracle optimizer statistics
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.
Gather optimizer statistics objects oracle11g
Gather optimizer statistics Schedule
Choose Job Name, Description, Select Immediately and press next.
Gather optimizer Statistics Review oracle11g
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.
select owner table_name num_rows last_analyzed from dba_tables
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.