|
| 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 |
|
|
|
|
| User Information |
User Information
Here are some scripts related to User Information .
User Objects
USER OBJECT NOTES:
Username - Owner of the object(s)
Tabs - Table(s)
Inds - Index(es)
Syns - Synonym(s)
Views - Views(s)
Seqs - Sequence(s)
Procs - Procedure(s)
Funcs - Function(s)
Pkgs - Packages(s)
Trigs - Trigger(s)
Deps - Dependencies
select USERNAME,
count(decode(o.TYPE#, 2,o.OBJ#,'')) Tabs,
count(decode(o.TYPE#, 1,o.OBJ#,'')) Inds,
count(decode(o.TYPE#, 5,o.OBJ#,'')) Syns,
count(decode(o.TYPE#, 4,o.OBJ#,'')) Views,
count(decode(o.TYPE#, 6,o.OBJ#,'')) Seqs,
count(decode(o.TYPE#, 7,o.OBJ#,'')) Procs,
count(decode(o.TYPE#, 8,o.OBJ#,'')) Funcs,
count(decode(o.TYPE#, 9,o.OBJ#,'')) Pkgs,
count(decode(o.TYPE#,12,o.OBJ#,'')) Trigs,
count(decode(o.TYPE#,10,o.OBJ#,'')) Deps
from obj$ o,
dba_users u
where u.USER_ID = o.OWNER# (+)
group by USERNAME
order by USERNAME
Invalid Objects
INVALID OBJECT NOTES:
Owner - Owner of the object
Object Type - Type of object
Object Name - Name of the object
Status - Status of the object
select OWNER,
OBJECT_TYPE,
OBJECT_NAME,
STATUS
from dba_objects
where STATUS = 'INVALID'
order by OWNER, OBJECT_TYPE, OBJECT_NAME
Object Modification
OBJECT MODIFICATION NOTES: (Modified in last 7 days)
Owner - Owner of the object
Object Name - Name of the object
Object Type - Type of the object
Last Modified - Last modification date/time
Created - Object creation date/time
Status - Status of the object
select OWNER,
OBJECT_NAME,
OBJECT_TYPE,
to_char(LAST_DDL_TIME,'MM/DD/YYYY HH24:MI:SS') last_modified,
to_char(CREATED,'MM/DD/YYYY HH24:MI:SS') created,
STATUS
from dba_objects
where (SYSDATE - LAST_DDL_TIME) < 7
order by LAST_DDL_TIME DESC
User Privileges
USER PRIVILEGES NOTES:
Grantee - Grantee name, user or role receiving the grant
Granted Role - Granted role name
Admin - Grant was with the ADMIN option
Default - Role is designated as a DEFAULT ROLE for the user
Privilege - System privilege
select rp.GRANTEE,
GRANTED_ROLE,
rp.ADMIN_OPTION,
DEFAULT_ROLE,
PRIVILEGE
from dba_role_privs rp, dba_sys_privs sp
where rp.GRANTEE = sp.GRANTEE
and rp.GRANTEE not in ('SYS','SYSTEM','DBA')
order by rp.GRANTEE, GRANTED_ROLE, PRIVILEGELabels: User Information |
| posted by Srinivasan .R @ 1:20 AM |
|
|
|
|
| Hit/Miss Ratios |
Hit/Miss Ratios
Here are some scripts related to Hit/Miss Ratios .
Buffer Hit Ratio
BUFFER HIT RATIO NOTES:
Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.
Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit Ratio should be > 80%, else increase DB_BLOCK_BUFFERS in init.ora
select sum(decode(NAME, 'consistent gets',VALUE, 0)) "Consistent Gets",
sum(decode(NAME, 'db block gets',VALUE, 0)) "DB Block Gets",
sum(decode(NAME, 'physical reads',VALUE, 0)) "Physical Reads",
round((sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0)) -
sum(decode(name, 'physical reads',value, 0))) /
(sum(decode(name, 'consistent gets',value, 0)) +
sum(decode(name, 'db block gets',value, 0))) * 100,2) "Hit Ratio"
from v$sysstat
Data Dict Hit Ratio
DATA DICTIONARY HIT RATIO NOTES:
Gets - Total number of requests for information on the data object.
Cache Misses - Number of data requests resulting in cache misses
Hit Ratio should be > 90%, else increase SHARED_POOL_SIZE in init.ora
select sum(GETS),
sum(GETMISSES),
round((1 - (sum(GETMISSES) / sum(GETS))) * 100,2)
from v$rowcache
SQL Cache Hit Ratio
SQL CACHE HIT RATIO NOTES:
Pins - The number of times a pin was requested for objects of this namespace.
Reloads - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.
Hit Ratio should be > 85%
select sum(PINS) Pins,
sum(RELOADS) Reloads,
round((sum(PINS) - sum(RELOADS)) / sum(PINS) * 100,2) Hit_Ratio
from v$librarycache
Library Cache Miss Ratio
LIBRARY CACHE MISS RATIO NOTES:
Executions - The number of times a pin was requested for objects of this namespace.
Cache Misses - Any pin of an object that is not the first pin performed since the object handle was created, and which requires loading the object from disk.
Hit Ratio should be < 1%, else increase SHARED_POOL_SIZE in init.ora
select sum(PINS) Executions,
sum(RELOADS) cache_misses,
sum(RELOADS) / sum(PINS) miss_ratio
from v$librarycacheLabels: Hit/Miss Ratios |
| posted by Srinivasan .R @ 1:12 AM |
|
|
|
| Friday, June 19, 2009 |
| Session Statistics |
Session Statistics
Here are some scripts related to Session Statistics .
Session I/O By User
SESSION I/O BY USER NOTES:
Username - Name of the Oracle process user
OS User - Name of the operating system user
PID - Process ID of the session
SID - Session ID of the session
Serial# - Serial# of the session
Physical Reads - Physical reads for the session
Block Gets - Block gets for the session
Consistent Gets - Consistent gets for the session
Block Changes - Block changes for the session
Consistent Changes - Consistent changes for the session
select nvl(ses.USERNAME,'ORACLE PROC') username,
OSUSER os_user,
PROCESS pid,
ses.SID sid,
SERIAL#,
PHYSICAL_READS,
BLOCK_GETS,
CONSISTENT_GETS,
BLOCK_CHANGES,
CONSISTENT_CHANGES
from v$session ses,
v$sess_io sio
where ses.SID = sio.SID
order by PHYSICAL_READS, ses.USERNAME
CPU Usage By Session
CPU USAGE BY SESSION NOTES:
Username - Name of the user
SID - Session id
CPU Usage - CPU centiseconds used by this session (divide by 100 to get real CPU seconds)
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
VALUE cpu_usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and NAME like '%CPU used by this session%'
and se.SID = ss.SID
order by VALUE desc
Resource Usage By User
RESOURCE USAGE BY USER NOTES:
SID - Session ID
Username - Name of the user
Statistic - Name of the statistic
Value - Current value
select ses.SID,
nvl(ses.USERNAME,'ORACLE PROC') username,
sn.NAME statistic,
sest.VALUE
from v$session ses,
v$statname sn,
v$sesstat sest
where ses.SID = sest.SID
and sn.STATISTIC# = sest.STATISTIC#
and sest.VALUE is not null
and sest.VALUE != 0
order by ses.USERNAME, ses.SID, sn.NAME
Session Stats By Session
SESSION STAT NOTES:
Username - Name of the user
SID - Session ID
Statistic - Name of the statistic
Usage - Usage according to Oracle
select nvl(ss.USERNAME,'ORACLE PROC') username,
se.SID,
sn.NAME stastic,
VALUE usage
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and se.SID = ss.SID
and se.VALUE > 0
order by sn.NAME, se.SID, se.VALUE desc
Cursor Usage By Session
CURSOR USAGE BY SESSION NOTES:
Username - Name of the user
Recursive Calls - Total number of recursive calls
Opened Cursors - Total number of opened cursors
Current Cursors - Number of cursor currently in use
select user_process username,
"Recursive Calls",
"Opened Cursors",
"Current Cursors"
from (
select nvl(ss.USERNAME,'ORACLE PROC')||'('||se.sid||') ' user_process,
sum(decode(NAME,'recursive calls',value)) "Recursive Calls",
sum(decode(NAME,'opened cursors cumulative',value)) "Opened Cursors",
sum(decode(NAME,'opened cursors current',value)) "Current Cursors"
from v$session ss,
v$sesstat se,
v$statname sn
where se.STATISTIC# = sn.STATISTIC#
and (NAME like '%opened cursors current%'
or NAME like '%recursive calls%'
or NAME like '%opened cursors cumulative%')
and se.SID = ss.SID
and ss.USERNAME is not null
group by nvl(ss.USERNAME,'ORACLE PROC')||'('||se.SID||') '
)
orasnap_user_cursors
order by USER_PROCESS,"Recursive Calls"
User Hit Ratios
USER HIT RATIO NOTES:
Username - Name of the user
Consistent Gets - The number of accesses made to the block buffer to retrieve data in a consistent mode.
DB Blk Gets - The number of blocks accessed via single block gets (i.e. not through the consistent get mechanism).
Physical Reads - The cumulative number of blocks read from disk.
Logical reads are the sum of consistent gets and db block gets.
The db block gets statistic value is incremented when a block is read for update and when segment header blocks are accessed.
Hit ratio should be > 90%
select USERNAME,
CONSISTENT_GETS,
BLOCK_GETS,
PHYSICAL_READS,
((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS)) 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 ((CONSISTENT_GETS+BLOCK_GETS-PHYSICAL_READS) / (CONSISTENT_GETS+BLOCK_GETS))Labels: Session Statistics |
| posted by Srinivasan .R @ 1:56 AM |
|
|
|
|
| Tablespace Information |
Tablespace Information
Here are some scripts related to Tablespace Information .
Information
TABLESPACE INFORMATION NOTES:
Tablespace Name - Name of the tablespace
Initial Extent - Default initial extent size
Next Extent - Default incremental extent size
Min Extents - Default minimum number of extents
Max Extents - Default maximum number of extents
PCT Increase - Default percent increase for extent size
Status - Tablespace status: ONLINE, OFFLINE, or INVALID (tablespace has been dropped)
Contents - Type of tablespace. This column will have 'TEMPORARY' (v7.3+) for dedicated temporary tablespaces, and 'PERMANENT' for tablespaces that can store both temporary sort segments and permanent objects.
select TABLESPACE_NAME,
INITIAL_EXTENT,
NEXT_EXTENT,
MIN_EXTENTS,
MAX_EXTENTS,
PCT_INCREASE,
STATUS,
CONTENTS
from dba_tablespaces
order by TABLESPACE_NAME
Coalesced Exts
WAIT STATISTIC NOTES:
Tablespace Name - Name of tablespace
Total Extents - Total number of free extents in tablespace
Extents Coalesced - Total number of coalesced free extents in tablespace
% Extents Coalesced - Percentage of coalesced free extents in tablespace
Total Bytes - Total number of free bytes in tablespace
Bytes Coalesced - Total number of coalesced free bytes in tablespace
Total Blocks - Total number of free oracle blocks in tablespace
Blocks Coalesced - Total number of coalesced free Oracle blocks in tablespace
% Blocks Coalesced - Percentage of coalesced free Oracle blocks in tablespace
select TABLESPACE_NAME,
TOTAL_EXTENTS,
EXTENTS_COALESCED,
PERCENT_EXTENTS_COALESCED,
TOTAL_BYTES,
BYTES_COALESCED,
TOTAL_BLOCKS,
BLOCKS_COALESCED,
PERCENT_BLOCKS_COALESCED
from dba_free_space_coalesced
order by TABLESPACE_NAME
Usage
TABLESPACE USAGE NOTES:
Tablespace Name - Name of the tablespace
Bytes Used - Size of the file in bytes
Bytes Free - Size of free space in bytes
Largest - Largest free space in bytes
Percent Used - Percentage of tablespace that is being used - Careful if it is more than 85%
select a.TABLESPACE_NAME,
a.BYTES bytes_used,
b.BYTES bytes_free,
b.largest,
round(((a.BYTES-b.BYTES)/a.BYTES)*100,2) percent_used
from
(
select TABLESPACE_NAME,
sum(BYTES) BYTES
from dba_data_files
group by TABLESPACE_NAME
)
a,
(
select TABLESPACE_NAME,
sum(BYTES) BYTES ,
max(BYTES) largest
from dba_free_space
group by TABLESPACE_NAME
)
b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME
order by ((a.BYTES-b.BYTES)/a.BYTES) desc
Users Default (SYSTEM)
SYSTEM TABLESPACE USAGE NOTES:
Username - Name of the user
Created - User creation date
Profile - Name of resource profile assigned to the user
Default Tablespace - Default tablespace for data objects
Temporary Tablespace - Default tablespace for temporary objects
Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.
select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME
Objects in SYSTEM TS
OBJECTS IN SYSTEM TABLESPACE NOTES:
Owner - Owner of the object
Object Name - Name of object
Object Type - Type of object
Tablespace - Tablespace name
Size - Size (bytes) of object
Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME
Freespace/Largest Ext
FREE, LARGEST, & INITIAL NOTES:
Tablespace - Name of the tablespace
Total Free Space - Total amount (bytes) of freespace in the tablespace
Largest Free Extent - Largest free extent (bytes) in the tablespace
select TABLESPACE_NAME,
sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space
group by TABLESPACE_NAMELabels: Tablespace Information |
| posted by Srinivasan .R @ 12:54 AM |
|
|
|
|
| Disk I/O, Events, Waits |
Disk I/O, Events, Waits
Here are some scripts related to Disk I/O, Events, Waits .
Datafile I/O
DATAFILE I/O NOTES:
File Name - Datafile name
Physical Reads - Number of physical reads
Reads % - Percentage of physical reads
Physical Writes - Number of physical writes
Writes % - Percentage of physical writes
Total Block I/O's - Number of I/O blocks
Use this report to identify any "hot spots" or I/O contention
select NAME,
PHYRDS "Physical Reads",
round((PHYRDS / PD.PHYS_READS)*100,2) "Read %",
PHYWRTS "Physical Writes",
round(PHYWRTS * 100 / PD.PHYS_WRTS,2) "Write %",
fs.PHYBLKRD+FS.PHYBLKWRT "Total Block I/O's"
from (
select sum(PHYRDS) PHYS_READS,
sum(PHYWRTS) PHYS_WRTS
from v$filestat
) pd,
v$datafile df,
v$filestat fs
where df.FILE# = fs.FILE#
order by fs.PHYBLKRD+fs.PHYBLKWRT desc
SGA Stats
SGA STAT NOTES:
Statistic Name - Name of the statistic
Bytes - Size
select NAME,
BYTES
from v$sgastat
order by NAME
Sort Stats
SORT NOTES:
Sort Parameter - Name of the sort parameter
Value - Number of sorts
sorts (memory) - The number of sorts small enough to be performed entirely in sort areas without using temporary segments.
sorts (disk) - The number of sorts that were large enough to require the use of temporary segments for sorting.
sorts (rows) - Number of sorted rows
The memory area available for sorting is set via the SORT_AREA_SIZE and SORT_AREA_RETAINED_SIZE init.ora parameters.
select NAME,
VALUE
from v$sysstat
where NAME like 'sort%'
All Events
SYSTEM EVENT (ALL) NOTES:
Event Name - Name of the event
Total Waits - Total number of waits for the event
Total Timeouts - Total number of timeouts for the event
Time Waited - The total amount of time waited for this event, in hundredths of a second
Average Wait - The average amount of time waited for this event, in hundredths of a second
select EVENT,
TOTAL_WAITS,
TOTAL_TIMEOUTS,
TIME_WAITED,
round(AVERAGE_WAIT,2) "Average Wait"
from v$system_event
order by TOTAL_WAITS
All Statistics
SYSTEM STATISTICS (ALL) NOTES:
Stat# - Number of the statistic
Name - Name of the statistic
Class - Statistic class: 1 (User), 2 (Redo), 4 (Enqueue), 8 (Cache), 16 (OS), 32 (Parallel Server), 64 (SQL), 128 (Debug)
Value - Value of the statistic
select STATISTIC#,
NAME,
CLASS,
VALUE
from v$sysstat
Wait Stats
WAIT STATISTIC NOTES:
Class - Class of block subject to contention
Count - Number of waits by this OPERATION for this CLASS of block
Time -Sum of all wait times for all the waits by this OPERATION for this CLASS of block
Data Blocks - Usually occurs when there are too many modified blocks in the buffer cache; reduce contention by adding DBWR processes.
Free List - May occur if multiple data loading programs run simultaneously.
Segment Header - May occur when may full table scans execute simultaneously with data loading processes; aggravated by the parallel options. Reschedule data loading jobs to reduce contention;
Sort Block - Rarely seen except when the Parallel Query option is used; reduce contention by reducing the degree of parallelism or decreasing the SORT_AREA_SIZE init.ora parameter setting.
Undo Block - Very rarely occurs; may be caused by multiple users updating records in the same data block at a very fast rate; contention can usually be resolved by increasing the PCTFREE of the tables being modified.
Undo Header - May occur if there are not enough rollback segments to support the number of concurrent transactions.
select CLASS,
COUNT,
TIME
from v$waitstat
order by CLASSLabels: Disk I/O, Events, Waits |
| posted by Srinivasan .R @ 12:50 AM |
|
|
|
|
| General Info |
General Info
Here are some scripts related to general database information .
Database Information
DATABASE INFORMATION NOTES:
Database Name - Name of the database
Created - Date/time the database was created
Log Mode - Archive log mode: NOARCHIVELOG or ARCHIVELOG
Checkpoint Change# - Last SCN checkpointed
Archive Change# - Last SCN archived
select NAME,
CREATED,
LOG_MODE,
CHECKPOINT_CHANGE#,
ARCHIVE_CHANGE#
from v$database
Size
DATAFILE NOTES:
File Name - Name of the datafile
Tablespace - Name of the tablespace
Datafile Size - Size of datafile (bytes)
Bytes Used - Amount of datafile used
Percent Used - Percent of datafile used
Bytes Free - Amount of datafile free
select FILE_NAME,
d.TABLESPACE_NAME,
d.BYTES datafile_size,
nvl(sum(e.BYTES),0) bytes_used,
round(nvl(sum(e.BYTES),0) / (d.BYTES), 4) * 100 percent_used,
d.BYTES - nvl(sum(e.BYTES),0) bytes_free
from DBA_EXTENTS e,
DBA_DATA_FILES d
where d.FILE_ID = e.FILE_ID (+)
group by FILE_NAME,d.TABLESPACE_NAME, d.FILE_ID, d.BYTES, STATUS
order by d.TABLESPACE_NAME,d.FILE_ID
Files
DATABASE FILE NOTES:
Filename - Name of the file(s)
Location - Location of the file(s)
select 'Archived Log Directory' "Filename",
value "Location"
from v$parameter
where name = 'log_archive_dest'
UNION
select 'Control Files' "Filename",
value "Location"
from v$parameter
where name = 'control_files'
UNION
select 'Datafile' "Filename",
name "Location"
from v$datafile
UNION
select 'LogFile Member' "Filename",
member "Location"
from v$logfile
init.ora
INIT.ORA PARAMETER NOTES:
Parameter - Init.ora parameter name
Value - Current value
Is Default - Whether the parameter value is the default
Session Modifiable - TRUE=the parameter can be changed with ALTER SESSION / FALSE=the parameter cannot be changed with ALTER SESSION
System Modifiable - IMMEDIATE=the parameter can be changed with ALTER SYSTEM / DEFERRED=the parameter cannot be changed until the next session / FALSE=the parameter cannot be changed with ALTER SYSTEM
Is Modified - Indicates how the parameter was modified. If an ALTER SESSION was performed, the value will be MODIFIED. If an ALTER SYSTEM (which will cause all the currently logged in sessions' values to be modified) was performed the value will be SYS_MODIFIED.
ALWAYS_ANTI_JOIN - Set the parameter to HASH to allow parallel processing of the NOT IN clause (a real resource hog). If you set the parameter to NESTED_LOOPS, the NOT IN is evaluated the same way it was as Oracle7.2 and earlier (which will not please you). MERGE uses the SORT_MERGE algorithm to process the NOT IN, which is faster than NESTED_LOOPS but not as fast as HASH.
DEFAULT: NULL
VALUES: NESTED_LOOPS, MERGE, HASH
COMPATIBLE - This parameter specifies the release with which the Oracle Server must maintain compatibility. Set to the release of Oracle RDBMS that you are currently running to take full advantage of any new features. Allows you to use the current version while at the same time guaranteeing backward compatibility with an earlier release. This is in case it becomes necessary to revert to the earlier release. Some features of the current release may be restricted. Oracle 7.1.5 introduced the ability to bypass the buffer cache (direct reads) for table scans and sorts (COMPATIBLE=7.1.5). Oracle 7.3 introduced temporary tablespaces that improve the performance of sorts and hash joins (COMPATIBLE=7.3.1)
DEFAULT: NULL
VALUES: Any Oracle RDBMS (e.g., Oracle7.2.3)
CONTROL_FILES - Specifies one or more names of control files, separated by commas. Oracle Corporation recommends using multiple files on different devices or mirroring the file at the OS level.
DEFAULT: OS Dependent
VALUES: 1 - 8 filenames
CURSOR_SPACE_FOR_TIME - Turns waiting for application cursors on (TRUE) or off (FALSE). If it is set to TRUE, the database uses more space for cursors to save time. It affects both the shared SQL area and the client's private SQL area. Shared SQL areas are kept pinned in the shared pool when this parameter's value is TRUE. As a result, shared SQL areas are not aged out of the pool as long as there is an open cursor that references them. Because each active cursor's SQL area is present in memory, execution is faster. Because the shared SQL areas never leave memory while they are in use, however, you should set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously. Setting this parameter to TRUE also retains the private SQL area allocated for each cursor between executes instead of discarding it after cursor execution. This saves cursor allocation and initialization time.
DEFAULT: FALSE
VALUES: TRUE/FALSE
DB_BLOCK_BUFFERS - Sets the size, in database blocks, of the data block buffer cache in the SGA. The larger the data block buffer cache is, the more memory will be available for sharing data already in memory amoung users - reducing the need for physical reads. You can determine the effectiveness of the data block buffer cache by measuring the hit ratio of the database.
DEFAULT: 32 Buffers
VALUES: 4 to unlimited
DB_BLOCK_SIZE - Defined when the database is created; it determines the size of each block within the database. You CANNOT change the block size of an existing database; the only method available for increasing the block size is to perform a full database Export, recreate the database with a different DB_BLOCK_SIZE value, and Import the database. In most environments, the default value for DB_BLOCK_SIZE is 2048 bytes (2KB). If you operating environment permits, you should increase the value to 4KB, 8KB, or higher. The performance gains obtained by using a larger block size are significant for both OLTP and batch applications. In general, each doubling of the database block size will reduce the time required for I/O-intensive batch operations be around 40 percent. As the database block size increases, your overall memory requirements may increase.
DEFAULT: OS Dependent
VALUES: 1024-32768
DB_FILE_MULTIBLOCK_READ_COUNT parameter helps determine how many blocks are read at a time by the database during full table scans. You should set the DB_BLOCK_MULTIBLOCK_READ_COUNT parameter to a value that takes the greatest advantage of the operating system's buffer during reads. For example, suppose the OS buffer available for reads is 64KB in size. If your database block size is 2KB, you should set DB_FILE_MULTIBLOCK_READ_COUNT to 32; if the block size is 4KB, set DB_FILE_MULTIBLOCK_READ_COUNT to 16. In some operating systems, the available buffer size is configurable.
DEFAULT: OS Dependent but usually a function of DB_BLOCK_BUFFERS and PROCESSES
VALUES: OS Dependent (normally 1 to either the DB_BLOCK_BUFFERS / 4 or the OS maximum I/O size / DB_BLOCK_SIZE)
DB_FILE_SIMULTANEOUS_WRITES - Number of write batches written by the database writers. If you are experiencing problems getting buffers written to your disks during checkpoints, try increasing the value. It is applicable only to the operation systems that support more than one write to your disk devices.
DEFAULT: 4
VALUES: 1 to 24
DB_WRITERS - Once the database block size and memory areas are properly established, tune the way in which Oracle writes data from memory. If your OS permits using multiple DBWR processes, set a value greater than '1' for the DB_WRITERS parameter. If you start more than on DBWR process, you may be able to reduce contention for blocks within the database block buffer cache. If there is only one DBWR process available, it becomes a possible bottleneck during I/O operations, even if the data is distributed among multiple devices.
ENQUEUE_RESOURCES - An enqueue is a sophisticated locking mechanism which permits several concurrent processes to share known resources to varying degrees. Any object which can be used concurrently can be protected with enqueues. For example, Oracle allows varying levels of sharing on tables: two processes can lock a table in share mode or in share update mode.
DEFAULT: Derived
VALUES: 10 - 65535
HASH_AREA_SIZE - This parameter specifies the maximum amount of memory, in bytes, to be used for the hash join. If this parameter is not set, its value defaults to twice the value of the SORT_AREA_SIZE parameter. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: 2 times the value of SORT_AREA_SIZE
VALUES: Any integer
HASH_JOIN_ENABLED - This parameter enables or disables the hash join feature. Set this parameter to TRUE to use hash joins. Set this parameter to FALSE to disable hash joins. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: TRUE
VALUES: TRUE/FALSE
HASH_MULTIBLOCK_IO_COUNT - This parameter specifies how many blocks a hash join reads and writes at once. When operating in multi-threaded server mode, however, this parameter is ignored (that is, the default value of 1 is used even if you set the parameter to another value). The value of DB_BLOCK_SIZE multiplied by the value of HASH_MULTIBLOCK_IO_COUNT should be less than 64 KB. This parameter strongly affects performance because it controls the number of partitions into which the input is divided.
DEFAULT: 1
VALUES: 1 - (65,536/DB_BLOCK_SIZE)
LOG_ARCHIVE_BUFFER_SIZE - The size of each archival buffer, in redo log blocks (operating system blocks). The default should be adequate for most applications. This parameter, with LOG_ARCHIVE_BUFFERS, can tune archiving so that it runs as fast as necessary, but not so fast that it reduces system performance.
DEFAULT: OS Dependent
VALUES: 1 - OS Dependent (in O/S blocks)
LOG_ARCHIVE_DEST - Applicable only if using the redo log in ARCHIVELOG mode. Use a text string to specify the default location and root of the disk file or tape device when archiving redo log files. (Archiving to tape is not supported on all operating systems.) The value cannot be a raw partition. To override the destination that this parameter specifies, either specify a different destination for manual archiving or use the Server Manager command ARCHIVE LOG START filespec for automatic archiving, where filespec is the new archive destination.
DEFAULT: OS Dependent
VALUES: Any valid path or device name, except raw partitions
LOG_ARCHIVE_START - Applicable only when you use the redo log in ARCHIVELOG mode, LOG_ARCHIVE_START indicates whether archiving should be automatic or manual when the instance starts up. TRUE indicates that archiving is automatic. FALSE indicates that the DBA will archive filled redo log files manually. (The Server Manager command ARCHIVE LOG START or STOP overrides this parameter.) In ARCHIVELOG mode, if all online redo log files fill without being archived, an error message is issued, and instance operations are suspended until the necessary archiving is performed. This delay is more likely if you use manual archiving. You can reduce its likelihood by increasing the number of online redo log files.
DEFAULT: FALSE
VALUES: TRUE/FALSE
LOG_BUFFER Sets the size, in bytes, of the redo log buffer area in the SGA. The default is set to four times the maximum database block size for the OS. For an OLTP application in which many users perform transactions, the LOG_BUFFER parameter needs to be increased beyond its default value. If the 'redo log space requests' statistic in V$SYSSTAT is non-zero, you should increase LOG BUFFER to support the transaction load without forcing transactions to wait for accesses to the redo log buffer.
DEFAULT: OS Dependent
VALUES: OS Dependent
LOG_CHECKPOINT_INTERVAL - The number of newly filled redo log file blocks needed to trigger a checkpoint. Regardless of this value, a checkpoint always occurs when switching from one online redo log file to another. If the value exceeds the actual redo log file size, checkpoints occur only when switching logs. The number of times DBWR has been notified to do a checkpoint for a given instance is shown in the cache statistic dbwr checkpoints, which is displayed in the System Statistics Monitor of the Server Manager. Additional cache statistics include background checkpoints started and background checkpoints completed.
DEFAULT: OS Dependent
VALUES: Unlimited (OS blocks, not database blocks)
LOG_SIMULTANEOUS_COPIES - The maximum number of redo buffer copy latches available to write log entries simultaneously. For good performance, you can have up to twice as many redo copy latches as CPUs. For a single-processor system, set to zero so that all log entries are copied on the redo allocation latch. If this parameter is set to 0, redo copy latches are turned off, and the parameters LOG_ENTRY_PREBUILD_THRESHOLD and LOG_SMALL_ENTRY_MAX_SIZE are ignored. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: CPU_COUNT
VALUES: 0 - unlimited
OPTIMIZER_MODE - When set to RULE, this parameter causes rule-based optimization to be used unless hints are specified in the query. When set to CHOOSE, the optimizer uses the cost-based approach for a SQL statement if there are statistics in the dictionary for at least one table accessed in the statement. (Otherwise, the rule-based approach is used.) You can set the goal for cost-based optimization by setting this parameter to FIRST_ROWS or ALL_ROWS. FIRST_ROWS causes the optimizer to choose execution plans that minimize response time. ALL_ROWS causes the optimizer to choose execution plans that minimize total execution time. The goal of cost-based optimization can also be set within a session by using ALTER SESSION SET OPTIMIZER_MODE.
DEFAULT: CHOOSE
VALUES: RULE/CHOOSE/FIRST_ROWS/ALL_ROWS
PARALLEL_MAX_SERVERS - Each instance must either have a value of zero or the same value as the other instances. Maximum number of query servers or parallel recovery processes for an instance.
DEFAULT: OS Dependent
VALUES: 0 - 256
PARALLEL_MIN_SERVERS - Minimum number of query server processes for an instance. This is also the number of query server processes Oracle creates when the instance is started.
DEFAULT: 0
General Info: 0 - PARALLEL_MAX_SERVERS
General Info - For a multiple-process operation, this parameter specifies the maximum number of operating system user processes that can simultaneously connect to an Oracle7 Server. Should include up to 6 for the background processes (or more if GC_LCK_PROCS is non-zero or if you use the dispatcher configuration) plus one for login; so a value of 20 would permit 13 or 14 concurrent users. The default values of DB_FILE_MULTIBLOCK_READ_COUNT and SESSIONS are derived from PROCESSES. If you alter the value of PROCESSES, you may want to adjust the values of these derived parameters.
DEFAULT: 25
General Info: 6 - OS Dependent
ROLLBACK_SEGMENTS - One or more rollback segments to allocate by name to this instance. If ROLLBACK_SEGMENTS is not overridden, an instance acquires all of the rollback segments named in this parameter, even if the number of rollback segments exceeds the minimum number required by the instance (calculated from the ratio TRANSACTIONS / TRANSACTIONS_PER_ROLLBACK_SEGMENT).
DEFAULT: NULL (the instance uses public rollback segments by default if you do not specify this parameter
VALUES: Any rollback segment names listed in DBA_ROLLBACK_SEGS except SYSTEM
ROW_CACHE_CURSORS - The number of cached recursive cursors used by the row cache manager for selecting rows from the data dictionary. The default value is sufficient for most systems.
DEFAULT: 10
VALUES: 10 - 3300
SHARED_POOL_RESERVED_MIN_ALLOC - This parameter controls allocation of reserved memory. Memory allocations larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle Server will allow fewer allocations from the reserved list and will request more memory from the shared pool list.
DEFAULT: 5000
VALUES: 5000 - SHARED_POOL_RESERVED_SIZE (in bytes)
SHARED_POOL_RESERVED_SIZE - This parameter controls the amount of SHARED_POOL_SIZE reserved for large allocations. SHARED_POOL_RESERVED_SIZE must be greater than SHARED_POOL_RESERVED_MIN_ALLOC to create a reserved list. The default value of 0 represents no reserved shared pool area. Ideally, this parameter should be large enough to satisfy any request scanning for memory on the reserved list without flushing objects from the shared pool. The amount of operating system memory, however, may constrain the size of the shared pool. In general, you should set SHARED_POOL_RESERVED_SIZE to 10% of SHARED_POOL_SIZE. For most systems, this value will be sufficient if you have already tuned the shared pool.
DEFAULT: 0
VALUES: From SHARED_POOL_RESERVED_MIN_ALLOC to one half of SHARED_POOL_SIZE (in bytes)
SHARED_POOL_SIZE - Sets the size, in bytes, of the shared pool in the SGA. If your application is OLTP-oriented, and you use packages and other procedural objects, you'll need a large shared SQL area. In environments using a large number of procedural objects, the size of your shared pool may exceed your data block buffer cache. If you have many users, you should increase the SHARED_POOL_SIZE parameter everytime you increase the DB_BLOCK_BUFFERS parameter.
DEFAULT: 3,500,000
VALUES: 300 Kbytes - OS Dependent
SORT_AREA_RETAINED_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory retained after a sort. This memory is released back to the PGA, not to the operating system, after the last row is fetched from the sort space. If a sort requires more memory, a temporary segment is allocated and the sort becomes an external (disk) sort. The maximum amount of memory to use for the sort is then specified by SORT_AREA_SIZE instead of by this parameter. Larger values permit more sorts to be performed in memory. However, multiple sort spaces of this size may be allocated. Usually, only one or two sorts occur at one time, even for complex queries. In some cases, though, additional concurrent sorts are required. Each sort occurs in its own memory area, as specified by SORT_AREA_RETAINED_SIZE.
DEFAULT: The value of SORT_AREA_SIZE
VALUES: From the value equivalent to one database block to the value of SORT_AREA_SIZE
SORT_AREA_SIZE - This parameter specifies the maximum amount, in bytes, of Program Global Area (PGA) memory to use for a sort. After the sort is complete and all that remains to do is to fetch the rows out, the memory is released down to the size specified by SORT_AREA_RETAINED_SIZE. After the last row is fetched out, all memory is freed. The memory is released back to the PGA, not to the operating system. Increasing SORT_AREA_SIZE size improves the efficiency of large sorts. Multiple allocations never exist; there is only one memory area of SORT_AREA_SIZE for each user process at any time. The default is usually adequate for most database operations. Only if very large indexes are created might you want to adjust this parameter. For example, if one process is doing all database access, as in a full database import, then an increased value for this parameter may speed the import, particularly the CREATE INDEX statements.
DEFAULT: OS Dependent
VALUES: The value equivalent to two database blocks (minimum)
SORT_DIRECT_WRITES - SORT_DIRECT_WRITES can improve sort performance if memory and temporary space are abundant on your system. When set to the default value of AUTO, and if the value of SORT_AREA_SIZE is greater than ten times the buffer size, SORT_DIRECT_WRITES automatically configures the SORT_WRITE_BUFFER_SIZE and SORT_WRITE_BUFFERS parameters. When SORT_DIRECT_WRITES is in AUTO mode, SORT_WRITE_BUFFERS and SORT_WRITE_BUFFER_SIZE have no effect. When SORT_DIRECT_WRITES is set to TRUE, each sort allocates additional buffers in memory to write directly to disk. When SORT_DIRECT_WRITES is set to FALSE, the sorts that write to disk write through the buffer cache.
DEFAULT: AUTO
VALUES: AUTO/TRUE/FALSE
SORT_READ_FAC - SORT_READ_FAC is a unitless ratio that describes the amount of time to read a single database block divided by the block transfer rate.
DEFAULT: OS Dependent
SORT_WRITE_BUFFER_SIZE - This parameter sets the size of the sort buffer when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 32768
VALUES: Any integer
SORT_WRITE_BUFFERS - This parameter sets the number of sort buffers when the SORT_DIRECT_WRITES parameter is set to TRUE. This parameter is recommended for use with symmetric replication.
DEFAULT: 2
VALUES: Any integer
SQL_TRACE - Disables or enables the SQL trace facility. Setting this parameter to TRUE provides information on tuning that you can use to improve performance. Because the SQL trace facility causes system overhead, you should run the database with the value TRUE only for the purpose of collecting statistics. You can change the value of this parameter without shutting down your Oracle instance by using the ALTER SESSION command.
DEFAULT: FALSE
VALUES: TRUE/FALSE
TIMED_STATISTICS - By default (when set to FALSE), the Server Manager statistics related to time (from the buffer manager) always are zero and the Server can avoid the overhead of requesting the time from the operating system. To turn on statistics, set the value to TRUE. Should normally be set to FALSE.
DEFAULT: FALSE
VALUES: TRUE/FALSE
USER_DUMP_DEST - The pathname for a directory where the server will write debugging trace files on behalf of a user process.
DEFAULT: OS Dependent
VALUES: Valid local pathname, directory, or disk
select NAME,
VALUE,
ISDEFAULT,
ISSES_MODIFIABLE,
ISMODIFIED
from v$parameter
order by NAME
License
LICENSE INFORMATION NOTES:
Sessions Max - Maximum number of concurrent user sessions allowed for the instance.
Sessions Warn - Warning limit for concurrent user sessions for the instance.
Sessions Current - Current number of concurrent user sessions.
Sessions Highwater - Highest number of concurrent user sessions sine the instance started.
Users Max - Maximum number of named users allowed for the database.
select SESSIONS_MAX,
SESSIONS_WARNING,
SESSIONS_CURRENT,
SESSIONS_HIGHWATER,
USERS_MAX
from v$license
Versions
DATABASE PRODUCT AND VERSION NOTES:
Version number of core library components in the Oracle Server
select BANNER product_versions
from v$versionLabels: General Info |
| posted by Srinivasan .R @ 12:03 AM |
|
|
|
|
|
