| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> What can cause boost in LIO/PIO?
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
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-LReceived on Fri Jun 20 2003 - 10:48:05 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |
