RE: What can cause boost in LIO/PIO?

From: Freeman Robert - IL <FREEMANR_at_tusc.com>
Date: Fri, 20 Jun 2003 09:19:26 -0700
Message-ID: <F001.005B64B4.20030620090510@fatcity.com>

What was this 5am job? It wasn't an analyze was it?

-----Original Message-----
To: Multiple recipients of list ORACLE-L Sent: 6/20/2003 11:29 AM

Hey all,

I'm testing out the max I/O thruput of an IBM FastT900 using a dual 2.4GHz
w/1GB RAM on Win2K server (not my choice but it's just for testing) running
Oracle 8.1.7. For one of my tests, I created the following heirarchical query:

SELECT *
FROM MYBIGTABLE GL1
START WITH GL1.TIME_STAMP LIKE '%40%'
CONNECT BY PRIOR REFERENCENO = WORKORDERNO; Here's the explain plan as TOAD sees it (mangled by e-mail):

Operation         Object Name   Rows    Bytes   Cost
SELECT STATEMENT Optimizer Mode=CHOOSE          95 K            36477
  CONNECT BY
    INDEX FAST FULL SCAN        MBT_TRANSTYPE   477 K   8 M     13165

TABLE ACCESS BY USER ROWID MYBIGTABLE ROWID ROW L
PARTITION RANGE ALL

1       8
      TABLE ACCESS FULL MYBIGTABLE      95 K    9 M     36477
1       8

The CONNECT BY was purposely done against non-indexed columns so as to have
the query consume more IO. Also, the instance was "detuned" to only have
less than 8MB of buffer cache. And I had started a full direct export of
the DB in an attempt to flush the SAN's 2GB shared cache. I want to see how
much real physical IO this puppy can produce. I know, I know, there are several other factors involved. I don't have the SAN available to me for
long for testing, so it's the best I could think of on short notice. Anyway...

I ran several of these concurrently, staggering the start of each randomly
by up to a minute. After introducing other loads in order to max out the
SAN and after the full export of the 30GB DB completed, I started killing of
the queries one-by-one, noticing the change in activity on the DB for each
kill. I left four queries running when I left last night to see how long
they'd take (no real purpose, just for kicks).

One of the tools I'm using to monitor is Quest's Spotlight on Oracle. When
I got in this AM, I noticed in SoO that the four queries were still running.
I think I maxed out the SAN. System-wide event waits were over 3000ms/s.
OK, to my question (finally!):

The wierd thing is that after a 5:00ish AM DBMS_JOB completed, the LIOs and
PIOs for the same four queries jumped dramatically. Why would this happen?
CPU usage was fairly constant and there were no other processes active. I'm
guessing this is an Oracle thing and not a SAN issue since the LIOs also jumped dramatically, but I could be wrong.

Since I can't describe this very well, I've put an annotated screenshot of
the Spotlight screen at http://society.servebeer.com/SANTA_19.png The image
is about 130K. Be kind to my li'l server, I've only got a 128Kb pipe up (768Kb down). :)

Any ideas?
Rich

Rich Jesse                           System/Database Administrator
[EMAIL PROTECTED]                  Quad/Tech Inc, Sussex, WI USA

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Jesse, Rich
INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: Freeman Robert - IL
INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------