Balance I/O

FAQ

Memberlist

Usergroups

Profile

Preferences

· nixdoc.net

· man pages

· Linux HOWTOs

· FreeBSD Tips

· Forums

Forum index » Databases » Oracle » Server

Balance I/O

Page 1 of 1 [9 Posts]

View previous topic :: View next topic

Author

Message

Paul
*nix forums Guru

Joined: 25 Feb 2005
Posts: 496

Posted: Thu Jul 20, 2006 9:15 pm Post subject: Note

Re: Balance I/O

Sybrand Bakker <postbus@sybrandb.demon.nl> wrote:

Quote:

Can one assume from this that you think that their Oracle offerings
leave something to be desired?

Quote:

Well they do, don't they?

Well, the one I bought, "Oracle SQL & CBO Internals" by Kimberly
Floss, President of the International Oracle Users Group (IOUG) no
less, took one look at it, and brought it back to the shop (yeah, I
know, but I had a book token burning a hole in my pocket and the shop
was closing) and they changed it for Jonathan Lewis' book - phew!
Lesson learnt, at least a glance at the reviews on Amazon before I
purchase anything from now on.

I thought that she should have some notion, but the book was a mess,
with stuff from 7 to 10 - I mean who wants to know about CBO from
1997? Even though some of the machines I log into still run 7, I'm not
going to kill myself learing about it.

Quote:

Actually there is a book of Rampant on I/O.
Do you think they mention mirroring and striping? SAME?
Direct I/O? Asynchronous I/O? The various quick I/O products out
there?

Nothing of the sort.

I did see one by Mladen Gogala and he was tech editor on another - he
seems to know his stuff - or is that not true either?

Maybe this will help us see clearly - from their site.

"Rampant is not motivated by profit. Because we put quality first,
Rampant can offer world-class technical books at a fraction of the
cost of other high-profit publishers."

I notice that Don Burleson's name seems prominent, and I know he's not
flavour of the month around here, but there was another guy with an
Indian (Arup Nanda?) name who's an ace (literally - top ranking
poster) on the oracle forums - you'd think he'd have a clue?

Paul...

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

Charles Hooper
*nix forums addict

Joined: 09 Jul 2006
Posts: 51

Posted: Thu Jul 20, 2006 12:23 pm Post subject: Note

Re: Balance I/O

astalavista wrote:

Quote:

hi,

I try to balance I/O, I use sar -d ( AIX 5.1 , oracle 9.2.0.4 )
Is it a good idea to decrease the disks which are near 100% busy ?
( 90% busy is it a low throuput disk ? )
Is it the right direction to look at %busy ?

Thanks in advance ...

100% busy is not desirable. Find out what files and events are
contributing to the problem. Do you have too many different types of
files on the same disk spindle? Do you have too many inefficient SQL
statements being processed? Did you not implement a large/fast enough
disk storage system? Making guesses based on apparent disk usage is
not sufficient. Start taking a look at what is causing the disk usage.
When you notice a performance problem, record the output of the
following SQL statements, executed once a minute for 10 minutes:

Redo log files and their size:
SELECT
LF.GROUP#,
LF.TYPE,
L.STATUS,
L.FIRST_TIME,
L.BYTES,
LF.MEMBER
FROM
V$LOGFILE LF,
V$LOG L
WHERE
L.GROUP#=LF.GROUP#
ORDER BY
LF.GROUP#;

Redo log file switch frequency (only need to execute this the last
time):
SELECT
LH2.RECID,
LH2.STAMP,
LH2.THREAD#,
LH2.SEQUENCE#,
LH2.FIRST_CHANGE#,
LH2.NEXT_CHANGE#,
LH1.FIRST_TIME PREV_FIRST_TIME,
LH2.FIRST_TIME,
ROUND((LH2.FIRST_TIME-LH1.FIRST_TIME)*24,2) HOURS
FROM
V$LOG_HISTORY LH1,
V$LOG_HISTORY LH2
WHERE
LH2.RECID=LH1.RECID+1
AND LH1.FIRST_TIME>TRUNC(SYSDATE-180)
ORDER BY
LH2.RECID DESC;

Activity of the data files and temp files:
SELECT
'DATA' FILE_TYPE,
FS.FILE#,
FS.PHYRDS,
FS.PHYWRTS,
FS.PHYBLKRD,
FS.PHYBLKWRT,
FS.READTIM,
FS.WRITETIM,
FS.AVGIOTIM,
FS.LSTIOTIM,
FS.MINIOTIM,
FS.MAXIORTM,
FS.MAXIOWTM,
DF.NAME
FROM
V$FILESTAT FS,
V$DATAFILE DF
WHERE
FS.FILE#=DF.FILE#
UNION ALL
SELECT
'TEMP' FILE_TYPE,
TS.FILE#,
TS.PHYRDS,
TS.PHYWRTS,
TS.PHYBLKRD,
TS.PHYBLKWRT,
TS.READTIM,
TS.WRITETIM,
TS.AVGIOTIM,
TS.LSTIOTIM,
TS.MINIOTIM,
TS.MAXIORTM,
TS.MAXIOWTM,
TF.NAME
FROM
V$TEMPSTAT TS,
V$TEMPFILE TF
WHERE
TS.FILE#=TF.FILE#
ORDER BY
1,
2;

Rollback segment usage:
SELECT
RN.NAME,
RN.USN,
RS.EXTENTS,
RS.RSSIZE,
RS.WRITES,
RS.XACTS,
RS.GETS,
RS.WAITS,
RS.OPTSIZE,
RS.HWMSIZE,
RS.SHRINKS,
RS.WRAPS,
RS.EXTENDS,
RS.AVEACTIVE,
RS.CUREXT,
RS.CURBLK
FROM
V$ROLLNAME RN,
V$ROLLSTAT RS
WHERE
RN.USN=RS.USN
ORDER BY
RN.NAME;

Also, take a broad look at the wait events that may be contributing to
disk utilization and other delays in the system. Once again, execute
once a minute for 10 minutes:
SELECT
0 SID,
TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI')
TIME_STAMP,
EVENT,
TOTAL_WAITS,
TOTAL_TIMEOUTS,
TIME_WAITED,
AVERAGE_WAIT
FROM
V$SYSTEM_EVENT
WHERE
EVENT IN (
'buffer busy waits',
'db file sequential read',
'db file scattered read',
'direct path read',
'direct path write',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync')
UNION ALL
SELECT
SID,
TO_DATE(TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI'),'YYYYMMDD HH24:MI')
TIME_STAMP,
EVENT,
TOTAL_WAITS,
TOTAL_TIMEOUTS,
TIME_WAITED,
AVERAGE_WAIT
FROM
V$SESSION_EVENT
WHERE
EVENT IN (
'buffer busy waits',
'db file sequential read',
'db file scattered read',
'direct path read',
'direct path write',
'enqueue',
'free buffer waits',
'latch free',
'log file parallel write',
'log file sync');

If you prefer, you can create a quick logging table by inserting this
SQL code before the first run of the above SQL statement (remove prior
to the second execution of the SQL statement):
CREATE TABLE
SYSTEM_LOG
AS

With the above logging table in place, you can quickly check for
problem areas:
SELECT
SL2.SID,
SL2.TIME_STAMP,
SL2.EVENT,
SL2.TOTAL_WAITS-NVL(SL1.TOTAL_WAITS,0) WAITS,
SL2.TOTAL_TIMEOUTS-NVL(SL1.TOTAL_TIMEOUTS,0) TIME_OUTS,
SL2.TIME_WAITED-NVL(SL1.TIME_WAITED,0) TIME_WAITED,
SL2.AVERAGE_WAIT
FROM
SYSTEM_LOG SL1,
SYSTEM_LOG SL2
WHERE
SL2.SID=SL1.SID(+)
AND SL2.TIME_STAMP=SL1.TIME_STAMP(+) + (1/24/60)
AND SL2.EVENT=SL1.EVENT(+)
ORDER BY
SID,
SL2.TIME_STAMP,
SL2.EVENT;

If you find a problem area where the SID is listed as 0, look for the
largest contributor in the same time period where the SID is greater
than 0. For example, if the delta value of time waited for db file
scattered read where SID=0 seems consistently high, that is an
indication that full table scans are likely contributing to the system
performance problems. You can then look at that value for the various
sessions to determine which session is contributing the most to the
performance problem. For there, you can enable a 10046 trace for the
session to determine the SQL statements executed by the session, the
wait events that result from the execution of the SQL statement, and
the file and block number (or other information present with other
types of waits) involved in the wait, which may be used to determine
the table that is the greatest contributor. This may lead you to look
at the statistics for the table and indexes on the table, where you may
find that the statistics for the table and indexes are stale -
statistics show that the table has 10 rows with an average size of 100
bytes per row, when there are actually 1,000,000 rows in the table.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Sybrand Bakker
*nix forums Guru

Joined: 03 Apr 2005
Posts: 1766

Posted: Thu Jul 20, 2006 5:19 am Post subject: Note

Re: Balance I/O

On Wed, 19 Jul 2006 23:16:08 +0100, Paul <paul@see.my.sig.com> wrote:

Quote:

Can one assume from this that you think that their Oracle offerings
leave something to be desired?

Paul...

Well they do, don't they?
Actually there is a book of Rampant on I/O.
Do you think they mention mirroring and striping? SAME?
Direct I/O? Asynchronous I/O? The various quick I/O products out
there?

Nothing of the sort.

--
Sybrand Bakker, Senior Oracle DBA

Paul
*nix forums Guru

Joined: 25 Feb 2005
Posts: 496

Posted: Wed Jul 19, 2006 10:16 pm Post subject: Note

Re: Balance I/O

"fitzjarrell@cox.net" <fitzjarrell@cox.net> wrote:

Quote:

I'll bet a method can be found in any of the Rampant Press offerings

Can one assume from this that you think that their Oracle offerings
leave something to be desired?

Paul...

Quote:

David Fitzjarrell

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

Chuck Whealton
*nix forums beginner

Joined: 16 Jul 2006
Posts: 10

Posted: Wed Jul 19, 2006 1:29 am Post subject: Note

Re: Balance I/O

astalavista wrote:

Quote:

Like another poster said, tuning definately isn't easy or fun.

You could stagger your I/O over multiple LUNS. I believe AIX still
uses LVM, right? You could also use some of the freely avaiable SQL
procedures to see which of your tables is getting hit the hardest and
relocate them to try and smooth out I/O, if necessary.

Charles R. Whealton
Charles Whealton @ pleasedontspam.com

Mladen Gogala
*nix forums Guru

Joined: 11 Mar 2005
Posts: 948

Posted: Tue Jul 18, 2006 9:56 pm Post subject: Note

Re: Balance I/O

fitzjarrell@cox.net wrote:

Quote:

Whaddya mean by "decrease the disks"? Get rid of surplus tracks by using
a hacksaw or rasp? I'm not sure that it's a good idea. Second, tuning is
a hard thing to do, but decreasing usage of something that completely
used up sounds like a plan. Exactly how do you do that?

--
Mladen Gogala
http://www.mgogala.com

I'll bet a method can be found in any of the Rampant Press offerings
...

David Fitzjarrell

Not in Easy Oracle PHP book, if that's what you're getting at.

--
Mladen Gogala
http://www.mgogala.com

fitzjarrell@cox.net
*nix forums Guru

Joined: 19 Jul 2005
Posts: 444

Posted: Tue Jul 18, 2006 9:38 pm Post subject: Note

Re: Balance I/O

Mladen Gogala wrote:

Quote:

astalavista wrote:
hi,

I try to balance I/O, I use sar -d ( AIX 5.1 , oracle 9.2.0.4 )
Is it a good idea to decrease the disks which are near 100% busy ?
( 90% busy is it a low throuput disk ? )
Is it the right direction to look at %busy ?

Thanks in advance ...

Whaddya mean by "decrease the disks"? Get rid of surplus tracks by using
a hacksaw or rasp? I'm not sure that it's a good idea. Second, tuning is
a hard thing to do, but decreasing usage of something that completely
used up sounds like a plan. Exactly how do you do that?

--
Mladen Gogala
http://www.mgogala.com

I'll bet a method can be found in any of the Rampant Press offerings
....

David Fitzjarrell

Mladen Gogala
*nix forums Guru

Joined: 11 Mar 2005
Posts: 948

Posted: Tue Jul 18, 2006 8:17 pm Post subject: Note

Re: Balance I/O

astalavista wrote:

Quote:

Vincent M
*nix forums Guru

Joined: 22 Feb 2005
Posts: 1332

Posted: Tue Jul 18, 2006 7:29 pm Post subject: Balance I/O

hi, I try to balance I/O, I use sar -d ( AIX 5.1 , oracle 9.2.0.4 ) Is it a good idea to decrease the disks which are near 100% busy ? ( 90% busy is it a low throuput disk ? ) Is it the right direction to look at %busy ? Thanks in advance ...

Google

Display posts from previous:

Page 1 of 1 [9 Posts]

View previous topic :: View next topic

The time now is Fri Nov 21, 2008 9:47 pm | All times are GMT

Forum index » Databases » Oracle » Server

Jump to:

Topic		Author	Forum	Replies	Last Post
Similar Topics
	balance I/O	Vincent M	AIX	2	Tue Jul 18, 2006 7:07 pm
	load balance solaris8	lancerset@gmail.com	Solaris	3	Fri Jul 07, 2006 4:50 pm
	hardware to load balance squid proxies?	Adrian	Squid	4	Mon Apr 17, 2006 1:58 pm
	load balance	MD. Jahidul Hasan	Postfix	5	Thu Feb 23, 2006 8:23 am
	Load Balance	Cafer Şimşek	Postfix	4	Fri Feb 03, 2006 2:48 pm

PHP Photo Gallery | Loans | Loans | Repair Bad Credit | Watch Anime Online

Other DeniX Solutions sites: Unix/Linux blog | electronics forum | medicine forum | science forum |

[ Time: 4.7042s ][ Queries: 20 (4.5293s) ][ GZIP on - Debug on ]