4 Replies Latest reply on Mar 5, 2010 5:28 PM by DanyC

    SQL Developer Optimization

      I have a few questions about SQL Developer that I was wondering if someone can help with:

      1. Does SQL Developer have the sql optimization feature that is present in Toad?

      Within TOAD, there is a facility that will help the user to optimize the sql and I was wondering if this was present in SQL Developer

      2. When using the compare facility for showing the difference between 2 tables, does SQL Developer show the data that is different?

        • 1. Re: SQL Developer Optimization
          No and no, but at least the first one is requested already at the SQL Developer Exchange. Go vote there to add weight for possible future implementation.

          • 2. Re: SQL Developer Optimization
            Barry Mcgillin-Oracle
            There are a couple of project in the works for this. Heres a quick exert of what we are planning. There is no confirmed release train for this at the minute though. This is provided for information only.

            SQL Code Advisor*

            By leveraging database features like Automated Workload Repository (AWR) and Active Session History (ASH), the potential exists to evaluate any SQL statement within a package or worksheet that has been executed and flag any statements exceeding some performance threshold. The developer will then immediately know if the SQL in question merits any tuning effort or should be left "as is".

            The goal of SQL Code Advisor is to provide real-time feedback to developer within an editor or worksheet on factors which may impact performance. Without going into great detail in this overview, here are some:

            * Connected to database instance with missing system statistics
            * SQL references tables and indexes with missing or stale statistics, or indexes in an invalid state
            * Population and cardinality estimates of referenced tables
            * Type, compression status, cache status, degree of parallelism for referenced tables
            * Explain plan indicates Full Table Scan performed on a large table
            * Explicit datatype conversions of columns in predicates, preventing use of available indexes

            SQL Tuning Advisor*

            By leveraging existing database APIs in the DBMS_SQLTUNE and DBMS_ADVISOR packages, with appropriate UI enhancements, this SQL Tuning Advisor extension will allow a developer to generate a report to warn when SQL performance may be impaired by:

            * stale optimizer statistics
            * missing indexes
            * improper coding practices.

            These APIs are able to perform more in-depth analyses of SQL statements than the optimizer. As a consequence, in addition to offering advice on specific environment and coding issues, it can produce a SQL Profile. The Profile contains additional statistics which help the optimizer find a more efficient execution plan. The original execution plan can be presented side-by-side with the enhanced SQL Profile-assisted execution plan for comparison. The developer has control over which, if any, of the recommendations to accept and deploy.
            • 3. Re: SQL Developer Optimization
              Sounds great!

              I would assume this would still be free as long as the customer is licensed for the tuning pack and or diagnostic pack?
              • 4. Re: SQL Developer Optimization
                Wow ..that's a good news, especially on Friday afternoon.

                Thanks Barry.