The assem's we build here are getting larger. The largest so far is about 3000 objects. It's reasonable to expect that with the more objects in an assem, the longer the checkout will take. It now takes about 90 minutes for the server to check out everything in the 3000 part assem and the user complains. I've watched the server during his checkouts, and one cpu is pegged at 100% with his oracle process. There's no other unnecessary activity or processes running on the server and watched the server i/o's - nothing seems out of the ordinary and all other porcesses are responsive during checkout. I'm suspecting the server just doesn't have the horsepower anymore. There's a TPI 110929 at PTC support that talks about tweaking db performance - would a tweak like like make much of a difference?
Snippet of TPI 110929:
optimizer_mode = RULE to optimizer_mode = CHOOSE
(*Update* Tech Support has received customer feedback from several sites that reported additional performance gains for their extra large assemblies with 1000+ components when the following 2 optimization parameters and assigned values are also added. The assigned values may be modified for varying results, please refer to Oracle Documentation for further guidance).
Network infrastructure - server @100Mb FD to router to 1Gb fiber to closet switch to client at 100Mb FD. Switch and router statistics show zero collisions/errors. The infrastructure is pretty robust! Network on the client - A few moments after the checkout button is pushed on the client, client cpu & network activity drops to nil. Client is basically doing nothing but waiting. Network on server - Same as the client, practically nil activity. Vaults - all local disks on the server, nothing replicated.
Using iostat on the server, I watched disk actvity & could tell that at the end of checkout transaction, it takes about 100 seconds or less to actually transfer the files to the client. Fits in with the above info - doesn't seem to be a networking issue.
Are there any sql scripts/utils that can be used to analyze or test what's going on inside oracle? Any Solaris 7 patches/tweaks? (other than the list required by PTC which I already have in place) Here's the $ORACLE_HOME/dbs/initilnk.ora file. Perhaps something in here needs adjustment?
# Dataserver DB initialization file. Made upon # migration procedure from Pro/Intralink v1.3 to v2.0. # Old value remained in commets just for # information and verification. # always_anti_join = NESTED_LOOPS audit_trail = NONE background_dump_dest = /home/ptc/oracle/dataserver/oracle/rdbms/log # /home/ptc/oracle/dataserver/oracle/rdbms/log compatible = 18.104.22.168.0 control_files = (/home/ptc/oracle/dataserver/dbs/ctrl1_ilnk.ctl) # /home/ptc/oracle/dataserver/dbs/ctrl1_ilnk.ctl db_block_buffers = 4800 db_block_size = 4096 db_file_multiblock_read_count = 16 db_files = 48 db_name = ilnk # ilnk global_names = FALSE job_queue_processes = 3 log_buffer = 1048576 log_checkpoint_interval = 40960 log_checkpoint_timeout = 1800 max_dump_file_size = 10240 mts_service = ilnk open_cursors = 350 optimizer_mode = RULE processes = 100 rollback_segments = (R01, R02, R03, R04, R05, R06, R07) # R01, R02, R03, R04, R05, R06, R07 shared_pool_size = 40000000 sort_area_retained_size = 65536 sort_area_size = 1048576 transaction_auditing = FALSE transactions_per_rollback_segment = 1 user_dump_dest = /home/ptc/oracle/dataserver/oracle/rdbms/log # /home/ptc/oracle/dataserver/oracle/rdbms/log remote_login_passwordfile = EXCLUSIVE # Old value was: EXCLUSIVE