v$sql中的LAST_ACTIVE_TIME是解析sql的时间

http://docs.oracle.com/cd/E11882_01/server.112/e25513/dynviews_3043.htm#REFRN30246

LAST_LOAD_TIME
Time at which the query plan was loaded into the library cache

LAST_ACTIVE_TIME
TIme at which the query plan was last active

 

 

执行新的sql，这个sql不在共享池中，这时会进行硬解析、v$sql中的last_active_time和last_load_time是硬解析的时间。

执行共享池内已经存在的sql，会进行软解析，last_active_time是软解析的时间，也是sql最后执行的时间，last_load_time的值不变。
  
执行新的sql
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),sysdate from dual;

TO_CHAR(SYSDATE,'YY SYSDATE
------------------- ---------
2013/01/23 10:26:39 23-JAN-13

SQL>  select sql_id,address, child_address,to_char(last_active_time,'yyyy/mm/dd hh24:mi:ss') last_active_time,last_load_time from v$sql where sql_text='select to_char(sysdate,''yyy
y/mm/dd hh24:mi:ss''),sysdate from dual';

SQL_ID        ADDRESS  CHILD_AD LAST_ACTIVE_TIME
------------- -------- -------- -------------------
LAST_LOAD_TIME
----------------------------------------------------------------------------
37y2sa8brg30s 525D8714 4D4F72C0 2013/01/23 10:26:39
2013-01-23/10:26:39

================================================================

 

继续执行同一条sql,这个sql已经在共享池内
SQL> select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss'),sysdate from dual;

TO_CHAR(SYSDATE,'YY SYSDATE
------------------- ---------
2013/01/23 10:30:17 23-JAN-13

SQL> select sql_id,address, child_address,to_char(last_active_time,'yyyy/mm/dd hh24:mi:ss') last_active_time,last_load_time from v$sql where sql_text='select to_char(sysdate,''yyyy
/mm/dd hh24:mi:ss''),sysdate from dual';

SQL_ID        ADDRESS  CHILD_AD LAST_ACTIVE_TIME
------------- -------- -------- -------------------
LAST_LOAD_TIME
----------------------------------------------------------------------------
37y2sa8brg30s 525D8714 4D4F72C0 2013/01/23 10:30:17
2013-01-23/10:26:39

 

 

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

转载于:http://blog.itpub.net/24756186/viewspace-753122/