Oracle Live

11/04/2016

QUERY – Indices

Filed under: Queries — mogukiller @ 11:32 pm
Tags:
-- Estado de los indices
-- Tamaño de un indice
-- Informacion de las particiones de un indice
-- Indica que queries utilizan ese indice y de que manera.
-- Comprobacion del uso que se esta haciendo del indice.
-- Degradacion de los indices.
-- Index Columns for 11g
-- Index Columns for 10g
-- Index Columns for 10g

COLUMN index_owner       format a15 wrap           heading "Index|Owner"
COLUMN table_name        format a30 wrap           heading "Table|Name"
COLUMN index_name        format a30 wrap           heading "Index|Name"
COLUMN list			 format a70 wrap           heading "Columns|List"

SELECT INDEX_OWNER, TABLE_NAME, INDEX_NAME, SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME, ','), 2) LIST
  FROM (SELECT INDEX_OWNER,
     TABLE_NAME,
     COLUMN_NAME,
     INDEX_NAME,
     COUNT(*) OVER(PARTITION BY INDEX_NAME) CNT,
     ROW_NUMBER() OVER(PARTITION BY INDEX_NAME ORDER BY COLUMN_NAME) SEQ
     FROM DBA_IND_COLUMNS
     WHERE INDEX_OWNER LIKE UPPER ('%&ENTER_OWNER_NAME%') AND
   TABLE_NAME LIKE UPPER ('%&ENTER_TABLE_NAME%'))
 WHERE SEQ = CNT
 START WITH SEQ = 1
CONNECT BY PRIOR SEQ + 1 = SEQ
       AND PRIOR INDEX_NAME = INDEX_NAME
ORDER BY TABLE_NAME;

-- Index Columns for 11g

BREAK ON INDEX_OWNER ON TABLE_NAME SKIP 1
COLUMN index_owner       format a15 wrap           heading "Index|Owner"
COLUMN table_name        format a30 wrap           heading "Table|Name"
COLUMN index_name        format a30 wrap           heading "Index|Name"
COLUMN columnas			 format a70 wrap           heading "Columns|List"
SELECT DISTINCT
 INDEX_OWNER, TABLE_NAME, INDEX_NAME,
 LISTAGG(COLUMN_NAME,',') WITHIN GROUP (ORDER BY COLUMN_POSITION) OVER (PARTITION BY INDEX_OWNER,TABLE_NAME,INDEX_NAME) AS COLUMNAS
FROM 
 DBA_IND_COLUMNS
WHERE
 INDEX_OWNER LIKE UPPER ('%&ENTER_OWNER_NAME%') 
 AND TABLE_NAME LIKE UPPER ('%&ENTER_TABLE_NAME%');
 
/*
Index           Table                          Index                          Columns
Owner           Name                           Name                           List
--------------- ------------------------------ ------------------------------ ----------------------------------------------------------------------
MG_OLAP        MG_RPT_COMPANY_DIM            FN_ERCOMPDIM_COMPCD_I          SYS_NC00020$
MG_OLAP        MG_RPT_COMPANY_DIM            IDX_LKP_COMPANY_DIM_I          COMPANY_CD,COMPANY_KEY
MG_OLAP        MG_RPT_COMPANY_DIM            DBA_TCS_IDX_1_I                COMPANY_CD
MG_OLAP        MG_RPT_COMPANY_DIM            IDX_LK2_COMPANY_DIM_I          COMPANY_KEY,END_DT,FLEX_FIELD1,START_DT
MG_OLAP        MG_RPT_COMPANY_DIM            DBA_TCS_IDX_3_I                FLEX_FIELD2
MG_OLAP        MG_RPT_COMPANY_DIM            IDX_COMPANY_DIM_FF1_I          FLEX_FIELD1
MG_OLAP        MG_RPT_COMPANY_DIM            PK_MG_RPT_COMPANY_DIM_I        COMPANY_KEY
*/

-- Degradacion de los indices.
'nota: una medida para mirar si un indice esta degradado es comprobar su tamaño con el de la tabla'

COL TABLE_NAME FOR A30
COL col1 HEADING "Index Size(MB)"
COL col2 HEADING "Table Size(MB)"
COL col3 HEADING "Relacion %"
SELECT
	ind.INDEX_NAME,tablas.table_name,ROUND(SUM(seg_ind.bytes)/1024/1024,2) col1,ROUND(size_table/1024/1024,2) col2, ROUND(SUM(seg_ind.bytes)/size_table*100,2) col3
FROM
	DBA_INDEXES ind,
	DBA_SEGMENTS seg_ind,
	(SELECT	tab.OWNER, tab.TABLE_NAME , SUM(seg.BYTES) size_table
	FROM
		DBA_TABLES tab,
		DBA_SEGMENTS seg
	WHERE
		tab.TABLE_NAME = seg.SEGMENT_NAME
		AND tab.OWNER ='&esquema'
	GROUP BY tab.OWNER, tab.TABLE_NAME) tablas
WHERE tablas.OWNER = ind.OWNER
	AND tablas.TABLE_NAME = ind.TABLE_NAME
	AND ind.INDEX_NAME = seg_ind.SEGMENT_NAME
	AND ind.OWNER = seg_ind.OWNER
GROUP BY ind.INDEX_NAME,tablas.table_name,tablas.size_table
ORDER BY 5 DESC;
/*
Index                          Table
Name                           Name                           Index Size(MB) Table Size(MB) Relacion %
------------------------------ ------------------------------ -------------- -------------- ----------
TAUF_ACCOUNT_KEY_IDX           TELE_ACCOUNT_USAGE_FACT                  4455           5864      75,97
TAF_ACCOUNT_KEY_IDX            TELE_ACCOUNT_FACT                        3311           4398      75,28
TAUF_STATEMENT_KEY_IDX         TELE_ACCOUNT_USAGE_FACT                  4360           5864      74,35
STG_SRV_PRE_INFA_PK            STG_SERVICE_PRE_INFA                       48             65      73,85
TAPF_ACCOUNT_KEY_IDX           TELE_ACCOUNT_PLAN_FACT                   4600           6720      68,45
TAPF_STATEMENT_KEY_IDX         TELE_ACCOUNT_PLAN_FACT                   4539           6720      67,54
TACUF_ACCOUNT_KEY_IDX          TELE_ACCOUNT_CALL_USAGE_FACT             6795          10174      66,79
PK_MG_RPT_COUNTRY_DIM         MG_RPT_COUNTRY_DIM                           4              6      66,67
FN_ERCOUNTRYDIM_COUNTRYCD      MG_RPT_COUNTRY_DIM                          4              6      66,67
UK_MG_RPT_COUNTRY_DIM         MG_RPT_COUNTRY_DIM                           4              6      66,67
TSF_SERVICE_KEY_IDX            TELE_SERVICE_FACT                       31661          48292      65,56
*/

-- Comprobacion del uso que se esta haciendo del indice.

col c1 heading 'Object|Name' format a30
col c2 heading 'Operation' format a15
col c3 heading 'Option' format a15
col c4 heading 'Index|Usage|Count' format 999,999
break on c1 skip 2
break on c2 skip 2

select
   p.object_name c1,
   p.operation   c2,
   p.options     c3,
   count(1)      c4
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat s
where
   p.object_owner <> 'SYS'
and
   p.operation like '%INDEX%'
and
   p.sql_id = s.sql_id and
   p.object_name like '%&indice%'
group by
   p.object_name,
   p.operation,
   p.options
order by
   4;
   
/*
                                                                  Index
Object                                                            Usage
Name                           Operation       Option             Count
------------------------------ --------------- --------------- --------
TAUF_ACCOUNT_KEY_IDX           INDEX           RANGE SCAN           649
*/

-- Indica que queries utilizan ese indice y de que manera.

col sql_text format a80
col c1 heading 'Object|Name' format a30
col c2 heading 'Operation' format a15
col c3 heading 'Option' format a15
col c4 heading 'Index|Usage|Count' format 999,999
break on c1 skip 2
break on c2 skip 2

select
   a.sql_text ,
   sum (a.executions),
   s.module c1,
   p.operation   c2,
   p.options     c3,
   count(1)      c4
from
   dba_hist_sql_plan p,
   dba_hist_sqlstat s,
   v$sql a
where
   p.object_owner <> 'SYS'
and
   p.operation like '%INDEX%'
and
   p.sql_id = s.sql_id and
   a.sql_id=s.sql_id and
   p.object_name like '%&indice%'
group by
   a.sql_text ,
   s.module,
   p.operation,
   p.options
order by
   6;

/*
                                                                                                                                                                    Index
                                                                                                   Object                                                            Usage
SQL_TEXT                                                                         SUM(A.EXECUTIONS) Name                           Operation       Option             Count
-------------------------------------------------------------------------------- ----------------- ------------------------------ --------------- --------------- --------
select f.usage_type_key,    case when f.usage_type_key = :"SYS_B_00" then :"SYS_               272                                INDEX           RANGE SCAN         1,144
B_01"   when f.usage_type_key = :"SYS_B_02" then :"SYS_B_03"   else :"SYS_B_04"
end as Name_Usage_Type,    utd.usage_type_name as Usage_Type_Name,    nvl(utd.re
*/

-- Informacion de las particiones de un indice

SET LINES 200 PAGES 5000
 COL OWNER FORMAT A10
 COL SEGMENT_NAME FORMAT A30
 COL TABLESPACE_NAME FORMAT A30
SELECT
	A.OWNER, A.TABLE_NAME,A.INDEX_NAME, 
	B.PARTITION_NAME,
	B.TABLESPACE_NAME,
	B.BYTES/1024/1024 "SIZE MB"
FROM
	DBA_INDEXES A,
	DBA_SEGMENTS B
WHERE
	A.INDEX_NAME=B.SEGMENT_NAME AND
	UPPER (A.TABLE_NAME) LIKE UPPER('%&tabla%')
ORDER BY
	A.TABLE_NAME, B.SEGMENT_NAME, B.PARTITION_NAME;
	
-- Tamaño de un indice

set verify off
BREAK ON TABLE_NAME SKIP 1 ON TABLE_OWNER SKIP 1
COMPUTE SUM LABEL 'Total:' OF SIZE_MB  ON TABLE_OWNER
COL TABLE_OWNER FOR A20
COL TABLE_NAME FOR A30
COL INDEX_NAME FOR A30
COL TABLESPACE_NAME FOR A30
SELECT 
	ind.TABLE_OWNER, ind.TABLE_NAME, ind.INDEX_NAME,ind.TABLESPACE_NAME, ROUND(SUM(seg.BYTES)/1024/1024,2) SIZE_MB, SUM(seg.BLOCKS) NUM_BLOCKS
FROM 
	DBA_INDEXES ind, DBA_SEGMENTS seg
WHERE
	ind.INDEX_NAME =	seg.SEGMENT_NAME	AND
	ind.TABLE_OWNER LIKE '%&table_owner%'	AND
	ind.TABLE_NAME  = '&table_name'	AND
	ind.INDEX_NAME	LIKE '%&index_name%'		
GROUP BY
	ind.TABLE_OWNER, ind.TABLE_NAME, ind.INDEX_NAME, ind.TABLESPACE_NAME
ORDER BY 5;

/*
                     Table                          Index
TABLE_OWNER          Name                           Name                           TABLESPACE_NAME                   SIZE_MB NUM_BLOCKS
-------------------- ------------------------------ ------------------------------ ------------------------------ ---------- ----------
MG_OLAP             MG_RPT_ACCOUNT_FACT             BM_ERAF_PREVPERIODKEY                                                 28       1792
                                                    BM_ERAF_CHARGEFLAG                                                    28       1792
                                                    BM_ERAF_PERIODKEY                                                     28       1792
                                                    BM_ERAF_ETLKEY                                                        28       1792
                                                    BM_ERAF_PMNTYPKEY                                                     28       1792
                                                    BM_ERAF_NEXTPERIODKEY                                                 28       1792
                                                    NK_ERAF_ACCTKEY                                                       82       5248
                                                    NK_ERAF_STMNTKEY                                                      84       5376
********************                                                                                              ----------
Total:                                                                                                                   334
*/

-- Estado de los indices

SELECT
	STATUS, COUNT(*) Total
FROM DBA_INDEXES
WHERE
	OWNER LIKE '%&propietario%'
GROUP BY STATUS
UNION
SELECT
	STATUS, COUNT(*) Total
FROM DBA_IND_PARTITIONS
WHERE
	INDEX_OWNER LIKE '%&propietario%'
GROUP BY STATUS;

/*
STATUS        TOTAL
-------- ----------
N/A             267
N/A           76198
USABLE          519
VALID          3502
*/

Dejar un comentario »

Aún no hay comentarios.

RSS feed for comments on this post. TrackBack URI

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

Blog de WordPress.com.

A %d blogueros les gusta esto: