How full is the current redo log file?

Here is a query that can tell us how full the current redo log file is. This is useful  when we  need to predict when the next log file will be archived out.

SQL> SELECT le.leseq                      "Current log sequence No",
          100*cp.cpodr_bno/le.lesiz         "Percent Full",
           cp.cpodr_bno                            "Current Block No",
           le.lesiz                                       "Size of Log in Blocks"
           FROM   x$kcccp  cp,    x$kccle  le
           WHERE    le.leseq =CP.cpodr_seq
           AND  bitand(le.leflg,24) = 8 ;

Sample Output :
Current log sequence No      Percent Full        Current Block No         Size of Log in Blocks
-----------------------                -------------           -----------------             ---------------------
                  7                       18.1982422               18635                        102400

Enjoy   J J J

How to gather statistics on data dictionary objects in Oracle

Before Oracle database 10g ,Oracle   explicitly recommended   not  to   gather   statistics   on  data dictionary   objects . As   of   Oracle database 10g   Oracle   explicitly does   recommend to   gather statistics on   data    dictionary   objects. As   we   might   know, there   is an   automatically   created   SCHEDULER JOB    in every   10g database   which runs   every night and   checks    for object    which   have either no statistics   at   all   or   for   which   the statistics   have   become   STALE   (which  means stat    at   least 10%   of   the  values have changed).   This   job   is   call GATHER_STATS_JOB and    belongs   to   the autotask   job   class.   

It   uses   a   program   which   again   call   a   procedure   from   built    in package DBMS_STATS   which does   the   statistics   collection. This   feature   only   works   if   the   initialization   parameter   STATISTICS_LEVEL   is   set to TYPICAL at   least   (which is the DEFAULT in 10g) and  it utilizes the TABLE MONITORING feature . TABLE  MONITORING is   enabled for  all tables in 10g by DEFAULT.

One question may come in our mind that  “Does  this job also collect   statistics on the data dictionary objects as well?” The answer is  “YES, it does!” and here is the proof  for this . First let us check if dbms_stats.gather_database_stats collect statistics for the data dictionary:

SQL> select count(*) from tab$;

COUNT(*) 
--------------
1227
SQL> create table t2 (col1 number);
Table created.

SQL> select count(*) from tab$;

COUNT(*)      
---------------
 1228

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
------------------
1213

SQL> exec dbms_stats.gather_database_stats;

PL/SQL procedure successfully completed.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;   

 NUM_ROWS
-------------------
1228             

IT DOES! – and now let’s see if the job does also: 

SQL> create table t3 (col1 number);

Table created.

SQL> create table t4 (col1 number);

Table created.

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
--------------
1228

Now  gather_stats_job run manually from DATABASE CONTROL !!! 

SQL> select NUM_ROWS from dba_tables where table_name=’TAB$’;

NUM_ROWS
-----------------
1230

and IT ALSO DOES! 

Even  though  there were  not  even 0.1%  of  the values changed it did!  So when should we gather statistics for the data dictionary manually? Oracle recommends to collect them when a significant  number of changes  were applied  to the data  dictionary,   like  dropping  significant  numbers  of  part ions  and creating new ones dropping tables, indexes, creating new ones and so on. But this only if it  is a significant number of changes and we cannot wait for the next automatically scheduled job run.         

Enjoy    :-)

What is the difference between V$ and GV$, also V$ and V_$ ?

These “$” views are called dynamic performance views. They are continuously updated while a database is open and in use, and their contents relate primarily to performance. The actual dynamic performance views are identified by the prefix V_$ .  Public synonyms for these views have the prefix V$ . We should access only the V$ objects, not the V_$ objects . Let's  have look on the object type  .
SQL>select owner, object_name, object_type  from dba_objects where object_name like '%SESSION'   and  object_name like 'V%' ;

Output :

OWNER        OBJECT_NAME               OBJECT_TYPE    
-----------     ---------------------           ------------------------
SYS              V_$SESSION                           VIEW
SYS              V_$HS_SESSION                     VIEW
SYS              V_$PX_SESSION                     VIEW
SYS              V_$DETACHED_SESSION         VIEW
SYS              V_$LOGMNR_SESSION             VIEW
SYS              V_$DATAPUMP_SESSION         VIEW
SYS              V$XS_SESSION                       VIEW
PUBLIC        V$SESSION                           SYNONYM
PUBLIC        V$HS_SESSION                     SYNONYM
PUBLIC        V$PX_SESSION                     SYNONYM
PUBLIC        V$DETACHED_SESSION         SYNONYM
PUBLIC        V$LOGMNR_SESSION             SYNONYM
PUBLIC        V$DATAPUMP_SESSION         SYNONYM
PUBLIC        V$XS_SESSION                     SYNONYM
Hence "v$xx" objects are synonym and "v$_xx"  are the views.


Let's have a look on the difference between V$ view and GV$ views . 

GV$ views are called 'Global Dymanic Performance views' and retrieve information about all started instances accessing one RAC database. In contrast, standard dynamic performance views (V$ views) retrieve information about the local instance only. For each of the V$ views available, there is a corresponding GV$ view except for a few exceptions. In addition to the V$ information, each GV$ view possesses an additional column name INST_ID. The INST_ID column displays the instance number from which the associated V$ view information is obtained. 

GV$  views use a special form of parallel execution. The parallel execution coordinator runs on the instance that the client connects to and one slave is allocated in each instance to query the underlying V$ view for that instance.

Enjoy     :-)