Help! What is wrong with this !

From: <sjoshi_at_ingr.com>
Date: 2000/07/20
Message-ID: <8l7g6b$ta$1@nnrp1.deja.com>#1/1

Hello PL/SQL Gurus:
 I getting errors of the type:

7/11     PLS-00201: identifier 'SYS.V$PARAMETER' must be declared
7/11     PL/SQL: Item ignored

10/19 PLS-00201: identifier 'SYS.V$SYSSTAT' must be declared 10/19 PL/SQL: Item ignored

With the following SQL when trying to run it thru. SQL/PLUS. Any pointers are greatly appreciated.
thanks
Sunit

CREATE OR REPLACE PROCEDURE checkTune
AS
v_dbname sys.v$parameter.value%TYPE;

 v_lchp NUMBER(3,2); --Library cache hit percent
 v_dchp NUMBER(3,2); --Dictionary cache hit percent
 v_consistentgets sys.v$sysstat.value%TYPE;
 v_dbblockgets sys.v$sysstat.value%TYPE;
 v_physicalreads sys.v$sysstat.value%TYPE;
 v_bchp NUMBER(3,2); --Buffer cache hit percent
 v_sortsdisk sys.v$sysstat.value%TYPE;
 v_sortsmem sys.v$sysstat.value%TYPE;
 v_sahp NUMBER(3,2); --Sort area hit percent
 v_iparm sys.v$parameter.name%TYPE;
 v_ivalue sys.v$parameter.value%TYPE;
 v_alloc NUMBER;
 v_free NUMBER;
 v_freePercent NUMBER;

BEGIN

	select round(sgasize/(1024*1024),2), round(bytes/(1024*1024),2),
	round(bytes/sgasize*100,2)
	Into v_alloc, v_free, v_freePercent
	from (select sum(bytes) sgasize from sys.v_$sgastat) s,
sys.v_$sgastat f
	where f.name='free memory';

dbms_output.put_line ('Allocated Memory(MB): ' || v_alloc);
dbms_output.put_line ('Free Memory(MB):      ' || v_free);
dbms_output.put_line ('% Free:               ' || v_freePercent);

• Get database name SELECT value INTO v_dbname FROM v$parameter WHERE name = 'db_name'; dbms_output.put_line ('Database name: '||v_dbname);

dbms_output.put_line ('Measurement                       Goal');
dbms_output.put_line ('------------------------------ --------');

• Get library cache hit percentage

    SELECT (1 - SUM(reloads)/SUM(pins)) INTO v_lchp FROM v$librarycache;     IF v_lchp < 0.99 THEN

       dbms_output.put_line ('Library cache hit percent      >=0.99
       '|| 'Results: ' ||to_char(v_lchp,'9999990.99')||' **Increase the
SHARED_POOL_SIZE in INIT.ORA');
       SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
name='shared_pool_size';
       dbms_output.put_line ('-- Current value: '||v_iparm||'

= '||v_ivalue);

    ELSE

       dbms_output.put_line ('Library cache hit percent      >=0.99


       '||'Results: ' ||to_char(v_lchp,'9999990.99')||' OK');

    END IF;
 --

• Get dictionary cache hit percentage

    SELECT (1 - SUM(getmisses)/SUM(gets)) INTO v_dchp FROM v$rowcache;     IF v_dchp < 0.90 THEN

       dbms_output.put_line ('Dictionary cache hit percent   >=0.90


       '||'Results: ' ||to_char(v_dchp,'9999990.99')||' **Increase the

SHARED_POOL_SIZE in INIT.ORA');
       SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
name='shared_pool_size';
       dbms_output.put_line ('-- Current value: '||v_iparm||'

= '||v_ivalue);

    ELSE

       dbms_output.put_line ('Dictionary cache hit percent   >=0.90


       '||'Results: ' ||to_char(v_dchp,'9999990.99')||' OK');

    END IF;
 --

• Get buffer cache hit percentage

    SELECT value INTO v_consistentgets FROM v$sysstat WHERE name = 'consistent gets';

    SELECT value INTO v_dbblockgets FROM v$sysstat WHERE name = 'db block gets';

    SELECT value INTO v_physicalreads FROM v$sysstat WHERE name = 'physical reads';

    v_bchp := 1 - (v_physicalreads/(v_consistentgets + v_dbblockgets));     IF v_bchp < 0.90 THEN

       dbms_output.put_line ('Buffer cache hit percent       >=0.90


       '||'Results: ' ||to_char(v_bchp,'9999990.99')||' **Increase the

DB_BLOCK_BUFFERS in INIT.ORA');
       SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
name='db_block_buffers';
       dbms_output.put_line ('-- Current value: '||v_iparm||'

= '||v_ivalue);

    ELSE

       dbms_output.put_line ('Buffer cache hit percent       >=0.90


       '||'Results: ' ||to_char(v_bchp,'9999990.99')||' OK');

    END IF;

• Get sort hit percentage

    SELECT value INTO v_sortsmem FROM v$sysstat WHERE name = 'sorts (memory)';

    SELECT value INTO v_sortsdisk FROM v$sysstat WHERE name = 'sorts (disk)';

    v_sahp := 1 - (v_sortsdisk)/(v_sortsmem + v_sortsdisk);     IF v_sahp < 0.90 THEN

       dbms_output.put_line ('Sort area hit percent          >=0.90


       '||'Results: ' ||to_char(v_sahp,'9999990.99')||' **Increase the

SORT_AREA_SIZE in INIT.ORA');
       SELECT name,value INTO v_iparm,v_ivalue FROM v$parameter WHERE
name='sort_area_size';
       dbms_output.put_line ('-- Current value: '||v_iparm||'

= '||v_ivalue);

    ELSE

       dbms_output.put_line ('Sort area hit percent          >=0.90


       '||'Results: ' ||to_char(v_sahp,'9999990.99')||' OK');

    END IF; END;
/

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jul 20 2000 - 00:00:00 CDT