Skip to content

audit purge

audit purge published on Комментариев к записи audit purge нет

short way truncate table sys.aud$ =)))

BEGIN
  DBMS_AUDIT_MGMT.init_cleanup(
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24 /* hours */);
END;
/
SET SERVEROUTPUT ON
BEGIN
  IF DBMS_AUDIT_MGMT.is_cleanup_initialized(DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD) THEN
    DBMS_OUTPUT.put_line('YES');
  ELSE
    DBMS_OUTPUT.put_line('NO');
  END IF;
END;
/
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-31);
END;
/

create purge audit job

--create STANDARD_AUDIT_TRAIL_PURGE_JOB
BEGIN
DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
AUDIT_TRAIL_PURGE_INTERVAL => 24,
AUDIT_TRAIL_PURGE_NAME => 'Standard_Audit_Trail_Purge_Job',
USE_LAST_ARCH_TIMESTAMP => TRUE );
END;
/

create job to update what can job delete from audit

BEGIN
  SYS.DBMS_SCHEDULER.CREATE_JOB
    (
       job_name        => 'SYS.MOVE_LAST_TIMESTAMP_FORWARD'
      ,start_date      => trunc (SYSTIMESTAMP) 
      ,repeat_interval => 'FREQ=DAILY;INTERVAL=1'
      ,end_date        => NULL
      ,job_class       => 'DEFAULT_JOB_CLASS'
      ,job_type        => 'PLSQL_BLOCK'
      ,job_action      => '
BEGIN
  DBMS_AUDIT_MGMT.set_last_archive_timestamp(
   audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   last_archive_time => SYSTIMESTAMP-31);
'
      ,comments        => NULL
    );
end;
/

BEGIN
DBMS_SCHEDULER.set_attribute( name => '"SYS"."MOVE_LAST_TIMESTAMP_FORWARD"', attribute => 'auto_drop', value => FALSE);
DBMS_SCHEDULER.enable(name=>'"SYS"."MOVE_LAST_TIMESTAMP_FORWARD"');
END; 
/

SELECT * FROM dba_audit_mgmt_config_params;
SELECT * FROM dba_audit_mgmt_last_arch_ts;
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS;

Standard_Audit_Trail_Purge_Job

ROLLBACK

BEGIN
  DBMS_AUDIT_MGMT.set_purge_job_status(
    audit_trail_purge_name   => 'STANDARD_AUDIT_TRAIL_PURGE_JOB',
    audit_trail_status_value => DBMS_AUDIT_MGMT.PURGE_JOB_DISABLE);
end;
/

almost all material from oracle-base.com

postgrespro pg_stat_wait patch

postgrespro pg_stat_wait patch published on Комментариев к записи postgrespro pg_stat_wait patch нет

usefull materials
github postgrespro
Waits monitoring on mail list of postgresql.org
pgconf2015 slides

Packages

ubuntu:

apt-get install gcc libreadline-dev zlib1g-dev libxml2-dev libldap2-dev python-dev libssl-dev build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev libperl-dev

fedora:

yum install -y gcc bison-devel flex bison readline-devel zlib-devel openssl-devel wget perl-ExtUtils-MakeMaker perl-ExtUtils-Embed  readline-devel zlib-devel libxml2-devel openldap-devel python-devel openssl-devel

Install from source

mkdir /postgres/src -p
cd /postgres/src
wget https://github.com/postgrespro/postgres/archive/waits_monitoring_94.zip
unzip waits_monitoring_94.zip
cd postgres-waits_monitoring_94/
./configure --with-ldap --with-perl --with-python --with-openssl --with-libxml --prefix=/usr/pgsql-pro --exec-prefix=/usr/pgsql-pro
make
make install
/postgres/src/postgres-waits_monitoring_94/contrib/pg_stat_wait
make
make install
groupadd postgres -g 26
useradd --uid 26 --gid postgres --groups postgres -d /postgres -s /bin/bash -c "PostgreSQL Software Owner" postgres

enable waits monitoring

add to postgresql.conf

#History GUC parameters:

 shared_preload_libraries = 'pg_stat_wait.so' #for background worker that will be sample waits.
 pg_stat_wait.history = on #/off - turn on/off history recording
 pg_stat_wait.history_size = 1000 #how many records keep in history
 pg_stat_wait.history_period = 100 # period in millseconds between the sampling
waits_monitoring=on

### alter system set waits_monitoring=on;

 postgres=# select * from pg_stat_wait_current;
  pid  |          sample_ts           | class_id | class_name | event_id | event_name | wait_time  |  p1  |  p2   |  p3   | p4 |   p5
-------+------------------------------+----------+------------+----------+------------+------------+------+-------+-------+----+--------
 19213 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        0 | READ       |    2971593 | 1663 | 13056 | 12817 |  0 |      6
 19061 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        0 | READ       |        622 | 1663 | 13056 | 12810 |  0 |     44
 19054 | 2015-07-21 05:54:41.86929-04 |        3 | Storage    |        1 | WRITE      |      47717 | 1663 | 16418 | 16431 |  0 | 165053
 19056 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |      15808 |    0 |     0 |     0 |  0 |      0
 19059 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |       3179 |    0 |     0 |     0 |  0 |      0
 19057 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |    2971267 |    0 |     0 |     0 |  0 |      0
 19055 | 2015-07-21 05:54:41.86929-04 |        4 | Latch      |        0 | Latch      |     157947 |    0 |     0 |     0 |  0 |      0
 19206 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        544 |    0 |     0 |     0 |  0 |      0
 19204 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       1958 |    0 |     0 |     0 |  0 |      0
 19209 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        380 |    0 |     0 |     0 |  0 |      0
 19062 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       | 2726490525 |    0 |     0 |     0 |  0 |      0
 19201 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        704 |    0 |     0 |     0 |  0 |      0
 19203 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       2002 |    0 |     0 |     0 |  0 |      0
 19208 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |       1625 |    0 |     0 |     0 |  0 |      0
 19210 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        999 |    0 |     0 |     0 |  0 |      0
 19205 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        248 |    0 |     0 |     0 |  0 |      0
 19202 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        648 |    0 |     0 |     0 |  0 |      0
 19207 | 2015-07-21 05:54:41.86929-04 |        5 | Network    |        0 | READ       |        718 |    0 |     0 |     0 |  0 |      0

ps: Many thanks to Vladimir Borodin for help !

awr report by hour

awr report by hour published on Комментариев к записи awr report by hour нет

simplest way to generate awr report by hour after benchmark
export awr

@?/rdbms/admin/awrextr.sql 

import awr

@?/rdbms/admin/awrload.sql

— make sure to set line size appropriately
— set linesize 152

generate awr

 set termout off
set linesize 80
set pagesize 10000
select 'spool awrrpt_dwhfrn_'|| snap_id ||'.html'|| chr(13)||
 'select output from table(dbms_workload_repository.awr_report_text('||dbid||',1,'||snap_id||','|| LEAD (snap_id, 1)  OVER (ORDER BY  dbid,snap_id ) ||',0));' || chr(13)||
 ' spool off '
 from dba_hist_snapshot where dbid !=1399642255 and extract( minute from begin_interval_time ) <2 ;

Primary Sidebar

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