Skip to content

postgresql building lock tree from text log

postgresql building lock tree from text log published on Комментариев к записи postgresql building lock tree from text log нет

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

[postgres@p38rmisdb01 postgres]$ grep -i 'lock:' postgresql-2018-04-06.log

2) made a with query enclosing every row with ‘ and with ; at the end
—-best of all do this on different server

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

Result is:

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

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

how-to find FTS of big tables in ash published on Комментариев к записи how-to find FTS of big tables in ash нет
 
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;
 

в результате увидим sql_id запроса и большую таблицу на которую идет фулскан


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

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

how to delete second group of redo log ( and recreate it with other sizes ) published on Комментариев к записи how to delete second group of redo log ( and recreate it with other sizes ) нет
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 ;

valid for standby database:

alter system set standby_file_management=manual;
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;

valid for standby database:

alter system set standby_file_management=auto;

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

how to find query with full scan for last hour ashtop.sql tanel poder (c) published on Комментариев к записи 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

 @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
    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

how to find pid which spam to alert log

how to find pid which spam to alert log published on Комментариев к записи 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

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

and it’s Flasback process =)

ps:oracle 12.1

PostgreSQL explain plan operations

PostgreSQL explain plan operations published on Комментариев к записи 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

Oracle, find query witch runs more than &1 sec published on Комментариев к записи Oracle, find query witch runs more than &1 sec нет
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)%';

Primary Sidebar

Яндекс.Метрика