Set as Homepage   Add to Favourites   Recommend   Contact



Menu

∙ Home
∙ Index (All Topics)
∙ About Me
∙ About This Blog
∙ Favourite Links
∙ RSS Feed

Categories

∙ ASP & PHP (1)
∙ HTML, XML and CSS (2)
∙ C / C++ (5)
∙ Java, JSP and Servlet (0)
∙ SQL-Oracle-PL/SQL (28)
∙ Operating Systems (1)
∙ OFF-Topic (8)

Popular Topics (Top 10)

∙ Decode Function in Oracle SQL (71921)

∙ Derin anlamlı sözler - Bunlar da Türkçe olanlar :) (68444)

∙ Oracle performance analysis - Tracing and performance evaluation (53369)

∙ When a transaction begins? (45813)

∙ Differences between C and C++ (44940)

∙ Turkcell Staj Günlüğü - 1: Introduction to Oracle (40708)

∙ Implicit vs. Explicit cursors - Performance analysis (24259)

∙ Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals" (22231)

∙ Finding and Removing Loop on a Singly-Linked List (21205)

∙ Turkcell Staj Günlüğü - 4: Transaction Management (20807)


Most Recent (Last 10)

∙ Matematik Asla Yalan Söylemez!

∙ Finding and Removing Loop on a Singly-Linked List

∙ Obfuscated C

∙ Is C a Vitamin? Yes, of course...

∙ Differences between C and C++

∙ Whence C? Why C? Whither C?

∙ Türkçe Karakterli Domain'lerin İç Yüzü

∙ Windows Source Codes

∙ Decode Function in Oracle SQL

∙ Hello World!


Recent Comments (Last 10)

∙ "tebrikler" By yasin on Turkcell Staj Günlüğü - 5: Startup, Shutdown

∙ "Gercekten Güzel Bir Çalışma" By Hüseyin Karabakla on Neden hazır blog'ları kullanmadım ki?

∙ "Konu paralelinde güzel bir özet ek okuma - " By TongucY on Oracle performance analysis - Tracing and performance evaluation

∙ "harika" By burak ozcan on Derin anlamlı sözler - Bunlar da Türkçe olanlar :)

∙ "Tebrikler" By Tarık Bayzın on Turkcell Staj Günlüğü - 1: Introduction to Oracle

∙ "Gayet Başarılı.." By Fahri ATES on Turkcell Staj Günlüğü - 1: Introduction to Oracle

∙ "Helal olsun" By ender onder on Turkcell Staj Günlüğü - 5: Startup, Shutdown

∙ "tebrikler.." By ender ondeer on Turkcell Staj Günlüğü - 4: Transaction Management

∙ "Adulation?" By fizikci on Matematik Asla Yalan Söylemez!

∙ "Rehberlik için çook teşekkürler" By Pınar Tanrıverdi on Kahin'e yolculuk nasıl başlamalı?


Archive (Last 12 Months)

∙ Feb, 2008 (4)
∙ Jan, 2008 (2)
∙ Dec, 2007 (1)
∙ Sep, 2007 (4)
∙ Aug, 2007 (9)
∙ Jul, 2007 (22)
∙ Jun, 2007 (3)
∙ Index (All Records)

Other Related Blogs

∙ Tom Kyte’s Blog
∙ Steven Feuerstein’s Blog
∙ Jonathan Lewis’s Blog
∙ H.Tonguç Yılmaz Oracle Blog
∙ Mennan Tekbir's Blog
∙ Hakkı Oktay’s Blog
∙ Osman Çam’s Blog

Stats

Total Topics
Total Topic Views
Total Comments
Unique Visitors
Total Visitors
: 45
: 914773
: 44

About this blog…
About this blog…
About Me
About Me
Favourite Links
Favourite Links
Neden hazır blog'ları kullanmadım ki?
Neden hazır blog'ları kullanmadım ki?
CSS is more powerful than you imagine
CSS is more powerful than you imagine
Turkcell Staj Günlüğü - 1: Introduction to Oracle
Turkcell Staj Günlüğü - 1: Introduction to Oracle
Turkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments
Turkcell Staj Günlüğü - 2: Data Blocks, Extends and Segments
Kahin'e yolculuk nasıl başlamalı?
Kahin'e yolculuk nasıl başlamalı?
Turkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control Files
Turkcell Staj Günlüğü - 3: Tablespaces, Datafiles and Control Files
Turkcell Staj Günlüğü - 4: Transaction Management
Turkcell Staj Günlüğü - 4: Transaction Management
Image formats - Which to use when
Image formats - Which to use when
Turkcell Staj Günlüğü - 5: Startup, Shutdown
Turkcell Staj Günlüğü - 5: Startup, Shutdown
Turkcell Staj Günlüğü - 6: Oracle Architecture
Turkcell Staj Günlüğü - 6: Oracle Architecture
ASP - Locales and Codepages
ASP - Locales and Codepages
Oracle performance analysis - Tracing and performance evaluation
Oracle performance analysis - Tracing and performance evaluation
Oracle performance analysis - Autotrace workshop
Oracle performance analysis - Autotrace workshop
Oracle performance analysis - Runstats workshop
Oracle performance analysis - Runstats workshop
Oracle performance analysis - Tkprof workshop
Oracle performance analysis - Tkprof workshop
Some favourite quotes
Some favourite quotes
Derin anlamlı sözler - Bunlar da Türkçe olanlar :)
Derin anlamlı sözler - Bunlar da Türkçe olanlar :)
Turkcell Staj Günlüğü - 7: Concurrency and Consistency
Turkcell Staj Günlüğü - 7: Concurrency and Consistency
"Kurtuluş"un hikayesi
"Kurtuluş"un hikayesi
Turkcell Staj Günlüğü - 8: Statement Processing and CBO
Turkcell Staj Günlüğü - 8: Statement Processing and CBO
When a transaction begins?
When a transaction begins?
Implicit vs. Explicit cursors - Performance analysis
Implicit vs. Explicit cursors - Performance analysis
Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals"
Turkcell Staj Günlüğü - 9: "SQL, PL/SQL and Java" ve "Redo Internals"
Affect of gathering table stats to decision of CBO
Affect of gathering table stats to decision of CBO
Bind is bad :) - An interesting case of bind variables fails
Bind is bad :) - An interesting case of bind variables fails
When the explanation doesn't sound quite right...
When the explanation doesn't sound quite right...
Turkcell Staj Günlüğü - 10: Import, Export ve SQL Loader
Turkcell Staj Günlüğü - 10: Import, Export ve SQL Loader
Turkcell Staj Günlüğü - 11: Autonomous Transactions ve Dynamic SQL
Turkcell Staj Günlüğü - 11: Autonomous Transactions ve Dynamic SQL
Difference between db block gets and consistent gets
Difference between db block gets and consistent gets
Object-Oriented Features of Oracle - Part 1: Native Datatypes vs. Object Datatypes
Object-Oriented Features of Oracle - Part 1: Native Datatypes vs. Object Datatypes
Object-Oriented Features of Oracle - Part 2: Object Types and Collection types
Object-Oriented Features of Oracle - Part 2: Object Types and Collection types
Object-Oriented Features of Oracle - Part 3: Object Tables, Object Views and REFs
Object-Oriented Features of Oracle - Part 3: Object Tables, Object Views and REFs
Examining show_space
Examining show_space
Turkcell Staj Günlüğü - 12: Partitioning
Turkcell Staj Günlüğü - 12: Partitioning
Hello World!
Hello World!
Decode Demo #1
Decode Demo #1
Decode Demo #2
Decode Demo #2
Decode Demo #3
Decode Demo #3
Decode Demo #4
Decode Demo #4
Decode Function in Oracle SQL
Decode Function in Oracle SQL
Windows Source Codes
Windows Source Codes
Türkçe Karakterli Domain'lerin İç Yüzü
Türkçe Karakterli Domain'lerin İç Yüzü
Whence C? Why C? Whither C?
Whence C? Why C? Whither C?
Differences between C and C++
Differences between C and C++
Is C a Vitamin? Yes, of course...
Is C a Vitamin? Yes, of course...
Obfuscated C
Obfuscated C
Finding and Removing Loop on a Singly-Linked List
Finding and Removing Loop on a Singly-Linked List
Matematik Asla Yalan Söylemez!
Matematik Asla Yalan Söylemez!
eXTReMe Tracker
Oracle performance analysis - Tracing and performance evaluation
Category: SQL-Oracle-PL/SQL
Date: 17.07.2007 15:51:23


This article is about Oracle SQL/PLSQL Performance Analysis, the aim and the tools.

Performance tuning is a broad and somewhat complex topic area when it comes to Oracle databases. Two of the biggest questions faced by your average DBA concern where to start and what to do. All you may know is that someone (a user) reports a problem about a slow or poor performing application or query. Where do you even begin to start when faced with this situation?

Oracle Performance Tuning is a complex, continuous and iterative process. It will be continuous because everytime performance needs and specifications of the system may change. Also the data, the statistics and the execution plans will change. The parameters and problematic queries or objects should be tuned for best performance every time. (This is the DBA's aspect of course, developer's will consider much more things such as reliability, mobility and modularity of code)

There are some approaches and methodologies of tuning an Oracle database. Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement. The steps are:

1- Database Design (if it's not too late): Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.

2- Application Tuning: Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.

3- Memory Tuning: Properly size your database buffers (shared_pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.

4- Disk I/O Tuning: Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.

5- Eliminate Database Contention: Study database locks, latches and wait events carefully and eliminate where possible.

6- Tune the Operating System: Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.

The change from a top-down structured approach to a principle-based "make it stop hurting" one is part of the problem. Gathering statistics is obviously important because how else do you know if you have improved (or worsened) the problem? Still, to some degree with either approach, you are left with the original two questions: what do I look for, and how do I make it better? If the structured approach left you scratching your head, the principled approach only adds to the confusion.

What would help the novice tuner is a list of items or areas to evaluate (configure, diagnose, and tune) in each of the following areas:

• Tuning the Buffer Cache
• Tuning the Redo Log Buffer
• Tuning the Shared Pool Memory
• Tuning the Program Global Area
• Optimizing Data Storage
• Optimizing Tablespaces
• Tuning Undo Segments
• Detecting Lock Contention
• Tuning SQL
Tuning SQL

An important facet of database system performance tuning is the tuning of SQL statements. SQL tuning involves three basic steps:

1- Identifying high load or top SQL statements that are responsible for a large share of the application workload and system resources, by reviewing past SQL execution history available in the system.

2- Verifying that the execution plans produced by the query optimizer for these statements perform reasonably.

3- Implementing corrective actions to generate better execution plans for poorly performing SQL statements.
These three steps are repeated until the system performance reaches a satisfactory level or no more statements can be tuned.

High-load SQL statements can be identified by:

1- Automatic Database Diagnostic Monitor

2- Automatic Workload Repository

3- V$SQL view

4- Custom Workload

5- SQL Trace
We usually use SQL trace and some tools to analyze the trace, then focus to the problem and tune. The most common tool for analyzing SQL trace is tkprof. There are some simpler tools such as Autotrace and Runstats.

AUTOTRACE

The autotrace utility is a very underutilized feature of SQL*Plus. It offers statement tracing and instant feedback on any successful SELECT, INSERT, UPDATE or DELETE statement. The utility requires a plan table (for the Explain Plan) under the current user’s schema. In addition, it requires the plustrace or DBA role for the user executing the trace. The source for the PLUSTRACE role can be found in $ORACLE_HOME/sqlplus/admin/plustrce.sql.

Autotrace is simpler, gives statistics just about the query, and it's just for single SQL statements, not woks for PL/SQL blocks.

Preparing to Analyse Statements

Before the AUTOTRACE command can be used a table called PLAN_TABLE must be created in your user account. This table is created by executing the following command:

@$ORACLE_HOME/RDBMS/ADMIN/utlxplan.sql

Also, the user to run Autotrace should have PLUSTRACE role granted. By default, plustrace role is undefined. You can create it and grant to user by executing following command:

@$ORACLE_HOME/SQLPLUS/ADMIN/PLUSTRCE.SQL

grant plustrace to user1 ;

You can control the report by setting the AUTOTRACE system variable.

SET AUTOTRACE OFF: No AUTOTRACE report is generated. This is the default.
SET AUTOTRACE ON EXPLAIN: The AUTOTRACE report shows only the optimizer execution path.
SET AUTOTRACE ON STATISTICS: The AUTOTRACE report shows only the SQL statement execution statistics.
SET AUTOTRACE ON: The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.
SET AUTOTRACE TRACEONLY: Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

Note: SET TIMING ON is a different parameter, that writes elapsed time to output.

SQL> set timing on
SQL> set autotrace on
SQL> select last_name from employees where department_id=10;

LAST_NAME
-------------------------
Whalen

Elapsed: 00:00:00.40

Execution Plan
----------------------------------------------------------
Plan hash value: 2056577954

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Ti
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    11 |     2   (0)| 00
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |    11 |     2   (0)| 00
|*  2 |   INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     1 |       |     1   (0)| 00
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPARTMENT_ID"=10)


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        340  bytes sent via SQL*Net to client
        376  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

This example executes a query to list some data in the employees table. Oracle prints four sets of data:

• The actual results of the query
• Execution time of the query (because of the command: "set timing on")
• An execution plan for the query
• The statistics for the query
Execution Plan

The execution plan describe the sequence of operations that Oracle performed to obtain the result for the query. In this example, the SELECT statement was resolved by executin a full table access of the emp table. That is, all the table was read from the table emp.

Statistics

The statistics provide a summary of how much work Oracle performed to execute the query. The statistics are:

Statistic Explanation

recursive calls: The number of internal calls Oracle has made to execute the command.
db block gets: The number of blocks retrieved to answer the query.
consistent gets: The number of blocks retrieved that did not change the data and therefore did not interfere with other users (i.e. by locking data).
physical reads: The number of blocks read from the disc.
redo size: The number of redo entries.
bytes sent via SQL*Net to client: The number of bytes sent across the network from the server to the client.
bytes received via SQL*Net from client: The number of bytes sent across the network from the client to the server.
SQL*Net roundtrips to/from client: The number of exchanges between client and server.
sorts (memory): The number of data sorts performed in memory.
sorts (disc): The number of data sorts performed on disc.
rows processed: The number of rows processed by the query.
The db block gets, consistent gets and physical reads give the number of blocks that were read to form the buffers or from the disc. For many queries, the number of physical reads is low as the data is already in the database buffers. If the number of physical reads is high then the query will be expected to be slow as there will be many disc accesses.

The bytes received/sent via SQL*Net indicate how much data is being moved across the network. This is important as moving a lot of data across the network may affect the network's performance.

The sorts indicate the amount of work done in sorting data during the execution of the query. Sorts are important as sorting data is a slow process.

You can find more examples on the topic: Oracle Performance Analysis: Autotrace workshop

RUNSTATS

Runstats is another simple utility that shows satisfactory insormation about both SQL statements' and PL/SQL procedures' performance evaluation. It is best for comparing two different approaches. It shows two vital sets of statistics:

The elapsed time difference between two approaches. It very simply shows me which approach is faster by the wall clock How many resources each approach takes. This can be more meaningful then even the wall clock timings. For example, if one approach is faster then the other but it takes thousands of latches (locks), I might avoid it simply because it will not scale as well.

The way this test harness works is by saving the system statistics and latch information into a temporary table. We then run a test and take another snapshot. We run the second test and take yet another snapshot. Now we can show the amount of resources used by approach 1 and approach 2.

Runstats package is written by Thomas KYTE, and you can find more information about it and deployment of it on: http://asktom.oracle.com/tkyte/runstats.html

For using runstats, you must first create runstats package once. The runstats package code is on the site above. Once you create the package, that is OK, you don't need to run package code everytime.

The second important point to consider is, runstats takes the time elapsed between executions of its procedures. So, you have to run the steps of code immediately.
exec runStats_pkg.rs_start;
**FIRST SQL OR PL/SQL CODE TO COMPARE**
exec runStats_pkg.rs_middle;
**SECOND SQL OR PL/SQL CODE TO COMPARE**
exec runStats_pkg.rs_stop;


You have to run whole code above immediately, every line immediately after each other, so first write your code to a textfile, then copy the whole code like above to console. Don't forget to have an extra newline at the end of the code you copy, to also run the last line "exec runStats_pkg.rs_stop;" immediately.

You can find examples with runstats on the topic: Oracle Performance Analysis: Runstats workshop

SQL TRACE AND TKPROF

The SQL Trace facility and TKPROF let you accurately assess the efficiency of the SQL statements an application runs. For best results, use these tools with EXPLAIN PLAN rather than using EXPLAIN PLAN alone. This gives more advanced statistics for each SQL Statement that run and recursively called SQL statements and even the internal SQL stamements of Oracle to modify the dictionary.

The SQL Trace facility provides performance information on individual SQL statements and recursive statements. It generates the following statistics for each statement:

• Parse, execute, and fetch counts
• CPU and elapsed times
• Physical reads and logical reads
• Number of rows processed
• Misses on the library cache
• Username under which each parse occurred
• Each commit and rollback
• Wait event data for each SQL statement, and a summary for each trace file
If the cursor for the SQL statement is closed, SQL Trace also provides row source information that includes:

• Row operations showing the actual execution plan of each SQL statement
• Number of rows, number of consistent reads, number of physical reads, number of physical writes, and time elapsed for each operation on a row
When the SQL Trace facility is enabled for a session or for an instance, performance statistics for all SQL statements executed in a user session or in the instance are placed into trace files. Using the SQL Trace facility can have a severe performance impact and may result in increased system overhead, excessive CPU usage, and inadequate disk space. So, it is good to use this for a system in test, not for a system in production.

Trace files are mostly hard to understand. So, there are utilities to format the trace files and give useful statistics output for us. TKPROF is the most common utility. TKPROF comes with Oracle release, that format the contents of the trace file and place the output into a readable output file.

The Performance Tuning Guide is a good document that shows everything about this concept. Follow the link for SQL Trace and Usage of TKPROF: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sqltrace.htm#sthref2019

You can find examples with SQL Trace and TKPROF on the topic: Oracle Performance Analysis: TKPROF workshop

10046 and 10053 Events

If you've got an interest in Oracle performance tuning, you'll probably have seen references to something called event 10046. Event 10046 is an undocumented, but well known feature within the Oracle database that generates detailed information on how a statement is parsed, what the values of bind variables are, and what wait events occurred during a particular session. Activating Event 10046 gives us a more detailed trace.

For methods that require tracing levels the following are valid values:

0 - No trace. Like switching sql_trace off.
2 - The equivalent of regular sql_trace.
4 - The same as 2, but with the addition of bind variable values.
8 - The same as 2, but with the addition of wait events.
12 - The same as 2, but with both bind variable values and wait events.
To ensure that wait events information is written to the trace file for the session, enable tracing with following SQL statement:

ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';

10053 Event with 10gR2 version provides very detailed information on how and why Cost Based Optimizer choosed to execute the query.

To enable 10053 event for sql trace, enable tracing with following SQL statement:

ALTER SESSION SET EVENTS ‘10053 TRACE NAME CONTEXT FOREVER, LEVEL 1';

For more information, please go further the references below. Also, I have written code example entries for each method. Follow the workshop topics.

Related Topics
Links & References

Comments

TUNING RELATED ISSUES
Name: Srinivas, Motadikela
URL: URL not entered.
Date: 09.10.2007 10:28:02


Hi,

I have heard all the time about the tuning issues after gone through the following link i got a cleared idea about the issues iam facing

Konu paralelinde güzel bir özet ek okuma -
Name: TongucY
URL: http://tonguc.wordpress.com
Date: 08.06.2009 11:39:15


http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html



© Copyright. All rights reserved. Designed by Bilal Hatipoğlu. RSS Feed  Valid W3C XHTML 1.0 Document  Valid W3C CSS Document