Re: V$SGA_TARGET_ADVICE in Oracle RDBMS 10.2

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Mon, 15 Aug 2005 11:19:18 +0000
Message-Id: <1124104758l.2788l.0l@medo.noip.com>

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


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

Received on Mon Aug 15 2005 - 06:21:54 CDT