[20210125]完善hide.sql指令碼.txt
[20210125]完善hide.sql指令碼.txt
--//想查詢包含_ash_的隱含引數.輸出太多,改寫一下hide.sql指令碼:
1.環境:
SYS@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.查詢包含_ash_的隱含引數:
SYS@book> @ hide _ash_
old 19: and lower(a.ksppinm) like lower('%&1%')
new 19: and lower(a.ksppinm) like lower('%_ash_%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ---------------------- ----- ---------
_ash_compression_enable To enable or disable string compression in ASH TRUE TRUE TRUE FALSE IMMEDIATE
_ash_disk_filter_ratio Ratio of the number of in-memory samples to the number of samples TRUE 10 10 FALSE IMMEDIATE
actually written to disk
_ash_disk_write_enable To enable or disable Active Session History flushing TRUE TRUE TRUE FALSE IMMEDIATE
_ash_dummy_test_param Oracle internal dummy ASH parameter used ONLY for testing! TRUE 0 0 FALSE IMMEDIATE
_ash_eflush_trigger The percentage above which if the in-memory ASH is full the emerge TRUE 66 66 FALSE IMMEDIATE
ncy flusher will be triggered
...
db_flash_cache_file flash cache file for default block size TRUE FALSE FALSE
db_flash_cache_size flash cache size for db_flash_cache_file TRUE 0 0 FALSE IMMEDIATE
db_flashback_retention_target Maximum Flashback Database log retention time in minutes. TRUE 1440 1440 FALSE IMMEDIATE
hash_area_size size of in-memory hash work area TRUE 131072 131072 TRUE FALSE
110 rows selected.
--//輸出太多,我想查詢是_ash_的隱含引數,實際上oracle將_解析為任何字元.改寫一下我的查詢指令碼.
SYS@book> @ hide \_ash\_
no rows selected
3.改寫如下:
$ cat hide.sql
col name format a40
col description format a66
col session_value format a22
col default_value format a22
col system_value format a22
select
a.ksppinm name,
a.ksppdesc DESCRIPTION,
b.ksppstdf DEFAULT_VALUE,
b.ksppstvl SESSION_VALUE,
c.ksppstvl SYSTEM_VALUE,
DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE') ISSES_MODIFIABLE,
DECODE
(
BITAND (a.ksppiflg / 65536, 3)
,1, 'IMMEDIATE'
,2, 'DEFERRED'
,3, 'IMMEDIATE'
,'FALSE'
) ISSYS_MODIFIABLE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx
and a.indx = c.indx
and lower(a.ksppinm) like lower('%&1%')
escape '\'
order by 1;
--//加入escape '\'就ok了.
SYS@book> column DESCRIPTION format a60
SYS@book> @ hide \_ash\_
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------------------------- ------------------------------------------------------------------ ---------------------- ---------------------- ---------------------- ----- ---------
_ash_compression_enable To enable or disable string compression in ASH TRUE TRUE TRUE FALSE IMMEDIATE
_ash_disk_filter_ratio Ratio of the number of in-memory samples to the number of samples TRUE 10 10 FALSE IMMEDIATE
actually written to disk
_ash_disk_write_enable To enable or disable Active Session History flushing TRUE TRUE TRUE FALSE IMMEDIATE
_ash_dummy_test_param Oracle internal dummy ASH parameter used ONLY for testing! TRUE 0 0 FALSE IMMEDIATE
_ash_eflush_trigger The percentage above which if the in-memory ASH is full the emerge TRUE 66 66 FALSE IMMEDIATE
ncy flusher will be triggered
_ash_enable To enable or disable Active Session sampling and flushing TRUE TRUE TRUE FALSE IMMEDIATE
_ash_min_mmnl_dump Minimum Time interval passed to consider MMNL Dump TRUE 90 90 FALSE IMMEDIATE
_ash_sample_all To enable or disable sampling every connected session including on TRUE FALSE FALSE FALSE IMMEDIATE
es waiting for idle waits
_ash_sampling_interval Time interval between two successive Active Session samples in mil TRUE 1000 1000 FALSE FALSE
lisecs
_ash_size To set the size of the in-memory Active Session History buffers TRUE 1048618 1048618 FALSE IMMEDIATE
10 rows selected.
--//你可以發現一些隱含引數意思,比如_ash_sampling_interval=1000,也就是1秒一個取樣.
--// _ash_sample_all=false,設定true時可以收集idle事件.
--//比如網路問題要收集SQL*Net message from client事件,連結:http://blog.itpub.net/267265/viewspace-2648449/
--//_ash_disk_filter_ratio =10,表示 10*_ash_sampling_interval,也就是10秒取樣的寫入 dba_hist_active_sess_history檢視.
select * from V$ACTIVE_SESSION_HISTORY where IS_AWR_SAMPLE='Y'
--//可以發現間隔10秒設定IS_AWR_SAMPLE='Y'.
--//我的測試環境_ash_size = 1048618, 1048618/1024/1024 ~= 1M,約等於1M.
SYS@book> select min(sample_time),sysdate from v$active_session_history;
MIN(SAMPLE_TIME) SYSDATE
----------------------- -------------------
2021-01-07 11:09:41.137 2021-01-25 09:12:06
--//竟然儲存很長時間,說明我的測試環境沒有什麼業務.而生產系統.
> select min(sample_time) from v$active_session_history;
MIN(SAMPLE_TIME)
-----------------------
2021-01-24 11:10:06.380
--//怪不記得我最近使用ashtop看到的情況更以前有點一樣,以前我在星期一早上,我執行ashtop可以看到上個星期6的部分資訊,現在星期
--//天都看不全.說明生產系統記錄的資訊量太大.許多資訊已經不再ash快取了.不過我發現oracle有點設定不合理.
> @ hide \_ash\_size
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
_ash_size To set the size of the in-memory Active Session History buffers TRUE 1048618 1048618 FALSE IMMEDIATE
--//感覺設定不合理,伺服器記憶體這麼大而_ash_size大小竟然與我的測試環境一樣大小.應該設定根據記憶體配置存在一些變化.
--//下面說明看生產系統遇到的情況:
> select min(sample_time) from v$active_session_history;
MIN(SAMPLE_TIME)
-----------------------
2021-01-24 11:33:49.356
> select trunc(sysdate-2), trunc(sysdate-1) from dual ;
TRUNC(SYSDATE-2) TRUNC(SYSDATE-1)
------------------- -------------------
2021-01-23 00:00:00 2021-01-24 00:00:00
> @ ashtop sql_id 1=1 trunc(sysdate-2) trunc(sysdate-1)
Total
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
11242 .1 61% | 2021-01-23 00:00:24 2021-01-23 23:59:59
1047 .0 6% | 4zbzjuu5h34dn 2021-01-23 00:02:29 2021-01-23 23:55:17
615 .0 3% | 2w5dgfjvasy4j 2021-01-23 00:02:26 2021-01-23 23:55:13
499 .0 3% | 3ddgu71paks5d 2021-01-23 00:02:35 2021-01-23 23:55:21
498 .0 3% | c5vp872ytwr03 2021-01-23 00:02:38 2021-01-23 23:55:25
493 .0 3% | 7y3xscmmqfymn 2021-01-23 00:02:33 2021-01-23 23:55:19
480 .0 3% | 9yfzqfdw2yhs4 2021-01-23 00:02:24 2021-01-23 23:55:11
463 .0 3% | d14tg929b4xj6 2021-01-23 00:25:05 2021-01-23 23:45:02
382 .0 2% | 6mnrdrgdys4uc 2021-01-23 00:00:31 2021-01-23 23:59:07
262 .0 1% | 8qdgcgn1sz7y8 2021-01-23 00:31:05 2021-01-23 23:40:59
187 .0 1% | 4ztz048yfq32s 2021-01-23 00:09:16 2021-01-23 23:39:19
163 .0 1% | g3gtp1awt0yu4 2021-01-23 00:52:08 2021-01-23 23:38:00
160 .0 1% | 4q31ffyqwkt1h 2021-01-23 01:39:43 2021-01-23 23:35:22
134 .0 1% | 8b4txypt6ttws 2021-01-23 00:07:41 2021-01-23 23:55:09
80 .0 0% | 772s25v1y0x8k 2021-01-23 00:00:57 2021-01-23 23:55:59
80 .0 0% | 9dj4166ys0z0w 2021-01-23 00:48:12 2021-01-23 23:57:26
66 .0 0% | gsmywgqtjazrc 2021-01-23 01:17:15 2021-01-23 22:49:23
65 .0 0% | 752akhc8hfqc6 2021-01-23 04:19:46 2021-01-23 23:05:21
63 .0 0% | ck3nrshb15tb4 2021-01-23 00:24:33 2021-01-23 23:57:49
53 .0 0% | 0uuczutvk6jqj 2021-01-23 00:26:10 2021-01-23 23:46:10
50 .0 0% | a3sc3s8k1fj9g 2021-01-23 00:12:54 2021-01-23 23:09:01
45 .0 0% | 185jrpktxy2t7 2021-01-23 00:22:19 2021-01-23 23:45:37
44 .0 0% | 6c23qpas152z3 2021-01-23 00:24:20 2021-01-23 23:20:10
44 .0 0% | 8g7tjhp1j0ky3 2021-01-23 00:02:37 2021-01-23 23:55:23
44 .0 0% | cr988d50t86za 2021-01-23 00:24:22 2021-01-23 22:05:06
42 .0 0% | 1yq9r01hhfrs2 2021-01-23 00:53:13 2021-01-23 22:37:44
32 .0 0% | g7ytdh9mxt1s0 2021-01-23 00:10:09 2021-01-23 22:34:17
30 .0 0% | dgu3kr3g9zfsv 2021-01-23 01:10:05 2021-01-23 23:19:57
29 .0 0% | 5u8tmx4r6j6yp 2021-01-23 00:50:05 2021-01-23 22:49:57
29 .0 0% | c3rvcbu8r3zx8 2021-01-23 00:29:38 2021-01-23 21:59:38
30 rows selected.
--//你可以發現我還是看到的資訊啊,注意FIRST_SEEN,LAST_SEEN欄位,時間上我們生產環境是rac.存在2個例項,另外一個例項不忙.
SYS@192.168.99.105:1521/dbcn> select inst_id,min(sample_time) from gv$active_session_history group by inst_id;
INST_ID MIN(SAMPLE_TIME)
---------- -----------------------
1 2021-01-24 11:38:41.386
2 2021-01-22 10:41:59.364
--//另外一個例項基本沒有業務,這樣看到的情況就是上面的情況.如果查詢改寫如下:
SYS@192.168.99.105:1521/dbcn> @ ashtop sql_id,inst_id,machine 1=1 trunc(sysdate-2) trunc(sysdate-1)
Total
Seconds AAS %This SQL_ID INST_ID MACHINE FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ---------- ----------- ------------------- -------------------
9361 .1 51% | 2 dm01dbadm02 2021-01-23 00:01:39 2021-01-23 23:59:59
1047 .0 6% | 4zbzjuu5h34dn 2 IMC 2021-01-23 00:02:29 2021-01-23 23:55:17
629 .0 3% | 2 dm01dbadm02 2021-01-23 00:00:24 2021-01-23 23:56:21
615 .0 3% | 2w5dgfjvasy4j 2 IMC 2021-01-23 00:02:26 2021-01-23 23:55:13
499 .0 3% | 3ddgu71paks5d 2 IMC 2021-01-23 00:02:35 2021-01-23 23:55:21
498 .0 3% | c5vp872ytwr03 2 IMC 2021-01-23 00:02:38 2021-01-23 23:55:25
493 .0 3% | 7y3xscmmqfymn 2 IMC 2021-01-23 00:02:33 2021-01-23 23:55:19
480 .0 3% | 9yfzqfdw2yhs4 2 IMC 2021-01-23 00:02:24 2021-01-23 23:55:11
463 .0 3% | d14tg929b4xj6 2 ZDFW\DELL56 2021-01-23 00:25:05 2021-01-23 23:45:02
382 .0 2% | 6mnrdrgdys4uc 2 localhost.l 2021-01-23 00:00:31 2021-01-23 23:59:07
262 .0 1% | 8qdgcgn1sz7y8 2 ZDFW\DELL56 2021-01-23 00:31:05 2021-01-23 23:40:59
190 .0 1% | 2 localhost.l 2021-01-23 00:08:07 2021-01-23 23:57:26
187 .0 1% | 4ztz048yfq32s 2 dm01dbadm02 2021-01-23 00:09:16 2021-01-23 23:39:19
152 .0 1% | 4q31ffyqwkt1h 2 dm01dbadm02 2021-01-23 01:39:43 2021-01-23 23:35:22
134 .0 1% | 8b4txypt6ttws 2 IMC 2021-01-23 00:07:41 2021-01-23 23:55:09
80 .0 0% | 772s25v1y0x8k 2 dm01dbadm02 2021-01-23 00:00:57 2021-01-23 23:55:59
80 .0 0% | 9dj4166ys0z0w 2 localhost.l 2021-01-23 00:48:12 2021-01-23 23:57:26
62 .0 0% | 752akhc8hfqc6 2 dm01dbadm02 2021-01-23 04:19:46 2021-01-23 23:05:21
53 .0 0% | 0uuczutvk6jqj 2 dm01dbadm02 2021-01-23 00:26:10 2021-01-23 23:46:10
44 .0 0% | 6c23qpas152z3 2 IMC 2021-01-23 00:24:20 2021-01-23 23:20:10
44 .0 0% | 8g7tjhp1j0ky3 2 IMC 2021-01-23 00:02:37 2021-01-23 23:55:23
44 .0 0% | cr988d50t86za 2 IMC 2021-01-23 00:24:22 2021-01-23 22:05:06
42 .0 0% | 1yq9r01hhfrs2 2 IMC 2021-01-23 00:53:13 2021-01-23 22:37:44
33 .0 0% | 2 2021-01-23 00:04:49 2021-01-23 20:03:54
30 .0 0% | dgu3kr3g9zfsv 2 ZDFW\DELL56 2021-01-23 01:10:05 2021-01-23 23:19:57
29 .0 0% | 5u8tmx4r6j6yp 2 ZDFW\DELL56 2021-01-23 00:50:05 2021-01-23 22:49:57
29 .0 0% | c3rvcbu8r3zx8 2 dm01dbadm02 2021-01-23 00:29:38 2021-01-23 21:59:38
29 .0 0% | ck3nrshb15tb4 2 IMC 2021-01-23 00:24:33 2021-01-23 23:50:23
28 .0 0% | 8835b6xt5yywq 2 dm01dbadm02 2021-01-23 03:32:18 2021-01-23 23:57:18
28 .0 0% | 2 IMC 2021-01-23 01:14:50 2021-01-23 23:40:22
30 rows selected.
--//可以發現都是例項2的語句,而且許多都是IMC機器執行的,這就是我以前提到的無聊的監測軟體執行的語句.
--//連結: http://blog.itpub.net/267265/viewspace-2745795/ -> [20201228]無聊的監測軟體.txt
--//這讓我想起一些事情,如果你監測消耗的資源比應用多,這樣監測是否有意義.
--//還有就是去年遇到的問題連結 http://blog.itpub.net/267265/viewspace-2732010/=> [20201104]磁碟空間消耗在哪裡.txt
--//順便說一下,我提到的情況是我們團隊設定有問題,監測程式要兩邊的例項,我們僅僅監測1個例項.rac就沒有這樣的情況:
SYS@192.168.99.105:1521/dbcn> @ ashtop sql_id,inst_id,machine "MACHINE='IMC'" trunc(sysdate) trunc(sysdate)+1
Total
Seconds AAS %This SQL_ID INST_ID MACHINE FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ---------- ------- ------------------- -------------------
761 .0 21% | 2w5dgfjvasy4j 1 IMC 2021-01-25 00:03:10 2021-01-25 10:15:35
410 .0 12% | 4zbzjuu5h34dn 2 IMC 2021-01-25 00:03:13 2021-01-25 10:15:28
379 .0 11% | 4zbzjuu5h34dn 1 IMC 2021-01-25 00:03:16 2021-01-25 10:15:41
249 .0 7% | 2w5dgfjvasy4j 2 IMC 2021-01-25 00:03:11 2021-01-25 10:15:24
229 .0 6% | 7y3xscmmqfymn 2 IMC 2021-01-25 00:03:17 2021-01-25 10:15:32
225 .0 6% | c5vp872ytwr03 2 IMC 2021-01-25 00:03:22 2021-01-25 10:15:37
223 .0 6% | 3ddgu71paks5d 2 IMC 2021-01-25 00:03:19 2021-01-25 10:15:34
220 .0 6% | 9yfzqfdw2yhs4 2 IMC 2021-01-25 00:03:09 2021-01-25 10:15:20
104 .0 3% | c5vp872ytwr03 1 IMC 2021-01-25 00:03:23 2021-01-25 10:15:52
96 .0 3% | 7y3xscmmqfymn 1 IMC 2021-01-25 00:03:19 2021-01-25 10:15:47
95 .0 3% | 3ddgu71paks5d 1 IMC 2021-01-25 00:03:20 2021-01-25 10:15:49
90 .0 3% | 9yfzqfdw2yhs4 1 IMC 2021-01-25 00:03:09 2021-01-25 10:15:21
49 .0 1% | 8b4txypt6ttws 1 IMC 2021-01-25 00:08:09 2021-01-25 10:15:19
41 .0 1% | 8b4txypt6ttws 2 IMC 2021-01-25 00:08:08 2021-01-25 10:15:19
22 .0 1% | 02hnhz4sz6k0s 1 IMC 2021-01-25 00:16:56 2021-01-25 09:52:17
20 .0 1% | 8g7tjhp1j0ky3 1 IMC 2021-01-25 00:08:34 2021-01-25 08:45:17
18 .0 1% | 6c23qpas152z3 1 IMC 2021-01-25 00:16:55 2021-01-25 10:07:15
18 .0 1% | ck3nrshb15tb4 1 IMC 2021-01-25 01:09:09 2021-01-25 10:02:46
15 .0 0% | 8as31c7q5z314 1 IMC 2021-01-25 01:09:04 2021-01-25 10:15:36
15 .0 0% | 8g7tjhp1j0ky3 2 IMC 2021-01-25 00:26:44 2021-01-25 08:30:31
15 .0 0% | 8q2qq3a76hqft 1 IMC 2021-01-25 00:17:09 2021-01-25 10:07:54
14 .0 0% | 1yq9r01hhfrs2 2 IMC 2021-01-25 00:08:13 2021-01-25 10:15:25
14 .0 0% | cr988d50t86za 1 IMC 2021-01-25 01:08:56 2021-01-25 10:02:17
13 .0 0% | 6c23qpas152z3 2 IMC 2021-01-25 00:03:08 2021-01-25 09:42:26
13 .0 0% | 1 IMC 2021-01-25 00:54:03 2021-01-25 09:57:39
12 .0 0% | 02hnhz4sz6k0s 2 IMC 2021-01-25 00:03:10 2021-01-25 10:15:21
12 .0 0% | d78ubma8q6xj2 1 IMC 2021-01-25 00:03:22 2021-01-25 10:15:51
11 .0 0% | 2 IMC 2021-01-25 01:17:58 2021-01-25 09:37:40
9 .0 0% | 1yq9r01hhfrs2 1 IMC 2021-01-25 01:03:59 2021-01-25 09:47:30
9 .0 0% | 284xbhpcdj6qa 1 IMC 2021-01-25 00:21:43 2021-01-25 09:47:41
30 rows selected.
--//兩邊都有.有點扯遠了
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2752521/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20210506]完善tix指令碼.txt指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20231117]完善ashtt.sql指令碼.txtSQL指令碼
- [20211230]完善sql_id指令碼.txtSQL指令碼
- [20211122]完善descx.sql指令碼.txtSQL指令碼
- [20221012]完善spsw.sql指令碼.txtSQL指令碼
- [20221010]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善gts.sql指令碼.txtSQL指令碼
- [20230203]完善awr.sql指令碼.txtSQL指令碼
- [20230123]完善curheapz.sql指令碼.txtSQL指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20191111]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220217]完善tpt gts.sql指令碼.txtSQL指令碼
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211202]完善d_buffer.sql指令碼.txtSQL指令碼
- [20211129]完善tpt tablist.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.sql指令碼.txtSQL指令碼
- [20211129]完善tpt killi.sql指令碼.txtSQL指令碼
- [20211126]完善tpt pr.sql指令碼.txtSQL指令碼
- [20230414]完善seg2.sql指令碼.txtSQL指令碼
- [20230210]建立完善swcnm.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220311]完善ash_wait_chains指令碼.txtAI指令碼
- [20220309]完善shp4.sql指令碼.txtSQL指令碼
- [20231025]完善tpt的trans.sql指令碼.txtSQL指令碼
- [20241114]建立完善ext_kglob.sh指令碼.txt指令碼
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20220323]完善tpt get_trace.sql指令碼.txtSQL指令碼
- [20220823]完善tpt的ashtop.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap_awr.sql指令碼.txtSQL指令碼
- [20190416]完善shared latch測試指令碼2.txt指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20230302]建立完善tpt o2.sql指令碼.txtSQL指令碼
- [20230203]建立完善sp1x.sql指令碼.txtSQL指令碼
- [20220519]完善tpt dash_wait_chains2.sql指令碼.txtAISQL指令碼