postgresql building lock tree from text log

1) собрать все логи с бд по локам

[code language=»sql»]
[postgres@p38rmisdb01 postgres]$ grep -i ‘lock:’ postgresql-2018-04-06.log
[/code]

2) made a with query enclosing every row with ‘ and with ; at the end
—-best of all do this on different server
[code language=»sql»]
with b as (
select unnest(array[

‘2018-06-05 00:03:33 KRAT [22266]: [17-1] db=lsd,appname=[unknown],user=app_group_master,client=192.168.5.111 DETAIL: Process holding the lock: 24957. Wait queue: 22266.’,
‘2018-06-05 09:00:36 KRAT [5922]: [6-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 17343. Wait queue: 5922.’,
‘2018-06-05 09:01:10 KRAT [5922]: [12-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 13309. Wait queue: 5922.’,
‘2018-06-05 09:06:56 KRAT [9196]: [6-1] db=lsd,appname=192.168.4.72,user=n2o,client=192.168.5.111 DETAIL: Process holding the lock: 10136. Wait queue: .’,
‘2018-06-05 09:09:55 KRAT [28905]: [4-1] db=lsd,appname=192.168.4.22,user=lsd,client=192.168.5.111 DETAIL: Process holding the lock: 24188. Wait queue: 28905.’,
‘2018-06-05 09:10:08 KRAT [28905]: [11-1] db=lsd,appname=192.168.4.22,user=lsd,client=192.168.5.111 DETAIL: Process holding the lock: 24188. Wait queue: 28905.’,
‘2018-06-05 09:11:07 KRAT [28905]: [15-1] db=lsd,appname=192.168.4.22,user=lsd,client=192.168.5.111 DETAIL: Process holding the lock: 24188. Wait queue: 28905.’,
‘2018-06-05 10:00:06 KRAT [342]: [5-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 23155. Wait queue: 342.’,
‘2018-06-05 10:00:41 KRAT [342]: [11-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 9430. Wait queue: 342.’,
‘2018-06-05 10:01:21 KRAT [342]: [17-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 1941. Wait queue: 342.’,
‘2018-06-05 10:09:23 KRAT [27511]: [411-1] db=lsd,appname=192.168.4.72,user=n2o,client=192.168.5.111 DETAIL: Process holding the lock: 18429. Wait queue: .’,
‘2018-06-05 10:09:24 KRAT [18429]: [6-1] db=lsd,appname=192.168.4.72,user=n2o,client=192.168.5.111 DETAIL: Process holding the lock: 30049. Wait queue: .’,
‘2018-06-05 10:09:24 KRAT [30049]: [4-1] db=lsd,appname=192.168.4.72,user=n2o,client=192.168.5.111 DETAIL: Process holding the lock: 18429. Wait queue: 30049.’,
‘2018-06-05 11:00:43 KRAT [29866]: [6-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Processes holding the lock: 2667, 27024. Wait queue: 29866.’,
‘2018-06-05 11:00:56 KRAT [29866]: [12-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 29718. Wait queue: 29866.’,
‘2018-06-05 11:14:49 KRAT [13709]: [902-1] db=lsd,appname=192.168.4.72,user=n2o,client=192.168.5.111 DETAIL: Process holding the lock: 4172. Wait queue: .’,
‘2018-06-05 11:14:49 KRAT [4172]: [4-1] db=lsd,appname=192.168.4.72,user=n2o,client=192.168.5.111 DETAIL: Process holding the lock: 13719. Wait queue: .’,
‘2018-06-05 12:00:07 KRAT [24431]: [5-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 26119. Wait queue: 24431.’,
‘2018-06-05 12:00:41 KRAT [24431]: [11-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 23751. Wait queue: 24431.’,
‘2018-06-05 12:01:02 KRAT [24431]: [17-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 11215. Wait queue: 24431.’,
‘2018-06-05 13:00:43 KRAT [19265]: [6-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 7261. Wait queue: 19265.’,
‘2018-06-05 13:00:46 KRAT [19265]: [12-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 22841. Wait queue: 19265.’,
‘2018-06-05 14:00:02 KRAT [13762]: [4-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 32362. Wait queue: 13762.’,
‘2018-06-05 14:00:06 KRAT [13762]: [10-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 17264. Wait queue: 13762.’,
‘2018-06-05 14:00:34 KRAT [13762]: [16-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 28253. Wait queue: 13762.’,
‘2018-06-05 14:00:37 KRAT [13762]: [22-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 14070. Wait queue: 13762.’,
‘2018-06-05 14:12:13 KRAT [30079]: [702-1] db=lsd,appname=192.168.4.72,user=n2o,client=192.168.5.111 DETAIL: Process holding the lock: 4418. Wait queue: .’,
‘2018-06-05 15:00:02 KRAT [8188]: [4-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 17264. Wait queue: 8188.’,
‘2018-06-05 15:00:07 KRAT [8188]: [10-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 1981. Wait queue: 8188.’,
‘2018-06-05 15:00:40 KRAT [8188]: [16-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Processes holding the lock: 13534, 27378. Wait queue: 8188.’,
‘2018-06-05 15:01:16 KRAT [8188]: [22-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 16850. Wait queue: 8188.’,
‘2018-06-05 16:00:02 KRAT [2756]: [4-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Processes holding the lock: 1981, 25953. Wait queue: 2756.’,
‘2018-06-05 16:00:07 KRAT [2756]: [10-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 23839. Wait queue: 2756.’,
‘2018-06-05 16:00:34 KRAT [2756]: [16-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 8448. Wait queue: 2756.’,
‘2018-06-05 16:01:07 KRAT [2756]: [22-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 6456. Wait queue: 2756.’,
‘2018-06-05 17:00:02 KRAT [29439]: [4-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 23839. Wait queue: 29439.’,
‘2018-06-05 17:00:29 KRAT [29439]: [11-1] db=lsd,appname=psql,user=postgres,client=[local] DETAIL: Process holding the lock: 3378. Wait queue: 29439.’

])
as txt),
analyze_lock as (
select substring(txt, 1,19)::timestamp without time zone as ttime,
split_part(split_part(txt, ‘holding the lock:’, 2), ‘. Wait’, 1) as holding_lock,
split_part(split_part(txt, ‘Wait queue: ‘, 2), ‘.’, 1) as wait_queue,
txt from b
—where txt like ‘%3103%’
— здесь можно оганичить по пользователю, по процессу
),
analyze_lock_normalize as ( —нормализованное предсталвение блокировок (все перечисленные через запятую разбиты на отдельные строки)
SELECT ttime,
unnest(string_to_array(regexp_replace(regexp_replace(cast((holding_lock) AS CHARACTER VARYING), ‘^\(‘,»), ‘\)$’, »), ‘,’))::int AS holding_lock,
unnest(string_to_array(regexp_replace(regexp_replace(cast((wait_queue) AS CHARACTER VARYING), ‘^\(‘,»), ‘\)$’, »), ‘,’))::int AS wait_queue,
txt
from analyze_lock
—WHERE ttime::time between ’14:45:00′::time and ’15:15:00′::time
— здесь можно оганичить по времени
)
,
— обычная паррент таблица
pp as (
select distinct holding_lock::int as id, null::int as parent_id, null::timestamp without time zone as ttime from analyze_lock_normalize t where not exists (select from analyze_lock_normalize t2 where t2.wait_queue = t.holding_lock )
union all
select distinct wait_queue::int as h_id, holding_lock::int as parrent_id, ttime from analyze_lock_normalize t —where not exists (select from analyze_lock t2 where t2.wait_queue like ‘%’||t.holding_lock||’%’ )
),

ltr as (
WITH RECURSIVE ltr_p AS (
SELECT id,
parent_id,
ttime,
id::text::ltree as ltree_lock,
id::text::ltree as main_lock,
1 as llevel
FROM pp
WHERE parent_id IS NULL
UNION ALL
SELECT n.id,
n.parent_id,
n.ttime,
r.ltree_lock || (n.id::text::ltree),
r.main_lock,
r.llevel + 1
FROM pp n
JOIN ltr_p r ON n.parent_id = r.id
where r.llevel < 10 — ограничиваем глубину погружения в локи (главное понять от кого они идут а не кого блокируют)
)
SELECT *, min(ttime) over (partition by main_lock) as main_lock_time, max(ttime) over (partition by main_lock) as main_lock_time_max
FROM ltr_p
ORDER BY ltree_lock
)

select replace(ltree_lock::text,’.’,’ —> ‘) as ltree_lock, min(ttime)/*-INTERVAL ‘5h’*/ as time_lock, main_lock, main_lock_time/*-INTERVAL ‘5h’*/, main_lock_time_max/*-INTERVAL ‘5h’*/ from ltr group by 1, 3, 4, 5 order by 4, 3, min(ttime) nulls first, 1
— you can change " -INTERVAL ‘5h’" to any suitable value

[/code]

Result is:

[code]
10040
10040 06.04.2018 4:38 06.04.2018 4:38
10040 —> 10018 06.04.2018 4:38 10040 06.04.2018 4:38 06.04.2018 4:38
10040 —> 10100 06.04.2018 4:38 10040 06.04.2018 4:38 06.04.2018 4:38
10040 —> 27005 06.04.2018 4:38 10040 06.04.2018 4:38 06.04.2018 4:38
20930
20930 06.04.2018 5:07 06.04.2018 5:07
20930 —> 21075 06.04.2018 5:07 20930 06.04.2018 5:07 06.04.2018 5:07
8548
8548 06.04.2018 6:41 06.04.2018 6:41
8548 —> 30636 06.04.2018 6:41 8548 06.04.2018 6:41 06.04.2018 6:41
27429
27429 06.04.2018 7:01 06.04.2018 7:01
27429 —> 3837 06.04.2018 7:01 27429 06.04.2018 7:01 06.04.2018 7:01
30857
30857 06.04.2018 7:02 06.04.2018 7:02
30857 —> 3837 06.04.2018 7:02 30857 06.04.2018 7:02 06.04.2018 7:02
10219
10219 06.04.2018 7:49 06.04.2018 7:57
10219 —> 31116 06.04.2018 7:49 10219 06.04.2018 7:49 06.04.2018 7:57
10219 —> 31116 —> 9138 06.04.2018 7:53 10219 06.04.2018 7:49 06.04.2018 7:57
10219 —> 31116 —> 31119 06.04.2018 7:55 10219 06.04.2018 7:49 06.04.2018 7:57
10219 —> 31116 —> 31119 —> 3410 06.04.2018 7:56 10219 06.04.2018 7:49 06.04.2018 7:57
10219 —> 31116 —> 9138 —> 31118 06.04.2018 7:57 10219 06.04.2018 7:49 06.04.2018 7:57
30944
30944 06.04.2018 7:56 06.04.2018 7:56
30944 —> 31114 06.04.2018 7:56 30944 06.04.2018 7:56 06.04.2018 7:56
31110
31110 06.04.2018 7:56 06.04.2018 7:56
31110 —> 12204 06.04.2018 7:56 31110 06.04.2018 7:56 06.04.2018 7:56
31110 —> 12204 —> 3411 06.04.2018 7:56 31110 06.04.2018 7:56 06.04.2018 7:56
15286
15286 06.04.2018 8:03 06.04.2018 8:03
15286 —> 13436 06.04.2018 8:03 15286 06.04.2018 8:03 06.04.2018 8:03
18662
18662 06.04.2018 8:04 06.04.2018 8:04
18662 —> 13436 06.04.2018 8:04 18662 06.04.2018 8:04 06.04.2018 8:04
22066
22066 06.04.2018 8:04 06.04.2018 8:04
22066 —> 13436 06.04.2018 8:04 22066 06.04.2018 8:04 06.04.2018 8:04
28885
28885 06.04.2018 9:02 06.04.2018 9:02
28885 —> 22676 06.04.2018 9:02 28885 06.04.2018 9:02 06.04.2018 9:02

[/code]
as you can see the PID 10219 is the cause of cascade locks

4) also you can check what that PID do before locking

[postgres@p38rmisdb01 postgres]$ grep -i ‘\[10219\]’ postgresql-2018-04-06.log

ps: thx to Amir for material !

how-to find FTS of big tables in ash

[code language=»sql»]

with ash_fts as (
select u.username, ash.sql_id,ash.sql_plan_hash_value,ash.sql_plan_line_id from v$active_session_history ash
join dba_users u on ash.user_id=u.user_id
where ash.sql_plan_operation=’TABLE ACCESS’ and ash.sql_plan_options=’FULL’ and ash.sample_time> sysdate-1/24
group by u.username, ash.sql_id,ash.sql_plan_hash_value,ash.sql_plan_line_id) — ищем фулсканы таблиц за последний час, если убрать условие, то будет за весь период который храниться в ASH (глубина зависит от нагрузки на базу, от часов до дней )

,big_tables as ( select round (dt.num_rows*dt.avg_row_len/1024/1024/1024) table_size_gb,table_name,owner From dba_tables dt
where round (dt.num_rows*dt.avg_row_len/1024/1024/1024) > 2) — переписал на получение информации из статистики, ищем таблицы больше 2 гб

select ash.sql_id,b.owner, hsp.object_name from dba_hist_sql_plan hsp
join ash_fts ash on ash.sql_plan_line_id=hsp.id and ash.sql_id=hsp.sql_id and ash.sql_plan_hash_value=hsp.plan_hash_value
join big_tables b on b.table_name=hsp.object_name;
[/code]
в результате увидим sql_id запроса и большую таблицу на которую идет фулскан
[code language=»sql»]

SQL_ID | OWNER | OBJECT_NAME
————- | —————————— | ——————————-
f9j0hsaz7h9y8 | LK | CALENDAR

[/code]

how to delete second group of redo log ( and recreate it with other sizes )

[code language=»sql»]
select ‘alter database drop logfile member »’|| member ||»’;’ from ( select rank ()over ( partition by group# order by member) as rnk ,member from v$logfile ) where rnk=2 ;
[/code]

valid for standby database:
[code language=»sql»]
alter system set standby_file_management=manual;
[/code]

[code language=»sql»]
alter database add logfile thread 1
group 11 (‘+REDO’) size 1G,
group 12 (‘+REDO’) size 1G,
group 13 (‘+REDO’) size 1G,
group 14 (‘+REDO’) size 1G,
group 15 (‘+REDO’) size 1G;

alter database add logfile thread 2
group 21 (‘+REDO’) size 1G,
group 22 (‘+REDO’) size 1G,
group 23 (‘+REDO’) size 1G,
group 24 (‘+REDO’) size 1G,
group 25 (‘+REDO’) size 1G;

alter database add logfile thread 3
group 31 (‘+REDO’) size 1G,
group 32 (‘+REDO’) size 1G,
group 33 (‘+REDO’) size 1G,
group 34 (‘+REDO’) size 1G,
group 35 (‘+REDO’) size 1G;

alter database add standby logfile thread 1
group 111 (‘+REDO’) size 1G,
group 112 (‘+REDO’) size 1G,
group 113 (‘+REDO’) size 1G,
group 114 (‘+REDO’) size 1G,
group 115 (‘+REDO’) size 1G,
group 116 (‘+REDO’) size 1G;

alter database add standby logfile thread 2
group 121 (‘+REDO’) size 1G,
group 122 (‘+REDO’) size 1G,
group 123 (‘+REDO’) size 1G,
group 124 (‘+REDO’) size 1G,
group 125 (‘+REDO’) size 1G,
group 126 (‘+REDO’) size 1G;

alter database add standby logfile thread 3
group 131 (‘+REDO’) size 1G,
group 132 (‘+REDO’) size 1G,
group 133 (‘+REDO’) size 1G,
group 134 (‘+REDO’) size 1G,
group 135 (‘+REDO’) size 1G,
group 136 (‘+REDO’) size 1G;
[/code]

valid for standby database:
[code language=»sql»]
alter system set standby_file_management=auto;
[/code]

how to find query with full scan for last hour ashtop.sql tanel poder (c)

every time it tooks about 15 minutes to find out how to find queries with full scan… time to save it to my blog
[code language=»sql»]
@http://blog.tanelpoder.com/files/scripts/ash/ashtop.sql sql_id,u.username,event "sql_plan_operation=’TABLE ACCESS’ and sql_plan_options=’FULL’" sysdate-1/24 sysdate
[/code]

[code language=»sql»]
Total | | | | | | | | Distinct
Seconds | AAS | %This | SQL_ID | USERNAME | EVENT | FIRST_SEEN | LAST_SEEN | Execs Seen
——— | ——- | ——- | ————- | ——————— | —————————————- | ——————— | ——————— | ———-
124 | .0 | 27% | | 4ztz048yfq32s | DBSNMP | direct path read | 2017-11-27 11:17:15 | 2017-11-27 11:48:41 | 2
58 | .0 | 13% | | 27mg3w92ah9fx | PATROL | <NULL> | 2017-11-27 11:18:28 | 2017-11-27 11:52:26 | 33
48 | .0 | 11% | | 4ztz048yfq32s | DBSNMP | <NULL> | 2017-11-27 11:17:29 | 2017-11-27 11:48:35 | 2
28 | .0 | 6% | | dwz7dgfp7k41u | XXX | <NULL> | 2017-11-27 10:53:20 | 2017-11-27 11:52:38 | 28
27 | .0 | 6% | | 8576v2udda8xd | PATROL | <NULL> | 2017-11-27 11:18:28 | 2017-11-27 11:51:50 | 27
17 | .0 | 4% | | cv8umnmuc8kc8 | XXX | <NULL> | 2017-11-27 10:53:53 | 2017-11-27 11:50:26 | 17
16 | .0 | 4% | | 297hq9h6h9mgt | PATROL | <NULL> | 2017-11-27 10:56:51 | 2017-11-27 11:45:34 | 16
15 | .0 | 3% | | 5ypmtb01t0bpf | NSI | <NULL> | 2017-11-27 10:53:26 | 2017-11-27 11:49:35 | 15
13 | .0 | 3% | | 68q8jd8rj7fjp | XXXXLOGS | <NULL> | 2017-11-27 11:06:25 | 2017-11-27 11:07:14 | 13
10 | .0 | 2% | | 9g6pyx7qz035v | XXXXAUDIT | <NULL> | 2017-11-27 10:59:31 | 2017-11-27 11:45:06 | 10
9 | .0 | 2% | | 9q00wxqqzqjdg | SYS | db file scattered read | 2017-11-27 11:08:02 | 2017-11-27 11:08:16 | 1
7 | .0 | 2% | | 98uu7x2kgw9f7 | SYS | db file scattered read | 2017-11-27 11:08:18 | 2017-11-27 11:08:30 | 1
4 | .0 | 1% | | 98uu7x2kgw9f7 | SYS | <NULL> | 2017-11-27 11:08:20 | 2017-11-27 11:08:28 | 1
4 | .0 | 1% | | 9q00wxqqzqjdg | SYS | gc cr multi block request | 2017-11-27 11:08:01 | 2017-11-27 11:08:15 | 1
4 | .0 | 1% | | 9q00wxqqzqjdg | SYS | <NULL> | 2017-11-27 11:08:03 | 2017-11-27 11:08:17 | 1

[/code]

how to find pid which spam to alert log

if u dont know who is spamming to alert log there is the way to figure out this
[code language=»sql»]
col cnt format a20
col host_id format a20
col message_text format a40
col process_id format a10
select count(*) cnt,host_id,message_text,process_id from X$DBGALERTEXT where message_text like ‘%Turning off busy%’ group by host_id,message_text,process_id;

CNT | HOST_ID | MESSAGE_TEXT | PROCESS_ID
———- | ——————— | —————————————- | ———-
28857 | xxxxxxxxxxxxxxxxxxxx | Turning off busy | 28518

[xxxxxxxxxxxxxxxxxxxx ]# ps -ef | grep 28518 | grep -v "grep"
oracle 28518 1 25 Oct16 ? 10:23:42 ora_fbda_xxxxxx2
[/code]

and it’s Flasback process =)

ps:oracle 12.1

PostgreSQL explain plan operations

to be continued

> — what does «Bitmap Heap Scan» phase do?

A plain indexscan fetches one tuple-pointer at a time from the index,
and immediately visits that tuple in the table. A bitmap scan fetches
all the tuple-pointers from the index in one go, sorts them using an
in-memory «bitmap» data structure, and then visits the table tuples in
physical tuple-location order. The bitmap scan improves locality of
reference to the table at the cost of more bookkeeping overhead to
manage the «bitmap» data structure — and at the cost that the data
is no longer retrieved in index order, which doesn’t matter for your
query but would matter if you said ORDER BY.

> — what is «Recheck condition» and why is it needed?

If the bitmap gets too large we convert it to «lossy» style, in which we
only remember which pages contain matching tuples instead of remembering
each tuple individually. When that happens, the table-visiting phase
has to examine each tuple on the page and recheck the scan condition to
see which tuples to return.

> — why are proposed «width» fields in the plan different between the two
> plans?

Updated statistics about average column widths, presumably.

> (actually, a nice explanation what exactly are those widths would also
> be nice :) )

Sum of the average widths of the columns being fetched from the table.

> — I thought «Bitmap Index Scan» was only used when there are two or more
> applicable indexes in the plan, so I don’t understand why is it used
> now?

True, we can combine multiple bitmaps via AND/OR operations to merge
results from multiple indexes before visiting the table … but it’s
still potentially worthwhile even for one index. A rule of thumb is
that plain indexscan wins for fetching a small number of tuples, bitmap
scan wins for a somewhat larger number of tuples, and seqscan wins if
you’re fetching a large percentage of the whole table.

regards, tom lane

https://www.postgresql.org/message-id/12553.1135634231@sss.pgh.pa.us

Oracle, find query witch runs more than &1 sec

[code language=»sql»]
with stats as (
select /*+ materialize */
* from (
select parsing_schema_name,t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
select s.parsing_schema_name,min(begin_interval_time) start#,max(begin_interval_time) end#, sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
round ((sum(elapsed_time_delta)/(sum(executions_delta)))/1000000) avg_sec
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where 1=1
and ss.begin_interval_time > trunc(sysdate-4,’iw’)
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
and (elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 > &1
group by sql_id, plan_hash_value,parsing_schema_name
)t
join dba_hist_sqltext s on s.sql_id=t.sql_id
order by execs*avg_sec desc ) tt where
rownum <= 50 )
select * from stats s
where parsing_schema_name not like ‘SYS%’
and s.sql_text not like ‘%/* DS_SVC */%’
and s.sql_text not like ‘%_job_proc%’
and s.sql_text not like ‘%SQL Analyze%’
and lower(s.sql_text) not like ‘%dbms_feature%’
and s.sql_text not like ‘%SYS_DBA_SEGS%’
and lower(s.sql_text) not like ‘%v$sys%’
and lower(s.sql_text) not like ‘%dba_audit_trail%’
and lower(s.sql_text) not like ‘%no_index(mytab)%’;
[/code]