DBA必备脚本：一键获得SQL访问的表及对应的索引，可协助快速定位SQL性能瓶颈

孔季雅 · 7 天前

我们的文章会在微信公众号IT民工的龙马人生和博客网站( www.htz.pw )同步更新，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

今天是我们分享Oracle性能脚本的第四天，也是最后一天，关于前面三期的脚本，大家可以参考：
Oracle DBA必备脚本：一秒就定位千行执行计划的SQL慢在哪一步
SQL优化必备脚本：Oracle获取绑定变量的字面SQL文本
Oracle DBA必备脚本：一键获取SQL性能数据，快速定位性能拐点
脚本的获取请关注公众号，回复脚本就可以获得方法。
在SQL优化中看完执行计划、获得字面SQL、获取历史性能指标后，接下来我们就需要查询SQL访问的表及表中的索引和它们的统计，这样可以帮助我们对执行计划的进行分析时提供数据。下面我们直接上脚本的执行结果，大家一看就明白脚本的作用。

SQL> @table_by_sqlid.sql
Session altered.
Enter value for sqlid: brzz4k1gksy8q
brzz4k1gksy8q
****************************************************************************************
TABLES
****************************************************************************************
TABLE LOG BUFFER NUM EMPTY OLD LAST
OWNER NAME TEMP POOL D PART ROWS BLOCKS BLOCKS BLOCK_SIZE(M) AVG_SIZE(M) STATS ANALYZED
--------------- ----------------------------------- ------- ------- - ---- ------------ ------------ ------------ ------------- ----------- ----- --------
HTZZ_GA HUAN YES.N DEFAULT 1 NO 1,958,247 16,364 0 127 104 NO 20160403
A_MODIFY_HTZ_HTZ YES.N DEFAULT 1 NO 1,058,843 15,197 0 118 56 NO 20160403
HTZ_PAYZR YES.N DEFAULT 1 NO 249 13 0 0 0 NO 20160404
HTZZ_MS HUAN YES.N DEFAULT 1 NO 2,119,314 17,388 0 135 115 NO 20160403
A_MODIFY_HTZ_HTZ YES.N DEFAULT 1 NO 1,812,182 24,377 0 190 96 NO 20160403
HTZ_PAYZR YES.N DEFAULT 1 NO 180 13 0 0 0 NO 20160402
HTZZ_MY HUAN YES.N DEFAULT 1 NO 4,364,453 35,800 0 279 237 NO 20160403
A_MODIFY_HTZ_HTZ YES.N DEFAULT 1 NO 8,605,916 85,577 0 668 459 NO 20160403
HTZ_PAYZR YES.N DEFAULT 1 NO 796 28 0 0 0 YES 20160404
HTZZ_YA HUAN YES.N DEFAULT 1 NO 952,608 7,888 0 61 50 NO 20160403
A_MODIFY_HTZ_HTZ YES.N DEFAULT 1 NO 1,477,275 18,257 0 142 78 NO 20160403
HTZ_PAYZR YES.N DEFAULT 1 NO 223 13 0 0 0 NO 20160317
12 rows selected.
****************************************************************************************
TABLE COLUMNS
****************************************************************************************
TABLE COLUMN Column NUM NUM AVG LAST
OWNER NAME NAME Date Type NL DENSITY NULLS DISTINCT BUCK COL LEN SAMPLE_SIZE HIST ANALYZED
--------------- ----------------------------------- ------------------------- --------------- -- ------------ ------------ -------- ----- ------------ ------------ ----- --------
HTZZ_GA HUAN HTZZ_ID NUMBER(22) N 0 0 1958247 254 7 5,662 HEIGH 20160403
CUST_ID NUMBER(22) N 0 0 1477120 1 8 1,958,247 NONE 20160403
HTZZ_NAME VARCHAR2(80) N 0 0 563392 254 8 5,662 HEIGH 20160403
ADDRESS_ID NUMBER(22) N 0 0 1949312 1 8 1,958,247 NONE 20160403
STATE VARCHAR2(3) Y 0 0 2 2 4 5,662 FREQU 20160403
STATE_DATE DATE(7) N 0 0 1435648 1 8 1,958,247 NONE 20160403
HTZZ_NBR_97 VARCHAR2(30) Y 0 0 1958247 254 11 5,662 HEIGH 20160403
ABM_FLAG VARCHAR2(1) Y 0 1,958,247 0 0 0 NONE 20160403
BATCH_BILLING_CYCLE_ID NUMBER(22) Y 1 10,633 1 1 5 1,947,614 NONE 20160403
A_MODIFY_HTZ_HTZ MODIFY_ID NUMBER(22) N 0 0 1058843 254 6 5,527 HEIGH 20160403
HTZZ_ID NUMBER(22) Y 0 0 228656 254 7 5,527 HEIGH 20160403
PAYMENT_ID NUMBER(22) Y 0 0 1057664 1 9 1,058,843 NONE 20160403
BILL_ID NUMBER(22) Y 0 475 1058843 1 8 1,058,368 NONE 20160403
MODIFY_TYPE NUMBER(22) Y 0 0 6 1 3 1,058,843 NONE 20160403
ACT_FLAG VARCHAR2(1) Y 1 0 1 1 2 1,058,843 NONE 20160403
STATE VARCHAR2(3) Y 0 0 4 3 4 5,527 FREQU 20160403
STATE_DATE DATE(7) Y 0 0 996608 1 8 1,058,843 NONE 20160403
CREATE_DATE DATE(7) Y 0 0 1000960 1 8 1,058,843 NONE 20160403
ERR_CODE VARCHAR2(12) Y 1 1,058,801 2 1 2 42 NONE 20160403
ERR_MESSAGE VARCHAR2(255) Y 1 1,058,801 2 1 2 42 NONE 20160403
HTZ_PAYZR HTZ_PAYZR_ID NUMBER(22) Y 0 0 248 248 6 249 FREQU 20160404
CUST_ID NUMBER(22) Y 0 0 220 220 8 249 FREQU 20160404
HTZ_PAYZR_NBR VARCHAR2(20) Y 0 0 125 125 17 249 FREQU 20160404
NAME VARCHAR2(80) Y 0 0 138 249 30 249 HEIGH 20160404
ADDRESS VARCHAR2(250) Y 0 0 133 1 28 249 NONE 20160404
HTZZ_NBR VARCHAR2(32) Y 0 0 150 1 13 249 NONE 20160404
BANK_NAME VARCHAR2(100) Y 0 0 104 1 30 249 NONE 20160404
BANK_HUAN VARCHAR2(30) Y 0 0 125 1 19 249 NONE 20160404
REGISTER_PROV VARCHAR2(50) Y 0 0 7 1 8 249 NONE 20160404
HTZ_PAYZR_TYPE NUMBER(22) Y 1 0 2 1 3 249 NONE 20160404
INVOICE_FLAG NUMBER(22) Y 1 0 2 1 3 249 NONE 20160404
RECEIVER VARCHAR2(100) Y 0 0 142 1 7 249 NONE 20160404
GESTOR_IDENTITY VARCHAR2(100) Y 0 0 138 1 19 249 NONE 20160404
GESTOR_PHONE VARCHAR2(100) Y 0 0 140 1 12 249 NONE 20160404
CREATED_DATE DATE(7) Y 0 0 226 1 8 249 NONE 20160404
STATE VARCHAR2(3) Y 0 0 2 2 4 249 FREQU 20160404
STATE_DATE DATE(7) Y 0 0 186 1 8 249 NONE 20160404
EFF_DATE DATE(7) Y 0 0 101 101 8 249 FREQU 20160404
EXP_DATE DATE(7) Y 0 0 32 32 8 249 FREQU 20160404
TAX_INVOICE_FLAG NUMBER(22) Y 0 0 1 1 3 249 FREQU 20160404
HTZZ_MS HUAN HTZZ_ID NUMBER(22) N 0 0 2119314 254 7 5,413 HEIGH 20160403
CUST_ID NUMBER(22) N 0 0 1696384 1 8 2,119,314 NONE 20160403
HTZZ_NAME VARCHAR2(80) N 0 0 534528 254 8 5,413 HEIGH 20160403
ADDRESS_ID NUMBER(22) N 0 0 2106624 1 8 2,119,314 NONE 20160403
STATE VARCHAR2(3) Y 0 0 2 2 4 5,414 FREQU 20160403
STATE_DATE DATE(7) N 0 0 1236352 1 8 2,119,314 NONE 20160403
HTZZ_NBR_97 VARCHAR2(30) Y 0 0 2099200 254 11 5,413 HEIGH 20160403
ABM_FLAG VARCHAR2(1) Y 0 2,119,314 0 0 0 NONE 20160403
BATCH_BILLING_CYCLE_ID NUMBER(22) Y 1 1,014 1 1 5 2,118,300 NONE 20160403
A_MODIFY_HTZ_HTZ MODIFY_ID NUMBER(22) N 0 0 1812182 254 6 5,558 HEIGH 20160403
HTZZ_ID NUMBER(22) Y 0 0 265984 254 7 5,558 HEIGH 20160403
PAYMENT_ID NUMBER(22) Y 0 0 1812182 1 9 1,812,182 NONE 20160403
BILL_ID NUMBER(22) Y 0 30,004 1778176 1 8 1,782,178 NONE 20160403
MODIFY_TYPE NUMBER(22) Y 0 0 7 1 3 1,812,182 NONE 20160403
ACT_FLAG VARCHAR2(1) Y 1 0 1 1 2 1,812,182 NONE 20160403
STATE VARCHAR2(3) Y 0 0 4 3 4 5,558 FREQU 20160403
STATE_DATE DATE(7) Y 0 0 1669376 1 8 1,812,182 NONE 20160403
CREATE_DATE DATE(7) Y 0 0 1667840 1 8 1,812,182 NONE 20160403
ERR_CODE VARCHAR2(12) Y 0 1,812,115 3 1 2 67 NONE 20160403
ERR_MESSAGE VARCHAR2(255) Y 0 1,812,115 3 1 2 67 NONE 20160403
HTZ_PAYZR HTZ_PAYZR_ID NUMBER(22) Y 0 0 179 179 6 180 FREQU 20160402
CUST_ID NUMBER(22) Y 0 0 163 163 8 180 FREQU 20160402
HTZ_PAYZR_NBR VARCHAR2(20) Y 0 0 144 144 17 180 FREQU 20160402
NAME VARCHAR2(80) Y 0 0 155 1 29 180 NONE 20160402
ADDRESS VARCHAR2(250) Y 0 0 131 1 27 180 NONE 20160402
HTZZ_NBR VARCHAR2(32) Y 0 0 149 1 11 180 NONE 20160402
BANK_NAME VARCHAR2(100) Y 0 0 95 1 28 180 NONE 20160402
BANK_HUAN VARCHAR2(30) Y 0 0 142 1 19 180 NONE 20160402
REGISTER_PROV VARCHAR2(50) Y 0 0 6 1 9 180 NONE 20160402
HTZ_PAYZR_TYPE NUMBER(22) Y 1 0 2 1 3 180 NONE 20160402
INVOICE_FLAG NUMBER(22) Y 1 0 2 1 3 180 NONE 20160402
RECEIVER VARCHAR2(100) Y 0 0 138 1 7 180 NONE 20160402
GESTOR_IDENTITY VARCHAR2(100) Y 0 0 138 1 19 180 NONE 20160402
GESTOR_PHONE VARCHAR2(100) Y 0 0 143 1 12 180 NONE 20160402
CREATED_DATE DATE(7) Y 0 0 179 1 8 180 NONE 20160402
STATE VARCHAR2(3) Y 0 0 2 2 4 180 FREQU 20160402
STATE_DATE DATE(7) Y 0 0 146 1 8 180 NONE 20160402
EFF_DATE DATE(7) Y 0 0 69 69 8 180 FREQU 20160402
EXP_DATE DATE(7) Y 0 0 18 18 8 180 FREQU 20160402
TAX_INVOICE_FLAG NUMBER(22) Y 0 0 1 1 3 180 FREQU 20160402
HTZZ_MY HUAN HTZZ_ID NUMBER(22) N 0 0 4364453 254 7 5,406 HEIGH 20160403
CUST_ID NUMBER(22) N 0 0 3269376 1 8 4,364,453 NONE 20160403
HTZZ_NAME VARCHAR2(80) N 0 0 1425280 254 8 5,406 HEIGH 20160403
ADDRESS_ID NUMBER(22) N 0 0 4283904 1 8 4,364,453 NONE 20160403
STATE VARCHAR2(3) Y 0 0 2 2 4 5,406 FREQU 20160403
STATE_DATE DATE(7) N 0 0 2170112 1 8 4,364,453 NONE 20160403
HTZZ_NBR_97 VARCHAR2(30) Y 0 0 4326400 254 11 5,406 HEIGH 20160403
ABM_FLAG VARCHAR2(1) Y 0 4,364,453 0 0 0 NONE 20160403
BATCH_BILLING_CYCLE_ID NUMBER(22) Y 1 9,032 2 1 5 4,355,421 NONE 20160403
A_MODIFY_HTZ_HTZ MODIFY_ID NUMBER(22) N 0 0 8605916 254 6 5,545 HEIGH 20160403
HTZZ_ID NUMBER(22) Y 0 0 759040 254 7 5,545 HEIGH 20160403
PAYMENT_ID NUMBER(22) Y 0 0 8605916 1 9 8,605,916 NONE 20160403
BILL_ID NUMBER(22) Y 0 76,936 8532992 1 8 8,528,980 NONE 20160403
MODIFY_TYPE NUMBER(22) Y 0 0 7 1 3 8,605,916 NONE 20160403
ACT_FLAG VARCHAR2(1) Y 1 0 1 1 2 8,605,916 NONE 20160403
STATE VARCHAR2(3) Y 0 0 4 3 4 5,545 FREQU 20160403
STATE_DATE DATE(7) Y 0 0 6995968 1 8 8,605,916 NONE 20160403
CREATE_DATE DATE(7) Y 0 0 6985216 1 8 8,605,916 NONE 20160403
ERR_CODE VARCHAR2(12) Y 0 8,605,705 2 2 2 211 FREQU 20160403
ERR_MESSAGE VARCHAR2(255) Y 1 8,605,705 2 1 2 211 NONE 20160403
HTZ_PAYZR HTZ_PAYZR_ID NUMBER(22) Y 0 0 795 1 6 796 NONE 20160404
CUST_ID NUMBER(22) Y 0 0 734 1 8 796 NONE 20160404
HTZ_PAYZR_NBR VARCHAR2(20) Y 0 0 430 254 17 796 HEIGH 20160404
NAME VARCHAR2(80) Y 0 0 442 1 29 796 NONE 20160404
ADDRESS VARCHAR2(250) Y 0 0 406 1 27 796 NONE 20160404
HTZZ_NBR VARCHAR2(32) Y 0 0 420 1 11 796 NONE 20160404
BANK_NAME VARCHAR2(100) Y 0 0 301 1 29 796 NONE 20160404
BANK_HUAN VARCHAR2(30) Y 0 0 423 1 19 796 NONE 20160404
REGISTER_PROV VARCHAR2(50) Y 0 0 12 1 8 796 NONE 20160404
HTZ_PAYZR_TYPE NUMBER(22) Y 1 0 2 1 3 796 NONE 20160404
INVOICE_FLAG NUMBER(22) Y 1 0 2 1 3 796 NONE 20160404
RECEIVER VARCHAR2(100) Y 0 0 376 1 7 796 NONE 20160404
GESTOR_IDENTITY VARCHAR2(100) Y 0 0 378 1 19 796 NONE 20160404
GESTOR_PHONE VARCHAR2(100) Y 0 0 384 1 12 796 NONE 20160404
CREATED_DATE DATE(7) Y 0 0 664 254 8 796 HEIGH 20160404
STATE VARCHAR2(3) Y 0 0 2 2 4 796 FREQU 20160404
STATE_DATE DATE(7) Y 0 0 500 1 8 796 NONE 20160404
EFF_DATE DATE(7) Y 0 0 188 188 8 796 FREQU 20160404
EXP_DATE DATE(7) Y 0 0 76 76 8 796 FREQU 20160404
TAX_INVOICE_FLAG NUMBER(22) Y 0 0 1 1 3 796 FREQU 20160404
HTZZ_YA HUAN HTZZ_ID NUMBER(22) N 0 0 952608 40 7 476,935 HEIGH 20160403
CUST_ID NUMBER(22) N 0 0 582509 40 8 476,543 HEIGH 20160403
HTZZ_NAME VARCHAR2(80) N 0 0 252063 40 8 477,732 HEIGH 20160403
ADDRESS_ID NUMBER(22) N 0 0 952608 40 8 477,111 HEIGH 20160403
STATE VARCHAR2(3) Y 0 0 2 2 4 477,230 FREQU 20160403
STATE_DATE DATE(7) N 0 0 386552 40 8 477,329 HEIGH 20160403
HTZZ_NBR_97 VARCHAR2(30) Y 0 0 952608 40 11 478,070 HEIGH 20160403
ABM_FLAG VARCHAR2(1) Y 0 952,608 0 0 0 NONE 20160403
BATCH_BILLING_CYCLE_ID NUMBER(22) Y 0 896 1 1 5 476,926 FREQU 20160403
A_MODIFY_HTZ_HTZ MODIFY_ID NUMBER(22) N 0 0 1477275 254 6 5,566 HEIGH 20160403
HTZZ_ID NUMBER(22) Y 0 0 172528 254 7 5,566 HEIGH 20160403
PAYMENT_ID NUMBER(22) Y 0 0 1477275 1 9 1,477,275 NONE 20160403
BILL_ID NUMBER(22) Y 0 18,216 1447552 1 8 1,459,059 NONE 20160403
MODIFY_TYPE NUMBER(22) Y 0 0 7 1 3 1,477,275 NONE 20160403
ACT_FLAG VARCHAR2(1) Y 1 0 1 1 2 1,477,275 NONE 20160403
STATE VARCHAR2(3) Y 0 0 4 3 4 5,566 FREQU 20160403
STATE_DATE DATE(7) Y 0 0 1338368 1 8 1,477,275 NONE 20160403
CREATE_DATE DATE(7) Y 0 0 1337216 1 8 1,477,275 NONE 20160403
ERR_CODE VARCHAR2(12) Y 1 1,477,227 2 1 2 48 NONE 20160403
ERR_MESSAGE VARCHAR2(255) Y 1 1,477,227 2 1 2 48 NONE 20160403
HTZ_PAYZR HTZ_PAYZR_ID NUMBER(22) Y 0 0 223 1 6 223 NONE 20160317
CUST_ID NUMBER(22) Y 0 0 179 179 8 223 FREQU 20160317
HTZ_PAYZR_NBR VARCHAR2(20) Y 0 0 139 139 17 223 FREQU 20160317
NAME VARCHAR2(80) Y 0 0 145 1 31 223 NONE 20160317
ADDRESS VARCHAR2(250) Y 0 0 134 1 26 223 NONE 20160317
HTZZ_NBR VARCHAR2(32) Y 0 0 173 1 12 223 NONE 20160317
BANK_NAME VARCHAR2(100) Y 0 0 111 1 30 223 NONE 20160317
BANK_HUAN VARCHAR2(30) Y 0 0 139 1 19 223 NONE 20160317
REGISTER_PROV VARCHAR2(50) Y 0 0 6 1 8 223 NONE 20160317
HTZ_PAYZR_TYPE NUMBER(22) Y 1 0 2 1 3 223 NONE 20160317
INVOICE_FLAG NUMBER(22) Y 1 0 2 1 3 223 NONE 20160317
RECEIVER VARCHAR2(100) Y 0 0 136 1 7 223 NONE 20160317
GESTOR_IDENTITY VARCHAR2(100) Y 0 0 134 1 19 223 NONE 20160317
GESTOR_PHONE VARCHAR2(100) Y 0 0 145 1 12 223 NONE 20160317
CREATED_DATE DATE(7) Y 0 0 221 1 8 223 NONE 20160317
STATE VARCHAR2(3) Y 0 0 2 2 4 223 FREQU 20160317
STATE_DATE DATE(7) Y 0 0 173 1 8 223 NONE 20160317
EFF_DATE DATE(7) Y 0 0 87 87 8 223 FREQU 20160317
EXP_DATE DATE(7) Y 0 0 49 49 8 223 FREQU 20160317
TAX_INVOICE_FLAG NUMBER(22) Y 0 0 1 1 3 223 FREQU 20160317
160 rows selected.
****************************************************************************************
INDEX INFO
****************************************************************************************
TABLE TABLE Index COLUMN Col
OWNER NAME Name Unique NAME Pos DESC
--------------- ----------------------------------- ------------------------------ --------- ------------------------- ---- ----
HTZZ_GA HUAN IDX_HTZZ_HTZZ_NBR_97 NONUNIQUE HTZZ_NBR_97 1 ASC
IDX_HTZZ_ADDRESS_ID NONUNIQUE ADDRESS_ID 1 ASC
IDX_HTZZ_CUST_ID NONUNIQUE CUST_ID 1 ASC
PK_HUAN NONUNIQUE HTZZ_ID 1 ASC
A_MODIFY_HTZ_HTZ IDX_AMTA_SA NONUNIQUE STATE 1 ASC
HTZZ_ID 2 ASC
IDX_MODIFY_TAX_API_HTZZ_ID NONUNIQUE HTZZ_ID 1 ASC
IDX_MODIFY_TAX_API_BILL_ID NONUNIQUE BILL_ID 1 ASC
IDX_MODIFY_TAX_API_PAYMENT_ID NONUNIQUE PAYMENT_ID 1 ASC
PK_MODIFY_TAX_API UNIQUE MODIFY_ID 1 ASC
HTZ_PAYZR IDX_HTZ_PAYZR_CUSTID NONUNIQUE CUST_ID 1 ASC
IDX_HTZ_PAYZR_NBR NONUNIQUE HTZ_PAYZR_NBR 1 ASC
HTZZ_MS HUAN IDX_HTZZ_HTZZ_NBR_97 NONUNIQUE HTZZ_NBR_97 1 ASC
IDX_HTZZ_ADDRESS_ID NONUNIQUE ADDRESS_ID 1 ASC
IDX_HTZZ_CUST_ID NONUNIQUE CUST_ID 1 ASC
PK_HUAN NONUNIQUE HTZZ_ID 1 ASC
A_MODIFY_HTZ_HTZ IDX_AMTA_SA NONUNIQUE STATE 1 ASC
HTZZ_ID 2 ASC
IDX_MODIFY_TAX_API_HTZZ_ID NONUNIQUE HTZZ_ID 1 ASC
IDX_MODIFY_TAX_API_BILL_ID NONUNIQUE BILL_ID 1 ASC
IDX_MODIFY_TAX_API_PAYMENT_ID NONUNIQUE PAYMENT_ID 1 ASC
PK_MODIFY_TAX_API UNIQUE MODIFY_ID 1 ASC
HTZ_PAYZR IDX_HTZ_PAYZR_CUSTID NONUNIQUE CUST_ID 1 ASC
IDX_HTZ_PAYZR_NBR NONUNIQUE HTZ_PAYZR_NBR 1 ASC
HTZZ_MY HUAN IDX_HTZZ_HTZZ_NBR_97 NONUNIQUE HTZZ_NBR_97 1 ASC
IDX_HTZZ_ADDRESS_ID NONUNIQUE ADDRESS_ID 1 ASC
IDX_HTZZ_CUST_ID NONUNIQUE CUST_ID 1 ASC
PK_HUAN NONUNIQUE HTZZ_ID 1 ASC
A_MODIFY_HTZ_HTZ IDX_AMTA_SA NONUNIQUE STATE 1 ASC
HTZZ_ID 2 ASC
IDX_MODIFY_TAX_API_HTZZ_ID NONUNIQUE HTZZ_ID 1 ASC
IDX_MODIFY_TAX_API_BILL_ID NONUNIQUE BILL_ID 1 ASC
IDX_MODIFY_TAX_API_PAYMENT_ID NONUNIQUE PAYMENT_ID 1 ASC
PK_MODIFY_TAX_API UNIQUE MODIFY_ID 1 ASC
HTZ_PAYZR IDX_HTZ_PAYZR_CUSTID NONUNIQUE CUST_ID 1 ASC
IDX_HTZ_PAYZR_NBR NONUNIQUE HTZ_PAYZR_NBR 1 ASC
HTZZ_YA HUAN IDX_HTZZ_HTZZ_NBR_97 NONUNIQUE HTZZ_NBR_97 1 ASC
IDX_HTZZ_ADDRESS_ID NONUNIQUE ADDRESS_ID 1 ASC
IDX_HTZZ_CUST_ID NONUNIQUE CUST_ID 1 ASC
PK_HUAN NONUNIQUE HTZZ_ID 1 ASC
A_MODIFY_HTZ_HTZ IDX_AMTA_SA NONUNIQUE STATE 1 ASC
HTZZ_ID 2 ASC
IDX_MODIFY_TAX_API_HTZZ_ID NONUNIQUE HTZZ_ID 1 ASC
IDX_MODIFY_TAX_API_BILL_ID NONUNIQUE BILL_ID 1 ASC
IDX_MODIFY_TAX_API_PAYMENT_ID NONUNIQUE PAYMENT_ID 1 ASC
PK_MODIFY_TAX_API UNIQUE MODIFY_ID 1 ASC
HTZ_PAYZR IDX_HTZ_PAYZR_CUSTID NONUNIQUE CUST_ID 1 ASC
IDX_HTZ_PAYZR_NBR NONUNIQUE HTZ_PAYZR_NBR 1 ASC
48 rows selected.
****************************************************************************************
INDEX STATS
****************************************************************************************
B Average Average
TABLE Index Tree Leaf Distinct NUM Leaf Blocks Data Blocks Cluster LAST
OWNER NAME Name LOG STATUS Level Blks Keys ROWS Per Key Per Key Factor D ANALYZED
--------------- ----------------------------------- ------------------------------ --- ------ ----- -------- ------------ ------------ ----------- ----------- ------------ - --------
HTZZ_GA A_MODIFY_HTZ_HTZ IDX_AMTA_SA NO VALID 2 6,678 246,246 1,071,929 1 4 1,049,369 1 20160403
PK_MODIFY_TAX_API YES VALID 2 5,132 1,058,843 1,058,843 1 1 882,178 1 20160403
IDX_MODIFY_TAX_API_PAYMENT_ID YES VALID 2 6,094 1,057,664 1,058,843 1 1 903,004 1 20160403
IDX_MODIFY_TAX_API_HTZZ_ID YES VALID 2 8,297 228,656 1,100,697 1 4 1,073,840 1 20160403
IDX_MODIFY_TAX_API_BILL_ID YES VALID 2 5,788 1,058,368 1,058,368 1 1 887,082 1 20160403
HTZ_PAYZR IDX_HTZ_PAYZR_NBR YES VALID 0 1 125 249 1 1 172 1 20160404
HUAN IDX_HTZZ_CUST_ID NO VALID 2 7,483 1,477,120 1,933,490 1 1 1,578,028 1 20160403
IDX_HTZZ_ADDRESS_ID NO VALID 2 8,884 1,904,876 1,904,876 1 1 1,254,457 1 20160403
IDX_HTZZ_HTZZ_NBR_97 NO VALID 2 8,936 1,958,247 1,970,858 1 1 1,423,949 1 20160403
HTZ_PAYZR IDX_HTZ_PAYZR_CUSTID YES VALID 0 1 220 249 1 1 213 1 20160404
HUAN PK_HUAN NO VALID 2 6,053 1,958,247 1,980,264 1 1 1,404,259 1 20160403
HTZZ_MS HUAN IDX_HTZZ_ADDRESS_ID NO VALID 2 8,490 2,022,856 2,022,856 1 1 1,157,040 1 20160403
IDX_HTZZ_CUST_ID NO VALID 2 8,211 1,696,384 2,070,262 1 1 1,444,799 1 20160403
PK_HUAN NO VALID 2 6,331 2,096,791 2,096,791 1 1 1,083,732 1 20160403
HTZ_PAYZR IDX_HTZ_PAYZR_NBR YES VALID 0 1 144 180 1 1 146 1 20160402
IDX_HTZ_PAYZR_CUSTID YES VALID 0 1 163 180 1 1 149 1 20160402
A_MODIFY_HTZ_HTZ IDX_MODIFY_TAX_API_BILL_ID YES VALID 2 9,955 1,778,176 1,782,186 1 1 1,489,988 1 20160403
IDX_MODIFY_TAX_API_HTZZ_ID YES VALID 2 11,030 265,984 1,809,081 1 6 1,740,956 1 20160403
IDX_MODIFY_TAX_API_PAYMENT_ID YES VALID 2 10,395 1,812,182 1,812,185 1 1 1,535,140 1 20160403
PK_MODIFY_TAX_API YES VALID 2 9,165 1,930,018 1,930,018 1 1 1,595,942 1 20160403
IDX_AMTA_SA NO VALID 2 9,132 280,019 1,739,740 1 5 1,668,714 1 20160403
HUAN IDX_HTZZ_HTZZ_NBR_97 NO VALID 2 9,732 2,099,200 2,122,670 1 1 1,114,238 1 20160403
HTZZ_MY A_MODIFY_HTZ_HTZ IDX_AMTA_SA NO VALID 2 28,305 759,040 8,208,572 1 10 7,943,750 1 20160403
PK_MODIFY_TAX_API YES VALID 2 44,461 8,285,595 8,285,595 1 1 7,553,864 1 20160403
IDX_MODIFY_TAX_API_PAYMENT_ID YES VALID 3 64,236 8,605,916 8,993,927 1 1 8,257,295 1 20160403
IDX_MODIFY_TAX_API_HTZZ_ID YES VALID 2 42,804 759,040 8,946,903 1 11 8,683,663 1 20160403
IDX_MODIFY_TAX_API_BILL_ID YES VALID 2 53,752 8,387,122 8,387,122 1 1 7,668,391 1 20160403
HUAN IDX_HTZZ_HTZZ_NBR_97 NO VALID 2 19,005 4,326,400 4,587,079 1 1 2,892,702 1 20160403
HTZ_PAYZR IDX_HTZ_PAYZR_CUSTID YES VALID 1 3 734 796 1 1 710 1 20160404
HUAN PK_HUAN NO VALID 2 12,846 4,364,453 4,376,430 1 1 2,732,681 1 20160403
IDX_HTZZ_CUST_ID NO VALID 2 15,009 3,269,376 4,239,396 1 1 3,635,087 1 20160403
IDX_HTZZ_ADDRESS_ID NO VALID 2 17,428 4,283,904 4,451,405 1 1 2,089,651 1 20160403
HTZ_PAYZR IDX_HTZ_PAYZR_NBR YES VALID 1 4 430 796 1 1 576 1 20160404
HTZZ_YA HTZ_PAYZR IDX_HTZ_PAYZR_NBR YES VALID 0 1 139 223 1 1 184 1 20160317
IDX_HTZ_PAYZR_CUSTID YES VALID 0 1 179 223 1 1 194 1 20160317
A_MODIFY_HTZ_HTZ IDX_MODIFY_TAX_API_BILL_ID YES VALID 2 7,562 1,447,552 1,459,059 1 1 1,301,466 1 20160403
IDX_MODIFY_TAX_API_HTZZ_ID YES VALID 2 7,670 172,528 1,470,080 1 8 1,435,352 1 20160403
IDX_MODIFY_TAX_API_PAYMENT_ID YES VALID 2 7,992 1,477,275 1,477,275 1 1 1,319,329 1 20160403
PK_MODIFY_TAX_API YES VALID 2 6,565 1,489,211 1,489,211 1 1 1,333,579 1 20160403
IDX_AMTA_SA NO VALID 2 6,942 199,377 1,573,723 1 7 1,540,332 1 20160403
HUAN IDX_HTZZ_HTZZ_NBR_97 NO VALID 2 4,408 949,926 949,926 1 1 509,166 1 20160403
IDX_HTZZ_ADDRESS_ID NO VALID 2 4,116 952,608 954,894 1 1 603,550 1 20160403
PK_HUAN NO VALID 2 2,942 941,430 941,430 1 1 512,488 1 20160403
IDX_HTZZ_CUST_ID NO VALID 2 3,940 582,509 980,708 1 1 682,828 1 20160403
44 rows selected.
****************************************************************************************
PARTITION TABLE
****************************************************************************************
no rows selected
****************************************************************************************
display every partition info
****************************************************************************************
no rows selected

复制代码

脚本的获取

关注公众号后回复”脚本“二字即可。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
电话、微信、QQ：18081072613
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

账号		自动登录	找回密码
密码			立即注册

DBA必备脚本：一键获得SQL访问的表及对应的索引，可协助快速定位SQL性能瓶颈

浏览过的版块

签约作者