Checking Oracle advisors

The Oracle advisors (since 10g version) are a wonderful way to check the instance performance and tune your Oracle database, and you can see this information in an easy and intuitive way with Enterprise Manager, but sometimes you don't have Enterprise Manager running or maybe you would like to get this information quickly with a script. If this is the case, you can use this SQL sentences to check advisors' information:

SQL> set linesize 150
SQL> column pga_target_mb  format 999,999,990 
SQL> column estd_extra_MB  format 999,999,990 
SQL> column name           format A30
SQL> column value          format A30

SQL> select pga_target_for_estimate/1048576 pga_target_mb, pga_target_factor factor, 
estd_extra_bytes_rw/1048576 estd_extra_MB, estd_pga_cache_hit_percentage pga_hit, 
estd_overalloc_count over_alloc from v$pga_target_advice
where pga_target_factor between .75 and 3 order by pga_target_factor;

PGA_TARGET_MB     FACTOR ESTD_EXTRA_MB    PGA_HIT OVER_ALLOC
------------- ---------- ------------- ---------- ----------
          120        .75       490,015         44        901
          160          1       275,813         58        593
          192        1.2       254,315         60        565
          224        1.4       180,064         68        130
          256        1.6       154,973         71        130
          288        1.8       154,460         71         68
          320          2       153,986         71         46
          480          3       151,257         72          0

8 rows selected.

SQL> SELECT SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_DB_TIME_FACTOR, ESTD_PHYSICAL_READS
from v$sga_target_advice where sga_size_factor between .75 and 3 order by SGA_SIZE_FACTOR;

  SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR ESTD_PHYSICAL_READS
---------- --------------- ------------ ------------------- -------------------
       192             .75       698484              1.0397           297575225
       256               1       671813                   1           287818188
       320            1.25       660325               .9829           283644824
       384             1.5       637752               .9493           275384442
       448            1.75       637752               .9493           275384442
       512               2       637752               .9493           275384442

6 rows selected.

SQL> select NAME, VALUE from v$parameter 
where NAME in ('shared_pool_size','large_pool_size','java_pool_size');

NAME                           VALUE
------------------------------ ------------------------------
shared_pool_size               0
large_pool_size                0
java_pool_size                 0

SQL> select pool, name, bytes FROM V$SGASTAT where name = 'free memory';

POOL         NAME                                BYTES
------------ ------------------------------ ----------
shared pool  free memory                      15515352
large pool   free memory                       3118304
java pool    free memory                       4194304

But be careful! The Oracle advisors are not included for free and you have to have a Database Diagnostic Pack license to use this data, but even if you issue just one select on this views it is recorded for ever and you would be liable for using a product without license. And as a side note, if you think that you can audit product usage in your Oracle database for compliance in an easy and reliable way, think it twice.

More information:
Oracle licensing compliance is important
Oracle license audit