What was this 5am job? It wasn't an analyze was it?
RF
-----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
---------------------------------------------------------------------
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).
