Oracle脚本获得统计信息
以下脚本可以获取某张表的统计信息，觉得很好，在此记下。声明：脚本来自《Pro Oracle SQL》一书，st-all.sql
简单演示：
luocs@MAA> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------------------------------------ -------------- ----------
T1 TABLE
sys@MAA> @st-all
Enter the owner name: luocs
Enter the table name: t1
old 2: v_owner varchar2(30) := upper('&p_owner');
new 2: v_owner varchar2(30) := upper('luocs');
old 3: v_table varchar2(30) := upper('&p_table');
new 3: v_table varchar2(30) := upper('t1');
===================================================================================================================================
TABLE STATISTICS
===================================================================================================================================
Owner : luocs
Table name : t1
Tablespace : users
Cluster name:
Partitioned : no
Last analyzed : 11/06/2012 22:00:11
Sample> Degree : 1
IOT Type :
IOT name :
# Rows : 22671
# Blocks : 320
Empty Blocks: 0
Avg Space : 0
Avg Row Length: 92
Monitoring? : yes
old 2: v_owner varchar2(30) := upper('&p_owner');
new 2: v_owner varchar2(30) := upper('luocs');
old 3: v_table varchar2(30) := upper('&p_table');
new 3: v_table varchar2(30) := upper('t1');
===================================================================================================================================
COLUMN STATISTICS
===================================================================================================================================
Name Analyzed Null?NDV Density# Nulls # Buckets Sample AvgLenLo-Hi Values
===================================================================================================================================
created 11/06/2012 22:00:10Y 764 .0013090 1 22671 8 08/22/2012 18:44:29 | 11/06/2012 11:33:50
data_object_id 11/06/2012 22:00:10Y 7675 .00013014959 1 7712 3 0 | 23033
edition_name 11/06/2012 22:00:10Y 0 .00000022671 0 0 |
generated 11/06/2012 22:00:10Y 2 .5000000 1 22671 2 N | Y
last_ddl_time 11/06/2012 22:00:10Y 813 .0012300 1 22671 8 08/22/2012 18:44:29 | 11/06/2012 11:33:50
namespace 11/06/2012 22:00:10Y 17 .0588240 1 22671 3 1 | 64
object_id 11/06/2012 22:00:10Y 22671 .0000440 1 22671 5 2 | 23033
object_name 11/06/2012 22:00:10Y 18242 .0000550 1 22671 21 ABSPATH | xdb-log13_TAB$xd
object_type 11/06/2012 22:00:10Y 40 .0250000 1 22671 7 CLUSTER | XML SCHEMA
owner 11/06/2012 22:00:10Y 14 .0714290 1 22671 7 APEX_030200 | XDB
secondary 11/06/2012 22:00:10Y 2 .5000000 1 22671 2 N | Y
status 11/06/2012 22:00:10Y 1 1.0000000 1 22671 6 VALID | VALID
subobject_name 11/06/2012 22:00:10Y 83 .01204822441 1 230 2 P0 | WRH$_WAITST_670567865_0
temporary 11/06/2012 22:00:10Y 2 .5000000 1 22671 2 N | Y
timestamp 11/06/2012 22:00:10Y 831 .0012030 1 22671 20 1997-04-12:12:59:00 | 2012-11-06:11:33:50
===================================================================================================================================
INDEX INFORMATION
===================================================================================================================================
Index Name BLevel Leaf Blks # Rows Distinct Keys Avg Lf/Blks/Key Avg Dt/Blks/Key Clustering Factor Sample> ------------------------------------------------------------ ------- --------------- --------------- --------------- --------------- --------------- ----------------- --------------- ----- -------- -------- --- ----- ---- -------------------
INX_ID 1 50 22671 22671 1 1 370 22671 NO NORM VALID 1 NO . 11/06/2012 22:00:12
Index Name Pos# Order Column Name
------------------------------------------------------------ ---------- ----- ------------------------------
inx_id 1 ASC object_id
sys@MAA> exec dbms_stats.gather_table_stats('LUOCS','T1',cascade=>true);
sys@MAA> @st-all
Enter the owner name: luocs
Enter the table name: t1
===================================================================================================================================
TABLE STATISTICS
===================================================================================================================================
Owner : luocs
Table name : t1
Tablespace : users
Cluster name:
Partitioned : no
Last analyzed : 01/09/2013 18:17:50
Sample> Degree : 1
IOT Type :
IOT name :
# Rows : 22671
# Blocks : 320
Empty Blocks: 0
Avg Space : 0
Avg Row Length: 92
Monitoring? : yes
===================================================================================================================================
COLUMN STATISTICS
===================================================================================================================================
Name Analyzed Null?NDV Density# Nulls # Buckets Sample AvgLenLo-Hi Values
===================================================================================================================================
created 01/09/2013 18:17:50Y 764 .0013090 1 22671 8 08/22/2012 18:44:29 | 11/06/2012 11:33:50
data_object_id 01/09/2013 18:17:50Y 7675 .00013014959 1 7712 3 0 | 23033
edition_name 01/09/2013 18:17:50Y 0 .00000022671 0 0 |
generated 01/09/2013 18:17:50Y 2 .5000000 1 22671 2 N | Y
last_ddl_time 01/09/2013 18:17:50Y 813 .0012300 1 22671 8 08/22/2012 18:44:29 | 11/06/2012 11:33:50
namespace 01/09/2013 18:17:50Y 17 .0588240 1 22671 3 1 | 64
object_id 01/09/2013 18:17:50Y 22671 .0000440 1 22671 5 2 | 23033
object_name 01/09/2013 18:17:50Y 18242 .0000550 1 22671 21 ABSPATH | xdb-log13_TAB$xd
object_type 01/09/2013 18:17:50Y 40 .0250000 1 22671 7 CLUSTER | XML SCHEMA
owner 01/09/2013 18:17:50Y 14 .0714290 1 22671 7 APEX_030200 | XDB
secondary 01/09/2013 18:17:50Y 2 .5000000 1 22671 2 N | Y
status 01/09/2013 18:17:50Y 1 1.0000000 1 22671 6 VALID | VALID
subobject_name 01/09/2013 18:17:50Y 83 .01204822441 1 230 2 P0 | WRH$_WAITST_670567865_0
temporary 01/09/2013 18:17:50Y 2 .5000000 1 22671 2 N | Y
timestamp 01/09/2013 18:17:50Y 831 .0012030 1 22671 20 1997-04-12:12:59:00 | 2012-11-06:11:33:50
===================================================================================================================================
INDEX INFORMATION
===================================================================================================================================
Index Name BLevel Leaf Blks # Rows Distinct Keys Avg Lf/Blks/Key Avg Dt/Blks/Key Clustering Factor Sample> ------------------------------------------------------------ ------- --------------- --------------- --------------- --------------- --------------- ----------------- --------------- ----- -------- -------- --- ----- ---- -------------------
INX_ID 1 50 22671 22671 1 1 370 22671 NO NORM VALID 1 NO . 01/09/2013 18:17:50
Index Name Pos# Order Column Name
------------------------------------------------------------ ---------- ----- ------------------------------
inx_id 1 ASC object_id
oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html
页:
[1]