Home » RDBMS Server » Server Administration » Please Move to Server Administration Folder (moved from Suggestions & Feedback by bb)
| Please Move to Server Administration Folder (moved from Suggestions & Feedback by bb) [message #342207] |
Thu, 21 August 2008 12:35  |
feroze
Messages: 144
Registered: September 2005
Location: chicago
|
Senior Member |
|
|
I am getting error while posting, Admin's sorry for this, Please move to Server Administration folder,
Thanks
Hi,
Experts , Please Give your opinions on the points discussed below, The discussion below is between a Developer & DBA.
I know this information is not enough for you people to give suggestion, In general what's your saying on these parameter setting /TS Design
It's an OLTP system
Current PGA Target is :- 3145728000 ( on each Node)
Buffer cache 5000mb.
| Quote: | HASH_AREA_SIZE -- Current Space 190 MB ( On each Node)
All the Invoice releated queries are using HASH Joins against STATEMENTT and INVOICE_STATEMENT tables..Is this space enough for Hash Joins? How much you want to Increase
|
| Quote: |
session_cached_cursors
Description
Allows specification the number of session cursors to cache. Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache. Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor. Oracle uses a least recently used algorithm to remove entries in the session cursor cache to make room for new entries when needed.
This script reports the current maximum usage in any session with respect to these limits. If either Usage figures approaches 100%, then the corresponding parameter should normally be increased.
|
select 'session_cached_cursors' parameter, lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from ( select max(s.value) used from V$STATNAME n, V$SESSTAT s
where n.name = 'session cursor cache count' and s.statistic# = n.statistic# ),
( select value from V$PARAMETER where name = 'session_cached_cursors' )
union all
select 'open_cursors', lpad(value, 5), to_char(100 * used / value, '990') || '%'
from ( select max(sum(s.value)) used from V$STATNAME n, V$SESSTAT s
where n.name in ('opened cursors current', 'session cursor cache count')
and s.statistic# = n.statistic# group by s.sid ),
( select value from V$PARAMETER where name = 'open_cursors' );
Production Results
Parameter Value Usage
Session cached Cursor 200 101%
Open Cursors 2000 17%
Based on Above Results Recommendation is to increase the session Casched Cursor.
| Quote: |
Data File Storage Structure - We need to discuss about our Tablespace and Datafiles Storage system.
Some information on tablespace design:
Distributing Tablespace
Tablespaces are the links between database objects and data files out on disk drive.
Tablespaces have the following attributes:
Ø
Hold and group various database objects for ease of management.
Ø Contain one or many database objects, which have storage requirements through segments.
Ø Store one or many physical database files on a disk drive.
Ø Link between the logical database objects and to the physical blocks on a disk drive.Consider these attributes when designing
tablespaces, the objects they old, and the location where they will be placed on disk drive.
Ø For example, placing the two most accessed tables, with their indexes, in one tablespace will cause problems:
Ø They would occupy the same relative areas on disk drive.
Ø They would likely produce contention on the storage array.
Basic guidelines for tablespace design:
Ø Spread potential hot objects through different tablespaces.
Ø Do not put similar objects in the same tablespace. To improve performance,
place two objects that are used in the same SQL query in different
tablespaces and at different locations on disk drive.
Ø Do not put an object’s indexes in the same tablespace as the object.
Ø Put similarly accessed objects together to allow for easy placement on a disk
drive that will have the same configuration (RAID level, caching, segment size, and similar attributes).
Ø Put similarly maintained objects together to allow for maintenance at the
tablespace level.
Ø Watch for volatile objects and do not place them in the same tablespaces as
stable objects. For instance if an application creates and drops objects regularly, dedicate a tablespace for its operations.
Ø Separate read-only objects and place them in their own tablespaces.
Ø Verify block size, percent-used, and percent-free for objects to reduce row-
chaining that would cause additional reads.
To identify tablespaces and the physical data file names that are performing large numbers of reads and writes, use the following SQL command:
select vtablespace.name tablespace_name,
vdatafile.name datafile_name,
vfilestat.phyrds physical_reads,
vfilestat.phywrts physical_writes,
vfilestat.avgiotim average_time_for_io
from v$datafile vdatafile,
v$filestat vfilestat,
v$tablespace vtablespace
where vdatafile.file# = vfilestat.file#
and vdatafile.ts# = vtablespace.ts#;
|
[Updated on: Thu, 21 August 2008 12:47] by Moderator Report message to a moderator |
|
|
|
| Re: Please Move to Server Administration Folder (moved from Suggestions & Feedback by bb) [message #342266 is a reply to message #342207] |
Thu, 21 August 2008 18:24  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
http://www.orafaq.com/forum/t/88153/0/
Too bad you did not read & follow posting guidelines as stated in URL above.
Forgive me, but I do not see any clear problem statement or question.
If you desire to know which configuration is better, then your only definitive recourse is to benchmark various alternatives & compare results.
If there was a cookie cutter solution to tuning/configuration, it would not be so hard to achieve.
There are multiple orthogonal tuning philosophies.
1) Application tuning can be achieved at the database level by utilizing things such as initSID.ora parameters, tablespace placements, separating tables from indexes, etc.
2) Application tuning is achieved 1 SQL statement at a time, by improving the behavior of the "worst" performing SQL until an acceptable level of performance is achieved.
3) Application tuning can be achieved by the Ready, Fire, Aim approach. When performance is not acceptable, start changing things hoping to get lucky & performance gets better.
Pick your poison & enjoy discussing how many angels can dance on the head of a pin.
|
|
|
|
Goto Forum:
Current Time: Mon Jul 08 07:13:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
