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

来源：这里教程网时间：2026-03-03 16:22:45 作者：

[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. --//两边都有.有点扯远了

编辑推荐：

返回列表

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

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推