Does spotlight give you the ability to see what the execution plan is for
the sql you are running?
What I would do is
- identify the problem (you have a slow running query/process by the sounds
of it so you already did this)
- trace the problem process capturing sql,binds and waits (if spotlight
can't do this ditch it)
- the problem will probably be a bad execution path, too many executions or
something similar, it could be contention showing up in waits
- identify a fix for where you are spending your time.
- test the fix
6 apply it.
- break out the beer and wait for the plaudits (this may only be a
theoretical thing I'm not sure if plaudits actually exist).
- go home
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"johndoe" <tobias_at_quova.com> wrote in message
news:10410bd.0307171739.6e710d87_at_posting.google.com...
> We are migrating from Oracle 8.1.7 to Oracle 9.2.0.3.
>
> Import of data etc went fine without any problems.
> Now i am testing applications that run on the DB.
>
> One application scrolls through a table with 14 million rows via a
> cursor.
> This never represented an issue with 8.1.7, however, on 9.2.0.3 the
> oracle process on the server just keeps growing and growing >3GB.
>
> It will grow until it cant allocate anymore memory and the instance
> hangs.
>
> I am using pga auto allocation set to 300MB.
> the servers total memory is 4GB, the SGA is connfigured to 2GB total.
>
> I have tried setting pga allocation to manual and 250kB per session,
> which did not resolve the problem.
>
> There are no other applications running on the server when this
> happens.
>
> Any help would be appreciated !
>
> init.ora:
>
>
############################################################################
##
> # Copyright (c) 1991, 2001, 2002 by Oracle Corporation
>
############################################################################
##
> # Initial Setup by Srini Chennur
> # Create Date 06/12/2003
> ######################################################
>
> ###########################################
> # Archive
> ###########################################
> log_archive_dest='/u0/oradata/CIS/arch'
> log_archive_format=%t_%s.dbf
> log_archive_start=true
> log_checkpoint_interval=4000000
>
> ###########################################
> # Cache and I/O
> ###########################################
> db_block_size=8192
> db_block_buffers=160788
> db_file_multiblock_read_count=16
> db_writer_processes=2
> log_buffer=1048576
>
> ###########################################
> # Cursors and Library Cache
> ###########################################
> open_cursors=255
> enqueue_resources=5000
> #session_cached_cursors=100
> #cursor_sharing=SIMILAR
>
> # _SQLEXEC_PROGRESSION_COST is set to ZERO to avoid SQL sharing issues
> # See Note:62143.1 for details, This will disable progression of Long
> Operations view
>
> _SQLEXEC_PROGRESSION_COST = 0
>
> ###########################################
> # Database Identification
> ###########################################
> db_name=CIS
> global_names = FALSE
>
> ###########################################
> # Diagnostics and Statistics
> ###########################################
> background_dump_dest=/u0/app/oracle/admin/CIS/bdump
> core_dump_dest=/u0/app/oracle/admin/CIS/cdump
> timed_statistics=TRUE
> user_dump_dest=/u0/app/oracle/admin/CIS/udump
> utl_file_dir=/u0/app/oracle/admin/CIS/udump/log
> utl_file_dir=/u0/app/oracle/admin/CIS/udump/out
> max_dump_file_size=10240
>
> ###########################################
> # File Configuration
> ###########################################
> control_files=( "/u0/oradata/CIS/control01.ctl",
> "/u1/oradata/CIS/control02.ctl")
>
> ###########################################
> # Instance Identification
> ###########################################
> instance_name=CIS
>
> ###########################################
> # Job Queues
> ###########################################
> job_queue_processes=10
> open_links = 4
>
> ###########################################
> # Miscellaneous
> ###########################################
> aq_tm_processes=1
> compatible=9.2.0.0.0
>
> ###########################################
> # Optimizer
> ###########################################
> hash_join_enabled=TRUE
> # query_rewrite_enabled=TRUE
> # parallel_automatic_tuning=TRUE
> star_transformation_enabled=FALSE
> optimizer_mode=CHOOSE
> # To reduce the permutaions required to arrive at an execution plan
> optimizer_max_permutations=1000
>
> # Make optimizer more inlcined to indexes
> optimizer_index_cost_adj=10
>
> # This will disable the logging information for Data Mining Tools
> transaction_auditing = FALSE
>
> ###########################################
> # Pools
> ###########################################
> java_pool_size=10485760
> large_pool_size=83886080
> shared_pool_size=438860800
>
> ###########################################
> # Processes and Sessions
> ###########################################
> processes=400
> sessions=500
>
> ###########################################
> # Redo Log and Recovery
> ###########################################
> fast_start_mttr_target=300
>
> ###########################################
> # Security and Auditing
> ###########################################
> remote_login_passwordfile=EXCLUSIVE
>
> ###########################################
> # Sort, Hash Joins, Bitmap Indexes
> ###########################################
> pga_aggregate_target=314572800
> workarea_size_policy=auto
> #sort_area_size=262144
>
> ###########################################
> # System Managed Undo and Rollback Segments
> ###########################################
> undo_management=AUTO
> undo_retention=10800
> undo_tablespace=RBS
> filesystemio_options=asynch
> use_indirect_data_buffers=true
> disk_asynch_io=true
> #############################
> # Parameter for Spotlight
> #############################
> O7_DICTIONARY_ACCESSIBILITY=TRUE
Received on Fri Jul 18 2003 - 03:09:11 CDT
