Skip to content

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'
    group by u.username, ash.sql_id,ash.sql_plan_hash_value,ash.sql_plan_line_id)                            
    , big_tables as (  select owner,segment_name,sum(bytes)/1024/1024 as MB_SIZE from dba_segments 
   group by segment_name,owner
    having sum(bytes)/1024/1024 > 2048) 
 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.segment_name=hsp.object_name ;   

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

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