Home » RDBMS Server » Performance Tuning » Performances and tuning
icon6.gif  Performances and tuning [message #246965] Fri, 22 June 2007 11:19 Go to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How to Identify Performance Bottlenecks in 10g and up:
  • Start by reading the following URL: www.perfvision.com/papers/Sampling.pdf
  • You can get a copy of the Active Session History (ASH) code here: www.perfvision.com/ash.php

Perfvision site is no more available.

[Updated on: Wed, 11 August 2021 08:19]

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: 4579
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: 68624
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: 26766
Registered: January 2009
Location: SoCal
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 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
    • 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

See link below

--update: corrected typo. jw.
--update: fix link. mc

[Updated on: Fri, 28 April 2023 00:52] 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: 26766
Registered: January 2009
Location: SoCal
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: 318
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 messageGo to next message
Michel Cadot
Messages: 68624
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

Re: SQL statement tuning [message #634324 is a reply to message #593950] Sat, 07 March 2015 12:25 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am attaching here the promotional first chapter of my book on SQL Tuning, and the scripts file for people to find. These are free to use and share. As always, there is no warranty express or implied and use at your own risk.

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

[Updated on: Thu, 12 March 2015 20:47]

Report message to a moderator

Re: SQL statement tuning [message #684750 is a reply to message #433888] Wed, 11 August 2021 03:35 Go to previous messageGo to next message
dragam
Messages: 31
Registered: May 2012
Location: Ireland
Member
[quote title=BlackSwan wrote on Sat, 05 December 2009 02:18]


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


This link is now apparently invalid!


Pól...


Re: Performances and tuning [message #684751 is a reply to message #246965] Wed, 11 August 2021 03:54 Go to previous messageGo to next message
dragam
Messages: 31
Registered: May 2012
Location: Ireland
Member
Hi

When I click on the two links on your post (first post on page), I get dropped into some sort of spam financial site!

HTH, À+

Pól...

Re: Performances and tuning [message #684754 is a reply to message #684751] Wed, 11 August 2021 08:31 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

(Original) perfvision no more exists, first post updated.
Orafaq was updated, BS link is no more available, it is replaced by KM links in the next posts.

Thanks to warn us about that.

Previous Topic: query output speed very slow
Goto Forum:
  


Current Time: Thu Mar 28 09:17:33 CDT 2024