RE: Need some 10053 Guidance to help me solve a puzzler
- From: <Christopher.Taylor2@xxxxxxxxxxxx>
- To: <Christopher.Taylor2@xxxxxxxxxxxx>, <jonathan@xxxxxxxxxxxxxxxxxx>, <niall.litchfield@xxxxxxxxx>
- Date: Thu, 11 Oct 2012 13:32:18 -0500
Further info on table size:
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Christopher.Taylor2@xxxxxxxxxxxx
Sent: Thursday, October 11, 2012 1:15 PM
To: jonathan@xxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
