I am Md.Samiuzzaman Khan (Tamim) work as Technical Consultant having 5+ years of experience in Oracle Database Administration and Development, Unix/Linux administration, System Design and Integration, Search Engine Optimization (SEO), Mobile Content Management System (SMPP and Web Services), Java and .Net (C#) base Application Development also in Project Management(Agile/Scrum).

Kill all oracle sessions

February 23, 2011 Leave a comment
rem ##################################################################
rem Filename:   kill_all_oracle_sessions.sql
rem Purpose:    Kill all oracle sessions for a user
rem Date:       23-Feb-2011
rem Author:     Tamim Khan (Email: tamimdba@gmail.com)
rem ##################################################################

set pagesize 0
spool kill_all.sql
select 'alter system kill session '''||sess.sid||', '||sess.serial#||''';' from v$session sess where username = '<User Name>';
spool off
spool kill_all.log
@kill_all.sql
spool off

To run the script connect oracle as a system user then run the file kill_all_oracle_sessions.sql

@kill_all_oracle_sessions.sql
Advertisements

Monitoring SGA (Free Memory) Using v$sgastat

February 7, 2011 Leave a comment

In this article, we will discuss how to get monitor free memory in System Global Area (SGA) in Oracle 10g. The System Global Area (SGA) Memory Structures in data. The SGA is read/write.  A system global area is a group of shared memory areas that dedicated to an Oracle database instance. For example Large Pool, Shared Pool, Java Pool etc.

SQL> SHOW SGA;
Total System Global Area  608174080 bytes
Fixed Size                  2006160 bytes
Variable Size             176161648 bytes
Database Buffers          423624704 bytes
Redo Buffers                6381568 bytes

Let’s use v$sgastat parameter in this article. The v$sgastat parameter displays detailed information on the system global area (SGA).

SQL> DESC v$sgastat;
Name                                      Null?    Type
----------------------------------------- -------- ----------------
POOL                                               VARCHAR2(12)
NAME                                               VARCHAR2(26)
BYTES                                              NUMBER

Let’s have a short explanation of all column of the v$sgastat parameter:

POOL: Designates the pool in which the memory in NAME resides:

  • Shared Pool: Memory is allocated from the shared pool.
  • Large Pool: Memory is allocated from the large pool.
  • Java Pool: Memory is allocated from the Java pool.
  • Streams Pool: Memory is allocated from the Streams pool.

NAME: The name column means system global area component name.

BYTES: The memory size in bytes.

Now we will use a simple SQL to see the free memory in SGA.

SQL> Select POOL, Round(bytes/1024/1024,0) Free_Memory_In_MB
  2  From V$sgastat
  3  Where Name Like '%free memory%';
POOL         FREE_MEMORY_IN_MB
------------ -----------------
shared pool                 22
large pool                   3
java pool                    4
streams pool                 8

Step by Step Oracle Enterprise Manager Configuration

February 7, 2011 Leave a comment

In this article, we will discuss monitoring configuration in Oracle Enterprise Manager.

Go to http://<hostname&gt;:<port-number>/em then specifying the system username and password after that we will click Login button.

N.B: Make sure enterprise manager console is running. to check it, issue following command.

$ emctl status dbconsole

The home page is displaying. After that we will click Monitoring Configuration under the Related Links.

The Configure Database Instance: Properties page is displaying. We will enter the correct password in the Monitor Password field and click Test Connection.

The Database Instance Configuration Result page is displaying. After some time next page is displaying.

The Configure Database Instance: Review page is displaying. We will click Submit button.

The Database Instance Configuration Result page is displaying. We will click OK button, after that we will go to Home page of the Oracle Database.


 

2010 in review

January 2, 2011 Leave a comment

The stats helper monkeys at WordPress.com mulled over how this blog did in 2010, and here’s a high level summary of its overall blog health:

Healthy blog!

The Blog-Health-o-Meter™ reads Fresher than ever.

Crunchy numbers

Featured image

The average container ship can carry about 4,500 containers. This blog was viewed about 17,000 times in 2010. If each view were a shipping container, your blog would have filled about 4 fully loaded ships.

 

In 2010, there were 32 new posts, growing the total archive of this blog to 67 posts. There were 12 pictures uploaded, taking up a total of 3mb. That’s about a picture per month.

The busiest day of the year was October 13th with 164 views. The most popular post that day was Use oradim in Linux.

Where did they come from?

The top referring sites in 2010 were google.co.in, en.wordpress.com, google.com, facebook.com, and yandex.ru.

Some visitors came searching, mostly for pl/sql lock timer, oracle parameterized view, oradim linux, oracle 11g oradim unix, and db_writer_processes.

Attractions in 2010

These are the posts and pages that got the most views in 2010.

1

Use oradim in Linux November 2009

2

Oracle 10g Enterprise Manager January 2010

3

Parameterized View in Oracle April 2010
1 comment

4

ER Diagram of HR System November 2009

5

Partition Table In Oracle January 2010

Categories: Oracle10g

Shared Memory Realm does not exist

December 20, 2010 Leave a comment

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Solution :

check ORACLE_SID has value if not exist then set ORACLE_SID

For Windows Environment

set ORACLE_SID=orcl 
echo %ORACLE_SID% 
orcl 

For Linux Environment

$ export ORACLE_SID=orcl

Now You need to Manually startup the database

sqlplus "sys/password as sysdba" 
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area  574619648 bytes
Fixed Size                  1250236 bytes
Variable Size             192941124 bytes
Database Buffers          377487360 bytes
Redo Buffers                2940928 bytes
Database mounted.
Database opened.
SQL> exit

Basic Concept of Performance Tuning in Oracle

October 25, 2010 Leave a comment

Scope of Performance Tuning

There is four main area for Performance Tuning.

1. SQL Tuning          – Responsibility of the Developer
2. Database Tuning     – Responsibility of the Database Administrator
3. System Tuning       – Responsibility of the System Administrator
4. Network Tuning      – Responsibility of the Network / LAN / WAN Administrator.

SQL Tuning

1. Find the problem to a single SQL

You may be lucky and know already the exact SQL causing the problem. If so, move straight on to the second step. Otherwise, click on the link above for help on finding the problem SQL.

2. Analyze the SQL to determine the nature of the problem

Most performance problems are quite common and easy to fix. This section will describe some of these and how to spot them, and then go on to describe a more general analysis method.

3. Fix the problem.

Almost every performance problem has a solution; it’s just that some are more complex than others. In order of increasing complexity and expense, such fixes include:
  • Analyze the underlying table to give Oracle’s Cost Based Optimizer the information it needs to resolve the SQL efficiently.
  • Add one or more hints to the SQL to encourage or discourage certain execution plans.
  • Minor changes to the SQL to encourage or discourage certain execution plans.
  • Restructure a poorly designed SQL that cannot be resolved efficiently.
  • Alter the underlying infrastructure. eg. Add or change (or even remove!) indexes; introduce clusters, partitions or index-organised tables; denormalize tables; use materialized views. Note that these actions venture outside the scope of this document, and should only be performed with the prior permission of (and preferably assistance from) the DBA and/or System Architect.
  • Refer the problem to the database administrator. Possible solutions here would include tuning the Oracle instance, restructuring or moving tablespaces, or archiving old data.
  • Refer the problem to the System Adminstrator. Possible solutions may include reconfiguration of existing hardware, or acquisition of new hardware.

Database Tuning

For optimum performance an Oracle database should be regularly tuned. Only tune a database after it has been up and running for a little while.
  • Tuning the cache hit ratio
  • Tuning the library cache
  • Tuning the log buffer
  • Tuning buffer cache hit ratio
  • Tuning sorts
  • Tuning rollback segments
  • Identifying missing indexes
  • Identifying index fragmentation
  • Identifying free list contention
  • Identify significant reparsing of SQL
  • Reducing database fragmentation
  • Rebuilding indexes
  • Reduce thrashing or poor system performance (or how to un-tune oracle?!)

System Tuning(Operating System)

Tune your operating system according to your operating system documentation. For Windows platforms, the default settings are usually sufficient. However, the Solaris and Linux platforms usually need to be tuned appropriately. The following sections describe issues related to operating system performance:
  • Basic OS Tuning Concepts
  • Solaris Tuning Parameters
  • Linux Tuning Parameters
  • HP-UX Tuning Parameters
  • Windows Tuning Parameters
  • Other Operating System Tuning Information
Details

Network Tuning

Network tuning is the performance optimization and tuning of SQL*Net based on an arbitrary UNP which could be TCP/IP, SPX/IP or DECnet. SQL*Net performance can be maximized by synchronization with tunable parameters of the UNP, for example, buffer size.SQL*Net transaction performance can be divided into components of connect time and query time, where
Total SQL*Net (Net8) Transaction Time = Connect Time + Query Time 

Connect time can be maximized by calibration of tunable parameters of SQL*Net and the UNP when designing and implementing networks.

SQL*Net Performance
For this discussion, SQL*Net performance and tuning analysis is based on two categories:

  • SQL*Net performance
  • SQL*Net tuning
Details1 

Details2


DB_WRITER_PROCESSES

September 20, 2010 Leave a comment

Setting the DB_WRITER_PROCESSES parameter (Background process DBWR) to a value greater than one is supported starting with the Oracle 8.0 release.

Multiple DBWR processes are mainly used to simulate asynchronous I/O when the operating system does not support it.  Since Windows NT and Windows 2000 use asynchronous I/O by default, using multiple DBWR processes may not necessarily improve performance.  Increasing this parameter is also likely to have minimal effect on single-CPU systems.  Increasing this parameter could, in fact, reduce performance on systems where the CPU’s are already over burdened.  In cases where the main performance bottleneck is that a single DBWR process cannot keep up with the work load, then increasing the value for DB_WRITER_PROCESSES may improve performance.

When increasing DB_WRITER_PROCESSES it may also be necessary to increase the DB_BLOCK_LRU_LATCHES parameter, as each DBWR process requires an LRU latch.

How to change db_writer_processes parameter

SQL> show parameter db_writer_processes
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     1

SQL> alter system set db_writer_processes=2 scope=spfile sid='*';

System altered.

Now restart the database to take the change effect.

 
SQL> show parameter db_writer_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
db_writer_processes                  integer     2

“The number of DBWR should be two times the number of CPUs (vary 1 or 2 depending on idle state of CPUs during production/test run)”

DB_WRITER_PROCESSES = 2 * number of CPU

Default value of DB_WRITER_PROCESSES is 1 or CPU_COUNT / 8, whichever is greater. If the number of processor groups is less than 36 but greater than the number of DB writer processes, then the number of DB writer processes is adjusted to be a multiple of the number of processor groups. If the number of DB writer processes is greater than or equal to the number of processor groups, then there is no adjustment.

Range of value in Oracle 10g- 1 to 20 but in Oracle 11g – 1 to 36.