| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: SQL tuning nightmare - db file sequential reads
On Wed, 05 Jul 2006 07:26:08 -0700, Jining Han wrote:
> What is frustrating is that the indexes were created to help
> queries/reports to run fast, and in most cases these indexes did just
> that. But then all of a sudden for certain, albeit a small number of
> queries, they are the bottleneck.
Jining, the problem is in the use of indexes. Indexes are relatively
complex mathematical structures which will, for any given key, provide
the needed rowid with
1) Constant speed which doesn't change with the placement of the keys
in the table
2) Much greater speed then sequential block by block read of the table.
It's an old lore that indexes should be used when relatively few rows are retrieved from the table and should not be used if a significant portion of the table is retrieved. In the era of RBO, in further text referred to as "Jurassic period", those percentages were determined by the "rules of thumb" or frequently pulled out of one's ass...umptions. CBO had no rules, CBO calculated the cost of both index scan and full table scan and expressed it as the number of blocks that should be read for either approach. The cheapest approach is then selected for execution. RBO, on the other hand, had a ranked list of the access methods and chose the highest ranked method available. Index access was ranked higher then the full table scan, so if index existed, it was selected for use. That approach works very well for certain types of the applications, namely OLTP applications. In order to minimize the response time, very few records were selected and shown directly on the user's screen. When one is optimizing an OLTP application, you want it to use indexes by default.
Unfortunately, in the environments with mixed application types, which is the vast majority of the environments, this type of optimization doesn't work nearly as well, so you don't want RBO-type of optimization. You want the intelligence of the CBO which is much better at discerning the proper approach.
To effectively configure CBO to emulate RBO, one first needs to understand one thing: indexes and tables are not read the same way. Index is read using single block reads, shown in the trace files as "db file sequential reads", while tables are read using multiblock reads, shown in the trace files as "db file scattered read". What OPTIMIZER_INDEX_COST_ADJ parameter does is to express the cost of a single block read as a percentage of the cost of a multi-block read. If you make that percentage small, then single block read will have significantly lower cost and CBO will chose index more frequently then it would have done otherwise. That, effectively, emulates "if there is an index, use it" paradigm of the RBO. In order to benefit from such emulation, one must have pure and undiluted OLTP application. Second thing to remember is something called "system statistics", stored in the AUX_STATS$ table in the data dictionary. This is not data dictionary statistics, this is the information about the nooks and crannies of your system. To collect this statistics, Oracle runs a little benchmark test and determines, among other things, how fast your CPU is and how many MIPS does it have. MIPS, as the world knows, stands for "Marketing Invention for Pushing Sales" and is one of the most popular performance measures ever. Two of the values to pay special attention to are called SREADTIM and MREADTIM, as is visible from the query below:
SQL> select * from sys.aux_stats$;
SNAME PNAME PVAL1 ------------------------------ ------------------------------ ---------- PVAL2 --------------------------------------------------------------------------------SYSSTATS_INFO STATUSCOMPLETED
SYSSTATS_INFO DSTART
SYSSTATS_INFO DSTOP
SYSSTATS_INFO FLAGS 1 SYSSTATS_MAIN CPUSPEEDNW 846.630237 SYSSTATS_MAIN IOSEEKTIM 10 SYSSTATS_MAIN IOTFRSPEED 4096 SYSSTATS_MAIN SREADTIM 4.132 SYSSTATS_MAIN MREADTIM 6.161 SYSSTATS_MAIN CPUSPEED 739 SYSSTATS_MAIN MBRC 8 SYSSTATS_MAIN MAXTHR 36977664 SYSSTATS_MAIN SLAVETHR 74752
13 rows selected.
SREADTIM is the time needed to complete a single block read and MREADTIME is, you're guessing, the time needed to complete a multiblock read. So, that sets the price of a single block read as some percentage of the multiblock read. As a direct consequence, collecting system statistics will result in optimizer being more likely to select indexes. Just for the record, MBRC is the default number of blocks that CBO thinks that the system will attempt reading in a single request when doing a full table scan. Notice the formulation "CBO thinks". The real number is, of course, determined by the DB_FILE_MULTIBLOCK_READ_COUNT parameter which now can be increased, without lowering the cost of full table scans and changing the execution plans.
The difference between system statistics and OPTIMIZER_INDEX* parameters is that the first one is global, affecting everything, and the latter can be changed on the session level, thus creating your very own RBO, for your very own session. CBO is very fine and well undocumented instrument that needs to be thoroughly understood before playing with it. Unfortunately, because of being so well undocumented, there are very few places where one can acquire the necessary knowledge. I would definitely recommend Centrex Consulting page belonging to the Wolfgang Breitling, Jonathan's book , some articles by Howard Rogers and, for those not proficient in English, Trivadis (http://www.trivadis.com). The page is written in the language which doesn't have a word for "fluffy", as captain Blackadder once said.
CBO is a rapidly developing and fast moving target. It has its advantages and its shortcomings. It's very interesting, the documentation is scant and non-existing, yet CBO is our only option in 10g and onwards. Somebody had a cunning plan and eliminated the plan stability, so plans can now change depending on the statistic information about the Oracle objects and depending on the patches to the optimizer if you happen to be installing the next patch release. The whole thing started to require complex strategies for statistics collection and extensive and costly testing to be performed before each patchset upgrade.
-- http://www.mgogala.comReceived on Wed Jul 05 2006 - 13:41:29 CDT
 |
 |