Arquivo do mês: maio 2012

Conceito de Latch oracle

Latches os “Locks” na memória no Oracle quinta-feira, abril 29th, 2010 Quem esta acostumado a verificar a performance através dos eventos de tempo, fatalmente vai se deparar com eventos de latches, e para esclarecer um pouco esse conceito não muito … Continuar lendo

Publicado em LATCHES | Deixe um comentário

Latches

Latch e Hard Parses Latch é um mecanismo de alocação de estruturas na memória SGA serializado e desenhado para que sejam alocados por curtos períodos de tempo. Ele controla os vários processos que desejam acessar áreas compartilhadas da SGA, permitindo que … Continuar lendo

Publicado em LATCHES | Deixe um comentário

DOWNLOAD LINUX RED HAT

Índice de /pub/redhat/linux/ Nome Tamanho Data da modificação [diretório pai] 1.0/ 13/07/02 00:00:00 1.1/ 13/07/02 00:00:00 2.0/ 13/07/02 00:00:00 2.1/ 13/07/02 00:00:00 3.0.3/ 13/07/02 00:00:00 4.0/ 13/07/02 00:00:00 4.1/ 13/07/02 00:00:00 4.2/ 13/07/02 00:00:00 5.0/ 13/07/02 00:00:00 5.1/ 13/07/02 00:00:00 … Continuar lendo

Publicado em DOWNLOADS, LINUX | Deixe um comentário

Install Oracle 11gR2 on Solaris 10

Install Oracle 11gR2 on Solaris 10 This document is a step-by-step guide howto install Oracle 11gR2 on Solaris 10. One of Favourite Design‘s bigger customers have such an Oracle 11gR2 installation burried deep down in an application stack of Favourite Designs contractual responsibility. … Continuar lendo

Publicado em ORACLE 11gR2 | Deixe um comentário

Oracle VirtualBox SHARED FOLDER

How to mount a shared folder in virtualbox guest Written by Super User Issue How to mount a shared folder in virtualbox guest Environment Host = Windows 7; shared folder name = TEST Guest = Red Hat / Oracle Enterprise Linux … Continuar lendo

Publicado em VIRTUAL BOX | Deixe um comentário

Tuning and Optimizing Red Hat Linux Advanced Server 2.1 for Oracle9i Database

The following procedure is a step-by-step guide with tips and information for tuning and optimizing Red Hat Linux Advanced Server 2.1 for Oracle9i. This summary (HOWTO) shows how I tuned and optimized Red Hat AS 2.1 for Oracle 9iR2 (9.2.0). … Continuar lendo

Publicado em S.O. TUNING | Marcado com , , , | Deixe um comentário

APOSTILA DE SQL

O objetivo da Apostila é trazer os principais comandos SQL usados em aplicações ORACLE, com exemplos, comentários e explicações. 2 FINALIDADE DO CAPITULO: No final deste capitulo você será capaz de criar scripts permitindo que o usuário entre com valores … Continuar lendo

Publicado em TUTORIAL | Marcado com | Deixe um comentário

1z0 -007

1z0 -007 QUESTION NO: 1 Examine the data in the EMPLOYEES and DEPARTMENTS tables. EMPLOYEES LAST_NAME DEPARTMENT_ID SALARY Getz 10 3000 Davis 20 1500 King 20 2200 Davis 30 5000 Kochhar 5000 DEPARTMENTS DEPARTMENT_ID DEPARTMENT_NAME 10 Sales 20 Marketing 30 … Continuar lendo

Publicado em OCP | Marcado com | Deixe um comentário

1z0-030

Oracle9i: New Features for Administrators QUESTION NO: 1 Examine the code. CREATE ROLE readonly IDENTIFIED USING app.chk_readwrite ; CREATE ROLE readwrite IDENTIFIED USING app.chk_readwrite ; CREATE OR REPLACE PROCEDURE app.chk_readwrite AUTHID CURRENT_USER IS ipchk STRING(30); BEGIN IF says context (‘USERENV’, … Continuar lendo

Publicado em OCP | Marcado com | Deixe um comentário

1z0-040

Oracle Database 10g: New Features for Administrators   QUESTION NO: 1 Which three methods can you use to run an Automatic Database Diagnostic Monitor (ADDM) analysis over a specific time period? (Choose three.) A. Enterprise Manager GUI B. DBMS_TRACE package … Continuar lendo

Publicado em OCP | Marcado com | Deixe um comentário

1z0-058

  Oracle Real Application Clusters 11g Release 2 and   Grid Infrastructure Administration   QUESTION NO: 1   Which three actions would be helpful in determining the cause of a node reboot?   A. determining the time of the node … Continuar lendo

Publicado em OCE RAC | Marcado com | Deixe um comentário

Oracle 1Z0-048

Oracle Database 10g R2: Administering RAC 1. A junior DBA is learning how to administer an Oracle Cluster and asks you how to diagnose voting disk online and offline problems, and where to find information on voting disk online and … Continuar lendo

Publicado em OCE RAC | Marcado com | Deixe um comentário

ENTENDENDO OS RELATÓRIOS GERADOS PELO STATSPACK

PURPOSE This article is a reference to understand the output generated by the STATSPACK utility. Since performance tuning is a very broad area this document only provide tuning advice in very specific areas. Several documents are available in Metalink to … Continuar lendo

Publicado em STATSPACK | Marcado com , , | Deixe um comentário

STATSPACK alert report for the DBA

Here is the ‘STATSPACK_ALERT.SQL’ script: –*********************************************************** — — STATSPACK alert report for the DBA — — Created 8/4/2000 by Donald K. Burleson — http://www.dba-oracle.com — — This script is provided free-of-charge by Don Burleson — — This script accepts the … Continuar lendo

Publicado em STATSPACK | Marcado com , , | Deixe um comentário

(STATSPACK) Guide

  Goal RDBMS version 10g offers a new and improved tool for diagnosing Database Perfromance issues. It is the Automated WorkLoad Repository (AWR). However, there are still a number of customers using statistics package (statspack) intially introduced in RDBMS version … Continuar lendo

Publicado em STATSPACK | Marcado com , | Deixe um comentário

COMO RESOLVER PROBLEMAS DE I/O

——————- SCOPE & APPLICATION ——————- The techniques described here can be followed when: o Statspack or AWR reports show I/O wait events in the “Top 5 Wait/Timed Events” section. o SQL Tracing with wait events of a database session shows … Continuar lendo

Publicado em I/O TUNING, PLSQL SCRIPTS, PLSQL TUNING | Marcado com | Deixe um comentário

FREELISTS and FREELIST GROUPS

FREELISTS and FREELIST GROUPS ============================= The following bulletin is comprised of several parts: 1. Introduction to Oracle data block organization 2. Free List Overview 3. Free List Types 4. Considerations for Optimizing Space Management 5. Algorithms 6. FreeLists in Oracle … Continuar lendo

Publicado em BUFFERS, FREELISTS, PLSQL SCRIPTS, PLSQL TUNING, SGA | Marcado com , , | Deixe um comentário

SCRIPT DE DIAGNOSTICO PARA TUNING EM 8i E 9i

Description The script does a performance healthchek on the instance and database and makes suggestions on modifications that can be made if specific conditions exist. The report should be run after the system has been up for at least 10 … Continuar lendo

Publicado em FAST DIAG | Marcado com , , , , | Deixe um comentário

How to use OS commands to diagnose Database Performance issues?

PURPOSE ——- The purpose of this document is to provide a few OS commands for UNIX operating systems to gather information about Physical Memory (RAM), swap memory,CPU usage and idle percentage, whether lots of processes are in the process run … Continuar lendo

Publicado em LINUX, PLSQL SCRIPTS, S.O. TUNING, TUTORIAL, UNIX | Deixe um comentário

understanding 9i Segment-Level Statistics

to understand new Segment-Level Statistics feature. SCOPE & APPLICATION ——————- Application DBA and Performance DBA’s Segment-Level Statistics ————————— This is a new feature introduce in 9.2. We can now gather segment-level statistics to find out the performance problems associated with … Continuar lendo

Publicado em INDEX TUNING, PERFORMANCE, PLSQL TUNING | Deixe um comentário

USING ORACLE SCRIPTS TO TUNE INDEX

Problem Description: ==================== You have many indexes on your tables. You are wondering if you are making the best use of indexing. You need to know how to pick the best columns to index. You need to know how to … Continuar lendo

Publicado em INDEX TUNING, PERFORMANCE, PLSQL TUNING | Deixe um comentário

TAMANHO DO ÍNDICE E OUTROS SEGMENTOS

select segment_name, sum(bytes)/1024 as MB from dba_segments where segment_name in( ‘PAY_STATISTICS’, ‘ICO_RQST_MULTI’, ‘INV_NBR_POOL’, ‘LOG_OPER_CONFIG’ ) group by segment_name   select segment_name, sum(bytes)/1024 as MB from dba_segments where segment_name in( ‘CTT_TDRS_GSM’, ‘AQ$_HLR_REQ_QT51_T’, ‘HLR_ASR_QT51’, ‘CTT_CDRS’, ‘PAY_PLAFOND_CONFIG’, ‘CAR_PLAFOND_UNIT’, ‘PRV_PLATAFORM_IMPACT’, ‘INSTALLED_PLATFORM’, ‘PRV_HLR_SERVERS’) group by … Continuar lendo

Publicado em INDEX TUNING | Deixe um comentário

How to Monitor the Usage of Indexes

This script will monitor the usage of indexes on the database. Product Name, Product Version Oracle Server, 7.3 to 10.0 Platform Platform Independent Date Created 09-Jul-1997 Instructions Execution Environment: SQL, SQL*Plus Access Privileges: Requires DBA access privileges to be executed. … Continuar lendo

Publicado em INDEX TUNING | Marcado com | Deixe um comentário

SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES

Abstract SCRIPT TO CHECK FOR FOREIGN KEY LOCKING ISSUES Product Name, Product Version Rdbms:07.0.X – 08.XX Platform Platform Independent Date Created 07-NOV-2002 Instructions Execution Environment: <SQL, SQL*Plus> Access Privileges: If run as owner of objects no special priveleges required Usage: … Continuar lendo

Publicado em INDEX TUNING, PLSQL SCRIPTS, PLSQL TUNING | Deixe um comentário

Pre-Loading Oracle Text indexes into Memory

Pre-Loading Oracle Text indexes into Memory Introduction Oracle Text indexes are stored in Oracle Database relational tables. These tables normally reside on disk. Systems with large memory configurations generally benefit greatly from Oracle caching – a second or subsequent search … Continuar lendo

Publicado em BUFFERS, INDEX TUNING, PLSQL SCRIPTS, PLSQL TUNING, SGA | Marcado com | Deixe um comentário

Identifying unused indexes in Oracle9i

PURPOSE ——- The purpose of this document is to explain how to find unused indexes using the new feature in Oracle9: “Identifying Unused Indexes” via ALTER INDEX MONITORING USAGE, as mentioned in Oracle9i Database Administrator’s Guide, Chapter 11. The clause … Continuar lendo

Publicado em INDEX TUNING, PARTICIONAMENTO, PLSQL SCRIPTS | Marcado com | Deixe um comentário

Database Performance FAQ

Database Performance FAQ INVESTIGATING A DATABASE PERFORMANCE ISSUE DIAGNOSTICS AWR reports/Statspack reports 10046 Trace Querying V$Session_wait System State Dumps Errorstack PSTACK PLSQL Profiler Hanganalyze INTERPRETING THE RESULTS/TRACES TOP DATABASE PERFORMANCE ISSUES/PROBLEMS AND HOW TO RESOLVE THEM Library Cache/Shared Pool Latch … Continuar lendo

Publicado em AWR, BUFFERS, FAST DIAG, PLSQL SCRIPTS, TRACE | Marcado com | Deixe um comentário

SET AUTOTRACE COMMAND

SET AUTOTRACE COMMAND: ====================== The SET AUTOTRACE command allows you to automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics in SQL*Plus. The report is generated after successful SQL DML (Data … Continuar lendo

Publicado em PLSQL SCRIPTS, PLSQL TUNING, TRACE | Marcado com | Deixe um comentário

How to Enable SQL_TRACE for Another Session Using Oradebug

How to Enable SQL_TRACE for Another Session Using ORADEBUG: =========================================================== The ORADEBUG utility can enable/disable setting the SQL tracing for another user’s session. To enable tracing for another session, the Oracle process identifier (PID) or the Operating System processes identifier … Continuar lendo

Publicado em PLSQL TUNING, TRACE | Marcado com , , , , | Deixe um comentário

Problemas comuns de performance

TROUBLESHOOTING GUIDE Common Performance Tuning Issues Table of Contents 1. Introduction 2. Shared Pool and Library Cache Performance Tuning 3. Buffer Cache Performance Tuning 4. Latch Contention 5. Redo Log Buffer Performance Tuning 6. Rollback Segment Performance Tuning 7. Temporary … Continuar lendo

Publicado em BUFFERS, PLSQL SCRIPTS, PLSQL TUNING, RECOMENDAÇÕES, SGA | Marcado com | Deixe um comentário

Understanding and Tuning the Shared Pool

Understanding and Tuning the Shared Pool 1.            Introduction The aim of this article is to introduce the key issues involved in tuning the shared pool in Oracle 7 through 9. The notes here are particularly important if your system shows … Continuar lendo

Publicado em BUFFERS, PLSQL SCRIPTS, SGA | Deixe um comentário

PINNING ORACLE APPLICATIONS OBJECTS INTO THE SHARED POOL

Pinning Oracle Applications Objects into the shared pool Scope: Oracle Applications DBAs who want to improve database performance by pinning packages into shared pool. Why pinning objects into the shared pool? Oracle Applications requires space in the ORACLE System Global … Continuar lendo

Publicado em BUFFERS, ORACLE EBS, PLSQL SCRIPTS, PLSQL TUNING, SGA | Deixe um comentário

How to measure the performance of 1.processes, 2.open_cursors 3.java_pool_size 4.large_pool_size

How to measure the performance of the following parameters. 1.processes, 2.open_cursors 3.java_pool_size 4.large_pool_size is there any quries to measure the performance of the parameters like cache hit ratio for db_cache_size parameter. How to know whether i have to increase the … Continuar lendo

Publicado em BUFFERS, PARAMETROS, PLSQL TUNING, SGA | Marcado com , | Deixe um comentário

ow to Automate Pinning Objects in Shared Pool at Database Startup

How to Automate Pinning Objects in the Shared Pool at Database Startup: ======================================================================= ******************************************************** 1/ Create the procedures to be executed by the triggers ******************************************************** a. Create a table to store the names of packages and procedures that had to … Continuar lendo

Publicado em BUFFERS, PLSQL TUNING, SGA | Deixe um comentário

ESTIMATE SHARED POOL UTILIZATION

Estimate Shared Pool Utilization Disclaimer: This script is provided for educational purposes only. It is NOT supported by Oracle World Wide Technical Support. The script has been tested and appears to work as intended. However, you should always test any … Continuar lendo

Publicado em BUFFERS, PLSQL TUNING, SGA | Deixe um comentário

How to Pin SQL Statements in Memory Using DBMS_SHARED_POOL

How to Pin SQL Statements in Memory Using DBMS_SHARED_POOL: =========================================================== Do the following: 1. Issue a SQL statement. For example: SQL>select * from scott.emp; 2. Get the SQL address from V$SQLAREA. SQL> select address, hash_value, sql_text from v$sqlarea where sql_text … Continuar lendo

Publicado em BUFFERS, PLSQL TUNING, SGA, SHARED POOL | Marcado com , , | Deixe um comentário

SGA TUNING INFORMATION

Description General guidelines and definitions are as follows: Data Block Cache – > .90 .85 Highly application dependent, increase the instance parameter SHARED_POOL_SIZE to increase hit ratio. – Avg. Users Per Stmt. – The average number of users who execute … Continuar lendo

Publicado em PLSQL SCRIPTS, SGA | Marcado com | Deixe um comentário

How To Use Automatic Shared Memory Management (ASMM) In Oracle10g

PURPOSE The main goal of this document is to show you how to put in place the ASMM facility introduced with Oracle10g, how to configure it, and also some examples on how to monitor its activity. SCOPE & APPLICATION All … Continuar lendo

Publicado em ASSM, PARAMETROS, PLSQL TUNING, SGA | Marcado com | Deixe um comentário

How to Identify The Segment Associated with Buffer Busy Waits

This note shows how to determine which segment is associated with buffer busy waits found in an AWR / statspack report, or 10046 trace. It is very important to know which segments are affected by buffer busy waits in order … Continuar lendo

Publicado em BUFFERS, WAIT EVENTS | Marcado com | Deixe um comentário

How to determine SGA Size (7.x, 8.x, 9.x, 10g)

How to Approximate the Size of the SGA in in 8.0.X, 8i, 9i, and 10g: =============================================================== This section discusses Oracle8, Oracle8i, Oracle9i, and Oracle 10g. Oracle7 is discussed at the end of this note. Showing size of the SGA ———————– … Continuar lendo

Publicado em SGA | Deixe um comentário

Dynamic SGA

Dynamic SGA: ============ Since the inception of Oracle, the System Global Area (SGA) has been static. In Oracle8i Database Administrators (DBAs) do not have any control over the SGA size once the instance is started. Oracle9i allows a DBA to … Continuar lendo

Publicado em PLSQL TUNING, SGA | Marcado com | Deixe um comentário

CRIANDO NOVA UNDO E EXCLUINDO ANTIGA

create undo tablespace UNDOTBS2 datafile ‘/u01/app/oracle/oradata/DESENV02/undotbs02.dbf’ size 100m; alter database datafile ‘/u01/app/oracle/oradata/DESENV02/undotbs02.dbf’ resize 1000m; select TABLESPACE_NAME, CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE, SEGMENT_SPACE_MANAGEMENT from dba_tablespaces where contents=’UNDO’; select TABLESPACE_NAME, CONTENTS, STATUS from dba_tablespaces where contents=’UNDO’; select owner,segment_name,tablespace_name, status from dba_rollback_segs order by 3; … Continuar lendo

Publicado em REDO / UNDO | Deixe um comentário

How to Find Sessions Generating Lots of Redo

To find sessions generating lots of redo, you can use either of the following methods. Both methods examine the amount of undo generated. When a transaction generates undo, it will automatically generate redo as well. The methods are: 1) Query … Continuar lendo

Publicado em PLSQL TUNING, REDO / UNDO | Marcado com , | Deixe um comentário

Toolkit for dynamic marking of Library Cache objects as Kept

Instructions Execution Environment: SQL*Plus, iSQL*Plus Access Privileges: Requires SYS Usage: sqlplus “/as sysdba” @PIND_install.sql Instructions: – copy&paste bellow appended script into file and run – make sure startup/shutdown triggers are (“_system_trig_enabled=TRUE” – enabled by default) – make sure the database … Continuar lendo

Publicado em BUFFERS, PLSQL TUNING | Marcado com | Deixe um comentário

RESOLVING INTENSE AND “RANDOM” BUFFER BUSY WAIT PERFORMANCE PROBLEMS

RESOLVING INTENSE AND “RANDOM” BUFFER BUSY WAIT PERFORMANCE PROBLEMS ——————————————————————– This document is composed of two sections; a summary section that broadly discusses the problem and its resolution, and a detailed diagnostics section that shows how to collect and analyze … Continuar lendo

Publicado em BUFFERS, WAIT EVENTS | Marcado com , , , | Deixe um comentário

BUFFER_POOL_KEEP

Reference Note for Init.Ora Parameter “BUFFER_POOL_KEEP”. The information here contains a parameter description from the Oracle9i documentation along with any additional support comments related to this parameter. If the parameter usage is different in Oracle9i to in earlier releases then … Continuar lendo

Publicado em BUFFERS | Marcado com | Deixe um comentário

Can We Tune Row Cache!

PURPOSE ——- To determine what options we have to tune Row Cache in Oracle7 through Oracle9i SCOPE & APPLICATION ——————- This article is intended for Oracle Support Analysts, Oracle Consultants and Database Administrators What is Row Cache? —————— Row Cache … Continuar lendo

Publicado em BUFFERS, STATSPACK | Marcado com , | Deixe um comentário

Monitoring the average value of used buffers per objec

monitoring the average value of used buffers per object hi, i created a view displaying the buffer cache on a per userobject basis. i’m planning to monitor this view over time and build up a time based metric. if there … Continuar lendo

Publicado em BUFFERS, PLSQL TUNING | Deixe um comentário

Multiple BUFFER subcaches

Multiple BUFFER subcaches: What is the Total BUFFER CACHE Size? =============================================================== How do we know that different buffer caches are initialized? ———————————————————— 1. V$BUFFER_POOL is the view that displays the entire buffer cache structure: SQL> select id,name,block_size, current_size,buffers from v$buffer_pool; … Continuar lendo

Publicado em BUFFERS, PERFORMANCE, PLSQL SCRIPTS | Deixe um comentário

recommendation for the Real Application Cluster Interconnect and Jumbo Frame

Purpose This note covers the current recommendation for the Real Application Cluster Interconnect and Jumbo Frames Scope and Application This article points out the issues surrounding Ethernet Jumbo Frame usage for the Oracle Real Application Cluster (RAC) Interconnect. In Oracle … Continuar lendo

Publicado em FAST DIAG, PLSQL SCRIPTS, RAC, RECOMENDAÇÕES | Marcado com , , , | Deixe um comentário