sapnote 0000766349 sql tunnig

09.03.2011 of 25

SAP Note 766349 - FAQ: Oracle SQL optimization

Note Language: English Version: 74 Validity: Valid Since 15.07.2009

Summary

Symptom

1. What is SQL optimization?

2. How important is SQL optimization?

3. Which criteria are used to determine the load of an SQL statement?

4. Where can I find information on the executed SQL statements?

5. How do I find the most processing-intensive SQL statements overall?

6. How do I find information on an SQL statement that is currentlyrunning?

7. How can I determine what values are hidden behind the bind variables?

8. Why does the SQL statement appear different on database level than inthe ABAP source code?

9. How many processing-intensive SQL statements should be observed aspart of SQL optimization?

10. What are selection and join conditions?

11. What is a run schedule?

12. How can I display the run schedule for an SQL statement?

13. What are the characteristic components of a run schedule?

14. What is an optimal run schedule?

15. What utilities are available for further analysis of aprocessing-intensive SQL statement?

16. How can I optimize a processing-intensive SQL statement?

17. What must be considered in terms of the index design?

18. What must I take into consideration with regard to the query design?

19. What analysis tools are available at Oracle level?

20. What information do the "Estimated Costs" and "Estimated Rows" providefor an SQL statement?

21. Is it possible to determine a buffer quality for individual tables?

22. What happens when there is an overflow of the Buffer Get?

23. How can I determine when a complex access is made in the executionplan?

09.03.2011 of 25


24. Can I see a sample analysis?

25. Is the ST05 data always reliable?

26. Where can I find further information on SQL optimization?

Other termsFAQ, frequently asked questions

Reason and Prerequisites

Solution

1. What is SQL optimization?

SQL optimization (or SQL statement tuning or Shared Cursor cacheanalysis) refers to the identification, analysis and optimization ofSQL statements that are responsible for the highest load in relationto I/O and CPU consumption on database level. These statements arealso called "processing-intensive SQL statements".

Due to the complexity of the topic, this note can only offer anoutline description of an analysis. However, SAP offers various books,training courses and services on the subject (see also the finalquestion in this note).

2. How important is SQL optimization?

Regular analysis and optimization of processing-intensive SQLstatements is the MOST important basis for efficient system operation.Resource-intensive SQL statements are directly responsible forincreased I/O and CPU activities and indirectly responsible forsubsequent problems such as buffer busy waits or unusually high I/Otimes.

3. Which criteria are used to determine the load of an SQL statement?

The two main criteria for the load that creates an SQL statement are:

o Number of disk reads (or physical reads)

- Number of blocks that were not in the memory of the Oraclebuffer and therefore had to be imported from the disk

- Measure of the I/O load of an SQL statement

o Number of buffer gets (or logical reads or reads)

- Number of the blocks read in Oracle Buffer Pool in the memory

- Measure of the CPU and memory load of an SQL statement

The following criteria are also relevant and should not be neglected,even though they generally have less influence upon the databaseperformance:

09.03.2011 of 25


o Number of processed rows

- Number of table entries returned by an SQL statement

- Measurement for the network load of an SQL statement

o Number of executions (or better: number of fetches, if available)

- Measurement for the communication load with the database

o CPU-Time

- Measurement of the CPU load of an SQL statement

o Elapsed-Time

- Measurement of the actual duration of an SQL statement

o Number of direct writes (for Oracle >=10g)

- Measurement for PSAPTEMPT and direct path write activities (forexample sorting, LOB accesses, parallel query, hash-joins,bitmap operations)

The SQL statements most relevant for SQL optimization are "Disk Read"and "Buffer Get". It is irrelevant whether a statement is executedoften, causing only a small load each time or whether it is executedjust once causing a large load. It is the total load triggered by thestatement that is decisive and not the load per execution.

In the R/3 environment, statements with bind variables are parsed(:A0, :A1 ...). These bind variables may contain other specificvalues. Nevertheless, all statements that have only varying values areconsidered as an IN statement (on a bind variable level).

4. Where can I find information on the executed SQL statements?

The central source of information for SQL statements is the SharedCursor Cache, which you can access through an SAP system by selecting

Transaction ST04-> Detail Analysis Menu-> SQL Request

This cache includes the following information for all SQL statementsexecuted since the last database start that were not displaced againfrom the shared cursor cache:

o Executions: Number of executions

o Disk reads: Number of blocks read from the disk

o Disk reads/execution: Number of blocks read by the disk perexecution

o Buffer gets: Number of blocks read from the buffer pool

09.03.2011 of 25


o Buffer gets/execution: Number of blocks read per execution from thepool buffer

o Rows processed: Number of processed rows

o Rows processed/execution: Number of rows processed for eachexecution

o Buffer gets/Row: Number of blocks read per processed row from thebuffer pool

o CPU Time: Consumed CPU time (SAP basis >= 6.40)

o Elapsed Time: Duration of the execution (SAP basis >= 6.40)

o SQL statement: Text of the SQL statement

o Program name: Name of the calling program

o "Callpoint in the ABAP program" button: Exact location in the ABAPsource code when the statement call originates

There are also buttons to define a reset time and start an evaluationsince this reset time only.

5. How do I find the most processing-intensive SQL statements overall?

You receive the SQL statements that are currently most processingintensive in terms of one of the three load criteria by sorting theShared Cursor Cache entries into disk reads, buffer gets or processedrows. It is best to sort according to the three criteria one after theother and to optimize the statements with the highest load in eachcase.

To automatically determine the most processing-intensive statements,you can also use the RSORADLD report (or /SDF/RSORADLD), that listsall statements by default that are responsible for more than 2% of thedisk reads or 5% of the buffer gets.

The SAP Early Watch Alert Services also gives you an overview of themost processing-intensive SQL statements.

6. How do I find information on an SQL statement that is currentlyrunning?

If a work process is busy accessing a table in SM50/SM66 for anextended period, you can determine the relevant Oracle session usingthe Client-PID as described in Note 618868.

To obtain details of the block accesses, you can determine thisstatement in the second step in the Shared Cursor Cache.

7. How can I determine what values are hidden behind the bind variables?

In some cases the ABAP source code contains literals or constants thatare transferred 1:1 to the database. Therefore an examination of theABAP source code will provide information about the values transferred

09.03.2011 of 25


to the database.

Otherwise, up to Oracle 9i you can only determine the content of thebind variables by taking measures such as the following BEFOREexecuting an SQL statement:

o Activate an SQL trace using Transaction ST05

o J2EE Environment: Activate an SQL trace using a web browser URL(http://<hostname>:<port>/SQLTrace) or using SAP J2EE Engine VisualAdministrator

o Activate an ORADEBUG trace (Note 613872)

o Debug the ABAP program that launches the SQL statement

o Oracle 10g or higher: Enhanced SQL auditing (Note 1128663)

No further information about the contents of the bind variables can beobtained until 9i.

As of Oracle 10g, the view V$SQL_BIND_CAPTURE is available, whichstores the contents of bind variables for the executed SQL statements.With this information, you can use the following query to determinethe bind variable contents specified for an SQL statement:

SELECT SUBSTR(SBC.NAME, 1, 10) BIND, SUBSTR(SBC.VALUE_STRING, 1, 50) VALUE, COUNT(*) "NUMBER"FROM V$SQL_BIND_CAPTURE SBC, V$SQL SWHERE S.SQL_TEXT LIKE '<sql_statement_pattern>' AND S.SQL_ID = SBC.SQL_IDGROUP BY NAME, VALUE_STRINGORDER BY 1, 2;

The bind variables are updated in V$SQL_BIND_CAPTURE every 15 minutesat the earliest. If you require a faster refresh for analysispurposes, you can temporarily set the underlying underscore parameter_CURSOR_BIND_CAPTURE_INTERVAL to a second value than 900 (that is, thedefault setting of 15 minutes):

ALTER SYSTEM SET "_CURSOR_BIND_CAPTURE_INTERVAL"=<seconds>;

By default, only the first 400 byte of the bind variable content of anSQL statement are saved. For statements with a lot of bind variables,this may mean that the values of the last bind variables are notcaptured. If you require further variable content beyond 400 byte, youcan set the _CURSOR_BIND_CAPTURE_AREA_SIZE parameter to a value<bytes> of more than 400, for example:

ALTER SYSTEM SET "_CURSOR_BIND_CAPTURE_AREA_SIZE"=<bytes>;

8. Why does the SQL statement appear different on database level than inthe ABAP source code?

09.03.2011 of 25


The Open-SQL statements executed from R/3 are transferred to thedatabase via the database interface (DBI). In many cases, thestatement is modified in the DBI before being transferred to thedatabase:

o If a column with an empty variable is compared in the WHEREsection, the DBI omits this condition.

o When you use FOR ALL ENTRIES, the program distributes a value listdepending on the DBI parameters described in Note 48230(particularly rsdb/max_blocking_factor,rsdb/max_in_blocking_factor) into statements with short IN lists orOR linkages.

Note that RSPARAM always displays a value of -1 for theseparameters when you use the default values. You can determine thevalue actually in use by referring to the dev_w* work process trace(transaction ST11).

If the FOR ALL ENTRIES list is empty, all data is generallyselected from the current client ("SELECT ... FROM <table> WHEREMANDT = :A0"). All conditions in the WHERE part are ignored.

o Accesses to R/3 pool and cluster tables (that are not available asindependent tables at an Oracle level) are converted into accesseson the related table pools or table clusters.

o If tables in R/3 are completely or generically buffered, thebuffers are reloaded, if necessary, with special DBI statements(for example, "SELECT * FROM <table> WHERE MANDT = :A0 ORDER BY<primary_key_fields>" for completely buffered tables) that may becompletely different to the statement from the ABAP source code.

o The DBI provides ABAP statements that have "SELECT SINGLE" or "UPTO ROWS" without ORDER BY with the FIRST_ROWS hint and "WHEREROWNUM <= ..." (Note 135048).

o Some operations (for example, kernel procedures, bulk operations,generations) can generate SQL statements although there is noregular SQL statement in the ABAP source code.

o Even when you use conversion exits, there can be significantdifferences between ABAP source code and database-side statements(such as additional conditions that are not explicitly present inthe ABAP source code).

o IN conditions from the ABAP source code may be converted into anynumber of possible conditions on the database level, depending onyour selection criteria: "=", "IN", "LIKE", "BETWEEN", ">", "<",">=", "<="

o Columns that appear in both the selection list and the WHEREcondition are removed from the selection list if it is clear fromthe WHERE condition what the column's value must be.

o If an expression ends with a space followed by a placeholder, thesystem generates an OR connection as follows:

09.03.2011 of 25


SQL statement: ... WHERE <column> LIKE '<string> %'

Statement after DBI transformation: ... WHERE (<column> LIKE'<string> %' OR <column> LIKE '<string>')

o The existence of synchronous matchcodes provides an explanation forSELECT COUNT operations on the database in connection with DMLoperations (INSERT, UPDATE, DELETE) in ABAP. For more information,see Note 1093107.

9. How many processing-intensive SQL statements should be observed aspart of SQL optimization?

There is no single, general response to this question. The answerdepends primarily on the corresponding rows of optimization and thecurrent system status. Therefore, in one case, optimization of asingle highly processing-intensive SQL statement can ensure that apreviously barely usable system function again, while in another case,more than 50 statements must be optimized to achieve the requiredimprovement in performance.

Indicators regarding whether a system is already well tuned or not areoutlined in Note 618868.

10. What are selection and join conditions?

During a selection condition, a column is compared with one or severalspecific values (for example, "MANDT = :A0", "BDAT > '20050821'"). Injoin conditions, two column values from different tables are compared(for example, "T_00.MANDT = T_01.MANDT")

Only the selection conditions can ever be used to enter a join. Onlywhen you access the inner table of a nested loop join (see below) canthe join conditions also be used.

Selection and join conditions are specified in the WHERE part of theSQL statement and - in the case of views - are also specified withinthe view definition in Transaction SE11. These view conditions MUSTNOT be overlooked when analyzing SQL statements.

11. What is a run schedule?

A run schedule (or explain, access path) shows how Oracle accesses therequested data (index access, full table scan, join type ...).

12. How can I display the run schedule for an SQL statement?

Up to and including Oracle 8i, the run schedule could be determinedusing the Oracle Explain function. As of Oracle 9i it is also bufferedin the V$SQL_PLAN performance view.

From the shared cursor cache overview, you can go to the executionplan using the Explain button or by clicking on the SQL statement andselecting the Explain button on the dialog box.

Whether the explains displayed in R/3 are based on V$SQL_PLAN or theprevious explain depends on the R/3 Release and Support Package

09.03.2011 of 25


version. If the display of the R/3 explain does not contain anyfurther information, the normal Oracle explain is used. If you findexplicit information concerning V$SQL_PLAN, the data comes from thisview.

13. What are the characteristic components of a run schedule?

o TABLE ACCESS FULL

During a full table scan, all table blocks up to the high watermark (last ever filled block) are read

o INDEX RANGE SCAN

Depending on the selection conditions, a subarea of an index isread

o INDEX UNIQUE SCAN

In the selection conditions, all key fields of a primary index arespecified with "=" so that no more than one entry of the index isreturned.

o INDEX FULL SCAN

The entire index tree is read from left to right (-> sorted result)

o INDEX FAST FULL SCAN (as of Oracle 9i)

The blocks of the index are read in the sequence on the hard disk(due to DB_FILE_MULTIBLOCK_READ_COUNT this is faster than INDEXFULL SCAN, but the result is not sorted)

o INDEX SKIP SCAN (as of Oracle 9i)

This is comparable with several INDEX RANGE SCANs and also takesinto account index fields behind an index field that is notspecified in the selection conditions or is not specified with "=".

o TABLE ACCESS BY INDEX ROWID

Read the complete data record of the table based on the ROWID thatwas determined with a preceding index access

o NESTED LOOPS

Two tables are joined, where the system is accessed using the tablein the run schedule directly under NESTED LOOPS and the secondtable is accessed with all of the entries that are returned by thistable.

o SORT JOIN / MERGE JOIN

With the sort merge join, the relevant entries are read in parallelfrom both tables. The two resulting quantities are then sorted andfinally combined for the final resulting quantity.

o MERGE JOIN CARTESIAN / BUFFER SORT

09.03.2011 of 25


A Cartesian MERGE JOIN (which often appears in relation to a BUFFERSORT) generates the Cartesian product of the two resultingquantities. If both of the resulting quantities have x and yelements, the Cartesian product result contains x * y records.

o HASH JOIN

A hash table is set up from the access table (the table directlyunder HASH JOIN in the run schedule). The entries of the secondtable are then checked on this hash table. If the join conditionsare fulfilled, the data record will be returned.

o VIEW index$_join$_001

A line such as this in the execution plan indicates an index join.You can use this join type to combine columns from several indexesusing a hash join, before the table needs to be accessed. Indexjoins are especially useful if there are several indexes that aremoderately selective, but that are very selective if used incombination.

14. What is an optimal run schedule?

An optimal run schedule only needs a minimum of blocks to be read. Thefollowing is especially important for optimal data access:

o In most cases, an index access is better than a full table scan. Afull table scan is mainly useful when a large portion of the tableentries must be read.

o Otherwise, you must ensure that the selective conditions aresupported by an index. The greater the selectivity of a condition,the more the resulting quantity will be restricted. Note: there isno direct relationship between the selectivity and the number ofdifferent values of a column. For example, if a column only evercontains the default value ' ' (and has therefore only a singlevalue), but in the selection conditions you are searching using avalue other than ' ', the condition is very selective. On the otherhand, if a column contains 1,000,000 different values, but 90% ofthe entries have the value '000000000000000', a comparison of thecolumn using '000000000000000' will be very unselective.

Also refer to the criteria specified below for the index design.

o Has joins were deactivated in the OLTP environment until Oracle 9i.In OLAP environments and as of Oracle 10g, they can be usedeffectively.

o Merge Join Cartesians should hardly ever occur. If they do, thereis likely a serious error (completely obsolete statistics, missingJoin or selection conditions, and so on). Also, ensure that theOracle parameter OPTIMIZER_INDEX_CACHING has a value of 50 orlower.

o Nested loop joins are usually preferable to sort merge joins. Sortmerge joins are only useful if the expected resulting quantitiesare small, based on the selection conditions for both tables, and

09.03.2011 of 25


there is no good join condition.

o The access table of a nested loop join and a hash join must be thetable with the smaller resulting set.

15. What utilities are available for further analysis of aprocessing-intensive SQL statement?

The following examinations can be used for further analysis, dependingon the analyses in the shared cursor cache in ST04:

a) Actual values in bind variables

To decide what conditions really are selective (that is, whichsignificantly limit the resulting quantity), typical values must bedetermined in the bind variables. Use transaction ST05 to create anSQL trace that includes the execution of the statement. The tracemust be started on the instance on which the transaction isexecuted with the relevant statement. Selecting the statement andchoosing the "Replace Variables" button gives you a statement withthe actual values, instead of binding variables.

If no SQL trace is possible ad hoc, you can also go to the callingABAP program, because you are working in various places withliterals instead of ABAP variables, which displays the values usedin the plaintext.

b) Number of suitable table entries

You can use the actual values from an SQL statement to determinethe selectivity by selecting the number of table entries that matchthe conditions. This can happen - for the current client - usingtransaction SE16. To do this, enter the individual conditions forthe table and select the "Number of Entries" button.

At Oracle level, you can determine the number of suitable entriesusing the following command:

SELECT COUNT(*) FROM <table> WHERE <condition_list>;

The advantage of this method it that unlike SE16, it is not limitedto the current client.

Important: A large number of blocks are imported with thesemethods, which can increase the I/O load. Do not execute theseactions on a large scale when the system has a heavy load.

c) Column value distribution

Use Transaction DB05 for a rough overview of what and how oftenvalue combinations of one or several columns appear in the table.

DB05 does not state WHICH column combination appears how often. Inaddition, the frequency is only broken down by intervals (1-10,11-100, ...). broken down. The result of a DB05 analysis istherefore only of limited use.

It is particularly useful for columns with a limited number of

09.03.2011 of 25


value combinations to determine each of these combinationsincluding the exact number of occurrences. This can be done asfollows at Oracle level:

ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 128;SELECT <column1>, ..., <columnN>, COUNT(*) FROM <table>GROUP BY <column1>, ..., <columnN>;

The ALTER SESSION accelerates the subsequent SELECT (during a fulltable scan, the system then reads 128 instead of only 8 or 32blocks for each I/O request from the disk). Since the parameteraffects the cost accounting, it can only be set to 128 here, andnot generally.

As of Release 4.6C, you can use transaction TAANA to determine thevalue distribution of columns.

Important: A large number of blocks are imported with thesemethods, which can increase the I/O load. Do not execute theseactions on a large scale when the system has a heavy load.

d) Index fragmentation

To prevent a large number of block accesses being caused by afragmented index, check the index fragmentation as described inNote 771929.

e) Source of the SQL statement

You can use the report name and source code button in the sharedcursor cache to reach the callpoint in ABAP. If it is still notclear there in which context the source code will run, a where-usedlist can return further information.

Information on the triggering transaction can also be determined bycapturing a current query in SM66 or by a permanent SQL tracerestricted to the table using ST05.

If no report name is displayed in the shared cursor cache, thestatement is called from a tool or script outside the R/3 system.However, there is also a number of external tools that can beidentified with a separate name.

If branching using a source code button fails, you may havedynamically generated source code.

If several reports execute an identical statement, the report thatfirst executed the statement is always displayed.

You cannot branch to the source code for statements that originatedin the R/3 kernel.

f) Block accesses for a specific statement execution

To test alternative access plans or partial accesses of a join, SQLstatements can be executed at Oracle level. To ensure that it iseasy to find their key performance indicators (such as the numberof buffer gets and disk reads for each execution) in the shared

09.03.2011 of 25


cursor cache, you should add a unique and easily identifiablecomment to the statement to be executed:

SELECT /* <comment> */ ...

Now you can easily search for the statement by using the searchterm <comment> in the Shared Cursor Cache.

g) Access paths for varied SQL statements

In various cases it can be useful to determine the access path forvaried SQL statements, for instance:

- Effect of hints

- Effect of more or fewer OR concatenations or IN list entries

- Effect of changed Oracle parameters

- Determining the costs for partial statements

You can use the "Enter SQL statement" button to determine theaccess path for any statement in transaction ST05. However, you canonly change dynamic Oracle parameters with newer releases andSupport Packages.

16. How can I optimize a processing-intensive SQL statement?

Before carrying out technical tuning of a processing intensive SQLstatement, you should always check from the application view to whatextent the SQL statement is even useful. For example, if the SQLstatement is the result of an inadequately filled input template, apoorly-qualified SE16 SELECT (report /1BCDWB/DB*), an incorrect query(report AQ*) or a design error in a customer-specific development, thestatement must be optimized from a non-technical view or avoidedcompletely. If the statement comes from the standard SAP system, youcan check whether a general solution is already available during anSAP note search.

Below is a general overview of classes of expensive SQL statements andpossible technical tuning approaches:

o Accesses to objects of the Oracle DDIC (DBA views)

Queries on DBA views are often very processing-intensive. Forstatements on DBA views, it does not make sense to optimize theaccess by creating new indexes or specifying hints.

These accesses are usually caused by monitoring tools (transactionRZ20, BR*TOOLS, external monitoring tools). You must thereforefirst determine the initializing tool and check to what extent thequery can be completely deactivated or the frequency of theexecution can be reduced.

If long runtimes and a high number of BUFFER GETs occur duringDBA_SEGMENT access in particular, then refer to Note 871455.

o Accesses within the F4 search help (M_V* views)

09.03.2011 of 25


Search help runtimes may vary, depending on the selection criteria.Since all possible queries cannot be optimized to the same extent,it is important to limit query constellations to the main queryconstellations only. Furthermore, you can implement a &SUBSTITUTELITERALS& hint in accordance with Note 1008433. This hint partiallyresults in optimal accesses, even if there are no histograms.

o Large number of executions

If an SQL statement is processing intensive, primarily due to thelarge number of executions, you must check whether the number ofexecutions can be reduced in the application.

If the statement in question contains a FOR ALL ENTRIES expression,it may also be possible to increase the blocking factors asdescribed in Note 881083.

o Large number of processed rows for each execution

If an SQL statement reads a very large number of data records witheach execution, you must check in the application whether the scopeof the dataset can be reduced (for example, by specifying moreconditions in the WHERE part).

o Large number of buffer gets in each execution

Check if the use of resources by Oracle is optimal. Consider thefollowing issues:

- Does the Oracle Optimizer fail to choose the current bestpossible access path?

If a detailed analysis of the SQL statement means that Oracledoes not decide on the optimal access path, you should firstcheck whether the CBO statistics correctly reflect the currentdataset. To make sure, you can generate new statistics that areas accurate as possible (Note 588668). You should also ensurethat a statistical run is scheduled at least once a week asdescribed in Note 132861.

If the access path is corrected and no side effects occur, youcan also create statistics as described in Note 756335 ontables that do not usually receive any statistics as describedin 122718.

If the access path is also not optimal with good statistics,this is often due to "features" of the RBO and CBO, which aredescribed in Note 176754. In such cases you can improveOracle's access by using hints (see Note 130480) or byadjusting the statistic values (see Note 724545).

Additional reasons for an incorrect access path are the wrongOracle parameter settings or the use of an obsolete patch set.For more information, see Note 618868.

To understand the CBO decisions better, also refer to Note750631, which contains approximation formulas for the cost

09.03.2011 of 25


accounting.

Under certain conditions, the displayed access path is alsoincorrect because Oracle performs another access internally.For more information, see Note 723879.

- Can the access be optimized by creating an additional index?

If the existing selective conditions are not sufficientlysupported by an index, a corresponding index can be created forthe optimization or - if no side effects are possible - anexisting index can be adjusted.

Note that in the R/3 system, some central tables are accessedusing special index tables (Note 185530 for SD, Note 191492 forMM/WM, and Note 187906 for PP/PM). In the cases described, youshould open the initial screen using an index table, so that noother index must be created. The same applies for accesses onthe table BSEG, for the index tables such as BSIS, BSAS, BSID,and BSAD.

- Is the number of buffer gets inexplicably high?

If possible, roughly calculate how many blocks must be read forthe statement. A maximum of 5 block accesses are required perexecution for each INDEX UNIQUE SCAN (or, more specifically:BLEVEL of index + 1).

If a significantly greater number of blocks is read with INDEXRANGE SCANs that your estimates would lead you to expect, thismay be due to an index fragmentation. For more information, seeNote 771929.

If changes take place to data in the same block even as it isaccessed, the consistent reading mechanism and its associatedaccess to CR blocks ("Consistent Read") may lead tosignificantly increased block accesses. Refer to Note 913247for more detailed information.

Another possible reason for a large number of Buffer Gets is ahigh volume of network communication between the database andSAP. This effect is usually negligible, however.

When you use LIKE conditions, a placeholder at the beginning orin the middle of the search template may cause a greater numberof buffer gets, since Oracle can only optimally useplaceholders that are at the end of the search term.

If you execute mass-data operations ("FROM TABLE<internal_table>" expressions in the ABAP statement), theunderlying SQL statement is executed for each data record of<internal_table>, while the system only records a singleexecution in the shared cursor cache. This causes anunnecessarily high number of block accesses for each execution.

In the case of parallel DML operations with a low COMMITfrequency on tables in ASSM tablespaces, the problems that aredescribed in Notes 896717 and 1037755 may be responsible for a

09.03.2011 of 25


high number of buffer gets.

If some INSERTs fail due to duplicate keys (ORA-00001), thesystem executes a recursive access to determine the constraintname, which in turn executes some buffer gets (for example, 15)that are added to the INSERT.

If a table contains more than 255 columns, for technicalreasons, Oracle splits the records into fragments that eachcontain 255 columns or fewer ("Intra Block Chaining"). If thesystem accesses columns with a COLUMN_ID greater than 255, anadditional buffer get is required. For columns with a COLUMN_IDgreater than 511, two additional buffer gets are required, andso on.

If the query contains a sorting such as ORDER BY, the systemmay have to sort a large data volume before it returns thefirst data records based on this sorting. Therefore, a largenumber of buffer gets may be required, even if the systemeventually transfers only few data records. In these cases, anindex that supports sorting or the prevention of ORDER BY cansolve the problem.

In individual cases, the high number of buffer gets is causedby the actual run schedule differing from the displayed runschedule. This can be checked as described in Note 723879.

o Large number of disk reads per execution

A high number of disk reads is often associated with full tablescans, because the blocks imported during a full table scan aresuppressed more quickly than blocks that were read during an indexaccess. In such a case, you must use appropriate measures to forcean index access (create a new index, adjust the statistics, hints,...).

A large number of disk reads often occurs in connection with indexrange scans and subsequent table accesses with ROWID if theclustering factor of the index is high. For more information, seeNote 832343.

To avoid blocks of a critical table being suppressed from thebuffer pool after some time, and later being read again from disk,you can set up a keep pool as described in Note 762808.

If the application needs to read a lot, or all, of the entries of atable repeatedly, and the table is not too large, considerbuffering the table completely in the SAP system. You must ensurethat the delayed SAP buffer synchronization will cause no problemsfor the application, and that the table's change rate is not toohigh.

Otherwise the same applies during the analysis of statements with ahigh number of disk reads as for statements with a high number ofbuffer gets.

o High CPU-time

09.03.2011 of 25


The usual cause of high CPU load is a large number of BUFFER GETs.Note 712624 contains analysis options and more characteristicreasons for increased CPU consumption.

o High Elapsed time

In many cases, a high elapsed time that cannot be explained by DISKREADs or BUFFER GETs is caused by lock situations, especiallyenqueues (refer to Note 745639). If necessary, you can use ORADEBUG(refer to Note 613872) to create a trace of the statement inquestion to obtain more information.

o Large number of direct write operations

Statements with a high number of direct write operations can beanalyzed as follows (depending on the triggering operation):

- (Sorting: Check whether the statement must sort anunnecessarily large amount of data, or if the sorting can becompletely avoided [for example, by choosing an index thatsupports the sorting sequence).

- LOB accesses: Check whether it would be useful to cache LOBdata, as described in Note 563359.

- Parallel query: Check whether the relevant SQL statement can beprocessed usefully without a parallel query (see Note 651060).

- Hash operations: Check whether the data scope of a hashoperation can be reduced, or if the hash operation can becompletely avoided (for example, by avoiding an undesirablehash join).

- Bitmap operations: Check whether a bitmap operation (forexample star transformation) can be usefully optimized.

17. What must be considered in terms of the index design?

For more information, see Note 912620.

18. What must I take into consideration with regard to the query design?

To ensure optimal SQL performance, you must take the following rulesinto account when you create an SQL statement:

o Ensure that the selective conditions in the WHERE clause aresupported by an index.

o Select as few columns as possible.

o Check whether it is sufficient to use only the data existing in anindex for a query so that a table access becomes obsolete.

o Avoid using not-equal conditions because these cannot be analyzedoptimally by the cost-based optimizer (CBO). Use range conditionsinstead (if possible).

09.03.2011 of 25


o Specify as many selective conditions as possible.

o Avoid using unnecessary conditions that are not selective (such as"GJAHR" BETWEEN '0000' and '9999') because these are confusing forthe cost-based optimizer (CBO).

o Avoid using unrequired range conditions because these can only beanalyzed in a limited way by the cost-based optimizer (CBO) (forexample, "GJAHR" BETWEEN '2010' AND '2010' --> better: "GJAHR" ='2010').

19. What analysis tools are available at Oracle level?

For various reasons, an SQL analysis may be useful at database leveldirectly (for example, if no R/3 functions are available in aJava-only environment). Therefore, useful Oracle functions for SQLanalysis are described below:

o Creating an SQL trace

You can use ORADEBUG (Note 613872) or DBMS_SYSTEM (Note 602667) tocreate an SQL trace of any session.

As of Oracle 10g, you can use the AUDIT command to activate anenhanced auditing of SQL statements in accordance with Note1128663.

o Displaying an Explain plan

With the following command, the run schedule is displayed for allsubsequent SQL statements, instead of them being executed:

SET AUTOTRACE TRACEONLY EXPLAIN

You can deactivate this mode with:

SET AUTOTRACE OFF

If you want to create an Explain for a statement with bindvariables, the above approach does not work. You can use thefollowing option instead:

EXPLAIN PLAN FOR <sql_statement>;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

o Display block accesses of an SQL statement

Among other things, the following command counts the Disk Reads andBuffer Gets of the subsequent SQL statements (without the actualresult of the query being returned):

SET AUTOTRACE TRACEONLY

You can deactivate this mode with:

SET AUTOTRACE OFF

09.03.2011 of 25


o PLAN statistics (Oracle >=9i)

As of Oracle 9i you can collect detailed PLAN statistics such asprocessed records, buffer gets, disk reads or required time. Sincethe collection of these extensive statistics has a significanteffect on database performance, this option is normallydeactivated. You can use SQLPLUS to activate and evaluate thesePLAN statistics on session level for individual tests. To do this,proceed as follows:

- Activating the PLAN statistics:

ALTER SESSION SET STATISTICS_LEVEL=ALL;

- Executing the relevant SQL statements

- Query from V$SQL_PLAN_STATISTICS_ALL

SELECT SUBSTR(LPAD(' ', PS.DEPTH) || OPERATION || ' ' || OPTIONS || DECODE(OBJECT_NAME, NULL, NULL, ' (' || OBJECT_NAME || ')'), 1, 40) ACTION, PS.COST, PS.CARDINALITY, PS.LAST_OUTPUT_ROWS "ROWS_PROC", PS.LAST_CR_BUFFER_GETS + PS.LAST_CU_BUFFER_GETS BUFFER_GETS, PS.LAST_DISK_READS DISK_READS, PS.LAST_ELAPSED_TIME TIMEFROM V$SQL_PLAN_STATISTICS_ALL PS, V$SQL S,WHERE S.SQL_TEXT LIKE '<sql_statement_pattern>' AND PS.ADDRESS=S.ADDRESS AND PS.CHILD_NUMBER = S.CHILD_NUMBERORDER BY PS.CHILD_NUMBER, PS.ID;

The columns COST and CARDINALITY contain the CBO estimates,while the columns ROWS_PROC, BUFFER_GETS, DISK_READS and TIME(in microseconds) display the actual statistics of the lastexecution.

- Deactivating the PLAN statistics:

ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;

o PLAN statistics (Oracle >= 10g)

As of Oracle 10g, you can also use the GATHER_PLAN_STATISTICS hint:

- Execute the SQL statement using the GATHER_PLAN_STATISTICShint:

SELECT /*+ GATHER_PLAN_STATISTICS */ ...

09.03.2011 of 25


- Indicate the execution plan using the following command:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS'));

The records and the time for each execution step are containedin the columns "A-Rows" and "A-Time".

o Sample data

In some situations, it is useful to get an impression of the datacomposition without having to scan the entire table or withouthaving to rely on how representative the first records of a tableare. You can use the following command to randomly select datarecords:

SELECT * FROM <table> SAMPLE (<percent>);

20. What information do the "Estimated Costs" and "Estimated Rows" providefor an SQL statement?

An EXPLAIN displays "Estimated Costs" and "Estimated Rows", which aresimply the CBO's calculation results (refer to Note 7550631). Sincethese results are based upon a number of assumptions (column valuesare distributed equally, statistics), and depend upon the databaseparameter settings, the calculated costs and rows are useful onlywithin a margin for error. High "Estimated Costs" and "Estimated Rows"are therefore neither a satisfactory nor a necessary indication of anexpensive SQL statement. Also, the calculated costs have no actualeffect upon the performance - the deciding costs are always the actualones, in the form of BUFFER GETs, DISK READs, or processed rows.

21. Is it possible to determine a buffer quality for individual tables?

The Oracle Buffer Quality (see Note 618868 for more information) is aglobal statistic of the database. Since individual SQL statements canstrongly affect this global value (both positively and negatively), atable-specific buffer quality is desirable. You can use the followingSQL statement (based on V$SEGMENT_STATISTICS) to determine the 20tables that have the poorest buffer quality (this query only considerstables with at least 10,000 buffer gets):

SELECT * FROM(SELECT S1.OBJECT_NAME OBJECT, S1.VALUE LOGICAL_READS, S2.VALUE PHYSICAL_READS, ROUND((S1.VALUE - S2.VALUE) / S1.VALUE * 100, 2) "QUALITY (%)" FROM V$SEGMENT_STATISTICS S1, V$SEGMENT_STATISTICS S2 WHERE S1.OBJ# = S2.OBJ# AND S1.VALUE > 10000 AND S1.STATISTIC_NAME = 'logical reads' AND S2.STATISTIC_NAME = 'physical reads' ORDER BY 4 ASC)

09.03.2011 of 25


WHERE ROWNUM <=20;

22. What happens when there is an overflow of the Buffer Get?

For Oracle Release 9i or lower, if the SQL statement executes morethan two billion Buffer Gets, the value overflows in the Shared CursorCache in the negative area. For Oracle Release 10g, the limit is fourbillion. When the system reaches this limit, the count begins at 0again. In both cases, the result is that the system can no longerimmediately recognize a large number of Buffer Gets. In addition, inmany cases, the Shared Cursor Cache analysis ignores statements withnegative Buffer Gets values, and as a result, the most expensive SQLstatements regarding Buffer Gets are not even displayed. To recognizethe affected SQL statements despite the overflow, you can select thefollowing options:

o Check whether there are accesses with a negative number of BufferGets:

SELECT * FROM V$SQL WHERE BUFFER_GETS < 0;

o Check the plausibility of the data - if the system displays anumber of Buffer Gets that is significantly lower than is possible,an overflow may be responsible.

o When you use Oracle 10g, also consider the AWR data fromDBA_HIST_SQLSTAT that was aggregated on the basis of Snapshot,since overflows between two snapshots (generally found in one-hourintervals) are improbable.

23. How can I determine when a complex access is made in the executionplan?

We are unable to provide a general answer to this question. However,the following individual approaches are available:

o As of Oracle 10g, the Active Session History(V$ACTIVE_SESSION_HISTORY) can provide very detailed informationabout a session's current activities (including the segmentcurrently being accessed).

o If the session executes read I/O accesses such as "db filesequential read" or "db file scattered read", you can use the eventparameters P1 (file number) and P2 (block number) to determine thesegment currently being accessed (see Note 619188).

o If CPU usage is high, you can use V$SEGMENT_STATISTICS to determinewhether the number of buffer gets for individual objects involvedin the join increases significantly. However, note thatV$SEGMENT_STATISTICS contains global data and does not solely referto the long-running program.

o If the session executes extensive sort operations and hashoperations, you can use V$SQL_WORKAREA_ACTIVE to determine whichsteps in the access plan currently require the PGA memory andPSAPTEMP tablespace, and to what extent.

o For certain activities such as full table scans and sorting

09.03.2011 of 25


activities, you can also use V$SESSION_LONGOPS to determine thecurrent activity.

24. Can I see a sample analysis?

The following SQL statement guarantees several thousand disk reads andbuffer gets per execution, although hardly any entries are returned:

SELECT *FROM "LTAP" T_00 , "LTAK" T_01WHERE ( T_01 . "MANDT" = :A0 AND T_01 . "LGNUM" = T_00 . "LGNUM" AND T_01 . "TANUM" = T_00 . "TANUM" ) AND T_00 . "MANDT" = :A1 AND T_00 . "LGNUM" = :A2 AND T_00 . "PQUIT" = :A3

The execution path contains a sort merge join:

SELECT STATEMENT ( Estimated Costs = 1,712 , Estimated #Rows = 52,660) MERGE JOIN TABLE ACCESS BY INDEX ROWID LTAP INDEX RANGE SCAN LTAP~0 SORT JOIN TABLE ACCESS BY INDEX ROWID LTAK INDEX RANGE SCAN LTAK~R

A nested loop join is most likely preferable to a sort merge join.Because - leaving aside the unselective MANDT - there are onlyspecifications for LTAP selection conditions in the WHERE part (LGNUM,PQUIT columns), you can check whether LTAP is suitable as an accesstable for a nested loop join. A suitable index already exists:

NONUNIQUE Index LTAP~M

Column Name #Distinct

MANDT 1LGNUM 7PQUIT 2MATNR 24.280

As you can see in the #Distinct column, neither LGNUM nor PQUIT havemany different values. However, this information does not yet allowany direct conclusions to be drawn on the selectivity of theconditions.

Even without an SQL trace, in this case you can already read the PQUITvalue ' ' from the ABAP source code:

SELECT LTAP~LGNUM LTAP~TANUM LTAP~TAPOS LTAP~VLTYP LTAP~NLTYP LTAK~BWLVS LTAK~BDATU LTAK~BZEIT INTO CORRESPONDING FIELDS OF TABLE IT_LTAP FROM LTAP JOIN LTAK ON LTAK~LGNUM = LTAP~LGNUM AND

09.03.2011 of 25


LTAK~TANUM = LTAP~TANUM WHERE LTAP~LGNUM = P_LGNUM AND LTAP~PQUIT = ' '.

Now you can determine the exact value distribution for LGNUM and PQUITusing a GROUP BY:

SELECT LGNUM, PQUIT, COUNT(*) FROM LTAP GROUP BY LGNUM, PQUIT;

LGN P COUNT(*)--- - ----------I01 X 10I02 5I02 X 33955I03 X 3088P01 164P01 X 81941S01 2S01 X 67807S02 10S02 X 3201W01 33W01 X 139158

You can tell that only very few entries ever exist for the usedcondition PQUIT = ' '. The condition is therefore very selective,although PQUIT only assumes two different values. A nested loop joinwith access through LTAP is therefore far preferable to a sort mergejoin.

The following options are available to persuade Oracle to use a nestedloop join:

o Specification of a hint (see Note 772497)

o Sufficiently increase the statistics value for the distinct valuesof PQUIT, to make access using the LTAP~M index more attractive forOracle (Note 724545).

One solution within the application is to split the join into twoseparate statements as described in Note 187935.

As an alternative, you can also use a SUBSTITUTE_LITERALS hint andhistograms (refer to Note 811852).

25. Is the ST05 data always reliable?

In a number of situations, the measured values may not be correct intransaction ST05. For example:

o Some operating system may not measure times on the basis ofmicroseconds. Due to an incorrect measurement of time in TRU64, forexample, all time data may be a multiple of 976 ms, as 976 ms(1000000 / 1024 ms) is the most precise time information possible.

o Check whether there are problems with time measurement in thesystem (for example, AMD x64 -> Notes 1036194, 1047734). If indoubt, you can check the plausibility of high relative time data by

09.03.2011 of 25


comparing it to the absolute time data in the extended trace. Onlyif the absolute times increase at least at the same rate are thestatement-specific relative runtimes correct.

o The system may assign incorrect statements to runtimes and it maynot display other statements at all if the SAP kernel mixes up dataduring the measurement of time. This may occur in particular whenthe system writes messages of the following type in the workprocess trace file (dev_w<number>) at the same time:

*** WARNING ==> TrStackPeek: record 1 requested, which is notfilled in stack (0)

In such a case, log a customer message with SAP, so the cause forthis problem can be analyzed.

o SQL accesses may not be logged at all if there are problems at adeeper level. It is also worth looking at the relevant work processtrace file. The following error indicates that SQL trace recordscould not be written successfully:

M *** ERROR => TrWriteEntry: TrBuffAddRec failed with rc=5 (NoMemory) (253 bytes) [trfile.c 1342]

26. Where can I find further information on SQL optimization?

In addition to covering general performance topics, the final sectionof Note 618868 contains references to books, SAP training and SAPservices that have the SQL optimization as a central topic.

Header Data

Release Status: Released for CustomerReleased on: 15.07.2009 13:51:59Master Language: GermanPriority: Recommendations/additional infoCategory: FAQPrimary Component: BC-DB-ORA Oracle

Secondary Components:SV-BO Backoffice Service Delivery

The Note is release-independent

Related Notes

09.03.2011 of 25


Number Short Text

1128663 Oracle 10g: SQL trace using auditing

1100926 FAQ: Network performance

1093107 Costly SQL statement due to synchronous matchcodes

1037755 Performance problems with ASSM tablespaces

913247 Performance problems due to open changes

912620 FAQ: Oracle indexes

896717 Problems with tables in ASSM tablespaces

881083 Blocking factors on Oracle-based systems

853576 Oracle 10g: Performance analysis w/ ASH and Oracle Advisors

832343 FAQ: Clustering factor

825653 Oracle: Common misconceptions

806554 FAQ: I/O-intensive database operations

797629 FAQ: Oracle histograms

793113 FAQ: Oracle I/O configuration

772497 FAQ: Oracle Hints

771929 FAQ: Index fragmentation

767414 FAQ: Oracle latches

762808 FAQ: Keep pool

756335 Statistics in tables w/ heavily fluctuating volumes of data

750631 Approximations for cost calculation of the CBO

745639 FAQ: Oracle enqueues

724545 Adjusting the CBO statistics manually using DBMS_STATS

723879 Explain: System displays an incorrect access path

712624 High CPU consumption by Oracle

706478 Preventing Basis tables from increasing considerably

651060 FAQ: Oracle Parallel Execution

619188 FAQ: Oracle wait events

618868 FAQ: Oracle performance

613872 Oracle traces with ORADEBUG

602667 Perform a SQL Trace on an oracle utility or external program

588668 FAQ: Database statistics

563359 Performance optimization for tables with LOB columns

502782 Composite SAP Note ora-4030

354080 Note collection for Oracle performance problems

329914 Expensive SQL statements composite note

191492 Performance: Customer developments in MM/WM

187906 Performance: Customer developments in PP and PM

185530 Performance: Customer developments in SD

176754 Problems with CBO and RBO

135048 Long runtimes statements w/ ROWNUM <= condition

132861 CBO: Statistics creation with SAPDBA or BRCONNECT

131372 CBO: Tuning of expensive SQL statements

130480 Database Hints in Open SQL for Oracle

122718 CBO: Tables with special treatment

48230 Parameters for the SELECT ... FOR ALL ENTRIES statement

09.03.2011 of 25


Number Short Text

3807 Error messages regarding rollback and undo segments

sapnote 0000766349 sql tunnig

Documents