Problem solve Get help with specific problems with your technologies, process and projects.

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

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 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

Dig Deeper on Database software management

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.