Oracle-L: Re: HP-UX 11.0 mount parameter mincache

From: chao_ping <chao_ping_at_vip.163.com>
Date: Wed, 15 Jan 2003 04:55:04 -0800
Message-ID: <F001.0053021C.20030115045504@fatcity.com>

tony ynot,

		Hi, what is your physical memory and sga? If set mincache=direct, enlarge your sga to a higher value(maybe half of physical memory according to your connections and parameters like sort_).
		Since enlarge the db_block_buffer does help, why set it back smaller?
		Since so much io wait, maybe your system is really weak in io capacity, or maybe your sql statement is poor.Try using statspack to find the top sql doing io and tune them.In most case, it helps.
		I have a small utility that helps you find the session doing the most io:

CREATE TABLE SESSTAT
(snapid number,
SID NUMBER,
timestamp DATE DEFAULT SYSDATE,
statistic# NUMBER,
name varchar2(64),
VALUE NUMBER
)
/

create sequence snap
/

CREATE OR REPLACE PROCEDURE SNAPSHOT
(inp_name1 varchar default 'redo size',

inp_name2 varchar default 'physical reads',
inp_name3 varchar default 'session logical reads',
inp_name4 varchar default 'CPU used by this session',
inp_name5 varchar default 'user commits')

as
current_snapid number;
begin
select snap.nextval into current_snapid from dual; INSERT INTO SESSTAT (snapid,sid,timestamp,statistic#,name,value) SELECT current_snapid,a.sid,SYSDATE,a.statistic#,b.name,a.value FROM v$sesstat a,v$statname b where a.statistic# = b.statistic#
and b.name in (inp_name1,inp_name2,inp_name3,inp_name4,inp_name5); commit;
end;
/

from sqlplus, do:
SQL>exec snapshot,

and after sometime(eg, 10 minutes),
do snapshot again and run this sql:

----this is the first report.

col beginid heading "snap id" new_value beginid format 999999 col endid heading "snap id" new_value endid format 999999

select max(snapid)-1 beginid from sesstat; select max(snapid) endid from sesstat; variable beginid number;
variable endid number;
begin

	:beginid :=&beginid;
	:endid := &endid;

end;
/

SET line 250
col username format a15
col program format a20
col machine format a10
col name format a25
col osuser format a10
col sid format 999
col snapid format 999

prompt ORDER BY COLUMN &name
variable name VARCHAR2(64);
BEGIN
:name := '&name';
END;
/
££here,you can input physical reads, or redo size.

SELECT *FROM (
select a.snapid,a.sid,c.username,c.process,c.machine,c.osuser,c.program,a.name,b.value-a.value "difference_value" from sesstat a,sesstat b ,v$session where a.sid=b.sid and a.name=b.name and b.snapid=a.snapid+1

AND a.sid=c.sid
and b.value- a.value >0
and b.snapid=:endid
AND a.name=:name

order by b.value-a.value DESC
)
WHERE ROWNUM <=10
/

Regards
zhu chao
msn:chao_ping_at_163.com
www.happyit.net
www.cnoug.org(China Oracle User Group)

2003-01-14 17:28:00 ,you wrote£º=======

>We have HP-UX 11.0 and are having IO problems.
>Oracle and HP say to set a mount parameter
>mincache=direct. We currently have it set
>to mincache=tmpcache. But when we set it
>to "direct" the db slows down to the point it's
>unusable. HP said to increase the db_block_buffers,
>so we increased then from 30k to 130k (8k block).
>That's 1GB. The db was a little more usable but
>still sucked. So we set the buffers back to 30k
>and reset mincache back to tmpcache. Now the
>db is again usable, but we there's still something
>wrong with IO. sar reports WIO at +80. ORacle
>says that should be close to 0. We are using
>HP's LVM (resold veritas) and cooked files.
>Any ideas?
>thanks
>
>__________________________________________________
>Do you Yahoo!?
>Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
>http://mailplus.yahoo.com
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: tony ynot
> INET: tony_ynot_at_yahoo.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

= = = = = = = = = = = = = = = = = = =

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: chao_ping
  INET: chao_ping_at_vip.163.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jan 15 2003 - 06:55:04 CST