Oracle 变量绑定与变量窥视合集系列二

简介:

 用示例演示一次硬分析(hard parse)和一次软分析(soft parse),以及一次更软的分析(softer soft parse),并对给出演示结果

我们先看一个硬解析和软解析关系测试,什么时候硬解析,什么时候软解析

LEO1@LEO1> drop table leo6 purge;                          清理环境

Table dropped.

LEO1@LEO1> create table leo6 as select * from dba_objects;      创建leo6

Table created.

LEO1@LEO1> select count(*) from leo6;                       第一次执行

  COUNT(*)

----------

     72011

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------

select count(*) from leo6                 1           1        1

总解析次数:1

硬解析次数:1

执行次数:1

硬解析发生在SQL语句第一次执行时,后续在执行相同语句就是软解析了,看看下面

LEO1@LEO1> select count(*) from leo6;                       第二次执行

  COUNT(*)

----------

     72011

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------

select count(*) from leo6                 2           1        2

总解析次数:2

硬解析次数:1

执行次数:2

SQL硬解析之后,后续相同的SQL都被软解析,除非SQL被剔除shared_pool

LEO1@LEO1> alter system flush shared_pool;       清空shared_pool

System altered.

LEO1@LEO1> select count(*) from leo6;            重新执行一次

  COUNT(*)

----------

     72011

LEO1@LEO1> select sql_text,parse_calls,loads,executions from v$sql where sql_text like 'select count(*) from leo6' order by 1;

SQL_TEXT                             PARSE_CALLS  LOADS   EXECUTIONS

-------------------------------------------------- ----------- -------------------------- ----------- ---------- ------------------

select count(*) from leo6                 1           1        1

当重新执行一次后,又重新有了一次硬解析,oracle认为这是一条新SQL,根据统计信息重新生成一次执行计划来执行。

Softer_soft_parse 会话对游标的缓存

什么是游标:游标可以理解为SQL语句的一个句柄,也叫SQL语句的指针,游标指向一条SQL语句,oracle会话要执行一条SQL时,首先要打开游标。

打开游标:新的SQL语句执行时候,在SGA中需要把这条SQL语句和shared_poolSQL语句的哈希值建立一条通道(连接),即建立SQL语句句柄,这个建立通道的过程就叫打开游标。

softer_soft_parse超软解析:打开游标的过程是在软解析之后,它要在shared_pool中寻找哈希值(这个哈希值就是软解析之后得到的),如果没有找到就需要重新构造游标(这就是硬解析过程),如果游标是打开状态,那么会话可以直接使用打开的游标连接到shared_poolSQL语句入口,执行SQL。如果游标是关闭状态,会话就需要重新建立到shared_pool连接(即打开游标操作),这样也会消耗一点点资源。而我们要做的就是尽量打开游标保持通道畅通,又由于这个操作是在软解析之后,又叫超软解析,比软解析还要软一点。

会话缓存游标实验

session_cached_cursor,这个参数可以控制,会话打开游标或关闭游标

如果值为0,说明不使用缓存游标功能,oracle每次都要重新打开游标

如果值为非0,说明使用缓存游标功能,会话一直保持打开状态,随时执行SQL语句

1.session_cached_cursors=0 测试

为了更好的对比效果,我们先要清空一下内存中的遗留数据,最简行以便的方法就是重启数据库了,当然我们这是测试环境,在生产环境中另当别论。

SYS@LEO1> shutdown immediate;               关闭数据库,清空SGA

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@LEO1> startup                           启动数据库

ORACLE instance started.

Total System Global Area  680607744 bytes

Fixed Size                  2216464 bytes

Variable Size             503320048 bytes

Database Buffers          167772160 bytes

Redo Buffers                7299072 bytes

Database mounted.

Database opened.

SYS@LEO1> conn leo1/leo1                    切换leo1用户

Connected.

LEO1@LEO1> set linesize 300                   设置版面

LEO1@LEO1> set pagesize 999

LEO1@LEO1> show parameter session_cached_cursors

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------------------

session_cached_cursors                   integer      50

检查参数默认为50

LEO1@LEO1> alter session set session_cached_cursors=0;

Session altered.

如果值为0,说明不使用缓存游标功能,oracle每次都要重新打开游标

LEO1@LEO1> show parameter session_cached_cursors

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------------------

session_cached_cursors                   integer      0

LEO1@LEO1> alter system flush shared_pool;       清空shared pool

System altered.

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';

'STAT-'||NAME                                VALUE

---------------------------------------------------------------------- ----------

STAT-opened cursors cumulative                  51660

STAT-opened cursors current                     88          当前打开游标数

STAT-pinned cursors current                      5

STAT-session cursor cache hits                    37902       会话缓冲区游标命中率

STAT-session cursor cache count                  3153

STAT-cursor authentications                      668

STAT-parse time cpu                            845         CPU解析耗时

STAT-parse time elapsed                         1974        解析总耗时

STAT-parse count (total)                         30593       解析总次数

STAT-parse count (hard)                         2700        硬解析次数

STAT-parse count (failures)                       6

STAT-parse count (describe)                      0

LATCH-shared pool                             263201       共享池latch

查询系统当前资源消耗情况

LEO1@LEO1> drop table leo7 purge;                           清空环境

Table dropped.

LEO1@LEO1> create table leo7 as select * from dba_objects;       创建leo7

Table created.

LEO1@LEO1> select count(*) from leo7;                        size71972

  COUNT(*)

----------

     71972

循环执行1w

LEO1@LEO1> begin

for leo in 1..10000 loop

execute immediate 'select count(*) from leo7';

end loop;

end;

/

PL/SQL procedure successfully completed.

再次查询一下当前系统资源消耗情况

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';  2    3  

'STAT-'||NAME                                 VALUE

---------------------------------------------------------------------- ----------

STAT-opened cursors cumulative                   63614

STAT-opened cursors current                      89      当前打开游标数

STAT-pinned cursors current                       6

STAT-session cursor cache hits                     39494   会话缓冲区游标命中率

STAT-session cursor cache count                   3513

STAT-cursor authentications                       713

STAT-parse time cpu                             851

STAT-parse time elapsed                          1992

STAT-parse count (total)                          41546   解析总次数

STAT-parse count (hard)                          2739    硬解析次数

STAT-parse count (failures)                        6

STAT-parse count (describe)                       0

LATCH-shared pool                              268172

这是session_cached_cursors=0的对比结果

当前打开游标数:88  ->  89   多了1

会话缓冲区游标命中率:37902   ->   39494   多了1592

硬解析次数:2700  ->  2739   多了39

解析总次数:30593  ->  41546  多了10953次,这个和执行的次数很接近,差不多软解析了1w

共享池latch数:263201  ->  268172   多了4971

2.session_cached_cursors=100 测试

系统当前资源消耗

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';  2    3  

'STAT-'||NAME                                 VALUE

---------------------------------------------------------------------- ----------

STAT-opened cursors cumulative                   80112

STAT-opened cursors current                      88

STAT-pinned cursors current                      5

STAT-session cursor cache hits                    44463

STAT-session cursor cache count                  4334

STAT-cursor authentications                      791

STAT-parse time cpu                            872

STAT-parse time elapsed                         2016

STAT-parse count (total)                         55199

STAT-parse count (hard)                         2771

STAT-parse count (failures)                       10

STAT-parse count (describe)                      0

LATCH-shared pool                             278343

LEO1@LEO1> alter session set session_cached_cursors=100;      

如果值为非0,说明使用缓存游标功能,会话一直保持打开状态,随时执行SQL语句

LEO1@LEO1> show parameter session_cached_cursors

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

session_cached_cursors               integer     100

再重新循环执行1w

LEO1@LEO1> begin

for leo in 1..10000 loop

execute immediate 'select count(*) from leo7';

end loop;

end;

/

PL/SQL procedure successfully completed.

LEO1@LEO1> select 'STAT-'||name,value from v$sysstat where name like '%parse%' or name like'%cursor%'

union all

select 'LATCH-'||name,gets from v$latch where name = 'shared pool';  

'STAT-'||NAME                                 VALUE

---------------------------------------------------------------------- ----------

STAT-opened cursors cumulative                   92040

STAT-opened cursors current                      89

STAT-pinned cursors current                       5

STAT-session cursor cache hits                     56058

STAT-session cursor cache count                   4690

STAT-cursor authentications                       803

STAT-parse time cpu                             872

STAT-parse time elapsed                          2017

STAT-parse count (total)                          56081

STAT-parse count (hard)                          2773

STAT-parse count (failures)                        10

STAT-parse count (describe)                       0

LATCH-shared pool                              280878

这是session_cached_cursors=100的对比结果

当前打开游标数:88  ->  89   多了1

会话缓冲区游标命中率:44463   ->   56058   多了11595

硬解析次数:2771  ->  2773   多了2

解析总次数:55199  ->  56081  多了882

共享池latch数:278343  ->  280878   多了2535

小结:我们从会话缓冲区游标命中率指标的对比结果可知,设置session_cached_cursors参数为非0时,oracle使用了缓存游标功能,命中率从1592提升到11595,共享池latch数从4971减少到2535,我们可以很明显的看出使用打开游标技术可以大大提高数据库的性能,softer_soft_parse超软解析可以提供比soft_parse软解析更好的性能。

  本文转自 leonarding151CTO博客,原文链接:http://blog.51cto.com/leonarding/1131508,如需转载请自行联系原作者



相关文章
|
1月前
|
SQL 存储 Oracle
Oracle的PL/SQL定义变量和常量:数据的稳定与灵动
【4月更文挑战第19天】在Oracle PL/SQL中,变量和常量扮演着数据存储的关键角色。变量是可变的“魔术盒”,用于存储程序运行时的动态数据,通过`DECLARE`定义,可在循环和条件判断中体现其灵活性。常量则是不可变的“固定牌”,一旦设定值便保持不变,用`CONSTANT`声明,提供程序稳定性和易维护性。通过 `%TYPE`、`NOT NULL`等特性,可以更高效地管理和控制变量与常量,提升代码质量。善用两者,能优化PL/SQL程序的结构和性能。
|
1月前
|
Oracle 关系型数据库 数据库
Flink Sink to Oracle 存在字段CLOB类型,如何处理错误”ORA-01461: 仅能绑定要插入LONG的LONG值“
做Flink CDC同步数据过程中,目标是Oracle数据库,其中某个字段较大被设置为CLOB类型,其中会遇到异常,”ORA-01461: 仅能绑定要插入LONG的LONG值“
|
SQL 缓存 监控
为什么Oracle中包含绑定变量的SQL会有多个游标?(译文)
为了改进包含绑定变量的SQL的执行计划,Oracle在11g版本中引入了一项名为自适应游标共享(ACS,Adaptive Cursor Sharing)的新功能。
113 0
|
SQL Oracle 关系型数据库
Oracle优化09-绑定变量
Oracle优化09-绑定变量
190 0
|
SQL 缓存 Oracle
Oracle-绑定变量binding variable解读
Oracle-绑定变量binding variable解读
283 0
|
SQL 监控 Oracle
Oracle数据库---怎样获得sql语句执行时的绑定变量
绑定变量是oracle常用的优化技术,有时候我们需要获得sql运行时绑定变量的值,用10046事件跟踪会话即可获得
482 0
|
SQL Oracle 关系型数据库
PLSQL_性能优化系列19_Oracle Explain Plan解析计划通过Profile绑定
20150529 Created By BaoXinjian   一、摘要   1. 应用场景 当运行很久的Job突然出现性能问题时,并排除数据量突然变大,可能原因有执行的脚本的某些对应的SQL的解析计划变更,在Oracle 10g中可以通过绑定profile,在11g中可以通过baseline进行绑定 2.
789 0