Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle performance 9.0.2

Posted on 2008-10-23
1
Medium Priority
?
495 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 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 Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

580 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