2 Replies Latest reply on Feb 12, 2015 9:14 PM by rp0428

    Index Advice from SQL Developer

    2883914

      I've been developer and DBA on IBM DB2 for the past two decades, and I'm just now jumping in to developing on Oracle. So, I apologize in advance if this is too-easy, "beginner stuff".

       

      When I wanted to optimize a query in DB2, I could use their "Visual Explain" tool which gives loads of details on how the optimizer parses and runs a query. It outputted a graphical tree-structure showing the details of how each table was going to be parsed, including which index it would choose, why it chose it, its index key-list, estimated processing time, and numerous other pieces of information about the query. In fact, if the optimizer determined that it could have used an index that doesn't actually exist, it will report that back as "index advice". From there, I can even right-click on the icon to manually create the suggested index.

       

      In Oracle SQL Developer, I've been able to successfully use "explain plan for <my SELECT statement>" and "select * from table(dbms_xplan.display);" to see a somewhat descriptive set of text-based information on the optimization plan of a query, but I don't see any sort of "Index Advice" like I did in the DB2 tool.

       

      Again, I apologize for my ignorance, but does this kind of feature exist in Oracle. I'm using Oracle 11g, and my SQL Developer version is "Version 1.5.5 Build MAIN-5969".

       

      Thanks in advance!

      Dave

        • 1. Re: Index Advice from SQL Developer
          thatJeffSmith-Oracle

          First things first, upgrade!

           

          Version 1.5 is approaching 10 years old.

           

          Go get v4.0.3.

           

          We do offer indexing advice, but it requires the Tuning Pack, which requires the Diagnostic Pack, which requires an Enterprise Edition database.

           

          If you have those things, you can launch the SQL Tuning advisor in the worksheet for your query. It can come back with indexing suggestions.

          • 2. Re: Index Advice from SQL Developer

             

            I've been developer and DBA on IBM DB2 for the past two decades, and I'm just now jumping in to developing on Oracle.

            One MAJOR difference between the two DBs is that in Oracle WRITERS do NOT block READERS. Oracle uses a totally different functionality to provide read consistency. Make sure you get up to speed on that concept by reading this entire chapter

            http://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm

            When I wanted to optimize a query in DB2, I could use their "Visual Explain" tool which gives loads of details on how the optimizer parses and runs a query. It outputted a graphical tree-structure showing the details of how each table was going to be parsed, including which index it would choose, why it chose it, its index key-list, estimated processing time, and numerous other pieces of information about the query. In fact, if the optimizer determined that it could have used an index that doesn't actually exist, it will report that back as "index advice". From there, I can even right-click on the icon to manually create the suggested index.

            There is not ONE piece of functionality in Oracle that will provide that information but rather several different ways to go about it.

             

            1. Statistics need to be up to date or any info you get will likely be inaccurate at best

            2. An 'explain plan' is NOT the same as an execution plan

            3. Detailed info about how the CBO (cost based optimizer) went about deciding on the best execution plan is only available if you create a 10053 trace file.

            4. The SQL Tuning Advisor, AWR reports and other such are each separate functional pieces. Enterprise Manager is the easiest interface to use to have access to virtually ALL of the functionality that needs to be 'managed' - get familiar with it.

             

            Stats - Oracle keeps some stat info available in the data dictionary and updates it ONLY when new statistics are collected. Keep the stats up-to-date BEFORE generating execution plans.

             

            Explain plan - Oracle's 'best guess' as to how it THINKS it will execute a given query

             

            Execution plan - Oracle's actual chosen method of execution

             

            Trace files - Oracle is HEAVILY instrumented and can generate a tremendous amount of detailed debugging info. Mostly that is never needed. Much will only be used when Oracle support instructs you to use it. A few others are used commonly: 10046 traces and 10053 traces.

             

            A 10053 trace is what wouldl contain detailed info about the different execution plans the CBO considered and/or rejected.

            10053 Trace Files - Getting Started - Doug's Oracle Blog

            Essentially, setting event 10053 causes the Cost Based Optimizer to write information to a trace file describing the information it is using and the results of it's calculations whilst walking through the decision-making process to determine the best execution plan. It includes the options that it has considered and discarded, those that it has accepted and options which are unavailable for various reasons.

            You can trace individual SQL statements using a 10046 trace. AWR reports (get familiar with them) can provide more global info about what is going on in the DB for a given period of time: memory usage, cpu usage, physical IO, logical IO, etc.

            In Oracle SQL Developer, I've been able to successfully use "explain plan for <my SELECT statement>" and "select * from table(dbms_xplan.display);" to see a somewhat descriptive set of text-based information on the optimization plan of a query, but I don't see any sort of "Index Advice" like I did in the DB2 tool.

            Correct - and an 'explain plan' is NOT an execution plan.

             

            Info about indexes and recommendations would be the purview of the Sql Tuning Advisor.

             

            By the way - with Oracle ALL versions, including the Enterprise version, are available for free download for learning and single developer use. That is the easiest way to get access to ALL of the advanced functionality to learn how to use it. All of the docs are available free for download also.


            If and when you have specific questions about Oracle database or SQL there are forums specifically for those topics:

            SQL & PL/SQL

            General Database Discussions