Database 11g features - performance related
What is the biggest database problem that most customer care about most. Many of them might pick the answers:
database performance!
Even with today 's uper power CPU and high capacity memory , even there 're so many tools we could use to investigate and diagnostic the performance problem. Few could have confidence that their database do not have performance problem.
According to experts from Oracle
"Most of today's database systems have been coded sub-optimally yet deliver performance that satisfies the customers requirements"
This is duede to the facts :
"buying a machine that is more than enough for an application database, so they 're never aware of the fact that they will not have to buy that large machine if they tune the database and thus save a lot of the unneccessary CPU power and memeory"
Do you have performance problem?
The first question is always the same: do you have performance problem?
With the long term goal of a self managing database, Oracle 11g is equiped with new tools to deal with this problem!
- Using the SYS_AUTO_SQL_TUNING_TASK maintenance job, 11g is able to list the expensive SQL which might cause performance problem from the AWR, which includes:
Top SQL of the past week
Top SQL of any single execution for the past week
Top SQL of any day for the past week
While with 10g you have to identified these sql mannully.
- I/O Calibration: It has been a long existing problems to determinde whether I/O performance problems are caused by the database or the storage subsystem. Unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature of Oracle Database issues I/Os randomly using Oracle datafiles to access the storage media, producing results that more closely match the actual performance of the database
- Enhanced ADDM for RAC: ADDM only generate instance-specific information for 10g, while 11g ADDM generate database-wide information for better RAC performance diagnostic.
How do we solve the performance problem?
- With these identified expensive SQL, 11g is NOW able to tune them automatically while with 10g we have to run SQL Tuning Advisor mannuly.
- LOB performance: with the introduction of the SecureFile LOB management, LOB performance is now comparable to OS filesystem performance, which make more viable to move media file into database rather than storing at the file system.
- XML performance enhancement: XML parsing has always been a CPU-intensive job. with the new concept of 'Binary XML', database now store the after-parse content XML and do not require the xml to be parsed next time we use it, this could save a lot of CPU for XML handling.
- Cube organized Materialized View: the previous materialized view is now rewrited to OLAP cube instead of summary table, this will reduce the need for building a lot of summary table for unpredictable query condiction.
- Query Result cache: 11g introduce the new concept of storing the SQL result in the memory and reuse it whenever possible, which is similiar to the previous materialized view thich could help to improve SQL query performance. the difference is the query reusult cache is in the memory!
Recommended books:
Author: Robert G. Freeman
Publisher: McGraw-Hill Osborne Media (2007)
Binding: Paperback, 362 pages
Author: Kevin Loney
Publisher: McGraw-Hill Osborne Media (2008)
Binding: Hardcover, 1368 pages