Thursday, May 12, 2011

Sun Solaries / Linux / AIX / UNIX OS performance monitoring or OS tuning for oracle database specially

Sun Solaries / Linux / AIX / UNIX OS performance monitoring or OS tuning for oracle database specially
----------------------------------------------------------------------------------------
First of all you need know the name of some tools for that, these all are about same in all plat form with slight syntax difference.
These tools are :-
1) iostat
2) vmstat (Virtual Memory Statistics)
3) netstat
4) mpstat for cpu (Report per-processor or per-processor-set statistics)

5) prstat / top

6) uptime

7) prtdiag ( such as #prtdiag –v )

8) ps –ef (for process monitoring)

# ps -ef | grep -i smon (searching a process like smon)
# ps -ef | grep -i oracle
9) sar System Activity Reporter

Now I want some details about these with some examples:-
1) iostat :- iostat reports terminal and disk I/O activity and CPU utilization. The first line of output is for the time period since boot & each subsequent line is for the prior interval . Kernel maintains a number of counters to keep track of the values.
For details help just type
root# man iostat

syntax:
Basic synctax is- iostat [option] [interval] [count] ( root# iostat 5 20 )
option – let you specify the device for which information is needed like disk , cpu or terminal. (-d , -c , -t or -tdc ) . x options gives the extended statistics .
interval – is time period in seconds between two samples . iostat 4 will give data at each 4 seconds interval.
count – is the number of times the data is needed . iostat 4 5 will give data at 4 seconds interval 5 times
output description: tty – terminal , cmdk0 sd0 nfs 1 – disk , cpu
The fields have the following meanings:
disk name of the disk
r/s reads per second
w/s writes per second
Kr/s kilobytes read per second
Kw/s kilobytes written per second
wait average number of transactions waiting for service (Q length)
actv average number of transactions actively being serviced
(removed from the queue but not yet completed)
%w percent of time there are transactions waiting
for service (queue non-empty)
%b percent of time the disk is busy (transactions
in progress)
2) vmstat - vmstat reports virtual memory statistics of process, virtual memory, disk, trap, and CPU activity.
On multicpu systems , vmstat averages the number of CPUs into the output. For per-process statistics .Without options, vmstat displays a one-line summary of the virtual memory activity since the system was booted.
For details help just type
root# man vmstat

Syntax
Basic synctax is- vmstat [option] [interval] [count]
option – let you specify the type of information needed such as paging -p , cache -c ,.interrupt -i etc.
if no option is specified information about process , memory , paging , disk ,interrupts & cpu is displayed .
interval – is time period in seconds between two samples . vmstat 4 will give data at each 4 seconds interval.
count – is the number of times the data is needed . vmstat 4 5 will give data at 4 seconds interval 5 times.
The fields of vmstat's display are

kthr
-------------------------
r in run queue
b blocked for resources I/O, paging etc.
w swapped

memory (in Kbytes)
---------------------
swap - amount of swap space currently available
free - size of the free list

page ( in units per second).
---------------------------
re page reclaims - see -S option for how this field is modified.
mf minor faults - see -S option for how this field is modified.
pi kilobytes paged in
po kilobytes paged out
fr kilobytes freed
de anticipated short-term memory shortfall (Kbytes)
sr pages scanned by clock algorithm

disk ( operations per second )
-------------------------------------
There are slots for up to four disks, labeled with a single letter and number.The letter indicates the type of disk
(s = SCSI, i = IPI, etc).The number is the logical unit number.

Faults
------------
in (non clock) device interrupts
sy system calls
cs CPU context switches

Cpu
-----------------
cpu - breakdown of percentage usage of CPU time.
On multiprocessors this is an a average across all processors.
Cs process
us user time
sy system time
id idle time

3) netstat
netstat displays the contents of various network-related data structures in depending on the options selected.
For details help just type
root# man vmstat
Values to look at:
* Collisions (Collis)
* Output packets (Opkts)
* Input errors (Ierrs)
* Input packets (Ipkts)
4) mpstat for cpu (: Report per-processor or per-processor-set statistics)

for details just type
root# man mpstat


5) prstat is like top command of linux
for average load, memory ,process

average load 12 13 20
first value is last 5 minutes load average
second value is last 10 minutes load average
third value is last 15 minutes load average


6) uptime

7) prtdiag ( such as #prtdiag –v )

8) ps –ef (for process monitoring)

# ps -ef | grep -i smon (searching a process like smon)
# ps -ef | grep -i oracle
9) sar System Activity Reporter

# sar –u 1 4

-u 1 4: Comparison of CPU utilization; 1 seconds apart; 4 times.


Since system tuning involves the art of finding acceptable compromises, you need the ability see the impact of your changes on multiple subsystems. System activity reporter (SAR) programs collect system-performance information in distinct groups. Table A shows how sar groups the performance information. The first column shows the switch you give to sar in order to request that particular information group, and the second column briefly describes the information group.
Table A

Switch Performance Monitoring Group
A All monitoring groups
a File access statistics
b Buffer activity
c System call activity
d Block device activity
g Paging out activity
k Kernel memory allocation
m Message and semaphores
p Paging in activity
q CPU Run queue statistics
r Unused memory and disk pages
u CPU usage statistics (default)
v Report status of system tables
w System swapping and switching
y TTY device activity
One way you can run sar is to specify a sampling interval and the number of times you want it to run. So, if you want to check the file-access statistics every 20 seconds for the next five minutes, you'd run sar like this:This whole listing is the command? It's just the first row, isn't it? What follows is the results of the command?
$ sar -a 20 15
output details
• %usr: The percentage of time the CPU is spending on user processes, such as applications, shell scripts, or interacting with the user.
• %sys: The percentage of time the CPU is spending executing kernel tasks. In this example, the number is high, because I was pulling data from the kernel's random number generator.
• %wio: The percentage of time the CPU is waiting for input or output from a block device, such as a disk.
• %idle: The percentage of time the CPU isn't doing anything useful.

Wednesday, May 11, 2011

No bootable CD, floppy or hard disk was detected in vmware

No bootable CD, floppy or hard disk was detected.
To install an operating system, insert a bootable CD or floppy and restart the virtual
machine by clicking the Reset button.



on vmware, after creation a machine when i trying to install Solaries Operating system
it says No bootable CD, floppy or hard disk was detected.

solution of this problem
---------------------------

i select CD/DvD rom detected when power on check
physical hard drive check


but you have to careful about boot sequence of your newly created machine.
for this have to boot sequence CD rom at the first .

for this --> when you start your machine --> press F2 for BIOS setup
then go boot --> then change the sequence via shift press + .
then F10 for save .

i think after this, your problem is solve.

Tuesday, May 10, 2011

tracing an session in oracle database

tracing an session in oracle database
=====================================


dbms_system.set_sql_trace_in_session(
sid NUMBER,
serial# NUMBER,
sql_trace BOOLEAN);


exec dbms_system.set_sql_trace_in_session(2210, 1012, TRUE);

exec dbms_system.set_sql_trace_in_session(2210, 1012, FALSE);

Write a message to oracle alert log or trace file manually

Write a message to oracle alert log or trace file manually
----------------------------------------------------------

for this you should use dbms_system package .

for example :-
I want to write a warning message in the alert log or in the trace file
such as message is "Warning! your DB need Restart now"

for this:-

dbms_system.ksdwrt (dest IN BINARY_INTEGER, tst IN VARCHAR2);

I) dest valid value will be folow

if value (1) - Write to the standard trace file
if value (2) - Write to the alert log
if value (3) - Write to both files at once

II) tst value is - your target message body .

example:-
======================================


1) Write to the standard trace file
================================

SQL*Plus: Release 11.1.0.6.0 - Production on Tue May 10 15:16:20 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

SQL> conn / as sysdba
Connected.
SQL>
SQL>
SQL> exec dbms_system.ksdwrt(1,'Warning! your DB need Restart now');

PL/SQL procedure successfully completed.

SQL> select value from v$parameter where name='background_dump_dest';

VALUE
-------------------------------------------------------------------------

g:\app\administrator\diag\rdbms\halimdb\halimdb\trace

-----content of trace file
================================
-------------------------------------------------------------------------------------------
Trace file g:\app\administrator\diag\rdbms\halimdb\halimdb\trace\halimdb_ora_3648.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows XP Version V5.1 Service Pack 3
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1077M/2036M, Ph+PgF:2800M/3929M, VA:1190M/2047M
Instance name: halimdb
Redo thread mounted by this instance: 1
Oracle process number: 19
Windows thread id: 3648, image: ORACLE.EXE (SHAD)


*** 2011-05-10 15:20:11.203
*** SESSION ID:(170.83) 2011-05-10 15:20:11.203
*** CLIENT ID:() 2011-05-10 15:20:11.203
*** SERVICE NAME:(SYS$USERS) 2011-05-10 15:20:11.203
*** MODULE NAME:(sqlplus.exe) 2011-05-10 15:20:11.203
*** ACTION NAME:() 2011-05-10 15:20:11.203

Warning! your DB need Restart now
---------------------------------------------------------------------------------------------


2) Write to the alert log
==============================

sQL> exec dbms_system.ksdwrt(2,'Warning! your DB need Restart now');

PL/SQL procedure successfully completed.


-------------------------content of alert log file--------------------
Completed: alter database open
Tue May 10 11:17:05 2011
Starting background process CJQ0
Tue May 10 11:17:05 2011
CJQ0 started with pid=19, OS id=1880
Tue May 10 07:52:40 2011
Stopping background process CJQ0
Tue May 10 08:46:16 2011
[YOURAPP] A Critical error was detected in module: INIT
Tue May 10 15:22:20 2011
Warning! your DB need Restart now
------------------------------------------------------------------------


3) Write to both files at once
================================


sQL> exec dbms_system.ksdwrt(3,'Warning! your DB need Restart now');

PL/SQL procedure successfully completed.

Monday, May 9, 2011

problem to install windows XP in my laptop

if you find any problem

change sata mode to IDE from VIOS of your laptop.

then try to install

i will write later about this in details.

Welcome screen for Switching user in Microsoft windows

Welcome screen for Switching user in Microsoft windows
=======================================================

Note: To enable Fast User Switching, you must also enable the Use the Welcome screen option. This feature cannot be used if your computer is a member of a domain. To enaorble Fast User Switching:

Log on to the computer as an administrator.

Click Start, click Control Panel, and then click User Accounts.
Click Change the way users log on or off.
If it is not already selected, click to select the Use the Welcome screen check box.

NOTE: The Use Fast User Switching check box is unavailable until you click to select the Use the Welcome screen check box.
Click to select the Use Fast User Switching check box, and then click Apply Options. Quit the User Accounts tool.

Sunday, May 8, 2011

What is Row Chaining and row Migration in oracle Database ?

What is Row Chaining and row Migration in oracle Database ?
===========================================================

What is Row Chaining
--------------------

Row Chaining happens when a row is too large to fit into a single database block. For example, if you use a 8KB block size for your database and you need to insert a row of 16KB into it, Oracle will use 2/3 blocks and store the row in chain of data blocks for that segment. And Row Chaining happens only when the row is being inserted.

you can show the block size of database by

SQL> conn / as sysdba
SQL> show parameter db_block_size


What is Row Migration
---------------------

Row Migration Occurs when a row that originally fitted into one data block is updated so
that the overall row length increases, and the block's free space is already
completely filled. In this case, Oracle migrates the data for the entire row
to a new data block, assuming the entire row can fit in a new block. Oracle
preserves the original row piece of a migrated row to point to the new block
containing the migrated row: the rowid of a migrated row does not change.



How can identified Row Chaining and row migration
------------------------------------------------

there are three ways :-

1) by Analyze command
2) USER_TABLES
3) V$SYSSTAT

You can show here from follows example :-




SQL*Plus: Release 10.2.0.1.0 - Production on Sun May 8 18:06:54 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL>
SQL>
SQL> conn stlbas@juncls
Enter password:
Connected.
SQL>
SQL>
SQL> @G:\oracle_new\DB10G2\APP1\BEFTN\RDBMS\ADMIN\utlchain.sql

Table created.

SQL>
SQL> desc CHAINED_ROWS ;
Name
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

OWNER_NAME
TABLE_NAME
CLUSTER_NAME
PARTITION_NAME
SUBPARTITION_NAME
HEAD_ROWID
ANALYZE_TIMESTAMP

SQL> select count(*) from CHAINED_ROWS ;

COUNT(*)
----------
0


SQL> SELECT owner, table_name, chain_cnt
2 FROM user_tables
3 WHERE chain_cnt > 0
4* and owner = 'STLBAS'
SQL>
SQL> /

no rows selected

SQL>
SQL>
SQL>SQL> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';

no rows selected

SQL>


=====================================================================
=========From above we cannot find any row chaining====================
=========below we create a table of row chaining=======================
=====================================================================


SQL> drop table chain_row_test ;

Table dropped.

SQL>
SQL> create table chain_row_test (a varchar(4000),b varchar(4000), c varchar(4000));

Table created.

SQL>

SQL> insert into chain_row_test (a,b,c) values ( 1, rpad('*',40000000,'*'), rpad('*',2300000,'*') )
SQL>
SQL>
SQL> /

1 row created.

SQL> commit ;

Commit complete.

SQL>
SQL>
SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
0

SQL>
SQL> analyze table chain_row_test list chained rows into chained_rows ;

Table analyzed.

SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
1

SQL>
SQL> select dbms_rowid.rowid_block_number(rowid) from chain_row_test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
508711

SQL>
SQL> insert into chain_row_test (a,b,c) values ( 1, rpad('*',5620000000,'*'), rpad('*',55500000,'*') )
SQL> /

1 row created.

SQL>
SQL>
SQL> commit ;

Commit complete.

SQL>
SQL>
SQL> select dbms_rowid.rowid_block_number(rowid) from chain_row_test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
508708
508711

SQL>
SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
1

SQL>
SQL> analyze table chain_row_test list chained rows into chained_rows ;

Table analyzed.

SQL>
SQL> SELECT count(*) FROM chained_rows;

COUNT(*)
----------
3

SQL> select dbms_rowid.rowid_block_number(rowid) from chain_row_test;

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
------------------------------------
508708
508711

SQL>
SQL>
SQL>
SQL>
SQL> ANALYZE TABLE chain_row_test LIST CHAINED ROWS;

Table analyzed.

SQL> SELECT name, value FROM v$sysstat WHERE name = 'table fetch continued row';
NAME VALUE
---------------------------------------------------------------- ----------
table fetch continued row 3


SQL>
SQL>
SQL> SELECT table_name, initial_extent, num_rows, blocks, empty_blocks, avg_space,
2 chain_cnt, avg_row_len
3 FROM user_tables
4 WHERE table_name = 'CHAIN_ROW_TEST'
5 /

TABLE_NAME INITIAL_EXTENT NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
------------------------------ -------------- ---------- ---------- ------------ ---------- ---------- -----------
CHAIN_ROW_TEST 65536 5 16 3 4852 3 4826

SQL>


SQL>
SQL> EXEC dbms_stats.gather_table_stats('STLBAS','CHAIN_ROW_TEST');

PL/SQL procedure successfully completed.

SQL>



======================================================================
=================we can find which rows are chained by follows query==
======================================================================



SQL> SELECT a, SUBSTR (b, 1, 10), SUBSTR (c, 1, 10)
2 FROM chain_row_test
3 WHERE ROWID IN (SELECT head_rowid
4 FROM chained_rows);

A
-----------------------------------------------------------------------

SUBSTR(B,1 SUBSTR(C,1
---------- ----------
1
********** **********

1
********** **********

1
********** **********


SQL>
SQL>
SQL>






How to avoid and eliminate Chained/Migrated rows ?
====================================================

1-ALTER TABLE ... MOVE command.

You can MOVE the table by using ALTER TABLE ... MOVE statement that enables you to relocate data of a non partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace for which you have quota. This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE. You can also use the ALTER TABLE ... MOVE statement with the COMPRESS keyword to store the new segment using table compression.

Note : Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE,and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must bedropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected aftermoving the table.


2-Increase PCTFREE.

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement.
If the PCTFREE has been set to a low value, that means there is not enough room in the block for updates. To avoid migration,all tables that are updated should have their PCTFREE set so that there is enough space within the block for updates.You need to increase PCTFREE to avoid migrated rows. If you leave more free space available in the block for updates, then the row will have more room to grow.

3- Import/Export can be taken as an approach to eliminate the migrated rows.

4- Avoid to create tables with more than 255 columns.