sys%ORA10GR2> @plan "select count(*) from v$session"
5 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select count(*) from v$session
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 65 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 65 | | |
| 2 | NESTED LOOPS | | 1 | 65 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 1 | 52 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 13 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("S"."INST_ID"=USERENV('INSTANCE') AND BITAND("S"."KSSPAFLG",1)<>0 AND
BITAND("S"."KSUSEFLG",1)<>0)
4 - filter("S"."KSUSEOPC"="E"."INDX")
18 rows selected.
so, right now X$KSUSE is presumed to have a single row flowing out of it....
we gather fixed stats:
sys%ORA10GR2> exec dbms_stats.gather_fixed_objects_stats
PL/SQL procedure successfully completed.
sys%ORA10GR2> @plan "select count(*) from v$session"
5 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select count(*) from v$session
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | NESTED LOOPS | | 141 | 2256 | 0 (0)| 00:00:01 |
| 3 | FIXED TABLE FULL | X$KSUSE | 141 | 1692 | 0 (0)| 00:00:01 |
| 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
11 rows selected.
and we can see the statistics have materially changed - 141 rows.....
We restart:
sys%ORA10GR2> startup force
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1261836 bytes
Variable Size 247467764 bytes
Database Buffers 281018368 bytes
Redo Buffers 7122944 bytes
Database mounted.
Database opened.
sys%ORA10GR2> @plan "select count(*) from v$session"
0 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select count(*) from v$session
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | NESTED LOOPS | | 141 | 2256 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 141 | 1692 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
"S"."INST_ID"=USERENV('INSTANCE'))
4 - filter("S"."KSUSEOPC"="E"."INDX")
18 rows selected.
it did not "lose them". Just in case that is a weird thing with startup force, we start again:
sys%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@dellpe ~]$ bounce
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 20 10:28:06 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
sys%ORA10GR2> ORACLE instance shut down.
sys%ORA10GR2> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 20 10:28:07 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to an idle instance.
idle> idle>
ORACLE instance started.
Total System Global Area 536,870,912 bytes
Fixed Size 1,261,836 bytes
Variable Size 247,467,764 bytes
Database Buffers 281,018,368 bytes
Redo Buffers 7,122,944 bytes
Database mounted.
Database opened.
idle> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
and upon looking:
[tkyte@dellpe ~]$ sysdba
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Dec 20 10:28:18 2007
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
sys%ORA10GR2> @plan "select count(*) from v$session"
0 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select count(*) from v$session
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 2376410614
-------------------------------------------------------------------------------
--------------
| Id | Operation | Name | Rows | Bytes | Cost (%CP
U)| Time |
-------------------------------------------------------------------------------
--------------
| 0 | SELECT STATEMENT | | 1 | 16 | 0 (
0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 |
| |
| 2 | NESTED LOOPS | | 141 | 2256 | 0 (
0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 141 | 1692 | 0 (
0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 4 | 0 (
0)| 00:00:01 |
-------------------------------------------------------------------------------
--------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
"S"."INST_ID"=USERENV('INSTANCE'))
4 - filter("S"."KSUSEOPC"="E"."INDX")
18 rows selected.
sys%ORA10GR2> set linesize 1000
sys%ORA10GR2> @plan "select count(*) from v$session"
5 rows deleted.
old 1: explain plan for &1
new 1: explain plan for select count(*) from v$session
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2376410614
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 0 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
| 2 | NESTED LOOPS | | 141 | 2256 | 0 (0)| 00:00:01 |
|* 3 | FIXED TABLE FULL | X$KSUSE | 141 | 1692 | 0 (0)| 00:00:01 |
|* 4 | FIXED TABLE FIXED INDEX| X$KSLED (ind:2) | 1 | 4 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(BITAND("S"."KSUSEFLG",1)<>0 AND BITAND("S"."KSSPAFLG",1)<>0 AND
"S"."INST_ID"=USERENV('INSTANCE'))
4 - filter("S"."KSUSEOPC"="E"."INDX")
18 rows selected.
still there - they are sticky. In fact:
sys%ORA10GR2> select a.name, b.rowcnt from v$fixed_table a, tab_stats$ b where a.object_id = b.obj# and rownum <= 10;
NAME ROWCNT
------------------------------ ----------
X$KQFTA 600
X$KQFVI 774
X$KQFVT 774
X$KQFDT 16
X$KQFCO 8612
X$KQFOPT 18
X$KSLLT 2568
X$KSLHOT 10
X$KSLLCLASS 8
X$KSLECLASS 4
10 rows selected.
I've emailed the note owner this.