本篇內(nèi)容介紹了“怎么理解數(shù)據(jù)庫(kù)的初始化參數(shù)cursor_sharing”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！
站在用戶的角度思考問(wèn)題，與客戶深入溝通，找到棗莊網(wǎng)站設(shè)計(jì)與棗莊網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗(yàn)，讓設(shè)計(jì)與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個(gè)性化、用戶體驗(yàn)好的作品，建站類(lèi)型包括：成都做網(wǎng)站、成都網(wǎng)站制作、成都外貿(mào)網(wǎng)站建設(shè)、企業(yè)官網(wǎng)、英文網(wǎng)站、手機(jī)端網(wǎng)站、網(wǎng)站推廣、空間域名、網(wǎng)站空間、企業(yè)郵箱。業(yè)務(wù)覆蓋棗莊地區(qū)。
一、Cursor_sharing 簡(jiǎn)介：
這個(gè)參數(shù)是用來(lái)告訴Oracle在什么情況下可以共享游標(biāo)，即SQL重用。
Cursor_sharing參數(shù)有3個(gè)值可以設(shè)置：
1)、EXACT：通常來(lái)說(shuō)，exact值是Oracle推薦的，也是默認(rèn)的，它要求SQL語(yǔ)句在完全相同時(shí)才會(huì)重用，否則會(huì)被重新執(zhí)行硬解析操作。
2)、SIMILAR：similar是在Oracle認(rèn)為某條SQL語(yǔ)句的謂詞條件可能會(huì)影響到它的執(zhí)行計(jì)劃時(shí)，才會(huì)被重新分析，否則將重用SQL。
3)、FORCE：force是在任何情況下，無(wú)條件重用SQL。
備注：上面所說(shuō)的SQL重用，僅僅是指謂詞條件不同的SQL語(yǔ)句，實(shí)際上這樣的SQL基本上都在執(zhí)行同樣的業(yè)務(wù)操作。
二、在Cursor_sharing參數(shù)值不同的時(shí)對(duì)SQL的影響：
2.1 創(chuàng)建實(shí)驗(yàn)環(huán)境：
----首先創(chuàng)建一張woo表----
WOO@woo> create table woo (id int,name varchar2(10)); Table created. Elapsed: 00:00:00.06
---產(chǎn)生一些數(shù)據(jù)----
WOO@woo> insert into woo values(1,'aa'); 1 row created. Elapsed: 00:00:00.00 WOO@woo> insert into woo values(2,'bb'); 1 row created. Elapsed: 00:00:00.00 WOO@woo> insert into woo values(3,'cc'); 1 row created. Elapsed: 00:00:00.00 WOO@woo> insert into woo values (4,'dd'); 1 row created. Elapsed: 00:00:00.00 WOO@woo> commit; Commit complete. Elapsed: 00:00:00.00 WOO@woo> select * from woo; ID NAME ---------- ---------- 1 aa 2 bb 3 cc 4 dd Elapsed: 00:00:00.01
---創(chuàng)建下面實(shí)驗(yàn)將要用到的三張表----
WOO@woo> create table woo_exact as select * from woo; Table created. Elapsed: 00:00:00.01 WOO@woo> create table woo_similar as select * from woo; Table created. Elapsed: 00:00:00.01 WOO@woo> create table woo_force as select * from woo; Table created. Elapsed: 00:00:00.00
---查看當(dāng)前session的trace文件的路徑----
WOO@woo> SELECT d.Value || '/' || Lower(Rtrim(i.Instance, Chr(0))) || '_ora_' || 2 p.Spid || '.trc' AS "trace_file_name" 3 FROM (SELECT p.Spid 4 FROM V$mystat m, V$session s, V$process p 5 WHERE m.Statistic# = 1 6 AND s.Sid = m.Sid 7 AND p.Addr = s.Paddr) p, 8 (SELECT t.Instance 9 FROM V$thread t, V$parameter v 10 WHERE v.Name = 'thread' 11 AND (v.Value = 0 OR t.Thread# = To_Number(v.Value))) i, 12 (SELECT VALUE 13 FROM V$parameter 14 WHERE NAME = 'user_dump_dest') d; trace_file_name ------------------------------------------------------- /DBSoft/diag/rdbms/woo/woo/trace/woo_ora_37746.trc Elapsed: 00:00:00.01
2.2 cursor_sharing=exact的情況：
WOO@woo> alter session set cursor_sharing=exact; Session altered. Elapsed: 00:00:00.00 WOO@woo> alter session set sql_trace=true; Session altered. Elapsed: 00:00:00.00 WOO@woo> select * from woo_exact where id=1; ID NAME ---------- ---------- 1 aa Elapsed: 00:00:00.00 WOO@woo> select * from woo_exact where id=2; ID NAME ---------- ---------- 2 bb Elapsed: 00:00:00.01 WOO@woo> select * from woo_exact where id=3; ID NAME ---------- ---------- 3 cc Elapsed: 00:00:00.00 WOO@woo> select * from woo_exact where id=1; ID NAME ---------- ---------- 1 aa Elapsed: 00:00:00.00
----從下面的查詢可以看出執(zhí)行了兩次硬解析----
WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_exact where%'; SQL_TEXT --------------------------------------------------------------------------------------- select * from woo_exact where id=1 select * from woo_exact where id=3 select * from woo_exact where id=2 Elapsed: 00:00:00.05 NAME VALUE ---------------------------------------------------------------- ---------- ADG parselock X get attempts 0 ADG parselock X get successes 0 parse time cpu 326 parse time elapsed 307 parse count (total) 56211 parse count (hard) 1681 parse count (failures) 10 parse count (describe) 0 8 rows selected.
cursor_sharing=similar的情況：
WOO@woo> alter session set cursor_sharing=similar; Session altered. Elapsed: 00:00:00.00 WOO@woo> alter system flush shared_pool; System altered. Elapsed: 00:00:00.13 WOO@woo> select * from woo_similar where id=1; ID NAME ---------- ---------- 1 aa Elapsed: 00:00:00.01 WOO@woo> select * from woo_similar where id=4; ID NAME ---------- ---------- 4 dd Elapsed: 00:00:00.00 WOO@woo> select * from woo_similar where id=8; no rows selected Elapsed: 00:00:00.00
----在這里可以看到執(zhí)行兩次SQL查詢，只進(jìn)行了一個(gè)硬解析----
WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_similar where %'; SQL_TEXT -------------------------------------------------------------------------------------------------------- select * from woo_similar where id=:"SYS_B_0" Elapsed: 00:00:00.02 WOO@woo> select name,value from v$sysstat where name like '%parse%'; NAME VALUE ---------------------------------------------------------------- ---------- ADG parselock X get attempts 0 ADG parselock X get successes 0 parse time cpu 374 parse time elapsed 352 parse count (total) 57024 parse count (hard) 2006 parse count (failures) 10 parse count (describe) 0 8 rows selected. Elapsed: 00:00:00.00 WOO@woo>
對(duì)于SIMILAR的情況，如果CBO發(fā)現(xiàn)被綁定變量的謂詞還有其他的執(zhí)行計(jì)劃可以選擇時(shí)，如果謂詞條件的值有變化，就將會(huì)產(chǎn)生一個(gè)新的子游標(biāo)，而不是重用之前的SQL；如果謂詞沒(méi)有其他的執(zhí)行計(jì)劃可選擇，則忽略謂詞的值，重用之前的SQL。
上面的例子還不能足以說(shuō)明該情況，接著下面的模擬：
cursor_sharing=force的情況
WOO@woo> alter session set cursor_sharing=force; Session altered. Elapsed: 00:00:00.00 WOO@woo> alter system flush shared_pool; System altered. Elapsed: 00:00:00.07 WOO@woo> alter session set sql_trace=true; Session altered. Elapsed: 00:00:00.02 WOO@woo> select * from woo_force where id=1; ID NAME ---------- ---------- 1 aa Elapsed: 00:00:00.00 WOO@woo> select * from woo_force where id=4; ID NAME ---------- ---------- 4 dd Elapsed: 00:00:00.00 WOO@woo> select * from woo_force where id=1; ID NAME ---------- ---------- 1 aa Elapsed: 00:00:00.00
----從下面的查詢中可以看出只進(jìn)行了一次硬解析，而且使用了綁定變量----
WOO@woo> select sql_text from v$sql where sql_text like 'select * from woo_force where %'; SQL_TEXT ------------------------------------------------------------------------------------------- select * from woo_force where id=:"SYS_B_0" Elapsed: 00:00:00.02 WOO@woo> select name,value from v$sysstat where name like '%parse%'; NAME VALUE ---------------------------------------------------------------- ---------- ADG parselock X get attempts 0 ADG parselock X get successes 0 parse time cpu 379 parse time elapsed 355 parse count (total) 57385 parse count (hard) 2145 parse count (failures) 10 parse count (describe) 0 8 rows selected. Elapsed: 00:00:00.01
總結(jié)：force是在任何情況下，無(wú)條件重用SQL。
“怎么理解數(shù)據(jù)庫(kù)的初始化參數(shù)cursor_sharing”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！
重慶分公司
重慶分公司
