V$SQL_游标、adaptive cursor sharing、bind peeking、直方图的概念

这里讨论的游标主要是共享游标（shared cursor），跟pl/sql语句中定义的游标（session cursor）不是一个概念。

 

共享游标:是用户提交SQL或PL/SQL程序块到Oracle的share pool之后，在library cache中生成的一个可执行对象，这个对象我们称之为游标（cursor）。是SQL语句在进行硬解析时生成的，其元数据被在视图V$sqlarea与v$sql中具体化。

PL/SQL游标:则是用于存放SQL语句的执行结果,用户可以通过这个中间缓冲区逐条取出游标中的记录并对其处理，直到所有的游标记录被逐一处理完毕。需要声明、打开、提取、关闭。

 

 

共享游标包括父游标和子游标。

父游标：是在进行硬解析时产生的。将SQL语句的文本进行哈希得到哈希值并在library cache寻找相同的哈希值（SQL语句必须完全一致包括大小写、空格回车等才能共享），如不存在则生存父游标且保存在library cache中，按顺序完成后续步骤。如果此时存在父游标，则进一步判断是否存在子游标。若存在相同的子游标，则直接调用其子游标的执行计划执行该SQL语句，否则转到下一步进行逻辑优化。

 

子游标：在发生硬解析时，在产生父游标的同时，则跟随父游标会产生相应的子游标，此时V$SQL.CHILD_NUMBER的值为0。如果存在父游标，由于不同的运行环境，此时同样会产生新的子游标，新子游标的CHILD_NUMBER在已有子游标基础上以1为单位累计。v$sql中的每一行表示了一个child cursor子游标，根据sql_id与父cursor关联。child cursor有自己的address，即v$sql.child_address。如果你想确定是由那种原因造成的子游标，需要查看v$sql_shared_cursor。

 

1.父游标的关键信息是sql文本，子游标的关键信息是执行计划和执行环境。

2.硬解析通常是由于不可共享的父游标造成的，如经常变动的SQL语句，或动态SQL或未使用绑定变量等。解决硬解析的办法则通常是使用绑定变量来解决。

3.与父游标SQL文本完全一致的情形下，多个相同的SQL语句可以共享一个父游标。

4.SQL文本、执行环境完全一致的情形下，子游标能够被共享，否则如果执行环境不一致则生成新的子游标。如果SQL文本相同，但是可能提交SQL语句的用户不同，或者用户提交的SQL语句所涉及到的对象为同名词等，都有可能生成不同的子游标。因为这些SQL语句的文本虽然完全一样，但是上下文环境却不一样，因此这样的SQL语句不是一个可执行的对象，必须细化为多个子游标后才能够执行。

5.游标是可以被所有进程共享的，也就是说如果100个进程都执行相同的SQL语句，那么这100个进程都可以同时使用该SQL语句所产生的游标，从而节省了内存。

 

 

两条一模一样的语句但是在不同的schema下执行的两种结果

如sye、system都执行select * from t1.test则V$SQL只有一条记录，谁先执行则PARSING_SCHEMA_NAME显示谁。

如sys和system都执行select * from test则V$SQL中有两条记录，两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样

SQL规范肯定是要绑定变量的，数据倾斜也并非什么常态，实际情况下adaptive cursor sharing还是用的比较少的，遇到没有绑定变量的情况下就把参数cursor_sharing改成force吧

BIND PEEKING

Bind Peeking 就是当在WHERE条件中使用绑定变量的时候，CBO会根据第一次使用的真实变量值来生成一个执行计划。在这个cursor的整个生命周期中，CBO不会再对相同的SQL进行hard parse。这种办法的优点是：如果索引字段的值是均匀分布的，hard parse就降低了，性能提高。但是缺点也很明显：如果字段分布不均匀，并且第一次使用值不具有普遍性，那么执行计划就将非常糟糕。即字段数据分布倾斜严重时，使用绑定变量进行查询时，bind peeking可能导致产生不正确的执行计划.

解决这个问题就是Oracle11g 提供的一个新特性Adpative Cursor Sharing

 

 

CURSOR_SHARING determines what kind of SQL statements can share the same cursors

Values:

■ FORCE

Allows the creation of a new cursor if sharing an existing cursor, or if the cursor plan is not optimal.
使用了绑定变量可以使用同一个执行计划游标，并运行创建一个新的执行计划游标（即adaptive cursor sharing可以和CURSOR_SHARING共存）

■ EXACT

Only allows statements with identical text to share the same cursor.
一模一样的SQL文本才能共用一个执行计划游标

cursor_sharing=force

意味着Oracle会对SQL谓词值进行强制的绑定变量替换，这样谓词不一样的SQL会认为是一模一样的SQL。使用第一次的bind peeking值生成执行计划，之后全部使用这个执行计划，当出现数据倾斜时还会自动创建新的执行计划。这种方式实现了游标共享，避免出现大量的library cache硬解析。

如果这种SQL语句本身是“Good SQL”，也就是条件列分布比较平均，没有出现过大的偏移分布。我们认为这种FORCE是很有益的。但是如果数据列分布不平均，这样借用第一次输入的bind peeking生成并且共享执行计划就很成问题，如果说在cursor_sharing取定FORCE遇到的潜在问题，和我们使用绑定变量时候使用的bind peeking值问题是相同的，但是11G之后这个问题都不存在了，11G的时候就算CURSOR_SHARING=force时也会使用adaptive cursor sharing功能。

adaptive cursor sharing自适应游标共享

默认启动的（_optimizer_adaptive_cursor_sharing参数默认为true）。

其允许一个使用绑定变量的SQL语句使用多个执行计划。即具有绑定变量的sql语句可能会生成多个游标。如果没有adaptive cursor sharing则数据存在数据倾斜的情况下使用绑定变量倒可能是最差的方式。
Interacting with adaptive cursor sharing
cursor_sharing:
-if cursor_sharing<>EXACT,statements containing literals may be rewritten using bind variables
-if statements are rewritten,adaptive cursor sharing may apply to them

sql_plan_management(SPM):
- if optimizer_capture_sql_plan_baselines is set to true,only the first generated plan is used
-AS a workload,set this parameter to false,and run your application until all plans are loaded in the cursor cache
-Manually load the cursor cache into the corresponding plan baseline

v$sql.IS_BIND_SENSITIVE

Indicates whether the cursor is bind sensitive (Y) or not (N). A query is considered bind-sensitive if the optimizer peeked at one of its bind variable values when computing predicate selectivities and where a change in a bind variable value may cause the optimizer to generate a different plan.

v$sql.IS_BIND_AWARE

Indicates whether the cursor is bind aware (Y) or not (N). A query is considered bind-aware if it has been marked to use extended cursor sharing. The query would already have been marked as bind-sensitive.

v$sql.IS_SHAREABLE

Indicates whether the cursor can be shared (Y) or not (N)

但是实践的情况来看：adaptive cursor sharing这个功能貌似不起什么作用，cursor_sharing= EXACT情况下，就算数据倾斜情况下执行计划也不一定会根据不同值生成不同执行计划，而是根据dba_indexes.CLUSTERING_FACTOR来判断走索引还是全表。更不要说所有语句共享一个执行计划的参数cursor_sharing=force的情况了。

 

 

 

直方图

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'HR', TABNAME => 'TESTTB', CASCADE => TRUE, ESTIMATE_PERCENT => 100, METHOD_OPT => 'FOR ALL INDEXED COLUMNS size 100');

METHOD_OPT - The value controls column statistics collection and histogram creation. It accepts either of the following options, or both in combination:

■ FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]

■ FOR COLUMNS [size clause] column [size_clause][,column [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | extension name | extension

- integer : Number of histogram buckets. Must be in the range [1,254].

- REPEAT : Collects histograms only on the columns that already have histograms.

- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.

- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the

columns.

- column_name : name of a column

- extension : can be either a column group in the format of (column_name, colume_name [, ...]) or an expression

The default is FOR ALL COLUMNS SIZE AUTO

该默认值是要给所有的数据列进行统计信息收集，并且依据auto的原则生成直方图Histogram。

如果给所有列都收集直方图，统计量收集过程将会很长，需要消耗很多的性能和空间。

auto选项如何决定收集哪些列的直方图呢，就是参考如下两条件，即该列使用过且倾斜

1 列里的值必须是倾斜的

2 该列做为谓语条件被使用过

直方图有关的两个SQL

select table_name,column_name,num_distinct,num_buckets,histogram from user_tab_col_statistics
select index_name,table_name,leaf_blocks,DISTINCT_KEYS,AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS,AVG_DATA_BLOCKS_PER_KEY from user_ind_statistics

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30126024/viewspace-2119091/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30126024/viewspace-2119091/