tag:blogger.com,1999:blog-2257062243512934022.post2724396489490612599..comments2023-10-31T18:51:23.836+05:30Comments on Sachin Arora's Blog: CURSOR_SHARING - Do we use it?Sachinhttp://www.blogger.com/profile/04551992629821526364noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-2257062243512934022.post-70728175222451406052010-02-27T12:50:47.650+05:302010-02-27T12:50:47.650+05:30Thanks
Really i appriciating your knowledge.........Thanks<br /><br />Really i appriciating your knowledge............reg Oracle conceptUnknownhttps://www.blogger.com/profile/11728637747900531099noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-11109492130853604992008-07-19T20:27:00.000+05:302008-07-19T20:27:00.000+05:30Thanks Sachin. It makes more sense after i read th...Thanks Sachin. It makes more sense after i read the Tom's linkAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-51005489517671400212008-07-19T00:56:00.000+05:302008-07-19T00:56:00.000+05:30I feel you are ALMOST right. However there are few...I feel you are ALMOST right. However there are few reasons - why i feel i still have some point to shared<BR/><BR/>For that I advice you to read:<BR/>" For example, if the predicate WHERE X=6 implies that I would want to use a full scan but the predicate WHERE X=5 implies that I would want to use an index range scan, the database would recognize that and set up different plans for me. In the case of different plans, you mark the bind variable as unsafe and add its value to the signature of the query, so to reuse this cursor, you must not only have the same SQL statement but also the same value for that particular bind variable."<BR/><BR/>Source: http://www.oracle.com/technology/oramag/oracle/06-jan/o16asktom.html<BR/><BR/>It keeps the value of the bind variable in the signature and only allows to use the same cursor space only if the same bund value is used.<BR/><BR/>Thx,<BR/>SachinSachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-33341348019719843012008-07-18T19:06:00.000+05:302008-07-18T19:06:00.000+05:30Thanks for your reply Sachin. I agree with you. I ...Thanks for your reply Sachin. I agree with you. I should not have used count(*). <BR/>Now i am using column name object_name. This is not indexed.<BR/><BR/><BR/>SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'MWSADMIN',TABNAME => 'TEST',ESTIMAT<BR/>E_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);<BR/><BR/>PL/SQL procedure successfully completed.<BR/><BR/>SQL> alter system flush shared_pool;<BR/><BR/>System altered.<BR/><BR/>SQL> alter session set cursor_sharing='SIMILAR';<BR/><BR/>Session altered.<BR/><BR/>SQL> select count(object_name) from test where status='GOOD';<BR/><BR/>COUNT(OBJECT_NAME)<BR/>------------------<BR/> 1<BR/>Execution Plan<BR/>----------------------------------------------------------<BR/> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte<BR/> s=24)<BR/><BR/> 1 0 SORT (AGGREGATE)<BR/> 2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt<BR/> es=13557840)<BR/><BR/><BR/>SQL> select count(object_name) from test where status='BAD';<BR/><BR/>COUNT(OBJECT_NAME)<BR/>------------------<BR/> 1<BR/><BR/><BR/>Execution Plan<BR/>----------------------------------------------------------<BR/> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte<BR/> s=24)<BR/><BR/> 1 0 SORT (AGGREGATE)<BR/> 2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt<BR/> es=13557840)<BR/><BR/><BR/>SQL> select count(object_name) from test where status='VALID';<BR/><BR/>COUNT(OBJECT_NAME)<BR/>------------------<BR/> 1134590<BR/><BR/><BR/>Execution Plan<BR/>----------------------------------------------------------<BR/> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte<BR/> s=24)<BR/><BR/> 1 0 SORT (AGGREGATE)<BR/> 2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt<BR/> es=13557840)<BR/><BR/>SQL> select count(object_name) from test where status='INVALID';<BR/><BR/>COUNT(OBJECT_NAME)<BR/>------------------<BR/> 4096<BR/><BR/><BR/>Execution Plan<BR/>----------------------------------------------------------<BR/> 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=1684 Card=1 Byte<BR/> s=24)<BR/><BR/> 1 0 SORT (AGGREGATE)<BR/> 2 1 TABLE ACCESS (FULL) OF 'TEST' (Cost=1684 Card=564910 Byt<BR/> es=13557840)<BR/><BR/><BR/>SQL> select sql_text from v$sql where sql_text like '%select count(object_name) from test <BR/>where status=%';<BR/><BR/>SQL_TEXT<BR/>--------------------------------------------------------------------------------<BR/>select count(object_name) from test where status=:"SYS_B_0"<BR/>EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t<BR/>est where status='VALID'<BR/><BR/>EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t<BR/>est where status='BAD'<BR/><BR/>EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t<BR/>est where status='INVALID'<BR/><BR/>EXPLAIN PLAN SET STATEMENT_ID='PLUS6521699' FOR select count(object_name) from t<BR/>est where status='GOOD'<BR/><BR/><BR/>For some reason, optimizer is using full table scan for all queries. It should not use<BR/>full table scan. But it is using Full tablescan. That is okay.<BR/><BR/>But here is my whole point. My understanding for cursor_sharing=similar is,<BR/><BR/>optimizer decides to use the new plan or exisitng plan based on the statistics and <BR/>histogram. Reusing the plan not only based on the literal values. In the below<BR/>paragrah, you are saying that optimizer will ensure the reusablity of the plan only in case<BR/>the same literal is provided. My statement is, oracle will resuse the plan<BR/>even if the use the different literal. If both the sql has different literal and<BR/>it returns exact/similar number of records, then optimizer use the same plan.<BR/><BR/>Do you agree?<BR/><BR/>=============<BR/>This is because the first time when the SQL ran, oracle engine found <BR/>the literal value as “unsafe” because using the same literal value <BR/>can cause bad plans for other similar SQL’s. So along with the PLAN, <BR/>optimizer stored the literal value also. This will ensure the <BR/>reusability of the plan only in case the same lieteral is provided. <BR/>In case of any change, optimizer will generate a new plan.<BR/>============Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-72183464924078721132008-07-18T09:50:00.000+05:302008-07-18T09:50:00.000+05:30In your case - all the 4 statement used the same e...In your case - all the 4 statement used the same execution plan i.e. all 4 used the index you created.<BR/><BR/>Since you were doing "select count() .." - it is making the sql to use index. So effectively there is no diff in all 4 statements.<BR/><BR/>I would suggest you to check "select *" OR "select (cols) .." rather than doing "select count(*) .." which will give optimizer a choice of using or rejecting an index. <BR/><BR/>In your case - i feel optimizer is forced to use an index.<BR/><BR/>- SachinSachinhttps://www.blogger.com/profile/04551992629821526364noreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-75581154032602525702008-07-17T23:44:00.000+05:302008-07-17T23:44:00.000+05:30SQL> create table test as select * from user_ob...SQL> create table test as select * from user_objects;<BR/><BR/>Table created.<BR/><BR/>SQL> /<BR/><BR/>35584 rows created.<BR/><BR/>SQL> commit;<BR/><BR/>Commit complete.<BR/><BR/>SQL> select count(*),status from test group by status;<BR/><BR/> COUNT(*) STATUS<BR/>---------- -------<BR/> 4096 INVALID<BR/> 1134592 VALID<BR/><BR/>SQL> update test set status='BAD' where rownum < 2;<BR/><BR/>1 row updated.<BR/><BR/>SQL> commit;<BR/><BR/>Commit complete.<BR/><BR/>SQL> update test set status='GOOD' where status='VALID'<BR/> 2 and rownum < 2;<BR/><BR/>1 row updated.<BR/><BR/>SQL> commit;<BR/><BR/>Commit complete.<BR/><BR/>SQL> select count(*),status from test group by status;<BR/><BR/> COUNT(*) STATUS<BR/>---------- -------<BR/> 1 BAD<BR/> 1 GOOD<BR/> 4096 INVALID<BR/> 1134590 VALID<BR/><BR/>SQL> <BR/><BR/>SQL> create index idx on test(status);<BR/><BR/>Index created.<BR/><BR/>SQL> EXECUTE DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'MWSADMIN',TABNAME => 'TEST',ESTIMA<BR/>TE_PERCENT => 10, METHOD_OPT => 'FOR ALL COLUMNS SIZE 1', CASCADE => TRUE);<BR/><BR/>PL/SQL procedure successfully completed.<BR/><BR/>SQL> alter system flush shared_pool;<BR/><BR/>System altered.<BR/><BR/>SQL> alter session set cursor_sharing='SIMILAR';<BR/><BR/>Session altered.<BR/><BR/>SQL> select sql_text from v$sql where<BR/> 2 sql_text like '%select count(*) from test where status=%';<BR/><BR/>no rows selected<BR/><BR/>SQL> select count(*) from test where status='BAD';<BR/><BR/> COUNT(*)<BR/>----------<BR/> 1<BR/><BR/>SQL> select count(*) from test where status='GOOD';<BR/><BR/> COUNT(*)<BR/>----------<BR/> 1<BR/><BR/>SQL> select count(*) from test where status='INVALID';<BR/><BR/> COUNT(*)<BR/>----------<BR/> 4096<BR/><BR/>SQL> select count(*) from test where status='VALID';<BR/><BR/> COUNT(*)<BR/>----------<BR/> 1134590<BR/><BR/>SQL> select sql_text from v$sql where<BR/> 2 sql_text like '%select count(*) from test where status=%';<BR/><BR/>SQL_TEXT<BR/>--------------------------------------------------------------------------------<BR/>select count(*) from test where status=:"SYS_B_0"<BR/><BR/><BR/>SQL> select * from v$version;<BR/><BR/>BANNER<BR/>----------------------------------------------------------------<BR/>Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production<BR/>PL/SQL Release 9.2.0.6.0 - Production<BR/>CORE 9.2.0.6.0 Production<BR/>TNS for Linux: Version 9.2.0.6.0 - Production<BR/>NLSRTL Version 9.2.0.6.0 - Production<BR/><BR/>SQL> <BR/><BR/>As per your post, v$sql should show 4 statement... But it is not.<BR/><BR/>Any thought??Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-78908794869683926852008-05-05T23:40:00.000+05:302008-05-05T23:40:00.000+05:30This was really helpfulThis was really helpfulAnonymousnoreply@blogger.comtag:blogger.com,1999:blog-2257062243512934022.post-75400993821629128112008-04-02T11:19:00.000+05:302008-04-02T11:19:00.000+05:30Describe clearly. Good effort.Amila KularatnaDescribe clearly. Good effort.<BR/><BR/>Amila KularatnaAnonymousnoreply@blogger.com