v$sql中的executions以多长时间来计算的?
如题,执行次数(executions)是从数据库启动后就开始计算的,还是只是最近几个小时的执行次数?能清除v$sql里面的信息重新来过吗?
http://www.itpub.net/thread-959103-1-1.html
SQL> create table test as select * from dba_objects;
表已创建。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
1
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
2
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
2
SQL> grant select on test to scott; //invalidation cursor
授权成功。
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
未选定行
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
1
一个小测试
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> /
COUNT(*)
----------
50437
SQL> alter session set optimizer_mode=rule; //产生新version
会话已更改。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> /
COUNT(*)
----------
50437
SQL> alter session set optimizer_mode=choose; //产生新version
会话已更改。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select child_number,executions from v$sql where sql_text='select count(*) from test';
CHILD_NUMBER EXECUTIONS
------------ ----------
0 2
1 2
2 1
SQL> select sql_id from v$sql where sql_text='select count(*) from test';
SQL_ID
-------------
7b2twsn8vgfsc
7b2twsn8vgfsc
7b2twsn8vgfsc
SQL> select * from v$sql_shared_cursor where sql_id='7b2twsn8vgfsc';
SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -
R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
7b2twsn8vgfsc 1F5CA95C 226E0CB8 0 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
7b2twsn8vgfsc 1F5CA95C 226DEFA8 1 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
7b2twsn8vgfsc 1F5CA95C 226DABD8 2 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
查文档倒数第5个
OPTIMIZER_MODE_MISMATCH VARCHAR2(1) Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)
表已创建。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
1
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
2
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
2
SQL> grant select on test to scott; //invalidation cursor
授权成功。
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
未选定行
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select EXECUTIONS
2 from v$sql where sql_text='select count(*) from test';
EXECUTIONS
----------
1
一个小测试
SQL> alter system flush shared_pool;
系统已更改。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> /
COUNT(*)
----------
50437
SQL> alter session set optimizer_mode=rule; //产生新version
会话已更改。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> /
COUNT(*)
----------
50437
SQL> alter session set optimizer_mode=choose; //产生新version
会话已更改。
SQL> select count(*) from test;
COUNT(*)
----------
50437
SQL> select child_number,executions from v$sql where sql_text='select count(*) from test';
CHILD_NUMBER EXECUTIONS
------------ ----------
0 2
1 2
2 1
SQL> select sql_id from v$sql where sql_text='select count(*) from test';
SQL_ID
-------------
7b2twsn8vgfsc
7b2twsn8vgfsc
7b2twsn8vgfsc
SQL> select * from v$sql_shared_cursor where sql_id='7b2twsn8vgfsc';
SQL_ID ADDRESS CHILD_AD CHILD_NUMBER U S O O S L S E B P I S T A B D L T
------------- -------- -------- ------------ - - - - - - - - - - - - - - - - - -
R I I R L I O S M U T N F A I T D L D B P C S R P T M B M R O P M F L
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
7b2twsn8vgfsc 1F5CA95C 226E0CB8 0 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N
7b2twsn8vgfsc 1F5CA95C 226DEFA8 1 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
7b2twsn8vgfsc 1F5CA95C 226DABD8 2 N N N N N N N N N N N N N N N N N N
N N N N N N N N N N N N N N N N N N N N N N N N N N N N N N Y N N N N
查文档倒数第5个
OPTIMIZER_MODE_MISMATCH VARCHAR2(1) Parameter OPTIMIZER_MODE mismatch (for example, all_rows versus first_rows_1)