ORACLE神话破灭

shiguibao

有些地方看得怪怪的说

cc59

呵呵。鬼子还不是很多oracle的东西搞不

juniper

最初由 cyr1974 发布
[B]为什么是神话呢？我认为oracle的很多技术我们根本就是一知半解 鬼子也没搞清楚 [/B]

鬼子在技术上还是比我们要更有优势一些,最起码语言上要比我们有优势

Yong Huang

juniper,

Please indicate the author and source of the article, unless you wrote it. Thank you.

Yong Huang

juniper

作者是Mike Ault

juniper

Oracle Myths Revisited


Mike Ault

juniper

Oracle Myths Revisited
SearchOracle

Mike Ault

juniper

The term Oracle “myth” refers to a principle about Oracle behavior which either was never true, or, used to be true, but is no longer. Most Oracle myths originated as the result of changing technology.

    Most people agree that many of today’s Oracle Myths were perfectly valid during their day (e.g. “disk load balancing is critical to performance”), but they became mythological as hardware and Oracle software improved.  

Let’s not forget that Oracle technology is more than 15 years-old, and the technology of 1989 is far different than it is today.

Fortunately, most Oracle professionals fully-understand the changing dynamics of Oracle Mythology and how once-valid advice can become invalid and take-on mythological status.

juniper

Ancient Oracle Myths



  

There are many old Oracle techniques which were very useful in the past but have become myths as the technology has changed.



Confounding the problem are the thousands of Oracle shops that are running on prehistoric hardware and unsupported releases of Oracle software.






Let’s take a look at some of the older myths.


Ancient Myth: Objects perform better in a single extent


Oracle University courses in the early 1990’s stressed that the compress=y export option would greatly improve the performance of resulting tables.  Today, Locally-Managed Tablespaces (LMT) makes this advice invalid.


Ancient Myth: A data buffer hit ratio (DBHR) should be kept greater than 90%


This myth was also propagated by Oracle Corporation in the early 1990’s when almost all Oracle databases were I/O-bound and SGA size was constrained by the 32-bit server technology.  Oracle-based products such as SAP also note in their manuals that the DBHR should be over 90%.  Oracle author Robert Freeman notes:



It has been demonstrated on many occasions that it is easy in a basic proof to prove just about anything. Given a basic proof I can prove that the buffer cache hit ratio means nothing, or I can prove that it is the most important thing in the world.



I know of several scripts which can be run against your database to give you any DBHR you desire. Does this make a myth?  Oracle does not seem to think so, and ratio-based advisories form the foundation of the Oracle10g Automatic Memory Management utility and the v$db_cache_advice advisory.



But there are some current Oracle myths, let’s take a look at them.

juniper

Current Oracle Myths


The current Oracle myth debates are largely a result of the changing Oracle technology and the inability of some Oracle professionals to adapt to the changes.



Current Myth: Indexes and tables do not need to be separated


This myth arose because of recommendations by Oracle back in the early 1990’s when disk contention was a major issue and the "separation" myth is misunderstood.  




It wasn't too long ago that separation of indexes and tables in databases was a good and accepted method for improving performance.



Of course this was because otherwise they would be on the same disk platter if they weren't separate and would conflict.  Moving indexes to a tablespace on a separate disk from tables always improved performance, not just the separation into a separate tablespace.




The main argument, supported by 10046 traces with a single-user system is that access to tables and indexes in a single query is not asynchronous in nature, but is rather a linear process. However, even in single user systems this fails to take into consideration the required head movement and disk latencies associated with reading index, then table. IN a multi-user environment it fails to take into consideration all of the above plus the effects of multi-user access against co-located tables and indexes.



Now with properly laid-out RAID much of the contention issues of co-location are removed or mitigated, however, maintenance is still made easier with separation into several tablespaces for tables and indexes. Separation into discrete tablespaces allows tracking of IO rates and volumes for specific objects or types of objects and also allows for use of multiple-block sizes.



Current Myth: High-update tables and indexes rarely need reorganization


This myth was started by the statements of Oracle experts that claimed that Oracle indexes always remain balanced and that indexes rarely benefit from rebuilding. Below we see the suggestion that, somehow, understanding “why” table and indexes become fragmented might help:



unless you want to be caught in the infinite loop of org, reorg, org, reorg.... You better have a clue as to "why"



While in a perfect world you could rebuild once using the absolute correct parameters and never have to rebuild again, I am afraid this doesn’t happen in the real world. It’s rather like expecting to clean your house once when it is full of rowdy teenagers, it just doesn’t make sense.



Today, it is well-understood that tables and indexes with high concurrent insert, update and delete activity system can quickly get a sub-optimal structure and require reorganization to reduce I/O for multi-block scan operations (using Oracle’s dbms_redefinition package, alter index move/rebuild, alter index coalesce, or even alter table move depending on availability requirements.) The concept of index balance is two-pronged, while a B-Tree is always height balanced, it can become sparse or right-handed, so it becomes width or load unbalanced.  


Current Myth: Multiple blocksizes don’t improve performance


This myth was perpetuated because multiple blocksizes were originally designed to support transportable tablespaces and some people could not see the other important side-benefits of multiple blocksizes. The chief benefit of different blocksizes is the more efficient use of limited RAM regions (db_cache_size, db_32k_cache_size, etc.) and the intelligent segregation of objects to reduce logical I/O (consistent gets) for multi-block scan reads.  



Today, Metalink notes that the multiple blocksize parameters are among the most important in Oracle tuning, and noted experts such as Robin Schumacher has demonstrated that Oracle indexes will build more-optimal b-tree structures within a large blocksize. Also, Reorganizing a high-DML index, or using small blocksizes for random single-row fetches (index access unique) of small rows can reduce the size in db_cache_size and therefore reduce PIO because more blocks fit into the cache area.



For example, some attempt to prove the assertion using small, artificial single-user experiments and suggest that multiple blocksizes are unlikely to help in a real-world database.  However, real-world shops report a very different experience with multiple blocksizes and a 32k blocksize for indexes:



"My favorite recent article was on 32KB indexes - our client (200GB+) saw a 20% reduction in I/O from this simple change...  “- Steve Taylor, Technical Services Manager EMEA



So, here we see how changing technology can convert a perfectly-valid approach from 15 years ago into a “Myth”, and reaching a false conclusion from a single-user test-script can create a modern myth, again because of changing technology.