RE: Need some 10053 Guidance to help me solve a puzzler
Date: Thu, 11 Oct 2012 14:37:39 -0500
Message-ID: <F05D8DF1FB25F44085DB74CB916678E8851607C435_at_NADCWPMSGCMS10.hca.corpad.net>
Hmmmm - so what *is* "VERY HIGH VALUE"? LOL
My value is:
SYSTEM STATISTICS INFORMATION
Using NOWORKLOAD Stats
CPUSPEED: 1879 millions instruction/sec IOTFRSPEED: 4096 bytes per millisecond (default is 4096) IOSEEKTIM: 10 milliseconds (default is 10)
BUG:6714158 - VERY HIGH VALUE FOR IOTFRSPEED STATISTIC AFTER GATHERING SYSTEM STATS Applies to:
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 This problem can occur on any platform.
Abstract: VERY HIGH VALUE FOR IOTFRSPEED STATISTIC AFTER GATHERING SYSTEM STATS
10053 trace shows that IOTFRSPEED is of very high value that seems to be unrealistic. This high value deceives the CBO and let it thinks that doing more I/Os (especially in FTS) could be of less cost than actual case. Solution
At the time of publishing this note, the bug hasn't been fixed yet. For the time being, please keep working without the system statistics until bug gets fixed. You need to do the following:
SQL> conn / as sysdba
SQL> exec DBMS_STATS.DELETE_SYSTEM_STATS;
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Thursday, October 11, 2012 1:02 PM
To: oracle-l_at_freelists.org
Subject: Re: Need some 10053 Guidance to help me solve a puzzler
Notes in-line
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: <Christopher.Taylor2_at_parallon.net> To: <jonathan_at_jlcomp.demon.co.uk>; <oracle-l_at_freelists.org> Sent: Thursday, October 11, 2012 6:36 PM Subject: RE: Need some 10053 Guidance to help me solve a puzzler
Ok, so now I'm confused a bit and I think I'm probably several mental steps behind you on this one so bear with me:
First:
db_file_multiblock_read_count=16
Systems Stats have been gathered (always one of the first things I do when
taking over a system)
- Okay, bit of a coincidence on the costing, then, but
- what's the block size
- what do the system stats look like
Second:
I ran the non-indexes query multiple times - if the table was nearly
completely cached, wouldn't the access times improve on the non-indexed
query as well? (There's probably some other considerations here that I'm
missing/not thinking about)
- How big is the table really, how big is the cache. My comment was that the table was largely UNcached.
- Bad wording on my part about the index access though,
- "the table is nearly completely cached for the indexed access"
- should have been more like "the bit of the table needed by the indexed acces path is neraly completely cached"
- 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
Third:
Why doesn't Oracle choose the Index and Drive the NL to get the 159K rows
on its own?
- because it thinks the volume of data required is scattered over a very large number of blocks and will require a very large number of random reads to acquire and that the time it will take to scan the entire table is much less than the time it will take to perform that number of single block reads.
- There's a very good book about this called "Cost Based Oracle - Fundamentals" (I wrote it, so I am biased, though).
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 11 2012 - 21:37:39 CEST