RE: INSERT...SELECT pegs CPU, but is waiting on scattered read?

  • From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 30 Apr 2004 09:01:27 -0500

I believe the Oracle kernel will have enough information to determine
which pool to look in, and then of course it can use a hash table to
search within that pool.


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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Muqthar Ahmed
Sent: Friday, April 30, 2004 8:49 AM
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: 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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Cary Millsap
Sent: Thursday, April 29, 2004 10:09 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Jesse, Rich
Sent: Thursday, April 29, 2004 3:24 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA


> -----Original Message-----
> From: Cary Millsap [mailto:cary.millsap@xxxxxxxxxx]
> Sent: Thursday, April 29, 2004 2:09 PM
> To: oracle-l@xxxxxxxxxxxxx
> 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.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 04-2004