|
| Friday, January 29, 2010 |
| Workflow Notification Mailer Setup |
Workflow Notification Mailer Setup
NOTIFICATION MAILER ARCHITECTURE
The main component of the Oracle Workflow Notification Mailer is the executable
WFMAIL. This is a server side program that queries the database for any pending
notifications. It then dispatches these notifications by calling sendmail for UNIX and the MAPI APIs for Microsoft Windows NT. The notification mailer also queries the local inbox for incoming messages. These messages are validated and then passed to the database for response processing. To configure Workflow Notification Mailer we have to do OS level setup (Sendmail) and Application level setup.
1. OS Level Setup
2. Application level Configuration
1. OS (Oracle Enterprise Linux 4 update 7) level Setup
At Os level we need to do bellow setups before configuring Application level
Sendmail should be installed by default when you install RedHat Linux. If it is not then you need to install the Sendmail RPM’s with the Red Hat distribution
Sendmail RPM’s (In Oracle Enterprise Linux 4 update 7)
Note: By default Oracle Enterprise Linux 4 update 7 have above all RPM’s
A. Configuring Sendmail
Edit the /etc/mail/sendmal.mc file
Look for this bellow line in this file
DAEMON_OPTIONS (`Port=smtp,Addr=127.0.0.1, Name=MTA')dn
Add 1 line with SMTP port Number and Server IP to this above line
DAEMON_OPTIONS (`Port=25,Addr=192.168.1.61, Name=MTA')dnl
Here 25 is SMTP Port Number, 192.168.1.61 is Server IP
After adding his like file look like this
DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')dnl
DAEMON_OPTIONS(`Port=25,Addr=192.168.1.61, Name=MTA')dnl
Save the file
Rebuild /etc/sendmail.cf from the revised /etc/mail/sendmail.mc
$m4 /etc/mail/sendmail.mc > /etc/mail/sendmail.cf
Restart the sendmail services
$ /etc/init.d/sendmail restart
B.Starting Sendmail
You can use the chkconfig command to get sendmail configured to start at boot time
$chkconfig sendmail on
The services should now be set at the appropriate run levels and then checked to ensure they are correct.
#chkconfig --level 2345 sendmail on
To check run level
#chkconfig --list sendmail
C. Check the sendmail services
$telnet
$telnet 192.168.1.61 25
Now sendmail is working
D. Dovecot IMAP Server
Now that the sendmail server has been setup to allow the sending of emails, we need to configure a means for the user to retrieve any emails that are waiting for them on the server. One of the packages that do this is dovecot, which handles POP and IMAP mailboxes in clear text or with link encryption (POPS and IMAPS); IMAPS is the preferred mail protocol for MUAs.
Install Dovecot RPM if not installed
E: Start the Dovecot services
Start the services from Command
$ /etc/init.d/dovecot restart
Or
Start Dovecot services from Server
Start the dovecot services then save
F.Check the Dovecot services
$telnet 192.168.1.61 143
G. Check the mails are going to your mail ID from Linux command line
$ mail –s “Hi this is from WF Server “ yourID@gmail.com
If u gets this mail to your mail Id It then send mail is working fine
H. Create DISCARD & PROCESS folders
Login to the Application User (testappl)
Under testappl user home it will be one mail folder. Under mail folder you create DISCARD & PROCESS folders
2. Application level Configuration
1. We can configure workflow notification mailer from Oracle Application Manager (OAM)
2. There are two kinds of Notification Outbound & Inbound in Workflow Mailer
3. For Outbound Notification, CM (Concurrent Manager) node should be able to connect to SMTP (Simple Mail Transfer Protocol) server/relay.
4. For Inbound Notification (Optional), CM node should be able to connect to IMAP (Internet Message Access Protocol) Server.
5. Workflow Notification Mailer in background run as Concurrent Manager (Workflow Mailer Service, Workflow Agent Listener Service)
Enable Workflow Agent Listener and Notification Mailer concurrent
programs
Schedule the Workflow Background process, Notification Mailer concurrent requests
Workflow Mailer Service, Workflow Agent Listener Service should be started
Configure Workflow Notification Mailer
Login to Oracle Application Manager
Go to Site Map
1. Click on Next
2. Click on Next
3. Here give Application User name, password and Test Mail Id
Give PROCESS and DISCARD folders (which we have created in application server) name in EMail Processing
4. Click on Next
5. Give Submit Date and time then click on Next
6. Click on Next
7. Here give user name and click on Send Test Message
Click on Next
8. Click on finish
B. STARTUP NOTIFICATION MAILER
The mailer program can be started from command line or through a concurrent
Program called "Notification Mailer". When the notification mailer is started as a concurrent request in Oracle Applications, the OS process is run by the Applications Manager (APPLMGR) user.
To start it from the application, submit the "Notification Mailer" Concurrent request.
To start it from command line on UNIX, execute the following command:
Go to FND_TOP/bin/
$WFMAIL apps/Pass 0 Y $FND_TOP/resource/wfmail.cfg
Log file for Workflow Mailer Notification are at $APPLCSF/$APPLLOG/
Happy Learning
G.Venugopal AchariLabels: Workflow Notification Mailer Setup |
posted by Srinivasan .R @ 1:26 AM  |
|
|
|
| Monday, August 3, 2009 |
| Data Dictionary Info |
Data Dictionary Info
Here are some scripts related to Data Dictionary Info .
Dictionary Cache
DATA DICTIONARY CACHE NOTES:
Parameter - Name of the parameter that determines the number of entries in the data dictionary cache.
Gets - Total number of requests for information on the data object.
Getmisses - Number of data requests resulting in cache misses.
% Cache Misses - Miss Ratio
Count - Total number of entries in the cache
Usage - Number of cache entries that contain valid data.
This report shows the DC cache statistics (a part of the shared pool). Whenever the database parses a SQL statement, it scans the text for syntax and semantic correctness. The semantic check requires cross-referencing of the information in the SQL statement and the data dictionary, including the table names, columns-to-date relationships, column names, data types, and security access privileges. To resolve the relationships, Oracle uses the data dictionary cache in the SGA. When the data sought in not in the cache, Oracle executes SQL statements to retrieve the data dictionary information from the SYSTEM tablespace. These statements for data dictionary information represent one type of recursive SQL statement. To increase the size available to the dictionary cache, increase the size of the shared pool area (via the SHARED_POOL_SIZE init.ora parameter.) Missing a get on the data dictionary or shared pool area of the SGA is more costly than missing a get on a data buffer or waiting for a redo buffer. If these parameters look familiar - you are probably recalling them from Oracle v6 (when you tuned these in the init.ora file)
select PARAMETER,
GETS,
GETMISSES,
round(GETMISSES/GETS,2)*100 "% Cache Misses",
COUNT,
USAGE
from v$rowcache
where GETS > 0
order by (GETMISSES/GETS)*100 desc
Latch Gets/Misses
LATCH GET AND MISS NOTES:
Latch Name - Name of the latch
Gets - Number of times obtained wait
Misses - Number of time obtained with wait but failed first try
Gets / Misses % - Ratio of misses to gets
Immediate Gets - Number of times obtained with no wait
Immediate Misses - Number of times failed to get with no wait
select NAME,
GETS,
MISSES,
round(((GETS-MISSES)*100) / GETS , 2) "Gets/Misses %",
IMMEDIATE_GETS,
IMMEDIATE_MISSES
from v$latch
where GETS != 0
or IMMEDIATE_MISSES > 0
order by ((GETS-MISSES) / GETS) descLabels: Data Dictionary Info |
| posted by Srinivasan .R @ 11:37 PM |
|
|
|
|
| Redo Log Buffer |
Redo Log Buffer
Here are some scripts related to Redo Log Buffer .
Contention
REDO LOG CONTENTION NOTES:
Redo Latch Name - Name of the latch
Gets - Number of times gotten wait
Misses - Number of times gotten wait but failed first try
Sleeps - Number of times slept when wanted wait
Immediate Gets - Number of times gotten without wait
Immediate Misses - Number of times failed to get without wait
Heavy access to the redo log buffer can result in contention for redo log prompt buffer latches.
select NAME,
GETS,
MISSES,
SLEEPS,
IMMEDIATE_GETS,
IMMEDIATE_MISSES
from v$latch
where NAME in ('redo allocation','redo copy')
Statistics
REDO BUFFER NOTES:
Parameter - Name of the parameter
Value - Current value for the parameter
select NAME,
VALUE
from v$sysstat
where NAME like 'redo%'
and VALUE > 0Labels: Redo Log Buffer |
| posted by Srinivasan .R @ 11:24 PM |
|
|
|
|
| Contention/Locking |
Contention/Locking
Here are some scripts related to Contention/Locking .
Lock Info
LOCK INFORMATION NOTES:
OS User - Name of operating system user
OS PID - Operating system process id
Oracle User - Name of Oracle user
Oracle ID - Oracle session id
Lock Type - Type of lock
Lock Held - Current lock held
Lock Requested - Type of lock requested
Status - Status of object (Blocking, Not Blocking, Global)
Object Owner - Owner of the object
Object Name - Name of the object
select OS_USER_NAME os_user,
PROCESS os_pid,
ORACLE_USERNAME oracle_user,
l.SID oracle_id,
decode(TYPE,
'MR', 'Media Recovery',
'RT', 'Redo Thread',
'UN', 'User Name',
'TX', 'Transaction',
'TM', 'DML',
'UL', 'PL/SQL User Lock',
'DX', 'Distributed Xaction',
'CF', 'Control File',
'IS', 'Instance State',
'FS', 'File Set',
'IR', 'Instance Recovery',
'ST', 'Disk Space Transaction',
'TS', 'Temp Segment',
'IV', 'Library Cache Invalidation',
'LS', 'Log Start or Switch',
'RW', 'Row Wait',
'SQ', 'Sequence Number',
'TE', 'Extend Table',
'TT', 'Temp Table', type) lock_type,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', lmode) lock_held,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive', request) lock_requested,
decode(BLOCK,
0, 'Not Blocking',
1, 'Blocking',
2, 'Global', block) status,
OWNER,
OBJECT_NAME
from v$locked_object lo,
dba_objects do,
v$lock l
where lo.OBJECT_ID = do.OBJECT_ID
AND l.SID = lo.SESSION_ID
SQL Lock Info
SQL ASSOCIATED WITH LOCK NOTES:
Oracle User - Name of the oracle user
SID - Oracle session id
Serial# - Serial# of the process
Type - Resource type (RW - Row wait enqueue lock, TM - DML enqueue lock, TX - Transaction enqueue lock, UL - User supplied lock)
Held - Type of lock held
Requested - Type of lock requested
ID1 - Resource identifier #1
ID2 - Resource identifier #2
SQL - SQL statement
select sn.USERNAME,
m.SID,
sn.SERIAL#,
m.TYPE,
decode(LMODE,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_type,
decode(REQUEST,
0, 'None',
1, 'Null',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share',
5, 'S/Row-X (SSX)',
6, 'Exclusive') lock_requested,
m.ID1,
m.ID2,
t.SQL_TEXT
from v$session sn,
v$lock m ,
v$sqltext t
where t.ADDRESS = sn.SQL_ADDRESS
and t.HASH_VALUE = sn.SQL_HASH_VALUE
and ((sn.SID = m.SID and m.REQUEST != 0)
or (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
(select s.ID1, s.ID2
from v$lock S
where REQUEST != 0
and s.ID1 = m.ID1
and s.ID2 = m.ID2)))
order by sn.USERNAME, sn.SID, t.PIECELabels: Contention/Locking |
| posted by Srinivasan .R @ 11:20 PM |
|
|
|
|
| Full Table Scans |
Full Table Scans
Here are some scripts related to Full Table Scans .
System Statistics (Table)SYSTEM STATISTICS (TABLE) NOTES:
Statistic Name - Name of the statistic
Bytes - Size
This query provides information on the full table scan activity. If your application is OLTP only, having long full table scans can be an indicator of having missing or incorrect indexes or untuned SQL.
Table fetch by rowid reflect the cumulative number of rows fetched from tables using a TABLE ACCESS BY ROWID operation.
Table fetch continued row reflect the cumulative number of continued rows fetched. This value is incremented when accessing a row that is longer than a block in length and when accessing "migrated" rows. Migrated rows are rows that were relocated from their original location to a new location because of an update that increased their size to the point where they could no longer be accommodated inside their original block. Access to migrated rows will cause this statistic's value to increment only if the access is performed by ROWID. Full table scans of tables that contain migrated rows do not cause this counter to increment.
Table scan blocks gotten reflect the cumulative number of blocks read for full table scans.
Table scans rows gotten reflect the cumulative number of rows read for full table scans.
Table scans (cache partitions) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (long scans) indicate a full scan of a table that has > 5 database blocks.
Table scans (rowid ranges) is used with the Parallel Query Option. The number of RowID ranges corresponds to the number of simultaneous query server processes that scan the table.
Table scans (short scans) indicate a full scan of a table that has <= 5 database blocks.
select NAME,
VALUE
from v$sysstat
where NAME like '%table'
Process Table Scans
PROCESS TABLE SCAN NOTES:
User Process - Name of user process
Long Scans - Full scan of a table that has > 5 database blocks.
Short Scans - Full scan of a table that has <= 5 database blocks.
Row Retrieved - Cumulative number of rows read for full table scans.
select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
Process Table Scans (Avg)
PROCESS TABLE SCAN (AVERAGE) NOTES:
User Process - Name of user process
Short Scans - Number of short scans (<= 5 blocks)
Long Scans - Number of long scans (> 5 blocks)
Rows Retrieved - Number of rows retrieved
Long Scans Length - Average long scan length (i.e. full table scan of > 5 blocks)
select ss.username||'('||se.sid||') ' "User Process",
sum(decode(name,'table scans (short tables)',value)) "Short Scans",
sum(decode(name,'table scans (long tables)', value)) "Long Scans",
sum(decode(name,'table scan rows gotten',value)) "Rows Retreived",
round((sum(decode(name,'table scan rows gotten',value)) - (sum(decode(name,'table scans (short tables)',value)) * 5)) / (sum(decode(name,'table scans (long tables)', value))),2) "Long Scans Length"
from v$session ss,
v$sesstat se,
v$statname sn
where se.statistic# = sn.statistic#
and (name like '%table scans (short tables)%'
or name like '%table scans (long tables)%'
or name like '%table scan rows gotten%')
and se.sid = ss.sid
and ss.username is not null
group by ss.username||'('||se.sid||') '
having sum(decode(name,'table scans (long tables)', value)) != 0
order by 3 descLabels: Full Table Scans |
| posted by Srinivasan .R @ 11:15 PM |
|
|
|
| Thursday, June 25, 2009 |
| SENDMAIL configuration |
SENDMAIL Configuration tested on RedHat 4 update 5
-------------------------------------------------------------------
Follow the below link:
http://www.owlriver.com/tips/sendmail-tip/
1> Apply the patch sendmail-cf-8.13.1-3.2.el4.i386.rpm which is available in 3rd cd
rpm -Uvh sendmail-cf-8.13.1-3.2.el4.i386.rpm
2> To be able to hand email TO it (on port 25), you will need packages:
rpm -q sendmail-cf m4
3> If not present, use your install media and install them
4> Edit /etc/mail/sendmail.mc with your favorite text editor. Toward the bottom of that
file is this stanza:
dnl This changes sendmail to only listen on the loopback device 127.0.0.1
dnl and not on any other network devices. Comment this out if you want
dnl to accept email over the network.
DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')
... Note that the string "dnl " at the left margin is how comments are added to
sendmail.mc -- this is an artifact of the ancient origins of sendmail and the m4 macro
processor
Make it read as the text says "to accept email over the network":
dnl DAEMON_OPTIONS(`Port=smtp,Addr=127.0.0.1, Name=MTA')
(this is the last line quoted in the first stanza, now commented out.)
4. rebuild /etc/sendmail.cf from the revised /etc/mail/sendmail.mc thus:
m4 /etc/mail/sendmail.mc > /etc/sendmail.cf
(This is considered Unix deep magic -- but all it does is have the m4 macro processor use
the sendmail-cf recipies, and make a new well-formed sendmail.cf. Note that lower in the
sendmail.mc file is an option to specify what hostname your mail server uses -- useful for
pretending to be a server with a resolvable name.)
(Note also that /etc/sendmail.cf moves to become /etc/mail/sendmail.cf with sendmail-
8.12.x and so that command will become m4 /etc/mail/sendmail.mc >
/etc/mail/sendmail.cf in later versions. But there are many other changes, including a
separate non-privleged child process.)
5. Tell sendmail to listen all the time (in so-called daemon mode) instead of delivering
just its own local mail queue just once an hour.
Edit /etc/sysconfig/sendmail
and change:
DAEMON=no
to:
DAEMON=yes
6. And restart sendmail
cd /etc/mail
make
service sendmail restart
(Note: The make step is needed in some variants of Red Hat, as the automatic execution
of the make is missing in some releases of the initscript package.)
7. Confirm it locally:
netstat -an | grep 25 | grep tcp
yields:
tcp 0 0 0.0.0.0:25 0.0.0.0:* LISTEN
(which says the host is listening on ALL interfaces for connections ...)
[I undid step 5 and 6 -- and reran this test -- it yields -- nothing -- that it is NOT able to be
connected to externally on port 25 -- and so THAT port is safe from a remote direct
attack.]
8. Test locally (I list YOUR part OUT of []):
telnet localhost 25
[... banner]
HELO LOCALHOST
[... response]
QUIT
It went:
[root@couch herrold]# telnet localhost 25
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
220 couch.basement.net ESMTP Sendmail 8.11.6/8.11.6; Sat, 17
Nov 2001 17:10:39 -0500
HELO localhost
250 couch.basement.net Hello
IDENT:+yf0DRAJjMc1ZxKCEIv/Y7hCZQtbC55G@couch.basement.net
[127.0.0.1], pleased to meet you
QUIT
221 2.0.0 couch.basement.net closing connection
Connection closed by foreign host.
$
9. The acid test -- From another host test it (we'll restate the conversation, and hide the
remote end's responses initially for clarity):
telnet couch.basement.net 25
HELO localhost
QUIT
Or more elaborately (I highlight your input in red):
[herrold@router herrold]$ telnet couch.basement.net 25
Trying 172.16.33.101...
Connected to 172.16.33.101.
Escape character is '^]'.
220 couch.basement.net ESMTP Sendmail 8.11.6/8.11.6; Sat, 17
Nov 2001 17:36:05 -0500
HELO localhost
250 couch.basement.net Hello router.basement.net
[172.16.33.2], pleased to meet you
MAIL FROM:
553 5.5.4 godzilla@localhost... Real domain name required for sender address
MAIL FROM:
250 2.1.0 godzilla@microsoft.com... Sender ok
RCPT TO:
250 2.1.5 root@localhost... Recipient ok
DATA
354 Enter mail, end with "." on a line by itself
Linux rules.
.
250 2.0.0 fAHMajY07056 Message accepted for delivery
QUIT
221 2.0.0 couch.basement.net closing connection
Connection closed by foreign host.
[herrold@router herrold]$
.... that was successful -- note that the 'domain must resolve' rule for senders kicked in on
the first part of the "MAIL FROM" offer -- this is an anti-spam feature. [Note the name
look up's working -- I have working residence DHCP and DNS services on Linux ... ].
The X- header is added by procmail.
There is a forward rule in place for root, as recommended in an earlier post; Out at the
end recipient host, we find:
Return-Path:
Received: from couch.basement.net
(dhcp065-024-xx-xx.columbus.rr.com [65.24.xx.xx])
by swampfox.owlriver.com (8.11.6/8.11.6) with ESMTP id
fAHMbGu07271 for ; Sat, 17 Nov
2001 17:37:16 -0500
Received: from localhost (router.basement.net [172.16.33.2])
by couch.basement.net (8.11.6/8.11.6) with SMTP id
fAHMajY07056
for root@localhost; Sat, 17 Nov 2001 17:37:06 -0500
Date: Sat, 17 Nov 2001 17:37:06 -0500
From: godzilla@microsoft.com
Message-Id: <200111172237.fAHMajY07056@couch.basement.net>
X-Loop: herrold@owlriver.com
Linux rules.
Read the received lines from the bottom up --
We forged the sender as "godzilla@microsoft.com" -- spammers do this all the time,
nothing new there; and offered to root@localhost -- The forward rule handed it for
delivery to herrold@owlriver.com. Seems to work fine
As this demonstrated, this is probably not enough to suffice in production. Do NOT
connect this to the outside world -- a bit more needs to be done --- As set, it will receive
mail only with a final (as to the originator's perspective) destination at the localhost --
/etc/mail/access and /etc/mail/local-host-names need attention to receive mail for
handling for others. But again, this is beyond the scope of this presentation.
10> To send a mail
sendmail –v sriasan@gmail.com – use gmail, I had spam issues with yahoo.com
this is to test sendmail
ctrl –d or .
check gmail for the mailLabels: SENDMAIL configuration |
| posted by Srinivasan .R @ 6:03 AM |
|
|
|
| Monday, June 22, 2009 |
| Rollback Segments |
Rollback Segments
Here are some scripts related to Rollback Segments .
Segments
ROLLBACK INFORMATION NOTES:
Segment Name - Name of the rollback segment.
Owner - Owner of the rollback segment.
Tablespace - Name of the tablespace containing the rollback segment.
Segment ID - ID number of the rollback segment.
File ID - ID number of the block containing the segment header.
Block ID - Starting block number of the extent.
Initial Extent - Initial extent size in bytes.
Next Extent - Secondary extent size in bytes.
Min Extents - Minimum number of extents.
Max Extents - Maximum number of extents.
PCT Increase - Percent increase for extent size.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
Instance - Instance this rollback segment belongs to (Parallel Server), or NULL for a single-instance system .
select SEGMENT_NAME,
OWNER,
TABLESPACE_NAME,
SEGMENT_ID,
FILE_ID,
BLOCK_ID,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
INSTANCE_NUM
from dba_rollback_segs
order by SEGMENT_NAME
Transactions
ROLLBACK STATISTIC (TRANSACTION TABLES) NOTES:
Statistic Name - Name of the statistic
Value - Current value
The name of the consistent changes statistic is misleading. It does not indicate the number of updates (or changes), but rather, the number of times a consistent get had to retrieve and "old" version of a block because of updates that occurred after the cursor had been opened. As of Oracle7.3, a more accurate statistic was added. Named data blocks consistent reads - undo records applied; the new statistic gives the actual number of data records applied.
The consistent gets statistic reflects the number of accesses made to the block buffer to retrieve data in a consistent mode. Most accesses to the buffer are done with the consistent get mechanism, which uses the SCN (System Change Number) to make sure the data being read has not changed sine the query was started.
The data blocks consistent reads - undo records applied statistic reflects the number of updates (or changes) applied.
select NAME,
VALUE
from v$sysstat
where name in (
'consistent gets',
'consistent changes',
'transaction tables consistent reads - undo records applied',
'transaction tables consistent read rollbacks',
'data blocks consistent reads - undo records applied',
'no work - consistent read gets',
'cleanouts only - consistent read gets',
'rollbacks only - consistent read gets',
'cleanouts and rollbacks - consistent read gets')
order by NAME
Contention
ROLLBACK CONTENTION NOTES:
Segment Name - Name of the rollback segment.
Seg# - Rollback segment number.
Gets - Number of header gets.
Waits - Number of header waits.
Hit Ratio - Ratio of gets to waits. This should be >= 99%.
Active Transactions - Number of active transactions.
Writes - Number of bytes written to rollback segment.
Hit Ratio should be >= 99% - if not, consider adding additional rollback segments.
Check the system undo header, system undo block, undo header, undo block statistics under "Wait Statistics" for additional information on rollback contention.
select b.NAME,
a.USN seg#,
GETS,
WAITS,
round(((GETS-WAITS)*100)/GETS,2) hit_ratio,
XACTS active_transactions,
WRITES
from v$rollstat a,
v$rollname b
where a.USN = b.USN
Growth
ROLLBACK EXTENDING AND SHRINKAGE NOTES:
Rollback Segment - Name of rollback segment.
Seg# - Rollback segment number.
Size - Size in bytes of the rollback segment.
OptSize - Optimal size of rollback segment.
HWM - High Water Mark of rollback segment size.
Extends - Number of times rollback segment was extended to have a new extent.
Wraps - Number of times rollback segment wraps from one extent to another.
Shrinks - Number of times rollback segment shrank, eliminating one or more additional extents each time.
Average Shrink - Total size of freed extents divided by number of shrinks.
Average Active - Current average size of active extents, where "active" extents have uncommitted transaction data.
Status - ONLINE if the segment is online, or PENDING OFFLINE if the segment is going offline but some active (distributed) transactions are using the rollback segment. When the transaction(s) complete, the segment goes OFFLINE.
select NAME,
a.USN,
RSSIZE,
OPTSIZE,
HWMSIZE,
EXTENDS,
WRAPS,
SHRINKS,
AVESHRINK,
AVEACTIVE,
STATUS
from v$rollstat a ,
v$rollname b
where a.USN=b.USN
order by NAMELabels: Rollback Segments |
| posted by Srinivasan .R @ 1:59 AM |
|
|
|
|
|
