A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.
This Oracle clone procedure can be use to quickly migrate a system from one UNIX server to another. It clones the Oracle database and this Oracle cloning procedures is often the fastest way to copy a Oracle database.
STEP 1: On the old system, go into SQL*Plus, sign on as SYSDBA and issue: “alter database backup controlfile to trace”. This will put the create database syntax in the trace file directory. The trace keyword tells oracle to generate a script containing a create controlfile command and store it in the trace directory identified in the user_dump_dest parameter of the init.ora file. It will look something like this:
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 240
MAXINSTANCES 1
MAXLOGHISTORY 113
LOGFILE
GROUP 1 ('/u03/oradata/oldlsq/log1a.dbf',
'/u03/oradata/olslsq/log1b.dbf') SIZE 30M,
GROUP 2 ('/u04/oradata/oldlsq/log2a.dbf',
'/u04/oradata/oldlsq/log2b.dbf') SIZE 30M
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
;
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 2: Shutdown the old database
STEP 3: Copy all data files into the new directories on the new server. You may change the file names if you want, but you must edit the controlfile to reflect the new data files names on the new server.
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u01/oradata/oldlsq/* newhost:/u01/oradata/newlsq
rcp /u03/oradata/oldlsq/* newhost:/u03/oradata/newlsq
rcp /u04/oradata/oldlsq/* newhost:/u04/oradata/newlsq
STEP 4: Copy and Edit the Control file – Using the output syntax from STEP 1, modify the controlfile creation script by changing the following:
Old:
CREATE CONTROLFILE REUSE DATABASE "OLDLSQ" NORESETLOGS
New:
CREATE CONTROLFILE SET DATABASE "NEWLSQ" RESETLOGS
STEP 5: Remove the “recover database” and “alter database open” syntax
# Recovery is required if any of the datafiles are restored
# backups, or if the last shutdown was not normal or immediate.
RECOVER DATABASE
# Database can now be opened normally.
ALTER DATABASE OPEN;
STEP 6: Re-names of the data files names that have changed.
Save as db_create_controlfile.sql.
Old:
DATAFILE
'/u01/oradata/oldlsq/system01.dbf',
'/u01/oradata/oldlsq/mydatabase.dbf'
New:
DATAFILE
'/u01/oradata/newlsq/system01.dbf',
'/u01/oradata/newlsq/mydatabase.dbf'
STEP 7: Create the bdump, udump and cdump directories
cd $DBA/admin
mkdir newlsq
cd newlsq
mkdir bdump
mkdir udump
mkdir cdump
mkdir pfile
STEP 8: Copy-over the old init.ora file
rcp $DBA/admin/olslsq/pfile/*.ora newhost:/u01/oracle/admin/newlsq/pfile
STEP 9: Start the new database
@db_create_controlfile.sql
STEP 10: Place the new database in archivelog mode.
For any oracle technical issues please email me on masamimalik@yahoo.com so I can assist you.
Thursday, January 29, 2009
Wednesday, January 28, 2009
Oracle Instances: Vision Development Test Production
Let us discuss something on the instances which a consultant normally works on...
Instance is an address for a location where Applications database is stored.
We might have heard of many instances such as Vision, Test, Development,CRP I, CRP II, UAT, Production etc.... but apart from the name there is no change in the behaviour of any of the Instances.
All instances carry the same technical features and functional setups, no difference what so ever in any of them, but why we call it by different names then ?
The different instance names are given based on the purpose for which it is used. Every instance is created with the same features and specifications, only naming is done based on the usage or the purpose.
We will see the standard or common purposes for which different instances are used in
an implementation.
Test Instance
It is a fresh instance which is used by the Implementation team to try out new setups and work arounds as per the clients business specifications and arriving at final solutions. Apart from that test instance is used for testing the performance of installation as well as other factors. The life of this instance starts from the first day of kick off and ends when the production begins or system has gone live.
Development Instance
It is also a fresh instance when installed, but more or less it replicates the test or Production instance (normally cloned). The purpose is to make constant development in the current business process and the system performance and working out new methods in solving critical bottlenecks with the current system. The life of this instance is infinity, since there is no end to development, it is an ongoing process, it continues even after the system has gone live.
Production Instance
It is a fresh instance installed with an intention to share it with all the users of the client with fine tunning. This instance is different from Test and Development with respect to the number of users, size of the database, security features etc. The life of the production instance begins when the User Acceptance testing is done, and it continues till the company is in existence.
Vision Instance
It is different from all other instances because the installation comes with the data, whereas all other instances are Fresh. Even though there is no particular necessity to have this instance in an real time scenario, but it is better to have a vision instance always for the following reasons:
1) It serves as a practice for the DBA to find out the installation problems that occurs due to hardware failures.
2) Functional consultant often try out a small business scenarios to find out solutions, in order to try out these scenarios, they need to do all the mandatory setups in an instance then only they can perform it. If they had vision instance, they can use the existing setups to try out thier business scenarios
3) Vision instance also acts as a tutor to the Functional Consultants in case they are lost in performing critical setups.
Instance is an address for a location where Applications database is stored.
We might have heard of many instances such as Vision, Test, Development,CRP I, CRP II, UAT, Production etc.... but apart from the name there is no change in the behaviour of any of the Instances.
All instances carry the same technical features and functional setups, no difference what so ever in any of them, but why we call it by different names then ?
The different instance names are given based on the purpose for which it is used. Every instance is created with the same features and specifications, only naming is done based on the usage or the purpose.
We will see the standard or common purposes for which different instances are used in
an implementation.
Test Instance
It is a fresh instance which is used by the Implementation team to try out new setups and work arounds as per the clients business specifications and arriving at final solutions. Apart from that test instance is used for testing the performance of installation as well as other factors. The life of this instance starts from the first day of kick off and ends when the production begins or system has gone live.
Development Instance
It is also a fresh instance when installed, but more or less it replicates the test or Production instance (normally cloned). The purpose is to make constant development in the current business process and the system performance and working out new methods in solving critical bottlenecks with the current system. The life of this instance is infinity, since there is no end to development, it is an ongoing process, it continues even after the system has gone live.
Production Instance
It is a fresh instance installed with an intention to share it with all the users of the client with fine tunning. This instance is different from Test and Development with respect to the number of users, size of the database, security features etc. The life of the production instance begins when the User Acceptance testing is done, and it continues till the company is in existence.
Vision Instance
It is different from all other instances because the installation comes with the data, whereas all other instances are Fresh. Even though there is no particular necessity to have this instance in an real time scenario, but it is better to have a vision instance always for the following reasons:
1) It serves as a practice for the DBA to find out the installation problems that occurs due to hardware failures.
2) Functional consultant often try out a small business scenarios to find out solutions, in order to try out these scenarios, they need to do all the mandatory setups in an instance then only they can perform it. If they had vision instance, they can use the existing setups to try out thier business scenarios
3) Vision instance also acts as a tutor to the Functional Consultants in case they are lost in performing critical setups.
Oracle Implemenation Stages not taught in training
Today we will be seeing the different stages that are available in an implementation project. A Project is not a short term activity to be completed in matter of days, it requires intensive analysis, thoughtful thinking and serious decision making in core and critical business activities. There decisions taken at every stage is critical and irreversible, there is no going back after
performing final step. We have to understand how a long term project is broken up in to phases and completed smoothly. We will see what are the different stages in an Oracle Application Implementation project in real time. i have taken Application Implementation Methodology (AIM) standards as the base for further explanations.
Application Implementation Methodology (AIM) has laid out various stages available in a project.
Stage 1 - Definition of Project Management
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
The success to any project is a well thought of PROJECT PLAN at the very beginning considering the requirements and available resources. A Normal Contention of many people is that Project Plan should normally stage the Start Date and End Date and the Consideration, but in real time it is actually more than that, the whole project is broken in to different phases and for each phase a detailed plan is laid out which in turns account for the overall project plan. Project Plan must be revised whenever there is a Change in the contract or requirement of the client.
Documentation to be Prepared
============================
Project Plan (Finalized or Draft)
Write up about the Client and the Consultant.
Stage 2 - Operational Analysis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
========
This is the very very Critical stage of the Project. One must devote the maximum possible time to this stage. Any compromise on this stage may prove critical at later stage of the project. In this stage a Consultant, who is a total stranger to the client so far is going to make an effort to become an expert in understanding the client business every inch. Only when he masters the clients business, he will be in a position to think beyond it and provide solutions considering the impact it can have on overall business. In this stage a consultant must carryout following activities:
--> Clearly understand the contractual obligations made between the client and the consultant with respect to the work to be performed.
--> Detailed study on the Client business and Organisation Structure
--> Capture the current business process as they are following ( Also known as AS IS documentation)
--> Collect the requirements from the client, managers, end users as to what they expect from the Oracle Product
--> Requirements with respect to Functional Side as well as Technical Side is collected
--> Document the bottlenecks faced with the existing system in place
--> Most important point is that get the document signed from the client
( Note: Normally speaking once a requirement is expressed at this stage , it will be taken care by the consultant, however in real time, requirements gathering becomes a day to day activity in many clients place, which is highly unprofessional way of working)
Documentation to be Prepared
============================
RD 10 --> Current Financial and Operating Structure
RD 20 --> Current Business Baseline
RD 50 --> Business Requirements Scenario
Stage 3 - Solution Design
~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
After making serious understanding and analysis on clients current business practices and future requirements, consultant must begin the process of providing solutions to each and every aspect. If an straight forward solution is not possible , think about the possibility of workaround. Enough analysis has to be one in drafting the new business process of the client which is going to be followed here after, it is also known as To Be Process.Once the solution is decided on, the same must be communicated to the client seeking acceptance. when the client is satisfied with the solution, consultant can put an end to his research work on providing alternate solutions.
Documentation to be Prepared
============================
BP 80 --> Business Process Design ( To be process )
BR 10 --> High Level Gap Analysis
BR 30 --> Business Requirements Mapping
MD 50 --> Application Extension Functional Design
MD 70 --> Application Extension Technical Design
Stage 4 - Building Stage
~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
Only at this stage the work of performing setups and access to the instance begins. Based on the Solutions and new business process ( To Be) designed at the previous stage, the consultant starts performing setting up of the Application. In case of a functional requirement, it will be setups from the front end, in case of technical requirements, sql scripts, customizations will be performed. Document all the setups that has been carried out at this stage, since it will be useful when the production Instance is configured or when there is a system crash or corruption of data or any hardware failure. After performing all required setups, the system has to be tested for its readiness to carry out operations
Documentation to be Prepared
=============================
BR 100 --> Application Setup
TE 20 --> Unit Test Script
TE 40 --> System Test Script
TE 50 --> System Integration Test Script
TE 120 --> System Integration Test Plan
Stage 5 - Transition Stage
~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
In this stage, client is exposed to the Applications for the first time to get the comfort level. In order to make the client to get a feel of the system, user guides, training manuals and training may be given in understanding how thier business is completly taken care by the product. Once the client is exposed to the Applications, they normally tend to show some interest in learning
new things, which can be used for testing the system. Once the client is satisfied with the Solutions and the way in which the system performs, he can express his willings to accept the system. In real time, acceptance will be taken up at two levels,
First Level - CRP - Conferance Room Piloting - Where the consultant gives a preview of the new system to the selected members of the client and get thier feedback.
Second Level - UAT - User Acceptance Testing - Here the consultant gives detailed demonstration on the new system to the users ( Super and End ), and get the acceptance from the users that the new system meet thier expectations.
Documentation to be Prepared
======================
TE 130 - User Acceptance Test Report
Stage 6 - Production Stage
~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
Once CRP and UAT is approved, it is the duty of the consultant to replicate the same in a new environment which will be used solely and exclusively for the Production purposes only. After making the production server ready with all setups, it is advised to clone the production server to an development or test server, so that any value addition or future testing can be carried out
separately in future. Once the production server is ready, it is given to the client to begin thier operations, which is termed as GO LIVE . In order to assist the client in adopting to the new system, consultant will provide them with User Manuals and also a support consultant for a short period of time if needed.
Once the production server is ready and used, the implementation gets over, and the client should give it in writing that he is satisfied with the implementation taken place, which is termed as sign off ( A Big Good Bye ) !!
Documentation to be Prepared
============================
User Manuals
Client's sign off on the Acceptance Certificate
NOTE:
=====
Documentations suggested in this post is recommended when AIM is adopted. Instead of the recommended documents, users can prepare documents based on thier methodology.
Nothing stated above is mandatory, every aspect is recommendatory in nature.
**********************************************
GENERALISED
===========
The Implementation Phase in a Oracle implementation project as per Oracle AIM.
1.Definition
2.Operation Analysis
3.Solution Design
4.Build
5.Transition
6.Production Process Overviews - Tasks & Deliverable
[BP] Business Process Architecture
[RD] Business Requirements Definition
[BR] Business Requirements Mapping
[TA] Application and Technical Architecture
[MD] Module Design and Build
[CV] Data Conversion
[DO] Documentation
[TE] Business System Testing
[PT] Performance Testing
[AP] Adoption and Learning
[PM] Production Migration
There are several deliverable from Oracle to deliver at the time of Implementation, But to provide all those documents is not required for Sensible Project.
To my understanding required documents needed to be delivered are
RD - 20 -Requirement Gathering document
BP - 80 or 90 - Future Business Mapping Document
MD - 50 - Functional Specification for Application Customization.
MD - 70 - Technical Specification for Customization.
CV-40-CV-60 - Data Collection Template
UAT - Training Scripts TA - Training Documents
BR - 100 Application Setup Documents
Its better if you have the AIM document generator with you. So that you can have all the document Templates handy. To download the AIM oracle documentation software go to http://www.ziddu.com/download/1682474/AIMtoolaset.EXE.html
performing final step. We have to understand how a long term project is broken up in to phases and completed smoothly. We will see what are the different stages in an Oracle Application Implementation project in real time. i have taken Application Implementation Methodology (AIM) standards as the base for further explanations.
Application Implementation Methodology (AIM) has laid out various stages available in a project.
Stage 1 - Definition of Project Management
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
The success to any project is a well thought of PROJECT PLAN at the very beginning considering the requirements and available resources. A Normal Contention of many people is that Project Plan should normally stage the Start Date and End Date and the Consideration, but in real time it is actually more than that, the whole project is broken in to different phases and for each phase a detailed plan is laid out which in turns account for the overall project plan. Project Plan must be revised whenever there is a Change in the contract or requirement of the client.
Documentation to be Prepared
============================
Project Plan (Finalized or Draft)
Write up about the Client and the Consultant.
Stage 2 - Operational Analysis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
========
This is the very very Critical stage of the Project. One must devote the maximum possible time to this stage. Any compromise on this stage may prove critical at later stage of the project. In this stage a Consultant, who is a total stranger to the client so far is going to make an effort to become an expert in understanding the client business every inch. Only when he masters the clients business, he will be in a position to think beyond it and provide solutions considering the impact it can have on overall business. In this stage a consultant must carryout following activities:
--> Clearly understand the contractual obligations made between the client and the consultant with respect to the work to be performed.
--> Detailed study on the Client business and Organisation Structure
--> Capture the current business process as they are following ( Also known as AS IS documentation)
--> Collect the requirements from the client, managers, end users as to what they expect from the Oracle Product
--> Requirements with respect to Functional Side as well as Technical Side is collected
--> Document the bottlenecks faced with the existing system in place
--> Most important point is that get the document signed from the client
( Note: Normally speaking once a requirement is expressed at this stage , it will be taken care by the consultant, however in real time, requirements gathering becomes a day to day activity in many clients place, which is highly unprofessional way of working)
Documentation to be Prepared
============================
RD 10 --> Current Financial and Operating Structure
RD 20 --> Current Business Baseline
RD 50 --> Business Requirements Scenario
Stage 3 - Solution Design
~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
After making serious understanding and analysis on clients current business practices and future requirements, consultant must begin the process of providing solutions to each and every aspect. If an straight forward solution is not possible , think about the possibility of workaround. Enough analysis has to be one in drafting the new business process of the client which is going to be followed here after, it is also known as To Be Process.Once the solution is decided on, the same must be communicated to the client seeking acceptance. when the client is satisfied with the solution, consultant can put an end to his research work on providing alternate solutions.
Documentation to be Prepared
============================
BP 80 --> Business Process Design ( To be process )
BR 10 --> High Level Gap Analysis
BR 30 --> Business Requirements Mapping
MD 50 --> Application Extension Functional Design
MD 70 --> Application Extension Technical Design
Stage 4 - Building Stage
~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
Only at this stage the work of performing setups and access to the instance begins. Based on the Solutions and new business process ( To Be) designed at the previous stage, the consultant starts performing setting up of the Application. In case of a functional requirement, it will be setups from the front end, in case of technical requirements, sql scripts, customizations will be performed. Document all the setups that has been carried out at this stage, since it will be useful when the production Instance is configured or when there is a system crash or corruption of data or any hardware failure. After performing all required setups, the system has to be tested for its readiness to carry out operations
Documentation to be Prepared
=============================
BR 100 --> Application Setup
TE 20 --> Unit Test Script
TE 40 --> System Test Script
TE 50 --> System Integration Test Script
TE 120 --> System Integration Test Plan
Stage 5 - Transition Stage
~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
In this stage, client is exposed to the Applications for the first time to get the comfort level. In order to make the client to get a feel of the system, user guides, training manuals and training may be given in understanding how thier business is completly taken care by the product. Once the client is exposed to the Applications, they normally tend to show some interest in learning
new things, which can be used for testing the system. Once the client is satisfied with the Solutions and the way in which the system performs, he can express his willings to accept the system. In real time, acceptance will be taken up at two levels,
First Level - CRP - Conferance Room Piloting - Where the consultant gives a preview of the new system to the selected members of the client and get thier feedback.
Second Level - UAT - User Acceptance Testing - Here the consultant gives detailed demonstration on the new system to the users ( Super and End ), and get the acceptance from the users that the new system meet thier expectations.
Documentation to be Prepared
======================
TE 130 - User Acceptance Test Report
Stage 6 - Production Stage
~~~~~~~~~~~~~~~~~~~~~~~~~~
Description
===========
Once CRP and UAT is approved, it is the duty of the consultant to replicate the same in a new environment which will be used solely and exclusively for the Production purposes only. After making the production server ready with all setups, it is advised to clone the production server to an development or test server, so that any value addition or future testing can be carried out
separately in future. Once the production server is ready, it is given to the client to begin thier operations, which is termed as GO LIVE . In order to assist the client in adopting to the new system, consultant will provide them with User Manuals and also a support consultant for a short period of time if needed.
Once the production server is ready and used, the implementation gets over, and the client should give it in writing that he is satisfied with the implementation taken place, which is termed as sign off ( A Big Good Bye ) !!
Documentation to be Prepared
============================
User Manuals
Client's sign off on the Acceptance Certificate
NOTE:
=====
Documentations suggested in this post is recommended when AIM is adopted. Instead of the recommended documents, users can prepare documents based on thier methodology.
Nothing stated above is mandatory, every aspect is recommendatory in nature.
**********************************************
GENERALISED
===========
The Implementation Phase in a Oracle implementation project as per Oracle AIM.
1.Definition
2.Operation Analysis
3.Solution Design
4.Build
5.Transition
6.Production Process Overviews - Tasks & Deliverable
[BP] Business Process Architecture
[RD] Business Requirements Definition
[BR] Business Requirements Mapping
[TA] Application and Technical Architecture
[MD] Module Design and Build
[CV] Data Conversion
[DO] Documentation
[TE] Business System Testing
[PT] Performance Testing
[AP] Adoption and Learning
[PM] Production Migration
There are several deliverable from Oracle to deliver at the time of Implementation, But to provide all those documents is not required for Sensible Project.
To my understanding required documents needed to be delivered are
RD - 20 -Requirement Gathering document
BP - 80 or 90 - Future Business Mapping Document
MD - 50 - Functional Specification for Application Customization.
MD - 70 - Technical Specification for Customization.
CV-40-CV-60 - Data Collection Template
UAT - Training Scripts TA - Training Documents
BR - 100 Application Setup Documents
Its better if you have the AIM document generator with you. So that you can have all the document Templates handy. To download the AIM oracle documentation software go to http://www.ziddu.com/download/1682474/AIMtoolaset.EXE.html
Tuesday, January 27, 2009
Performance Monitoring Scripts
=================================================================
==>CONTENTION(SCRIPTS TO CHECK FOR CONTENTION AT VARIOUS LEVELS)
=================================================================
1. Find the objects that are currently locked
-----------------------------------------------------
Description: Anything appear in the result will be a locked object
select o.object_name,l.oracle_username,l.os_user_name,l.session_id
,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;
2. Monitor System wide wait event
------------------------------
Description: This script will display the waits on the instance since the instance has been created. Good place to look for system bottle necks
col event form A50
col Prev form 999
col Curr form 999
col Tot form 999
select
event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from
v$session_Wait
group by event
order by 4
3. Rollback Segment Contention
----------------------------
Description: Display the contention in rollback segments
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
=========================================
==>IO(IO RATE DISPLAY AND TUNING SCRIPTS)
=========================================
1. File I/O
------------
Description: Display I/O by File.
select
substr(a.file#,1,2) "#",
substr(a.name,1,30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
2.Monitor IO by Tablespace and Data File
-----------------------------------------
Description: Try to balance the I/O by mixing active and inactive data files / tablespaces on the same drive
column name format a18 heading 'Tablespace' jus cen
column file format a50 heading 'File Name' jus cen
column pbr format 99,999,999,990 heading 'Physical|Blocks|Read' jus cen
column pbw format 99,999,999,990 heading 'Physical |Blocks|Written' jus cen
column pyr format 99,999,999,990 heading 'Physical |Reads' jus cen
column pyw format 99,999,999,990 heading 'Physical|Writes' jus cen
ttitle center 'Disk I/O Activity by file' skip 2
compute sum of pyr on report
compute sum of pyw on report
compute sum of pbr on report
compute sum of pbw on report
break on report
select
df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where
f.file# = df.file_id
order by df.tablespace_name
3. Filestats Snapshot Script
-----------------------------
Description: This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and reports on the delta.
col name for a50
set linesize 132
set pages 666
-- drop temporary table
drop table jh$filestats;
create table jh$filestats as
select file#, PHYBLKRD, PHYBLKWRT
from v$filestat;
prompt Waiting......
exec dbms_lock.sleep(10);
prompt NOTE: Only the top 10 files...
select * from (
select df.name, fs.phyblkrd - t.phyblkrd "Reads",
fs.PHYBLKWRT - t.PHYBLKWRT "Writes",
(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO"
from v$filestat fs, v$datafile df, jh$filestats t
where df.file# = fs.file#
and t.file# = fs.file#
and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0
order by "Total IO" desc )
where rownum <= 10
/
=====================================================
==>INDEXING(Oracle Index Analysis and Tuning Scripts)
=====================================================
1.Find the Unindexed Tables and the Columns for Constraints in your database
----------------------------------------------------------------------------
Description: This script will display the table name and column name of the tables that need to be indexed.
COL table_name format A20 head 'TABLE_NAME'
COL constraint_name format A20 head 'CONSTRAINT_NAME'
COL table2 format A20 head 'TABLE_TO_BE_INDEXED'
COL column_name format A20 head 'COLUMN_TO_BE_INDEXED'
SET linesize 100
SELECT t.table_name,c.constraint_name,c.table_name table2
,acc.column_name
FROM all_constraints t, all_constraints c
, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name =acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( SELECT '1' FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
2. Table Index Report
---------------------
Description: Table to Index Cross Reference Report
ttitle left 'Date: ' format a10 cur_date -
center 'Table/Index Cross Reference for ' format a8 sql.user -
right 'Page:' format 999 sql.pno
set heading on
set pagesize 56
set linesize 100
set newpage 0
column uniqueness format a9 heading 'Unique?'
column index_name format a30 heading 'Index|Name'
column table_name format a24 heading 'Table|Name'
column column_name format a24 heading 'Column|Name'
column table_type format a8 heading 'Index|Type'
break on table_name skip 1 on table_type on index_name on uniqueness
select user_indexes.table_name, user_indexes.index_name,
uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position
;
clear breaks
clear columns
ttitle off
3. Foreign Keys without Indexes
-------------------------------
Description: This script lists foreign keys that are missing indexes on the foreign key columns in the child table. If the index is not in place, share lock problems may occur on the parent table.
ttitle 'Foreign Keys with Indexes Missing on Child Table'
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
||'['||acc.position||'])'||' ***** Missing Index' "Missing Index"
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
and acc.owner not in ('SYS','SYSTEM')
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
acc.column_name, acc.position;
ttitle off
===============================================================================
==>Memory Tuning (Scripts to help you tune SGA, Library Cache, Data Cache etc.)
===============================================================================
1. Buffer Cache Hit Ratio
--------------------------
Description - Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;
2. Calculate Dictionary Cache Ratio
-----------------------------------
Description: Increase Shared pool size to reach a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column
select
parameter,
gets,
Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;
3. Calculate Individual Cache Hit Ratios
----------------------------------------
Description: Reduce the Reloads and try to increase the hit ratios to above 85%
ttitle center 'LIBRARY CACHE STATS' skip 2
column namespace format a8 heading 'Library'
column gets format 9,999,990 heading 'GETS'
column gethits format 9,999,990 heading 'GETHITS'
column gethitratio format 99.90 heading 'GET|HIT|RATIO'
column pins format 999,999,990 heading 'PINS'
column pinhits format 999,999,990 heading 'PINHITS'
column pinhitratio format 99.90 heading 'PIN|HIT|RATIO'
column reloads format 999,990 heading 'RELOADS'
compute sum of gets on report
compute sum of gethits on report
compute sum of pins on report
compute sum of pinhits on report
compute sum of reloads on report
break on report
select
namespace,gets,gethits,gethitratio,pins,pinhits,
pinhitratio, reloads
from v$librarycache
where gets+gethits+pins+pinhits>0;
4. Calculate the Library Cache Ratio for the Whole system
---------------------------------------------------------
Description: This should be near 0. If the Ratio is larger than 1% then increase the SHARED_POOL_SIZE.
column libcache format 99.99 heading 'Percentage' jus cen
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache
5. Calculating the hit ratio for multiple pools
-----------------------------------------------
Description: To run this script you must get connect as user sys
select name,
1 - ( physical_reads / ( db_block_gets +
consistent_gets)) "HIT RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0
6. Check Free Shared pool and flush
-----------------------------------
Description: This script check the free space in the shared pool and when the size is small execute an alter system flush shared pool statement.
CREATE OR REPLACE PROCEDURE flush_it ( pct_full IN NUMBER) AS
--
-- Cursor definitions
--
CURSOR get_share IS
SELECT SUM(sharable_mem) FROM
sql_summary;
--
CURSOR get_var IS
SELECT value FROM v$sga WHERE name LIKE 'Var%';
--
CURSOR get_time IS
SELECT sysdate FROM dual;
--
-- Variable definitions
--
todays_date DATE;
mem_ratio NUMBER;
share_mem NUMBER;
variable_mem NUMBER;
cur INTEGER;
sql_com VARCHAR2(60);
row_proc NUMBER;
--
-- Procedure Body
--
BEGIN
OPEN get_share;
OPEN get_var;
FETCH get_share INTO share_mem;
dbms_output.put_line('share_mem: '||to_char(share_mem));
FETCH get_var INTO variable_mem;
dbms_output.put_line('variable_mem: '||to_char(variable_mem));
mem_ratio:=share_mem/variable_mem;
dbms_output.put_line(to_char(mem_ratio));
IF mem_ratio>(pct_full/100) THEN
cur:=dbms_sql.open_cursor;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
dbms_sql.parse(cur,sql_com,dbms_sql.v7);
row_proc:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
OPEN get_time;
FETCH get_time INTO todays_date;
INSERT INTO dba_running_stats VALUES
(
'Flush of Shared Pool',1,35,todays_date,0
);
COMMIT;
END IF;
END;
7. Check Session Level Hit Ratio
--------------------------------
Description: The Hit Ratio should be higher than 90%
select Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
from V$SESSION,V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and ( Consistent_Gets + Block_Gets )>0
and username is not null
order by Username,"Hit Ratio %";
8. Copying the data from one database to another
------------------------------------------------
- If you want to copy the data from one database to another having many tables and constraints,all you need is this script which will first disable all the constraints then delete the prior data and then finaly copy the data from any remote database and finaly it enables all the constraints on the table of that database.......all you need is to have a table having names of all the tables and the corresponding constraints
CREATE OR REPLACE procedure pre_tra
as
cursor cconstraints is select table_name, constraint_name, status from all_constraints where owner='owner name';
c2 cconstraints%rowtype;
mytab varchar2(200);
mytab1 varchar2(200);
mytab3 varchar2(200);
mytab4 varchar2(200);
retrycounter number;
counter number;
cstatus varchar2(20);
enableerrorfound boolean:=true;
--MAX_TRY number:=10000;
begin
begin
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab := c2.table_name;
mytab1 := c2.constraint_name;
mytab3 :='alter table ' || mytab || ' disable constraint ' || mytab1;
execute immediate mytab3;
end loop;
close cconstraints;
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
execute immediate 'delete ' || mytab;
end loop;
close cconstraints;
end;
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
mytab4 :='DATABASENAME';
execute immediate 'insert into ' || mytab || ' (select * from '|| mytab || '@' || mytab4 || ')';
DBMS_OUTPUT.PUT_LINE(mytab);
commit;
DBMS_OUTPUT.PUT_LINE(mytab);
end loop;
close cconstraints;
begin
DBMS_OUTPUT.PUT_LINE('Starting enable script.....');
RETRYCOUNTER := 3;
WHILE RETRYCOUNTER > 0
loop
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
DBMS_OUTPUT.PUT_LINE('retrycounter==>'||mytab);
mytab := c2.table_name;
mytab1 := c2.constraint_name;
--cstatus:= c2.status;
--if cstatus = 'DISABLED' then
mytab3 :='alter table ' || mytab || ' enable constraint ' || mytab1;
DBMS_OUTPUT.PUT_LINE('Q' || mytab3);
DBMS_OUTPUT.PUT_LINE(retrycounter);
--enableerrorfound:=false;
BEGIN
execute immediate mytab3;
EXCEPTION
WHEN OTHERS THEN
enableerrorfound:=true;
END;
--end if;
end loop;
RETRYCOUNTER := RETRYCOUNTER - 1;
DBMS_OUTPUT.PUT_LINE(retrycounter);
close cconstraints;
end loop;
DBMS_OUTPUT.PUT_LINE(retrycounter);
IF (enableerrorfound) THEN
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> TRUE');
else
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> FALSE');
end if;
end;
end pre_tra;
/
9. List Session Specific Memory
-------------------------------
Description: List the UGA and PGA used by each session on the server
column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'
compute sum of minmem on report
compute sum of maxmem on report
break on report
select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/
10. List the size of Stored Procedures and use it to tune Shared Pool
---------------------------------------------------------------------
Description: This script lists the size of stored objects
column num_instances heading "Num" format 999
column type heading "Object Type" format a12
column source_size heading "Source" format 99,999,999
column parsed_size heading "Parsed" format 99,999,999
column code_size heading "Code" format 99,999,999
column error_size heading "Errors" format 999,999
column size_required heading "Total" format 999,999,999
compute sum of size_required on report
select count(name) num_instances
,type
,sum(source_size) source_size
,sum(parsed_size) parsed_size
,sum(code_size) code_size
,sum(error_size) error_size
,sum(source_size)
+sum(parsed_size)
+sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type
order by 2
/
11. Redo Latch Contention Monitor
---------------------------------
Description: Try to reduce the contention by reducing all the ratios to be less than 1
SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'
COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
12. Sorts Monitoring Scripts
----------------------------
Description: Monitor the sorts in memory vs disk. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size
SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990
SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
13. Table Shared Mem Usage Report
---------------------------------
Description: Table Shared Memory Usage Report
set termout off feedback off echo off pagesize 59
set linesize 79 newpage 0 space 1 escape off
col iname noprint new_val instance
col today noprint new_val datetime
select name iname from v$database;
select to_char(sysdate, 'Mon DD, YYYY HH24:MI') today from dual;
btitle ce '======================================================' skip 1 -
le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno
ttitle center 'Database: ' instance ' - Table Usage Report' -
skip 1 col 62 datetime skip 2
set pagesize 60
col name format A28 heading "TABLE NAME"
set termout off feedback off echo off pagesize 59
set linesize 79 newpage 0 space 1 escape off
col iname noprint new_val instance
col today noprint new_val datetime
select name iname from v$database;
select to_char(sysdate, 'Mon DD, YYYY HH24:MI') today from dual;
btitle ce '======================================================' skip 1 -
le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno
ttitle center 'Database: ' instance ' - Table Usage Report' -
skip 1 col 62 datetime skip 2
set pagesize 60
col name format A28 heading "TABLE NAME"
col tablespace format A10
col KB format 99,999,999
col sharable_mem format 99,990 heading "SHARE MEM"
col loads format 9,990
col executions format 999,990
col own noprint
break on tablespace
spool table_usage.txt
select c.loads, c.executions, c.owner||'.'||c.name name,
t.tablespace_name tablespace, s.bytes/1024 KB, c.sharable_mem
from dba_tables t,
dba_segments s,
v$db_object_cache c
where c.type = 'TABLE'
and c.executions > 0
and c.name = t.table_name
and c.owner = t.owner
and c.name = s.segment_name
and c.owner = s.owner
order by 4, 1 desc, 2 desc, 3
/
spool off;
exit;
14. TO MEASURE THE BUFFERCACHE HIT RATIO FOR USER WISE
------------------------------------------------------
Description: When all application users are running in the server then we have to see the bufferhit ratio of all application users so that we can measure individuals performance.
select username, (1-(physical_reads/(consistent_gets+block_gets)))*100 "Buffer Cache Hit Ratio"
from v$session se, v$sess_io si
where se.sid = si.sid
and (consistent_gets+block_gets) > 0
and username is not null
order by username
/
15. Transaction Processing Rate
-------------------------------
Description: Computer Transaction Processing rate to get a better idea on sizing memory objects
SELECT SUM(s.value/
(86400*(SYSDATE - TO_DATE(i.VALUE,'J')))) "tps"
FROM V$SYSSTAT s, V$INSTANCE i
WHERE s.NAME in ('user commits','transaction rollbacks')
AND i.KEY = 'STARTUP TIME - JULIAN';
===============================================================================
==>Rollback (Rollback segment tuning and sizing scripts)
===============================================================================
1. Active Transactions in Rollback Segments
-------------------------------------------
Description: This script will display the active user and the rollback segment being used in the database
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal te
FROM
v$lock l,
v$session s,
v$rollname r
WHERE
l.sid = s.sid(+) AND
trunc(l.id1/65536) = r.usn AND
l.type = 'TX' AND
l.lmode = 6
ORDER BY r.name
/
2. Rollback Segment Wrap Time Monitoring Script
-----------------------------------------------
Description: This script reports how many hours it has been since the rollback segments wrapped
select n.name,
round(24*(sysdate-to_date(i1.value||' '||i2.value,'j SSSSS')) /
(s.writes/s.rssize),1) "Hours"
from v$instance i1,
v$instance i2,
v$rollname n,
v$rollstat s
where
i1.key = 'STARTUP TIME - JULIAN'
and i2.key = 'STARTUP TIME - SECONDS'
and n.usn = s.usn
and s.status = 'ONLINE'
3. Rollback Statistics
----------------------
Description: Gives lots of usefull easy to read info on how your RBS are performing. Needs 132 char display.
col name for a7
col xacts for 9990 head "Actv|Trans"
col InitExt for 990.00 head "Init|Ext|(Mb)"
col NextExt for 990.00 head "Next|Ext|(Mb)"
col MinExt for 99 head "Min|Ext"
col MaxExt for 999 head "Max|Ext"
col optsize for 9990.00 head "Optimal|Size|(Mb)"
col rssize for 9990.00 head "Curr|Size|(Mb)"
col hwmsize for 9990.00 head "High|Water|Mark|(Mb)"
col wraps for 999 head "W|R|A|P|S"
col extends for 990 head "E|X|T|E|N|D|S"
col shrinks for 990 head "S|H|R|I|N|K|S"
col aveshrink for 990.00 head "AVG|Shrink|(Mb)"
col gets head "Header|Gets"
col waits for 99990 head "Header|Waits"
col writes for 999,999,990 head "Total|Writes|Since|Startup|(Kb)"
col wpg for 9990 head "AVG|Writes|Per|HedGet|(bytes)"
set lines 132 pages 40 feed off
break on report
compute sum of gets on report
compute sum of waits on report
compute avg of aveshrink on report
compute avg of wpg on report
select name,
XACTS,
initial_extent/1048576 InitExt,
next_extent/1048576 NextExt,
min_extents MinExt,
max_extents MaxExt,
optsize/1048576 optsize,
RSSIZE/1048576 rssize,
HWMSIZE/1048576 hwmsize,
wraps,
extends,
shrinks,
aveshrink/1048576 aveshrink,
gets,
waits,
writes/1024 writes,
writes/gets wpg
from v$rollstat,v$rollname,dba_rollback_segs
where v$rollstat.usn=v$rollname.usn
and dba_rollback_segs.segment_id=v$rollname.usn
order by name
/
===========================================
==>SQL (SQL AND APPLICATION TUNING SCRIPTS)
===========================================
1. Who is currently running what SQL
------------------------------------
Description: This script will show you the user's OS name, Username in the database and the SQL Text they are running
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece
2. Auto-Changing SQL Prompt to the user and database connected
---------------------------------------------------------------
Description: When you connect to sqlplus, you see the the following sql prompt SQL>
By using this sql in the glogin.sql, you will see a prompt similar to the following: SAMI@RACDB1>
The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7).
You have to insert the following line of code in glogin.sql which is usually found in
$ORACLE_HOME/sqlplus/admin
set termout off
set echo off
define X=NotConnected
define Y=DBNAME
Column Usr New_Value X
Column DBName New_Value Y
Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;
--- The following does not work in 8.1.5 but works in 8.1.6 or above
---Select SYS_CONTEXT('USERENV','DB_NAME') DBNAME From Dual;
--- If you are using 8.1.5, use this .
Select Global_Name DBNAME from Global_Name;
set termout on
set sqlprompt '&X@&Y> '
Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is executed.
3.Capture application's sql in N tier evironment
------------------------------------------------
Description: In N tier structure it is really hard to track user session specially in a web enviroment, All users connect to database using the same application , so all requests are going to database on behalf of one oracle user. then how can you track actions of one ENDUSER (application user).
In the v$session, there are serval columns you can use to distinguish ENDUSER such as Module, Action and Client_info.
for example, you can use
dbms_application_info.set_client_info('client info')
to set client info accroding to user's ip or loginname in your application, then it is easy to find out what sql statement was execute by this client by using the following script
select s.sid, s.username, s.program, oc.sql_text
from v$session s, v$open_cursor oc
where s.saddr=oc.saddr and s.sid=oc.sid
and s.client_info='client_info';
4. SQL trace and TKPROF in SQLPLUS
-----------------------------------
Description: Purpose: To read raw session trace file with option to format trace file with TKPROF and read it in SQLPLUS Session
#!/bin/sh
#################################################################
#################################################################
## ScriptName:trace ##
## Purpose: To read raw session trace file with option to ##
## format trace file with TKPROF and read it in SQLPLUS Session##
## You must set TRACE_DIR and ORACLE_SID in ##
## this script Put trace in < $HOME/bin > make it executable ##
## and run it from sqlplus session ##
## Example: SQL> ! trace ##
## Note:You Must Run this script from your sqlplus session ##
#################################################################
#################################################################
clear
n1=`ps |grep sqlplus|awk '{print $1}' `
n2=`ps -ef|grep $n1|grep oracle"$ORACLE_SID"|awk '{print $2}'`
TRACE_DIR=/u01/app/oracle/admin/$ORACLE_SID/udump
echo " 1.Do you want to view raw trace file"
echo " 2.Do you want to format trace file and view it"
read choice
if [ "$choice" = "1" ];
then cat $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc|more
else
if [ "$choice" = "2" ];
then
tkprof $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc $TRACE_DIR/trace_session_$n2
clear
echo "Your TKPROF formated file name is $TRACE_DIR/trace_session_$n2.prf "
echo " Do you want to read it now y/n "
read ans
if [ "$ans" = "y" ];
then
cat $TRACE_DIR/trace_session_$n2.prf|more
else
if [ "$ans" = "n" ];
then
echo "Happy Hunting Perfomance Problems"
fi
fi
fi
fi
=============================================
==>OTHER SCRIPTS IN THIS PERF/TUNING CATEGORY
=============================================
1. Find Full Table Scans
------------------------
Description: The output of this script will display all sql statements in the shared pool that are doing full table scans.
--run this as sys in SQL worksheet
create table full_sql (sql_text varchar2(1000), executions number);
create or replace procedure p_findfullsql as
v_csr number;
v_rc number;
v_string varchar2(2000);
v_count number;
cursor c1 is select sql_text,executions from v$sqlarea where lower(sql_text) like '%select%';
begin
for x1 in c1 loop
delete from plan_table ;
Begin
v_Csr := DBMS_SQL.OPEN_CURSOR;
v_string := 'explain plan for ' ;
v_string := v_string||x1.sql_text ;
DBMS_SQL.PARSE(v_csr, v_string, DBMS_SQL.V7);
v_rc := DBMS_SQL.EXECUTE(v_csr);
DBMS_SQL.CLOSE_CURSOR(v_csr);
Exception
when others then
null;
End ;
select count(*) into v_count from plan_table where options like '%FULL%' and operation like '%TABLE%' ;
if v_count > 0 then
insert into full_sql(sql_text,executions) values (x1.sql_text, x1.executions) ;
end if;
end loop ;
commit;
end ;
/
execute p_findfullsql ;
select * from full_sql;
drop table full_sql;
2. Identify top 20 Oracle Processes in Unix
-------------------------------------------
Description: Find the top 20 longest running processes in unix. Useful for high CPU bound systems with large number of users. Script also identifies processes without a oracle session.
#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / "
$SQLPLUS >> $wlogfile < set pages 0 lines 80 trims on echo off verify off pau off
column pu format a8 heading 'O/S|ID' justify left
column su format a11 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Session|ID' justify right
column sser format 999999 heading 'Serial|No' justify right
column spid format 999999 heading 'UNIX|ID' justify right
column ltime format a11 heading 'Login|Time' justify right
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time, 'MMDD:HH24MISS') ltime,
'$SIDNAME $CPUTIME'
from v\$process p,
v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID
union all
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#,
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME'
from v\$process a
where a.spid = $UNIXPID
and not exists (select 1 from v\$session s
where a.addr=s.paddr);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date
#
# End of Script
3. Monitor and Verify Deadlocks and Waiting Sessions
----------------------------------------------------
Description: Monitor and Verify DEAD Locks: Holding and Waiting Sessions
Script: waiters.sql
set lines 80 echo on ver off timing on term on pages 60 feed on head on
spool DEAD_LOCK_WAITERS.LST
col " " for A25
col "Holding Session Info" for A25
col "Waiting Session Info" for A25
select --+ ORDERED
'Session ID' || CHR(10) ||
'Mode Held' || CHR(10) ||
'Lock Type' || CHR(10) ||
'Mode Requested' || CHR(10) ||
'Lock ID 1' || CHR(10) ||
'Lock ID 2' " "
-------------------------------- END of Header
,
HH.session_id || CHR(10) ||
HH.mode_held || CHR(10) ||
HH.lock_type || CHR(10) ||
HH.mode_requested || CHR(10) ||
HH.lock_id1 || CHR(10) ||
HH.lock_id2 "Holding Session Info"
------------------------------ END of Holding Session
,
Ww.session_id || CHR(10) ||
WW.mode_held || CHR(10) ||
Ww.lock_type || CHR(10) ||
Ww.mode_requested || CHR(10) ||
Ww.lock_id1 || CHR(10) ||
Ww.lock_id2 "Waiting Session Info"
------------------------------ END of Waiting Session
from
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where blocking_others = 'Blocking' and
mode_held != 'None' and
mode_held != 'Null'
) HH,
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where mode_requested != 'None'
) WW
-----------------------------------------------------
where WW.lock_type = HH.lock_type and
WW.lock_id1 = HH.lock_id1 and
WW.lock_id2 = HH.lock_id2
;
spool off
4. SGA Sizing
-------------
Description: An interesting script to size the SGA
set serverout on
DECLARE
l_uplift CONSTANT NUMBER := 0.3;
/* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 50;
/* Change ths to a predicted number existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;
BEGIN
dbms_output.enable(20000);
IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;
SELECT
avg(value)*l_numusers
,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';
SELECT sum(sharable_mem) INTO l_sum_sql_shmem FROM v$sqlarea;
SELECT sum(sharable_mem) INTO l_sum_obj_shmem FROM v$db_object_cache;
l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) ) ||' and '
|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) ) ||' bytes');
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND((l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||' and '|| TO_CHAR(ROUND((l_total_max + (l_total_max * l_uplift )) /1024*1024) ,0) ) ||' M bytes');
end;
/
5.DB Block Efficiency Script
----------------------------
Description: This script uses the new way of calculating DB_Block Buffer Efficiency.
Logical Read: consistent gets + db block gets
Hit-Ratio: (logical reads - physical reads) / (logical reads)
Our Aim: OLTP >= 95%, DSS/Batch >= 85%
Solution: enlarge block buffers, tune SQL, check appropriateness of indexes
select name, ((consistent_gets + db_block_gets) - physical_reads)/(consistent_gets + db_block_gets) * 100 "Hit Ratio%"
from v$buffer_pool_statistics
where physical_reads > 0;
6. EXPLAIN and get some statistics on your SQL
----------------------------------------------
Description: Haven't you ever thought there should be an easier way to do the EXPLAIN PLAN and TKPROF statistics than to edit your
queries to add the commands (like EXPLAIN PLAN SET...), or to have to find or write a script that automates this? It should
be an automatic part of SQL*Plus. Well, as of SQL*Plus 3.3 it is!! The command is called 'SET AUTOTRACE ON'!
The SET AUTOTRACE Command
sql>SET AUTOTRACE ON
And then run your select statement. Example:
SQL> SET AUTOTRACE ON
SQL> SELECT d.deptno, d.dname, e.empno, e.ename FROM dept d, emp e WHERE d.deptno = e.deptno;
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 KING
.
.
30 SALES 7900 JAMES
30 SALES 7521 WARD
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
670 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
There are also some other options, for example there is a TRACEONLY option which supresses the SQL output.
Some setup issues:
If you go off and try this on your instance, you may run into some problems. There are a few setup steps that need to be taken to make this work:
-Make sure you have access to PLAN_TABLE. If you don't, create it using utlxplan.sql (It should be in a directory like $ORACLE_HOME/rdbms73/admin/) and make sure you have access to it from the user you are using to tune the SQL.
-You also need to create the PLUSTRACE role, and grant it to the desired users. The script to create this role is in:
$ORACLE_HOME/plus33/Plustrce.sql
It has to be run from SYS in order to have the correct security access. Then grant the role to the desired users or ROLEs.
7. See How Far Sorts Have Progressed
------------------------------------
Description: This script will display the estimated work and time remaining for long operations (i.e. over 10 minutes).
select sid,
message || '(' || time_remaining || ')' "Long Ops"
from v$session_longops
where time_remaining > 600;
8. Types of Contention a Database Instance is (or is not) Experiencing
----------------------------------------------------------------------
Description: This is possibly one of the most useful tuning scripts available. The V$SYSTEM_EVENT dynamic performance view is the highest-level view of the "Session Wait Interface". Information in this view is cumulative since the database instance was started, and one can get a very good idea of what types of contention a database instance is (or is not) experiencing by monitoring this view.
/**********************************************************************
* File: systemevent.sql
* Type: SQL*Plus script
* Description:
* This is possibly one of the most useful tuning scripts available.
*
* The V$SYSTEM_EVENT dynamic performance view is the highest-level
* view of the "Session Wait Interface". Information in this view
* is cumulative since the database instance was started, and one
* can get a very good idea of what types of contention a database
* instance is (or is not) experiencing by monitoring this view.
*
* NOTE: this script sorts output by the TIME_WAITED column in the
* V$SYSTEM_EVENT view. If the Oracle initialization parameter
* TIMED_STATISTICS is not set to TRUE, then the TIME_WAITED
* column will not be populated.
*
* Please *disregard* the advice of people who insist that turning
* off TIMED_STATISTICS is somehow a performance boost. Whatever
* performance overhead that might be incurred is more than
* compensated for by the incredible tuning information that
* results...
*
*********************************************************************/
set echo off feedback off timing off pause off verify off
set pagesize 100 linesize 500 trimspool on trimout on
col event format a26 truncate heading "Event Name"
col total_waits format 999,990.00 heading "Total|Waits|(in 1000s)"
col total_timeouts format 999,990.00 heading "Total|Timeouts|(in 1000s)"
col time_waited format 999,990.00 heading "Time|Waited|(in Hours)"
col pct_significant format 90.00 heading "% of|Concern"
col average_wait format 990.00 heading "Avg|Wait|(Secs)"
col instance new_value V_INSTANCE noprint
select lower(replace(t.instance,chr(0),
)) instance
from sys.v_$thread t,
sys.v_$parameter p
where p.name = 'thread'
and t.thread# = to_number(decode(p.value,'0','1',p.value));
col total_time_waited new_value V_TOTAL_TIME_WAITED noprint
select sum(time_waited) total_time_waited
from sys.v_$system_event
where event not in ('SQL*Net message from client',
'rdbms ipc message',
'slave wait',
'pmon timer',
'smon timer',
'rdbms ipc reply',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'inactive session',
'Null event')
/
select event,
(total_waits / 1000) total_waits,
(total_timeouts / 1000) total_timeouts,
(time_waited / 360000) time_waited,
decode(event,
'SQL*Net message from client', 0,
'rdbms ipc message', 0,
'slave wait', 0,
'pmon timer', 0,
'smon timer', 0,
'rdbms ipc reply', 0,
'SQL*Net message to client', 0,
'SQL*Net break/reset to client', 0,
'inactive session', 0,
'Null event', 0,
(time_waited / &&V_TOTAL_TIME_WAITED)*100) pct_significant,
(average_wait / 100) average_wait
from sys.v_$system_event
where (time_waited/360000) >= 0.01
order by pct_significant desc, time_waited desc
spool sysevent_&&V_INSTANCE
/
spool off
9. Wait Sessions
----------------
Description: The Script lists the Session ID,Event Name,Time Waited on the Database. This Script is useful in identifying the resource intensive Queries. Can Be used on oracle 8i & 9i Databases.
select se.sid,s.username,Se.Event,se.time_waited
from v$session s, v$session_event se
where s.username is not null
and s.username not in ('SYS','SYSTEM')
and se.sid=s.sid
and s.status='ACTIVE'
and se.event not like '%SQL*Net%';
10. Health Check
---------------
Description: Look at the statistics of your Database
REM Database Health monitoring script.
REM
REM Segments Max extents & Current extent comparison
REM
set line 180
set pagesize 10000
set feedback off
prompt
col Time format a50 heading "System Time"
select to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') Time from dual;
prompt
prompt
prompt
prompt**---------------Objects Reaching Max extents-----------------------**
prompt
col segment_name format a40 heading "Object Name"
col max_extents format 9999999999 heading "Max Extents"
col curr_extents format 99999999999 heading "Curent Extents"
select a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select
segment_name,max(extent_id) curr_extents from dba_extents group by segment_name) b where a.segment_name = b.segment_name
and (a.max_extents - b.curr_extents) <= 10;
prompt
prompt**---------------User Session Information----------------------------**
prompt
col sid format 9999 heading "SID"
col serial# format 9999999 heading "SERIAL NO"
col logon_time format 9999999 heading "Login Time"
col username format a12
col osuser format a24
col program format a38
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,to_char(s.LOGON_TIME,'DD-MON-YY:HH24:MI:SS') "Log on Time",round((s.LAST_CALL_ET/(60*60*24)),2)"Wait in Days" from v$session s,v$process p where s.paddr = p.addr and s.username is not null order by 8 desc;
prompt
prompt**---------------File Information-------------------------------------**
prompt
col file_name format a55 heading "File Name"
col SizeInMB format 99999999 heading "Total Size (MB)"
col MAXSIZE format 99999999 heading "Maximum Size (MB)"
select file_name,BYTES/(1024*1024) SizeInMB,AUTOEXTENSIBLE,MAXBYTES/(1024*1024) MAXSIZE from dba_data_files;
prompt
prompt**---------------Tablespace Information-------------------------------**
prompt
col tablespace_name format a25 heading "Tablespace Name"
col logging format a10
col status format a12
col extent_management format a30 heading "Local/Dict"
select tablespace_name,status,contents,decode(logging,LOGGING,'YES','NO') Logging,extent_management from dba_tablespaces;
SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name;
prompt
prompt**---------------Tablespace Fragmentation Status----------------**
prompt
col TABLESPACE_NAME format a25 heading "Tablespace Name"
select TABLESPACE_NAME Name,TOTAL_EXTENTS "Total Extents",EXTENTS_COALESCED,decode(PERCENT_EXTENTS_COALESCED,100,'NO','YES') "Frag" from
dba_free_space_coalesced;
prompt
prompt**---------------Latch Contention-------------------------------**
prompt
col name format a40 heading "Latch Name"
SELECT name, gets, misses,
round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
sleeps,
round(sleeps/decode(misses,0,1,misses),3) "sleeps/misses"
from v$latch
where gets != 0
order by name;
select name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_misses) Hit_Ratio
from v$latch where immediate_gets != 0;
prompt
prompt**---------------Shared Pool Statistics-------------------------------**
prompt
col namespace format a22
select namespace,gets,gethits,round(gethitratio,2) gethitratio,pins,pinhits,round(pinhitratio,2)
pinhitratio,reloads,invalidations from v$librarycache;
prompt
prompt**---------------Roll back segment Statistics-------------------------------**
prompt
col segment_name format a30 heading "Segment Name"
col status format a15
select segment_name,a.status,initial_extent/(1024) "Initial KB",next_extent/(1024)"NextKB",max_extents,min_extents,optsize/102
"Opt Size KB",curext "Current Extents" from dba_rollback_segs a,v$rollstat b where a.segment_id = b.usn;
prompt
prompt**---------------Top 20 Events and System Statistics-------------------------------**
prompt
col event format a40 heading "Event Name"
col Stat format a40 heading "Stat Name"
select * from ( select name "Stat",value from v$sysstat order by value desc ) where rownum <= 20 ;
select * from ( select event,total_waits from v$system_event order by total_waits desc ) where rownum <=
20;
prompt
prompt**---------------Buffer Cache statistics-------------------------------**
prompt
select (1-(a.value/(b.value+c.value))) *100 "Buffer Cache Hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.name
= 'physical reads' and b.name = 'consistent gets' and c.name = 'db block gets';
col name format a20 heading "Buffer Pool Name"
select name,free_buffer_wait,write_complete_wait,buffer_busy_wait,db_block_gets,consistent_gets,physical_reads,physical_writes
from v$buffer_pool_statistics;
prompt
prompt
prompt
prompt**---------------File I/O statistics-------------------------------**
prompt
col file# format 99 heading "File No"
select file#,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",READTIM "Read Time",WRITETIM "Write Time",AVGIOTIM "Avg Time" from v$filestat;
set feedback on
10.Handy oracle freespace scripts
--------------------------------------
There are several views in the data dictionary that display database free space. We have to start by defining free space:
Is "free space" the empty space in the tablespace?
Is "free space the available space on the data files and filesystems?
There are several salient DBA views for displaying free space in Oracle:
dba_free_space
dba_data_files
dba_tablespaces
Here is a simple script to display Oracle free space within the data file space. Note the in-line view where dba_data_files is joined into dba_free_space:
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;
This shows the allocated and free bytes within the data files, but it DOES not show available free space on the OS filesystem:
TABLESPACE_NAME FILE_NAME ALLOCATED_BYTES FREE_BYTES
-------------------- ------------------------- --------------- --------------
TBS_LOCALS /u01/app/oradata/devdb/de 20,971,520 20,774,912
vdb/devdb_tbs_locals_01.d
bf
Note that there are many other ways to create tablespace reports. This method create an intermediate view to doisplay free space:
Rem free_space.sql
rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;
CREATE VIEW SYS.FREE_SPACE AS
SELECT
TABLESPACE_NAME TABLESPACE,
FILE_ID,
COUNT(*) PIECES,
SUM(BYTES) FREE_BYTES,
SUM(BLOCKS) FREE_BLOCKS,
MAX(BYTES) LARGEST_BYTES,
MAX(BLOCKS) LARGEST_BLKS
FROM
SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
This is the next script:
rem tsfree.sql - Shows all free space within tablespaces.
Prompt be sure that you have run free_space.sql prior to this script
clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;
column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
COLUMN PIECES FORMAT 9,999 HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE FORMAT 999 HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE FORMAT 999 HEADING 'Percent|FREE|Contiguous';
ttitle left datevar right sql.pno -
center ' Instance Data File Storage' SKIP 1 -
center ' in ORACLE Megabytes (1048576 bytes)' -
skip skip;
BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT
select to_char(sysdate,'mm/dd/yy') today,
TABLESPACE_NAME,
PIECES,
(D.BYTES/1048576) FILE_MBYTES,
(F.FREE_BYTES/1048576) FREE_MBYTES,
((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
(F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
D.FILE_ID= F.FILE_ID AND
D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;
Here is the report from this script.
Tablespace Pieces Mbytes Mbytes FREE Mbytes Contiguous
--------------- ---------- ---------- ------- ------- ---------- ----------
MASTER1_DETAILS 1 18 2 10 2 10
MASTER1_DETAILS 1 20 20 100 20 100
MASTER2_DETAILS 1 2 1 65 1 65
MASTER3_DETAILS 1 5 5 95 5 95
MASTER4_DETAILS 2 3 1 36 1 35
RBS_ONE 11 490 380 78 280 57
RBS_TWO 11 490 379 77 279 57
SYSTEM 17 60 45 76 45 75
TEMP 1 650 650 100 650 100
TOOLS 2 15 9 61 8 55
USERS 41 100 31 31 4 4
----------
13,255
This report is useful for finding the largest sized chunk of free space within a tablespace
==>CONTENTION(SCRIPTS TO CHECK FOR CONTENTION AT VARIOUS LEVELS)
=================================================================
1. Find the objects that are currently locked
-----------------------------------------------------
Description: Anything appear in the result will be a locked object
select o.object_name,l.oracle_username,l.os_user_name,l.session_id
,decode(l.locked_mode,2,'Row-S',3,'Row-X',4,'Share',5,'S/Row-X',6 ,'Exclusive','NULL')
from user_objects o , v$locked_object l
where o.object_id = l.object_id;
2. Monitor System wide wait event
------------------------------
Description: This script will display the waits on the instance since the instance has been created. Good place to look for system bottle necks
col event form A50
col Prev form 999
col Curr form 999
col Tot form 999
select
event,
sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",
count(*) "Tot"
from
v$session_Wait
group by event
order by 4
3. Rollback Segment Contention
----------------------------
Description: Display the contention in rollback segments
column "Ratio" format 99.99999
select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;
=========================================
==>IO(IO RATE DISPLAY AND TUNING SCRIPTS)
=========================================
1. File I/O
------------
Description: Display I/O by File.
select
substr(a.file#,1,2) "#",
substr(a.name,1,30) "Name",
a.status,
a.bytes,
b.phyrds,
b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;
2.Monitor IO by Tablespace and Data File
-----------------------------------------
Description: Try to balance the I/O by mixing active and inactive data files / tablespaces on the same drive
column name format a18 heading 'Tablespace' jus cen
column file format a50 heading 'File Name' jus cen
column pbr format 99,999,999,990 heading 'Physical|Blocks|Read' jus cen
column pbw format 99,999,999,990 heading 'Physical |Blocks|Written' jus cen
column pyr format 99,999,999,990 heading 'Physical |Reads' jus cen
column pyw format 99,999,999,990 heading 'Physical|Writes' jus cen
ttitle center 'Disk I/O Activity by file' skip 2
compute sum of pyr on report
compute sum of pyw on report
compute sum of pbr on report
compute sum of pbw on report
break on report
select
df.tablespace_name name,
df.file_name "file",
f.phyrds pyr,
f.phyblkrd pbr,
f.phywrts pyw,
f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where
f.file# = df.file_id
order by df.tablespace_name
3. Filestats Snapshot Script
-----------------------------
Description: This script takes a snapshot of v$filestats at the current time and saves it. It then waits 10 seconds and takes another snapshot and reports on the delta.
col name for a50
set linesize 132
set pages 666
-- drop temporary table
drop table jh$filestats;
create table jh$filestats as
select file#, PHYBLKRD, PHYBLKWRT
from v$filestat;
prompt Waiting......
exec dbms_lock.sleep(10);
prompt NOTE: Only the top 10 files...
select * from (
select df.name, fs.phyblkrd - t.phyblkrd "Reads",
fs.PHYBLKWRT - t.PHYBLKWRT "Writes",
(fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) "Total IO"
from v$filestat fs, v$datafile df, jh$filestats t
where df.file# = fs.file#
and t.file# = fs.file#
and (fs.PHYBLKRD+fs.PHYBLKWRT) - (t.PHYBLKRD+t.PHYBLKWRT) > 0
order by "Total IO" desc )
where rownum <= 10
/
=====================================================
==>INDEXING(Oracle Index Analysis and Tuning Scripts)
=====================================================
1.Find the Unindexed Tables and the Columns for Constraints in your database
----------------------------------------------------------------------------
Description: This script will display the table name and column name of the tables that need to be indexed.
COL table_name format A20 head 'TABLE_NAME'
COL constraint_name format A20 head 'CONSTRAINT_NAME'
COL table2 format A20 head 'TABLE_TO_BE_INDEXED'
COL column_name format A20 head 'COLUMN_TO_BE_INDEXED'
SET linesize 100
SELECT t.table_name,c.constraint_name,c.table_name table2
,acc.column_name
FROM all_constraints t, all_constraints c
, all_cons_columns acc
WHERE c.r_constraint_name = t.constraint_name
AND c.table_name =acc.table_name
AND c.constraint_name = acc.constraint_name
AND NOT EXISTS ( SELECT '1' FROM all_ind_columns aid
WHERE aid.table_name = acc.table_name
AND aid.column_name = acc.column_name)
ORDER BY c.table_name;
2. Table Index Report
---------------------
Description: Table to Index Cross Reference Report
ttitle left 'Date: ' format a10 cur_date -
center 'Table/Index Cross Reference for ' format a8 sql.user -
right 'Page:' format 999 sql.pno
set heading on
set pagesize 56
set linesize 100
set newpage 0
column uniqueness format a9 heading 'Unique?'
column index_name format a30 heading 'Index|Name'
column table_name format a24 heading 'Table|Name'
column column_name format a24 heading 'Column|Name'
column table_type format a8 heading 'Index|Type'
break on table_name skip 1 on table_type on index_name on uniqueness
select user_indexes.table_name, user_indexes.index_name,
uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position
;
clear breaks
clear columns
ttitle off
3. Foreign Keys without Indexes
-------------------------------
Description: This script lists foreign keys that are missing indexes on the foreign key columns in the child table. If the index is not in place, share lock problems may occur on the parent table.
ttitle 'Foreign Keys with Indexes Missing on Child Table'
SELECT acc.owner||'-> '||acc.constraint_name||'('||acc.column_name
||'['||acc.position||'])'||' ***** Missing Index' "Missing Index"
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
and acc.owner not in ('SYS','SYSTEM')
AND (acc.owner, acc.table_name, acc.column_name, acc.position)
IN
(SELECT acc.owner, acc.table_name, acc.column_name, acc.position
FROM all_cons_columns acc, all_constraints ac
WHERE ac.constraint_name = acc.constraint_name
AND ac.constraint_type = 'R'
MINUS
SELECT table_owner, table_name, column_name, column_position
FROM all_ind_columns)
ORDER BY acc.owner, acc.constraint_name,
acc.column_name, acc.position;
ttitle off
===============================================================================
==>Memory Tuning (Scripts to help you tune SGA, Library Cache, Data Cache etc.)
===============================================================================
1. Buffer Cache Hit Ratio
--------------------------
Description - Calculate buffer cache hit ratio in the database. Make sure it is more than 80 for an oltp environment and 99 is the best value
column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;
2. Calculate Dictionary Cache Ratio
-----------------------------------
Description: Increase Shared pool size to reach a 90% hit ratio on Dictionary Cache. Entries for dc_table_grants, d_user_grants, and dc_users should be under 5% each in the MISS RATE % column
select
parameter,
gets,
Getmisses ,
getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses ;
3. Calculate Individual Cache Hit Ratios
----------------------------------------
Description: Reduce the Reloads and try to increase the hit ratios to above 85%
ttitle center 'LIBRARY CACHE STATS' skip 2
column namespace format a8 heading 'Library'
column gets format 9,999,990 heading 'GETS'
column gethits format 9,999,990 heading 'GETHITS'
column gethitratio format 99.90 heading 'GET|HIT|RATIO'
column pins format 999,999,990 heading 'PINS'
column pinhits format 999,999,990 heading 'PINHITS'
column pinhitratio format 99.90 heading 'PIN|HIT|RATIO'
column reloads format 999,990 heading 'RELOADS'
compute sum of gets on report
compute sum of gethits on report
compute sum of pins on report
compute sum of pinhits on report
compute sum of reloads on report
break on report
select
namespace,gets,gethits,gethitratio,pins,pinhits,
pinhitratio, reloads
from v$librarycache
where gets+gethits+pins+pinhits>0;
4. Calculate the Library Cache Ratio for the Whole system
---------------------------------------------------------
Description: This should be near 0. If the Ratio is larger than 1% then increase the SHARED_POOL_SIZE.
column libcache format 99.99 heading 'Percentage' jus cen
select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache
5. Calculating the hit ratio for multiple pools
-----------------------------------------------
Description: To run this script you must get connect as user sys
select name,
1 - ( physical_reads / ( db_block_gets +
consistent_gets)) "HIT RATIO"
from sys.v$buffer_pool_statistics
where db_block_gets + consistent_gets > 0
6. Check Free Shared pool and flush
-----------------------------------
Description: This script check the free space in the shared pool and when the size is small execute an alter system flush shared pool statement.
CREATE OR REPLACE PROCEDURE flush_it ( pct_full IN NUMBER) AS
--
-- Cursor definitions
--
CURSOR get_share IS
SELECT SUM(sharable_mem) FROM
sql_summary;
--
CURSOR get_var IS
SELECT value FROM v$sga WHERE name LIKE 'Var%';
--
CURSOR get_time IS
SELECT sysdate FROM dual;
--
-- Variable definitions
--
todays_date DATE;
mem_ratio NUMBER;
share_mem NUMBER;
variable_mem NUMBER;
cur INTEGER;
sql_com VARCHAR2(60);
row_proc NUMBER;
--
-- Procedure Body
--
BEGIN
OPEN get_share;
OPEN get_var;
FETCH get_share INTO share_mem;
dbms_output.put_line('share_mem: '||to_char(share_mem));
FETCH get_var INTO variable_mem;
dbms_output.put_line('variable_mem: '||to_char(variable_mem));
mem_ratio:=share_mem/variable_mem;
dbms_output.put_line(to_char(mem_ratio));
IF mem_ratio>(pct_full/100) THEN
cur:=dbms_sql.open_cursor;
sql_com:='ALTER SYSTEM FLUSH SHARED_POOL';
dbms_sql.parse(cur,sql_com,dbms_sql.v7);
row_proc:=dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
OPEN get_time;
FETCH get_time INTO todays_date;
INSERT INTO dba_running_stats VALUES
(
'Flush of Shared Pool',1,35,todays_date,0
);
COMMIT;
END IF;
END;
7. Check Session Level Hit Ratio
--------------------------------
Description: The Hit Ratio should be higher than 90%
select Username,
OSUSER,
Consistent_Gets,
Block_Gets,
Physical_Reads,
100*( Consistent_Gets + Block_Gets - Physical_Reads)/
( Consistent_Gets + Block_Gets ) "Hit Ratio %"
from V$SESSION,V$SESS_IO
where V$SESSION.SID = V$SESS_IO.SID
and ( Consistent_Gets + Block_Gets )>0
and username is not null
order by Username,"Hit Ratio %";
8. Copying the data from one database to another
------------------------------------------------
- If you want to copy the data from one database to another having many tables and constraints,all you need is this script which will first disable all the constraints then delete the prior data and then finaly copy the data from any remote database and finaly it enables all the constraints on the table of that database.......all you need is to have a table having names of all the tables and the corresponding constraints
CREATE OR REPLACE procedure pre_tra
as
cursor cconstraints is select table_name, constraint_name, status from all_constraints where owner='owner name';
c2 cconstraints%rowtype;
mytab varchar2(200);
mytab1 varchar2(200);
mytab3 varchar2(200);
mytab4 varchar2(200);
retrycounter number;
counter number;
cstatus varchar2(20);
enableerrorfound boolean:=true;
--MAX_TRY number:=10000;
begin
begin
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab := c2.table_name;
mytab1 := c2.constraint_name;
mytab3 :='alter table ' || mytab || ' disable constraint ' || mytab1;
execute immediate mytab3;
end loop;
close cconstraints;
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
execute immediate 'delete ' || mytab;
end loop;
close cconstraints;
end;
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
mytab :=c2.table_name;
mytab4 :='DATABASENAME';
execute immediate 'insert into ' || mytab || ' (select * from '|| mytab || '@' || mytab4 || ')';
DBMS_OUTPUT.PUT_LINE(mytab);
commit;
DBMS_OUTPUT.PUT_LINE(mytab);
end loop;
close cconstraints;
begin
DBMS_OUTPUT.PUT_LINE('Starting enable script.....');
RETRYCOUNTER := 3;
WHILE RETRYCOUNTER > 0
loop
open cconstraints;
loop
fetch cconstraints into c2;
exit when cconstraints%notfound;
DBMS_OUTPUT.PUT_LINE('retrycounter==>'||mytab);
mytab := c2.table_name;
mytab1 := c2.constraint_name;
--cstatus:= c2.status;
--if cstatus = 'DISABLED' then
mytab3 :='alter table ' || mytab || ' enable constraint ' || mytab1;
DBMS_OUTPUT.PUT_LINE('Q' || mytab3);
DBMS_OUTPUT.PUT_LINE(retrycounter);
--enableerrorfound:=false;
BEGIN
execute immediate mytab3;
EXCEPTION
WHEN OTHERS THEN
enableerrorfound:=true;
END;
--end if;
end loop;
RETRYCOUNTER := RETRYCOUNTER - 1;
DBMS_OUTPUT.PUT_LINE(retrycounter);
close cconstraints;
end loop;
DBMS_OUTPUT.PUT_LINE(retrycounter);
IF (enableerrorfound) THEN
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> TRUE');
else
DBMS_OUTPUT.PUT_LINE('enableerrorfound==> FALSE');
end if;
end;
end pre_tra;
/
9. List Session Specific Memory
-------------------------------
Description: List the UGA and PGA used by each session on the server
column name format a25
column total format 999 heading 'Cnt'
column bytes format 9999,999,999 heading 'Total Bytes'
column avg format 99,999,999 heading 'Avg Bytes'
column min format 99,999,999 heading 'Min Bytes'
column max format 9999,999,999 heading 'Max Bytes'
ttitle 'PGA = dedicated server processes - UGA = Client machine process'
compute sum of minmem on report
compute sum of maxmem on report
break on report
select se.sid,n.name,
max(se.value) maxmem
from v$sesstat se,
v$statname n
where n.statistic# = se.statistic#
and n.name in ('session pga memory','session pga memory max',
'session uga memory','session uga memory max')
group by n.name,se.sid
order by 3
/
10. List the size of Stored Procedures and use it to tune Shared Pool
---------------------------------------------------------------------
Description: This script lists the size of stored objects
column num_instances heading "Num" format 999
column type heading "Object Type" format a12
column source_size heading "Source" format 99,999,999
column parsed_size heading "Parsed" format 99,999,999
column code_size heading "Code" format 99,999,999
column error_size heading "Errors" format 999,999
column size_required heading "Total" format 999,999,999
compute sum of size_required on report
select count(name) num_instances
,type
,sum(source_size) source_size
,sum(parsed_size) parsed_size
,sum(code_size) code_size
,sum(error_size) error_size
,sum(source_size)
+sum(parsed_size)
+sum(code_size)
+sum(error_size) size_required
from dba_object_size
group by type
order by 2
/
11. Redo Latch Contention Monitor
---------------------------------
Description: Try to reduce the contention by reducing all the ratios to be less than 1
SET feedback OFF
COLUMN name FORMAT a15
COLUMN gets FORMAT 99999999
COLUMN misses FORMAT 999999
COLUMN immediate_gets FORMAT 99999999 HEADING 'IMM_GETS'
COLUMN immediate_misses FORMAT 99999999 HEADING 'IMM_MISSES'
PROMPT Examining Contention for Redo Log Buffer Latches...
PROMPT ----------------------------------------------------
SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');
12. Sorts Monitoring Scripts
----------------------------
Description: Monitor the sorts in memory vs disk. Try to keep the disk/memory ratio to less than .10 by increasing the sort_area_size
SET HEADING OFF
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990
SELECT name, value FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');
13. Table Shared Mem Usage Report
---------------------------------
Description: Table Shared Memory Usage Report
set termout off feedback off echo off pagesize 59
set linesize 79 newpage 0 space 1 escape off
col iname noprint new_val instance
col today noprint new_val datetime
select name iname from v$database;
select to_char(sysdate, 'Mon DD, YYYY HH24:MI') today from dual;
btitle ce '======================================================' skip 1 -
le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno
ttitle center 'Database: ' instance ' - Table Usage Report' -
skip 1 col 62 datetime skip 2
set pagesize 60
col name format A28 heading "TABLE NAME"
set termout off feedback off echo off pagesize 59
set linesize 79 newpage 0 space 1 escape off
col iname noprint new_val instance
col today noprint new_val datetime
select name iname from v$database;
select to_char(sysdate, 'Mon DD, YYYY HH24:MI') today from dual;
btitle ce '======================================================' skip 1 -
le 'table_usage.sql' col 70 'Page: 'FORMAT 999 sql.pno
ttitle center 'Database: ' instance ' - Table Usage Report' -
skip 1 col 62 datetime skip 2
set pagesize 60
col name format A28 heading "TABLE NAME"
col tablespace format A10
col KB format 99,999,999
col sharable_mem format 99,990 heading "SHARE MEM"
col loads format 9,990
col executions format 999,990
col own noprint
break on tablespace
spool table_usage.txt
select c.loads, c.executions, c.owner||'.'||c.name name,
t.tablespace_name tablespace, s.bytes/1024 KB, c.sharable_mem
from dba_tables t,
dba_segments s,
v$db_object_cache c
where c.type = 'TABLE'
and c.executions > 0
and c.name = t.table_name
and c.owner = t.owner
and c.name = s.segment_name
and c.owner = s.owner
order by 4, 1 desc, 2 desc, 3
/
spool off;
exit;
14. TO MEASURE THE BUFFERCACHE HIT RATIO FOR USER WISE
------------------------------------------------------
Description: When all application users are running in the server then we have to see the bufferhit ratio of all application users so that we can measure individuals performance.
select username, (1-(physical_reads/(consistent_gets+block_gets)))*100 "Buffer Cache Hit Ratio"
from v$session se, v$sess_io si
where se.sid = si.sid
and (consistent_gets+block_gets) > 0
and username is not null
order by username
/
15. Transaction Processing Rate
-------------------------------
Description: Computer Transaction Processing rate to get a better idea on sizing memory objects
SELECT SUM(s.value/
(86400*(SYSDATE - TO_DATE(i.VALUE,'J')))) "tps"
FROM V$SYSSTAT s, V$INSTANCE i
WHERE s.NAME in ('user commits','transaction rollbacks')
AND i.KEY = 'STARTUP TIME - JULIAN';
===============================================================================
==>Rollback (Rollback segment tuning and sizing scripts)
===============================================================================
1. Active Transactions in Rollback Segments
-------------------------------------------
Description: This script will display the active user and the rollback segment being used in the database
column rr heading 'RB Segment' format a18
column us heading 'Username' format a15
column os heading 'OS User' format a10
column te heading 'Terminal' format a10
SELECT r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal te
FROM
v$lock l,
v$session s,
v$rollname r
WHERE
l.sid = s.sid(+) AND
trunc(l.id1/65536) = r.usn AND
l.type = 'TX' AND
l.lmode = 6
ORDER BY r.name
/
2. Rollback Segment Wrap Time Monitoring Script
-----------------------------------------------
Description: This script reports how many hours it has been since the rollback segments wrapped
select n.name,
round(24*(sysdate-to_date(i1.value||' '||i2.value,'j SSSSS')) /
(s.writes/s.rssize),1) "Hours"
from v$instance i1,
v$instance i2,
v$rollname n,
v$rollstat s
where
i1.key = 'STARTUP TIME - JULIAN'
and i2.key = 'STARTUP TIME - SECONDS'
and n.usn = s.usn
and s.status = 'ONLINE'
3. Rollback Statistics
----------------------
Description: Gives lots of usefull easy to read info on how your RBS are performing. Needs 132 char display.
col name for a7
col xacts for 9990 head "Actv|Trans"
col InitExt for 990.00 head "Init|Ext|(Mb)"
col NextExt for 990.00 head "Next|Ext|(Mb)"
col MinExt for 99 head "Min|Ext"
col MaxExt for 999 head "Max|Ext"
col optsize for 9990.00 head "Optimal|Size|(Mb)"
col rssize for 9990.00 head "Curr|Size|(Mb)"
col hwmsize for 9990.00 head "High|Water|Mark|(Mb)"
col wraps for 999 head "W|R|A|P|S"
col extends for 990 head "E|X|T|E|N|D|S"
col shrinks for 990 head "S|H|R|I|N|K|S"
col aveshrink for 990.00 head "AVG|Shrink|(Mb)"
col gets head "Header|Gets"
col waits for 99990 head "Header|Waits"
col writes for 999,999,990 head "Total|Writes|Since|Startup|(Kb)"
col wpg for 9990 head "AVG|Writes|Per|HedGet|(bytes)"
set lines 132 pages 40 feed off
break on report
compute sum of gets on report
compute sum of waits on report
compute avg of aveshrink on report
compute avg of wpg on report
select name,
XACTS,
initial_extent/1048576 InitExt,
next_extent/1048576 NextExt,
min_extents MinExt,
max_extents MaxExt,
optsize/1048576 optsize,
RSSIZE/1048576 rssize,
HWMSIZE/1048576 hwmsize,
wraps,
extends,
shrinks,
aveshrink/1048576 aveshrink,
gets,
waits,
writes/1024 writes,
writes/gets wpg
from v$rollstat,v$rollname,dba_rollback_segs
where v$rollstat.usn=v$rollname.usn
and dba_rollback_segs.segment_id=v$rollname.usn
order by name
/
===========================================
==>SQL (SQL AND APPLICATION TUNING SCRIPTS)
===========================================
1. Who is currently running what SQL
------------------------------------
Description: This script will show you the user's OS name, Username in the database and the SQL Text they are running
SELECT osuser, username, sql_text
from v$session a, v$sqltext b
where a.sql_address =b.address
order by address, piece
2. Auto-Changing SQL Prompt to the user and database connected
---------------------------------------------------------------
Description: When you connect to sqlplus, you see the the following sql prompt SQL>
By using this sql in the glogin.sql, you will see a prompt similar to the following: SAMI@RACDB1>
The following code works on Oracle 8i (8.1.5, 8.1.6, 8.1.7).
You have to insert the following line of code in glogin.sql which is usually found in
$ORACLE_HOME/sqlplus/admin
set termout off
set echo off
define X=NotConnected
define Y=DBNAME
Column Usr New_Value X
Column DBName New_Value Y
Select SYS_CONTEXT('USERENV','SESSION_USER' ) Usr From Dual;
--- The following does not work in 8.1.5 but works in 8.1.6 or above
---Select SYS_CONTEXT('USERENV','DB_NAME') DBNAME From Dual;
--- If you are using 8.1.5, use this .
Select Global_Name DBNAME from Global_Name;
set termout on
set sqlprompt '&X@&Y> '
Please note that this method will work only when you make a new sql plus session because when you make a new sql plus session, then only glogin.sql is executed.
3.Capture application's sql in N tier evironment
------------------------------------------------
Description: In N tier structure it is really hard to track user session specially in a web enviroment, All users connect to database using the same application , so all requests are going to database on behalf of one oracle user. then how can you track actions of one ENDUSER (application user).
In the v$session, there are serval columns you can use to distinguish ENDUSER such as Module, Action and Client_info.
for example, you can use
dbms_application_info.set_client_info('client info')
to set client info accroding to user's ip or loginname in your application, then it is easy to find out what sql statement was execute by this client by using the following script
select s.sid, s.username, s.program, oc.sql_text
from v$session s, v$open_cursor oc
where s.saddr=oc.saddr and s.sid=oc.sid
and s.client_info='client_info';
4. SQL trace and TKPROF in SQLPLUS
-----------------------------------
Description: Purpose: To read raw session trace file with option to format trace file with TKPROF and read it in SQLPLUS Session
#!/bin/sh
#################################################################
#################################################################
## ScriptName:trace ##
## Purpose: To read raw session trace file with option to ##
## format trace file with TKPROF and read it in SQLPLUS Session##
## You must set TRACE_DIR and ORACLE_SID in ##
## this script Put trace in < $HOME/bin > make it executable ##
## and run it from sqlplus session ##
## Example: SQL> ! trace ##
## Note:You Must Run this script from your sqlplus session ##
#################################################################
#################################################################
clear
n1=`ps |grep sqlplus|awk '{print $1}' `
n2=`ps -ef|grep $n1|grep oracle"$ORACLE_SID"|awk '{print $2}'`
TRACE_DIR=/u01/app/oracle/admin/$ORACLE_SID/udump
echo " 1.Do you want to view raw trace file"
echo " 2.Do you want to format trace file and view it"
read choice
if [ "$choice" = "1" ];
then cat $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc|more
else
if [ "$choice" = "2" ];
then
tkprof $TRACE_DIR/"$ORACLE_SID"_ora_$n2.trc $TRACE_DIR/trace_session_$n2
clear
echo "Your TKPROF formated file name is $TRACE_DIR/trace_session_$n2.prf "
echo " Do you want to read it now y/n "
read ans
if [ "$ans" = "y" ];
then
cat $TRACE_DIR/trace_session_$n2.prf|more
else
if [ "$ans" = "n" ];
then
echo "Happy Hunting Perfomance Problems"
fi
fi
fi
fi
=============================================
==>OTHER SCRIPTS IN THIS PERF/TUNING CATEGORY
=============================================
1. Find Full Table Scans
------------------------
Description: The output of this script will display all sql statements in the shared pool that are doing full table scans.
--run this as sys in SQL worksheet
create table full_sql (sql_text varchar2(1000), executions number);
create or replace procedure p_findfullsql as
v_csr number;
v_rc number;
v_string varchar2(2000);
v_count number;
cursor c1 is select sql_text,executions from v$sqlarea where lower(sql_text) like '%select%';
begin
for x1 in c1 loop
delete from plan_table ;
Begin
v_Csr := DBMS_SQL.OPEN_CURSOR;
v_string := 'explain plan for ' ;
v_string := v_string||x1.sql_text ;
DBMS_SQL.PARSE(v_csr, v_string, DBMS_SQL.V7);
v_rc := DBMS_SQL.EXECUTE(v_csr);
DBMS_SQL.CLOSE_CURSOR(v_csr);
Exception
when others then
null;
End ;
select count(*) into v_count from plan_table where options like '%FULL%' and operation like '%TABLE%' ;
if v_count > 0 then
insert into full_sql(sql_text,executions) values (x1.sql_text, x1.executions) ;
end if;
end loop ;
commit;
end ;
/
execute p_findfullsql ;
select * from full_sql;
drop table full_sql;
2. Identify top 20 Oracle Processes in Unix
-------------------------------------------
Description: Find the top 20 longest running processes in unix. Useful for high CPU bound systems with large number of users. Script also identifies processes without a oracle session.
#!/bin/ksh
#
# Find Highest CPU used Oracle processes and get the Username and SID from oracle
# Only 3 character SIDNAME is displayed - Adjust the script according to your need.
#
date
echo " Top 20 CPU Utilized Session from `hostname`"
echo " ============================================"
echo "O/S Oracle Session Session Serial UNIX Login Ora CPU Time"
echo "ID User ID Status ID No ID MMDD:HHMISS SID Used"
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
ps -ef|grep LOCAL|cut -c1-15,42-79|sort -rn +2 | head -20 | while read LINE
do
SIDNAME=`echo $LINE | awk '{ print $4 }' | cut -c7-14`
CPUTIME=`echo $LINE | awk '{ print $3 }'`
UNIXPID=`echo $LINE | awk '{ print $2 }'`
#echo $SIDNAME $CPUTIME $UNIXPID
export ORACLE_SID=$SIDNAME
SIDNAME=`echo $ORACLE_SID | cut -c4-6`
export ORACLE_HOME=`/dba_script/bin/find_ohome.sh ${ORACLE_SID}`
export SHLIB_PATH=$ORACLE_HOME/lib:/usr/lib
export TMPDIR=/tmp
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export SQLPLUS="$ORACLE_HOME/bin/sqlplus -s / "
$SQLPLUS >> $wlogfile < set pages 0 lines 80 trims on echo off verify off pau off
column pu format a8 heading 'O/S|ID' justify left
column su format a11 heading 'Oracle|User ID' justify left
column stat format a8 heading 'Session|Status' justify left
column ssid format 999999 heading 'Session|ID' justify right
column sser format 999999 heading 'Serial|No' justify right
column spid format 999999 heading 'UNIX|ID' justify right
column ltime format a11 heading 'Login|Time' justify right
select p.username pu,
s.username su,
s.status stat,
s.sid ssid,
s.serial# sser,
lpad(p.spid,7) spid,
to_char(s.logon_time, 'MMDD:HH24MISS') ltime,
'$SIDNAME $CPUTIME'
from v\$process p,
v\$session s
where p.addr=s.paddr
and p.spid=$UNIXPID
union all
select a.username, 'Kill Me', 'NoOracle', a.pid, a.serial#,
lpad(a.spid,7) spid, 'KILL UNIXID', '$SIDNAME $CPUTIME'
from v\$process a
where a.spid = $UNIXPID
and not exists (select 1 from v\$session s
where a.addr=s.paddr);
EOF
done
echo "-------- ----------- -------- ------- ------- ------- ----------- --- --------"
date
#
# End of Script
3. Monitor and Verify Deadlocks and Waiting Sessions
----------------------------------------------------
Description: Monitor and Verify DEAD Locks: Holding and Waiting Sessions
Script: waiters.sql
set lines 80 echo on ver off timing on term on pages 60 feed on head on
spool DEAD_LOCK_WAITERS.LST
col " " for A25
col "Holding Session Info" for A25
col "Waiting Session Info" for A25
select --+ ORDERED
'Session ID' || CHR(10) ||
'Mode Held' || CHR(10) ||
'Lock Type' || CHR(10) ||
'Mode Requested' || CHR(10) ||
'Lock ID 1' || CHR(10) ||
'Lock ID 2' " "
-------------------------------- END of Header
,
HH.session_id || CHR(10) ||
HH.mode_held || CHR(10) ||
HH.lock_type || CHR(10) ||
HH.mode_requested || CHR(10) ||
HH.lock_id1 || CHR(10) ||
HH.lock_id2 "Holding Session Info"
------------------------------ END of Holding Session
,
Ww.session_id || CHR(10) ||
WW.mode_held || CHR(10) ||
Ww.lock_type || CHR(10) ||
Ww.mode_requested || CHR(10) ||
Ww.lock_id1 || CHR(10) ||
Ww.lock_id2 "Waiting Session Info"
------------------------------ END of Waiting Session
from
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where blocking_others = 'Blocking' and
mode_held != 'None' and
mode_held != 'Null'
) HH,
-----------------------------------------------------
(
select /*+ RULE */ *
from SYS.dba_locks
where mode_requested != 'None'
) WW
-----------------------------------------------------
where WW.lock_type = HH.lock_type and
WW.lock_id1 = HH.lock_id1 and
WW.lock_id2 = HH.lock_id2
;
spool off
4. SGA Sizing
-------------
Description: An interesting script to size the SGA
set serverout on
DECLARE
l_uplift CONSTANT NUMBER := 0.3;
/* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 50;
/* Change ths to a predicted number existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;
BEGIN
dbms_output.enable(20000);
IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;
SELECT
avg(value)*l_numusers
,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';
SELECT sum(sharable_mem) INTO l_sum_sql_shmem FROM v$sqlarea;
SELECT sum(sharable_mem) INTO l_sum_obj_shmem FROM v$db_object_cache;
l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem;
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem;
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND(l_total_avg + (l_total_avg * l_uplift), 0) ) ||' and '
|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) ) ||' bytes');
dbms_output.put_line('Recommended Shared_pool size between :' || TO_CHAR(ROUND((l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||' and '|| TO_CHAR(ROUND((l_total_max + (l_total_max * l_uplift )) /1024*1024) ,0) ) ||' M bytes');
end;
/
5.DB Block Efficiency Script
----------------------------
Description: This script uses the new way of calculating DB_Block Buffer Efficiency.
Logical Read: consistent gets + db block gets
Hit-Ratio: (logical reads - physical reads) / (logical reads)
Our Aim: OLTP >= 95%, DSS/Batch >= 85%
Solution: enlarge block buffers, tune SQL, check appropriateness of indexes
select name, ((consistent_gets + db_block_gets) - physical_reads)/(consistent_gets + db_block_gets) * 100 "Hit Ratio%"
from v$buffer_pool_statistics
where physical_reads > 0;
6. EXPLAIN and get some statistics on your SQL
----------------------------------------------
Description: Haven't you ever thought there should be an easier way to do the EXPLAIN PLAN and TKPROF statistics than to edit your
queries to add the commands (like EXPLAIN PLAN SET...), or to have to find or write a script that automates this? It should
be an automatic part of SQL*Plus. Well, as of SQL*Plus 3.3 it is!! The command is called 'SET AUTOTRACE ON'!
The SET AUTOTRACE Command
sql>SET AUTOTRACE ON
And then run your select statement. Example:
SQL> SET AUTOTRACE ON
SQL> SELECT d.deptno, d.dname, e.empno, e.ename FROM dept d, emp e WHERE d.deptno = e.deptno;
DEPTNO DNAME EMPNO ENAME
---------- -------------- ---------- ----------
10 ACCOUNTING 7839 KING
.
.
30 SALES 7900 JAMES
30 SALES 7521 WARD
14 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 MERGE JOIN
2 1 SORT (JOIN)
3 2 TABLE ACCESS (FULL) OF 'EMP'
4 1 SORT (JOIN)
5 4 TABLE ACCESS (FULL) OF 'DEPT'
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
2 consistent gets
0 physical reads
0 redo size
670 bytes sent via SQL*Net to client
376 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
14 rows processed
There are also some other options, for example there is a TRACEONLY option which supresses the SQL output.
Some setup issues:
If you go off and try this on your instance, you may run into some problems. There are a few setup steps that need to be taken to make this work:
-Make sure you have access to PLAN_TABLE. If you don't, create it using utlxplan.sql (It should be in a directory like $ORACLE_HOME/rdbms73/admin/) and make sure you have access to it from the user you are using to tune the SQL.
-You also need to create the PLUSTRACE role, and grant it to the desired users. The script to create this role is in:
$ORACLE_HOME/plus33/Plustrce.sql
It has to be run from SYS in order to have the correct security access. Then grant the role to the desired users or ROLEs.
7. See How Far Sorts Have Progressed
------------------------------------
Description: This script will display the estimated work and time remaining for long operations (i.e. over 10 minutes).
select sid,
message || '(' || time_remaining || ')' "Long Ops"
from v$session_longops
where time_remaining > 600;
8. Types of Contention a Database Instance is (or is not) Experiencing
----------------------------------------------------------------------
Description: This is possibly one of the most useful tuning scripts available. The V$SYSTEM_EVENT dynamic performance view is the highest-level view of the "Session Wait Interface". Information in this view is cumulative since the database instance was started, and one can get a very good idea of what types of contention a database instance is (or is not) experiencing by monitoring this view.
/**********************************************************************
* File: systemevent.sql
* Type: SQL*Plus script
* Description:
* This is possibly one of the most useful tuning scripts available.
*
* The V$SYSTEM_EVENT dynamic performance view is the highest-level
* view of the "Session Wait Interface". Information in this view
* is cumulative since the database instance was started, and one
* can get a very good idea of what types of contention a database
* instance is (or is not) experiencing by monitoring this view.
*
* NOTE: this script sorts output by the TIME_WAITED column in the
* V$SYSTEM_EVENT view. If the Oracle initialization parameter
* TIMED_STATISTICS is not set to TRUE, then the TIME_WAITED
* column will not be populated.
*
* Please *disregard* the advice of people who insist that turning
* off TIMED_STATISTICS is somehow a performance boost. Whatever
* performance overhead that might be incurred is more than
* compensated for by the incredible tuning information that
* results...
*
*********************************************************************/
set echo off feedback off timing off pause off verify off
set pagesize 100 linesize 500 trimspool on trimout on
col event format a26 truncate heading "Event Name"
col total_waits format 999,990.00 heading "Total|Waits|(in 1000s)"
col total_timeouts format 999,990.00 heading "Total|Timeouts|(in 1000s)"
col time_waited format 999,990.00 heading "Time|Waited|(in Hours)"
col pct_significant format 90.00 heading "% of|Concern"
col average_wait format 990.00 heading "Avg|Wait|(Secs)"
col instance new_value V_INSTANCE noprint
select lower(replace(t.instance,chr(0),
)) instance
from sys.v_$thread t,
sys.v_$parameter p
where p.name = 'thread'
and t.thread# = to_number(decode(p.value,'0','1',p.value));
col total_time_waited new_value V_TOTAL_TIME_WAITED noprint
select sum(time_waited) total_time_waited
from sys.v_$system_event
where event not in ('SQL*Net message from client',
'rdbms ipc message',
'slave wait',
'pmon timer',
'smon timer',
'rdbms ipc reply',
'SQL*Net message to client',
'SQL*Net break/reset to client',
'inactive session',
'Null event')
/
select event,
(total_waits / 1000) total_waits,
(total_timeouts / 1000) total_timeouts,
(time_waited / 360000) time_waited,
decode(event,
'SQL*Net message from client', 0,
'rdbms ipc message', 0,
'slave wait', 0,
'pmon timer', 0,
'smon timer', 0,
'rdbms ipc reply', 0,
'SQL*Net message to client', 0,
'SQL*Net break/reset to client', 0,
'inactive session', 0,
'Null event', 0,
(time_waited / &&V_TOTAL_TIME_WAITED)*100) pct_significant,
(average_wait / 100) average_wait
from sys.v_$system_event
where (time_waited/360000) >= 0.01
order by pct_significant desc, time_waited desc
spool sysevent_&&V_INSTANCE
/
spool off
9. Wait Sessions
----------------
Description: The Script lists the Session ID,Event Name,Time Waited on the Database. This Script is useful in identifying the resource intensive Queries. Can Be used on oracle 8i & 9i Databases.
select se.sid,s.username,Se.Event,se.time_waited
from v$session s, v$session_event se
where s.username is not null
and s.username not in ('SYS','SYSTEM')
and se.sid=s.sid
and s.status='ACTIVE'
and se.event not like '%SQL*Net%';
10. Health Check
---------------
Description: Look at the statistics of your Database
REM Database Health monitoring script.
REM
REM Segments Max extents & Current extent comparison
REM
set line 180
set pagesize 10000
set feedback off
prompt
col Time format a50 heading "System Time"
select to_char(sysdate,'DD-MON-YYYY:HH24:MI:SS') Time from dual;
prompt
prompt
prompt
prompt**---------------Objects Reaching Max extents-----------------------**
prompt
col segment_name format a40 heading "Object Name"
col max_extents format 9999999999 heading "Max Extents"
col curr_extents format 99999999999 heading "Curent Extents"
select a.segment_name,a.max_extents,b.curr_extents from dba_segments a,(select
segment_name,max(extent_id) curr_extents from dba_extents group by segment_name) b where a.segment_name = b.segment_name
and (a.max_extents - b.curr_extents) <= 10;
prompt
prompt**---------------User Session Information----------------------------**
prompt
col sid format 9999 heading "SID"
col serial# format 9999999 heading "SERIAL NO"
col logon_time format 9999999 heading "Login Time"
col username format a12
col osuser format a24
col program format a38
select s.sid,s.serial#,p.spid,s.username,s.osuser,s.program,to_char(s.LOGON_TIME,'DD-MON-YY:HH24:MI:SS') "Log on Time",round((s.LAST_CALL_ET/(60*60*24)),2)"Wait in Days" from v$session s,v$process p where s.paddr = p.addr and s.username is not null order by 8 desc;
prompt
prompt**---------------File Information-------------------------------------**
prompt
col file_name format a55 heading "File Name"
col SizeInMB format 99999999 heading "Total Size (MB)"
col MAXSIZE format 99999999 heading "Maximum Size (MB)"
select file_name,BYTES/(1024*1024) SizeInMB,AUTOEXTENSIBLE,MAXBYTES/(1024*1024) MAXSIZE from dba_data_files;
prompt
prompt**---------------Tablespace Information-------------------------------**
prompt
col tablespace_name format a25 heading "Tablespace Name"
col logging format a10
col status format a12
col extent_management format a30 heading "Local/Dict"
select tablespace_name,status,contents,decode(logging,LOGGING,'YES','NO') Logging,extent_management from dba_tablespaces;
SELECT Total.name "Tablespace Name",
Free_space, (total_space-Free_space) Used_space, total_space
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name = Total.name;
prompt
prompt**---------------Tablespace Fragmentation Status----------------**
prompt
col TABLESPACE_NAME format a25 heading "Tablespace Name"
select TABLESPACE_NAME Name,TOTAL_EXTENTS "Total Extents",EXTENTS_COALESCED,decode(PERCENT_EXTENTS_COALESCED,100,'NO','YES') "Frag" from
dba_free_space_coalesced;
prompt
prompt**---------------Latch Contention-------------------------------**
prompt
col name format a40 heading "Latch Name"
SELECT name, gets, misses,
round((gets-misses)/decode(gets,0,1,gets),3) hit_ratio,
sleeps,
round(sleeps/decode(misses,0,1,misses),3) "sleeps/misses"
from v$latch
where gets != 0
order by name;
select name,immediate_gets,immediate_misses,(immediate_gets)/(immediate_gets+immediate_misses) Hit_Ratio
from v$latch where immediate_gets != 0;
prompt
prompt**---------------Shared Pool Statistics-------------------------------**
prompt
col namespace format a22
select namespace,gets,gethits,round(gethitratio,2) gethitratio,pins,pinhits,round(pinhitratio,2)
pinhitratio,reloads,invalidations from v$librarycache;
prompt
prompt**---------------Roll back segment Statistics-------------------------------**
prompt
col segment_name format a30 heading "Segment Name"
col status format a15
select segment_name,a.status,initial_extent/(1024) "Initial KB",next_extent/(1024)"NextKB",max_extents,min_extents,optsize/102
"Opt Size KB",curext "Current Extents" from dba_rollback_segs a,v$rollstat b where a.segment_id = b.usn;
prompt
prompt**---------------Top 20 Events and System Statistics-------------------------------**
prompt
col event format a40 heading "Event Name"
col Stat format a40 heading "Stat Name"
select * from ( select name "Stat",value from v$sysstat order by value desc ) where rownum <= 20 ;
select * from ( select event,total_waits from v$system_event order by total_waits desc ) where rownum <=
20;
prompt
prompt**---------------Buffer Cache statistics-------------------------------**
prompt
select (1-(a.value/(b.value+c.value))) *100 "Buffer Cache Hit ratio" from v$sysstat a,v$sysstat b,v$sysstat c where a.name
= 'physical reads' and b.name = 'consistent gets' and c.name = 'db block gets';
col name format a20 heading "Buffer Pool Name"
select name,free_buffer_wait,write_complete_wait,buffer_busy_wait,db_block_gets,consistent_gets,physical_reads,physical_writes
from v$buffer_pool_statistics;
prompt
prompt
prompt
prompt**---------------File I/O statistics-------------------------------**
prompt
col file# format 99 heading "File No"
select file#,PHYRDS "Physical Reads",PHYWRTS "Physical Writes",READTIM "Read Time",WRITETIM "Write Time",AVGIOTIM "Avg Time" from v$filestat;
set feedback on
10.Handy oracle freespace scripts
--------------------------------------
There are several views in the data dictionary that display database free space. We have to start by defining free space:
Is "free space" the empty space in the tablespace?
Is "free space the available space on the data files and filesystems?
There are several salient DBA views for displaying free space in Oracle:
dba_free_space
dba_data_files
dba_tablespaces
Here is a simple script to display Oracle free space within the data file space. Note the in-line view where dba_data_files is joined into dba_free_space:
SELECT
a.tablespace_name,
a.file_name,
a.bytes allocated_bytes,
b.free_bytes
FROM
dba_data_files a,
(SELECT file_id, SUM(bytes) free_bytes
FROM dba_free_space b GROUP BY file_id) b
WHERE
a.file_id=b.file_id
ORDER BY
a.tablespace_name;
This shows the allocated and free bytes within the data files, but it DOES not show available free space on the OS filesystem:
TABLESPACE_NAME FILE_NAME ALLOCATED_BYTES FREE_BYTES
-------------------- ------------------------- --------------- --------------
TBS_LOCALS /u01/app/oradata/devdb/de 20,971,520 20,774,912
vdb/devdb_tbs_locals_01.d
bf
Note that there are many other ways to create tablespace reports. This method create an intermediate view to doisplay free space:
Rem free_space.sql
rem run this script first, to create the free_space view;
drOP VIEW SYS.FREE_SPACE;
CREATE VIEW SYS.FREE_SPACE AS
SELECT
TABLESPACE_NAME TABLESPACE,
FILE_ID,
COUNT(*) PIECES,
SUM(BYTES) FREE_BYTES,
SUM(BLOCKS) FREE_BLOCKS,
MAX(BYTES) LARGEST_BYTES,
MAX(BLOCKS) LARGEST_BLKS
FROM
SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME, FILE_ID;
This is the next script:
rem tsfree.sql - Shows all free space within tablespaces.
Prompt be sure that you have run free_space.sql prior to this script
clear breaks;
clear computes;
set verify off;
set pagesize 66;
set linesize 79;
set newpage 0;
column temp_col new_value spool_file noprint;
column today new_value datevar noprint;
column TABLESPACE_NAME FORMAT A15 HEADING 'Tablespace';
COLUMN PIECES FORMAT 9,999 HEADING 'Tablespace|Pieces';
COLUMN FILE_MBYTES FORMAT 99,999 HEADING 'Tablespace|Mbytes';
cOLUMN FREE_MBYTES FORMAT 99,999 HEADING 'Free|Mbytes';
COLUMN CONTIGUOUS_FREE_MBYTES FORMAT 99,999 HEADING 'Contiguous|Free|Mbytes';
COLUMN PCT_FREE FORMAT 999 HEADING 'Percent|FREE';
COLUMN PCT_CONTIGUOUS_FREE FORMAT 999 HEADING 'Percent|FREE|Contiguous';
ttitle left datevar right sql.pno -
center ' Instance Data File Storage' SKIP 1 -
center ' in ORACLE Megabytes (1048576 bytes)' -
skip skip;
BREAK ON REPORT
COMPUTE SUM OF FILE_MBYTES ON REPORT
select to_char(sysdate,'mm/dd/yy') today,
TABLESPACE_NAME,
PIECES,
(D.BYTES/1048576) FILE_MBYTES,
(F.FREE_BYTES/1048576) FREE_MBYTES,
((F.FREE_BLOCKS / D.BLOCKS) * 100) PCT_FREE,
(F.LARGEST_BYTES/1048576) CONTIGUOUS_FREE_MBYTES,
((F.LARGEST_BLKS / D.BLOCKS) * 100) PCT_CONTIGUOUS_FREE
from SYS.DBA_DATA_FILES D, SYS.FREE_SPACE F
where D.STATUS = 'AVAILABLE' AND
D.FILE_ID= F.FILE_ID AND
D.TABLESPACE_NAME = F.TABLESPACE
order by TABLESPACE_NAME;
Here is the report from this script.
Tablespace Pieces Mbytes Mbytes FREE Mbytes Contiguous
--------------- ---------- ---------- ------- ------- ---------- ----------
MASTER1_DETAILS 1 18 2 10 2 10
MASTER1_DETAILS 1 20 20 100 20 100
MASTER2_DETAILS 1 2 1 65 1 65
MASTER3_DETAILS 1 5 5 95 5 95
MASTER4_DETAILS 2 3 1 36 1 35
RBS_ONE 11 490 380 78 280 57
RBS_TWO 11 490 379 77 279 57
SYSTEM 17 60 45 76 45 75
TEMP 1 650 650 100 650 100
TOOLS 2 15 9 61 8 55
USERS 41 100 31 31 4 4
----------
13,255
This report is useful for finding the largest sized chunk of free space within a tablespace
Sunday, January 25, 2009
Fixing & Registering ORACLE_HOMES in Central Inventory
Central Inventory location is determined by oraInst.loc located in:
HP/Solaris- /var/opt/oracle/oraInst.loc
Linux- /etc/oraInst.loc
Windows- \\HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc
To check if all oracle homes are registered within the Central Inventory run the following command:
${ORACLE_HOME}/opatch/bin/opatch lsinventory -all
Invoking OPatch 10.2.0.3.0
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /software/oracle/OEM10gR2/agent10g
Central Inventory : /software/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.3.0
OUI version : 10.2.0.3.0
OUI location : /software/oracle/OEM10gR2/agent10g/oui
Log file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/opatch/opatch....log
Lsinventory Output file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/....lsinventory.....txt
----------------------------------...
List of Oracle Homes:
Name Location
agent10g /software/oracle/OEM10gR2/agent10g
bzq18i /software/oracle8i
8i32bit /software/oracle/8.1.7.4_32
oracle9207 /software/oracle/9.2.0.7
oracle9205 /software/oracle/9.2.0.5
.
.
.
OPatch succeeded.
If oracle home is missing in the Central Inventory , attach it by the following command:
${ORACLE_HOME}/oui/bin/runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="oracle home location" ORACLE_HOME_NAME="a name" CLUSTER_NODES="{}"
If you have problems while trying to attach a 9.2 home to the Central Inventory, read note 457457.1To manual load the updated inventory to the OEM, use the command:
emctl control agent runCollection:host Inventory
To remove incorrect oracle home ftom the Central Inventory, run the command:
${ORACLE_HOME}/oui/bin/runInstaller -silent -detachHome ORACLE_HOME="oracle home location" ORACLE_HOME_NAME="a name"
HP/Solaris- /var/opt/oracle/oraInst.loc
Linux- /etc/oraInst.loc
Windows- \\HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc
To check if all oracle homes are registered within the Central Inventory run the following command:
${ORACLE_HOME}/opatch/bin/opatch lsinventory -all
Invoking OPatch 10.2.0.3.0
Oracle interim Patch Installer version 10.2.0.3.0
Copyright (c) 2005, Oracle Corporation. All rights reserved..
Oracle Home : /software/oracle/OEM10gR2/agent10g
Central Inventory : /software/oracle/oraInventory
from : /var/opt/oracle/oraInst.loc
OPatch version : 10.2.0.3.0
OUI version : 10.2.0.3.0
OUI location : /software/oracle/OEM10gR2/agent10g/oui
Log file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/opatch/opatch....log
Lsinventory Output file location : /software/oracle/OEM10gR2/agent10g/cfgtoollogs/....lsinventory.....txt
----------------------------------...
List of Oracle Homes:
Name Location
agent10g /software/oracle/OEM10gR2/agent10g
bzq18i /software/oracle8i
8i32bit /software/oracle/8.1.7.4_32
oracle9207 /software/oracle/9.2.0.7
oracle9205 /software/oracle/9.2.0.5
.
.
.
OPatch succeeded.
If oracle home is missing in the Central Inventory , attach it by the following command:
${ORACLE_HOME}/oui/bin/runInstaller -silent -ignoreSysPrereqs -attachHome ORACLE_HOME="oracle home location" ORACLE_HOME_NAME="a name" CLUSTER_NODES="{}"
If you have problems while trying to attach a 9.2 home to the Central Inventory, read note 457457.1To manual load the updated inventory to the OEM, use the command:
emctl control agent runCollection:host Inventory
To remove incorrect oracle home ftom the Central Inventory, run the command:
${ORACLE_HOME}/oui/bin/runInstaller -silent -detachHome ORACLE_HOME="oracle home location" ORACLE_HOME_NAME="a name"
Thursday, January 22, 2009
Oracle Cumulative Patch Updates
How Often Should I apply Oracle CPU Patches?
Remember, you DON'T have to apply patches, and sometimes patches can CAUSE unplanned issues. The best practices are always to conduct a full stress in a TEST environment before applying the patches in production. It also depend on your production policy. In many shops, patches are only applied to address a specific known problem.
In general there are two approaches to applying Oracle patches:
1 - If it ain't broke - don't fix it. Don't apply Oracle patches and risk hitting bugs.
2 - Apply all patches and be proactive. Lots of extra work and you risk a patch causing a problem.
Me, I wait for major releases and re-install at-once, and I only look at patches to fix specific issues.
Remember, you DON'T have to apply patches, and sometimes patches can CAUSE unplanned issues. The best practices are always to conduct a full stress in a TEST environment before applying the patches in production. It also depend on your production policy. In many shops, patches are only applied to address a specific known problem.
In general there are two approaches to applying Oracle patches:
1 - If it ain't broke - don't fix it. Don't apply Oracle patches and risk hitting bugs.
2 - Apply all patches and be proactive. Lots of extra work and you risk a patch causing a problem.
Me, I wait for major releases and re-install at-once, and I only look at patches to fix specific issues.
Tuesday, January 20, 2009
Pl/sql to Validate email
Oracle delivers an API in EBS that can validate Email Address Format.However it does not capture the invalidity if Email String were to be say xxx@hostYou may use the below pl/sql function for Email Address Validation.
CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
RETURN VARCHAR2 IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := instr(l_user_name,'.');
l_at_pos := instr(l_user_name,'@');
l_str_length := length(l_user_name);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR (l_at_pos = 1) OR (l_at_pos = l_str_length) OR (l_dot_pos = l_str_length)) THEN RETURN 'FAILURE';
END IF;
IF instr(substr(l_user_name,l_at_pos),'.') = 0 THEN RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xx_check_email;
CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
RETURN VARCHAR2 IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := instr(l_user_name,'.');
l_at_pos := instr(l_user_name,'@');
l_str_length := length(l_user_name);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR (l_at_pos = 1) OR (l_at_pos = l_str_length) OR (l_dot_pos = l_str_length)) THEN RETURN 'FAILURE';
END IF;
IF instr(substr(l_user_name,l_at_pos),'.') = 0 THEN RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xx_check_email;
Subscribe to:
Posts (Atom)