CLASS12_4 Gather Database Optimizer statistics using DBMS_STATS

 

Previously we have a discussion that why we need to collect optimizer statistics? How this work with our database to maintain best query execution plan?  We have learned that how can we collect or update database statistics using GUI interface (enterprise manager).

 

Now we are going to collect Oracle Database statistics using Package DBMS_STATS. 

 

dbms_stats.gather_table_stats

 

In the above examples, I have gathered database statistics (GATHER_TABLE_STATS). As you can easily distinguish that first I’ve run following select query to extract info regarding NUM_ROWS & LAST_ANALYZED for TABLE_NAME ‘DEPT2’.

 

SELECT OWNER, TABLE_NAME, NUM_ROWS, LAST_ANALYZED FROM DBA_TABLES WHERE TABLE_NAME =’DEPT2’;

 

This gave the output of ‘NUM_ROWS’ and ‘LAST_ANALYZED’ columns as null. It means information is not updated, or stats are not gathered. Then I used the package DBMS_STATS in order to get manual statistics

 
BEGIN
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME =>’SYS’, TABNAME => ‘DEPT2’);
END;
 
DBMS_STATS Package has run successfully and then I run the first query again to check whether optimized statistics have updated or not. Hence, you see NUM_ROWS containing the same values which are contained by the dept2 table and last_analyzed date has been updated as well.Similarly, we can gather schema stats as well. Not even schema but we can also get following object statistics./*

TABLE, INDEX, SCHEMA, DATABASE, DATA DICTIONARY, FIXED TABLE

*/

Related Post

Example:

BEGIN DBMS_STATS.gather_schema_stats(ownname => ‘SCOTT’

, options => ‘GATHER’);

Options can be equals to = (GATHER

GATHER AUTO

GATHER EMPTY

GATHER STALE)

 

GATHER = Gather all statistics from this schema,

Gather Auto = Oracle database automatically decides itself whether statistics are updated or not?

Gather Empty = collect statistics from those tables from  where statistics were not taken before I.e.  Table’s last_analyzed column is null.

Gather Stale =Gather statistics from those tables whose information made before but are not up to date.

 




  • Mikel

    View Comments

    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

    6 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

    11 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

    12 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

    12 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

    12 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

    1 year ago