Oracle SQL optimization-2(zt)
Oracle SQL optimization(摘自SAP Note 766349)
17. What must be considered in terms of the index design?
For more information, see Note 912620.
18. What analysis tools are available at Oracle level?
For various reasons, an SQL analysis may be useful at database level directly (for example, if no R/3 functions are available in a Java-only environment). Therefore, useful Oracle functions for SQL analysis are described below:
- Creating an SQL trace
You can use ORADEBUG (Note 613872) or DBMS_SYSTEM (Note 602667) to create an SQL trace of any session.
- Displaying an Explain plan
With the following command, the run schedule is displayed for all subsequent 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 bind variables, the above approach does not work. You can use the following option instead:
EXPLAIN PLAN FOR ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
- Display block accesses of an SQL statement
Among other things, the following command counts the Disk Reads and Buffer Gets of the subsequent SQL statements (without the actual result of the query being returned):
SET AUTOTRACE TRACEONLY
You can deactivate this mode with:
SET AUTOTRACE OFF
- PLAN statistics (Oracle >=9i)
As of Oracle 9i you can collect detailed PLAN statistics such as processed records, buffer gets, disk reads or required time. Since the collection of these extensive statistics has a significant effect on database performance, this option is normally deactivated. You can use SQLPLUS to activate and evaluate these PLAN 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 TIME
FROM V$SQL_PLAN_STATISTICS_ALL PS, V$SQL S WHERE
S.SQL_TEXT LIKE '' AND
PS.ADDRESS = S.ADDRESS
ORDER BY 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 last execution.
- Deactivating the PLAN statistics:
ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;
- PLAN-Statistiken (Oracle >= 10g)
As of Oracle 10g, you can also use the GATHER_PLAN_STATISTICS hint:
- Execute the SQL statement using the GATHER_PLAN_STATISTICS hint:
SELECT /*+ GATHER_PLAN_STATISTICS */ ...
- 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 contained in the columns "A-Rows" and "A-Time".
- Sample data
In some situations, it is useful to get an impression of the data composition without having to scan the entire table or without having to rely on how representative the first records of a table are. You can use the following command to randomly select data records:
SELECT * FROM SAMPLE (
SELECT * FROM
相關文章
- oracle 特殊SQL(TABLE( CAST( MULTISET()[zt]OracleSQLAST
- oracle常用經典SQL查詢(zt)OracleSQL
- [zt] Oracle與SQL Server的互連OracleSQLServer
- Oracle11g新特性:SQL Result Cache [zt]OracleSQL
- ORACLE SQL語句優化技術分析(zt)OracleSQL優化
- ORACLE DBA常用SQL指令碼工具->管理篇(zt)OracleSQL指令碼
- 應遵循的PL/SQL編碼規則 - ORACLE (zt)SQLOracle
- [zt] SQL存取Oracle當中掃描資料的方法SQLOracle
- oracle enqueue(zt)OracleENQ
- SQL Server專題 [zt]SQLServer
- sql mode 型別(ZT)SQL型別
- zt_oracle11g sql baseline與sql execution plan執行計劃OracleSQL
- zt_Oracle 10gR2新SQL提示——opt_paramOracle 10gSQL
- Oracle資料庫維護常用的SQL程式碼示例(zt)Oracle資料庫SQL
- ORACLE LARGE MEMORY(zt)Oracle
- oracle job管理(zt)Oracle
- oracle event 2 (zt)Oracle
- [zt] Oracle LogMinerOracle
- zt_oracle indexOracleIndex
- Oracle RAC TAF [zt]Oracle
- Oracle的MTS (ZT)Oracle
- oracle block 格式 (zt)OracleBloC
- ORACLE ROWID (zt)Oracle
- [zt] ORACLE RAC原理Oracle
- jboss oracle rac (zt)Oracle
- SQL Access Advisor(zt)SQL
- [精華zt] SQL調優整理SQL
- SQL優化器探討(zt)SQL優化
- [zt] 影響SQL效能的原因SQL
- 產生top sql的原因(zt)SQL
- [zt] sql server 死鎖總結SQLServer
- oracle time_zone(zt)Oracle
- ZT oracle全文索引Oracle索引
- (zt)Oracle效能調整Oracle
- Oracle Job 小結(ZT)Oracle
- ORACLE診斷事件(zt)Oracle事件
- ORACLE鎖的管理(zt)Oracle
- zt_oracle golden gateOracleGo