Re: V$SGA_TARGET_ADVICE in Oracle RDBMS 10.2

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Mon, 15 Aug 2005 11:22:09 GMT
Message-Id: <pan.2005.08.15.11.22.09.477040@sbcglobal.net>

On Mon, 15 Aug 2005 06:45:40 +0000, Jonathan Lewis wrote:

> Look in v$fixed_view_definition for the
> SQL that defines gv$sga_target_advice.
> As a short term measure on a test db,
> you can always create your own gv$
> and v$ views to overwrite the proper
> ones.

Yup, I did that:
  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.

-- 
http://www.mgogala.com

Received on Mon Aug 15 2005 - 06:22:09 CDT