Criar Tablespace
|
1 2 |
12:49:57 SYS@ORCL > create tablespace tools datafile '/u01/oracle/oradata/ORCL/tools.dbf' size 200m autoextend on next 100m; Tablespace created. |
Consultando espaço livre na tablespace.
|
1 2 3 4 5 |
select * from dba_free_space where tablespace_name = 'TOOLS'; TABLESPACE_NAME FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO ------------------------------ ---------- ---------- ---------- ---------- ------------ TOOLS 13 128 208666624 25472 13 |
Instalando o Statspack
|
1 |
@?/rdbms/admin/spcreate |
Defina a senha do perfstat
|
1 2 3 4 5 |
Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING Enter value for perfstat_password: perfstat |
Definir a tablespace padrão
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
Choose the Default tablespace for the PERFSTAT user --------------------------------------------------- Below is the list of online tablespaces in this database which can store user data. Specifying the SYSTEM tablespace for the user's default tablespace will result in the installation FAILING, as using SYSTEM for performance data is not supported. Choose the PERFSTAT users's default tablespace. This is the tablespace in which the STATSPACK tables and indexes will be created. TABLESPACE_NAME CONTENTS STATSPACK DEFAULT TABLESPACE ------------------------------ --------------------- ---------------------------- PROD01_DATA PERMANENT PROD01_INDX PERMANENT SYSAUX PERMANENT * TOOLS PERMANENT USERS PERMANENT Elapsed: 00:00:00.00 Pressing <return> will result in STATSPACK's recommended default tablespace (identified by *) being used. Enter value for default_tablespace: TOOLS Using tablespace TOOLS as PERFSTAT default tablespace. |
Definir a tablespace temporária para o usuário PERFSTAT
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
Choose the Temporary tablespace for the PERFSTAT user ----------------------------------------------------- Below is the list of online tablespaces in this database which can store temporary data (e.g. for sort workareas). Specifying the SYSTEM tablespace for the user's temporary tablespace will result in the installation FAILING, as using SYSTEM for workareas is not supported. Choose the PERFSTAT user's Temporary tablespace. TABLESPACE_NAME CONTENTS DB DEFAULT TEMP TABLESPACE ------------------------------ --------------------- -------------------------- TEMP TEMPORARY * Pressing <return> will result in the database's default Temporary tablespace (identified by *) being used. Enter value for temporary_tablespace: TEMP Using tablespace TEMP as PERFSTAT temporary tablespace. ... Creating PERFSTAT user ... Installing required packages ... Creating views ... Granting privileges NOTE: SPCUSR complete. Please check spcusr.lis for any errors. ... ... ... 12:53:33 SYS@ORCL > set echo off; Creating Package STATSPACK... Package created. Elapsed: 00:00:07.92 No errors. Creating Package Body STATSPACK... Package body created. Elapsed: 00:01:29.14 No errors. <strong>NOTE:</strong> <strong>SPCPKG complete. Please check spcpkg.lis for any errors.</strong> |
Coletando estatísticas manual.
|
1 |
execute statspack.snap; |
Coletando estatísticas automática.
|
1 |
@?/rdbms/admin/spauto |
Listando os Snapshots
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
[oracle@ORCL]$ sqlplus perfstat/perfstat select name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS') "Date/Time" from stats$snapshot,v$database order by 1, 2; NAME SNAP_ID Date/Time --------- ---------- ------------------- ORCL 1 16.04.2018:13:00:04 ORCL 2 16.04.2018:14:00:18 ORCL 3 16.04.2018:15:00:29 ORCL 11 16.04.2018:16:00:11 ORCL 12 16.04.2018:17:00:26 ORCL 13 16.04.2018:18:00:26 ORCL 14 16.04.2018:19:00:11 ORCL 15 16.04.2018:20:00:11 ORCL 16 16.04.2018:21:00:20 ORCL 17 16.04.2018:22:00:05 10 rows selected. |
Analisando relatório
|
1 |
@?/rdbms/admin/spreport |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level Comment ------------ ------------ --------- ----------------- ----- -------------------- ORCL ORCL 1 16 Apr 2018 13:00 5 2 16 Apr 2018 14:00 5 3 16 Apr 2018 15:00 5 11 16 Apr 2018 16:00 5 12 16 Apr 2018 17:00 7 13 16 Apr 2018 18:00 7 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 11 Begin Snapshot Id specified: 11 Enter value for end_snap: 13 End Snapshot Id specified: 13 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_11_13. To use this name, press <return> to continue, otherwise enter an alternative. Enter value for report_name: analise lentidao |
Report Gerado
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
Using the report name analise_lentidao STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 1775857365 NBS 1 16-Apr-18 15:11 12.2.0.1.0 NO Host Name Platform CPUs Cores Sockets Memory (G) ~~~~ ---------------- ---------------------- ----- ----- ------- ------------ bdora-granleste. Linux x86 64-bit 8 8 4 15.4 Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------ Begin Snap: 33 17-Apr-18 14:00:00 92 3.4 End Snap: 35 17-Apr-18 16:00:25 96 2.6 Elapsed: 120.42 (mins) Av Act Sess: 0.7 DB time: 80.42 (mins) DB CPU: 53.39 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 6,480M 6,432M Std Block Size: 8K Shared Pool: 1,184M 1,232M Log Buffer: 4,248K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ ------------------ ----------------- ----------- ----------- DB time(s): 0.7 0.1 0.00 0.00 DB CPU(s): 0.4 0.0 0.00 0.00 Redo size: 26,156.2 1,868.5 Logical reads: 14,586.2 1,042.0 Block changes: 211.4 15.1 Physical reads: 1.1 0.1 Physical writes: 9.7 0.7 User calls: 158.3 11.3 Parses: 235.6 16.8 Hard parses: 14.0 1.0 W/A MB processed: 0.1 0.0 Logons: 0.3 0.0 Executes: 570.9 40.8 Rollbacks: 13.9 1.0 Transactions: 14.0 |
Remover jobs agendados
Ver tarefas
|
1 |
select job, log_user from user_jobs; |
Remover job
|
1 |
exec dbms_job.remove(81); |
Buscar parametros
|
1 2 3 |
SGA Target Advisory PGA Memory Advisory |
Deletar jobs antigos
|
1 |
delete from perfstat.stats$snapshot where snap_time < (sysdate - 7); |
Desinstalar o statspack
|
1 |
@?/rdbms/admin/spdrop.sql |