oraclesql疑難解析
1. 如何分析Oracle
以oracle表分析為例：
drop table test;
select count(*) from test;
--創建測試表
create table test
(
id number(9),
nick varchar2(30)
);
--插入測試數據
begin
for i in 1..100000 loop
insert into test(id) values(i);
end loop;
commit;
end;
select * from test;
--更新nick欄位，使數據發生嚴重傾斜
update test set nick='abc' where rownum<99999;
--創建索引
create index idx_test_nick on test(nick);
update test set nick='def' where nick is null;
--只對索引譽毀進行分析
analyze index idx_test_nick compute statistics;
select * from user_indexes;
--查看索引名,對應存儲的數據塊,不同的key數量,記錄數（行數）的分析信息
select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS
from user_indexes
where index_name = 'IDX_TEST_NICK';
--dba_tab_col_statistics
--查看錶的統計信息
select COLUMN_NAME, NUM_BUCKETS, num_distinct
from USER_tab_columns
where table_name = 'TEST';
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--由上可以看到,對索引分析之後,sql的執行路徑都是基於規則的,索引的慶孫備欄位的偏移
--先根據索引找到rowid,然後再根據rowid讀取記錄,這個過程肯定比全表掃描讀取記錄要慢
--user_part_col_statistics 分區分析信息
--分析表的第二列nick
analyze table test compute statistics for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--根據上面的執行計劃，還是按照規則來執行的凱笑
--分析表
analyze table test compute statistics for table;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--分析表之後，完全按照成本來執行
--刪除所有的統計數據，並只對表與列進行分析，不分析索引，
--ORACLE使用CBO的優化器，並產生了正確的執行計劃
analyze table test delete statistics;
--分析列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=99998 Bytes=
1499970)
1 0 TABLE ACCESS (FULL) OF 'TEST' (Cost=49 Card=99998 Bytes=14
99970)
--
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=2 Bytes=30)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=2 Byt
es=30)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--創建TEST表ID列上的索引，但不對索引進行分析
create index idx_test_id on test(id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--當條件中即有id，又有nick時，因為nick上有直方圖,ORACLE知道nick='abc'的值特別的多，所以不走IDX_TEST_NICK索引,走IDX_TEST_ID上的索引
select * from test where id=5 and nick='abc';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=15
000)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1000
Bytes=15000)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=400)
--當條件中即有id，又有nick時，因為nick上有直方圖,ORACLE知道nick='def'的值特別的少，所以走IDX_TEST_NICK上的索引,不走IDX_TEST_ID索引
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
select * from test where nick='def' and id=5;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE) (Cost
=1 Card=2)
--在分析ID列後，ORACLE發現ID列的選擇度更高，所以不再選擇IDX_TEST_NICK索引，而是選擇IDX_TEST_ID
analyze table test compute statistics for columns size 1 id;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=7)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=7)
2 1 INDEX (RANGE SCAN) OF 'IDX_TEST_ID' (NON-UNIQUE) (Cost=1
Card=1)
/*
下面來看另外一種情況，我們刪除所有的統計數據，然後在ID列上創建唯一索引,在此條件下，
只分析表與分析列nick,我們看到ORACLE走了正確的執行計劃，
走了UK_TEST_ID,其實從這里也給我們帶來很多的啟示：
在主鍵與唯一鍵約束的列上是否需要直方圖的問題?
如果在這些列上有像這樣的查詢where id > 100 and id < 1000,
我們還是需要有直方圖的，但除此之外，好像真的沒有直方圖的必要了!
*/
analyze table test delete statistics;
drop index idx_test_id;
create unique index uk_test_id on test(id);
--分析表的第二列nick
analyze table test compute statistics for table for columns size 2 nick;
select * from test where id=5 and nick='def';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=15)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (Cost=2 Card=1 Byt
es=15)
2 1 INDEX (UNIQUE SCAN) OF 'UK_TEST_ID' (UNIQUE) (Cost=1 Car
d=100000)
從以上一系列的實驗可以看出，對ORACLE的優化器CBO來說，表的分析與列的分析才是最重要的，索引的分析次之。還有我們可以考慮我們的哪些列上需要直方圖，對於bucket的個數問題,oracle的默認值是75個，所以根據你的應用規則，選擇合適的桶數對性能也是有幫助的。因為不必要的桶的個數的大量增加，必然會帶來SQL語句硬解析時產生執行計劃的復雜度問題。
2. [SQL][oracle疑難問題ORA-00031]：標記要終止的會話 解決方法
問題場景：在操作大數據量的時候，會出現種種問題導致數據不正確導致需要重新處理數據，如果這個時候刪除表數據會無法刪除成功，然後手動終止會話可能會提示「標記要終止的會話」，這是因為手動終止會話後，進程的狀態被設置為「killed」,但是鎖定的資源很長時間沒有被釋放，那麼可以在OS級再殺死相應的進程（線程），詳細操作如下：
1，查詢臨時會話的session：
--查找sql執行的會話session id, 用session id 這一欄的數據進行kill掉會話
SELECT B.SID,
B.OBJECT,
A.SQL_ID,
A.LOGON_TIME,
A.SQL_EXEC_START,
A.MODULE,
C.SQL_TEXT,
C.SQL_FULLTEXT,
薯肢 A.OSUSER,
A.EVENT#,
A.EVENT,
A.INST_ID,
A.ACTION,
A.PROCESS,
A.STATUS,
'alter system kill session ''' || a.sid || ', ' || a.serial# ||
', @' || a.inst_id || ''' IMMEDIATE;' kill_session,
A.*
FROM GV$SESSION A, GV$ACCESS B,GV$SQL C
WHERE A.SID = B.SID
AND A.INST_ID = B.INST_ID
AND A.SQL_ID = C.SQL_ID(+)
AND A.INST_ID = C.INST_ID(+)
--AND A.TYPE <> 'BACKGROUND'
--AND A.STATUS = 'ACTIVE'
AND B.OWNER = 'BI_DM'
--AND A.INST_ID =1
--AND A.ACTION IN ('ORA$AT_SA_SPC_SY_4630','ORA$AT_OS_OPT_SY_4629')
AND B.OBJECT = 'TMP_T_JOBNAME'
--AND A.USERNAME LIKE 'DEV%'
--AND A.MACHINE IN (/*'infor',*/'winitdb')
ORDER BY A.SQL_EXEC_START;
2，執行session id 裡面是語句
alter system kill session '2761, 65372, @2' IMMEDIATE;
在kill session的時候，當提示RA-00031：標記要終止的會話 時
按如下操作：
1，select a.spid,b.sid,b.serial#,b.username 悔手掘from v$process a,v$session b where a.addr=b.paddr and b.status='KILLED';
2，如果利用上面的命令殺死一個進程後，進程狀態被置為"killed"，但是鎖定的資源很長時間沒有被釋放，那麼可以在OS級再殺死相應的進程（線程），首先執行下面的語句獲得進碧核程（線程）號：
select b.spid,a.osuser,b.program from v$session a,v$process b where a.paddr=b.addr and a.sid=9065 --9065就是上面的sid
.在OS上殺死這個進程（線程）
1)、在unix上，用root身份執行命令：#kill -9 9065（即第2步查詢出的spid）
2)、在windows（unix也適用）用orakill殺死線程，orakill是oracle提供的一個可執行命令，語法為：orakill sid thread
其中：
sid：表示要殺死的進程屬於的實例名 。可以用select name from v$database;查詢
thread：是要殺掉的線程號，即第2步查詢出的spid。
例：c:>orakill system 9065
注意：這里要注意的是kill OS進程是在服務端操作，而不是你程序所在客戶機
3. oracle sql是怎麼解析的
導讀：Oracle的後台運作原理是什麼？我們的一條命令是如何被執行的？今天我們就從一條簡單的Select語句開始，看看Oracle資料庫後台的運作機制。
Select語句可以說是DBA和資料庫開發者在工作中使用最多的語句之一，但這條語句是如何執行？在Oracle資料庫中又是如何運作的呢?今天我們就從一條簡單的Select語句開始，看看Oracle資料庫後台的運作機制。這對於我們之後的系統管理與故障排除非常有幫助。
第一步：客戶端把語句發給伺服器端執行
當我們在客戶端執行select語句時，客戶端會把這條SQL語句發送給伺服器端，讓伺服器端的進程來處理這語句。也就是說，Oracle客戶端是不會做任何的操作，他的主要任務就是把客戶端產生的一些SQL語句發送給伺服器端。雖然在客戶端也有一個資料庫進程，但是，這個進程的作用跟伺服器上的進程作用事不相同的。伺服器上的資料庫進程才會對SQL語句進行相關的處理。不過，有個問題需要說明，就是客戶端的進程跟伺服器的進程是一一對應的。也就是說，在客戶端連接上伺服器後，在客戶端與伺服器端都會形成一個進程，客戶端上的我們叫做客戶端進程;而伺服器上的我們叫做伺服器進程。所以，由於所有的SQL語句都是伺服器進程執行的，所以，有些人把伺服器進程形象地比喻成客戶端進程的「影子」。
第二步：語句解析
當客戶端把SQL語句傳送到伺服器後，伺服器進程會對該語句進行解析。同理，這個解析的工作，也是在伺服器端所進行的。雖然這只是一個解析的動作，但是，其會做很多「小動作」。
1. 查詢高速緩存。伺服器進程在接到客戶端傳送過來的SQL語句時，不會直接去資料庫查詢。而是會先在資料庫的高速緩存中去查找，是否存在相同語句的執行計劃。如果在數據高速緩存中，剛好有其他人使用這個查詢語句的話，則伺服器進程就會直接執行這個SQL語句，省去後續的工作。所以，採用高速數據緩存的話，可以提高SQL語句的查詢效率。一方面是從內存中讀取數據要比從硬碟中的數據文件中讀取數據效率要高，另一方面，也是因為這個語句解析的原因。
不過這里要注意一點，這個數據緩存跟有些客戶端軟體的數據緩存是兩碼事。有些客戶端軟體為了提高查詢效率，會在應用軟體的客戶端設置數據緩存。由於這些數據緩存的存在，可以提高客戶端應用軟體的查詢效率。但是，若其他人在伺服器進行了相關的修改，由於應用軟體數據緩存的存在，導致修改的數據不能及時反映到客戶端上。從這也可以看出，應用軟體的數據緩存跟資料庫伺服器的高速數據緩存不是一碼事。
2. 語句合法性檢查。當在高速緩存中找不到對應的SQL語句時，則資料庫伺服器進程就會開始檢查這條語句的合法性。這里主要是對SQL語句的語法進行檢查，看看其是否合乎語法規則。如果伺服器進程認為這條SQL語句不符合語法規則的時候，就會把這個錯誤信息，反饋給客戶端。在這個語法檢查的過程中，不會對SQL語句中所包含的表名、列名等等進行SQL他只是語法上的檢查。
3. 語言含義檢查。若SQL語句符合語法上的定義的話，則伺服器進程接下去會對語句中的欄位、表等內容進行檢查。看看這些欄位、表是否在資料庫中。如果表名與列名不準確的話，則資料庫會就會反饋錯誤信息給客戶端。
所以，有時候我們寫select語句的時候，若語法與表名或者列名同時寫錯的話，則系統是先提示說語法錯誤，等到語法完全正確後，再提示說列名或表名錯誤。若能夠掌握這個順序的話，則在應用程序排錯的時候，可以節省時間。
4. 獲得對象解析鎖。當語法、語義都正確後，系統就會對我們需要查詢的對象加鎖。這主要是為了保障數據的一致性，防止我們在查詢的過程中，其他用戶對這個對象的結構發生改變。對於加鎖的原理與方法，我在其他文章中已經有專門敘述，在這里就略過不談了。
5. 數據訪問許可權的核對。當語法、語義通過檢查之後，客戶端還不一定能夠取得數據。伺服器進程還會檢查，你所連接的用戶是否有這個數據訪問的許可權。若你連接上伺服器的用戶不具有數據訪問許可權的話，則客戶端就不能夠取得這些數據。故，有時候我們查詢數據的時候，辛辛苦苦地把SQL語句寫好、編譯通過，但是，最後系統返回個 「沒有許可權訪問數據」的錯誤信息，讓我們氣半死。這在前端應用軟體開發調試的過程中，可能會碰到。所以，要注意這個問題，資料庫伺服器進程先檢查語法與語義，然後才會檢查訪問許可權。
6. 確定最佳執行計劃。當語句與語法都沒有問題，許可權也匹配的話，伺服器進程還是不會直接對資料庫文件進行查詢。伺服器進程會根據一定的規則，對這條語句進行優化。不過要注意，這個優化是有限的。一般在應用軟體開發的過程中，需要對資料庫的sql語言進行優化，這個優化的作用要大大地大於伺服器進程的自我優化。所以，一般在應用軟體開發的時候，資料庫的優化是少不了的。
當伺服器進程的優化器確定這條查詢語句的最佳執行計劃後，就會將這條SQL語句與執行計劃保存到數據高速緩存。如此的話，等以後還有這個查詢時，就會省略以上的語法、語義與許可權檢查的步驟，而直接執行SQL語句，提高SQL語句處理效率。
第三步：語句執行
語句解析只是對SQL語句的語法進行解析，以確保伺服器能夠知道這條語句到底表達的是什麼意思。等到語句解析完成之後，資料庫伺服器進程才會真正的執行這條SQL語句。
這個語句執行也分兩種情況。一是若被選擇行所在的數據塊已經被讀取到數據緩沖區的話，則伺服器進程會直接把這個數據傳遞給客戶端，而不是從資料庫文件中去查詢數據。若數據不在緩沖區中，則伺服器進程將從資料庫文件中查詢相關數據，並把這些數據放入到數據緩沖區中。
這里仍然要注意一點，就是Oracle資料庫中，定義了很多種類的高速緩存。像上面所說的SQL語句緩存與現在講的數據緩存。我們在學習資料庫的時候，需要對這些緩存有一個清晰的認識，並了解各個種類緩存的作用。這對於我們後續資料庫維護與資料庫優化是非常有用的。
第四步：提取數據
當語句執行完成之後，查詢到的數據還是在伺服器進程中，還沒有被傳送到客戶端的用戶進程。所以，在伺服器端的進程中，有一個專門負責數據提取的一段代碼。他的作用就是把查詢到的數據結果返回給用戶端進程，從而完成整個查詢動作。
從這整個查詢處理過程中，我們在資料庫開發或者應用軟體開發過程中，需要注意以下幾點：
一是要了解資料庫緩存跟應用軟體緩存是兩碼事情。資料庫緩存只有在資料庫伺服器端才存在，在客戶端是不存在的。只有如此，才能夠保證資料庫緩存中的內容跟資料庫文件的內容一致。才能夠根據相關的規則，防止數據臟讀、錯讀的發生。而應用軟體所涉及的數據緩存，由於跟資料庫緩存不是一碼事情，所以，應用軟體的數據緩存雖然可以提高數據的查詢效率，但是，卻打破了數據一致性的要求，有時候會發生臟讀、錯讀等情況的發生。所以，有時候，在應用軟體上有專門一個功能，用來在必要的時候清除數據緩存。不過，這個數據緩存的清除，也只是清除本機上的數據緩存，或者說，只是清除這個應用程序的數據緩存，而不會清除資料庫的數據緩存。
二是絕大部分SQL語句都是按照這個處理過程處理的。我們DBA或者基於Oracle資料庫的開發人員了解這些語句的處理過程，對於我們進行涉及到SQL語句的開發與調試，是非常有幫助的。有時候，掌握這些處理原則，可以減少我們排錯的時間。特別要注意，資料庫是把數據查詢許可權的審查放在語法語義的後面進行檢查的。所以，有時會若光用資料庫的許可權控制原則，可能還不能滿足應用軟體許可權控制的需要。此時，就需要應用軟體的前台設置，實現許可權管理的要求。而且，有時應用資料庫的許可權管理，也有點顯得繁瑣，會增加伺服器處理的工作量。因此，對於記錄、欄位等的查詢許可權控制，大部分程序涉及人員喜歡在應用程序中實現，而不是在資料庫上實現。
4. Sql語句解析過程
為了將用戶寫的SQL文本轉化為Oracle認識的且可執行的語句 這個過程就叫做解析過程 解析分為硬解析和軟解析 一條SQL語句在第一次被執行時必須進行硬解析
當客戶端發出一條SQL語句（也可以是一個存儲過程或者一個匿名PL/SQL塊）進入shared pool時（注意 我們從前面已經知道 Oracle對這些SQL不叫做SQL語句 而是稱為游標 因為Oracle在處理SQL時 需要很多相關的輔助信息 這些輔助信息與SQL語句一起組成了游標） Oracle首先將SQL文本轉化為ASCII值 然後根據hash函數計算其對應的hash值（hash_value） 根據計算出的hash值到library cache中找到對應的bucket 然後比較bucket里是否存在該SQL語句
如果不存在 則需要按照我們前面所描述的 獲得shared pool latch 然後在shared pool中的可用chunk鏈表（也就是bucket）上找到一個可用的chunk 之後釋放shared pool latch 在獲得了chunk以後 這塊chunk就可以認為是進入了library cache 接下來 進行硬解析過程 硬解析包括以下幾個步驟
對SQL語句進行文法檢查 看是否有文法錯誤 比如沒有寫from select拼寫錯誤等 如果存在文法錯誤 則退出解析過程
到數據字典里校驗SQL語句涉及的對象和列是否都存在 如果不存在 則退出解析過程 這個過程會載入dictionary cache
將對象進行名稱轉換 比如將同名詞翻譯成實際的對象等 比如select * from t中 t是一個同名詞 指向hr t 於是Oracle將t轉換為hr t 如果轉換失敗 則退出解析過程
檢查發出SQL語句的用戶是否伏侍簡具有訪問SQL語句里所引用的對象的許可權 如果沒有許可權 則退出解析過程
通過優化器創建一個最優的執行計劃 這個過程會根據數據字典里記錄的對象的統計信息 來計算最優的執行計劃 這一步牽涉大量數談碧學運算 是最消耗CPU資源的
將該游標所產生的執行計劃 SQL文本等裝載進library cache的heap中
在硬解析的過程中 進程會一直持有library cache latch 直到硬解析結束為止 硬解析結束以後 會為SQL語句產生兩個游標 一個是父游標 另一個是子游標 父游標里主要包含兩種信息 SQL文本以及優化目標（optimizer goal） 父游標在第一次打開時被鎖定 直到其他所有的session都關閉該游標後才被解鎖 當父游標缺褲被鎖定的時候是不能被交換出library cache的 只有在解鎖以後才能被交換出library cache 父游標被交換出內存時 父游標對應的所有子游標也被交換出library cache 子游標包括游標所有的信息 比如具體的執行計劃 綁定變數等 子游標隨時可以被交換出library cache 當子游標被交換出library cache時 Oracle可以利用父游標的信息重新構建出一個子游標來 這個過程叫reload 可以使用下面的方式來確定reload的比率
select *sum(reloads)/sum(pins) Reload_Ratio from v$librarycache;
一個父游標可以對應多個子游標 子游標具體的個數可以從視圖v$sqlarea的version_count欄位體現出來 而每個具體的子游標則全都在視圖v$sql里體現 當具體綁定變數的值與上次綁定變數的值有較大差異（比如上次執行的綁定變數值的長度是 位 而這次執行綁定變數的值的長度是 位）時或者當SQL語句完全相同 但是所引用的表屬於不同的用戶時 都會創建一個新的子游標
如果在bucket中找到了該SQL語句 則說明該SQL語句以前運行過 於是進行軟解析 軟解析是相對於硬解析而言的 如果解析過程中 可以從硬解析的步驟中去掉一個或多個的話 這樣的解析就是軟解析 軟解析分為以下三種類型
第一種是某個session發出的SQL語句與library? cache里其他session發出的SQL語句一致 這時 該解析過程中可以去掉硬解析中的 和 但是仍然要進行硬解析過程中的 也就是表名和列名檢查 名稱轉換和許可權檢查
* 第二種是某個session發出的SQL語句是該session之前發出的曾經執行過的SQL語句 這時 該解析過程中可以去掉硬解析中的 和 這四步 但是仍然要進行許可權檢查 因為可能通過grant改變了該session用戶的許可權
* 第三種是當設置了初始化參數session_cached_cursors時 當某個session第三次執行相同的SQL時 則會把該SQL語句的游標信息轉移到該session的PGA里 這樣 該session以後再執行相同的SQL語句時 會直接從PGA里取出執行計劃 從而跳過硬解析的所有步驟 這種情況下 是最高效的解析方式 但是會消耗很大的內存
我們舉一個例子來說明解析SQL語句的過程 在該測試中 綁定變數名稱相同 但是變數類型不同時 所出現的解析情況 如下所示
首先 執行下面的命令 清空shared pool里所有的SQL語句
SQL> alter system flush shared_pool;
然後 定義一個數值型綁定變數 並為該綁定變數賦一個數值型的值以後 執行具體的查詢語句
SQL> variable v_obj_id number;
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
接下來 定義一個字元型的綁定變數 變數名與前面相同 為該綁定變數賦一個字元型的值以後 執行相同的查詢
SQL> variable v_obj_id varchar ( );
SQL> exec :v_obj_id := ;
SQL> select object_id object_name from sharedpool_test
where object_id=:v_obj_id;
OBJECT_ID OBJECT_NAME
AGGXMLIMP
然後我們到視圖v$sqlarea里找到該SQL的父游標的信息 並到視圖v$sql里找該SQL的所有子游標的信息
SQL> select sql_text version_count from v$sqlarea where
sql_text like %sharedpool_test% ;
SQL_TEXT
VERSION_COUNT
select object_id object_name from sharedpool_test where
object_id=:v_obj_id
SQL> select sql_text child_address address from v$sql
where sql_text like %sharedpool_test% ;
SQL_TEXT
CHILD_ADDRESS ADDRESS
select object_id object_name from sharedpool_test where
object_id=:v_obj_id F
B D
select object_id object_name from sharedpool_test where
object_id=:v_obj_id FC
B D
從記錄父游標的視圖v$sqlarea的version_count列可以看到 該SQL語句有 個子游標 而從記錄子游標的視圖v$sql里可以看到 該SQL文本確實有兩條記錄 而且它們的SQL文本所處的地址（ADDRESS列）也是一樣的 但是子地址（CHILD_ADDRESS）卻不一樣 這里的子地址實際就是子游標所對應的heap 的句柄
lishixin/Article/program/Oracle/201311/18653
5. OracleSQL精妙SQL語句講解
行列轉換 行轉列
DROP TABLE t_change_lc;
CREATE TABLE t_change_lc (card_code VARCHAR ( ) q NUMBER bal NUMBER);
INSERT INTO t_change_lc
SELECT card_code ROWNUM q trunc(dbms_random VALUE * ) bal FROM al CONNECT BY ROWNUM <=
UNION
SELECT card_code ROWNUM q trunc(dbms_random VALUE * ) bal FROM al CONNECT BY ROWNUM <= ;
SELECT * FROM t_change_lc;
SELECT a card_code
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
FROM t_change_lc a
GROUP BY a card_code
ORDER BY ;
行列轉換 列轉行
DROP TABLE t_change_cl;
CREATE TABLE t_change_cl AS
SELECT a card_code
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
SUM(decode(a q a bal )) q
FROM t_change_lc a
GROUP BY a card_code
ORDER BY ;
SELECT * FROM t_change_cl;
SELECT t card_code
t rn q
decode(t rn t q t q t q t q ) bal
FROM (SELECT a * b rn
FROM t_change_cl a
(SELECT ROWNUM rn FROM al CONNECT BY ROWNUM <= ) b) t
ORDER BY ;
行列轉換 行轉列 合並
DROP TABLE t_change_lc_ma;
核局陪CREATE TABLE t_change_lc_ma AS SELECT card_code quarter_ ||q AS q FROM t_change_lc;
SELECT * FROM t_change_lc_ma;
SELECT t card_code substr(MAX(sys_connect_by_path(t q ; )) ) q
FROM (SELECT a card_code
a q
row_number() over(PARTITION BY a card_code ORDER BY a q) rn
FROM t_change_lc_ma a) t
START WITH t rn =
CONNECT BY t card_code = PRIOR t card_code
AND t rn = PRIOR t rn
GROUP BY t card_code;
行列轉換 列轉行 分割
DROP TABLE t_change_cl_ma;
CREATE TABLE t_change_cl_ma AS
SELECT t card_code substr(MAX(sys_connect_by_path(t q ; )) ) q
FROM (SELECT a card_code
a q
臘歷改蠢row_number() over(PARTITION BY a card_code ORDER BY a q) rn
FROM t_change_lc_ma a) t
START WITH t rn =
CONNECT BY t card_code = PRIOR t card_code
AND t rn = PRIOR t rn
GROUP BY t card_code;
SELECT * FROM t_change_cl_ma;
SELECT t card_code
substr(t q
instr( ; || t q ; rn)
instr(t q || ; ; rn) instr( ; || t q ; rn)) q
FROM (SELECT a card_code a q b rn
FROM t_change_cl_ma a
(SELECT ROWNUM rn FROM al CONNECT BY ROWNUM <= ) b
WHERE instr( ; || a q ; rn) > ) t
ORDER BY ;
實現一條記錄根據條件多表插入
DROP TABLE t_ia_src;
CREATE TABLE t_ia_src AS SELECT a ||ROWNUM c b ||ROWNUM c FROM al CONNECT BY ROWNUM<= ;
DROP TABLE t_ia_dest_ ;
CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));
DROP TABLE t_ia_dest_ ;
CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));
DROP TABLE t_ia_dest_ ;
CREATE TABLE t_ia_dest_ (flag VARCHAR ( ) c VARCHAR ( ));
SELECT * FROM t_ia_src;
SELECT * FROM t_ia_dest_ ;
SELECT * FROM t_ia_dest_ ;
SELECT * FROM t_ia_dest_ ;
INSERT ALL
WHEN (c IN ( a a )) THEN
INTO t_ia_dest_ (flag c) VALUES(flag c )
WHEN (c IN ( a a )) THEN
INTO t_ia_dest_ (flag c) VALUES(flag c )
ELSE
INTO t_ia_dest_ (flag c) VALUES(flag ||flag c ||c )
SELECT c c f flag f flag FROM t_ia_src;
如果存在就更新 不存在就插入用一個語句實現
DROP TABLE t_mg;
CREATE TABLE t_mg(code VARCHAR ( ) NAME VARCHAR ( ));
SELECT * FROM t_mg;
MERGE INTO t_mg a
USING (SELECT the code code the name NAME FROM al) b
ON (de = de)
WHEN MATCHED THEN
UPDATE SET a NAME = b NAME
WHEN NOT MATCHED THEN
INSERT (code NAME) VALUES (de b NAME);
抽取/刪除重復記錄
DROP TABLE t_p;
CREATE TABLE t_p AS SELECT code_ ||ROWNUM code dbms_random string( z ) NAME FROM al CONNECT BY ROWNUM<= ;
INSERT INTO t_p SELECT code_ ||ROWNUM code dbms_random string( z ) NAME FROM al CONNECT BY ROWNUM<= ;
SELECT * FROM t_p;
SELECT * FROM t_p a WHERE a ROWID <> (SELECT MIN(b ROWID) FROM t_p b WHERE de=de);
SELECT de b NAME
FROM (SELECT de
a NAME
row_number() over(PARTITION BY de ORDER BY a ROWID) rn
FROM t_p a) b
WHERE b rn > ;
IN/EXISTS的不同適用環境
t_orders customer_id有索引
SELECT a *
FROM t_employees a
WHERE a employee_id IN
(SELECT b sales_rep_id FROM t_orders b WHERE b customer_id = );
SELECT a *
FROM t_employees a
WHERE EXISTS (SELECT
FROM t_orders b
WHERE b customer_id =
AND a employee_id = b sales_rep_id);
t_employees department_id有索引
SELECT a *
FROM t_employees a
WHERE a department_id =
AND EXISTS
(SELECT FROM t_orders b WHERE a employee_id = b sales_rep_id);
SELECT a *
FROM t_employees a
WHERE a department_id =
AND a employee_id IN (SELECT b sales_rep_id FROM t_orders b);
FBI
DROP TABLE t_fbi;
CREATE TABLE t_fbi AS
SELECT ROWNUM rn dbms_random STRING( z ) NAME SYSDATE + dbms_random VALUE * dt FROM al
CONNECT BY ROWNUM <= ;
CREATE INDEX idx_nonfbi ON t_fbi(dt);
DROP INDEX idx_fbi_ ;
CREATE INDEX idx_fbi_ ON t_fbi(trunc(dt));
SELECT * FROM t_fbi WHERE trunc(dt) = to_date( yyyy mm dd ) ;
不建議使用
SELECT * FROM t_fbi WHERE to_char(dt yyyy mm dd ) = ;
LOOP中的MIT/ROLLBACK
DROP TABLE t_loop PURGE;
create TABLE t_loop AS SELECT * FROM user_objects WHERE = ;
SELECT * FROM t_loop;
逐行提交
DECLARE
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
MIT;
END LOOP;
END;
模擬批量提交
DECLARE
v_count NUMBER;
BEGIN
FOR cur IN (SELECT * FROM user_objects) LOOP
INSERT INTO t_loop VALUES cur;
v_count := v_count + ;
IF v_count >= THEN
MIT;
END IF;
END LOOP;
MIT;
END;
真正的批量提交
DECLARE
CURSOR cur IS
SELECT * FROM user_objects;
TYPE rec IS TABLE OF user_objects%ROWTYPE;
recs rec;
BEGIN
OPEN cur;
WHILE (TRUE) LOOP
FETCH cur BULK COLLECT
INTO recs LIMIT ;
forall 實現批量
FORALL i IN recs COUNT
INSERT INTO t_loop VALUES recs (i);
MIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur;
END;
悲觀鎖定/樂觀鎖定
DROP TABLE t_lock PURGE;
CREATE TABLE t_lock AS SELECT ID FROM al;
SELECT * FROM t_lock;
常見的實現邏輯 隱含bug
DECLARE
v_cnt NUMBER;
BEGIN
這里有並發性的bug
SELECT MAX(ID) INTO v_cnt FROM t_lock;
here for other operation
v_cnt := v_cnt + ;
INSERT INTO t_lock (ID) VALUES (v_cnt);
MIT;
END;
高並發環境下 安全的實現邏輯
DECLARE
v_cnt NUMBER;
BEGIN
對指定的行取得lock
SELECT ID INTO v_cnt FROM t_lock WHERE ID= FOR UPDATE;
在有lock的情況下繼續下面的操作
SELECT MAX(ID) INTO v_cnt FROM t_lock;
here for other operation
v_cnt := v_cnt + ;
INSERT INTO t_lock (ID) VALUES (v_cnt);
MIT; 提交並且釋放lock
END;
硬解析/軟解析
DROP TABLE t_hard PURGE;
CREATE TABLE t_hard (ID INT);
SELECT * FROM t_hard;
DECLARE
sql_ VARCHAR ( );
BEGIN
hard parse
java中的同等語句是 Statement execute()
FOR i IN LOOP
sql_ := insert into t_hard(id) values( || i || ) ;
EXECUTE IMMEDIATE sql_ ;
END LOOP;
MIT;
soft parse
java中的同等語句是 PreparedStatement execute()
sql_ := insert into t_hard(id) values(:id) ;
FOR i IN LOOP
EXECUTE IMMEDIATE sql_
USING i;
END LOOP;
MIT;
END;
正確的分頁演算法
SELECT *
FROM (SELECT a * ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= )
WHERE rn > ;
分頁演算法(why not this one)
SELECT a * ROWNUM rn
FROM (SELECT * FROM t_employees ORDER BY first_name) a
WHERE ROWNUM <= AND ROWNUM > ;
分頁演算法(why not this one)
SELECT b *
FROM (SELECT a * ROWNUM rn
FROM t_employees a
WHERE ROWNUM < =
ORDER BY first_name) b
WHERE b rn > ;
OLAP
小計合計
SELECT CASE
WHEN a deptno IS NULL THEN
合計
WHEN a deptno IS NOT NULL AND a empno IS NULL THEN
小計
ELSE
|| a deptno
END deptno
a empno
a ename
SUM(a sal) total_sal
FROM scott emp a
GROUP BY GROUPING SETS((a deptno) (a deptno a empno a ename) ());
分組排序
SELECT a deptno
a empno
a ename
a sal
可跳躍的rank
rank() over(PARTITION BY a deptno ORDER BY a sal DESC) r
密集型rank
dense_rank() over(PARTITION BY a deptno ORDER BY a sal DESC) r
不分組排序
rank() over(ORDER BY sal DESC) r
FROM scott emp a
ORDER BY a deptno a sal DESC;
當前行數據和前/後n行的數據比較
SELECT a empno
a ename
a sal
上面一行
lag(a sal) over(ORDER BY a sal DESC) lag_
下面三行
lead(a sal ) over(ORDER BY a sal DESC) lead_
FROM scott emp a
lishixin/Article/program/Oracle/201311/16728
6. 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
7. 在登陸sql plus時老是出現錯誤提示 ora-12154:tns:無法解析指定的連接標識符
一、原因：pl/sql 每當oracle client中service name發生變化，都會按照client安裝目錄下最新的tnsnames.ora，去作為它的鬧茄讀取文件；如果該文件在卸載oracle client後被手工毀敗刪除或手工改動過後，未在oracle client中修改；則會報「ORA-12514：tns:無法解析指定的連接標識符」錯誤。
二、解決方法：
1、用tnsping 檢測 plSQL登陸時database(資料庫)別名是否可正確解析，如報「TNS-03505：無法解析名稱」，則此database別名有沖突，需更改。
2、手工改動tnsnames.ora後，在oracle client中修改主機名（「對象」--「將資料庫添加到樹」--「從本地的……」，然後把錯誤主機名刪除或從樹中移除）
3、重新打開plSQL，用正確的database別名登陸，即恢復正常
4、如仍有問題，不登陸進入plSQL界面"tools」--"Preferences"--"Oracle"--"Connection"，把"home」（oracle主目錄名）里的數據清空，應用後，重新啟動plSQL即可纖彎顫。
三、附錄：
1、在做以上更改後oracle自帶的sqlPLUS，可能會出現不能正常登陸遠程伺服器的情況；因為其訪問的是database下的tnsnames.ora文件，目錄與client的不一樣，默認為 X（盤符）:oracleproct10.2.0db_1NETWORKADMIN nsnames.ora
2、解決方法：只需將client下tnsnames.ora中定義的主機字元串（service_name）的命令行復制到database 下的tnsnames.ora內，然後保存就可以了。
8. 2020-01-20 oracle中sql如何執行，什麼是硬解析和軟解析
1.語法檢查：檢查 SQL 拼寫是否正確，如果不正確，Oracle 會報語法錯誤。
2.語義檢查：檢查 SQL 中的訪問對象是否存在。比如我們在寫 SELECT 語句的時候，列名寫錯了，系統就會提示錯誤。語法檢查和語義侍槐察檢查的作用是保證 SQL 語句沒有錯誤。
3.許可權檢查：看用戶是否具備訪問該數據的許可權。
4.共享池檢查：共享池（Shared Pool）是一塊內存池，最主要的作用是緩存 SQL 語句和該語句的執行計劃。Oracle 通過檢查共享池是否存在 SQL 語句的執行計劃，來判斷進行軟解析，還是硬解析。那軟解析和硬解析又該怎麼理解呢？在共享池中，Oracle 首先對 SQL 語句進行 Hash 運算，然後根據 Hash 值在庫緩存（Library Cache）中查找，如果存在 SQL 語句的執行計劃，就直接拿來執行，直接進入「執行器」的環節，這就是軟解析。如果沒有找到 SQL 語句和執行計劃，Oracle 就需要創建解析樹進行解析明羨，生成執行計劃，進入「優化器」這個步驟，這就是硬解析。
5.優化器：優化器中就是要進行硬解析，也就老茄是決定怎麼做，比如創建解析樹，生成執行計劃。
6.執行器：當有了解析樹和執行計劃之後，就知道了 SQL 該怎麼被執行，這樣就可以在執行器中執行語句了。
共享池是 Oracle 中的術語，包括了庫緩存，數據字典緩沖區等。我們上面已經講到了庫緩存區，它主要緩存 SQL 語句和執行計劃。而數據字典緩沖區存儲的是 Oracle 中的對象定義，比如表、視圖、索引等對象。當對 SQL 語句進行解析的時候，如果需要相關的數據，會從數據字典緩沖區中提取。
如何避免硬解析，盡量使用軟解析呢？在 Oracle 中，綁定變數是它的一大特色。綁定變數就是在 SQL 語句中使用變數，通過不同的變數取值來改變 SQL 的執行結果。
9. 【基於ORACLE資料庫的SQL語句優化分析】 資料庫查詢語句的優化
【摘要】隨著資料庫應用范圍及規模的不斷擴大，資料庫的性能問題逐漸顯現，優化資料庫有助於維持系統的穩定性以及運行的高效性。本文主要依據筆者在實際工作中的精坦敏拍英，對SQL語句優化的目的、SQL語句優化技術及原則進行全面分析和闡述。
【關鍵詞】ORACLE資料庫；SQL語句；優化
1前言
隨著現代化信息技術的迅猛發展，互聯網應用的日益普及，資料庫技術的影響力越來越大。作為信息系統管理的核心，資料庫的主要操作就是查詢，資料庫的應用效率在很大程度上是由查詢速度決定的，特別是對於規模較大的資料庫而言，查詢速度十分關鍵。查詢速度在SQL語句中佔有很大比重，所以，通過對查詢語句進行優化有助於促進應用系統性能及效率的進一步提升。
2SQL語句優化分析
2.1SQL語句優化的目的
對於一個資料庫而言，在確保設計無誤的前提下，要想避免出現性能問題必須確保其擁有合理的SQL語句拿喚結構。最簡單的資料庫尋找數據路徑是對SQL語句進行調整，ORACLE資料庫性能提升的主要途徑就是對SQL語句進行適當的調整。從本質上講，SQL語句優化就是確保所使用的語句可以被優化器識別，對索引進行有效利用以便控製表掃描的I/O次數，有效防止出現表搜索。用高性能的SQL語句替代低性能的SQL語句，確定最佳的數據查找路徑，盡可能使CPU時間與I/O時間保持平衡是進行優化的主要目的。在對SQL語句進行優化的過程中，以系統需求為依據確定最有可能實現性能提升的語句並進行優化。
2.2SQL語句優化技術及原則
當數據量積累到一定程度之後，對於資料庫全表SQL語句進行一次掃描，若查詢策略較好，一般只用幾秒鍾，但如果SQL語句性能較低，就需要用幾分鍾甚至更多時間。從這點不難看出，SQL語句性能對於查詢速度具有極大的影響，所以，對於應用系統而言，不僅能滿足功能的實現，還要保證SQL語句的質量。
（1）採取適宜的索引。為達到優化查詢的目的，一項重要工作就是確定相適應的索引，並嚴格依照原則加以使用，與此同時，為有效控制I/O競爭，不可以在同一個磁碟中同時建立索引和用戶表空間。
語句1：SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE CUS_NO NOT IN
(SELECT CUS_NO FROM SERVICE);
語句2： SELECT CUS_NO, CUS_NAME FROM CUSTOMER WHERE NOT EXISTS
(SELECT * FROM SERVICE WHERE SERVICE.CUS_NO=CUSTOMER.CUS_NO);
上述兩個語句可以達到一致的查詢結果，對二者進行對比，當執行語句1時，由於ORACLE未利用CUSTOMER 表上CUS_NO索引，所以就會掃描整表，在執行語句2的過讓羨程中，ORACLE所掃描的只是CUSTOMER 表子查詢中的聯合查詢，並且使用了CUS_NO索引，因此，在執行效率方面明顯優於前者。
（2）避免在SELECT子句中出現「*」。ORACLE在進行解析時，需要按照一定順序對「*」進行轉換，該項轉換工作的進行需要對資料庫的數據字典進行查詢，勢必需要花費較多的時間，這樣就會導致較低的效率，所以，要避免在SELECT子句中出現「*」。
（3）如果必要可以利用COMMIT提交事務。ORACLE能夠自動提交DDL語句，而諸如DML等類型的語句的提交則是通過手動方式或者回滾事務實現的。在編寫應用程序的過程中，在操作諸如insert、delete以及update 等較為復雜的語境的時候，利用COMMIT提交事務可以講會話中持有的鎖加以釋放，將存在於緩存中的未經修改的數據塊進行清除，進而將系統資源予以釋放，促進系統性能的進一步提升，因此，如果有必要，可以利用COMMIT對相關事務進行提交。
（4）聯合查詢連接順序的確定。如果查詢操作涉及到多個表，基礎表應當是交叉表，所謂交叉表具體是指被其他表引用的表。連接執行效果在很大程度上受到FROM語句中表的順序的影響，對於FROM中所包含的表，ORACLE解析器進行處理的順序是由右至左，SQL語句中所選擇的基礎表會因優化器的不同而有所區別，在使用CBO的情況下，優化器會對SQL語句中各個表的物理大小以及索引狀態進行檢查，在此基礎上確定一個花費最小的執行路徑；在使用RBO的情況下，如果全部的連接條件均有索引與之相對應，那麼，FROM子句中位置最後面的表就是基礎表。
（5）IN用EXISTS取代。在對數個基礎表查詢過程中，一般需要進行表的連接。因為利用IN的子查詢過程中，ORACLE的掃描對象是全表，因此，出於提高查詢效率目的的考慮，應當將IN用EXISTS取代。
（6）在索引列中不使用計算。當通過對函數進行引用在WHERE子句中進行計算的時候，假如索引列只是函數的一部分，優化器就會針對全表進行掃描，而不會使用索引，所以，在索引列中不能使用函數。
3結語
綜上所述，隨著現代化信息技術的迅猛發展，互聯網應用的日益普及，資料庫技術的影響力越來越大。在信息量迅速激增的形勢下，資料庫優化調整成為當前所面臨的一大關鍵性問題，特別是對規模較大的資料庫而言，及時進行優化的意義更加倍重大。對於資料庫的運行性能而言，最主要的影響因素主要體現在以下幾點：資料庫系統架構的設計是否合理，資源配置是否科學以及SQL語句編寫效率等。筆者從事的是電信企業的運營分析工作，每天都要從資料庫取各種數據，可以說是離不開資料庫，所以在實踐中，我覺得嚴格遵守SQL語句優化原則及方法，並在實踐中及時總結經驗教訓，可以實現對系統響應時間的有效控制，促進運行效率的提升。
參考文獻
[1] 許開宇,胡文驊. 如何提高ORACLE資料庫應用程序的性能[J]. 計算機應用與軟體. 2002(10)
[2] 鄭耀,吳建嵐. 基於Oracle資料庫的語句優化策略[J]. 信息與電腦(理論版). 2011(07)
[3] 高攀,施蔚然. 基於Oracle資料庫的SQL語句優化[J]. 電腦編程技巧與維護. 2010(22)
[4] 鍾小權,葉猛. Oracle資料庫的SQL語句優化[J]. 計算機與現代化. 2011(03)
作者簡介：
王勇軍，男，（1981.1-），吉林通化人，就職於中國聯合網路通信有限公司長春市分公司，通信工程師，本科，研究方向：SQL使用
（作者單位：中國聯合網路通信有限公司長春市分公司）
10. oracle SQL查詢時提示 用戶數據中的connect by 循環 報錯是什麼原因
一般是數據錯誤導致了死循環。
如數據為這樣：
ID 父ID 值
1 2 10
2 1 20
如圖，ID為1的父ID為2，而同時ID為2的父ID是1，這樣的話，就會互相認對方的ID為父ID，就會造成一個死循環，這種錯誤，一般不用修改語句，需要正確檢查數據的正確性。