Solved

Oracle performance 9.0.2

Posted on 2008-10-23
1
489 Views
Last Modified: 2013-12-19
After and export and import (DUMP) in Oracle 9.2.0 the performance has dramatically degraded.
We recreated the indexes, ran the statistics but no real improvement

I like some clues how to proceed
ORA file is

##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
 
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_cache_size=25165824
db_file_multiblock_read_count=16
 
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
 
###########################################
# Database Identification
###########################################
db_domain=""
db_name=SPECTRUM
 
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=D:\ORACLE\admin\SPECTRUM\bdump
core_dump_dest=D:\ORACLE\admin\SPECTRUM\cdump
timed_statistics=TRUE
user_dump_dest=D:\ORACLE\admin\SPECTRUM\udump
 
###########################################
# File Configuration
###########################################
control_files=("D:\ORACLE\SPECTRUM\control01.ctl", "D:\ORACLE\SPECTRUM\control02.ctl", "D:\ORACLE\SPECTRUM\control03.ctl")
 
###########################################
# Instance Identification
###########################################
instance_name=SPECTRUM
 
###########################################
# Miscellaneous
###########################################
compatible=9.2.0.0.0
 
###########################################
# Optimizer
###########################################
hash_join_enabled=TRUE
query_rewrite_enabled=FALSE
star_transformation_enabled=FALSE
 
###########################################
# Pools
###########################################
java_pool_size=0
large_pool_size=8388608
shared_pool_size=50331648
 
###########################################
# Processes and Sessions
###########################################
processes=150
 
###########################################
# Redo Log and Recovery
###########################################
fast_start_mttr_target=300
 
###########################################
# Security and Auditing
###########################################
remote_login_passwordfile=EXCLUSIVE
 
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=25165824
sort_area_size=524288
 
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_retention=10800
undo_tablespace=UNDOTBS1
 
0
Comment
Question by:mschaeffers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 2

Accepted Solution

by:
barfi earned 500 total points
ID: 22787955
When you say performance degraded after export import? If it already finished running then look at what is running that is causing it to slow down. What sqls are running behind the db that is causing it to perform slow. Is your application running slow?
Export has already finished so do not worry about that now. You ran statistics which is good and created indexes your another option is to run the statspack analyzer that might tell you about the performance of your  database. You need to look at what runs and the databse is slow. It cannot be just export import. It can be memory,processor or other memory parameters that needs to be resized. How much memory you have and how much memory your db server is using and what is your sga size? Here is a link to statspack analyzer -go thru it and follow the instructions and you will learn what are the other factors that can cause poor performance:
http://www.txmemsys.com/statspack-reg.htm
I hope this helps.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question