文档章节

查看索引的状态

易野
 易野
发布于 2017/08/26 21:54
字数 845
阅读 17
收藏 0

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=34212043984587&id=1019722.6&_adf.ctrl-state=jbg21eo77_41

Script 1: Index Fragmentation 
 
TFSIFRAG.SQL provides information critical in determining whether an 
index is a candidate for rebuilding.  An index is a candidate for 
rebuilding when a relatively high number of index leaf row deletes have 
occured. 
 
 Please note: This report does not indicate the actual index "balance."  

 
Script 2: Index Statistics 
 
TFSISTAT.SQL prints a variety of statistics about the given index.
Statistics include B*tree height, the number of distinct and repeated
keys, the number of  branch rows and blocks, the number of leaf rows
and blocks and information about space utilization. 
 
 Please note: You will receive an "ORA-01476:  divisor is equal to
 zero" for an index on a table into which no rows have been inserted.  
 
 
Script 3: Index Keys for a Table  
 
TFSINKEY.SQL prints uniqueness, name and column information of indexes for the
given table and owner.  The arguments are not case-sensitive, but may not 
include wildcards.  The script will behave unreliably for an index not owned 
by the owner of its base table.

 

========= 
Script #1: 
==========
 
SET ECHO off 
REM NAME:   TFSIFRAG.SQL 
REM USAGE:"@path/tfsifrag schema_name index_name" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on INDEX_STATS 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Reports index fragmentation statistics 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                     Index Fragmentation Statistic 
REM                 
REM    index name        S_EMP_USERID_UK 
REM    leaf rows deleted            0 
REM    leaf rows in use            25 
REM    index badness            0.000   
REM  
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
set verify off  
def ownr  = &&1  
def name  = &&2  
  
ttitle - 
  center 'Index Fragmentation Statistic'   skip 2 
  
set heading off  
  
col name                 newline  
col lf_blk_rows          newline  
col del_lf_rows          newline  
col ibadness newline   
  
validate index &ownr..&name;  
  
select  
  'index name        '||name,  
  'leaf rows deleted '||to_char(del_lf_rows,'999,999,990')  del_lf_rows,  
  'leaf rows in use  '||to_char(lf_rows-del_lf_rows,'999,999,990')  lf_blk_rows,	  
  'index badness     '||to_char(del_lf_rows/(lf_rows+0.00001),'999,990.999') ibadness  
from  
  index_stats  
/  
  
undef ownr  
undef name  
set verify on
 
 
==============
Sample Output: 
==============
 
                         Index Fragmentation Statistic 
 
 
index name                   S_EMP_USERID_UK 
leaf rows deleted            0 
leaf rows in use             25 
index badness                0.000 
 
 
 
 
========== 
Script #2: 
==========
 
SET ECHO off 
REM NAME:   TFSISTAT.SQL 
REM USAGE:"@path/tfsistat schema_name index_name" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on INDEX_STATS 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    Report index statistics. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                                Index Statistics  
REM 
REM    S_EMP_USERID_UK  
REM    ----------------------------------------------------------  
REM    height                          1 
REM    blocks                          5 
REM    del_lf_rows                     0  
REM    del_lf_rows_len                 0 
REM    distinct_keys                  25 
REM    most_repeated_key               1  
REM    btree_space                 1,876 
REM    used_space                    447  
REM    pct_used                       24 
REM    rows_per_key                    1 
REM    blks_gets_per_access            2  
REM    lf_rows                        25            br_rows               0  
REM    lf_blks                         1            br_blks               0 
REM    lf_rows_len                   447            br_rows_len           0  
REM    lf_blk_len                  1,876            br_blk_len            0   
REM  
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
set verify off
def ownr        = &&1 
def name        = &&2 
 
ttitle - 
  center  'Index Statistics'  skip 2 
 
set heading off 
 
col name   newline 
col headsep              newline 
col height               newline 
col blocks               newline 
col lf_rows              newline 
col lf_blks        	 newline 
col lf_rows_len          newline 
col lf_blk_len           newline 
col br_rows              newline 
col br_blks              newline 
col br_rows_len          newline 
col br_blk_len           newline 
col del_lf_rows          newline 
col del_lf_rows_len      newline 
col distinct_keys        newline 
col most_repeated_key    newline 
col btree_space          newline 
col used_space    	 newline 
col pct_used             newline 
col rows_per_key         newline 
col blks_gets_per_access newline 
 
validate index &ownr..&name; 
 
select 
  name, 
  '----------------------------------------------------------'    headsep, 
  'height               '||to_char(height,     '999,999,990')     height, 
  'blocks               '||to_char(blocks,     '999,999,990')     blocks, 
  'del_lf_rows          '||to_char(del_lf_rows,'999,999,990')     del_lf_rows, 
  'del_lf_rows_len      '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len, 
  'distinct_keys        '||to_char(distinct_keys,'999,999,990')   distinct_keys, 
  'most_repeated_key    '||to_char(most_repeated_key,'999,999,990') most_repeated_key, 
  'btree_space          '||to_char(btree_space,'999,999,990')       btree_space, 
  'used_space           '||to_char(used_space,'999,999,990')        used_space, 
  'pct_used                     '||to_char(pct_used,'990')          pct_used, 
  'rows_per_key         '||to_char(rows_per_key,'999,999,990')      rows_per_key, 
  'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access, 
  'lf_rows      '||to_char(lf_rows,    '999,999,990')||'        '||+ 
  'br_rows      '||to_char(br_rows,    '999,999,990')                  br_rows, 
  'lf_blks      '||to_char(lf_blks,    '999,999,990')||'        '||+ 
  'br_blks      '||to_char(br_blks,    '999,999,990')                  br_blks, 
  'lf_rows_len  '||to_char(lf_rows_len,'999,999,990')||'        '||+ 
  'br_rows_len  '||to_char(br_rows_len,'999,999,990')                  br_rows_len, 
  'lf_blk_len   '||to_char(lf_blk_len, '999,999,990')||'        '||+ 
  'br_blk_len   '||to_char(br_blk_len, '999,999,990')                br_blk_len 
from 
  index_stats 
/ 
 
undef ownr 
undef name 
set verify on
 
 
==============
Sample Output: 
==============
 
                                Index Statistics                
S_EMP_USERID_UK 
----------------------------------------------------------  
height                          1  
blocks                          5  
del_lf_rows                     0  
del_lf_rows_len                 0   
distinct_keys                  	25  
most_repeated_key               1  
btree_space                 	1,876 
used_space                    	447  
pct_used                       	24  
rows_per_key                    1  
blks_gets_per_access            2  
lf_rows                		25         
br_rows                 	0 
lf_blks				1         
br_blks                 	0  
lf_rows_len           		447         
br_rows_len            		0  
lf_blk_len          		1,876         
br_blk_len              	0 
 
 
 
 
========== 
Script #3: 
==========  
 
SET ECHO off 
REM NAME:   TFSIKEYS.SQL 
REM USAGE:"@path/tfsikeys idx_owner table_name" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM    SELECT on DBA_IND_COLUMNS and DBA_INDEXES 
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM Shows the index keys for a particular table. 
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM             Index Keys Summary 
REM 
REM    Uniqueness                Index Name                    Column Name 
REM    ---------- ----------------------------------------  ------------------ 
REM    UNIQUE                    SCOTT.S_EMP_ID_PK               ID  
REM 
REM    UNIQUE                    SCOTT.S_EMP_USERID_UK           USERID 
REM   
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
set verify off
def ixowner	= &&1 
def tabname	= &&2 
 
ttitle - 
   center  'Index Keys Summary'  skip 2 
 
col uniq    format a10 heading 'Uniqueness'  justify c trunc 
col indname format a40 heading 'Index Name'  justify c trunc 
col colname format a25 heading 'Column Name' justify c trunc 
 
break - 
  on indname skip 1 - 
  on uniq 
 
select 
  ind.uniqueness                  uniq, 
  ind.owner||'.'||col.index_name  indname, 
  col.column_name                 colname 
from 
  dba_ind_columns  col, 
  dba_indexes      ind 
where 
  ind.owner = upper('&ixowner') 
    and 
  ind.table_name = upper('&tabname') 
    and 
  col.index_owner = ind.owner  
    and 
  col.index_name = ind.index_name 
order by 
  col.index_name, 
  col.column_position 
/ 
 
undef ixowner 
undef tabname 
set verify on


==============
Sample Output: 
==============

 
         Index Keys Summary 
 
 
Uniqueness                Index Name                    Column Name 
---------- ---------------------------------------- ---------------------- 
UNIQUE                SCOTT.S_EMP_ID_PK                        ID 
                                                                       
UNIQUE                SCOTT.S_EMP_USERID_UK                    USERID

© 著作权归作者所有

共有 人打赏支持
易野
粉丝 3
博文 158
码字总数 119510
作品 0
深圳
线上 ELK 集群健康值 red 状态问题排查与解决

之前一直运行正常的数据分析平台,最近一段时间没有注意发现日志索引数据一直未生成,大概持续了n多天,当前状态: 单台机器, Elasticsearch(下面称ES)单节点(空集群),1000+shrads, 约200G大...

haifeiWu
08/07
0
0
Elasticsearch 5.4 Indices(索引) API

前言 声明:本博客根据ELasticsearch官网文档翻译整理,转载请注明出处:http://blog.csdn.net/napoay 一、索引管理 1.1 创建索引 创建索引 默认分片为5,副本为1. 创建索引并指定分片数和副...

napoay
2017/06/15
0
0
MongoDB学习笔记(四)--索引 && 性能优化

索引 基础索引 用到ensureIndex方法建立索引,1为升序,-1为降序。 MongoDB数据库在创建集合的时候,默认会为_id创建索引。 注:当系统已有大量数据时,创建索引就是一个非常耗时的工作,只需...

老朱教授
2017/10/01
0
0
查看Oracle当前用户下的信息(用户,表视图,索引,表空间,同义词等)

1、用户   查看当前用户的缺省表空间 SQL>select username,defaulttablespace from userusers;   查看当前用户的角色 SQL>select * from user_role_privs;   查看当前用户的系统权限和...

lq2011
2017/12/16
0
0
mysql性能优化(一)

mysql性能优化、慢查询分析、优化索引和配置 一.每项的基本思路步骤 1.性能瓶颈定位:show命令、慢查询日志、explain分析查询、profiling分析查询、 2.索引及查询优化 3.配置优化 二.mysql是...

攀岩人生
2017/06/16
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Spring详解

Spring详解(一)------概述 目录 1、什么是 Spring ? 2、Spring 起源 3、Spring 特点 4、Spring 框架结构 5、Spring 框架特征 6、Spring 优点   本系列教程我们将对 Spring 进行详解的介绍...

DemonsI
21分钟前
0
0
CentOS7系统Nginx安装

1、下载nginx,官方网站https://nginx.org wget https://nginx.org/download/nginx-1.14.0.tar.gz 2、下载Nginx Sticky Module,官方网站https://bitbucket.org/nginx-goodies/nginx-sticky-......

m_lm
24分钟前
0
0
使用zTree树控件(二)

1:treeNode.checked用于判断是勾选还是取消勾选。(treeNode指的是节点) 2:treeObj.transformToArray(nodes)用于查询nodes节点下的所有子节点,json格式。(treeObj为数的id)...

uug
24分钟前
0
0
export, import 和 export default的区别

ES6的两个功能: export 和 import export 对外输出模块 import 引入(加载)进来一个模块 一、export => import 单个变量 export var name = "lishi" 在其他文件里引用 import {name} f...

Js_Mei
29分钟前
1
0
打造RecyclerView的n级列表

先上效果图: 1.该多级列表的优势: 支持无限级列表展开 基于一个recyclerView实现 可以自定义每一级item的样式,定制化更强 2.设计的思路 数据结构List<ItemBean>,ItemBean类中有变量List<...

WelliJohn
38分钟前
1
1

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部