7 Replies Latest reply: Oct 24, 2013 6:19 AM by Sven W. RSS

    How to resolve most of the Oracle SQL , PL/SQL Performance issues with help of quick Checklist/guidelines ?

    Praful_Solanki

      Please go thru below important checklist/guidelines to identify issue in any Perforamnce issue and resolution in no time.

       

       

      Checklist for Quick Performance  problem Resolution

       

      ·         get trace, code and other information for given PE case

       

                - Latest Code from Production env

                - Trace (sql queries, statistics, row source operations with row count, explain plan, all wait events)

                - Program parameters & their frequently used values

                - Run Frequency of the program

                - existing Run-time/response time in Production

                - Business Purpose

               

      ·         Identify most time consuming SQL taking more than 60 % of program time using Trace & Code analysis

      ·         Check all mandatory parameters/bind variables are directly mapped to index columns of large transaction tables without any functions

      ·         Identify most time consuming operation(s) using Row Source Operation section

      ·         Study program parameter input directly mapped to SQL

      ·         Identify all Input bind parameters being used to SQL

      ·         Is SQL query returning large records for given inputs

      ·         what are the large tables and their respective columns being used to mapped with input parameters

      ·         which operation is scanning highest number of records in Row Source operation/Explain Plan

      ·         Is Oracle Cost Based Optimizer using right Driving table for given SQL ?

      ·         Check the time consuming index on large table and measure Index Selectivity

      ·         Study Where clause for input parameters mapped to tables and their columns to find the correct/optimal usage of index

      ·         Is correct index being used for all large tables?

      ·         Is there any Full Table Scan on Large tables ?

      ·         Is there any unwanted Table being used in SQL ?

      ·         Evaluate Join condition on Large tables and their columns

      ·         Is FTS on large table b'cos of usage of non index columns

      ·         Is there any implicit or explicit conversion causing index not getting used ?

      ·         Statistics of all large tables are upto date ?

      ·        

      Quick Resolution tips

       

       

      1) Use Bulk Processing feature BULK COLLECT with LIMIT and FOR ALL for DML instead of row by row processing

      2) Use Data Caching Technique/Options to cache static data

      3) Use Pipe Line Table Functions whenever possible

      4) Use Global Temporary Table, Materialized view to process complex records

      5) Try avoiding multiple network trips for every row between two database using dblink, Use Global temporary table or set operator to reduce network trip

      6) Use EXTERNAL Table to build interface rather then creating custom table and program to Load and validate the data

      7) Understand Oracle's Cost based Optimizer and Tune most expensive SQL queries with help of Explain plan

      8) Follow Oracle PL/SQL Best Practices

      9) Review tables and their indexes being used in the SQL queries and avoid unnecessary Table scanning

      10) Avoid costly Full Table Scan on Big Transaction tables with Huge data volume,

      11) Use appropriate filtration condition on index columns of seeded Oracle tables directly mapped to program parameters

      12) Review Join condition on existing query explain plan

      13) Use Oracle hint to guide Oracle Cost based optimizer to choose best plan for your custom queries

      14) Avoid applying SQL functions on index columns

      15) Use appropriate hint to guide Oracle CBO to choose best plan to reduce response time

       

       

      Thanks

      Praful

        • 1. Re: How to resolve most of the Oracle SQL , PL/SQL Performance issues with help of quick Checklist/guidelines ?
          Hoek

          Ah, the Magical Silver Bullits! Finally, now I know what to do...

          Too bad that lots of your points are plain wrong (for example: one should AVOID using hints, one should let Optimizer decide) or outdated, from the previous century or missing (not a word regarding Function Based Indexes?) and you're not even pointing to the Online Oracle Documentation or the Performance Tuning Guide.

          Oracle Database Online Documentation 11g Release 2 (11.2)

          Performance Tuning Guide Contents

          and this article sums it all up better:

          ORACLE-BASE - PL/SQL: Stop Making the Same Performance Mistakes

          • 2. Re: How to resolve most of the Oracle SQL , PL/SQL Performance issues with help of quick Checklist/guidelines ?
            Paul  Horth

            And the question is?

             

            BTW: a lot of what you posted is garbage or misleading, but I still don't know why you posted it.

            • 3. Re: How to resolve most of the Oracle SQL , PL/SQL Performance issues with help of quick Checklist/guidelines ?
              Praful_Solanki

              Thanks Hoek for sharing useful URL on the same. Appreciated.

              • 4. Re: How to resolve most of the Oracle SQL , PL/SQL Performance issues with help of quick Checklist/guidelines ?
                BluShadow

                I understand you were trying to post something helpful to people, but sorry, this list is appalling.

                 

                1) Use Bulk Processing feature BULK COLLECT with LIMIT and FOR ALL for DML instead of row by row processing

                No, use pure SQL.

                 

                 

                2) Use Data Caching Technique/Options to cache static data

                No, use pure SQL, and the database and operating system will handle caching.

                 

                 

                3) Use Pipe Line Table Functions whenever possible

                No, use pure SQL

                 

                 

                4) Use Global Temporary Table, Materialized view to process complex records

                No, use pure SQL

                 

                 

                5) Try avoiding multiple network trips for every row between two database using dblink, Use Global temporary table or set operator to reduce network trip

                No, use pure SQL

                 

                 

                6) Use EXTERNAL Table to build interface rather then creating custom table and program to Load and validate the data

                Makes no sense.

                 

                 

                7) Understand Oracle's Cost based Optimizer and Tune most expensive SQL queries with help of Explain plan

                What about using the execution trace?

                 

                 

                8) Follow Oracle PL/SQL Best Practices

                Which are?

                 

                 

                9) Review tables and their indexes being used in the SQL queries and avoid unnecessary Table scanning

                You mean design your database and queries properly?  And table scanning is not always bad.

                 

                 

                10) Avoid costly Full Table Scan on Big Transaction tables with Huge data volume,

                It depends if that is necessary or not.

                 

                 

                11) Use appropriate filtration condition on index columns of seeded Oracle tables directly mapped to program parameters

                No, consider that too many indexes can have an impact on overall performance and can prevent the CBO from picking the best plan.  There's far more to creating indexes than just picking every column that people are likely to search on; you have to consider the cardinality and selectivity of data, as well as the volumes of data being searched and the most common search requirements.

                 

                 

                12) Review Join condition on existing query explain plan

                Well, if you don't have your join conditions right then your query won't work, so that's obvious.

                 

                 

                13) Use Oracle hint to guide Oracle Cost based optimizer to choose best plan for your custom queries

                 

                No.  Oracle recommends you do not use hints for query optimization (it says so in the documentation).  Only certain hints such as APPEND etc. which are more related to certain operations such as inserting data etc. are acceptable in general.  Oracle recommends you use the query optimization tools to help optimize your queries rather than use hints.

                 

                 

                14) Avoid applying SQL functions on index columns

                Why?  If there's a need for a function based index, then it should be used.

                 

                15) Use appropriate hint to guide Oracle CBO to choose best plan to reduce response time

                See 13.

                 

                In short, there are no silver bullets for dealing with performance.  Each situation is different and needs to be evaluated on its own merits.

                • 6. Re: How to resolve most of the Oracle SQL , PL/SQL Performance issues with help of quick Checklist/guidelines ?
                  Nikolay Savvinov

                  Hi,

                   

                  I won't even go throw the listed items one-by-one. The very concept of having a "magical checklist"  to prevent or resolve all performance problems is totally and entirely wrong. Checklists are good for when you're doing the same sequence of actions over and over, just to make sure that you haven't forgotten anything. Performance tuning doesn't fit into this scheme, because performance issues are all different and require different actions. And these actions need to be based on understand of what is going on, not on some stupid memorized rules. But if you need to write it up in form of a check list, there you go:

                   

                  1) Identify the root cause correctly

                  2) Identify the corrective action to address the root cause

                  3) Apply it.

                   

                  The nature of the corrective action can be different depending on circumstances. It can be enforcing index usage. It can be avoiding index usage and forcing a full scan of an index or a table. It can be switching some SQL to bind variables. It can be switching SQL from bind variables to literals. It can be creating a histogram on a column. It can be removing a histogram on a column. I.e. the same thing could be good or bad depending on circumstances. All these "good practices" are built on a simple premise: that some situations occur statistically more frequently than other. E.g. statistically situations requiring binds occur more frequently than situations requiring literals. But it doesn't mean that your specific problem at hand always requires binds. And same with everything else.

                   

                  Another important point is that many such checklists are influenced by "good practices", or simply rephrase such "good practices" in one form or another. I don't have anything against "good practices" per se, most of them make sense, but they have to do more with preventing problems more than resolving them. Unfortunately, this distinction is almost always ignored. Worrying about "good practices" before identifying (and addressing) the root cause of an issue is probably the stupidest thing one can do in case of an ungoing performance issue (and nevertheless many "consultants" and "analysts" do exactly that). Let me illustrate my point using a medical analogy: if you don't wash your hands regularly, you can get bellyache. But if your belly is already aching, not only dirty hands is not necessarily the reason, but even if it was, washing hands after the fact won't fix the infection.

                   

                  Best regards,

                    Nikolay

                  • 7. Re: How to resolve most of the Oracle SQL , PL/SQL Performance issues with help of quick Checklist/guidelines ?
                    Sven W.

                    I think the checklist is decent. I've seen much worse checklists in the past.

                    The quick resolution tipps unfortunately have almost nothing to do with the checklist.

                     

                    Why? For me it seems the checklist is focused on SQL especially on SELECT statements and the execution plan, while the resolution is focused on PL/SQL, especially ETL jobs (btw oracle recommends to do ELT instead of ETL). This makes it a heavy mismatch.

                     

                    Also I do agree with Nikolay. Most "solutions" tend to favor certain concepts, for example they work in a data warehouse environment, but they wont work in another environement (OLTP). With that in mind, you maybe can write good "best practices" as long as if you also describe the specific scenarios where those work and where not.