Re: V$SGA_TARGET_ADVICE in Oracle RDBMS 10.2

  • From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
  • To: fairlie rego <fairlie_r@xxxxxxxxx>
  • Date: Mon, 15 Aug 2005 11:19:18 +0000

On 08/15/2005 03:21:12 AM, fairlie rego wrote:
> Hi Mladen,
>  
> Hmmm I do not have the same problem...
>  
>  
> SQL> select * from v$sga_target_advice;
>  
>   SGA_SIZE SGA_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR 
> ESTD_PHYSICAL_READS
> ---------- --------------- ------------ ------------------- 
> -------------------
>        424               1           65                   1                
> 7565
>        106             .25           65                   1                
> 7630
>        212              .5           65                   1                
> 7565
>        848               2           65                   1                
> 7565
>        530            1.25           65                   1                
> 7565
>        636             1.5           65                   1                
> 7565
>        742            1.75           65                   1                
> 7565
>        318             .75           65                   1                
> 7565
> 8 rows selected.
>  
> So generate an errorstack and send me the file and we'll see which ratio is 
> the problem...
>  
> SQL> alter session set events '1476 trace name errorstack level 3';
> Session altered.
> 
> Reissue the statement 
>  
> Regards,
> Fairlie



Here is the heart of
the problem:

  1* select base_estd_dbtime,base_sgasz,base_estd_phy_reads from x$kmgsbsadv
SQL> /

BASE_ESTD_DBTIME BASE_SGASZ BASE_ESTD_PHY_READS
---------------- ---------- -------------------
              35        352                   0
              35        352                   0
              35        352                   0
              35        352                   0
              35        352                   0
              35        352                   0
              35        352                   0
              35        352                   0

As you can see, estimated physical reads are 0. The view definition is:

select A.inst_id, A.sgasz, round((A.sgasz / A.base_sgasz), 4),
round(A.base_dbtime * round((A.dbtime / A.base_estd_dbtime), 4), 0),
round((A.dbtime / A.base_estd_dbtime), 4), round(A.base_phy_reads *
round((A.estd_physical_reads / A.base_estd_phy_reads), 4), 0) from x$kmgsbsadv A
order by A.inst_id

The last column causes division by zero. This is an interface to kernel
management routines and one of them should prevent this number from ever 
becoming zero. 


-- 
Mladen Gogala
http://www.mgogala.com


--
//www.freelists.org/webpage/oracle-l

Other related posts: