第九章 sqlplus的調整

第九章 sqlplus的調優

一、跟蹤語句

1、自動跟蹤報告

Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]

前提：自己的使用者下用命令@$ORACLE_HOME/RDBMS/ADMIN/UTLXPLAN.SQL建立plan_table，並由dba許可權使用者授予plustrace角色

2、執行計劃
執行計劃包括四個欄位：
ID_PLUS_EXP：   Shows the line number of each execution step.
PARENT_ID_PLUS_EXP： Shows the relationship between each step and its parent.This column is useful for large  reports.
PLAN_PLUS_EXP：  Shows each step of the report.
OBJECT_NODE_PLUS_EXP： Shows database links or parallel query servers used.

通過命令column parent_id_plus_exp noprint 可以隱藏該列。

3、統計資訊
recursive calls ： Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.
db block gets：  Number of times a CURRENT block was requested.
consistent gets ： Number of times a consistent read was requested for a block
physical reads Total：  number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.
redo size ： Total amount of redo generated in bytes
bytes sent through SQL*Net to client：Total number of bytes sent to the client from the foreground processes.
bytes received through SQL*Net from client： Total number of bytes received from the client over Oracle Net.
SQL*Net round-trips to/from client： Total number of Oracle Net messages sent to and received from the client
sorts (memory)：  Number of sort operations that were performed completely in memory and did not require any disk  writes
sorts (disk)：  Number of sort operations that required at least one disk write
rows processed：  Number of rows processed during the operation

按照需要可以合理選擇set autot的選項。通常來說，查詢大量資料時，考慮set autot on traceonly

二、收集時間資訊

set timing on 顯示一條語句或一個語句塊的執行時間
set timing off
clear timing

三、並行和分步式查詢的跟蹤

對於並行或分散式的查詢，執行計劃顯示的成本是基於優化器對行數評估而得到的。通常統計結果是累計值。
含有星號的行標識。

執行計劃中有三列：
ID_PLUS_EXP ：Shows the line number of each execution step.
OTHER_TAG_PLUS_EXP：Describes the function of the SQL statement in the OTHER_PLUS_EXP column.
OTHER_PLUS_EXP：Shows the text of the query for the parallel server or remote database

就統計資訊來說，如果consistent gets或physical reads的值相對於其所返回的值來說比較高，表明查詢代價很高，可能需要優化。可以使用tkprof或v$sql檢視進行監控。

四、sqlplus指令碼的調優

1.column noprint
用法 COLUMN column_name NOPRINT
表示不顯示某一列資訊。可以用在排除少數幾個不需要的列顯示的情況。該命令是將指定的列查出來了但不顯示，而如果在select

中沒有這一列，則是根本就不對該列進行查詢。

2.set appinfo off
設定指令碼通過dbms_application_info的註冊，該包用於監控指令碼的效能和資源使用情況，off表示關閉了sqlplus對該包的內部調

用。

3.set arraysize
設定sqlplus每次從資料庫抽取資料的行數，範圍從1到5000
當前的幾個版本的資料庫，該值的設定影響不大。如果這個值過大，會花費更多的sqlplus記憶體，降低效能。

4.set define off
禁止用命令設定 用值代替變數

5.set flush off
允許作業系統的快取輸出。
on禁止快取輸出到螢幕。
如果執行的指令碼不需要使用者互動，可以在執行結束後再看到，就可以設定為off。

6.set linesize
設定每行的字元數
儘量將此值設的小一點，以避免額外的記憶體分配；但linesize太小，列值不在一行顯示，也會降低效能

7.set longchunksize
設定sqlplus在輸出CLOB、LONG、NCLOB、XML型別資料時的大小。

8.set pagesize
設定每頁的行數。增加該值可以避免頻繁顯示頁首資訊，設定為0會阻止頁首顯示。

9.set serveroutput
off將禁止dbms_output.put_line的輸出顯示，在內部不會呼叫dbms_output包

10.set sqlprompt
設定sqlplus的提示符，預設是“SQL>”。iSQLplus不支援

11.set tab
設定輸出的結果中空格的格式。set tab on後多個空格被壓縮。除了減少資料寫以外，該命令的使用弊大於利。

12.set termout
set termout off 不會將結果顯示在螢幕上，如果既不需要顯示結果，也不需要將結果spooling到檔案，可以用該命令。

13.set trimout on/set trimspool on
去掉結果結尾的空格或空行

14.undefine
刪除不必要的替代變數，減少了對‘&’、new_value、old_value呼叫時的時間花費。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-753449/，如需轉載，請註明出處，否則將追究法律責任。