一個很好的指令碼,用來顯示cbo的統計資訊
發表人: | 發表時間: 2007年一月09日, 19:38
Select to show Optimizer Statistics for CBO,Note:31412.1
Instructions | |
---|---|
Execution Environment: SQL*Plus Access Privileges: Requires select privileges on dba_tables, dba_tab_columns, dba_indexes, dba_ind_columns Usage: sqlplus | |
Description | |
The following select statement can be used to display the optimizer statistics related to a particular table. The script will ask for Table Name and Owner to show Statistics for. The default Owner is the current user. |
一個很好的指令碼,用來顯示cbo的統計資訊
發表人: | 發表時間: 2007年一月09日, 19:38
Select to show Optimizer Statistics for CBO,Note:31412.1
Instructions | |
---|---|
References | |
none | |
Script | |
set echo off set scan on set lines 150 set pages 66 set verify off set feedback off set termout off column uservar new_value Table_Owner noprint select user uservar from dual; set termout on column TABLE_NAME heading "Tables owned by &Table_Owner" format a30 select table_name from dba_tables where owner=upper('&Table_Owner') order by 1 / undefine table_name undefine owner prompt accept owner prompt 'Please enter Name of Table Owner (Null = &Table_Owner): ' accept table_name prompt 'Please enter Table Name to show Statistics for: ' column TABLE_NAME heading "Table|Name" format a15 column PARTITION_NAME heading "Partition|Name" format a15 column SUBPARTITION_NAME heading "SubPartition|Name" format a15 column NUM_ROWS heading "Number|of Rows" format 9,999,990 column BLOCKS heading "Blocks" format 999,990 column EMPTY_BLOCKS heading "Empty|Blocks" format 999,990 column AVG_SPACE heading "Average|Space" format 9,990 column CHAIN_CNT heading "Chain|Count" format 990 column AVG_ROW_LEN heading "Average|Row Len" format 990 column COLUMN_NAME heading "Column|Name" format a25 column NULLABLE heading Null|able format a4 column NUM_DISTINCT heading "Distinct|Values" format 99,990 column NUM_NULLS heading "Number|Nulls" format 99,990 column NUM_BUCKETS heading "Number|Buckets" format 990 column DENSITY heading "Density" format 990 column INDEX_NAME heading "Index|Name" format a15 column UNIQUENESS heading "Unique" format a9 column BLEV heading "B|Tree|Level" format 90 column LEAF_BLOCKS heading "Leaf|Blks" format 990 column DISTINCT_KEYS heading "Distinct|Keys" format 9,999,990 column AVG_LEAF_BLOCKS_PER_KEY heading "Average|Leaf Blocks|Per Key" format 99,990 column AVG_DATA_BLOCKS_PER_KEY heading "Average|Data Blocks|Per Key" format 99,990 column CLUSTERING_FACTOR heading "Cluster|Factor" format 999,990 column COLUMN_POSITION heading "Col|Pos" format 990 column col heading "Column|Details" format a24 column COLUMN_LENGTH heading "Col|Len" format 990 column GLOBAL_STATS heading "Global|Stats" format a6 column USER_STATS heading "User|Stats" format a6 column SAMPLE_SIZE heading "Sample|Size" format 9,999,990 column to_char(t.last_analyzed,'MM-DD-YYYY') heading "Date|MM-DD-YYYY" format a10 prompt prompt *********** prompt Table Level prompt *********** prompt select TABLE_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tables t where owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') / select COLUMN_NAME, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_columns t where table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) / select INDEX_NAME, UNIQUENESS, BLEVEL BLev, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_indexes t where table_name = upper('&Table_name') and table_owner = upper(nvl('&Owner',user)) / break on index_name select i.INDEX_NAME, i.COLUMN_NAME, i.COLUMN_POSITION, decode(t.DATA_TYPE, 'NUMBER',t.DATA_TYPE||'('|| decode(t.DATA_PRECISION, null,t.DATA_LENGTH||')', t.DATA_PRECISION||','||t.DATA_SCALE||')'), 'DATE',t.DATA_TYPE, 'LONG',t.DATA_TYPE, 'LONG RAW',t.DATA_TYPE, 'ROWID',t.DATA_TYPE, 'MLSLABEL',t.DATA_TYPE, t.DATA_TYPE||'('||t.DATA_LENGTH||')') ||' '|| decode(t.nullable, 'N','NOT NULL', 'n','NOT NULL', NULL) col from dba_ind_columns i, dba_tab_columns t where i.table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) and i.table_name = t.table_name and i.column_name = t.column_name order by index_name,column_position / prompt prompt *************** prompt Partition Level prompt *************** select PARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_partitions t where table_owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') order by partition_position / break on partition_name select PARTITION_NAME, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_BUCKETS, NUM_NULLS, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_PART_COL_STATISTICS t where table_name = upper('&Table_name') and owner = upper(nvl('&Owner',user)) / break on partition_name select t.INDEX_NAME, t.PARTITION_NAME, t.BLEVEL BLev, t.LEAF_BLOCKS, t.DISTINCT_KEYS, t.NUM_ROWS, t.AVG_LEAF_BLOCKS_PER_KEY, t.AVG_DATA_BLOCKS_PER_KEY, t.CLUSTERING_FACTOR, t.GLOBAL_STATS, t.USER_STATS, t.SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_ind_partitions t, dba_indexes i where i.table_name = upper('&Table_name') and i.table_owner = upper(nvl('&Owner',user)) and i.owner = t.index_owner and i.index_name=t.index_name / prompt prompt *************** prompt SubPartition Level prompt *************** select PARTITION_NAME, SUBPARTITION_NAME, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_tab_subpartitions t where table_owner = upper(nvl('&&Owner',user)) and table_name = upper('&&Table_name') order by SUBPARTITION_POSITION / break on partition_name select p.PARTITION_NAME, t.SUBPARTITION_NAME, t.COLUMN_NAME, t.NUM_DISTINCT, t.DENSITY, t.NUM_BUCKETS, t.NUM_NULLS, t.GLOBAL_STATS, t.USER_STATS, t.SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_SUBPART_COL_STATISTICS t, dba_tab_subpartitions p where t.table_name = upper('&Table_name') and t.owner = upper(nvl('&Owner',user)) and t.subpartition_name = p.subpartition_name and t.owner = p.table_owner and t.table_name=p.table_name / break on partition_name select t.INDEX_NAME, t.PARTITION_NAME, t.SUBPARTITION_NAME, t.BLEVEL BLev, t.LEAF_BLOCKS, t.DISTINCT_KEYS, t.NUM_ROWS, t.AVG_LEAF_BLOCKS_PER_KEY, t.AVG_DATA_BLOCKS_PER_KEY, t.CLUSTERING_FACTOR, t.GLOBAL_STATS, t.USER_STATS, t.SAMPLE_SIZE, to_char(t.last_analyzed,'MM-DD-YYYY') from dba_ind_subpartitions t, dba_indexes i where i.table_name = upper('&Table_name') and i.table_owner = upper(nvl('&Owner',user)) and i.owner = t.index_owner and i.index_name=t.index_name / clear breaks set echo on | |
Sample Output | |
*********** Table Level *********** Table Number Empty Average Chain Average Global User Sample Date Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ---------- T1 5,000 20 0 0 0 9 YES NO 5,000 06-30-2003 Column Column Distinct Number Global User Sample Date Name Details Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY ------------------------- ------------------------ -------- ------- ------- ---------- ------ ------ ---------- ---------- T1C1 NUMBER(22) 5,000 0 1 0 YES NO 5,000 06-30-2003 T1C2 NUMBER(22) 7 0 1 0 YES NO 5,000 06-30-2003 T1C3 NUMBER(22) 8 0 1 0 YES NO 5,000 06-30-2003 B Average Average Index Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date Name Unique Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY --------------- --------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ---------- UN_T1 NONUNIQUE 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003 Index Column Col Column Name Name Pos Details --------------- ------------------------- ---- ------------------------ UN_T1 T1C1 1 NUMBER(22) *************** Partition Level *************** Partition Number Empty Average Chain Average Global User Sample Date Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ---------- Q1 2,144 10 0 0 0 9 YES NO 2,144 06-30-2003 Q2 2,856 10 0 0 0 9 YES NO 2,856 06-30-2003 Partition Column Distinct Number Global User Sample Date Name Name Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY --------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ---------- Q1 T1C1 2,144 0 1 0 YES NO 2,144 06-30-2003 T1C2 3 0 1 0 YES NO 2,144 06-30-2003 T1C3 8 0 1 0 YES NO 2,144 06-30-2003 Q2 T1C1 2,856 0 1 0 YES NO 2,856 06-30-2003 T1C2 4 0 1 0 YES NO 2,856 06-30-2003 T1C3 8 0 1 0 YES NO 2,856 06-30-2003 B Average Average Index Partition Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date Name Name Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYYY --------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- ---------- UN_T1 Q1 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003 UN_T1 Q2 1 12 5,000 5,000 1 1 12 YES NO 5,000 06-30-2003 *************** SubPartition Level *************** Partition SubPartition Number Empty Average Chain Average Global User Sample Date Name Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY --------------- --------------- ---------- -------- -------- ------- ----- ------- ------ ------ ---------- ---------- Q1 SYS_SUBP497 803 5 0 0 0 9 YES NO 803 06-30-2003 Q2 SYS_SUBP499 1,072 5 0 0 0 9 YES NO 1,072 06-30-2003 Q1 SYS_SUBP498 1,341 5 0 0 0 9 YES NO 1,341 06-30-2003 Q2 SYS_SUBP500 1,784 5 0 0 0 9 YES NO 1,784 06-30-2003 Partition SubPartition Column Distinct Number Global User Sample Date Name Name Name Values Density Buckets NUM_NULLS Stats Stats Size MM-DD-YYYY --------------- --------------- ------------------------- -------- ------- ------- ---------- ------ ------ ---------- ---------- Q1 SYS_SUBP497 T1C1 803 0 1 0 YES NO 803 06-30-2003 SYS_SUBP498 T1C1 1,341 0 1 0 YES NO 1,341 06-30-2003 SYS_SUBP497 T1C2 3 0 1 0 YES NO 803 06-30-2003 SYS_SUBP498 T1C2 3 0 1 0 YES NO 1,341 06-30-2003 SYS_SUBP497 T1C3 3 0 1 0 YES NO 803 06-30-2003 SYS_SUBP498 T1C3 5 0 1 0 YES NO 1,341 06-30-2003 Q2 SYS_SUBP499 T1C1 1,072 0 1 0 YES NO 1,072 06-30-2003 SYS_SUBP500 T1C1 1,784 0 1 0 YES NO 1,784 06-30-2003 SYS_SUBP499 T1C2 4 0 1 0 YES NO 1,072 06-30-2003 SYS_SUBP500 T1C2 4 0 1 0 YES NO 1,784 06-30-2003 SYS_SUBP499 T1C3 3 0 1 0 YES NO 1,072 06-30-2003 SYS_SUBP500 T1C3 5 0 1 0 YES NO 1,784 06-30-2003 B Average Average Index Partition SubPartition Tree Leaf Distinct Number Leaf Blocks Data Blocks Cluster Global User Sample Date Name Name Name Level Blks Keys of Rows Per Key Per Key Factor Stats Stats Size MM-DD-YYY --------------- --------------- --------------- ----- ---- ---------- ---------- ----------- ----------- -------- ------ ------ ---------- --------- UN_T1 Q1 SYS_SUBP497 1 2 803 803 1 1 2 YES NO 803 06-30-200 UN_T1 SYS_SUBP498 1 3 1,341 1,341 1 1 3 YES NO 1,341 06-30-200 UN_T1 Q2 SYS_SUBP499 1 3 1,072 1,072 1 1 3 YES NO 1,072 06-30-200 UN_T1 SYS_SUBP500 1 4 1,784 1,784 1 1 4 YES NO 1,784 06-30-200 |
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/82387/viewspace-1016308/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 一個shell 指令碼用來同步表用的指令碼
- 推薦一個很好用的指令碼session snapper指令碼SessionAPP
- 用指令碼來講一個技術生活的故事指令碼
- 檢視錶的統計資訊SQL指令碼SQL指令碼
- 關於ORACLE自動統計CBO統計資訊Oracle
- mysql sql 行為的統計--一個很有用的指令碼MySql指令碼
- redis裡顯示key大小的shell指令碼Redis指令碼
- 分享檢視統計資訊非常好的指令碼指令碼
- 輸出顯示豎指令碼指令碼
- 自動檢查RAID 資訊的一個指令碼AI指令碼
- 關於ORACLE自動統計CBO統計資訊[轉帖]Oracle
- 一個很好的網站網站
- Oracle運維指令碼-收集統計資訊Oracle運維指令碼
- 顯示來自多個表的資料——JOIN
- [指令碼例項]——統計系統資訊指令碼指令碼
- 用python指令碼計算某一個檔案的行數Python指令碼
- 發一個指令碼來湊數的(obs1.2)指令碼
- 今天寫了一個統計執行sql次數的指令碼SQL指令碼
- 將SQL Server中所有表的列資訊顯示出來SQLServer
- ORACLE10g自動收集CBO統計資訊Oracle
- 一個很好的部落格地址
- SQL Server 監控統計阻塞指令碼資訊SQLServer指令碼
- Oracle 11g 統計資訊收集指令碼Oracle指令碼
- 表統計資訊匯出匯入指令碼指令碼
- 收集統計資訊的SQL指令碼(sosi.sql)--崔華大師SQL指令碼
- 通過shell指令碼生成資料統計資訊的報表指令碼
- 透過shell指令碼生成資料統計資訊的報表指令碼
- 在同一頁面顯示多個JavaScript統計圖表JavaScript
- 檢視mfc原始碼的一個很好的導航工具 (轉)原始碼
- 7 個日常實用的 Shell 拿來就用指令碼例項!指令碼
- 一個Eclipse程式碼顯示主題Eclipse
- 編寫shell指令碼實現統計一個小組的成績情況,統計資訊包括:總分,平均分,最高分...指令碼
- 分享一個很好的音樂站
- 一個還算可以用的啟動指令碼指令碼
- 用JAVASCIRPT寫的一個動態顯示日期的函式! (轉)Java函式
- 通過shell指令碼來統計段大小指令碼
- 用 Linux 命令顯示硬體資訊Linux
- 如何使用Neofetch個性化顯示Linux系統資訊Linux