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.
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
*/
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.
What is the Main Cause of a Heart Attack? What is its Solution? A heart attack is the blockage of… Read More
In the vast economic arena, one term that often takes center stage, inciting extensive debates and discussions, is the "debt… Read More
De-Dollarization: The Changing Face of Global Finance The financial landscape is in a state of flux, with an intriguing economic… Read More
The curtains closed on a dramatic Bundesliga season with Bayern Munich standing tall once again, clinching their 11th straight title.… Read More
The Unfolding Story of Celine Dion's Health In recent news that has left fans across the globe stunned, iconic singer… Read More
As the echoes of the recent NBA season start to fade, the attention of enthusiasts is firmly glued to one… Read More
View Comments
@Mikel This has gone over my head but hopefully the Net will find it useful. Good Luck with your efforts.