OracleRDBMSSQL語句處理流程

OracleRDBMS

SQL語句處理流程趙元杰中程在線(北京)科技2021.82/6/20251內(nèi)容提要10g9iR22/6/20252Oracle數(shù)據(jù)庫設計與性能共享池概念回憶：Sharedpool是SGA的一局部，它是用來存放由應用用戶發(fā)出的SQL和PL/SQL語句；SharedPool由3個部件組成：庫緩存(librarycache)-用來存放應用用戶剛發(fā)出的SQL及PL/SQL語句、存儲過程、函數(shù)、包、觸發(fā)器、同義詞PL/SQL包及JAVA類庫等對象信息；數(shù)據(jù)字典(DataDictionaryCache)-表、列等信息存儲在該區(qū)；用戶全局區(qū)〔UserGlobalArea〕-使用共享效勞器選件時，用戶全局區(qū)才有用，用戶全局區(qū)用于存放用戶會話的應用2/6/20253Oracle數(shù)據(jù)庫設計與性能Oracle共享池庫高速緩存工作方式：用來緩存共享的SQLstatements和PL/SQL塊，這些可以給所有的連接用戶共享由LRU(最小使用最先淘汰算法)來管理不是FIFO(先進先出)算法管理！！Oracle如何知道你的SQL語句是否在里面先通過一個hash算法將Statementtext運算成一個hash數(shù)值然后通過這個hash值在共享池中查找2/6/20254Oracle數(shù)據(jù)庫設計與性能SQL語句分析SQL語法分析(SyntacticalAnalysis)語法正確性分析，如關鍵字拼寫等語義分析(SemanticAnalysis)當前用戶訪問表是否有權限等存入共享池(sharedpool)當前SQL語句語法和語義正確后存儲在SGA的共享詞內(nèi)，可供當前會話再次使用或其他用戶使用2/6/20255Oracle數(shù)據(jù)庫設計與性能SQL語句的HASH值SQL語句在語義分析后，都產(chǎn)生一個叫SQLHsahValues的值SQLHsahValues是由Hash函數(shù)產(chǎn)生的，在數(shù)據(jù)庫中相同的SQL語句具有相同的Hash值Oracle系統(tǒng)通過SQLHash值來判斷某個語句是否出現(xiàn)過運行過程中，可從V$SQLTEXT查詢到SQL語句及其Hash值：SQL>SELECTa.username"用戶名",a.sid"SID號",a.serial#"序列號",2b.id1"ID1",c.sql_text"SQL語句"3FROMv$sessiona,v$lockb,v$sqltextc4WHEREa.lockwaitisnotnullANDa.lockwait=b.kaddr5anda.sql_address=c.address6*anda.sql_hash_value=c.hash_value;

用戶名SID號序列號ID1SQL語句-------------------------------------------------------------------------------------INMON1857968196685updatetst2setsal=98765wherename='zyj'2/6/20256Oracle數(shù)據(jù)庫設計與性能SQL語句重新加載如果在SHAREDPOOL中找不到當前會話所發(fā)出的SQL語句，那么Oracle系統(tǒng)重新加載重新加載的SQL語句要做下面的工作：語法正確性分析如關鍵字拼寫等2/6/20257Oracle數(shù)據(jù)庫設計與性能2/6/20258Oracle數(shù)據(jù)庫設計與性能綁定變量綁定變量就是將類似的SQL語句的變化局部采用變量替代，從而減少Oracle系統(tǒng)類似語句的重新分析(硬分析)如果采用綁定變量，系統(tǒng)只做軟分析工作SQL語句硬分析原因：沒有共享的SQL(沒有使用綁定變量),共享SQL重新硬分析了〔查詢V$sqlarea的parse_calls和excutions字段，如果某個SQL對應的parse_calls接近excutions數(shù)，說明該SQL經(jīng)常被重新硬分析〕注意：并不是綁定變量就是最好2/6/20259Oracle數(shù)據(jù)庫設計與性能共享光標(CURSOR_SHARING)綁定變量原因：沒有共享的SQL硬分析注意：并不是綁定變量就是最好2/6/202510Oracle數(shù)據(jù)庫設計與性能SQL語句執(zhí)行方案cost指cbo中這一步所消耗的資源，這個值是相對值card是指方案中這一步所處理的行數(shù)bytes指cbo中這一步所處理所有記錄的字節(jié)數(shù)，是估算出來的一組值。2/6/202511Oracle數(shù)據(jù)庫設計與性能內(nèi)容提要10g9iR22/6/202512Oracle數(shù)據(jù)庫設計與性能1．發(fā)出查詢余款的SQL語句，如：先查詢帳戶余額：SQL語句通過SGA得到效勞器進程；效勞器進程檢查共享池中有無該條語句，無該語句那么將放置共享池中并準備運行；執(zhí)行SQL語句，把存放有余款的數(shù)據(jù)塊從數(shù)據(jù)文件中讀到SGA的數(shù)據(jù)高速緩沖區(qū)；顯示結果，比方余款為$325。Selectaccount_balanceFrombanktableWhereaccount_number='111222333'Andaccount_type='SAVINGS'；一個銀行業(yè)務處理流程2/6/202513Oracle數(shù)據(jù)庫設計與性能2．取款$25：SQL語句為：取款就是修改當前帳戶的余額：1.客戶進程通過SGA把SQL語句傳給效勞器進程；2.效勞器進程查找有無該條語句，有執(zhí)行；3.分析SQL語句并存入共享池；4.執(zhí)行SQL語句；5.要處理的數(shù)據(jù)在數(shù)據(jù)高速緩沖區(qū)嗎？是轉7；6.從數(shù)據(jù)文件中讀數(shù)據(jù)塊到數(shù)據(jù)高速緩沖區(qū)；7.在回滾段中記錄原來的數(shù)值〔$325〕；8.在重做日志中生成該事務的一個拷貝；9.將數(shù)據(jù)高速緩沖區(qū)中的余額改為$300；10.銀行柜員機通過SGA發(fā)出工作完成信號〔提交〕:11.在重做日志中記錄已完成事務；12.去除回滾段中的恢復信息〔UndoInformation〕；13.顧客取錢完成。UpdateBank_tablesetaccount_balanct=300Whereaccount_number='111222333'Andaccount_type='SAVINGS'；一個銀行業(yè)務處理流程2/6/202514Oracle數(shù)據(jù)庫設計與性能內(nèi)容提要10g9iR22/6/202515Oracle數(shù)據(jù)庫設計與性能SQL語句從發(fā)出到執(zhí)行的主要流程： SQL語句處理流程2/6/202516Oracle數(shù)據(jù)庫設計與性能SQL語句執(zhí)行根本流程： SQL語句處理流程2/6/202517Oracle數(shù)據(jù)庫設計與性能多表連接的SQL語句執(zhí)行流程： SQL語句處理流程2/6/202518Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-三項主要工作分析(Parsing)優(yōu)化(Optimization)執(zhí)行(Execution)2/6/202519Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-分析語法分析(SyntacticalAnalysis)語義分析(SemanticAnalysis)存入共享池(sharedpool)2/6/202520Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程:1.語法分析各表示符號查詢/建議語法selectename,job,d.deptno,dnamefromempedeptdwheree.deptno=d.deptnoande.job="CLERK“orderdeptnoSELECT<listofexpressions>FROM<listofdatasourceobjects>WHERE<condition>ORDERBY<listofexpressions>2/6/202521Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程:2.語義分析解決引用關系(鎖-latches)檢驗權限SELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job=‘CLERK’ORDERBYdeptnoSCOTT.EMP(table)ENAMEJOBDEPTNO…SCOTT.DEPT(table)DEPTNODNAME…SchemaSCOTTDataDictionary2/6/202522Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程:3a.進入共享池轉化為HashSQL并共享池中查到SELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job=‘CLERK’ORDERBYd.deptno628938992HASHSystemGlobalAreaSharedPoolSharedSQLAreaUser1User2User3OracleInstance42/6/202523Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程:3a.共享池工作如果共享池沒找到就加載到共享池SELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job=‘CLERK’ORDERBYd.deptno628938992V$SQL(DataDictionaryviewintoSQLinSharedPool)HASH_VALUESQL_TEXT---------------------------------------------------------------619739417SELECTCOUNT(*)FROMUSER_POLICIESVWHEREV.OB...619739417SELECTCOUNT(*)FROMUSER_POLICIESVWHEREV.OB...628938992SELECTename,job,e.deptno,dnameFROMempe,d...636388251insertintoccol$(con#,obj#,intcol#,pos#,col#)val......HASH5,62/6/202524Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程:優(yōu)化FinalExecutionPlan/RowSourceGenerator

0SELECTSTATEMENTOptimizer=CHOOSE10MERGEJOIN21SORT(JOIN)32TABLEACCESS(FULL)OF'DEPT'41SORT(JOIN)54TABLEACCESS(FULL)OF'EMP'SELECTename,job,d.deptno,dname…DataDictionary評估不同訪問路徑(包括latches)確定最正確路徑，并保持在共享池中OptimizerPlanA:Cost=5

PlanB:Cost=3

PlanC:Cost=10

2/6/202525Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程:執(zhí)行在私有SQL區(qū)分配光標[綁定變量-Bindvalues]運行光標INSERT/UPDATE/DELETE鎖/修改SELECT識別活動數(shù)據(jù)集(active-set)從光標中返回數(shù)據(jù)關閉光標2/6/202526Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-要點SQL語句的執(zhí)行要經(jīng)過下面的步驟：解析SQL-在共享池中找該語句；檢查語法；執(zhí)行和返回結果；解析SQL：檢查平安性；檢查SQL語法；可能SQL語句重寫。執(zhí)行：創(chuàng)立執(zhí)行方案；捆綁執(zhí)行方案；執(zhí)行方案執(zhí)行；取出結果。顯示結果-包括排序、轉換和重格式化；轉換結果集-對內(nèi)置函數(shù)的結果進行轉換。2/6/202527Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-要點SQL語句的解析：接收SQL到共享池；檢查語法等；重新書寫查詢(QueryRewrite)：如果創(chuàng)立了實體視圖(enablequeryrewrite)；Altersessionsetquery_rewrite_enabled=true；該SQL語句采用查詢重寫(見實體視圖)。Oracle的cursor_sharing參數(shù)：Force--除變量外語句完全相同使用同一個光標；Exact(默認)--語句完全相同使用同一個光標；9i/10g/11g增加SIMILAR參數(shù)，強制共享只有文字不同的語句解釋方案。2/6/202528Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-要點生成執(zhí)行方案：優(yōu)化器職能是決定最有效方法為查詢效勞；查詢速度和查詢效率：最大速度(first_rows)重點是最短時間返回結果；最小的資源(all_rows)使用最少的機器資源和磁盤資源；優(yōu)化器模式由optimizer_mode參數(shù)決定：CBO-Oracle通過運行analyze分析的統(tǒng)計數(shù)據(jù)；RBO-Oracle使用數(shù)據(jù)字典中的索引的信息；Oracle的optimizer_mode=choose；沒有統(tǒng)計數(shù)據(jù)，那么使用RBO，否那么使用CBO；Oracle10g默認optimizer_mode=ALL_ROWS。2/6/202529Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-性能統(tǒng)計(1)TKPROFSELECTename,job,d.deptno,dnameFROMempe,deptdWHEREe.deptno=d.deptnoANDe.job='CLERK'ORDERBYd.deptnocallcountcpuelapsed-------------------------------Parse10.010.01Execute10.000.00Fetch20.000.00-------------------------------total40.010.0122/6/202530Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-性能統(tǒng)計(2)SELECTCOUNT(*)FROMbig_user_table

22739callcountcpuelapsed-------------------------------Parse10.070.08Execute10.000.00Fetch20.951.12-------------------------------total41.021.212/6/202531Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-性能統(tǒng)計(3)SELECTusernameFROMbig_user_tableWHEREid=100callcountcpuelapsed-------------------------------Parse10.080.07Execute10.000.00Fetch20.000.00-------------------------------total40.080.07(分析工作代價相當高.)2/6/202532Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-共享池快速分析最優(yōu)保持執(zhí)行方案SELECTusernameFROMbig_user_tableWHEREid=100298300393V$SQL(DataDictionaryviewintoSQLinSharedPool)HASH_VALUESQL_TEXT---------------------------------------------------------------2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...298300393SELECTusernameFROMbig_user_tableWHEREobject...4049165760selectorder#,columns,typesfromaccess$whered_o......HASH2/6/202533Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-硬分析與軟分析硬分析與軟分析〔Hard-Parsevs.Soft-Parse〕：HardParse-對SQL語句進行語法檢查和語義分析，并生成執(zhí)行方案和執(zhí)行編碼；SoftParse-對SQL語句進行語法檢查和語義分析2/6/202534Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-軟分析更好--AfteraddedtoSharedPool--executed4timesina*new*session:SELECTusernameFROMbig_user_tableWHEREobject_id=100callcountcpuelapsed-------------------------------Parse40.000.00Execute40.000.00Fetch80.010.00-------------------------------total160.010.00軟分析代價較小2/6/202535Oracle數(shù)據(jù)庫設計與性能SQL執(zhí)行過程-軟分析缺乏SELECTusernameFROMbig_user_tableWHEREid=100;SELECTusernameFROMbig_user_tableWHEREobject_id=250;298300393V$SQL(DataDictionaryviewintoSQLinSharedPool)HASH_VALUESQL_TEXT---------------------------------------------------------------2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...2591785020selectobj#,type#,ctime,mtime,stime,status,dataobj...1737037929SELECTobject_nameFROMall_objectsWHEREobjec...298300393SELECTobject_nameFROMall_objectsWHEREobject...4049165760selectorder#,columns,typesfromaccess$whered_o......1737037929HASHHASH2/6/202536Oracle數(shù)據(jù)庫設計與性能在共享池中重用SQL語句：當SQL語句被傳遞給Oracle處理時，關鍵是重復使用已經(jīng)在共享池中的語句，而不是讓Oracle在接受語句時去準備新的語句；與共享池中的語句相一致的語句，就重用共享池中的語句；Oracle提供在數(shù)據(jù)庫中存儲代碼的能力，當應用系統(tǒng)開始運行時，從數(shù)據(jù)庫中讀取代碼〔可用PL/SQL語句編制〕并傳遞到共享池中去處理。從數(shù)據(jù)庫中取出的代碼是編譯過的并駐留在共享池中；利用數(shù)據(jù)庫中存儲的程序代碼設計應用系統(tǒng)，檢查所有的事務處理以及主要的通用的過程，研究現(xiàn)有的應用系統(tǒng)并把主要的處理程序轉換為數(shù)據(jù)庫中存儲的程序代碼。在Oracle中存儲代碼可以通過過程、程序包、函數(shù)、觸發(fā)器等來實現(xiàn)。

SQL代碼的重用2/6/202537Oracle數(shù)據(jù)庫設計與性能內(nèi)容提要10g9iR2一個銀行業(yè)務處理流程理解SQL執(zhí)行過程關于CURSOR_SHARING參數(shù)

監(jiān)視SQLArea的SQL語句調(diào)整SQL步驟2/6/202538Oracle數(shù)據(jù)庫設計與性能CURSOR_SHARING參數(shù)與SHRAED_POOL_SIZE有關什么是SHARED_POOLSHARED_POOL分為兩個局部第一局部為庫高速緩存第二局部為字典高速緩存PL/SQL程序分析后存放在庫高速緩存共享池由SHARED_POOL_SIZE參數(shù)設置2/6/202539Oracle數(shù)據(jù)庫設計與性能CURSOR_SHARING參數(shù)-不同SQLselectname,addressfromapp.employee;selectname,addressfromapp.employee;selectname,addressfromemployee;selectaddressfromempwherename=‘Emke,Larry’;selectaddressfromempwherename=‘Drake,Rick’;selectaddressfromempwherename=:EMP;selectsysdatefromdual;selectto_char(sysdate,‘dd-mon-yy’)fromdual;2/6/202540Oracle數(shù)據(jù)庫設計與性能CURSOR_SHARING參數(shù)CURSOR_SHARING可能的值:FORCEEXACT(default)SIMILAR(Oracle9i/10g/11g)CURSOR_SHARING有三種修改:ALTERSYSTEMALTERSESSIONSPFILE(或INITsid.ora)CURSOR_SHARING_EXACT提示2/6/202541Oracle數(shù)據(jù)庫設計與性能CURSOR_SHARING參數(shù)8iR2開始引入cursor_sharing參數(shù)8i設置cursor_sharing=FORCE和EXACT(默認)9i增加SIMILAR參數(shù)值默認值是EXACT-它只允許完全相同文本的語句共享一個游標。這是早期版本的行為；SIMILAR參數(shù)值使相似語句共享同樣的游標，而不危及執(zhí)行方案的平安。例如：只有最優(yōu)共享語句共享游標；FORCE會強迫Oracle對相似語句共享游標，但存在非最優(yōu)執(zhí)行方案的風險，如，最優(yōu)共享和非最優(yōu)共享語句會共享同一個游標。SQL>SELECT*FROMMYTABLEWHERENAME='tom'SQL>SELECT*FROMMYTABLEWHERENAME='turner'2/6/202542Oracle數(shù)據(jù)庫設計與性能CURSOR_SHARING_EXACT提示CURSOR_SHARING_EXACT提示被用于在語句級控制游標共享；這個標記類似于初始化參數(shù)cursor_sharing被設置為EXACT，并屏蔽原來的初始化參數(shù)它導致語句共享采用精確匹配構建的游標。下面例子〔見下一頁)：2/6/202543Oracle數(shù)據(jù)庫設計與性能CURSOR_SHARING_EXACT提示CURSOR_SHARING_EXACT例子下面例子使用提示：ALTERSYSTEMSETcursor_sharing='SIMILAR'SCOPE=BOTH;…--astheclientruntwosimilarSQLstatementsSELECTlatitudeFROMuwclass.serversWHEREsrvr_id=1;SELECTlatitudeFROMuwclass.serversWHEREsrvr_id=2;SELECTlatitudeFROMuwclass.serversWHEREsrvr_id=3;…SELECTaddress,child_address,sql_text,sql_idFROMgv$sqlWHEREsql_fulltextLIKE'%uwclass%';SELECT/*+CURSOR_SHARING_EXACT*/latitudeFROMuwclass.serversWHEREsrvr_id=3;SELECTaddress,child_address,sql_text,sql_idFROMgv$sqlWHEREsql_fulltextLIKE'%uwclass%';……2/6/202544Oracle數(shù)據(jù)庫設計與性能內(nèi)容提要10g9iR2一個銀行業(yè)務處理流程理解SQL執(zhí)行過程關于CURSOR_SHARING參數(shù)

監(jiān)視SQLArea的SQL語句調(diào)整SQL步驟2/6/202545Oracle數(shù)據(jù)庫設計與性能監(jiān)視SQLArea的SQL語句V$SYSSTAT–顯示OracleCPU所有會話情況V$SESSTAT–顯示每個會話對OracleCPU使用情況V$SQLAREA–顯示目前運行的SQL語句根本情況2/6/202546Oracle數(shù)據(jù)庫設計與性能監(jiān)視SQLArea的SQL語句查詢過分分析的語句:高的百分比說明CPU花在分析語句上而不是執(zhí)行上.PL/SQL程序應該采用綁定變量；加大SHARED_POOL配置SELECTs1.value/s2.value*100FROMv$sysstats1,v$sysstats2WHERE=‘parsetimecpu’AND=‘cpuusedbythissession’;2/6/202547Oracle數(shù)據(jù)庫設計與性能監(jiān)視SQLArea的SQL語句查詢頻繁重分析的語句:如果分析接近執(zhí)行數(shù),那么說明每個語句在執(zhí)行時都被分析一次.SELECTsql_text,parse_calls,executionsFROMv$sqlareaORDERBYparse_callsdesc;2/6/202548Oracle數(shù)據(jù)庫設計與性能監(jiān)視SQLArea的SQL語句下面查詢結果應該比較低.表示語句不用重分析就可執(zhí)行.SELECTs1.value/s2.valueFROMv$sysstats1,v$sysstats2WHERE=‘parsecount(hard)’AND=‘executecount’;2/6/202549Oracle數(shù)據(jù)庫設計與性能監(jiān)視SQLArea的SQL語句本地頻繁分析的語句:如果存在很高的類似的語句,可采用綁定變量.SELECTsubstr(sql_text,1,40),count(*)FROMv$sqlareaGROUPBYsubstr(sql_text,1,40)HAVINGcount(*)>3ORDERBY2desc;2/6/202550Oracle數(shù)據(jù)庫設計與性能監(jiān)視SQLArea的SQL語句能從緩沖區(qū)得到哪些SQL語句在使用CPU50000是一個隨意給定的點.用EXPLAIN或跟蹤可得到.SELECTbuffer_gets,executions,buffer_gets/executionsratio,sql_text,address,hash_valueFROMv$sqlareaWHEREbuffer_gets>50000ANDexecutions>0ORDERBY3desc;2/6/202551Oracle數(shù)據(jù)庫設計與性能監(jiān)視SQLArea的SQL語句找出最消耗CPU的會話:注意：某時間點都會找出最差的語句(相對)SELECTss.username,v.value,ss.serial#,ss.logon_time,ss