文档章节

查看索引的状态

易野
 易野
发布于 2017/08/26 21:54
字数 845
阅读 19
收藏 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

© 著作权归作者所有

共有 人打赏支持
易野
粉丝 4
博文 171
码字总数 126481
作品 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

没有更多内容

加载失败,请刷新页面

加载更多

slot分发内容

slot元素作为组件模板之中的内容分发插槽。这个元素自身将被替换。 有 name 特性的 slot 称为具名 slot。 有 slot 特性的内容将分发到名字相匹配的具名 slot。 内容分发就是指混合父组件的内...

Carbenson
10分钟前
1
0
python开发入门

1.执行python文件 # python ./demo.py 2.Python ImportError: No module named 'requests'异常 解决方法: # pip install requests;...

硅谷课堂
11分钟前
1
0
官宣,PyTorch 1.0 稳定版本现已推出

简评:快来一起快乐地学习吧。 随着 PyTorch 生态系统和社区继续为开发人员提供有趣的新项目和教育资源,今天(12 月 7日)在 NeurIPS 会议上发布了 PyTorch 1.0 稳定版。研究人员和工程师现...

极光推送
24分钟前
1
0
对比理解adr,ldr指令

很多人在写简单的裸机代码或分析uboot时,常常遇到adr ldr指令。却分不清这2者的区别,今天就来谈谈adr与ldr指令。 参照韦老师的代码和Makefile写了test_adr.S: .text .globl _start _start...

天王盖地虎626
35分钟前
2
0
将spring boot 项目注册为Linux的服务

springboot 注册为Linux系统服务 springboot 注册为Linux系统服务

miaojiangmin
36分钟前
4
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部