| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: INSERT...SELECT pegs CPU, but is waiting on scattered read?
Cary,
If I have DEFAULT, KEEP, and RECYCLE pools, does Oracle searches within its pool or scan the complete buffer cache(all 3 pools)?
Muqthar
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Cary Millsap
Sent: Thursday, April 29, 2004 10:09 PM
To: oracle-l_at_freelists.org
Subject: RE: INSERT...SELECT pegs CPU, but is waiting on scattered read?
Rich,
The Oracle kernel is not going to emit a trace line until either (a) it completes a "timed event" (like an OS read() call), or (b) it completes a db call (like an EXEC).
If you wait long enough, you'll see a line of trace data that will look something like this:
EXEC #n:c=huge,e=huge,p=small,cr=huge,cu=who-knows,...
The cr figure will be the number of CONSISTENT_GETS done by the call, and the cr figure will be the number of BLOCK_GETS done by the call. Your performance problem is that this EXEC is taking a long time, because it's doing so much work. It's not really "I/O" because it's not accessing any peripheral device. Your problem is strictly the number of in-memory accesses upon the Oracle database buffer cache for the data the kernel is using to satisfy your SELECT.
This is where most Oracle systems I see (and hear about) spend most of their time, and it's why I'm so disappointed whenever authors tell you that the best way to "tune your system" is to make your database buffer cache bigger. It takes typically 20+ microseconds (0.000020 seconds) for Oracle to execute a single access upon its buffer cache. This means that a million buffer fetches will take 20+ CPU seconds to execute. A billion will eat over 5-1/2 hours of CPU time.
The solution to a problem like yours is to make your SQL answer the required business question with the shortest code path possible.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 5/7 Dallas, 5/18 New Jersey, 6/22
Pittsburgh
- SQL Optimization 101: 5/3 Boston, 5/24 San Diego, 6/14 Chicago - Hotsos Symposium 2005: March 6-10 Dallas - Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jesse, Rich
Sent: Thursday, April 29, 2004 3:24 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: INSERT...SELECT pegs CPU, but is waiting on scattered read?
CONSISTENT_GETS shows 106M and growing very rapidly. BLOCK_GETS at 87K
and
growing slowly.
Reduce I/O = better performance, I'm guessing?
I think I'm misunderstanding the relationship between these counters and
the
10046 trace, but wouldn't the increases in these stats produce trace
output?
It's still stuck at that PARSE #33 line. The EXEC hasn't even shown up
yet,
but perhaps it just hasn't flushed to the trace file from the server
process
yet?
I really am in the middle of reading your book. Honest! :) Bookmarks
all
over the place...
Rich
Rich Jesse System/Database Administrator rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
> -----Original Message-----
> From: Cary Millsap [mailto:cary.millsap_at_hotsos.com]
> Sent: Thursday, April 29, 2004 2:09 PM
> To: oracle-l_at_freelists.org
> Subject: RE: INSERT...SELECT pegs CPU, but is waiting on
> scattered read?
>
>
> If you can still connect to Oracle, you'll probably see a tremendous
> amount of activity reflected in V$SESS_IO.BLOCK_GETS and
> ~.CONSISTENT_GETS. Cut the SELECT statement into a SQL*Plus
> session and
> go to work on "tuning the SQL." This (a SQL tuning issue) is almost
> undoubtedly the cause of your problem.
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- * * * * * * * * * The information contained in this E-mail message is privileged, confidential, and may be protected from disclosure; please be aware that any other use, printing,copying, disclosure or dissemination of this communication may be subject to legal restriction or sanction. If you think that you have received this E-mail message in error, please reply to the sender and delete it from your computer. Thank you. ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Apr 30 2004 - 08:46:47 CDT
 |
 |