Solved

Oracle performance 9.0.2

Posted on 2008-10-23
1
484 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
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now