[go: up one dir, main page]
Scribd
Upload
13 views7 pages

Stats Pack

Uploaded by

cristianovinicius
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
13 views7 pages

Stats Pack

Uploaded by

cristianovinicius
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

STATSPACK

1) Criar tablespace para abrigar os dados


do Statspack
export ORACLE_SID=orcl
sqlplus / as sysdba

CREATE TABLESPACE perfstat_tbs DATAFILE


'+DATA' SIZE 10M AUTOEXTEND ON NEXT
10M MAXSIZE 1G;

alter session set container=pdb1;

CREATE TABLESPACE perfstat_tbs DATAFILE


'+DATA' SIZE 10M AUTOEXTEND ON NEXT
10M MAXSIZE 1G;

2) Criar statspack

alter session set container=CDB$ROOT;

@?/rdbms/admin/spcreate
alter session set container=pdb1;

@?/rdbms/admin/spcreate

3) Gerando snapshot

sqlplus /nolog

conn perfstat/perfstat@orcl

EXECUTE statspack.snap;
conn perfstat/perfstat@pdb1

EXECUTE statspack.snap;

4) Criação de job para geração dos


snapshots

• DBMS_JOBS

conn / as sysdba

@?/rdbms/admin/spauto
alter session set container=pdb1;

@?/rdbms/admin/spauto

set verify off


set pagesize 250
set linesize 150
set echo off
col job for 9999
col log_user head "Owner" for a8
col what head "Job PL/SQL" for a80 wrap
col ls head "Last Exec" for a17
col nd head "Next Exec" for a17
col total_time head "Time" for 99999
col interval head "Freq Exec" for a25 wrap
col failures head "Fail" for 999
col what for a50

select job,broken,log_user,
to_char(last_date,'dd/mm/yy hh24:mi:ss')
ls,
to_char(next_date,'dd/mm/yy hh24:mi:ss')
nd,
-- total_time,
interval,
failures,
what
from dba_jobs
order by job;

• DBMS_SCHEDULER

BEGIN
DBMS_SCHEDULER.CREATE_SCHEDULE(
schedule_name => 'GERA_SNAPSHOT',
repeat_interval =>
'FREQ=MINUTELY;BYMINUTE=00,30');
END;
/

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name =>
'JOB_GERA_SNAPSHOT',
job_type =>
'STORED_PROCEDURE',
job_action => 'statspack.snap',
schedule_name => 'GERA_SNAPSHOT',
auto_drop => FALSE,
comments => 'Geracao de
coleta snapshots Statspack');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE('JOB_GERA_SNAPSH
OT');
END;
/

5) Geração de relatórios
conn perfstat/perfstat@orcl

select
snap_id,to_char(snap_time,'DD/
MM/YYYY HH24:MI:SS') "DATE" from
stats$snapshot order by
snap_time desc;

@?/rdbms/admin/spreport

conn perfstat/perfstat@pdb1

select
snap_id,to_char(snap_time,'DD/
MM/YYYY HH24:MI:SS') "DATE" from
stats$snapshot order by
snap_time desc;

@?/rdbms/admin/spreport

6) Alteração nível de coleta do Statspack


SELECT SNAP_LEVEL, DESCRIPTION FROM
STATS$LEVEL_DESCRIPTION ORDER BY
SNAP_LEVEL;
execute
statspack.modify_statspack_param
eter(i_snap_level => 6,
i_modify_parameter => 'true');

8) Remoção de snapshots
@?/rdbms/admin/sppurge

EXEC
STATSPACK.PURGE(I_NUM_DAYS=>40,I_EX
TENDED_PURGE=>TRUE);

@?/rdbms/admin/sptrunc

9) Desinstalação Statspack

conn / as sysdba

@?/rdbms/admin/spdrop

drop tablespace PERFSTAT_TBS including


contents and datafiles;
alter session set container=pdb1;

@?/rdbms/admin/spdrop

drop tablespace PERFSTAT_TBS including


contents and datafiles;

Fontes:
https://docs.oracle.com/cd/B10501_01/server.920/a96533/
statspac.htm

You might also like