===========================================================
PRAGMA SERIALLY_REUSABLE
===========================================================
PRAGMA SERIALLY_REUSABLE 查看全文
===========================================================
INSTEAD OF(zt)
===========================================================
===========================================================
操作CLOB(zt)
===========================================================
动态PL/SQL,对CLOB字段操作可传递表名table_name，表的唯一标志字段名field_id，clob字段名field_name，记录号v_id，开始处理字符的位置v_pos，传入的字符串变量v_clob 查看全文
===========================================================
Oracle's Parallel Execution Features(zt)
===========================================================
Using Oracle's Parallel Execution Features | |
===========================================================
Oracle SQL optimization-2(zt)
===========================================================
Oracle SQL optimization(摘自SAP Note 766349) 查看全文
===========================================================
Oracle SQL optimization(zt)
===========================================================
Oracle SQL optimization(摘自SAP Note 766349)
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 (http://<hostname>:<port>/SQLTrace) 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 '<sql_statement_pattern>' 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 '<sql_statement_pattern>' AND
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"=<seconds>;
ALTER SYSTEM SET "_CURSOR_BIND_CAPTURE_INTERVAL"=<seconds>;
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 <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 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 <table> WHERE MANDT = :A0"). All conditions in the WHERE part are ignored.
- Accesses to R/3 pool and cluster tables (that are not available as independent tables at an Oracle level) are converted into accesses on the related table pools or table clusters.
- If tables in R/3 are completely or generically buffered, the buffers 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 be completely different to the statement from the ABAP source code.
- The DBI provides ABAP statements that have "SELECT SINGLE" or "UP TO ROWS" without ORDER BY with the FIRST_ROWS hint and "WHERE ROWNUM <= ..." (Note 135048).
- Some operations (for example, kernel procedures, bulk operations, generations) can generate SQL statements although there is no regular SQL statement in the ABAP source code.
- Even when you use conversion exits, there can be significant differences between ABAP source code and database-side statements (such as additional conditions that are not explicitly present in the ABAP source code).
- IN conditions from the ABAP source code may be converted into any number of possible conditions on the database level, depending on your selection criteria: "=", "IN", "LIKE", "BETWEEN", ">", "<", ">=", "<="
- Columns that appear in both the selection list and the WHERE condition are removed from the selection list if it is clear from the WHERE condition what the column's value must be.
- If an expression ends with a space followed by a placeholder, the system generates an OR connection as follows:
SQL statement: ... WHERE <column> LIKE '<string> %'
Statement after DBI transformation: ... WHERE (<column> LIKE '<string> %' OR <column> LIKE '<string>')
9. How many processing-intensive SQL statements should be observed as part of SQL optimization?
There is no single, general response to this question. The answer depends primarily on the corresponding rows of optimization and the current system status. Therefore, in one case, optimization of a single highly processing-intensive SQL statement can ensure that a previously barely usable system function again, while in another case, more than 50 statements must be optimized to achieve the required improvement in performance.
Indicators regarding whether a system is already well tuned or not are outlined in Note 618868.
10. What are selection and join conditions?
During a selection condition, a column is compared with one or several specific values (e.g. "MANDT = :A0", "BDAT > '20050821'"). In join 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. Only when you access the inner table of a nested loop join (see below) can the join conditions also be used.
Selection and join conditions are specified in the WHERE part of the SQL statement and - in the case of views - are also specified within the view definition in Transaction SE11. These view conditions MUST NOT be overlooked when analyzing SQL statements.
11. What is a run schedule?
A run schedule (or explain, access path) shows how Oracle accesses the requested 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 determined using the Oracle Explain function. As of Oracle 9i it is also buffered in the V$SQL_PLAN performance view.
From the shared cursor cache overview, you can go to the execution plan using the Explain button or by clicking on the SQL statement and selecting the Explain button on the dialog box.
Whether the explains displayed in R/3 are based on V$SQL_PLAN or the previous explain depends on the R/3 Release and Support Package version. If the display of the R/3 explain does not contain any further information, the normal Oracle explain is used. If you find explicit information concerning V$SQL_PLAN, the data comes from this view.
13. What are the characteristic components of a run schedule?
- TABLE ACCESS FULL
During a full table scan, all table blocks up to the high water mark (last ever filled block) are read
- INDEX RANGE SCAN
Depending on the selection conditions, a subarea of an index is read
- INDEX UNIQUE SCAN
In the selection conditions, all key fields of a primary index are specified with "=" so that no more than one entry of the index is returned.
- INDEX FULL SCAN
The entire index tree is read from left to right (-> sorted result)
- 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 fater than INDEX FULL SCAN but the result is not sorted)
- INDEX SKIP SCAN (as of Oracle 9i)
This is comparable with several INDEX RANGE SCANs and also takes into account index fields behind an index field that is not specified in the selection conditions or is not specified with "=".
- TABLE ACCESS BY INDEX ROWID
Read the complete data record of the table based on the ROWID that was determined with a preceding index access
- NESTED LOOPS
Two tables are joined, where the system is accessed using the table in the run schedule directly under NESTED LOOPS and the second table is accessed with all of the entries that are returned by this table.
- SORT JOIN / MERGE JOIN
With the sort merge join, the relevant entries are read in parallel from both tables. The two resulting quantities are then sorted and finally combined for the final resulting quantity.
- MERGE JOIN CARTESIAN / BUFFER SORT
A Cartesian MERGE JOIN (which often appears in relation to a BUFFER SORT) generates the Cartesian product of the two resulting quantities. If both of the resulting quantities have x and y elements, the Cartesian product result contains x * y records.
- HASH JOIN
A hash table is set up from the access table (the table directly under HASH JOIN in the run schedule). The entries of the second table are then checked on this hash table. If the join conditions are fulfilled, the data record will be returned.
- 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 indexes using a hash join, before the table needs to be accessed. Index joins are especially useful if there are several indexes that are moderately selective, but that are very selective if used in combination.
14. What is an optimal run schedule?
An optimal run schedule only needs a minimum of blocks to be read. The following is especially important for optimal data access:
- In most cases, an index access is better than a full table scan. A full table scan is mainly useful when a large portion of the table entries must be read.
- Otherwise, you must ensure that the selective conditions are supported by an index. The greater the selectivity of a condition, the more the resulting quantity will be restricted. Note: there is no direct relationship between the selectivity and the number of different values of a column. For example, if a column only ever contains the default value ' ' (and has therefore only a single value), but in the selection conditions you are searching using a value other than ' ', the condition is very selective. On the other hand, if a column contains 1,000,000 different values, but 90% of the entries have the value '000000000000000', a comparison of the column using '000000000000000' will be very unselective.
Also refer to the criteria specified below for the index design.
- Hash joins are deactivated in the standard R/3 system (HASH_JOIN_ENABLE is set to FALSE). They are only really used effectively in BW environments.
- Merge Join Cartesians should hardly ever occur. If they do, there is likely a serious error (completely obsolete statistics, missing Join or selection conditions, and so on). Also, ensure that the Oracle parameter OPTIMIZER_INDEX_CACHING has a value of 50 or lower.
- Nested loop joins are usually preferable to sort merge joins. Sort merge joins are only useful if the expected resulting quantities are small, based on the selection conditions for both tables, and there is no good join condition.
- The access table of a nested loop join and a hash join must be the table with the smaller resulting set.
15. What utilities are available for further analysis of a processing-intensive SQL statement?
The following examinations can be used for further analysis, depending on the analyses in the shared cursor cache in ST04:
a) Actual values in bind variables
To decide what conditions really are selective (that is, which significantly limit the resulting quantity), typical values must be determined in the bind variables. Use transaction ST05 to create an SQL trace that includes the execution of the statement. The trace must be started on the instance on which the transaction is executed with the relevant statement. Selecting the statement and choosing the "Replace Variables" button gives you a statement with the actual values, instead of binding variables.
If no SQL trace is possible ad hoc, you can also go to the calling ABAP program, because you are working in various places with literals instead of ABAP variables, which displays the values used in the plaintext.
b) Number of suitable table entries
You can use the actual values from an SQL statement to determine the selectivity by selecting the number of table entries that match the conditions. This can happen - for the current client - using transaction SE16. To do this, enter the individual conditions for the table and select the "Number of Entries" button.
At Oracle level, you can determine the number of suitable entries using the following command:
SELECT COUNT(*) FROM <table> WHERE <condition_list>;
SELECT COUNT(*) FROM <table> WHERE <condition_list>;
The advantage of this method it that unlike SE16, it is not limited to the current client.
Caution: A large number of blocks are imported with these methods, which can increase the I/O load. Do not execute these actions 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 often value combinations of one or several columns appear in the table.
DB05 does not state WHICH column combination appears how often. In addition, the frequency is only broken down by intervals (1-10, 11-100, ...). broken down. The result of a DB05 analysis is therefore only of limited use.
It is particularly useful for columns with a limited number of value combinations to determine each of these combinations including the exact number of occurrences. This can be done as follows at Oracle level:
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT = 128;
SELECT <column1>, ..., <columnN>, COUNT(*) FROM <table>
GROUP BY <column1>, ..., <columnN>;
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 full table scan, the system then reads 128 instead of only 8 or 32 blocks for each I/O request from the disk). Since the parameter affects the cost accounting, it can only be set to 128 here, and not generally.
As of Release 4.6C, you can use transaction TAANA to determine the value distribution of columns.
Caution: A large number of blocks are imported with these methods, which can increase the I/O load. Do not execute these actions 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 a fragmented index, check the index fragmentation as described in Note 771929.
e) Source of the SQL statement