[20210125]完善hide.sql指令碼.txt

lfree發表於2021-01-25

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章