{"id":117,"date":"2018-04-17T18:04:19","date_gmt":"2018-04-17T21:04:19","guid":{"rendered":"http:\/\/wordpress.rodrigosilvaesilva.com.br\/?p=117"},"modified":"2018-04-17T18:04:19","modified_gmt":"2018-04-17T21:04:19","slug":"instalacao-statspack-coleta-estatisticas-completo","status":"publish","type":"post","link":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/2018\/04\/17\/instalacao-statspack-coleta-estatisticas-completo\/","title":{"rendered":"Instala\u00e7\u00e3o Statspack \/ Coleta estat\u00edsticas completo"},"content":{"rendered":"
Criar Tablespace<\/h5>\n
12:49:57 SYS@ORCL > create tablespace tools datafile '\/u01\/oracle\/oradata\/ORCL\/tools.dbf' size 200m autoextend on next 100m;\nTablespace created.\n\n<\/pre>\n
Consultando espa\u00e7o livre na tablespace.<\/h5>\n
select * from dba_free_space where tablespace_name = 'TOOLS';\n\nTABLESPACE_NAME                   FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO\n------------------------------ ---------- ---------- ---------- ---------- ------------\nTOOLS                                  13        128  208666624      25472           13<\/pre>\n
Instalando o Statspack<\/h5>\n
@?\/rdbms\/admin\/spcreate<\/pre>\n
Defina a senha do perfstat<\/h5>\n
Choose the PERFSTAT user's password\n-----------------------------------\nNot specifying a password will result in the installation FAILING\n\nEnter value for perfstat_password: perfstat<\/pre>\n
Definir a tablespace padr\u00e3o<\/h5>\n
Choose the Default tablespace for the PERFSTAT user\n---------------------------------------------------\nBelow is the list of online tablespaces in this database which can\nstore user data.  Specifying the SYSTEM tablespace for the user's\ndefault tablespace will result in the installation FAILING, as\nusing SYSTEM for performance data is not supported.\n\nChoose the PERFSTAT users's default tablespace.  This is the tablespace\nin which the STATSPACK tables and indexes will be created.\n\nTABLESPACE_NAME                CONTENTS              STATSPACK DEFAULT TABLESPACE\n------------------------------ --------------------- ----------------------------\nPROD01_DATA                    PERMANENT\nPROD01_INDX                    PERMANENT\nSYSAUX                         PERMANENT             *\nTOOLS                          PERMANENT\nUSERS                          PERMANENT\nElapsed: 00:00:00.00\n\nPressing <return> will result in STATSPACK's recommended default\ntablespace (identified by *) being used.\n\nEnter value for default_tablespace: TOOLS\n\nUsing tablespace TOOLS as PERFSTAT default tablespace.\n\n<\/pre>\n
Definir a tablespace tempor\u00e1ria para o usu\u00e1rio PERFSTAT<\/h5>\n
Choose the Temporary tablespace for the PERFSTAT user\n-----------------------------------------------------\nBelow is the list of online tablespaces in this database which can\nstore temporary data (e.g. for sort workareas).  Specifying the SYSTEM\ntablespace for the user's temporary tablespace will result in the\ninstallation FAILING, as using SYSTEM for workareas is not supported.\n\nChoose the PERFSTAT user's Temporary tablespace.\n\nTABLESPACE_NAME                CONTENTS              DB DEFAULT TEMP TABLESPACE\n------------------------------ --------------------- --------------------------\nTEMP                           TEMPORARY             *\n\nPressing <return> will result in the database's default Temporary\ntablespace (identified by *) being used.\n\nEnter value for temporary_tablespace: TEMP\n\nUsing tablespace TEMP as PERFSTAT temporary tablespace.\n\n... Creating PERFSTAT user\n\n... Installing required packages\n\n... Creating views\n\n... Granting privileges\n\nNOTE:\nSPCUSR complete. Please check spcusr.lis for any errors.\n...\n\n...\n\n...\n\n12:53:33 SYS@ORCL > set echo off;\nCreating Package STATSPACK...\n\nPackage created.\n\nElapsed: 00:00:07.92\nNo errors.\nCreating Package Body STATSPACK...\n\nPackage body created.\n\nElapsed: 00:01:29.14\nNo errors.\n\nNOTE:<\/strong>\nSPCPKG complete. Please check spcpkg.lis for any errors.<\/strong><\/pre>\n
Coletando estat\u00edsticas manual.<\/h5>\n
execute statspack.snap;<\/pre>\n
Coletando estat\u00edsticas autom\u00e1tica.<\/h5>\n
@?\/rdbms\/admin\/spauto<\/pre>\n
Listando os Snapshots<\/h5>\n
[oracle@ORCL]$ sqlplus perfstat\/perfstat\n\nselect name,snap_id,to_char(snap_time,'DD.MM.YYYY:HH24:MI:SS')\n\"Date\/Time\" from stats$snapshot,v$database\norder by 1, 2;\n\nNAME         SNAP_ID Date\/Time\n--------- ---------- -------------------\nORCL               1 16.04.2018:13:00:04\nORCL               2 16.04.2018:14:00:18\nORCL               3 16.04.2018:15:00:29\nORCL              11 16.04.2018:16:00:11\nORCL              12 16.04.2018:17:00:26\nORCL              13 16.04.2018:18:00:26\nORCL              14 16.04.2018:19:00:11\nORCL              15 16.04.2018:20:00:11\nORCL              16 16.04.2018:21:00:20\nORCL              17 16.04.2018:22:00:05\n\n10 rows selected.\n<\/pre>\n
Analisando relat\u00f3rio<\/h5>\n
@?\/rdbms\/admin\/spreport<\/pre>\n
Listing all Completed Snapshots\n\n                                                       Snap\nInstance     DB Name        Snap Id   Snap Started    Level Comment\n------------ ------------ --------- ----------------- ----- --------------------\nORCL          ORCL                  1 16 Apr 2018 13:00     5\n                                  2 16 Apr 2018 14:00     5\n                                  3 16 Apr 2018 15:00     5\n                                 11 16 Apr 2018 16:00     5\n                                 12 16 Apr 2018 17:00     7\n                                 13 16 Apr 2018 18:00     7\n\nSpecify the Begin and End Snapshot Ids\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~\nEnter value for begin_snap: 11\nBegin Snapshot Id specified: 11\n\nEnter value for end_snap: 13\nEnd   Snapshot Id specified: 13\n\n\n\nSpecify the Report Name\n~~~~~~~~~~~~~~~~~~~~~~~\nThe default report file name is sp_11_13.  To use this name,\npress <return> to continue, otherwise enter an alternative.\n\nEnter value for report_name: analise lentidao<\/pre>\n
Report Gerado<\/h5>\n
Using the report name analise_lentidao\n\nSTATSPACK report for\n\nDatabase    DB Id    Instance     Inst Num  Startup Time   Release     RAC\n~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---\n          1775857365 NBS                 1 16-Apr-18 15:11 12.2.0.1.0  NO\n\nHost Name             Platform                CPUs Cores Sockets   Memory (G)\n~~~~ ---------------- ---------------------- ----- ----- ------- ------------\n     bdora-granleste. Linux x86 64-bit           8     8       4         15.4\n\nSnapshot       Snap Id     Snap Time      Sessions Curs\/Sess Comment\n~~~~~~~~    ---------- ------------------ -------- --------- ------------------\nBegin Snap:         33 17-Apr-18 14:00:00       92       3.4\n  End Snap:         35 17-Apr-18 16:00:25       96       2.6\n   Elapsed:     120.42 (mins) Av Act Sess:       0.7\n   DB time:      80.42 (mins)      DB CPU:      53.39 (mins)\n\nCache Sizes            Begin        End\n~~~~~~~~~~~       ---------- ----------\n    Buffer Cache:     6,480M     6,432M   Std Block Size:         8K\n     Shared Pool:     1,184M     1,232M       Log Buffer:     4,248K\n\nLoad Profile              Per Second    Per Transaction    Per Exec    Per Call\n~~~~~~~~~~~~      ------------------  ----------------- ----------- -----------\n      DB time(s):                0.7                0.1        0.00        0.00\n       DB CPU(s):                0.4                0.0        0.00        0.00\n       Redo size:           26,156.2            1,868.5\n   Logical reads:           14,586.2            1,042.0\n   Block changes:              211.4               15.1\n  Physical reads:                1.1                0.1\n Physical writes:                9.7                0.7\n      User calls:              158.3               11.3\n          Parses:              235.6               16.8\n     Hard parses:               14.0                1.0\nW\/A MB processed:                0.1                0.0\n          Logons:                0.3                0.0\n        Executes:              570.9               40.8\n       Rollbacks:               13.9                1.0\n    Transactions:               14.0\n<\/pre>\n
Remover jobs agendados<\/h4>\n
Ver tarefas<\/h5>\n
select job, log_user from user_jobs;<\/pre>\n
Remover job<\/h5>\n
exec dbms_job.remove(81);<\/pre>\n
Buscar parametros<\/h5>\n
SGA Target Advisory\n\nPGA Memory Advisory<\/pre>\n
Deletar jobs antigos<\/h5>\n
delete from perfstat.stats$snapshot where snap_time < (sysdate - 7);<\/pre>\n
Desinstalar o statspack<\/h5>\n
@?\/rdbms\/admin\/spdrop.sql<\/pre>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Criar Tablespace 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\u00e7o livre na tablespace. 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 @?\/rdbms\/admin\/spcreate Defina a…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[158,339],"class_list":["post-117","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-instalacao-statspack","tag-statspack"],"_links":{"self":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/117","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=117"}],"version-history":[{"count":0,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/posts\/117\/revisions"}],"wp:attachment":[{"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rodrigosilvaesilva.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}