oracle cursor_sharing similar value on some of the issues set

2011-09-28  来源:本站原创  分类:Database  人气:53 

About oracle cursor_sharing = similar number of test parameters

When you have passed alter system set cursor_sharing = "SIMILAR" when

buyer_id a string type, if the display to indicate, as when the test results will only be compiled once

Test data

select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';

SELECT * FROM APPLY_BUYER  where buyer_id='13111'and buyer_name='a';
SELECT * FROM APPLY_BUYER  where buyer_id='13112'and buyer_name='b';
SELECT * FROM APPLY_BUYER  where buyer_id='13113'and buyer_name='c';
SELECT * FROM APPLY_BUYER  where buyer_id='13114'and buyer_name='d';
SELECT * FROM APPLY_BUYER  where buyer_id='13115'and buyer_name='e';
SELECT * FROM APPLY_BUYER  where buyer_id='13116'and buyer_name='f';
SELECT * FROM APPLY_BUYER  where buyer_id='13117'and buyer_name='g';
select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';

Test results

2 2FC3F204        1136223711      7       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0"and buyer_name=:"SYS_B_1"

If we pass the number, the system conversion, the compiler will not be the same every time, but his address and the code are the same? Do not quite understand

Test data

select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';

SELECT * FROM APPLY_BUYER  where buyer_id=13101 and buyer_name='aa';
SELECT * FROM APPLY_BUYER  where buyer_id=13102 and buyer_name='bb';
SELECT * FROM APPLY_BUYER  where buyer_id=13103 and buyer_name='cc';
SELECT * FROM APPLY_BUYER  where buyer_id=13104 and buyer_name='dd';
SELECT * FROM APPLY_BUYER  where buyer_id=13105 and buyer_name='ee';
SELECT * FROM APPLY_BUYER  where buyer_id=13106 and buyer_name='ff';
SELECT * FROM APPLY_BUYER  where buyer_id=13107 and buyer_name='gg';
select address,hash_value, executions, sql_text from v$sql  v$sql where upper(sql_text) like 'SELECT * FROM APPLY_BUYER%';

Test results

9 2FE57024        1923906214      1       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1"
10      2FE57024        1923906214      1       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1"
11      2FE57024        1923906214      1       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1"
12      2FE57024        1923906214      1       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1"
13      2FE57024        1923906214      1       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1"
14      2FE57024        1923906214      1       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1"
15      2FE57024        1923906214      1       SELECT * FROM APPLY_BUYER  where buyer_id=:"SYS_B_0" and buyer_name=:"SYS_B_1"

Results:

The default is set by the developer to go, not by the system to do it!

相关文章
  • oracle cursor_sharing similar value on some of the issues set 2011-09-28

    About oracle cursor_sharing = similar number of test parameters When you have passed alter system set cursor_sharing = "SIMILAR" when buyer_id a string type, if the display to indicate, as when the test results will only be compiled once Test da

  • Oracle cursor_sharing, histogram for the sql version count of 2010-04-11

    This paper mainly studies cursor_sharing different parameters set, histograms or on the SQL version count to collect impact. 1, cursor_sharing parameters and SQL version count Database of version: Quote SQL> select * from v $ version where rownum <2

  • Whether the histogram in the case of cursor_sharing = similar, force of impact on the SQL execution plan 2011-03-16

    In the case of no histogram, cursor_sharing = similar and the difference between force SQL> conn zhou / zhou Connected. SQL> create table cursor_t as select * from sys.obj $; Table created. SQL> show parameter cursor NAME TYPE VALUE -------------

  • Solution oracle of the Enterprise Manager Console option missing issues 2010-07-26

    z switched http://kalogen.javaeye.com/blog/719817 Today in the All Programs menu options into the oracle, suddenly can not find, on the way they want to do one, just create a bat file, enter the command: D: \ oracle \ ora92 \ bin \ oemapp.bat console

  • (R) oracle appears in the page sorted duplicate data issues 2011-10-08

    In the sort of oracle, according to a non-unique sort of location, can not guarantee that each page of data is not repeated with other data also. Because the oracle does not have a stable sorting algorithm, so the first 10 rows and the first 20 rows

  • Oracle strong to kill the process, to resolve issues such as table lock 2011-10-10

    1, find the sid, serial #; SELECT / * + rule * / s.username, l.type, decode (l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL, o.owner, o.object_name, o.object_type, s.sid, s.serial #, s.terminal, s.machine, s.program, s.osuser, s.statu

  • Histogram is set to similar studies with cursor_sharing 2010-03-29

    When cursor_sharing parameters similar to reduce the hard parsing. In this setting, if the conditions of an equivalent column sql queries there histogram, Oracle would think that by a different letter for each variable are generated cursor unsafe, an

  • Using Oracle 10g SQL Optimizer (STA) Optimization statement 2010-10-23

    SQL optimizer (SQL Tuning Advisor STA) is introduced in Oracle10g optimization tool to help DBA, which is characterized by simple, smart, DBA value of the call function can give a poor performance, statements of the optimization results. Here's what

  • Oracle performance-related views and arguments of several 2011-08-05

    One. Performance View Performance view of Oracle database performance in some of the records of view, by looking at these views, access to databases on current and historical performance data at a given time. Than SQL_TRACE, AWR report for the data m

  • Oracle 10g SQL optimizer (STA) Optimization statement 2011-04-14

    SQL optimizer (SQL Tuning Advisor STA) is introduced in Oracle10g DBA with the help of optimization tools, it is characterized by simple, smart, DBA values ​​need to call function can give a poor performance of the statement of the optimization resul

  • Oracle: soft and hard parsing + binding resolution spy 2011-06-11

    Speaking of soft parsing (soft prase) and hard parsing (hard prase), it can not be said about Oracle's sql processing. When you issue a sql statement delivered Oracle, and get the result in the implementation, Oracle sql this will be a few steps of t

  • cursor_sharing parameters for the performance impact of expdp 2011-09-28

    The customer's database using the cursor_sharing = similar parameters, tested and found this parameter greatly affects the performance of expdp. The essential reason is the SQL execution plan has changed. Under normal circumstances, 28 minutes to com

  • Oracle硬解析和软解析的区别分析 2014-10-28

    我们都知道在Oracle中每条SQL语句在执行之前都需要经过解析,这里面又分为软解析和硬解析.在Oracle中存在两种类型的SQL语句,一类为 DDL语句(数据定义语言),就是每次执行都需要进行硬解析.还有一类就是DML语句,他们会根据情况选择要么进行硬解析,要么进行软解析. 一.摘要 Oracle硬解析和软解析是我们经常遇到的问题,所以需要考虑何时产生软解析何时产生硬解析,如何判断 SQL的执行过程 当发布一条SQL或PL/SQL命令时,Oracle会自动寻找该命令是否存在于共享池中来决定对当

  • Oracle 硬解析与软解析 2012-03-09

    --======================= -- Oracle 硬解析与软解析 --======================= Oracle 硬解析与软解析是我们经常遇到的问题,什么情况会产生硬解析,什么情况产生软解析,又当如何避免硬解析?下面的描述将给出 软硬解析的产生,以及硬解析的弊端和如何避免硬解析的产生. 一.SQL语句的执行过程 当发布一条SQL或PL/SQL命令时,Oracle会自动寻找该命令是否存在于共享池中来决定对当前的语句使用硬解析或软解析. 通常情况下,SQL语句

  • oracle study notes 14 - PLSQL1 2010-03-06

    pl / sql programming example: 1. Only the operative part of the pl / sql block set serveroutput on; open the Output Options begin dbms_output.put_line('hello'); end; dbms_output is provided by the oracle package (similar to the Java development kit),

  • [Transferred] to answer all doubts on the Oracle license, organize various users of the statement. 2010-03-23

    And answer your doubts on the Oracle license, organize various users of the statement. Often discussed the recent Forum ORACLE database to see the license issue, I think a lot of friends at the time of purchase of ORACLE products, There may be some d

  • oracle jdbc driver package in the 2010-06-05

    from: http://junsansi.itpub.net/post/29894/279694 From oracle8 to oracle10, JDBC driver package directory are different. Looked at the documentation of oracle, learned about their use, I believe we are sometimes very confusing, written to share about

  • oracle 10g in the error: "ORA-01843: invalid month" 2010-07-02

    Perform in the newly installed oracle insert similar "22-OCT-10" date type of data will be reported out: "ORA-01843: invalid month" in the error message, not always successfully inserted. By check online to find the following solution,

  • Silent Mode installation of Oracle (change) 2010-09-21

    Oracle offers similar with RedHat Kickstart automated installation: Silent Mode. OUI through a series of predefined options to replace the interactive installation options. Predefined options are stored in the response File in. Silent Mode installati

  • Oracle language, from the entry to the master !____( constantly updated) 2010-10-21

    Section: User rights management CREATE ANY TABLE; can be built in any program in the table; CREATE SESSION; connect to the database; CREATE TABLE; create a table; CREATE VIEW; create a view CREATE PROCEDURE; building procedures, functions, packages C