Clarification of BSTAT-ESTAT results
Date: 1995/12/22
Message-ID: <4beqkb$dv8_at_ds2.acs.ucalgary.ca>
BSTAT-ESTAT Advice
Hi,
As described in the previous mail message our situation is that we are experiencing performance problems with a system which has only been in full production for 2 months. We are having troubles figuring out where our bottlenecks are due to negative numbers in our bstat/estat results. I was perusing through the responses and here are a few more of the facts to our situation that were requested.
Hardware:
Sun 2000e server - 8 cpu's (60 mhz) 1 gig memory
Sun 1000e batch server - 4 cpu's (60 mhz) 512 meg memory
Sun 20 clients (4 of them) - 2 cpu's (60 mhz) 256 meg memory each
Sun array 1 - 30 gig of 1 gig disks, raid 0+1 (mirrored & stripped) 32k
blocking
Sun array 2 - mirror of array 1
Sun array 3 - 24 gig of 2 gig disks, stripped (home directories, reports,
application software)
25 gig of plain old scsi disk for backups and scratch
2 gig of internal system disk on all machines
Software:
Solaris 2.4 patch bundle 29
Oracle 7.1.6
One oracle instance running a forms v3 based financial accounting system
Answers to some questions:
The server was NOT bounced during this time. Oracle tech support confirmed that we were over running the largest integer value of 2 billion + on some of our base tables (eg. v$sysstat). Oracle support could not confirm that all of our negative numbers we as a result of overflow.
The server was running for one day before we took these statistics. We have followed up with additional statistics and found results varied by a few million on the more important figures but were in the ballpark.
Some of our wait times such as dispatcher timer (163580005) are inaccurate due to negative numbers in the base table v$dispatcher. So we don't think we have MTS contention. However, we have had up to 45 shared servers running at once. Our mts_server init.ora parameter is set at 20 and mts_max_servers is set at 50.
The number of processes in the server ranges from 60 (at night) to 220 during the day. The number of users on the system is around 140-160 during the day.
Solaris supports asynchronous io so Oracle recommends that you do not have more than one database writer. I have seen what looks like solaris spawn a number of sub async processes under the DBWR process using truss. In addition, we seem to be generating a good deal of reports lately and not all that many transactions so the shared server processes should be the busy ones selecting data.
We seem to have around 22 meg of free memory in the shared pool whenever I check and our hit ratio seems astronomical from the bstat/estat. As far as the unix system goes we have one gig of memory and only 189 meg allocated to the SGA leaving 811 meg for the operating system and user processes including shared servers, so I don't think we are paging however we are having the hardware boys look into it.
What next:
We are trying to track down some of the problem jobs. Some of our tables are
over 1 gig in size and 10 million plus rows. We do have many in-house built
reports.
We are looking at de-fragging and de-chaining again. Also considering changing the block size on the database.
Possibly have corrupted indexes so we are going to analyze and verify all indexes when we get some down time.
Needed Information:
What would be nice to get is some comparison bstat/estat and environment
number from anyone out there with comparable system. There is the possibility
we are underconfigured in what we are trying to do (ie. our server is
configurable to 20 cpus at 150 mhz each).
Below is an example piece of code from one section of a report and our init.ora parameter file.
The report does this operation 8 times. Yes, I know, it does a sum, decode, and substring on 130 thousand rows. Surprisingly enough this was actually one of the best ways to do it.
INSERT INTO fin_temp_prs (COMP,PROD,VAL_CODE,REGION,AREA,ACTUAL_BAL_N_YTD,
YEAR,PER_NO,STEP,INS_TS)
SELECT a.COMP,
DECODE(USE_FA_MAJ_PROD_FLAG,'Y',nvl(decode(b.MAJ_PROD,'WATE','OIL',b.MAJ_PROD),
'NONE'),REPORT_AS_PRODUCT), c.VAL_CODE,b.USER_CODE_02,
substr(b.AREA,1,3), SUM(REPT_BAL_N_YTD), YEAR, PER_NO, 'Actuals',
sysdate
FROM FA_BALANCE a,
FA_HEAD b,
WR_VAL_D c,
FIN_DOWNLOAD_ACCT_CTL d,
FIN_COMP FC
WHERE a.YEAR = '1995'
AND a.PER_NO = '11'
AND b.COMP='001'
AND FC.PgmID = 'PRS'
AND a.COMP = FC.COMP
AND a.FAC_NO = b.FAC_NO
AND a.account || a.sub_feat BETWEEN account_s || sub_feat_s and
account_f || sub_feat_f
AND c.VAL_CODE = d.val_code
AND c.COMP='001'
AND d.DOWNLOAD_ID = 'PRS'
AND d.SECTION_CODE = 'ACTUALS'
GROUP BY
a.COMP,
DECODE(USE_FA_MAJ_PROD_FLAG,'Y', nvl(decode(b.MAJ_PROD, 'WATE','OIL',
b.MAJ_PROD), 'NONE'),REPORT_AS_PRODUCT), c.VAL_CODE, b.USER_CODE_02, substr(b.AREA,1,3), YEAR, PER_NO, 'Actuals',sysdate call count cpu elapsed disk query currentrows
------- ------ -------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 803.81 6368.92 321245 1221375 82 540
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ----------
total 2 803.82 6368.92 321245 1221375 82 540
Misses in library cache during parse: 0
Optimizer hint: RULE
Parsing user id: 236 (OPS$KPRITCHA)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT HINT: RULE
133964 SORT (GROUP BY)
133964 NESTED LOOPS
133966 NESTED LOOPS
3055 NESTED LOOPS
1495 NESTED LOOPS
196 TABLE ACCESS (FULL) OF 'FIN_COMP'
1495 TABLE ACCESS (BY ROWID) OF 'FIN_DOWNLOAD_ACCT_CTL'
1560 INDEX (RANGE SCAN) OF 'FIN_DOWNLOAD_ACCT_CTL_KEY1' (UNIQUE)
3055 TABLE ACCESS (BY ROWID) OF 'WR_VAL_D'
4550 INDEX (RANGE SCAN) OF 'WR_VAL_D_KEY1' (NON-UNIQUE)
133966 TABLE ACCESS (BY ROWID) OF 'FA_BALANCE'
10843370 INDEX (RANGE SCAN) OF 'FA_BALANCE_KEY2' (NON-UNIQUE)
133964 TABLE ACCESS (BY ROWID) OF 'FA_HEAD'
133966 INDEX (UNIQUE SCAN) OF 'FA_HEAD_KEY1' (UNIQUE)
******************************************************************************
Init.ora:
#
# $Header: initx.orc 7001300.6 95/02/11 14:16:11 wyim Osd<unix> $ Copyr (c)
1992
Oracle
#
# include database configuration parameters
ifile = /data01/home/dba/oracle/admin/EYEP/pfile/configEYEP.ora
rollback_segments =
(rbs01,rbs26,rbs02,rbs27,rbs03,rbs28,rbs04,rbs29,rbs05,rbs30,rbs06,rbs31,
rbs07,rbs32,rbs08,rbs33,rbs09,rbs34,rbs10,rbs35,rbs11,rbs36,rbs12,rbs37,rbs13, rbs38,rbs14,rbs39,rbs15,rbs40,rbs16,rbs41,rbs17,rbs42,rbs18,rbs43,rbs19,rbs44, rbs20,rbs45,rbs21,rbs46,rbs22,rbs47,rbs23,rbs48,rbs24,rbs49,rbs25,rbs50)# Example INIT.ORA file
##############################################################################
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
###############################################################################
# tuning parameters
db_files = 50
max_rollback_segments = 51
db_file_multiblock_read_count = 32 # SMALL
optimizer_mode=rule
db_block_buffers = 30000
#_db_block_write_batch=32
#db_file_simultaneous_writes=6
#_lgwr_async_write=false
#async_write=false
#db_writers=24
shared_pool_size = 104857600
log_checkpoint_interval = 100000000
processes = 600
open_cursors = 450
dml_locks = 2000
log_buffer = 10485760 log_simultaneous_copies=16 log_small_entry_max_size=80
sequence_cache_entries = 1000
sequence_cache_hash_buckets = 89
# audit_trail = true # if you want auditing
timed_statistics = true # if you want timed statistics max_dump_file_size = 40960 # limit trace file size to 20 Meg each log_archive_start = true # if you want automatic archivingos_authent_prefix = OPS$
remote_os_authent = true
pre_page_sga = true
sort_area_size = 1048576
checkpoint_process = true
log_archive_dest = /archives/EYEP/EYEPLOG _trace_files_public=TRUE
spin_count = 1000
session_cached_cursors=50
# global_names = TRUE
mts_dispatchers="tcp,8" mts_max_dispatchers=50 mts_servers=20 mts_max_servers=50 mts_service=EYEP mts_listener_address="(ADDRESS=(PROTOCOL=tcp)(KEY=EYEP)(PORT=1526)(HOST=calbe2)"
Thanks in advance for you help...
Regards,
Andy
Received on Fri Dec 22 1995 - 00:00:00 CET