forked from carlos-sierra/cscripts
-
Notifications
You must be signed in to change notification settings - Fork 0
/
cs_index_usage.sql
executable file
·199 lines (199 loc) · 6.68 KB
/
cs_index_usage.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
----------------------------------------------------------------------------------------
--
-- File name: cs_index_usage.sql
--
-- Purpose: Index Usage (is an index still in use?)
--
-- Author: Carlos Sierra
--
-- Version: 2023/01/09
--
-- Usage: Execute connected to PDB
--
-- Enter owner, table and index
--
-- Example: $ sqlplus / as sysdba
-- SQL> @cs_index_usage.sql
--
-- Notes: Developed and tested on 12.1.0.2.
--
---------------------------------------------------------------------------------------
--
@@cs_internal/cs_primary.sql
@@cs_internal/cs_cdb_warn.sql
@@cs_internal/cs_set.sql
@@cs_internal/cs_def.sql
@@cs_internal/cs_file_prefix.sql
--
DEF cs_script_name = 'cs_index_usage';
--
COL username FOR A30;
SELECT username
FROM dba_users
WHERE oracle_maintained = 'N'
AND common = 'NO'
ORDER BY
username
/
PRO
PRO 1. Table Owner:
DEF table_owner = '&1.';
UNDEF 1;
COL p_owner NEW_V p_owner FOR A30 NOPRI;
SELECT username AS p_owner
FROM dba_users
WHERE oracle_maintained = 'N'
AND common = 'NO'
AND username = UPPER(TRIM('&&table_owner.'))
AND ROWNUM = 1
/
--
COL table_name FOR A30;
SELECT table_name, blocks, num_rows
FROM dba_tables
WHERE owner = '&&p_owner.'
ORDER BY
table_name
/
PRO
PRO 2. Table Name:
DEF table_name = '&2.';
UNDEF 2;
COL p_table_name NEW_V p_table_name NOPRI;
SELECT table_name AS p_table_name
FROM dba_tables
WHERE owner = '&&p_owner.'
AND table_name = UPPER(TRIM('&&table_name.'))
AND ROWNUM = 1
/
--
COL index_name FOR A30;
SELECT index_name, leaf_blocks
FROM dba_indexes
WHERE owner = '&&p_owner.'
AND table_name = '&&p_table_name.'
ORDER BY
index_name
/
PRO
PRO 3. Index Name:
DEF index_name = '&3.'
UNDEF 3;
DEF p_index_name = '';
COL p_index_name NEW_V p_index_name NOPRI;
SELECT index_name AS p_index_name
FROM dba_indexes
WHERE owner = '&&p_owner.'
AND table_name = '&&p_table_name.'
AND index_name = UPPER(TRIM('&&index_name.'))
AND ROWNUM = 1
/
--
SELECT '&&cs_file_prefix._&&cs_script_name._&&p_owner..&&p_table_name..&&p_index_name.' cs_file_name FROM DUAL;
--
@@cs_internal/cs_spool_head.sql
PRO SQL> @&&cs_script_name..sql "&&p_owner." "&&p_table_name." "&&p_index_name."
@@cs_internal/cs_spool_id.sql
--
PRO TABLE_OWNER : "&&p_owner."
PRO TABLE_NAME : "&&p_table_name."
PRO INDEX_NAME : "&&p_index_name."
--
COL index_name FOR A30;
COL plan_hash_value FOR 999999999999999;
COL executions FOR 999,999,999,990;
COL elapsed_seconds FOR 999,999,990;
COL cpu_seconds FOR 999,999,990;
COL sql_text FOR A100 TRUNC;
BREAK ON REPORT;
COMPUTE SUM LABEL "TOTAL" OF executions elapsed_seconds cpu_seconds ON REPORT;
PRO
PRO v$object_dependency -> v$sql
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT d.to_name AS index_name, s.sql_id, s.plan_hash_value, s.sql_text,
s.executions, s.elapsed_seconds, s.cpu_seconds, s.last_active_time
FROM ( SELECT d.to_name, d.from_hash, d.from_address
FROM v$object_dependency d, v$db_object_cache c
WHERE d.to_owner = '&&p_owner.'
AND d.to_name = NVL('&&p_index_name.', d.to_name)
--AND d.to_type = 70 -- MULTI-VERSIONED OBJECT
AND d.to_name IN (SELECT index_name FROM dba_indexes WHERE owner = '&&p_owner.' AND table_name = '&&p_table_name.')
AND c.hash_value = d.to_hash
AND c.addr = d.to_address
AND c.type = 'MULTI-VERSIONED OBJECT'
GROUP BY d.to_name, d.from_hash, d.from_address
) d
CROSS APPLY (
SELECT s.sql_id, s.sql_text, s.plan_hash_value,
SUM(s.executions) AS executions,
ROUND(SUM(s.elapsed_time)/POWER(10, 6)) AS elapsed_seconds,
ROUND(SUM(s.cpu_time)/POWER(10, 6)) AS cpu_seconds,
MAX(s.last_active_time) AS last_active_time
FROM v$sql s
WHERE s.hash_value = d.from_hash
AND s.address = d.from_address
-- AND s.parsing_user_id <> 0
-- AND s.parsing_schema_id <> 0
GROUP BY
s.sql_id, s.sql_text, s.plan_hash_value
) s
ORDER BY
d.to_name, s.sql_id, s.plan_hash_value
/
PRO
PRO v$sql_plan -> v$sql
PRO ~~~~~~~~~~~~~~~~~~~
SELECT p.object_name AS index_name, p.sql_id, p.plan_hash_value, s.sql_text,
s.executions, s.elapsed_seconds, s.cpu_seconds, s.last_active_time
FROM ( SELECT p.object_name, p.sql_id, p.plan_hash_value
FROM v$sql_plan p
WHERE p.object_owner = '&&p_owner.'
AND p.object_name = NVL('&&p_index_name.', p.object_name)
AND p.object_type LIKE '%INDEX%'
AND p.object_name IN (SELECT index_name FROM dba_indexes WHERE owner = '&&p_owner.' AND table_name = '&&p_table_name.')
GROUP BY p.object_name, p.sql_id, p.plan_hash_value
) p
CROSS APPLY (
SELECT MAX(s.sql_text) AS sql_text,
SUM(s.executions) AS executions,
ROUND(SUM(s.elapsed_time)/POWER(10, 6)) AS elapsed_seconds,
ROUND(SUM(s.cpu_time)/POWER(10, 6)) AS cpu_seconds,
MAX(s.last_active_time) AS last_active_time
FROM v$sql s
WHERE s.sql_id = p.sql_id
AND s.plan_hash_value = p.plan_hash_value
-- AND s.parsing_user_id <> 0
-- AND s.parsing_schema_id <> 0
) s
ORDER BY
p.object_name, s.sql_id, s.plan_hash_value
/
PRO
PRO dba_hist_sql_plan -> dba_hist_sqltext
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT p.object_name AS index_name, p.sql_id, p.plan_hash_value, s.sql_text, p.timestamp
FROM ( SELECT p.object_name, p.sql_id, p.plan_hash_value, MAX(timestamp) AS timestamp
FROM dba_hist_sql_plan p
WHERE p.object_owner = '&&p_owner.'
AND p.object_name = NVL('&&p_index_name.', p.object_name)
AND p.object_type LIKE '%INDEX%'
AND p.dbid = TO_NUMBER('&&cs_dbid.')
AND p.object_name IN (SELECT index_name FROM dba_indexes WHERE owner = '&&p_owner.' AND table_name = '&&p_table_name.')
GROUP BY p.object_name, p.sql_id, p.plan_hash_value
) p
CROSS APPLY (
SELECT MAX(DBMS_LOB.SUBSTR(s.sql_text, 1000)) AS sql_text
FROM dba_hist_sqltext s
WHERE s.sql_id = p.sql_id
) s
ORDER BY
p.object_name, p.sql_id, p.plan_hash_value
/
--
PRO
PRO SQL> @&&cs_script_name..sql "&&p_owner." "&&p_table_name." "&&p_index_name."
--
@@cs_internal/cs_spool_tail.sql
@@cs_internal/cs_undef.sql
@@cs_internal/cs_reset.sql
--