-
Notifications
You must be signed in to change notification settings - Fork 18
/
Copy pathexasnap.sql
385 lines (377 loc) · 18.5 KB
/
exasnap.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
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
--------------------------------------------------------------------------------
-- File name: exasnap.sql (Exadata Snapper) BETA
--
-- Purpose: Display various Exadata IO efficiency metrics of a session
-- from V$SESSTAT.
--
-- Author: Tanel Poder ( http://blog.tanelpoder.com | tanel@tanelpoder.com )
--
-- Copyright: (c) 2012 All Rights Reserved
--
-- Usage:
-- 1) TAKE A SNAPSHOT
--
-- SELECT exasnap.begin_snap(<sid>) FROM dual;
-- or
-- SELECT exasnap.begin_snap('<sid>[@<instance#>]') FROM dual;
--
-- 2) Run the measured query in the session you snapshotted
-- (or just wait for it to run a while)
--
-- 3) TAKE A 2ND SNAPSHOT
--
-- SELECT exasnap.begin_snap(<sid>) FROM dual;
-- or
-- SELECT exasnap.begin_snap('<sid>[@<instance#>]') FROM dual;
--
-- 4) REPORT SESSION METRICS
--
-- @exasnap.sql basic <begin_snap> <end_snap>
-- or
-- @exasnap.sql % <begin_snap> <end_snap>
--
-- The latter script gives you more output.
--
-- Other: This is still a pretty raw script in development and will
-- probably change a lot once it reaches v1.0.
--
-- The PX slaves aggregate their metrics back to the QC session
-- once the query completes, so querying the QC session only is
-- ok if you wait for the query to finish (or cancel it) before
-- taking a snapshot.
-- To measure a query still running,
--
--------------------------------------------------------------------------------
SET LINES 999 PAGES 5000 TRIMOUT ON TRIMSPOOL ON TAB OFF
COL ioeff_percentage FOR A52
BREAK ON inst_id SKIP 1 ON SID ON CATEGORY SKIP 1 DUP
-- keep in capital (MB or GB)
DEF unit=MB
-- adjust for MB or GB
DEF divisor=1024*1024
DEF blocksize=8192
DEF asm_mirrors=2
DEFINE nothing =""
PROMPT
PROMPT ---------------------------------------------------------------------------------------------------------------------------------------------¬hing
PROMPT -- Exadata Snapper v0.5 BETA by Tanel Poder @ Enkitec - The Exadata Experts ( http://www.enkitec.com )
PROMPT ---------------------------------------------------------------------------------------------------------------------------------------------¬hing
WITH stats AS (
SELECT
stat_name name
, SUM(delta) value
, AVG(snap_seconds) snap_seconds -- is the same for all records in this snap_id
FROM (
SELECT
esn1.snap_id
, esn1.snap_time begin_snap_time
, esn2.snap_time end_snap_time
, esn2.snap_time - esn1.snap_time snap_interval
, TO_NUMBER(EXTRACT(second from esn2.snap_time - esn1.snap_time)) +
TO_NUMBER(EXTRACT(minute from esn2.snap_time - esn1.snap_time)) * 60 +
TO_NUMBER(EXTRACT(hour from esn2.snap_time - esn1.snap_time)) * 60 * 60 +
TO_NUMBER(EXTRACT(day from esn2.snap_time - esn1.snap_time)) * 60 * 60 * 24 +
TO_NUMBER(TO_CHAR(esn2.snap_time,'xFF')) -
TO_NUMBER(TO_CHAR(esn1.snap_time,'xFF')) snap_seconds -- looks like the last part is buggy but it's too late to figure this out!
, esn1.snap_name begin_snap_name
, esn2.snap_name end_snap_name
, ess1.stat_name
, ess1.value begin_value
, ess2.value end_value
, ess2.value - ess1.value delta
FROM
ex_snapshot esn1
, ex_session es1
, ex_sesstat ess1
, ex_snapshot esn2
, ex_session es2
, ex_sesstat ess2
WHERE
-- snap_id
esn1.snap_id = es1.snap_id
AND ess1.snap_id = esn1.snap_id
AND es1.snap_id = ess1.snap_id
AND es1.inst_id = ess1.inst_id
AND es1.sid = ess1.sid
AND es1.serial# = ess1.serial#
--
AND esn2.snap_id = es2.snap_id
AND es2.snap_id = ess2.snap_id
AND ess2.snap_id = esn2.snap_id
AND es2.inst_id = ess2.inst_id
AND es2.sid = ess2.sid
AND es2.serial# = ess2.serial#
AND ess1.stat_name = ess2.stat_name
AND ess1.inst_id = ess2.inst_id
AND ess1.sid = ess2.sid
AND ess1.serial# = ess2.serial#
--
AND esn1.snap_id = &2
AND esn2.snap_id = &3
--
-- AND ess2.value - ess1.value != 0 -- for testing
)
GROUP BY
stat_name
),
sq AS (
SELECT
*
FROM (
SELECT
0 inst_id
, 0 sid
, CASE WHEN TRIM(name) IN (
'cell physical IO bytes sent directly to DB node to balance CPU'
, 'cell physical IO bytes pushed back due to excessive CPU on cell'
, 'cell physical IO bytes sent directly to DB node to balanceCPU u'
) THEN
'cell physical IO bytes sent directly to DB node to balance CPU'
ELSE name
END name
, value
FROM
--gv$sesstat NATURAL JOIN v$statname
stats
WHERE
1=1
-- AND (name LIKE 'cell%bytes%' OR name LIKE 'physical%bytes%')
AND TRIM(name) IN (
'physical read total bytes'
, 'physical write total bytes'
, 'physical read total bytes optimized'
, 'cell physical IO bytes eligible for predicate offload'
, 'cell physical IO interconnect bytes'
, 'cell physical IO interconnect bytes returned by smart scan'
, 'cell physical IO bytes saved by storage index'
, 'cell IO uncompressed bytes'
, 'cell blocks processed by cache layer'
, 'cell blocks processed by txn layer'
, 'cell blocks processed by data layer'
, 'cell blocks processed by index layer'
, 'db block gets from cache'
, 'consistent gets from cache'
, 'db block gets direct'
, 'consistent gets direct'
-- following three stats are the same thing (named differently in different versions)
, 'cell physical IO bytes sent directly to DB node to balance CPU'
, 'cell physical IO bytes pushed back due to excessive CPU on cell'
, 'cell physical IO bytes sent directly to DB node to balanceCPU u'
, 'bytes sent via SQL*Net to client'
, 'bytes received via SQL*Net from client'
, 'table fetch continued row'
, 'chained rows skipped by cell'
, 'chained rows processed by cell'
, 'chained rows rejected by cell'
)
)
PIVOT (
SUM(value)
FOR name IN (
'physical read total bytes' AS phyrd_bytes
, 'physical write total bytes' AS phywr_bytes
, 'physical read total bytes optimized' AS phyrd_optim_bytes
, 'cell physical IO bytes eligible for predicate offload' AS pred_offloadable_bytes
, 'cell physical IO interconnect bytes' AS interconnect_bytes
, 'cell physical IO interconnect bytes returned by smart scan' AS smart_scan_ret_bytes
, 'cell physical IO bytes saved by storage index' AS storidx_saved_bytes
, 'cell IO uncompressed bytes' AS uncompressed_bytes
, 'cell blocks processed by cache layer' AS cell_proc_cache_blk
, 'cell blocks processed by txn layer' AS cell_proc_txn_blk
, 'cell blocks processed by data layer' AS cell_proc_data_blk
, 'cell blocks processed by index layer' AS cell_proc_index_blk
, 'db block gets from cache' AS curr_gets_cache_blk
, 'consistent gets from cache' AS cons_gets_cache_blk
, 'db block gets direct' AS curr_gets_direct_blk
, 'consistent gets direct' AS cons_gets_direct_blk
, 'cell physical IO bytes sent directly to DB node to balance CPU' AS cell_bal_cpu_bytes
, 'bytes sent via SQL*Net to client' AS net_to_client_bytes
, 'bytes received via SQL*Net from client' AS net_from_client_bytes
, 'table fetch continued row' AS chain_fetch_cont_row
, 'chained rows skipped by cell' AS chain_rows_skipped
, 'chained rows processed by cell' AS chain_rows_processed
, 'chained rows rejected by cell' AS chain_rows_rejected
)
)
),
precalc AS (
SELECT
inst_id
, sid
, ROUND((phyrd_bytes)/(&divisor)) db_physrd_&unit
, ROUND((phywr_bytes)/(&divisor)) db_physwr_&unit
, ROUND((phyrd_bytes+phywr_bytes)/(&divisor)) db_physio_&unit
, ROUND(pred_offloadable_bytes/(&divisor)) pred_offloadable_&unit
, ROUND(phyrd_optim_bytes/(&divisor)) phyrd_optim_&unit
, ROUND((phyrd_optim_bytes-storidx_saved_bytes)/(&divisor)) phyrd_flash_rd_&unit
, ROUND(storidx_saved_bytes/(&divisor)) phyrd_storidx_saved_&unit
, ROUND((phyrd_bytes-phyrd_optim_bytes)/(&divisor)) spin_disk_rd_&unit
, ROUND((phyrd_bytes-phyrd_optim_bytes+(phywr_bytes*2))/(&divisor)) spin_disk_io_&unit
, ROUND(uncompressed_bytes/(&divisor)) scanned_uncomp_&unit
, ROUND(interconnect_bytes/(&divisor)) total_ic_&unit
, ROUND(smart_scan_ret_bytes/(&divisor)) smart_scan_ret_&unit
, ROUND((interconnect_bytes-smart_scan_ret_bytes)/(&divisor)) non_smart_scan_&unit
, ROUND(cell_proc_cache_blk * &blocksize / (&divisor)) cell_proc_cache_&unit
, ROUND(cell_proc_txn_blk * &blocksize / (&divisor)) cell_proc_txn_&unit
, ROUND(cell_proc_data_blk * &blocksize / (&divisor)) cell_proc_data_&unit
, ROUND(cell_proc_index_blk * &blocksize / (&divisor)) cell_proc_index_&unit
, ROUND(curr_gets_cache_blk * &blocksize / (&divisor)) curr_gets_cache_&unit
, ROUND(cons_gets_cache_blk * &blocksize / (&divisor)) cons_gets_cache_&unit
, ROUND(curr_gets_direct_blk * &blocksize / (&divisor)) curr_gets_direct_&unit
, ROUND(cons_gets_direct_blk * &blocksize / (&divisor)) cons_gets_direct_&unit
, ROUND(cell_bal_cpu_bytes / (&divisor)) cell_bal_cpu_&unit
, ROUND(net_to_client_bytes / (&divisor)) net_to_client_&unit
, ROUND(net_from_client_bytes / (&divisor)) net_from_client_&unit
, chain_fetch_cont_row
, chain_rows_skipped
, chain_rows_processed
, chain_rows_rejected
FROM sq
),
precalc2 AS (
SELECT
inst_id
, sid
, db_physio_&unit
, db_physrd_&unit
, db_physwr_&unit
, pred_offloadable_&unit
, phyrd_optim_&unit
, phyrd_flash_rd_&unit + spin_disk_rd_&unit phyrd_disk_and_flash_&unit
, phyrd_flash_rd_&unit
, phyrd_storidx_saved_&unit
, spin_disk_io_&unit
, spin_disk_rd_&unit
, ((spin_disk_io_&unit - spin_disk_rd_&unit)) AS spin_disk_wr_&unit
, scanned_uncomp_&unit
, ROUND((scanned_uncomp_&unit/NULLIF(spin_disk_rd_&unit, 0))*db_physrd_&unit) est_full_uncomp_&unit
, total_ic_&unit
, smart_scan_ret_&unit
, non_smart_scan_&unit
, cell_proc_cache_&unit
, cell_proc_txn_&unit
, cell_proc_data_&unit
, cell_proc_index_&unit
, cell_bal_cpu_&unit
, curr_gets_cache_&unit
, cons_gets_cache_&unit
, curr_gets_direct_&unit
, cons_gets_direct_&unit
, net_to_client_&unit
, net_from_client_&unit
, chain_fetch_cont_row
, chain_rows_skipped
, chain_rows_processed
, chain_rows_rejected
FROM
precalc
),
--SELECT
-- inst_id
-- , SUM(db_physio_&unit)
-- , SUM(db_physrd_&unit)
-- , SUM(db_physwr_&unit)
-- , SUM(pred_offloadable_&unit)
-- , SUM(phyrd_optim_&unit)
-- , SUM(spin_disk_io_&unit)
-- , SUM(spin_disk_rd_&unit)
-- , SUM(spin_disk_io_&unit - spin_disk_rd_&unit) AS spin_disk_wr_&unit
-- , SUM(scanned_uncomp_&unit)
-- , ROUND(SUM((scanned_uncomp_&unit/spin_disk_rd_&unit)*db_physrd_&unit)) AS est_full_uncomp_&unit
-- , SUM(total_ic_&unit)
-- , SUM(smart_scan_ret_&unit)
-- , SUM(non_smart_scan_&unit)
--FROM
-- precalc2
--GROUP BY ROLLUP
-- (inst_id)
--/
unpivoted AS (
SELECT * FROM precalc2
UNPIVOT (
&unit
FOR metric
IN (
phyrd_optim_&unit
, phyrd_disk_and_flash_&unit
, phyrd_flash_rd_&unit
, phyrd_storidx_saved_&unit
, spin_disk_rd_&unit
, spin_disk_wr_&unit
, spin_disk_io_&unit
, db_physrd_&unit
, db_physwr_&unit
, db_physio_&unit
, scanned_uncomp_&unit
, est_full_uncomp_&unit
, non_smart_scan_&unit
, smart_scan_ret_&unit
, total_ic_&unit
, pred_offloadable_&unit
, cell_proc_cache_&unit
, cell_proc_txn_&unit
, cell_proc_data_&unit
, cell_proc_index_&unit
, cell_bal_cpu_&unit
, curr_gets_cache_&unit
, cons_gets_cache_&unit
, curr_gets_direct_&unit
, cons_gets_direct_&unit
, net_to_client_&unit
, net_from_client_&unit
, chain_fetch_cont_row
, chain_rows_skipped
, chain_rows_processed
, chain_rows_rejected
)
)
),
metric AS (
SELECT 'BASIC' type, 'DB_LAYER_IO' category, 'DB_PHYSIO_&unit' name FROM dual UNION ALL
SELECT 'BASIC', 'DB_LAYER_IO', 'DB_PHYSRD_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'DB_LAYER_IO', 'DB_PHYSWR_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'AVOID_DISK_IO', 'PHYRD_OPTIM_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'AVOID_DISK_IO', 'PHYRD_DISK_AND_FLASH_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'AVOID_DISK_IO', 'PHYRD_FLASH_RD_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'AVOID_DISK_IO', 'PHYRD_STORIDX_SAVED_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REAL_DISK_IO', 'SPIN_DISK_IO_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REAL_DISK_IO', 'SPIN_DISK_RD_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REAL_DISK_IO', 'SPIN_DISK_WR_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'COMPRESS', 'SCANNED_UNCOMP_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'COMPRESS', 'EST_FULL_UNCOMP_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'PRED_OFFLOADABLE_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'TOTAL_IC_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'SMART_SCAN_RET_&unit' FROM dual UNION ALL
SELECT 'BASIC', 'REDUCE_INTERCONNECT', 'NON_SMART_SCAN_&unit' FROM dual UNION ALL
SELECT 'ADVANCED', 'CELL_PROC_DEPTH', 'CELL_PROC_CACHE_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'CELL_PROC_DEPTH', 'CELL_PROC_TXN_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CELL_PROC_DEPTH', 'CELL_PROC_DATA_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CELL_PROC_DEPTH', 'CELL_PROC_INDEX_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'CELL_PROC_DEPTH', 'CELL_BAL_CPU_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CURR_GETS_CACHE_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CONS_GETS_CACHE_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CURR_GETS_DIRECT_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'IN_DB_PROCESSING', 'CONS_GETS_DIRECT_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CLIENT_COMMUNICATION', 'NET_TO_CLIENT_&unit' FROM DUAL UNION ALL
SELECT 'BASIC', 'CLIENT_COMMUNICATION', 'NET_FROM_CLIENT_&unit' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_FETCH_CONT_ROW' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_ROWS_SKIPPED' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_ROWS_PROCESSED' FROM DUAL UNION ALL
SELECT 'ADVANCED', 'FALLBACK_TO_BLOCK_IO', 'CHAIN_ROWS_REJECTED' FROM DUAL
)
SELECT
-- inst_id
-- , sid
category
-- , type
, metric
, '|'||RPAD(NVL(RPAD('#', ROUND(&unit / NULLIF( (SELECT MAX(&unit) FROM unpivoted u, metric m WHERE u.metric = m.name AND m.type LIKE UPPER('&1')), 0) * 50 ), '#'), ' '), 50, ' ')||'|' ioeff_percentage
, &unit
, TO_CHAR(ROUND(&unit / (SELECT snap_seconds FROM stats WHERE rownum = 1),1), '9999999.9') AS " &unit/sec"
FROM
unpivoted u
, metric m
WHERE
u.metric = m.name
AND m.type LIKE UPPER('&1')
/