Author: biti_rainy (biti_rainy@itpub.net)
Summary: This article tries to explain the meaning of cursor_sharing = similar by simple experiment.
1.1. Experimental phenomenon
Let's first look at the performance of the table without analysis without statistical data.
SQL> ALTER Session Set Cursor_sharing = Similar;
Session altered.
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ------------------------
PARSE TIME CPU 4948
PARSE TIME ELAPSED 4468
PARSE COUNT (TOTAL) 170148
Parse Count (HARD) 1619 (Hard Analysis Substation)
PARSE COUNT (FAILURES) 80
SQL> SELECT Count (*) from T where Object_id = 1000;
Count (*)
------------
0
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ------------------------
PARSE TIME CPU 4948
PARSE TIME ELAPSED 4468
PARSE COUNT (TOTAL) 170172
Parse Count (HARD) 1620
PARSE COUNT (FAILURES) 80
SQL> /
Name Value
-------------------------------------------------- ------------------------
Parse Time CPU 4948PARSE TIME ELAPSED 4468
PARSE COUNT (TOTAL) 170176
Parse Count (HARD) 1620
PARSE COUNT (FAILURES) 80
SQL> SELECT Count (*) from T where Object_id = 1000;
Count (*)
------------
0
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ------------------------
PARSE TIME CPU 4948
PARSE TIME ELAPSED 4468
PARSE COUNT (TOTAL) 170178
Parse Count (HARD) 1620
PARSE COUNT (FAILURES) 80
SQL> SELECT Count (*) from T where Object_id = 1001;
Count (*)
------------
0
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ------------------------
PARSE TIME CPU 4948
PARSE TIME ELAPSED 4468
PARSE COUNT (TOTAL) 170180
Parse Count (HARD) 1620 (even if the Object_ID is changed, there is still no hard analysis)
Parse Count (Failures) 80 Let's see the performance after analysis of the analysis table and field information
SQL> Analyze Table T1 Compute Statistics for Table for Columns Object_ID;
Table analyzed.
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ------------------------
PARSE TIME CPU 4973
PARSE TIME ELAPSED 4495
PARSE COUNT (TOTAL) 170982
Parse Count (HARD) 1640
PARSE COUNT (FAILURES) 80
SQL> SELECT Count (*) from t1 where object_id = 5000;
Count (*)
------------
0
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ------------------------
PARSE TIME CPU 4973
PARSE TIME ELAPSED 4495
PARSE COUNT (TOTAL) 170984
Parse Count (HARD) 1641
PARSE COUNT (FAILURES) 80
SQL> SELECT Count (*) from t1 where object_id = 5000;
Count (*)
------------
0
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ---------------------- Parse Time CPU 4973
PARSE TIME ELAPSED 4495
PARSE COUNT (TOTAL) 171008
Parse Count (HARD) 1641 (Dissuction did not change)
PARSE COUNT (FAILURES) 80
SQL> SELECT Count (*) from t1 where object_id = 5001;
Count (*)
------------
0
SQL> SELECT NAME, VALUE FROM V $ sysstat where name Like '% parse%;
Name Value
-------------------------------------------------- ------------------------
PARSE TIME CPU 4973
PARSE TIME ELAPSED 4495
PARSE COUNT (TOTAL) 171010
Parse Count (HARD) 1642 (a hard analysis when Object_ID changes)
PARSE COUNT (FAILURES) 80
SQL>
SQL> SELECT SQL_TEXT, Child_Number from V $ SQL Where SQL_TEXT LIKE 'SELECT Count (*) from T1 Where%';
SQL_Text
-------------------------------------------------- ----------------------------
Child_Number
----------------
Select count (*) from t1 where object_id =: "sys_b_0"
0
Select count (*) from t1 where object_id =: "sys_b_0"
1
1.2. Conclusion
It can be seen that if there is a Histograms that exists, each time a different value is made, hard parsing is generated. If there is no Histograms, hard analysis is not generated. In other words, when the field is analyzed, Similar's performance and exact are, when the fields are not analyzed, when there is Histograms, Similar's performance is the same as force. This avoids converting into a variable form as FORCE, because there is Histograms to convert to variables, it is easy to generate errors, and there is no use of statistics. And Exact, there is no hard analysis in the absence of Histograms, so that if the implementation plan is not affected by the data distribution (because there is no statistical information) is no substantive significance. Similar is integrated with the advantages of both. About the Author:
Net name COOLYL
Oracle expert. There are extensive Oracle practical experience, the architecture, backup recovery, SQL optimization, overall performance optimization, Oracle Internal has in-depth research.
Personal Education and Growth Experience: How to properly apply Oracle feature in the database application design with profound understanding. There is a wealth of Oracle practical experience, the architecture, backup recovery, SQL optimization, and the overall performance optimization of the database, and Oracle INTERNAL have in-depth research. He was responsible for the development of a telestric system, and then became a system integrated company DBA, and then turned into a technical person in charge of the Zhuhai R & D center in Hong Kong (the company's main product is the optimization tool of SQL and database. To Europe and North America), thereafter become freelancers, providing customers with separate Oracle database technical services and advanced performance adjustments, while providing ITPUB South China and East China training. Currently serving a large-scale e-commerce website in China, maintaining system databases and provides development support. Good at technical field: Oracle's current work Dynamics: Alibaba DBA Personal Blog: http://blog.itpub.net/biti_riany e-mail: Biti_Rainy @ itpub.net
