Re: Basic query tracing and profiling question...

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 18 Apr 2007 05:02:37 -0700
Message-ID: <1176897757.674236.251280@y5g2000hsa.googlegroups.com>

On Apr 17, 2: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:
> db_file_multiblock_read_count = 32
> db_block_buffers = 22800
> shared_pool_size = 254642800
> large_pool_size = 890800
> java_pool_size = 20971520
> log_checkpoint_interval = 10000
> log_checkpoint_timeout = 1800
> parallel_max_servers = 5 # SMALL
> log_buffer = 3276800
> #timed_statistics = true # if you want timed statistics
> max_dump_file_size = 10240 # limit trace file size to 5M each
> log_archive_start = true
> log_archive_dest = "L:\Logs"
> log_archive_format = %%ORACLE_SID%%T%TS%S.ARC
> #rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6 )
> global_names = true
> resource_manager_plan = system_plan
> user_dump_dest = o:\oracle\admin\XXXX\udump
> db_block_size = 8192
> remote_login_passwordfile = exclusive
> job_queue_processes = 4
> job_queue_interval = 10
> open_links = 4
> distributed_transactions = 500
> mts_dispatchers = "(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)"
> compatible = 8.1.0
> sort_area_size = 130536
> sort_area_retained_size = 65536

Here are a couple of random observations based on what you posted here:
* Statistics likely have not been gathered regularly. Changes (or no changes) in the statistics as the objects in the database change may cause plan changes, or the plans if they remain the same to become less than ideal.
* The value for db_file_multiblock_read_count encourages the use of full tablescans.
* The buffer cache is roughly 186,777,600 bytes, while the shared pool is 254,642,800 bytes.
* You are reserving 20,971,520 bytes for the Java pool. If the application is not using Java, shrink that value to 1MB so that the memory can be used somewhere else.
* The log buffer is set to 3,276,800 bytes - values greater than 1MB may indicate that memory is being wasted. * The largest trace file is limited to 10,240 operating system blocks. If you intend to resolve this problem, you may need to increase this value.
* parallel_max_servers = 5 - I wonder if the application is over-using parallel operations when not needed, which may cause performance problems.
* resource_manager_plan = system_plan ... resource limits being imposed?
* mts_dispatchers ... Multi-Threaded/Shared Server, one session's connection can queue behind another, best used for short transactions. * timed_statistics = false - I suggest that you set this to TRUE if you want to determine the cause of the performance problem. * sort_area_size = 130536 - Oracle sorts to the temp tablespace if more than 130,536 bytes are needed during the sorting operation. * sort_area_retained_size = 65536 - Jonathan Lewis has an interesting observation in his "Cost-Based Oracle Fundamentals" book about what happens if this parameter differs from sort_area_size.

Theories about what is happening:
The statistics are not accurate, leading to full tablescans when not appropriate, since Oracle believes that it is dealing with small tables. This causes the buffer cache to be flooded with table blocks, which then forces other, dirty blocks, out of the buffer cache - this triggers a lot of disk activity. Disk activity takes time, and the session may need to wait for this activity to complete (this may be compounded if one drive in the RAID 5 array, using Niall Litchfield's observation, failed). The session may also force sorts to the temp tablespace, which further compounds the delay. This causes other sessions to queue in line waiting for their turn with the Multi- /Shared Server.

Suggestions:
* Set timed_statistics = true and restart the Oracle database instance.
* After the system has operated for a couple hours, run a SQL statement like this to give you a feel for what is causing the slowness:
SELECT
  EVENT,
  TOTAL_WAITS,
  TIME_WAITED
FROM
  (SELECT
    EVENT,
    TOTAL_WAITS,
    TIME_WAITED
  FROM
    V$SYSTEM_EVENT
  WHERE
    EVENT NOT LIKE 'SQL*Net%'

    AND EVENT NOT LIKE '%timer%'
    AND EVENT NOT LIKE '%rdbms%'
    AND EVENT NOT LIKE '%Streams%'

  ORDER BY
    TIME_WAITED DESC)
WHERE
  ROWNUM<=10;

This may also be helpful:
SELECT
  NAME,
  VALUE
FROM
  V$SYSSTAT
ORDER BY
  UPPER(NAME); Report back what you find. I am sure that the contributors to this group will be happy to assist you in correcting the performance problem.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Apr 18 2007 - 07:02:37 CDT