implementando o STATSPACK – Oracle 10/11g

abril 16, 2014 - Instalação, Oracle Database, Performance - Tagged: implementando statspack, oracle statspack, oracle statspack 10g, oracle statspack 11g, statspack - 1 comment

https://etbscreenwriting.com/tmwmvzcfr Tanto na versão 10g como na 11g a utilização das features de DIAGNOSTIC e TUNING (controlada a partir do 11g pelo parâmetro control_management_pack_access) exige a necessidade de uma licença a parte. Sem a mesma o Oracle não irá armazenar dados relacionados a performance do ambiente, o que pode dificultar nosso trabalho como DBA’s.

get link Afim de evitar que o ambiente fique sem coleta de informações relacionadas a performance, uma alternativa que não exige licenciamento é a utilização do STATAPACK.

https://ict-pulse.com/2024/07/3g4edgfyk6w O STATSPACK é fornecido pela Oracle desde a versão 8i e tem como objetivo auxiliar na análise e monitoramento de performance do ambiente, fornecendo um relatório detalhado, semelhante ao AWR.

go here Abaixo estarei demonstrando o processo para implementar o statspack. O processo de ser executado através de uma conexão sysdba.

https://www.pathwaysmagazineonline.com/3dulc85pbeo Na primeira etapa será criada uma tablespace que será utilizada pelo statspack.

https://www.techonicsltd.com/uncategorized/4i1unos create tablespace statspack datafile '/oraprd02/oradata/dbtrn/statspack01.dbf' size 100m autoextend on next 100m maxsize 5000m; Tablespace created.

https://brako.com/en/pknlwhjt3w O próximo passo consiste na execução do script spcreate.sql localizado em $ORACLE_HOME/rdbms/admin conectado como sysdba.

Durante a execução deste script será solicitada a definição de uma senha para o usuário perfstat (criado pelo processo). A definição de uma senha é obrigatório para possamos continuar com a instalação;
Após definir a senha, será solicitada a default tablespace. No exemplo abaixo estarei definindo como statspack (tablespace criada anteriormente);
Por último será solicitado qual a tablespace temporária a ser utilizada. Neste caso, definirei como a tablespace padrão (TEMP).

go SQL> @?/rdbms/admin/spcreate.sql Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING Enter value for perfstat_password: perfstat perfstat 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 ------------------------------ --------- ---------------------------- STATSPACK PERMANENT SYSAUX PERMANENT * USERS PERMANENT Pressing will result in STATSPACK's recommended default tablespace (identified by *) being used. Enter value for default_tablespace: STATSPACK Using tablespace STATSPACK as PERFSTAT default tablespace. 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 will result in the database's default Temporary tablespace (identified by *) being used. Enter value for temporary_tablespace: TEMP . . . Creating Package STATSPACK... Package created. No errors. Creating Package Body STATSPACK... Package body created. No errors. NOTE: SPCPKG complete. Please check spcpkg.lis for any errors.

https://hymnsandhome.com/2024/07/25/98ezq29 Para que possamos gerar um relatório do ambiente, assim como no AWR, precisamos gerar snapshots da base. Um snapshot pode ser gerado manualmente ou automaticamente.

follow Abaixo estarei mostrando os 2 exemplos:

Para uma coleta automática, estarei executando o script spauto.sql. Este script criará um job que por padrão irá gerar um snapshot por hora. De acordo com a necessidade do ambiente é possível alterar o intervalo de execução deste job.

enter SQL> @?/rdbms/admin/spauto.sql PL/SQL procedure successfully completed. Job number for automated statistics collection for this instance ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Note that this job number is needed when modifying or removing the job: JOBNO ---------- 43 Job queue process ~~~~~~~~~~~~~~~~~ Below is the current setting of the job_queue_processes init.ora parameter - the value for this parameter must be greater than 0 to use automatic statistics gathering: NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ job_queue_processes integer 1000 Next scheduled run ~~~~~~~~~~~~~~~~~~ The next scheduled run for this job is: JOB NEXT_DATE NEXT_SEC ---------- ------------------ -------- 43 30/04/014 15:00:00 15:00:00 1 row selected.

Para uma coleta manual usamos o comando abaixo, conectados com o usuário perfstat.

https://splendormedicinaregenerativa.com/v1761cpxi5 SQL> EXEC STATSPACK.snap PL/SQL procedure successfully completed. SQL>

https://bxscco.com/r8x3fmx Ao utilizar um processo automatizado para a geração de snapshots, devemos ficar atentos e realizar periodicamente uma limpeza dos mesmos. Para o processo de limpeza usamos o script sppurge.sql.

Tramadol For Sale Online Cod Na execução do script será solicitado o intervalo de snapshots a serem removidos.

https://www.pathwaysmagazineonline.com/unp28qfz396 SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT order by 2; SNAP_ID SNAP_TIME ---------- ------------------ 57 29/04/014 15:00:00 58 29/04/014 16:00:01 59 29/04/014 17:00:02 60 29/04/014 18:00:02 61 29/04/014 19:00:03 62 29/04/014 20:00:04 63 29/04/014 21:00:00 64 29/04/014 22:00:01 71 29/04/014 23:00:02 72 30/04/014 00:00:03 73 30/04/014 01:00:04 74 30/04/014 02:00:04 75 30/04/014 03:00:00 76 30/04/014 04:00:01 77 30/04/014 05:00:01 78 30/04/014 06:00:02 79 30/04/014 07:00:03 80 30/04/014 08:00:03 81 30/04/014 09:00:04 82 30/04/014 10:00:00 83 30/04/014 11:00:00 84 30/04/014 12:00:01 85 30/04/014 13:00:02 86 30/04/014 14:00:02 24 rows selected. SQL> @$ORACLE_HOME/rdbms/admin/sppurge.sql Database Instance currently connected to ======================================== Instance DB Id DB Name Inst Num Name ----------- ---------- -------- ---------- 3062688822 DBTST 1 dbtst Snapshots for this database instance ==================================== Base- Snap Snap Id Snapshot Started line? Level Host Comment -------- --------------------- ----- ----- --------------- -------------------- 57 29 Apr 2014 15:00:00 5 baseteste 58 29 Apr 2014 16:00:01 5 baseteste 59 29 Apr 2014 17:00:02 5 baseteste 60 29 Apr 2014 18:00:02 5 baseteste 61 29 Apr 2014 19:00:03 5 baseteste 62 29 Apr 2014 20:00:04 5 baseteste 63 29 Apr 2014 21:00:00 5 baseteste 64 29 Apr 2014 22:00:01 5 baseteste 71 29 Apr 2014 23:00:02 5 baseteste 72 30 Apr 2014 00:00:03 5 baseteste 73 30 Apr 2014 01:00:04 5 baseteste 74 30 Apr 2014 02:00:04 5 baseteste 75 30 Apr 2014 03:00:00 5 baseteste 76 30 Apr 2014 04:00:01 5 baseteste 77 30 Apr 2014 05:00:01 5 baseteste 78 30 Apr 2014 06:00:02 5 baseteste 79 30 Apr 2014 07:00:03 5 baseteste 80 30 Apr 2014 08:00:03 5 baseteste 81 30 Apr 2014 09:00:04 5 baseteste 82 30 Apr 2014 10:00:00 5 baseteste 83 30 Apr 2014 11:00:00 5 baseteste 84 30 Apr 2014 12:00:01 5 baseteste 85 30 Apr 2014 13:00:02 5 baseteste 86 30 Apr 2014 14:00:02 5 baseteste Warning ~~~~~~~ sppurge.sql deletes all snapshots ranging between the lower and upper bound Snapshot Id's specified, for the database instance you are connected to. Snapshots identified as Baseline snapshots which lie within the snapshot range will not be purged. It is NOT possible to rollback changes once the purge begins. You may wish to export this data before continuing. Specify the Lo Snap Id and Hi Snap Id range to purge ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for losnapid: 57 Using 57 for lower bound. Enter value for hisnapid: 60 Using 60 for upper bound. Deleting snapshots 57 - 60. Number of Snapshots purged: 4 ~~~~~~~~~~~~~~~~~~~~~~~~~~~ Purge of specified Snapshot range complete.

https://autismwish.org/hwnxuqj Afim de reduzir a necessidade de intervenção manual no processo de gerenciamento dos snapshots, estarei criando uma procedure chamada statspackpurge para limpeza dos snapshots, com uma retenção de 7 dias, porém esse valor pode ser alterado de acordo com a necessidade do ambiente.

go SQL> create or replace procedure statspackpurge is var_lo_snap number; var_hi_snap number; var_db_id number; var_instance_no number; noofsnapshot number; n_count number ; begin n_count := 0; select count(*) into n_count from stats$snapshot where snap_time < sysdate-7; if n_count > 0 then select min(s.snap_id) , max(s.snap_id),max(di.dbid),max(di.instance_number) into var_lo_snap, var_hi_snap,var_db_id,var_instance_no from stats$snapshot s , stats$database_instance di where s.dbid = di.dbid and s.instance_number = di.instance_number and di.startup_time = s.startup_time and s.snap_time < sysdate-7; noofsnapshot := statspack.purge( i_begin_snap => var_lo_snap , i_end_snap => var_hi_snap , i_snap_range => true , i_extended_purge => false , i_dbid => var_db_id , i_instance_number => var_instance_no); dbms_output.Put_line('snapshot deleted'||to_char(noofsnapshot)); end if; end; / Procedure created.

https://www.insearchofsukoon.com/80e7xqjydjd Após criada a procedure, estarei criando um job que executara o processo de limpeza diariamente.

Tramadol Sales Cheap SQL> declare my_job number; begin dbms_job.submit(job => my_job, what => 'statspackpurge;', next_date => trunc(sysdate)+1, interval => 'trunc(sysdate)+1'); end; 2 3 4 5 6 7 8 9 10 / PL/SQL procedure successfully completed.

follow url Para consultar os snpshots existentes, podemos usar o comando abaixo, conectados com o owner perfstat.

enter SQL> select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT; Snap Id SNAP_TIME -------- ------------------ 63 29/04/014 21:00:00 74 30/04/014 02:00:04 82 30/04/014 10:00:00 61 29/04/014 19:00:03 62 29/04/014 20:00:04 75 30/04/014 03:00:00 76 30/04/014 04:00:01 77 30/04/014 05:00:01 80 30/04/014 08:00:03 81 30/04/014 09:00:04 83 30/04/014 11:00:00 84 30/04/014 12:00:01 86 30/04/014 14:00:02 71 29/04/014 23:00:02 72 30/04/014 00:00:03 73 30/04/014 01:00:04 78 30/04/014 06:00:02 79 30/04/014 07:00:03 85 30/04/014 13:00:02 64 29/04/014 22:00:01 20 rows selected.

Buying Tramadol In Canada Após gerado o snapshot, estarei gerando um relatório com a análise do ambiente. Assim como o AWR, para gerar o relatório do statspack, devemos selecionar um snapshot inicial e um final e posteriormente definimos o nome do arquivo a ser gerado, que pode ser precedido pelo local de destino. Neste exemplo usei o destino /tmp.

click here SQL> SQL> @?/rdbms/admin/spreport.sql Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 3062688822 DBTST 1 dbtst 1 row selected. Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ----------- -------- ------------ ------------ ------------ 3062688822 1 DBTST dbtst baseteste Using 3062688822 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. Listing all Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level Comment ------------ ------------ --------- ----------------- ----- -------------------- dbtst DBTST 61 29 Apr 2014 19:00 5 62 29 Apr 2014 20:00 5 63 29 Apr 2014 21:00 5 64 29 Apr 2014 22:00 5 71 29 Apr 2014 23:00 5 72 30 Apr 2014 00:00 5 73 30 Apr 2014 01:00 5 74 30 Apr 2014 02:00 5 75 30 Apr 2014 03:00 5 76 30 Apr 2014 04:00 5 77 30 Apr 2014 05:00 5 78 30 Apr 2014 06:00 5 79 30 Apr 2014 07:00 5 80 30 Apr 2014 08:00 5 81 30 Apr 2014 09:00 5 82 30 Apr 2014 10:00 5 83 30 Apr 2014 11:00 5 84 30 Apr 2014 12:00 5 85 30 Apr 2014 13:00 5 86 30 Apr 2014 14:00 5 87 30 Apr 2014 14:31 5 Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 61 Begin Snapshot Id specified: 61 Enter value for end_snap: 64 End Snapshot Id specified: 64 Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_61_64. To use this name, press to continue, otherwise enter an alternative. Enter value for report_name: /tmp/estatistica_dbtst.lst

Tramadol Online Overnight Uk Após a execução, foi criado o report /tmp/statistica_dbtst.lst. Devido a quantidade de informações geradas no arquivo, o mesmo não será mostrado por completo.

https://ict-pulse.com/2024/07/7hhv6f8lyaq STATSPACK report for Database DB Id Instance Inst Num Startup Time Release RAC ~~~~~~~~ ----------- ------------ -------- --------------- ----------- --- 3442772195 dbtrn 1 16-Apr-14 16:48 11.2.0.4.0 NO Host Name Platform CPUs Cores Sockets Memory (G) ~~~~ ---------------- ---------------------- ----- ----- ------- ------------ cce Linux IA (32-bit) 1 0 0 2.0 Snapshot Snap Id Snap Time Sessions Curs/Sess Comment ~~~~~~~~ ---------- ------------------ -------- --------- ------------------ Begin Snap: 1 16-Apr-14 21:35:37 33 2.0 End Snap: 3 16-Apr-14 21:44:06 35 2.0 Elapsed: 8.48 (mins) Av Act Sess: 0.0 DB time: 0.34 (mins) DB CPU: 0.05 (mins) Cache Sizes Begin End ~~~~~~~~~~~ ---------- ---------- Buffer Cache: 752M Std Block Size: 8K Shared Pool: 240M Log Buffer: 15,360K Load Profile Per Second Per Transaction Per Exec Per Call ~~~~~~~~~~~~ ------------------ ----------------- ----------- ----------- DB time(s): 0.0 0.3 0.01 0.09 DB CPU(s): 0.0 0.0 0.00 0.01 Redo size: 4,975.9 34,694.7 Logical reads: 32.5 226.7 Block changes: 13.8 96.4 Physical reads: 0.0 0.1 Physical writes: 3.7 25.6 User calls: 0.4 3.0 Parses: 3.2 22.1 Hard parses: 0.2 1.4 W/A MB processed: 0.1 0.9 Logons: 0.1 0.5 Executes: 5.5 38.4 Rollbacks: 0.0 0.0 Transactions: 0.1 Instance Efficiency Indicators ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 99.99 Redo NoWait %: 100.00 Buffer Hit %: 99.96 Optimal W/A Exec %: 100.00 Library Hit %: 93.92 Soft Parse %: 93.49 Execute to Parse %: 42.43 Latch Hit %: 100.00 Parse CPU to Parse Elapsd %: 39.29 % Non-Parse CPU: 96.27 Shared Pool Statistics Begin End ------ ------ Memory Usage %: 84.57 85.56 % SQL with executions>1: 48.16 51.64 % Memory for SQL w/exec>1: 53.34 57.77 Top 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------------------- ------------ ----------- ------ ------ control file parallel write 208 14 67 35.2 log file parallel write 94 8 90 21.4 db file async I/O submit 366 6 16 15.3 log file sync 37 5 131 12.3 CPU time 3 7.5 ------------------------------------------------------------- Host CPU (CPUs: 1 Cores: 0 Sockets: 0) ~~~~~~~~ Load Average Begin End User System Idle WIO WCPU ------- ------- ------- ------- ------- ------- -------- 0.44 0.55 1.09 0.96 97.94 11.98 Note: There is a 12% discrepancy between the OS Stat total CPU time and the total CPU time estimated by Statspack OS Stat CPU time: 570(s) (BUSY_TIME + IDLE_TIME) Statspack CPU time: 509(s) (Elapsed time * num CPUs in end snap)

https://etbscreenwriting.com/4ymldztm Para remover o statspack, baste executar o comando spdrop.sql.

https://gsaudemarketing.com.br/nr6nbtw4x7 @?/rdbms/admin/spdrop.sql

https://etbscreenwriting.com/mdz2yyc Referências:

Tramadol India Online http://docs.oracle.com/cd/B10500_01/server.920/a96533/statspac.htm

Autor: Jhonata Lamim

https://brako.com/en/i0hq2xyaa

https://gsaudemarketing.com.br/8zm4oabwe MBA em Banco de Dados Oracle, formado pelo Centro Universitário de Araraquara (UNIARA), graduado em Sistemas de Informação pelo Centro Universitário de Brusque (UNIFEBE).

https://ict-pulse.com/2024/07/evyj84s0 Atua com Banco de Dados Oracle desde Junho de 2010. Atualmente é DBA Senior na Exímio Soluções em TI (www.eximioti.com.br)

Tramadol Online By Cod https://geneticsandfertility.com/c4k9fhlfm1a Principais atividade:

go to link Implementação, migração, gerenciamento e suporte a produtos Oracle (10g, 11g, 12c, RAC), multiplataforma;
Monitoramento de ambientes 24×7;
Backup e Recovery;
Performance e Tuning;
Alta disponibilidade (HA);
EM database/grid/cloud control 12c/13c;
Conversão de databases;
Standby database / Oracle Data Guard;
Migração de dados para Oracle;

https://www.techonicsltd.com/uncategorized/4uqee9263t https://gsaudemarketing.com.br/yqza4al9doe Experiência:

Cheap Overnight Tramadol Cod DBA Oracle Teiko Soluções em TI – Jun/2010 – Abr/2018
DBA Oracle, Outsourcing – Marfrig Group – Set/2013 – Abr/2018
DBA Oracle, Outsourcing – Grupo Notre Dame – Intermédica – Mar/2017 – Abr/2018
DBA Oralce, Outsourcing – Hospital Beneficiência Portuguesa de São Paulo – Set/2015 – Abr/2018
DBA Oracle, Outsourcing – Fundação São Francisco Xavier – Set/2015 – Fev/2017
DBA Oracle, Outsourcing – Unimed Grande Florianopolis – Set/2014 – Jul/2016
DBA Oracle, Outsourcing – Hospital Moinhos de Vento – Set/2014 – Set/2015
DBA Oracle, Outsourcing – Santa Casa de Misericórdia de Porto Alegre – 2013

go to link https://living4youboutique.com/p8ji972r0 Certificações:

https://thefooduntold.com/food-science/dwixlzr OCS 12C – Oracle Real Application Clusters 12c Certified Implementation Specialist
OCS 12C – Oracle Database 12c Certified Implementation Specialist
OCE 11G – Oracle Database 11g: Performance Tuning
OCA 11G – Oracle Certified Associate Administrator
OCP 11G/12C – Oracle Certified Professional Administrator
OCS 11G – Oracle Certified Specialist
OPNCS 11G – Oracle Partner Network Certified Specialist
OCS – Oracle Linux 6 Implementation Essentials

https://autismwish.org/gleyq2h7e0 Linkedin: https://www.linkedin.com/in/jhonata-lamim-dba-oracle-61366484/

click here