Mysterious low performance problem

[neuralnode]

Hi All,

I'm experiencing performance issues on one of two seemingly identical Oracle databases (Oracle 10g), running on Solaris 10.

The problem is as follows: The DB at question is SLOW, especially while processing reports overnight, is swapping a lot, and many oracle processes are in the sleep state, as if waiting for their turn.
However, the physical memory is NOT used excessively, i.e. there is still lots of RAM to be used (total of 16GB). vmstat shows 5-minute long pageins reching 16000 while the DB is processing overnight.
The processors' load is minimal.

There exists an identical DB on another machine, which behaves normally and runs fast. That machine is a LOT weaker than the one which causes problems.

Both DBs run on UFS.

So my question is: How could I most effectively troubleshoot the issue? Is there any tool or script (DTrace?) which would help reveal the true cause of the slowdown?

To me it looks as if the memory allocation/scheduling for the oracle jobs/processes is flawed. How can I check that?

Thank you in advance!
(Pls mind I'm not a DB administrator as such)

--

 

[KenCunningham]

When you say 'identical' do you mean in data and configuration? First step (which you've probably already done) is to compare the parameters within which your databases run for obvious disparities.

The internet - allowing those who don't know what they're talking about to have their say.

 

[carp]

I would also check the execution plans and statistics between databases to see if they are identical. If not, then you need to find out where the databases are NOT identical.

 

[SantaMufasa]

Neural,

Along with Ken, I recommend a side-by-side (e.g. in Excel) comparison of these instance parameters in particular for your two focus instances:
[tt]
filesystemio_options
sga_max_size
sga_target
shared_pool_size
large_pool_size
java_pool_size
streams_pool_size
global_context_pool_size
olap_page_pool_size
shared_pool_reserved_size
db_block_size
db_file_multiblock_read_count
optimizer_features_enable
optimizer_mode
optimizer_index_cost_adj
optimizer_index_caching
optimizer_dynamic_sampling
optimizer_secure_view_merging
_optimizer_native_full_outer_join
cursor_space_for_time
session_cached_cursors
cursor_sharing
open_cursors
db_keep_cache_size
db_cache_size
parallel_server
parallel_server_instances
parallel_min_percent
parallel_min_servers
parallel_max_servers
parallel_instance_group
instance_groups
parallel_execution_message_size
parallel_adaptive_multi_user
parallel_threads_per_cpu
parallel_automatic_tuning
pga_aggregate_target
workarea_size_policy
max_dump_file_size
active_instance_count
job_queue_processes
audit_trail
_gby_hash_aggregation_enable
_library_cache_advice
[/tt]

Also, for each instance, run Oracle Automated Workload Repository (AWR) report (html version is easier to read). The focus of the performance differences between the two instances' AWR reports should be apparent. If you are not familiar with the AWR, just Google for "Oracle AWR" -- it results in several high-quality links.

Carp's suggestion of confirming when tables and indexes were last analyzed is very important for performance. You can determine when analyses last occurred for a schema with the following SQL*Plus script. (You cannot just copy and paste to a SQL*Plus prompt, the code below because of the "ACCEPT" statement. You should save the code to a script, then invoke the script from a SQL*Plus prompt. Example: SQL> @check_stats):

accept ownr prompt "Enter the schema for which to check stats: "
col x heading "Last|Analyzed" format a12
col y heading "Count" format 99,999
col objects format a7
COL owner format a30
set pages 35
set heading on
select owner,'Tables' Objects,trunc(last_analyzed) x,count(*)y
  from dba_tables
 where owner = upper('&ownr')
 group by owner,trunc(LAST_ANALYZED)
union
select owner,'Indexes' Objects,trunc(last_analyzed) x,count(*)y
  from dba_indexes
 where owner = upper('&ownr')
 group by owner,trunc(LAST_ANALYZED)
order by x
/

              Last
OWNER OBJECTS Analyzed       Count
----- ------- ------------ -------
WXYZ  Indexes 29-AUG-10         12
WXYZ  Tables  29-AUG-10          4
WXYZ  Indexes 21-FEB-11        961
WXYZ  Tables  21-FEB-11        239
WXYZ  Indexes 22-FEB-11         59
WXYZ  Tables  22-FEB-11          6
WXYZ  Indexes 24-FEB-11          5
WXYZ  Tables  24-FEB-11          2
WXYZ  Indexes                   19

If the differences in the above output for schemas on the two different instances is significant (or if the LAST_ANALYZED dates are stale for either of the schemas) then you should regather statistics for applicable schemas.

Let us know your findings or if any of the above is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[neuralnode]

Thanx guys!

In fact the DB administrator claims the DBs are identical in both terms: data AND configuration. I will, however, forward your advice to our Oracle team, and we'll see what they say then.

---