Re: ORACLE Performance Tuning - Your experiences

deangup_at_admin.ci.seattle.wa.us wrote:

>I'm not a model DBA but here are my answers.
 

>> Which DB_BLOCK_SIZE are you using?
>We went with 4.096 bytes because that's a physical read on our server (RS/6000).
I agree that one should try to match the block size w/the OS physical read size.

>> What is your value of DB_MULTI_FILE_READ_COUNT?
>Don't know! :-}
 

>> Which formulas do you use for the determination of parameters in the
>> storage clause?
>I like to set initial to 100K and next to 1M. That way if it's a small table it takes one extent, if it's larger it
>usually won't need more than a few additional extents. I run DFRAG once a week and it moves everything
>into the primary extent for fragmented tables, so there seems little to gain in sweating out a precise
>estimate before the table is loaded.

I think that one should examine one's data. If you have many tables w/just a few rows, I would recommend setting initial to smaller than 100k.

>> Which parameters do you monitor and how often do you monitor them?
>I keep a close eye on amount of freespace in each tablespace, and compare the size of the largest
>freespace extent in a tablespace to each object's value for its next extent to find objects that would have
>problems if they took an extent. I also watch the total extent count for all objects, though in production
>DFRAG makes that unnecessary. DFRAG can't degragment freespace extents so I watch them more
>closely since I have to do them manually with EXP and IMP.
I like to look at the end user SQL statements causing the most physical I/O per execution, archiving it to a table, and performing a "select count desc" on that table.

Also, I think it is important to archive the performance data you do gather on a regular basis so that you can develop a history.

>> How do you find out about your current workload?
>I use BSTAT and ESTAT to get a count of the maximum number of concurrent sessions each day, and
>graph it monthly.
 

>Paul de Anguera, City of Seattle / HRIS

• Paul

--
posborn_at_menlosoftware.com      Menlo Software
http://www.menlosoftware.com   744 College Ave.
V:415.324.1286                      Menlo Park, CA 94025-5204 USA