A simple question!!!

[see_one]

Hi,

I got this procedure which I am running on SQL PLus. After I paste and hit enter it tells me the procedure has been created successfully. What I don't know how to run it so that I can the report? If you can help me that would be really helpful to me. Thanks.


SQL> DECLARE
2 libcac number(10,2);
3 rowcac number(10,2);
4 bufcac number(10,2);
5 redlog number(10,2);
6 spsize number;
7 blkbuf number;
8 logbuf number;
9 BEGIN
10 select value into redlog from v$sysstat
11 where name = 'redo log space requests';
12 select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
13 select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
14 select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
15 from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
16 where cur.statistic# = ncu.statistic#
17 and ncu.name = 'db block gets'
18 and con.statistic# = nco.statistic#
19 and nco.name = 'consistent gets'
20 and phys.statistic# = nph.statistic#
21 and nph.name = 'physical reads';
22 select value into spsize from v$parameter where name = 'shared_pool_size';
23 select value into blkbuf from v$parameter where name = 'db_block_buffers';
24 select value into logbuf from v$parameter where name = 'log_buffer';
25 dbms_output.put_line('> SGA CACHE STATISTICS');
26 dbms_output.put_line('> ********************');
27 dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
28 dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
29 dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
30 dbms_output.put_line('> Redo Log space requests = '||redlog);
31 dbms_output.put_line('> ');
32 dbms_output.put_line('> INIT.ORA SETTING');
33 dbms_output.put_line('> ****************');
34 dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
35 dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Blocks');
36 dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
37 dbms_output.put_line('> ');
38 if
39 libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
40 END IF;
41 if
42 rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
43 END IF;
44 if
45 bufcac < 90 then dbms_output.put_line('*** HINT: Buffer Cache too low! Increase the DB Block Buffer value.');
46 END IF;
47 if
48 redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
49 END IF;
50 END;
51 /

PL/SQL procedure successfully completed.

[Axr2]

set serveroutput on

[see_one]

thanks a bunch.

[slimdave]

There's also the question of the functionality of the procedure, of course. As has been much discussed, most of these ratios (particularly the buffer cache hit) are meaningless. With one bad piece of SQL running on your application (and slowing it down) your LIO's could be raised enough to give you a great cache hit ratio -- obviously this is not a good thing.

[Axr2]

Originally posted by slimdave
There's also the question of the functionality of the procedure, of course. As has been much discussed, most of these ratios (particularly the buffer cache hit) are meaningless. With one bad piece of SQL running on your application (and slowing it down) your LIO's could be raised enough to give you a great cache hit ratio -- obviously this is not a good thing.

Based on the initial question, I presumed the poster was new to Oracle altogether. In which case, there's not much point going into a discussion about hit ratios.

[slimdave]

Originally posted by Axr2
Based on the initial question, I presumed the poster was new to Oracle altogether. In which case, there's not much point going into a discussion about hit ratios.

It's never too early to learn, though.

[Axr2]

Here is a very enlightening article about hit ratios.

http://www.jlcomp.demon.co.uk/fhr.rtf

[DaPi]

It is becoming more widely appreciated that it is not a good idea to rely on hit ratios as a method for assessing the efficiency of an Oracle database system. But it is important to remember that an idea is not automatically bad simply because it has gone out of fashion.