Focus On Oracle

Installing, Backup & Recovery, Performance Tuning,
Troubleshooting, Upgrading, Patching

Oracle Engineered System


当前位置: 首页 » 技术文章 » Oracle 12c+

Oracle Database 19c在优化方面有哪些新特性

Nigel是Oracle Optimizer的产品经理。他从1988年开始做与Oracle技术相关的工作,1996年加入甲骨文公司。他参与了各种各样的工作,包括开发、基准测试、咨询和售前工作。下面的内容来自他在oracle blog的内容。原文地址:https://blogs.oracle.com/optimizer/whats-new-in-the-oracle-optimizer-for-oracle-database-19c


For those of you that want to keep up-to-speed with the latest changes, this post includes a quick summary of the headline new features in Oracle Database 19c with links to the documentation. In addition, most of the new optimizer white papers have been published. You will find links to them in the new Oracle Query Optimization page (and there will be further updates to this page soon).

Automatic Indexing

This is a complex and sophisticated piece of functionality but, ironically, it requires only a tiny API because it has been designed to be very easy to use and require virtually zero manual intervention. I'm sure that you can expect a lot to be written about this feature, but you can find material in the SQL Tuning Guide and the DBMS_AUTO_INDEX API.

Real-Time Statistics

Statistics can now be collected 'on-the-fly' during conventional DML operations. This feature requires no manual intervention, but you can find details in the SQL Tuning Guide's Optimizer Statistics Concepts chapter.

High-Frequency Statistics Gathering

Real-time statistics are a complement to conventional gathered statistics, so there is still a need for both. To help keep gathered statistics up-to-date, we added a new task to do it more frequently. It is again documented in the SQL Tuning Guide.

Automatic SQL Plan Management

There is a lot to say about this feature, so my intention is to post a blog in the next few of weeks. We have included more automation in the SQL evolve advisor task, so see the new features guide and the section on managing SQL plan baselines.

Documentation for the SQL plan management (SPM) API can be found in the DBMS_SPM API section. Note that the following SQL plan management task parameters have new default values in Oracle Database 19c: 

PARAMETER_NAME                     DEFAULT
--------------------------------   ----------
ALTERNATE_PLAN_BASELINE            AUTO
ALTERNATE_PLAN_LIMIT               UNLIMITED
ALTERNATE_PLAN_SOURCE              AUTO

If you currently use SQL plan management, especially if you use it to micro-manage individual SQL execution plans, then you need to be aware of the new default behavior when upgrading to Oracle Database 19c. You do have a choice: you can use the new defaults immediately or, if you prefer, you can set these parameters back to the pre-19c defaults very easily.

An additional piece of SQL plan management functionality was added late-in-the-day, so that will make its way through soon. I will post an update here when it's documented.

Hint Usage Reporting

Diagnosing SQL statement hint issues can be difficult. More information on how we have made is easier can be found in the documentation and in this blog post. Using this feature to diagnose issues with SQL plan baselines is presented in this blog post.

SQL Plan Comparison

We've made it much easier to compare a 'reference' SQL execution plan with multiple plans from a variety of different sources using a single API call. It's a great tool for diagnosis and you can find details in the section on comparing execution plans in the documentation. The API is DBMS_XPLAN.COMPARE_PLANS.

SQL Quarantine

This feature allows you to prevent runaway SQL statements from consuming all your system resource. It is covered in detail in the Database Administrator's Guide chapter on Diagnosing and Resolving Problems.

Automatic Diagnosis and Repair

The process of diagnosing and repairing problems has been enhanced to make it easier to treat issues with the DBMS_SQLDIAG API and the addition of the V$SQL_TESTCASES view.

There's more in in the Database Administrator's Guide.

Licensing

Not all of these features will be available on all database platforms. Consult the Database Licensing Information User Manual for details


Reference

https://blogs.oracle.com/optimizer/whats-new-in-the-oracle-optimizer-for-oracle-database-19c

https://blogs.oracle.com/author/nigel-bayliss




相关文章

基于PDB的Active Data Guard(Oracle 21.7+)
在Oracle数据库中使用REST
OGG from MySQL to Oracle
Oracle数据库容灾之两地三中心实践
低代码开发用Oracle Apex,看这篇就够了
Oracle Database 20c之SQL宏
Java beginner for Oracle DBA
Oracle Database 20c之区块链表
Oracle Database 20c的一些微妙变化
关于Oracle的Sequence,你需要知道的
Oracle数据库优化方面资料
Oracle Database 19c在优化方面有哪些新特性
Top