Home » RDBMS Server » Performance Tuning » How to tune SQL or Identify Performance Problem and Bottleneck  () 4 Votes
icon6.gif  How to tune SQL or Identify Performance Problem and Bottleneck [message #246965] Fri, 22 June 2007 11:19 Go to next message
Michel Cadot
Messages: 58953
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How to Identify Performance Bottlenecks in 10g and up:

[Updated on: Fri, 16 August 2013 14:50]

Report message to a moderator

How to Identify Performance Problem and Bottleneck [message #247207 is a reply to message #246965] Mon, 25 June 2007 01:18 Go to previous messageGo to next message
Frank Naude
Messages: 4420
Registered: April 1998
Senior Member
This following resources may also help:
These articles may help you understand some key points:

[Updated on: Tue, 01 July 2014 04:40] by Moderator

Report message to a moderator

Performance Monitoring [message #271021 is a reply to message #246965] Fri, 28 September 2007 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 58953
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The following from Joel Garry explains what you have to do to be able to optimize your performances.

  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

SQL statement tuning [message #433888 is a reply to message #246965] Fri, 04 December 2009 19:18 Go to previous messageGo to next message
BlackSwan
Messages: 22723
Registered: January 2009
Senior Member



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 <"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
    • execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);
      execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
      execute DBMS_METADATA.SET_TRANSFORM_PARAM (DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',false);
          
      TRUNCATE TABLE PLAN_TABLE;
      
      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')
      );
      
      SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, COLUMN_POSITION 
      FROM ALL_IND_COLUMNS 
      WHERE TABLE_NAME IN (SELECT OBJECT_NAME FROM PLAN_TABLE p WHERE  object_type = 'TABLE') ORDER BY 1,2,4;
      
      COMMIT;
      
      
    • 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
      ALTER SESSION SET SQL_TRACE=TRUE;
      -- invoke the slow SQL statement
      ALTER SESSION SET SQL_TRACE=FALSE;
      
      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

http://www.orafaq.com/forum/t/84315/136107/

[Updated on: Mon, 18 February 2013 02:26] by Moderator

Report message to a moderator

Re: SQL statement tuning [message #506499 is a reply to message #433888] Mon, 09 May 2011 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 22723
Registered: January 2009
Senior Member
BASICS of Performance Tuning by Kevin Meade

http://www.orafaq.com/forum/mv/msg/170674/505512/136107/#msg_505512
Re: SQL statement tuning [message #514568 is a reply to message #433888] Tue, 05 July 2011 07:40 Go to previous messageGo to next message
LNossov
Messages: 288
Registered: July 2011
Location: Germany
Senior Member
Remote SQL Tuning is very problematic. Information listed above isn't sufficient for that. Oracle's SQLT (s. the Note 215187.1 in MyOracle) can help much more. Or the package dbms_sqldiag (export & import of testcases). Even if the testcases involve only metadata.
Re: SQL statement tuning [message #593950 is a reply to message #514568] Thu, 22 August 2013 04:09 Go to previous message
Michel Cadot
Messages: 58953
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another BASICS of Performance Tuning by Kevin Meade:

http://www.orafaq.com/forum/mv/msg/189201/593907/102589/#msg_593907

Previous Topic: Logging operation takes longer but no wait events
Goto Forum:
  


Current Time: Mon Sep 01 10:25:18 CDT 2014

Total time taken to generate the page: 0.56136 seconds