RE: Need some 10053 Guidance to help me solve a puzzler

Query Result Set = 197,188 rows

Table Size:
18,272,128 Rows
5958.75MB (~6GB)
23835 Extents

Returning 10.791% of the table.

Chris

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Christopher.Taylor2_at_parallon.net Sent: Thursday, October 11, 2012 1:15 PM To: jonathan_at_jlcomp.demon.co.uk; oracle-l_at_freelists.org Subject: RE: Need some 10053 Guidance to help me solve a puzzler

1.)
Quote:
-- Okay, bit of a coincidence on the costing, then, but

• what's the block size
• what do the system stats look like

db_block_size=8192

system stats:

SYSSTATS_MAIN	CPUSPEEDNW	1878.73888439774
SYSSTATS_MAIN	IOSEEKTIM	10
SYSSTATS_MAIN	IOTFRSPEED	4096
SYSSTATS_MAIN	SREADTIM	1.562
SYSSTATS_MAIN	MREADTIM	26.342
SYSSTATS_MAIN	CPUSPEED	3003
SYSSTATS_MAIN	MBRC		871
SYSSTATS_MAIN	MAXTHR	287382528
SYSSTATS_MAIN	SLAVETHR	29696

(see anything here that bears on this issue?)

2.)
Quote:
--should have been more like "the bit of the table needed by the indexed
access path is nearly completely cached"

Okay that would make sense.

3.)
Quote:
-- Oracle thinks that the data you want is all over the place and will
require a very large number of random reads
-- it's possible that the data you want is actually all packed into a
fairly small part of the table

That also makes sense.

4.)
Quote:
-- How big is the table really, how big is the cache. My comment was that
the table was largely UNcached.

Table Size:
5958.75MB (~6GB) 23835 Extents

Cache:
Buffer Cache: 24,832 MB (~24GB)

5.)
Quote
-- There's a very good book about this called "Cost Based Oracle -
Fundamentals" (I wrote it, so I am biased, though).

I have your book :-p (Reading about something is a lot different than getting to get your hands onto it and really understanding it!)

Chris

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Oct 11 2012 - 20:32:18 CEST