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?

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,

DNAME VARCHAR2(27),

LOC VARCHAR2(5));

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.

Related Post

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.

 




  • Rameez

    Recent Posts

    Heart Attack Causes and its Solution

    What is the Main Cause of a Heart Attack? What is its Solution? A heart attack is the blockage of… Read More

    4 months ago

    Understanding the Debt Ceiling: Its Impact, Importance, and Implications

    In the vast economic arena, one term that often takes center stage, inciting extensive debates and discussions, is the "debt… Read More

    9 months ago

    De-Dollarization: The New World Order of Currency and Its Global Impact

    De-Dollarization: The Changing Face of Global Finance The financial landscape is in a state of flux, with an intriguing economic… Read More

    10 months ago

    Unstoppable Bayern Munich: The Story Behind Their 11th Consecutive Bundesliga Title

    The curtains closed on a dramatic Bundesliga season with Bayern Munich standing tall once again, clinching their 11th straight title.… Read More

    10 months ago

    Celine Dion Cancels Concert Tour Due to Deteriorating Stiff-Person Syndrome

    The Unfolding Story of Celine Dion's Health In recent news that has left fans across the globe stunned, iconic singer… Read More

    10 months ago

    Navigating the Crossroads: LeBron James, Anthony Davis, and the LA Lakers’ Uncertain Future

    As the echoes of the recent NBA season start to fade, the attention of enthusiasts is firmly glued to one… Read More

    10 months ago