Skip to content

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)%';

Postgresql how-to find top query for last 15 seconds

Postgresql how-to find top query for last 15 seconds published on Комментариев к записи Postgresql how-to find top query for last 15 seconds нет
DROP TABLE IF EXISTS pg_stat_statements_temp; 
  create table pg_stat_statements_temp as
  SELECT  d.datname,u.usename, s.queryid,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean,
now() as cur_date
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid;
 
    select pg_sleep(15);



 with stat as (SELECT  d.datname,u.usename, s.queryid,s.query,
round(s.total_time::numeric, 2) AS total_time,
s.calls,
round(s.mean_time::numeric, 2) AS mean
FROM    pg_stat_statements s
    join pg_database d on s.dbid=d.oid
    join pg_user u on s.userid=u.usesysid  )
    select  s.datname,s.usename,s.queryid,regexp_replace(s.query, E'[\n\r]+', ' ', 'g' )::varchar(80) AS short_query,
    s.total_time-t.total_time as time,
    s.calls-t.calls as calls,
    s.mean -t.mean as mean, 
    round( 
      (100 * (s.total_time-t.total_time) /sum(s.total_time-t.total_time) OVER ()
            )::numeric, 2)     as cpu,
    now()-t.cur_date as diff_time, 
CASE WHEN s.calls-t.calls >0  THEN 
     (s.calls-t.calls )/(extract ( second from now()-t.cur_date) + extract ( minutes from now()-t.cur_date)*60 + extract ( minutes from now()-t.cur_date)*60*60 ) 
    else '0' 
    end as tps
     from stat s
    	join pg_stat_statements_temp t on s.datname=t.datname and s.usename=t.usename and s.queryid=t.queryid
    	order by cpu desc limit 20;

Oracle find query runs slower than ‘&1’ seconds in ‘&2’ minutes

Oracle find query runs slower than ‘&1’ seconds in ‘&2’ minutes published on Комментариев к записи Oracle find query runs slower than ‘&1’ seconds in ‘&2’ minutes нет
with stats as (
select /*+ materialize */
 * from (
select  t.start#,t.end#,t.sql_id,t.plan_hash_value,t.execs,t.avg_sec,s.sql_text from (
 select  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 > (sysdate -&2/1440)
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
 )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 1=1
  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

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