Home » RDBMS Server » Performance Tuning » Performances and tuning
 Performances and tuning [message #246965] |
Fri, 22 June 2007 11:19  |
 |
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
| Performance Monitoring [message #271021 is a reply to message #246965] |
Fri, 28 September 2007 14:11  |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
The following from Joel Garry explains what you have to do to be able to optimize your performances.
- Read Concepts manual.
- 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.
- Read the Performance manual.
- 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.
- Understand what plans are and how to use them to understand 4.
- Understand what statspack can tell you.
- Understand when, how and why to use tracing.
- Understand what waits are and how to evaluate them.
- Read and work through books and articles by Jonathan Lewis, Tom Kyte, and Cary Milsap.
- Understand why rules of thumb can be a bad idea for improving the database of customers.
- 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.
- 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.
- Create clear metrics for performance improvement.
- Read Concepts manual.
- Go to #1 above
|
|
|
|
| SQL statement tuning [message #433888 is a reply to message #246965] |
Fri, 04 December 2009 19:18 |
 |
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.
- 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?
How to format your post?
If you are providing code snippets such as SQL*Plus copy/paste, please be sure to use formatting tags: [code] Enter your code here.[/code] It makes a huge difference to the forum readers. Unreadable code makes a question harder to understand and will delay answers. A lot of people don't spend that much time on the forum and unclear posts will be skipped more often because they take a lot more time to decipher.
The difference between the code blocks below is clear:
First a block without proper tags, you'll notice that it is not that readable:
DECLARE
CURSOR yourcursor
IS
SELECT yourcolumn
, another_column
FROM yourtable
WHERE some_column BETWEEN A AND B
AND some_other > SYSDATE;
BEGIN
FOR a_record IN yourcursor
LOOP
do_something_here;
IF a_record.another_column = 1
THEN
do_extra_stuff;
ELSE
do_other_stuff;
END IF;
END LOOP;
END;
Now, the same PL/SQL block this time with use of the code tags. It clearly delineates the code from the other text and preserves text indenting.
DECLARE
CURSOR yourcursor
IS
SELECT yourcolumn
, another_column
FROM yourtable
WHERE some_column BETWEEN A AND B
AND some_other > SYSDATE;
BEGIN
FOR a_record IN yourcursor
LOOP
do_something_here;
IF a_record.another_column = 1
THEN
do_extra_stuff;
ELSE
do_other_stuff;
END IF;
END LOOP;
END;
You can also paste the code in your message, select it and hit this button  .
This is how the post form screen looks like and the way [code] tags are to be used:
What if the code is not formatted itself? Code tags will do little about that. So you first have to format the code
- by hand
- via an external tool (like TOAD)
- via the SQL Formatter page of OraFAQ.
Make sure that lines of code do not exceed 80 characters. Use the "Preview Message" button to check it.
Scott Mackey has created a nice little document explaining in detail and with screencaps how to format. The document can be downloaded/viewed here.
- 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.
- 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.
- 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 #634324 is a reply to message #593950] |
Sat, 07 March 2015 12:25 |
 |
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: Performances and tuning [message #684754 is a reply to message #684751] |
Wed, 11 August 2021 08:31 |
|
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
Goto Forum:
Current Time: Tue Dec 03 12:11:18 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
