Re: Basic query tracing and profiling question...

On Apr 17, 1:19 pm, Brian K <brikee..._at_geemail.invalid> wrote:
> You need all this just to capture executing queries?
>
> > Operating system you're running Oracle on
> Windows Server 2000 (Standard)
>
> > Available disk resources
> C: System 67.71GB
> L: Oracle Logs 387GB
> O: Oracle Home 534GB
>
> > What files you have on which disks
> L: Oracle archive log files (*.arc)
> O: Everything else
>
> > The init.ora file for this instance
> See below
>
> > How many users connect to this database
> 1; test environment
>
> > A Statspack report generated during the time when this occurs
> Vendor will not allow us to install statspack, apparantly. Your guess
> as to motive is as good as mine.
>
> init.ora:
> # 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.
> #
> # NOTE: Parameter values suggested in this file are based on conservative
> # estimates for computer memory availability. You should adjust values
> upward
> # for modern machines.
> #
> ###########################################################################­####
>
> db_name = "XXXX"
>
> instance_name = XXXX
>
> service_names = XXXX
>
> db_files = 1500
> # db_files = 80
> # db_files = 400
> # db_files = 1500
>
> control_files = ("o:\oracle\oradata\XXXX\control01.ctl",
> "o:\oracle\oradata\XXXX\control02.ctl",
> "o:\oracle\oradata\XXXX\control03.ctl")
>
> open_cursors = 300
> max_enabled_roles = 30
> db_file_multiblock_read_count = 32
> # db_file_multiblock_read_count = 8
> # db_file_multiblock_read_count = 16
> # db_file_multiblock_read_count = 32
>
> db_block_buffers = 22800
> # db_block_buffers = 19200 DT 01302007
> # db_block_buffers = 100
> # db_block_buffers = 550
> # db_block_buffers = 3200
>
> shared_pool_size = 254642800
> # shared_pool_size = 52428800 DT 01302007
> # shared_pool_size = 3500000
> # shared_pool_size = 5000000
> # shared_pool_size = 9000000
>
> large_pool_size = 890800
> # large_pool_size = 614400 DT 01302007
> java_pool_size = 20971520
>
> log_checkpoint_interval = 10000
> log_checkpoint_timeout = 1800
>
> processes = 600
> # processes = 50
> # processes = 100
> # processes = 200
>
> parallel_max_servers = 5 # SMALL
> # parallel_max_servers = 4 x (number of CPUs)
> # parallel_max_servers = 4 x (number of CPUs)
>
> log_buffer = 3276800
> # log_buffer = 32768
> # log_buffer = 32768
> # log_buffer = 163840
>
> #audit_trail = true # if you want auditing
> #timed_statistics = true # if you want timed statistics
> max_dump_file_size = 10240 # limit trace file size to 5M each
>
> # Uncommenting the line below will cause automatic archiving if
> archiving has
> # been enabled using ALTER DATABASE ARCHIVELOG.
> log_archive_start = true
> log_archive_dest = "L:\Logs"
> log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
>
> # If using private rollback segments, place lines of the following
> # form in each of your instance-specific init.ora files:
> #rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 )
>
> # If using public rollback segments, define how many
> # rollback segments each instance will pick up, using the formula
> # # of rollback segments = transactions /
> transactions_per_rollback_segment
> # In this example each instance will grab 40/5 = 8
> # transactions = 40
> # transactions_per_rollback_segment = 5
>
> # Global Naming -- enforce that a dblink has same name as the db it
> connects to
> global_names = true
>
> # Edit and uncomment the following line to provide the suffix that will be
> # appended to the db_name parameter (separated with a dot) and stored as the
> # global database name when a database is created. If your site uses
> # Internet Domain names for e-mail, then the part of your e-mail address
> after
> # the '@' is a good candidate for this parameter value.
> # db_domain = us.acme.com # global database name is db_name.db_domain
>
> # Uncomment the following line if you wish to enable the Oracle Trace
> product
> # to trace server activity. This enables scheduling of server collections
> # from the Oracle Enterprise Manager Console.
> # Also, if the oracle_trace_collection_name parameter is non-null,
> # every session will write to the named collection, as well as enabling you
> # to schedule future collections from the console.
> # oracle_trace_enable = true
>
> oracle_trace_collection_name = ""
> # define directories to store trace and alert files
> background_dump_dest = o:\oracle\admin\XXXX\bdump
> #Uncomment this parameter to enable resource management for your database.
> #The SYSTEM_PLAN is provided by default with the database.
> #Change the plan name if you have created your own resource plan.#
> resource_manager_plan = system_plan
> user_dump_dest = o:\oracle\admin\XXXX\udump
>
> db_block_size = 8192
>
> remote_login_passwordfile = exclusive
>
> os_authent_prefix = ""
>
> # The following parameters are needed for the Advanced Replication Option
> job_queue_processes = 4
> job_queue_interval = 10
> open_links = 4
>
> distributed_transactions = 500
> mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
> # Uncomment the following line when your listener is configured for SSL
> # (listener.ora and sqlnet.ora)
> # mts_dispatchers = "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
>
> compatible = 8.1.0
> sort_area_size = 130536
> # sort_area_size = 65536 DT 01302007
> sort_area_retained_size = 65536

You're running a 'one-disk wonder' and it's no wonder why you have performance problems; controlfiles, redo logs, system tablespace, data tablespaces, index tablespaces, redo logs, rollback segments, temp tablespace all on ONE disk is a recipe for disaster. I am not at all surprised that you're experiencing such difficulties. Transactional activity not only writes to the data files, it also writes to the controlfiles, redo logs and the rollback segments, all of which are on one, lonely, overworked disk. ANY relatively heavy transactional activity will send your database into a tailspin. Get more disks (they're cheap) and spread out this load before you go any further.

Knowing what Oracle's optimizer is doing with your transactions is the least of your concerns; what you need to deal with is your poor disk configuration (or mis-cofiguration). Address that issue then go after your transactions and attempt to tune those. Tuning them now is a waste of time and effort.

David Fitzjarrell Received on Tue Apr 17 2007 - 13:52:52 CDT