Re: V$sql: first and last-load-time
- From: Dion Cho <ukja.dion@xxxxxxxxx>
- To: fmhabash@xxxxxxxxx
- Date: Fri, 20 Mar 2009 22:02:50 +0900
Document!
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_2113.htm
Excerpt:
FIRST_LOAD_TIME VARCHAR2(19) Timestamp of the parent creation time...
LAST_LOAD_TIME VARCHAR2(19) Time at which the query plan (heap 6) was loaded
into the library cache
Cursor(SQL) LCO consists of parent and child. This is due to the fact that
same SQL text might have multiple execution plans.
Simple speaking(too simple), parent is SQL text and child is execution plan.
So, first_load_time will be updated when parent is being created and
last_load_time will be updated when the execution plan is being created.
Following test case would help to illustrate the situation.
UKJA@ukja102> create table t1(c1 int);
Table created.
UKJA@ukja102>
UKJA@ukja102> insert into t1 select 1 from dual connect by level <= 10000;
10000 rows created.
UKJA@ukja102> insert into t1 select 2 from dual connect by level <= 1;
1 row created.
UKJA@ukja102>
UKJA@ukja102> create index t1_n1 on t1(c1);
Index created.
UKJA@ukja102>
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1',
method_opt=>'for columns c1 size 1', no_invalidate=>false);
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102> select /* _placeholder */ * from t1 where c1 = 2;
C1
----------
2
UKJA@ukja102>
UKJA@ukja102> select first_load_time, last_load_time from v$sql
2 where sql_text like 'select /* _placeholder */ * from t1 where c1 =
2%';
FIRST_LOAD_TIME
LAST_LOAD_TIME
--------------------------------------
--------------------------------------
2009-03-20/21:58:54
2009-03-20/21:58:54
UKJA@ukja102>
UKJA@ukja102> exec dbms_lock.sleep(5);
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102> alter system flush shared_pool;
System altered.
UKJA@ukja102>
UKJA@ukja102> select /* _placeholder */ * from t1 where c1 = 2;
C1
----------
2
UKJA@ukja102>
UKJA@ukja102> select first_load_time, last_load_time from v$sql
2 where sql_text like 'select /* _placeholder */ * from t1 where c1 =
2%';
FIRST_LOAD_TIME
LAST_LOAD_TIME
--------------------------------------
--------------------------------------
2009-03-20/21:58:59
2009-03-20/21:58:59
UKJA@ukja102>
UKJA@ukja102> exec dbms_lock.sleep(5);
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102> exec dbms_stats.gather_table_stats(user, 't1',
method_opt=>'for columns c1 size 2', no_invalidate=>false);
PL/SQL procedure successfully completed.
UKJA@ukja102>
UKJA@ukja102> select /* _placeholder */ * from t1 where c1 = 2;
C1
----------
2
UKJA@ukja102>
UKJA@ukja102> select first_load_time, last_load_time from v$sql
2 where sql_text like 'select /* _placeholder */ * from t1 where c1 =
2%';
FIRST_LOAD_TIME
LAST_LOAD_TIME
--------------------------------------
--------------------------------------
2009-03-20/21:58:59 2009-03-20/21:59:05
================================
Dion Cho - Oracle Performance Storyteller
http://dioncho.wordpress.com (english)
http://ukja.tistory.com (korean)
================================
On Fri, Mar 20, 2009 at 9:35 PM, FmHabash <fmhabash@xxxxxxxxx> wrote:
> I can see how and when first-load-time changes. Simply invalidating a
> cursor does it. However, could not come up with a way to manipulate
> last-load-time to understand what it really means.
> If a cursor gets reloaded, why doesn't first-lt changes. Why did oracle
> come up with this attribute for a sql.
>
> Thanks
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
