?
Solved

Oracle performance 9.0.2

Posted on 2008-10-23
1
Medium Priority
?
490 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 1500 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

765 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