Solved

Oracle performance 9.0.2

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ODBC in excel 2016 in Windows 10 via VBA 16 258
PL/SQL Two changes 7 41
migration MS SQL database to Oracle 30 71
capture vmstat info and insert it into an oracle table 31 62
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

726 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