Re: V$SGA_TARGET_ADVICE in Oracle RDBMS 10.2

From: fairlie rego <fairlie_r_at_yahoo.com>
Date: Mon, 15 Aug 2005 00:21:12 -0700 (PDT)
Message-ID: <20050815072112.80140.qmail@web31914.mail.mud.yahoo.com>

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

Mladen Gogala <gogala_at_sbcglobal.net> wrote: Select from V$SGA_TARGET_ADVICE fails on Oracle 10.2 for Linux. The error reported is about division by zero:

SQL> select * from v$sga_target_advice;
select * from v$sga_target_advice
*
ERROR at line 1:
ORA-01476: divisor is equal to zero

I discovered this by attempting to run STATSPACK. Of course, it bombed out with the same error. SGA parameters are like this:

SQL> show parameter sga

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE

pre_page_sga boolean FALSE
sga_max_size big integer 400M
sga_target big integer 352M

Does anybody have any idea? Is this just one of the "features"? Statspack is fairly important tool and I'd hate to run a production system on something like this. The description of this table is here:

V$SGA_TARGET_ADVICE V$SGA_TARGET_ADVICE provides information about the SGA_TARGET initialization parameter. Column Datatype Description
SGA_SIZE NUMBER Size of the SGA
SGA_SIZE_FACTOR NUMBER Ratio between the SGA_SIZE and the current size of the SGA

ESTD_DB_TIME NUMBER Estimated DB_TIME for this SGA_SIZE
ESTD_DB_TIME_FACTOR NUMBER Ratio between ESTD_DB_TIME and DB_TIME for the current size of the SGA
ESTD_PHYSICAL_READS NUMBER Estimated number of physical reads

This table contains two ratios and one of them must be zero for an almost inactive database like this one. Now I know why is 10.2 out only for Linux.

--

http://www.mgogala.com

--

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

Fairlie Rego
Senior Oracle Consultant
Optus Telecommunications
www.optus.com.au
Mobile: +61 4 02 792 405

When I read about the evils of drinking, I gave up reading.

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 15 2005 - 02:24:59 CDT