Oracle Database11g SQL Performance Advisor

The Oracle Database 11g SQL Performance advisor is a handy tool for SQL tuning, which can be a complex and time-consuming process. Learn more about it in this excerpt from our sister site SearchOracle.com.

IT Reseller Takeaway: Manual SQL tuning is a complex and time-consuming process that requires a high level of expertise. Oracle Database11g offers the Oracle Tuning Pack, a solution that automates the entire application tuning process. Our sister site SearchOracle.com published this excerpt from the forthcoming book Oracle 11g New Features. Get familiar with this feature so you are armed with the tools you need to help your customers perform SQL tuning.

The declarative nature of SQL syntax has always made it difficult to perform SQL tuning. The basic tenet of cost-based SQL optimization is that the person who writes a SQL query simply "declares" what columns they want to see (the SELECT clause), the tables where the columns reside (the FROM clause), and the filtering conditions (the WHERE clause). It's always up to the SQL optimizer to determine the optimal execution plan. This is...

a formidable challenge, especially in a dynamic environment, which is why Oracle introduced the 10g new feature of CBO dynamic sampling.

More on Oracle
Oracle Database 11g tutorial

Oracle Database 11g comes through the channel

Oracle tuning consultants have know for many years that the best way to tune an Oracle system is to take a top-down approach, finding the optimal configuration for external factors (OS kernel settings, disk I/O subsystem) and determining the best overall setting for the Oracle instance (init.ora parameters).

Holistic tuning involves tuning a representative workload, adjusting global parameters in order to optimize as much SQL as possible. Only then is it prudent to start tuning individual SQL statements. Many Oracle professionals who adopt a bottom-up approach (tuning the SQL first) find all of their hard work undone when a change is made to a global setting, such as one of the SQL optimizer parameters or recomputing optimizer statistics. Oracle's holistic SQL tuning approach is new, and many Oracle professionals find it difficult to embrace, but this is about to change. The Oracle 11g SQL Performance Analyzer (SPA) is primarily designed to speed up the holistic SQL tuning process, automating much of the tedium.

Read the entire article at SearchOracle.com
This was first published in August 2007

Dig deeper on Database Management Products and Solutions

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

MicroscopeUK

SearchCloudProvider

SearchSecurity

SearchStorage

SearchNetworking

SearchCloudComputing

SearchConsumerization

SearchDataManagement

SearchBusinessAnalytics

Close