ITPub博客

首页 > 数据库 > Oracle > [20210125]完善hide.sql脚本.txt

[20210125]完善hide.sql脚本.txt

原创 Oracle 作者:lfree 时间:2021-01-25 10:22:25 0 删除 编辑

[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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2855
  • 访问量
    6643915