Re: How to speed up Oracle 9.2.0 on SuSE Enterprise Server 7

Yeah. I tried that and I put the init.ora from Sofia in the other server, create spfile from it and the result was the same!

Latchezar Kostov

Daniel Morgan <dmorgan_at_exesolutions.com> wrote in message news:<3D9B138E.53FA2B3F_at_exesolutions.com>...
> "Howard J. Rogers" wrote:
>
> > If this is blindingly obvious, forgive me: but get hold of the init.ora
> > files from Sofia, and make sure that your init.ora files are identical. Only
> > then do you start wondering about platform specifics.
> >
> > Regards
> > HJR
> >
> > "Latchezar Kostov" <lgk_at_ifd.orbitel.bg> wrote in message
> > news:c4053a39.0210020350.3154887f_at_posting.google.com...
> > > I had install an Oracle 9.2.0 database over SuSE Enterprise Server 7
> > > and I have a big trouble.
> > > The situation is that...
> > > In our project we import two kind of files - bon and pcout. We have a
> > > developement system in Sofia running on 2xCPU system with Mandrake 8.1
> > > and Oracle 9.0.1. Here the two type of files goes fast - ~ 1 hour for
> > > the pcout files and ~45 min for the bon files (140 files bon and 140
> > > pcout).
> > > When I install the new system - Oracle 9.2.0 on SuSE Enterprise Server
> > > 7 (which is certificate by Oracle) on 2xCPU machine... the pcout files
> > > passed for about 4 hours.. but every file go aproximately for constant
> > > time... but the bigger problem is with bon file. The needed time for
> > > them grown with every one file and for the third file is about 5 hours
> > > for a 5 MB file....
> > > I was trying to change the parameters of the database, to optimise the
> > > SQL but no effect... we just one time have success to run them fast
> > > from our debbuging tool but no more!
> > > I made the database with Oracle tools saying to not make example
> > > schemas and I change some of the parameters which are shown down:
> > >
> > ############################################################################
> > ##
> > > # 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=700
> > >
> > > ###########################################
> > > # Database Identification
> > > ###########################################
> > > db_domain=""
> > > db_name=klpb
> > >
> > > ###########################################
> > > # Diagnostics and Statistics
> > > ###########################################
> > > background_dump_dest=/apps/oracle/admin/klpb/bdump
> > > core_dump_dest=/apps/oracle/admin/klpb/cdump
> > > timed_statistics=TRUE
> > > user_dump_dest=/apps/oracle/admin/klpb/udump
> > >
> > > ###########################################
> > > # File Configuration
> > > ###########################################
> > > control_files=("/apps/oracle/product/9.2.0/oradata/klpb/control01.ctl",
> > > "/apps/oracle/product/9.2.0/oradata/klpb/control02.ctl",
> > > "/apps/oracle/product/9.2.0/oradata/klpb/control03.ctl")
> > >
> > > ###########################################
> > > # Instance Identification
> > > ###########################################
> > > instance_name=klpb
> > >
> > > ###########################################
> > > # Job Queues
> > > ###########################################
> > > job_queue_processes=10
> > >
> > > ###########################################
> > > # License Limits
> > > ###########################################
> > > license_max_users=1000
> > >
> > > ###########################################
> > > # MTS
> > > ###########################################
> > > dispatchers="(protocol=TCP)"
> > >
> > > ###########################################
> > > # Miscellaneous
> > > ###########################################
> > > aq_tm_processes=1
> > > compatible=9.2.0.0.0
> > >
> > > ###########################################
> > > # Network Registration
> > > ###########################################
> > > local_listener=LISTENER_KLPB
> > >
> > > ###########################################
> > > # Optimizer
> > > ###########################################
> > > hash_join_enabled=TRUE
> > > query_rewrite_enabled=FALSE
> > > star_transformation_enabled=FALSE
> > >
> > > ###########################################
> > > # PL/SQL
> > > ###########################################
> > > utl_file_dir=*
> > >
> > > ###########################################
> > > # Pools
> > > ###########################################
> > > java_pool_size=83886080
> > > large_pool_size=8388608
> > > shared_pool_size=83886080
> > >
> > > ###########################################
> > > # 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=60
> > > undo_tablespace=UNDOTBS1
> > >
> > > Could you give to me some help or idea? If I put additional UNDO
> > > tablespaces and TEMPORARY tablespaces than one big the system must go
> > > fast? Or we must put additiona RAM memory of the server (640 MB now)?
> > >
> > > Thank you in advance!
> > > Latchezar Kostov
>
> Not blindingly obvious to me as this is 9.2 and 9.2 will be more than pleased to
> ignore the init.ora files as it will boot itself from the spfile. And changes
> made real-time are written to the spfile and may not be in the original init.ora
> from which it was generated.
>
> I would suggest reverse engineering the init.ora from the spfile ... or ...
> comparing a listing from v$parameter.
>
> Daniel Morgan
Received on Thu Oct 03 2002 - 07:42:59 CDT