Demora en base datos

Me llamo abel, tengo oracle 8.05 bajo unixware, tuvimos que restaurar la base de datos, antes andaba muy bien en los informes pero ahora es lenta sobre todo en los informes auxiliares de contabilidad, se bloque y pone a esperar a los usuarios, no soy experto, solo se algunas cosas he cambiado parametro corrido estadisticas pero no
tengo 640 megas, el archivo paramteros
# $Header: initx.orc 12-jun-97.09:14:56 hpiao Exp $ Copyr (c) 1992 Oracle
#
# include database configuration parameters
ifile = /home/oracle/app/oracle/admin/BDHA/pfile/configBDHA.ora
rollback_segments = (r01,r02,r03,r04,r05,r06,r07,r08,r09)
control_files= (
/home/oracle/app/oracle/product/8.0.5./dbs/oradata/BDHA/control01.ctl,
/home/oracle/app/oracle/admin/BDHA/pfile/control03.ctl,
/var/opt/oracle/oradata/BDHA/control02.ctl)
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you customize
# your RDBMS installation for your site. Important system parameters
# are discussed, and example settings given.
#
# Some parameter settings are generic to any size installation.
# For parameters that require different values in different size
# installations, three scenarios have been provided: SMALL, MEDIUM
# and LARGE. Any parameter that needs to be tuned according to
# installation size will have three settings, each one commented
# according to installation size.
#
# Use the following table to approximate the SGA size needed for the
# three scenarious provided in this file:
#
# -------Installation/Database Size------
# SMALL MEDIUM LARGE
# Block 2K 4500K 6800K 17000K
# Size 4K 5500K 8800K 21000K
#
# To set up a database that multiple instances will be using, place
# all instance-specific parameters in one file, and then have all
# of these files point to a master file using the IFILE command.
# This way, when you change a public
# parameter, it will automatically change on all instances. This is
# necessary, since all instances must run with the same value for many
# parameters. For example, if you choose to use private rollback segments,
# these must be specified in different files, but since all gc_*
# parameters must be the same on all instances, they should be in one file.
#
# INSTRUCTIONS: Edit this file and the other INIT files it calls for
# your site, either by using the values provided here or by providing
# your own. Then place an IFILE= line into each instance-specific
# INIT file that points at this file.
###############################################################################
# tuning parameters
#db_files estaba en 80
db_files = 100
#db_file_multiblock_read_count = 8 # SMALL
# db_file_multiblock_read_count = 16 # MEDIUM
db_file_multiblock_read_count = 32 # LARGE
#db_block_buffers = 200 # SMALL
# db_block_buffers = 550 # MEDIUM
#db_block_buffers = 3200 # LARGE
#estaba en 90000ok
db_block_buffers = 90000 # LARGE
#shared_pool_size = 3500000 # SMALL
# shared_pool_size = 6000000 # MEDIUM
#shared_pool_size = 9000000 # LARGE
#estaba en 45000000 50000000 60000000 ok
shared_pool_size = 120000000 # LARGE
#estaba en 15000
log_checkpoint_interval = 25000
log_checkpoint_timeout = 0
#processes = 50 # SMALL
#processes = 100 # MEDIUM
#estaba en 160 ok
processes = 180 # LARGE
#estaba en 130
#dml_locks = 200 # SMALL
# dml_locks = 200 # MEDIUM
dml_locks = 500 # LARGE
#log_buffer = 8192 # SMALL
# log_buffer = 32768 # MEDIUM
#log_buffer = 163840 # LARGE
#log_buffer = 1310720 parece q no puede subir mas
log_buffer = 1310720
#sequence_cache_entries = 10 # SMALL
# sequence_cache_entries = 30 # MEDIUM
#estaba en 600 750 800ok
sequence_cache_entries = 1000 # LARGE
#sequence_cache_hash_buckets = 10 # SMALL
# sequence_cache_hash_buckets = 23 # MEDIUM
#estaba en 150 ok
sequence_cache_hash_buckets = 200 # LARGE
# audit_trail = true # if you want auditing
# timed_statistics = true # if you want timed statistics
max_dump_file_size = 10240 # limit trace file size to 5 Meg each
log_archive_start = true # if you want automatic archiving
global_names = TRUE
# mts_dispatchers="ipc,1"
# mts_max_dispatchers=10
# mts_servers=1
# mts_max_servers=10
# mts_service=BDHA
# mts_listener_address="(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))"
#
# needed if running OPS
#
# PARALLEL_SERVER=TRUE
open_cursors=350
#estabe en 85536 132072 163072 350000ok
sort_area_size =200000

1 Respuesta

Respuesta
1
A pesar que me enviaste el init. Ora, dependera de la cantidad de memoria RAM que tengas en tu servidor, trata que el SGA no pase del 40%, el shared ponele el 30% del tamaño de tu SGA y al buffer_cache ponele el 60%, no veo el large_pool_size, ni el java_pool_size, el db_file_multiblock_read_count ponele en 8, si no vas a ir a traer mucha data por cada vez que bajes a leer.
Me parece raro que si solo fue el restore nunca cambiaste el init. Ora.
Como sugerencia, podrias hacer un analyze_schema a todos tus esquemas (menos sys y system, porque deja mal la db).
Si tenes alguna copia anterior del init. Ora comparalo, porque tambien no es aconsejable, cambiar tantos parametros juntos.

Añade tu respuesta

Haz clic para o

Más respuestas relacionadas