AIOUG - All India Oracle User Group
Email ID :  Password :  
Register Renew Forgot Password ?
 
AIOUG Sangam header image 3

 

 

"Go Hands-On with SQL Developer"

 

Saturday,November 3rd 2012

 

Exploring the Tuning Techniques Using Oracle SQL Developer 3.1

Abstract

SQL Tuning is the process of assuring that the SQL queries issued in a real-time application executes in the fastest possible time. Tuning can be done by the application developer as well as the database administrator. It starts from the design phase, and needs to be continuously worked on after the deployment.

 

Oracle SQL Developer 3.1 supports tuning by its rich tuning utilities. This presentation looks at the SQL processing phases, the tuning methods, and the SQL Tuning tools available in Oracle SQL Developer 3.1. Key concepts covered include optimizer access paths and SQL plan baseline. The tools such as Explain Plan, Autotrace, Tuning Advisor and SQL Real-Time Monitoring are detailed with practice sessions. This presentation also involves identifying and tuning the poorly written SQL statement, and ordering keys for composite index.

       
Hands-on Session
    SQL Tuning Advisor
      Creating a Database Connection
      Providing Privileges and removing existing statistics on a user
      Running the SQL Tuning Advisor on a SQL Developer
      Implementing the SQL Tuning Advisor on a statement
    Optimizer Access Path (Using it for 8 different cases)
      Compare Single Column Index Access path
      With and Without Index
      Bitmap Index Access
      Function based index etc...
    Working with Tuning Utilities in SQL Developer
      Use SQL Developer to generate and display the Explain plan of a SQL query and use Auto trace option
      Examine the effects of changing column order in a composite index
      Use SQL Plan Baseline to associate a hinted execution plan with a hard coded SQL statement.
      SQL Real Time Monitor feature
     
Note: For the hands on session, attendees are required to bring a laptop with Linux, Windows or Mac with a minimum 2GBs of RAM.
     
Key takeaways from this session
  Choosing an appropriate SQL tuning approach
    Identifying poorly performing SQL Statements
    Using basic tuning techniques to tune inefficient SQLs
    Interpreting execution plans
    Describing the Oracle Optimizer Process
    Understanding Optimizer access paths
     

     

 

Sharon Sophia Stephen is a Curriculum Developer with the Oracle Server Technologies Curriculum group and has over 5+ years of development and teaching experience. Prior to working with Oracle, she was a Database Developer with Keane India Ltd., Bangalore, and Lecturer in Rajalakshmi Engineering College, Chennai. She is an Oracle Certified Associate. She is also certified in various IBM technologies, and trained many students for certifications. She has been involved in the Oracle Big Data Curriculum, and has authored materials on Oracle Big Data and Oracle NoSQL Database Administration. Her areas of interest in database include PL/SQL programming, Database Tuning and Big Data Research.

     

 

Dimpi Sarmah is a Curriculum Developer with the Oracle Server Technologies Curriculum group. She is an Oracle Certified Associate. She has been involved in the Oracle Database Curriculum activities. Her areas of interest are APEX, SQL and SQL Tuning.

     

     

Diamond

Platinum

Gold

Silver

Bronze

 

 

 

 

 

 

Supporting Sponsors

 

Have any questions? Please write to us sangam12@aioug.org