Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: performance problem when migrate 7.3.4 to 8.0.5

Re: performance problem when migrate 7.3.4 to 8.0.5

From: Richard Armstrong <StateOArt_at_worldnet.att.net>
Date: Thu, 20 May 1999 16:07:46 -0600
Message-ID: <7i20ur$mk9$1@bgtnsc02.worldnet.att.net> 





Try this script.  It might point you in the right direction.  Warning... I
have not tested this script for Oracle 8, so you might have to tweak it for
new views or tables.  Also, several init.ora parameters have changed, so you
might have to interpret the correct keyword.



spool snapshot.txt


prompt****************************************************


prompt Hit Ratio Section

prompt****************************************************


prompt

prompt  =========================




prompt  BUFFER HIT RATIO

prompt  =========================




prompt (should be > 70, else increase db_block_buffers in init.ora)



--select trunc((1-(sum(decode(name,'physical reads',value,0))/






column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
                c.value   "phys_reads",
                d.value   "phy_writes",
                round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
                "BUFFER HIT RATIO"




from v$sysstat a, v$sysstat b, v$sysstat c, v$sysstat d
where


        a.statistic# = 37


and


        b.statistic# = 38


and


        c.statistic# = 39


and


        d.statistic# = 40;



prompt


prompt

prompt  =========================




prompt  DATA DICT HIT RATIO

prompt  =========================




prompt (should be higher than 90 else increase shared_pool_size in init.ora)



prompt



column "Data Dict. Gets"   format 999,999,999
column "Data Dict. cache misses" format 999,999,999
select sum(gets) "Data Dict. Gets",

                sum(getmisses) "Data Dict. cache misses",
                trunc((1-(sum(getmisses)/sum(gets)))*100) "DATA DICT CACHE


HIT RATIO"



from v$rowcache;


prompt

prompt  =========================




prompt  LIBRARY CACHE MISS RATIO

prompt  =========================




prompt (If > .1, i.e., more than 1% of the pins resulted in reloads, then
increase the shared_pool_size in init.ora)
prompt

column "LIBRARY CACHE MISS RATIO" format 99.9999
column "executions" format 999,999,999
column "Cache misses while executing" format 999,999,999
select sum(pins) "executions", sum(reloads) "Cache misses while executing",
                (((sum(reloads)/sum(pins)))) "LIBRARY CACHE MISS RATIO"


from v$librarycache;



prompt

prompt  =========================




prompt  Library Cache Section

prompt  =========================




prompt hit ratio should be > 70, and pin ratio > 70 ...
prompt



column "reloads" format 999,999,999


select namespace, trunc(gethitratio * 100) "Hit ratio",
trunc(pinhitratio * 100) "pin hit ratio", reloads "reloads"
from v$librarycache;


prompt


prompt

prompt  =========================




prompt  REDO LOG BUFFER

prompt  =========================




prompt


set heading off


column value format 999,999,999


select substr(name,1,30),

                value




from v$sysstat where name = 'redo log space requests';



set heading on


prompt


prompt


prompt



column bytes format 999,999,999


select name, bytes from v$sgastat where name = 'free memory';



prompt

prompt****************************************************


prompt SQL Summary Section

prompt****************************************************


prompt


column "Tot SQL run since startup" format 999,999,999
column "SQL executing now"  format 999,999,999
select sum(executions) "Tot SQL run since startup",

                sum(users_executing) "SQL executing now"
                from v$sqlarea;





prompt


prompt

prompt****************************************************


prompt Lock Section

prompt****************************************************


prompt

prompt  =========================




prompt SYSTEM-WIDE LOCKS - all requests for locks or latches

prompt  =========================




prompt


select substr(username,1,12) "User",

                substr(lock_type,1,18) "Lock Type",
                substr(mode_held,1,18) "Mode Held"
        from sys.dba_lock a, v$session b
        where lock_type not in ('Media Recovery','Redo Thread')
        and a.session_id = b.sid;




prompt

prompt  =========================




prompt DDL LOCKS - These are usually triggers or other DDL

prompt  =========================




prompt

select substr(username,1,12) "User",
                substr(owner,1,8) "Owner",
                substr(name,1,15) "Name",
                substr(a.type,1,20) "Type",
                substr(mode_held,1,11) "Mode held"
from sys.dba_ddl_locks a, v$session b
        where a.session_id = b.sid;





prompt

prompt  =========================




prompt  DML LOCKS - These are table and row locks...

prompt  =========================




prompt

select substr(username,1,12) "User",
                substr(owner,1,8) "Owner",
                substr(name,1,20) "Name",
                substr(mode_held,1,21) "Mode held"
from sys.dba_dml_locks a, v$session b
        where a.session_id = b.sid;





prompt


prompt

prompt****************************************************


prompt Latch Section

prompt****************************************************


prompt if miss_ratio or immediate_miss_ratio > 1 then latch
prompt contention exists, decrease LOG_SMALL_ENTRY_MAX_SIZE in init.ora
prompt


column "miss_ratio" format 99999.99


column "immediate_miss_ratio" format 99999.99
select substr(l.name,1,30) name,

        (misses/(gets+.001))*100 "miss_ratio",
        (immediate_misses/(immediate_gets+.001))*100
                "immediate_miss_ratio"
        from v$latch l, v$latchname ln
        where l.latch# = ln.latch#
        and

        (misses/(gets+.001))*100 > .2
or
        (immediate_misses/(immediate_gets+.001))*100 > .2


)


order by l.name;



prompt


prompt

prompt****************************************************


prompt Rollback Segment Section

prompt****************************************************


prompt if any count below is > 1% of the total number of requests for data
prompt then more rollback segments are needed
 --column count format 999,999,999


select class, count


        from v$waitstat


where class in ('free list','system undo header','system undo block',

                        'undo header','undo block')


group by class,count;



column "Tot # of Requests for Data" format 999,999,999,999
select sum(value) "Tot # of Requests for Data" from v$sysstat where
name in ('db block gets', 'consistent gets');
prompt

prompt  =========================




prompt  ROLLBACK SEGMENT CONTENTION

prompt  =========================




prompt


prompt   If any ratio is > .01 then more rollback segments are needed



column "Ratio" format 99.99999


select name, waits, gets, waits/gets "Ratio"


        from v$rollstat a, v$rollname b
where a.usn = b.usn;



column "total_waits" format 999,999,999
column "total_timeouts" format 999,999,999
prompt


prompt


set feedback on;

prompt****************************************************


prompt Session Event Section

prompt****************************************************


prompt if average-wait > 0 then contention exists
prompt

        select substr(event,1,30) event,
                total_waits, total_timeouts, average_wait


 from v$session_event


where average_wait > 0 ;


--or total_timeouts > 0;





prompt


prompt

prompt****************************************************


prompt Queue Section

prompt****************************************************


prompt average wait for queues should be near zero ...
prompt


column "totalq" format 999,999,999


column "# queued" format 999,999,999


select paddr, type "Queue type", queued "# queued", wait, totalq,
decode(totalq,0,0,wait/totalq) "AVG WAIT" from v$queue;



set feedback on;


prompt


prompt


--prompt****************************************************


--prompt Multi-threaded Server Section


--prompt****************************************************


--prompt


--prompt If the following number is > 1


--prompt then increase MTS_MAX_SERVERS parm in init.ora


--prompt







--prompt


--prompt If the following number increases, consider adding dispatcher


processes



--prompt







--set feedback off;


--prompt


--prompt


--prompt        =========================


--prompt        DISPATCHER USAGE


--prompt        =========================


--prompt (If Time Busy > 50, then change MTS_MAX_DISPATCHERS in init.ora)


--column "Time Busy" format 999,999.999


--column busy  format 999,999,999


--column idle  format 999,999,999


--select name, status, idle, busy,







--prompt


--prompt


--select count(*) "Shared Server Processes"







--prompt


--prompt


--prompt high-water mark for the multi-threaded server


--prompt





--select * from v$mts;





--prompt


--prompt****************************************************


--prompt file i/o should be evenly distributed across drives.


--prompt





--select


--substr(a.file#,1,2) "#",


--substr(a.name,1,30) "Name",


--a.status,


--a.bytes,


--b.phyrds,


--b.phywrts


--from v$datafile a, v$filestat b


--where a.file# = b.file#;





--select substr(name,1,55) system_statistic, value







spool off;



--






Richard Armstrong


State Of The Art Consulting, Inc.


http://www.stateoart.com





cz <zhangc_at_netcom.ca> wrote in message


news:7i1lhi$ago$1_at_clio.net.metrotor.on.ca...

> We just migrated our Oracle server from 7.3.4 to 8.0.5 for one of our
> application with fat client built by PowerBuilder and we encounted an
> EXTREMELY slow performance problem. What could it be?
>
>




Received on Thu May 20 1999 - 17:07:46 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US