Re: Basic query tracing and profiling question...

On Apr 17, 1:19 pm, Brian K <brikee...@xxxxxxxxxxxxxxx> 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

.

Relevant Pages

  • Re: Oracle Performance -- Possible Disk Bottleneck
    ... confirm this is a disk bottle neck. ... ton of information in the statspack report, so I'm not sure what else ... so I'm hoping someone here has experience with SANs and ORACLE to help ... this is a disk bottleneck and that giving the database more spindles ...
    (comp.databases.oracle.server)
  • Re: Oracle Performance -- Possible Disk Bottleneck
    ... I've read that 15k drives can perform 180 IO's per second. ... confirm this is a disk bottle neck. ... so I'm hoping someone here has experience with SANs and ORACLE to help ... this is a disk bottleneck and that giving the database more spindles ...
    (comp.databases.oracle.server)
  • Re: High Avg. Disk Queue Length When Opening Shared Calendars
    ... Let's assume 10K spindles, a 3:1 read/write ratio, and IOPS/user of 1. ... Now let's look at the logs. ... the database LUN would become a bottleneck prior to the log LUN. ... A disk bottleneck on the database LUN. ...
    (microsoft.public.exchange.admin)
  • Oracle and I/O performance.
    ... dba's are complaining about performance of importing a database export. ... another path to the SAN so that both volumes are on separate paths but I ... Oracle is running DirectI/O, asynchronous. ... An iostat on the two disks shows the bps and tps for the disk doing the ...
    (Tru64-UNIX-Managers)
  • Re: Disk Configuration for AD domain controllers
    ... need guidance for configuring the disk drives. ... I read that it is recommended to store the AD database and logs on a ... AD is not such a large database as Exchange for example. ... If your RAID controller has a battery backup, it is usually safe to leave write cache enabled on the disk. ...
    (microsoft.public.windows.server.general)