Oracle SQL optimization
Oracle SQL optimization[@more@]
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 currently running?
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 in the ABAP source code?
9. How many processing-intensive SQL statements should be observed as part 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 a processing-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 analysis tools are available at Oracle level?
19. What information do the "Estimated Costs" and "Estimated Rows" provide for an SQL statement?
20. Is it possible to determine a buffer quality for individual tables?
21. What happens when there is an overflow of the Buffer Gets?
22. Can I see a sample analysis?
23. Where can I find further information on SQL optimization?
Frequently asked questions Solution
1. What is SQL optimization?
SQL optimization (or SQL statement tuning or Shared Cursor cache analysis) refers to the identification, analysis and optimization of SQL statements that are responsible for the highest load in relation to I/O and CPU consumption on database level. These statements are also called "processing-intensive SQL statements".
Due to the complexity of the topic, this note can only offer an outline description of an analysis. However, SAP offers various books, training courses and services on the subject (see also the final question in this note).
2. How important is SQL optimization?
Regular analysis and optimization of processing-intensive SQL statements is the MOST important basis for efficient system operation. Resource-intensive SQL statements are directly responsible for increased I/O and CPU activities and indirectly responsible for subsequent problems such as buffer busy waits or unusually high I/O times.
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:
- Number of disk reads (or physical reads)
- Number of blocks that were not in the memory of the Oracle buffer and therefore had to be imported from the disk
- Measure of the I/O load of an SQL statement
- 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 database performance:
- Number of processed rows
- Number of table entries returned by an SQL statement
- Measurement for the network load of an SQL statement
- Number of executions
- Measurement for the communication load with the database
- CPU-Time
- Measurement of the CPU load of an SQL statement
- Elapsed-Time
- Measurement of the actual duration of an SQL statement
- Number of direct writes (for Oracle >=10g)
- Measurement for PSAPTEMPT and direct path write activities (for example 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 executed often, causing only a small load each time or whether it is executed just once causing a large load. It is the total load triggered by the statement 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 specific values. Nevertheless, all statements that have only varying values are considered 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 Shared Cursor Cache, which you can access through an SAP system by selecting
Transaction ST04
-> Detail Analysis Menu
-> SQL Request
-> Detail Analysis Menu
-> SQL Request
This cache includes the following information for all SQL statements executed since the last database start that were not displaced again from the shared cursor cache:
- Executions: Number of executions
- Disk reads: Number of blocks read from the disk
- Disk reads/execution: Number of blocks read by the disk per execution
- Buffer gets: Number of blocks read from the buffer pool
- Buffer gets/execution: Number of blocks read per execution from the pool buffer
- Rows processed: Number of processed rows
- Rows processed/execution: Number of rows processed for each execution
- Buffer gets/Row: Number of blocks read per processed row from the buffer pool
- CPU Time: Consumed CPU time (SAP basis >= 6.40)
- Elapsed Time: Duration of the execution (SAP basis >= 6.40)
- SQL statement: Text of the SQL statement
- Program name: Name of the calling program
- "Callpoint in the ABAP program" button: Exact location in the ABAP source code when the statement call originates
There are also buttons to define a reset time and start an evaluation since 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 processing intensive in terms of one of the three load criteria by sorting the Shared Cursor Cache entries into disk reads, buffer gets or processed rows. It is best to sort according to the three criteria one after the other and to optimize the statements with the highest load in each case.
To automatically determine the most processing-intensive statements, you can also use the RSORADLD report (or /SDF/RSORADLD), that lists all statements by default that are responsible for more than 2% of the disk reads or 5% of the buffer gets.
The SAP Early Watch Alert Services also gives you an overview of the most processing-intensive SQL statements.
6. How do I find information on an SQL statement that is currently running?
If a work process is busy accessing a table in SM50/SM66 for an extended period, you can determine the relevant Oracle session using the Client-PID as described in Note 618868.
To obtain details of the block accesses, you can determine this statement 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 that are transferred 1:1 to the database. Therefore an examination of the ABAP source code will provide information about the values transferred to the database.
Otherwise, up to Oracle 9i you can only determine the content of the bind variables by taking measures such as the following BEFORE executing an SQL statement:
- Activate an SQL trace using Transaction ST05
- J2EE Environment: Activate an SQL trace using a web browser URL () or using SAP J2EE Engine Visual Administrator
- Activate an ORADEBUG trace (Note 613872)
- Debug the ABAP program that launches the SQL statement
No further information about the contents of the bind variables can be obtained until 9i.
As of Oracle 10g, the view V$SQL_BIND_CAPTURE is available, which stores the contents of bind variables for the executed SQL statements. With this information, you can use the following query to determine the 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 S
WHERE
S.SQL_TEXT LIKE '' AND
S.SQL_ID = SBC.SQL_ID
GROUP BY NAME, VALUE_STRING
ORDER BY 1, 2;
SELECT
SUBSTR(SBC.NAME, 1, 10) BIND,
SUBSTR(SBC.VALUE_STRING, 1, 50) VALUE,
COUNT(*) "NUMBER"
FROM
V$SQL_BIND_CAPTURE SBC, V$SQL S
WHERE
S.SQL_TEXT LIKE '
S.SQL_ID = SBC.SQL_ID
GROUP BY NAME, VALUE_STRING
ORDER BY 1, 2;
The bind variables are updated in V$SQL_BIND_CAPTURE every 15 minutes at the earliest. If you require a faster refresh for analysis purposes, you can temporarily set the underlying underscore parameter _CURSOR_BIND_CAPTURE_INTERVAL to a second value than 900 (that is, the default setting of 15 minutes):
ALTER SYSTEM SET "_CURSOR_BIND_CAPTURE_INTERVAL"=;
ALTER SYSTEM SET "_CURSOR_BIND_CAPTURE_INTERVAL"=
By default, only the first 400 byte of the bind variable content of an SQL statement are saved. For statements with a lot of bind variables, this may mean that the values of the last bind variables are not captured. If you require further variable content beyond 400 byte, you can set the _CURSOR_BIND_CAPTURE_AREA_SIZE parameter to a value of more than 400, for example:ALTER SYSTEM SET "_CURSOR_BIND_CAPTURE_AREA_SIZE"=;
8. Why does the SQL statement appear different on database level than in the ABAP source code?
The Open-SQL statements executed from R/3 are transferred to the database via the database interface (DBI). In many cases, the statement is modified in the DBI before being transferred to the database:
- If a column with an empty variable is compared in the WHERE section, the DBI omits this condition.
- When you use FOR ALL ENTRIES, the program distributes a value list depending on the DBI parameters described in Note 48230 (particularly rsdb/max_blocking_factor, rsdb/max_in_blocking_factor) into statements with short IN lists or OR linkages.
Note that RSPARAM always displays a value of -1 for these parameters when you use the default values. You can determine the value actually in use by referring to the dev_w* workprocess trace (Transaction ST11).
If the FOR ALL ENTRIES list is empty, all data is generally selected from the current client ("SELECT ... FROM WHERE MANDT = :A0"). All conditions in the WHERE part are ignored.
相關文章
- Oracle SQL optimization-2(zt)OracleSQL
- SQL Server OptimizationSQLServer
- SQLite Learning、SQL Query Optimization In Multiple RuleSQLite
- Website Performance OptimizationWebORM
- Spark SQL原始碼解析(四)Optimization和Physical Planning階段解析SparkSQL原始碼
- Trust Region Policy OptimizationRust
- C++ Empty Class OptimizationC++
- 【sql調優之執行計劃】使用hint(一)Hints for Optimization Approaches and GoalsSQLAPPGo
- 【OPTIMIZATION】Oracle影響優化器選擇的相關技術Oracle優化
- Database | 淺談Query Optimization (1)Database
- Database | 淺談Query Optimization (2)Database
- Memory-Efficient Adaptive OptimizationAPT
- 【SQL】Oracle SQL monitorSQLOracle
- SQL in ORACLE and SQL ServerSQLOracleServer
- 【AP】a pratical guide to robust optimization(1)GUIIDE
- Communication Complexity of Convex Optimization
- Perceptron, Support Vector Machine and Dual Optimization Problem (2)Mac
- Perceptron, Support Vector Machine and Dual Optimization Problem (3)Mac
- Perceptron, Support Vector Machine and Dual Optimization Problem (1)Mac
- Join Query Optimization with Deep Reinforcement Learning AlgorithmsGo
- Availability and Optimization of Free Space in a Data Block(五)AIBloC
- Optimization with Function-Based Indexes (201)FunctionIndex
- ORACLE TOP SQLOracleSQL
- Oracle樹 sqlOracleSQL
- Oracle 常用SQLOracleSQL
- oracle SQL with 子句OracleSQL
- Oracle SQL JOINOracleSQL
- first oracle sqlOracleSQL
- Oracle PL/SQLOracleSQL
- 【Oracle】--PL/SQL匯入Oracle sql指令碼"傻瓜教程"OracleSQL指令碼
- [Oracle]Oracle良性SQL建議OracleSQL
- 【SQL】Oracle SQL處理的流程SQLOracle
- 【SQL】Oracle SQL共享池檢查SQLOracle
- ORACLE SQL and SQL*PLUS (strong recommend)OracleSQL
- Oracle SQL處理OracleSQL
- Oracle SQL Model ClauseOracleSQL
- [ORACLE] SQL執行OracleSQL
- ORACLE SQL PROFILE使用OracleSQL