OraFAQ Forum - RDF feed http://www.orafaq.com/forum/ How to tune SQL or Identify Performance Problem and Bottleneck http://www.orafaq.com/forum/./mv/msg/84315/246965/#msg_246965 How to Identify Performance Bottlenecks in 10g and up:
Michel Cadot 2007-06-22T16:19:44-00:00
How to Identify Performance Problem and Bottleneck http://www.orafaq.com/forum/./mv/msg/84315/247207/#msg_247207 This following resources may also help:
These articles may help you understand some key points:
Frank Naude 2007-06-25T06:18:03-00:00
Performance Monitoring http://www.orafaq.com/forum/./mv/msg/84315/271021/#msg_271021
  1. Read Concepts manual.
  2. Understand that most performance issues come from application issues. For example, if some silly SQL reads an entire table to get a few rows, you will likely have a lot of unnecessary I/O that won't fill up the SGA.
  3. Read the Performance manual.
  4. Understand the optimizer. It can only use the information it is given. If the statistics it uses are wrong, nonexistent, or skewed in a manner the optimizer doesn't know about, it can choose a silly plan for accessing the data. Sometimes a full table scan is not silly.
  5. Understand what plans are and how to use them to understand 4.
  6. Understand what statspack can tell you.
  7. Understand when, how and why to use tracing.
  8. Understand what waits are and how to evaluate them.
  9. Read and work through books and articles by Jonathan Lewis, Tom Kyte, and Cary Milsap.
  10. Understand why rules of thumb can be a bad idea for improving the database of customers.
  11. Understand that tools based strictly on Oracle can be a bit misleading from a systems standpoint, and systems tools can be misleading from Oracle's viewpoint. Simply knowing you have a lot of reads does not mean anything is wrong, after all, what is a database going to be used for? A proper tuning methodology will figure out what critical bottlenecks are, and what to do about them.
  12. Understand the basics. For example, if you have sequential write-intensive archive writing interfering with random reads and writes for undo and everything else, thrashing a SAN cache, you probably have a configuration problem. If you have multiple users accessing data, you need to understand how Oracle handles the issues involved.
  13. Create clear metrics for performance improvement.
  14. Read Concepts manual.
  15. Go to #1 above

Michel Cadot 2007-09-28T19:11:16-00:00
SQL statement tuning http://www.orafaq.com/forum/./mv/msg/84315/433888/#msg_433888

NOBODY can tune a SQL statement just by looking at it.
Not even Oracle, which is why this is a manual process.

But WE can help! Just follow these easy steps.

  1. Post your SQL

    • Make sure it's formatted, otherwise we can't (and won't!) read it.
      PLEASE read the section "How to Format your SQL"
      How to format your post?

  2. Post the EXPLAIN PLAN that Oracle uses to execute your SQL.

    • Run the following in SQL*Plus
    • If PLAN_TABLE does not exist, then invoke @$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL
      EXPLAIN PLAN FOR <"slow" SQL statement>
      SELECT * FROM table(dbms_xplan.display);
    • Don't forget to format the plan when you post it. The indentation is VERY important.

  3. Post the DDL used to create your tables and their indexes

    • Easiest way to do this is with DBMS_METADATA
      EXPLAIN PLAN FOR <your slow SQL statement>;
      SELECT DBMS_METADATA.GET_DDL (object_type, object_name, object_owner)
      FROM   plan_table
      WHERE  object_type IN ('TABLE','VIEW');
      SELECT DBMS_METADATA.GET_DDL ('INDEX', index_name, index_owner)
      FROM   all_indexes
      WHERE  table_owner, table_name IN (
          SELECT object_owner, object_name
          FROM   plan_table p
          WHERE  object_type IN ('TABLE')
    • cut the SQL from code frame above & PASTE into a terminal/command window running sqlplus
    • cut SQL & output from above & PASTE formatted results into your post.

  4. Post a Trace of the problem SQL when it is running

    • This is probably the hardest thing to do for a novice, but it is absolutely the MOST important. Most problems are MUCH easier to diagnose from a trace, many others are impossible to accurately diagnose without one. It it WELL worth your time doing this - you will almost certainly get good relevant help if you post a trace.
    • Run the following in SQL*Plus
      -- invoke the slow SQL statement
      SHOW PARAMETER user_dump_dest

    • Now find the trace file within User Dump Dest folder (displayed from the SHOW PARAMETER command above], and type the following from the Operating System Prompt:
      tkprof <trace_file.trc> trace_results.txt

If/when you have a problem, post formatted whole sqlplus session so we can see what you see.
If you do not understand or can not provide requested details, tell us why you don't comply.

Want to help yourself? DIY Tuning


--update: corrected typo. jw.]]>
BlackSwan 2009-12-05T01:18:34-00:00
Re: SQL statement tuning http://www.orafaq.com/forum/./mv/msg/84315/506499/#msg_506499 BASICS of Performance Tuning by Kevin Meade

BlackSwan 2011-05-09T13:44:48-00:00
Re: SQL statement tuning http://www.orafaq.com/forum/./mv/msg/84315/514568/#msg_514568 LNossov 2011-07-05T12:40:54-00:00 Re: SQL statement tuning http://www.orafaq.com/forum/./mv/msg/84315/593950/#msg_593950 BASICS of Performance Tuning by Kevin Meade:


Michel Cadot 2013-08-22T09:09:17-00:00
Re: SQL statement tuning http://www.orafaq.com/forum/./mv/msg/84315/634324/#msg_634324
At the suggestion of BlackSwan, I have also attached the latest document which describes the information needed to tune a SQL statement and how you can get it using the scripts also available here. If you are going to ask for help in tuning a SQL statement, please review this document first. You will at some point be asked to produce this information so if you can have it in your original post, better results will follow. I suggest providing the BASIC information first, then follow up with the ADVANCED information when asked.

Enjoy. Kevin

Oracle SQL Performance Tuning and Optimization: It's all about the Cardinalities

Kevin Meade 2015-03-07T18:25:05-00:00