关于sharedpool的深入探讨(四)

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

关于sharedpool的 深入探讨(四)

link:

http://www.eygle.com/internal/shared_pool-4.htm    
 


我们进一步来讨论一下sharedpool的处理:

先进行相应查询，获得测试数据:

 

[oracle@jumperudump]$sqlplus"/assysdba"SQL*Plus:Release9.2.0.3.0-ProductiononThuAug2610:21:542004Copyright(c)1982,2002,Corporation.Allrightsreserved.Connectedto:Oracle9iEnterpriseEditionRelease9.2.0.3.0-ProductionWiththePartitioning,OLAPandOracleDataMiningoptionsJServerRelease9.2.0.3.0-ProductionSQL>connecteygle/eygleConnected.SQL>createtableempasselect*fromscott.emp;Tablecreated.SQL>SQL>connect/assysdbaConnected.SQL>startupforce;ORACLEinstancestarted.TotalSystemGlobalArea47256168bytesFixedSize451176bytesVariableSize29360128bytesDatabaseBuffers16777216bytesRedoBuffers667648bytesDatabasemounted.Databaseopened.SQL>setlinesize120SQL>connectscott/tigerConnected.SQL>select*fromemp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------7369SMITHCLERK790217-DEC-80800208888EYGLEMANAGER11-AUG-04999910107566JONESMANAGER783902-APR-812975207698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756619-APR-873000207839KINGPRESIDENT17-NOV-815000107876ADAMSCLERK778823-MAY-871100207900JAMESCLERK769803-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-8213001011rowsselected.SQL>connecteygle/eygleConnected.SQL>select*fromemp;EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO------------------------------------------------------------------------------7369SMITHCLERK790217-DEC-80800208888EYGLEMANAGER11-AUG-04999910107566JONESMANAGER783902-APR-812975207698BLAKEMANAGER783901-MAY-812850307782CLARKMANAGER783909-JUN-812450107788SCOTTANALYST756619-APR-873000207839KINGPRESIDENT17-NOV-815000107876ADAMSCLERK778823-MAY-871100207900JAMESCLERK769803-DEC-81950307902FORDANALYST756603-DEC-813000207934MILLERCLERK778223-JAN-8213001011rowsselected.SQL>selectSQL_TEXT,VERSION_COUNT,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx')HEX,ADDRESS2fromv$sqlareawheresql_textlike'select*fromemp%';SQL_TEXTVERSION_COUNTHASH_VALUEHEXADDRESS--------------------------------------------------------------select*fromemp226487075579de011e552D9EA28SQL>selectsql_text,username,ADDRESS,HASH_VALUE,to_char(HASH_VALUE,'xxxxxxxxxx')HEX_HASH_VALUE,CHILD_NUMBER,CHILD_LATCH2fromv$sqla,dba_usersbwherea.PARSING_USER_ID=b.user_idandsql_textlike'select*fromemp%';SQL_TEXTUSERNAMEADDRESSHASH_VALUEHEX_HASH_VACHILD_NUMBERCHILD_LATCH------------------------------------------------------------------------------------------------------select*fromempSCOTT52D9EA2826487075579de011e501select*fromempEYGLE52D9EA2826487075579de011e511注意:这里我们可以看出v$sqlarea和v$sql两个视图的不同之处v$sql中为每一条sql保留一个条目，而v$sqlarea中根据sql_text进行groupby,通过version_count计算子指针的个数.我们注意到,这两条sql语句因为其代码完全相同,所以其ADDRESS,HASH_VALUE也完全相同.这就意味着,这两条sql语句在sharedpool中的存储位置是相同的(尽管其执行计划可能不同),代码得以共享.在此过程中Oracle完成sql解析的第一个步骤:语法解析Oracle进行语法检查时遵循自右向左,自下向上的原则,如果发现语法错误就马上返回错误.语法检查通过以后,Oracle将sql文本转换为相应的ASCII数值,然后根据数值通过Hash函数计算其HASH_VALUE在sharedpool中寻找是否存在相同的sql语句,如果存在,则进入下一步骤;如果不存在则尝试获取sharedpoollatch请求内存,存储该sql代码在这里有一个问题需要说明一下:因为大小写字母的ascii值是不同的,所以Oracle会把大小写不同的代码作为不同的sql来处理,我们看一下测试:SQL>select*fromscott.dept;DEPTNODNAMELOC-------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONSQL>select*fromscott.DEPT;DEPTNODNAMELOC-------------------------------------10ACCOUNTINGNEWYORK20RESEARCHDALLAS30SALESCHICAGO40OPERATIONSBOSTONSQL>colsql_textfora30SQL>selectsql_text,hash_valuefromv$sqlwheresql_textlike'select*fromscott%';SQL_TEXTHASH_VALUE----------------------------------------select*fromscott.DEPT4096614922select*fromscott.dept2089404358我们注意到,仅只大小写的不同使得原本相同的sql语句变成了两条"不同的代码"所以从这里我们可以看出,sql的规范编写非常重要.完成这一个步骤以后,Oracle需要进行的是语义分析:在此步骤中,Oracle需要验证对象是否存在,相关用户是否具有权限,引用的是否是相同的对象...对于我们第一个查询,实际上emp表来自不同的用户,那么sql的执行计划也就不同了当然影响sql执行计划的因素还有很多,包括优化器模式等SQL>selecta.*,to_char(to_hash,'xxxxxxxxxx')Hex_HASH_VALUE2fromV$OBJECT_DEPENDENCYawhereto_name='EMP';FROM_ADDFROM_HASHTO_OWNERTO_NAMETO_ADDRETO_HASHTO_TYPEHEX_HASH_VA-------------------------------------------------------------------------------------------------52D9EA282648707557SCOTTEMP52D9DEBC8282280102315dc1aa52D9EA282648707557EYGLEEMP52D82E58193049145327310f63dSQL>ALTERSESSIONSETEVENTS'immediatetracenameLIBRARY_CACHElevel1';Sessionaltered.SQL>ALTERSESSIONSETEVENTS'immediatetracenameLIBRARY_CACHElevel2';Sessionaltered.SQL>ALTERSESSIONSETEVENTS'immediatetracenameLIBRARY_CACHElevel4';Sessionaltered.SQL>ALTERSESSIONSETEVENTS'immediatetracenameLIBRARY_CACHElevel8';Sessionaltered.SQL>ALTERSESSIONSETEVENTS'immediatetracenameLIBRARY_CACHElevel16';Sessionaltered.SQL>ALTERSESSIONSETEVENTS'immediatetracenameLIBRARY_CACHElevel32';Sessionaltered.SQL>ALTERSESSIONSETEVENTS'immediatetracenameLIBRARY_CACHElevel100';Sessionaltered. 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>