ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 学习视图 v$sql_shared_cursor

学习视图 v$sql_shared_cursor

原创 Linux操作系统 作者:lsq_008 时间:2008-12-26 13:00:28 0 删除 编辑

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

这个视图是用来查看sql没有被共享的原因,分以下两种情况进行测试:

1. 优化器模式不同时

以scott用户登录数据库,执行:


SQL> select count(*) from emp where empno=9000;
 
  COUNT(*)
----------
         0

SQL> alter session set optimizer_mode=first_rows_1
 
Session altered.
 
SQL> select count(*) from emp where empno=9000;
 
  COUNT(*)
----------
         0

以sys用户登录数据库,查看该视图:

SQL> select sql_id,address,sql_text,address, child_address, optimizer_mode from v$sql
  2  where sql_text like 'select count(*) from emp%';
 
SQL_ID        ADDRESS  SQL_TEXT                       ADDRESS  CHILD_AD OPTIMIZER_
------------- -------- ------------------------------ -------- -------- ----------
fph4602hqpfaz 299D1180 select count(*) from emp where 299D1180 299D07DC ALL_ROWS
                        empno=9000
 
fph4602hqpfaz 299D1180 select count(*) from emp where 299D1180 29900CB0 FIRST_ROWS
                        empno=9000
                       
SQL> select sql_id,address,child_address,child_number,OPTIMIZER_MODE_MISMATCH from v$sql_shared_cursor where OPTIMIZER_MODE_MISMATCH='Y';

SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER O
------------- -------- -------- ------------ -
fph4602hqpfaz 299D1180 29900CB0            1 Y
fph4602hqpfaz 299D1180 296E7CA0            2 Y

2.schema 不同时。

首先以scott用户登录数据库:

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 26 15:01:36 2008
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
 
SQL> show user
USER is "SCOTT"
SQL> select count(*) from emp;
 
  COUNT(*)
----------
        15

 

再以sys用户登录数据库:

-bash-3.00$ sqlplus '/as sysdba'
 
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Dec 26 15:02:26 2008
 
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
 
 
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Data Mining and Real Application Testing options
 
SQL> show user
USER is "SYS"
SQL> select count(*) from emp;
 
  COUNT(*)
----------
        14

此时,再查看:

SQL> select sql_id,address,sql_text,address, child_address, optimizer_mode from v$sql
  2  where sql_text like 'select count(*) from emp%';
 
SQL_ID        ADDRESS  SQL_TEXT                                                     ADDRESS  CHILD_AD OPTIMIZER_
------------- -------- ------------------------------------------------------------ -------- -------- ----------
g59vz2u4cu404 2A378C10 select count(*) from emp                                     2A378C10 2A37826C ALL_ROWS
g59vz2u4cu404 2A378C10 select count(*) from emp                                     2A378C10 2A36A20C ALL_ROWS

 SQL> select sql_id,address,child_address,child_number,OPTIMIZER_MODE_MISMATCH, AUTH_CHECK_MISMATCH from v$sql_shared_cursor where AUTH_CHECK_MISMATCH='Y';
 
SQL_ID        ADDRESS  CHILD_AD CHILD_NUMBER O A
------------- -------- -------- ------------ - -
5wpg5u2rw4xk5 2A2756DC 2AF82BD8            1 N Y
g59vz2u4cu404 2A378C10 2A36A20C            1 N Y

总结:通过以上两个小测试,可以发现,sql能否被共享,取决于很多因素,上述试验就验证了当优化器模式和用户模式不同时,sql不能被共享的情况。

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

请登录后发表评论 登录
全部评论
十余年大型金融及电信系统数据库管理经验,曾服务于中国建设银行、中国移动。对oracle,mysql数据库有深入了解。 擅长python开发,独立开发了开源数据库自动化监控运维平台Power Monitor。

注册时间:2008-02-29

  • 博文量
    325
  • 访问量
    1259079