Tip

Oracle Database11g SQL Performance Advisor

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

    Requires Free Membership to View

    Login

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest
Next
View All Comments

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

Back to top