« Writing a Design DocumentMySQL Error 1130 when connecting from MySQL Control Center »

What are new or updated features in Oracle 10g that are directly related to database performance?

10/03/04 | by Clarise | Categories: Databases

What are some enhancements in Oracle 10g that are directly related to database performance? Here is a summary of new or updated features in 10g based on
Oracle Database Performance Tuning Guide 10g Release 1 (10.1)

1. Automatic Performance Diagnostic and Tuning Features
10g has the following Diagnostic and Tuning tools:
- Automatic SQL Tuning Advisor
- Automatic Database Diagnostic Monitoring (ADDM)
- Automatic Optimizer Statistics Collection
- Automatic Workload Repository (AWR)

2. Oracle Trace Obsolescence
Oracle Trace as a functionality is no longer available. For the tracing of database activity, SQL Trace or TKPROF should be used instead.

3. Rule-based Optimization (RBO) Obsolescence
RBO is no longer supported in 10g. It is available as a non-supported feature to provide backwards compatibility. Oracle Metalink provides desupport notices and related articles.

4. Automatic Shared Memory Management
Automatic Shared Memory Management utilizes self-tuning algorithms to put Oracle in control of allocating memory within the SGA. Normally, SGA is controlled by DB_CACHE_SIZE (default block size), SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE. To utilize Automatic Shared Memory Management, the STATISTICS_LEVEL is set to TYPICAL or ALL and the SGA_TARGET is set to a value other than "0". The SGA_TARGET parameter sets the amount of memory available to the SGA.

5. Wait Model Improvements
The following views have been modified:
V$EVENT_NAME
V$SESSION
V$SESSION_WAIT
The following are new views:
V$ACTIVE_SESSION_HISTORY
V$SYSTEM_WAIT_HISTORY
V$SESS_TIME_MODEL
V$SYS_TIME_MODEL
V$SYSTEM_WAIT_CLASS
V$SESSION_WAIT_CLASS
V$EVENT_HISTOGRAM
V$FILE_HISTOGRAM
V$TEMP_HISTOGRAM

6. Optimizer Hints
The following are new Hints:
- "SPREAD_MIN_ANALYSIS": specifies analysis options for spreadsheets
- "USE_NL_WITH_INDEX": specifies a nested loops join.
- "QB_NAME": specifies a name for a query block.
- "NO_QUERY_TRANSFORMATION": causes the optimizer to skip all query transformations.
- "NO_USE_NL", "NO_USE_MERGE", "NO_USE_HASH", "NO_INDEX_FFS", "NO_INDEX_SS", and "NO_STAR_TRANSFORMATION": cause the optimizer to exclude various operations from the execution plan
- "INDEX_SS", "INDEX_SS_ASC", and "INDEX_SS_DESC": cause the optimizer to use index skip scan operations in the execution plan
The following have been deprecated:
- AND_EQUAL
- HASH_AJ
- MERGE_AJ
- NL_AJ
- HASH_SJ
- MERGE_SJ
- NL_SJ
- EXPAND_GSET_TO_UNION
- ORDERED_PREDICATES
- ROWID
- STAR

7. Hash Partitioned Global Indexes
Hash Partitioned Global Index can improve performance of indexes where a small number leaf blocks in the index have high contention in multiuser OLTP environment. With the support for hash partitioned global indexes, index entries are hashed to different partitions based on partitioning key and the number of partitions. This results in increased throughput because it spreads out contention over number of defined partitions.

8. Automatic Optimizer Statistics Collection
Automated statistics collection reduces the chances of getting poor execution plans because of missing or stale statistics. Automated statistics collection allow Oracle to gather the statistics on all database objects automatically and maintains those statistics in a regularly-scheduled maintenance job.

Send feedback »
 

No feedback yet

July 2019
Mon Tue Wed Thu Fri Sat Sun
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        
 << <   > >>
This blog contains thoughts that range from non-technical to technical. Its name is derived from "Yakity Blah Blah" a column I once had that discussed a cornucopia of ideas. Who am I? I'm Clarise Z. Doval Santos, providing Project Management and Technical Leadership for data management and analytic, data science, IoT and sensor analytics ecosystems 37.652951177164 -122.490877706959

Search

  XML Feeds

powered by b2evolution free blog software