oraclesql周
① Oraclesql
前言
sql_trace 是我在工作中經常要用到的調優工具 相比較statspack 我更願意用這個工具
因為數據逗搜庫慢原因的 %以上是由於sql問題造成的 statspack沒有sql的執行計劃 顯示沒有它直觀 方便 對想要針對性不強
介紹資料庫調優需要經常會用到的工具 可以很精確地跟抓取相關session正在運行的sql 再通過tkprof分析出來sql的執行計劃等相關信息 從而判斷那些sql語句存在問題
統計如下信孝鄭息（摘字官方文檔）
Parse execute and fetch counts
CPU and elapsed times
Physical reads and logical reads
Number of rows processed
Misses on the library cache
Username under which each parse occurred
Each mit and rollback
使用
使用前需要注意的地方
初始化參數timed_statistics=true 允許sql trace 和其他的一些動態性能視圖收集與時間（cpu elapsed）山慎歷有關的參數 一定要打開 不然相關信息不會被收集 這是一個動態的參數 也可以在session級別設置
SQL>alter session set titimed_statistics=true
MAX_DUMP_FILE_SIZE跟蹤文件的大小的限制 如果跟蹤信息較多可以設置成unlimited 可以是KB MB單位 I開始默認為unlimited這是一個動態的參數 也可以在session級別設置
SQL>alter system set max_mp_file_size=
SQL>alter system set max_mp_file_size=unlimited
USER_DUMP_DEST指定跟蹤文件的路徑 默認路徑實在$ORACLE_BASE/admin/ORA_SID/ump這是一個動態的參數 也可以在session級別設置
SQL>alter system set user_mp_dest=/oracle/trace
資料庫級別
設置slq_trace參數為true會對整個實例進行跟蹤 包括所有進程 用戶進程和後台進程 會造成比較嚴重的性能問題 生產環境一定要慎用
SQL>alter system set sql_trace=true;
Session級別
當前會話
SQL>alter session set sql_trace=true;
SQL>alter session set sql_trace=false;
其他會話
通過oracle提供的系統包 DBMS_SYSTEM SET_SQL_TRACE_IN_SESSION來實現
SQL>execute dbms_system set_sql_trace_in_session(sid serial# true);
SQL>execute dbms_system set_sql_trace_in_session(sid serial# false);
注
sid serial#從v$session視圖中獲得
DBMS_SYSTEM包里還可以對其他用戶的參數（如 timed_statistics max_mp_file）進行設置 在這不做介紹了 很少用到 想了解dbms_system里的程序包可以desc dbms_system看一下
得到trace文件後我們要用tkprof他進行格式化 通過sql語句快速定位到相應的trace文件
Tkprof
tkprof的目的是將sql trace生成的跟蹤文件轉換成用戶可以理解的格式
格式
tkprof tracefile outputfile [optional | parameters ]
參數和選項（這里只介紹最常用的 也是最實用的）
explain=user/password執行explain命令將結果放在SQL trace的輸出文件中
sys=[yes/no]確定系統是否列出由sys用戶產生或重調的sql語句
sort=sort_option按照指定的方法對sql trace的輸出文件進行降序排序
sort_option選項
prscnt按解析次數排序
prscpu按解析所花cpu時間排序
prsela按解析所經歷的時間排序
prsdsk按解析時物理的讀操作的次數排序
prsqry按解析時以一致模式讀取數據塊的次數排序
prscu按解析時以當前讀取數據塊的次數進行排序
execnt按執行次數排序
execpu按執行時花的cpu時間排序
exeela按執行所經歷的時間排序
exedsk按執行時物理讀操作的次數排序
exeqry按執行時以一致模式讀取數據塊的次數排序
execu按執行時以當前模式讀取數據塊的次數排序
exerow按執行時處理的記錄的次數進行排序
exemis按執行時庫緩沖區的錯誤排序
fchcnt按返回數據的次數進行排序
fchcpu按返回數據cpu所花時間排序
fchela按返回數據所經歷的時間排序
fchdsk按返回數據時的物理讀操作的次數排序
fchqry按返回數據時一致模式讀取數據塊的次數排序
fchcu按返回數據時當前模式讀取數據塊的次數排序
fchrow按返回數據時處理的數據數量排序
注
這些排序中我經常用到的是fchdsk fckchela fchqry 因為有問題的sql一般都是大的查詢造成的 當然更新 插入 刪除時也會存在全表掃描 這就需要:exedsk exeqry exeela等選項 根據具體情況具體分析
Cpu時間和Elapsed時間都是以秒為單位 而且兩個值基本上一樣 但我比較常用elapsed 他是反映的用戶相應時間 從運行sql到用戶得到結果的時間 會更實際些
tkprof輸出文件各列的含義 （理解下面的含義對我們快速定位問題很有幫助）
parse:
將sql語句轉換成執行計劃 包括檢查是否有正確的授權 需要到得表 列及其他引用到得對象是否存在 這些信息分別存在v$librarycache v$rowcache
execute
oracle實際執行的語句 如 insert update delete 這些會修改數據 對於select操作 這部只是確定選擇的行數
fetch
返回查詢獲得的行數 只有執行select會被收集
Count
這個語句被parse execute fetch的次數的統計
Cpu
這個語句所有的parse execute fetch所用的cpu總的時間 以秒為單位 如果TIMED_STATISTICS 關閉的話 值為
Elapsed
這個語句所有的parse execute fetch所消耗的總的時間 以秒為單位 如果TIMED_STATISTICS 關閉的話 值為
Disk
這個語句所有的parse execute fetch從磁碟上的數據文件中讀取的數據塊的數量
Query
在一致性讀的模式下 這個語句所有的parse execute fetch所獲取的buffer數量（這部分是從內存讀取的也就是邏輯讀取的 相當於執行計劃里的consistent gets）
Current
在current模式下 這個語句所有的parse execute fetch所獲取的buffer數量 一般是current模式下發生的delect insert update的操作都會獲取buffer
Rows
語句返回的行數 不包括子查詢中返回的記錄數目 對於select語句 返回在fetch這步 對於insert delete update操作 返回記錄是在execute這步
分析
我一般的思路步驟是
先找磁碟多的sq l(sort= fchdsk ) 意味著全表掃描 找運行時間長的(sort= fchela) 意味著sql可能寫的不好或磁碟 邏輯讀較多 找出一致性讀較多的(sort= fchqry) 當表不是很大的時候（可能全部緩存住了） 沒有發生磁碟讀 但不意味著不需要建立索引 或者sql需要優化 找出當前模式從緩沖區獲得數據的數量(sort=exedsk exeela exeqry) 這些主要集中在dml語句里的操作 看是否有必要優化sql或建立索引之所以排序是為了在sql很多的時候快速定位sql 如果sql比較少的話就沒必要排序了 但我們要有分析問題的思路
舉例
我自己建立了一個表
create table t (id int);
begin
for v in loop
insert into t values(v );
end loop
mit;
end;
下面是sql_trace所抓到得sql
不正常狀態
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
TABLE ACCESS FULL T
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
TABLE ACCESS (FULL) OF T
首先這是一個select語句 它走了全部掃描
磁碟讀（ ）和邏輯讀（ ）都很多
運行了 次（Execute） 分析了 次（Parse） 一共用了將近 秒（elapsed）
我只是選擇表的一行的數據的結果 就發生這么大的成本 很顯然是全表掃描的結果造成的
正常狀態
在做跟蹤前我為這個表建立了一個索引
Create index t on t (id);
*******************************************************************************
select *
from t
where id=
call count cpu elapsed disk query current rows
Parse Execute Fetch
total
Misses in library cache ring parse:
Optimizer goal: CHOOSE
Parsing user id: (WH)
Rows Row Source Operation
INDEX RANGE SCAN T (object id )
Rows Execution Plan
SELECT STATEMENT GOAL: CHOOSE
INDEX (RANGE SCAN) OF T (NON UNIQUE)
*******************************************************************************
同樣的語句
它走了索引 物理讀 這個 其實是開始讀索引時需要第一次讀入的 以後運行就沒有了
邏輯讀 （平均這個sql一次 個邏輯讀）
同樣運行了 次（Execute）
分析了 次（Parse） 運行次數越多 分析次數越少越好一共只用了 秒（elapsed）
lishixin/Article/program/Oracle/201311/17866
② oraclesql語句實現數字四捨五入取整
Oracle SQL語句實現數字四捨五入取整可以使用ROUND函數。
解釋：
Oracle SQL中的ROUND函數用於四捨五入數字。這個函數可以接受一個或多個數值作為參數，並返回最接近的整數。當需要四捨五入一個數字時，可以使用ROUND函數來實現。
ROUND函數的基本語法如下：
ROUND
其中：
* number：是需要四捨五入的數值。
* decimal_places：是可選參數，表示要保留的小數位數。如果此參數為負數，則進行整數的四捨五入。如果不提供此參數，默認值為0，表示直接取整。
例如，要將數字欄位四捨五入取整，可以使用以下SQL語句：
SELECT ROUND FROM table_name;
如果希望保留特定的小數位數進行四捨五入，可以傳入相應的decimal_places參數。例如，保留兩位小數進行四捨五入：
SELECT ROUND FROM table_name;
總之，使用Oracle SQL中的ROUND函數，可以輕松實現數字的四捨五入取整操作。根據需求，可以選擇是否保留特定的小數位數。
③ oraclesql執行順序優先順序
Oracle SQL執行順序優先順序為：
1. 解析與編譯階段。
2. 執行計劃生成。
3. 執行階段。
接下來是對這一執行順序的
解析與編譯階段： 這是SQL語句處理的第一個階段。在這一階段，Oracle會檢查語法，識別並驗證SQL語句中的表和列名，同時還會解析使用到的任何函數或過程。此外，還會根據對象定義和數據統計信息生成執行計劃的基礎結構。如果SQL語句被緩存或包含在一個命名塊中，那麼Oracle可能會跳過解析階段，直接使用已編譯的代碼。
執行計劃生成階段： 在解析和編譯之後，Oracle會生成執行計劃，這是根據解析和編譯過程中收集到的數據統計信息和對象定義來決定的。Oracle優化器會考慮多種可能的執行路徑並選擇成本最低的一種。這個計劃詳細說明了如何檢索數據以及如何以最高效的方式執行查詢。
執行階段： 這是最後一個階段，Oracle根據生成的執行計劃開始獲取數據並返回結果。根據計劃中的指示，它會訪問磁碟上的數據或將數據從內存緩存中檢索出來，對數據進行必要的操作並返回結果集。執行階段的具體細節取決於查詢的復雜性以及資料庫的配置和數據分布。
整體上，Oracle SQL的執行過程涉及多個階段和多個復雜的決策過程，從解析和編譯到執行計劃的生成和執行。了解這些階段的順序和每個階段的作用對於有效地編寫和優化SQL查詢至關重要。同時，還需要考慮到資料庫的性能和資源管理等方面的影響。