Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Stuck on insert into a global temporary table

Stuck on insert into a global temporary table

From: <roman_stangel_at_my-deja.com>
Date: Tue, 02 Jan 2001 15:24:59 GMT
Message-ID: <92srs3$3v$1@nnrp1.deja.com> 






Hello,



The symptoms of my problem are that execution of
a packaged stored procedure gets (inexplicably)
stuck on an insert into a global temporary
table.  A dynamic performance view v$sqlarea
confirms that the session is executing the
insert.  When trying to kill such a session, I
get a message that it can’t be killed and that it
can only be marked for removal.  Trying to
shutdown the database in NORMAL or IMMEDIATE mode
does not work either (waits indefinitely) and
only SHUTDOWN ABORT mode is effective.



I would love to hear from anyone who could shed
some light on why the execution gets stuck on the
insert statement.  Here are some details:



I am running Oracle 8.1.6.1.0 on RedHat 6.0.



I checked for lock collisions but found none.



I have found out that a certain critical mass of
work is necessary to be performed in the
procedure before the problem exhibits itself.



Also, I found that it makes no difference if the
insertion into the temporary table is done in
one “insert from select” statement, or they are
inserted one by one.  This second option allowed
me to find out that it gets stuck consistently
after inserting a certain number of records.
(The number records inserted before the procedure
gets stuck is consistent unless certain execution
conditions change, such as a change in temporary
table space definition – see below.)



It makes no difference whether I commit each
record or try to commit after all records are
inserted.  Also adding nolog option to the insert
statement makes no difference.



Seeing that the ABORT option was necessary to
stop the database got me thinking that it might
be some database configuration issue, so I
checked the database logs, but did not find
anything noteworthy except several messages in
alert_ORCL.log: cannot allocate new log /
Checkpoint not complete.  The conclusion from a
little research on this topic was that this is
not a serious problem; it may cause delays in
terms of minutes in most cases.



Since the inserts were concerning global
temporary tables, I got suspicious about the
temporary table space for this schema, and I
changed it from TEMP to another temporary table
space, however this did not fix the problem
regardless of how I set up this table space.
However, there was some indirect relationship to
the problem, because when I ran the test after
some parameter changes, the session would get
stuck on a different record. Again, the record
where it got stuck was consistently same for each
parameter change.  In each case all table spaces
seemed far from having run out of space.



Any ideas?  At this point I am thinking that it
is an Oracle configuration issue.  (My


installation is pretty much out-of-the-box.)  I
have attached the contents of my init.ora file,
and will be glad to provide any other information
necessary.



Thanks in advance,



Roman Stangel




#

# Copyright (c) 1991, 1998 by Oracle Corporation

#

##################################################

############################

# 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.

#

# NOTE: Parameter values suggested in this file

are based on conservative


# estimates for computer memory availability. You

should adjust values upward


# for modern machines.

#

##################################################

#############################




db_name = "ORCL"


instance_name = ORCL



service_names = ORCL



# db_files =



80


       # SMALL



# db_files =



400


       # MEDIUM



# db_files =



1500


       # LARGE


control_files =


("/home/oracle/u03/oradata/ORCL/control01.ctl", "/
home/oracle/u03/oradata


/ORCL/control02.ctl", "/home/oracle/u03/oradata/OR
CL/control03.ctl")



open_cursors = 100


max_enabled_roles = 30


# db_file_multiblock_read_count =


8                                   # SMALL


# db_file_multiblock_read_count =

16                                  # MEDIUM


# db_file_multiblock_read_count =

32                                  # LARGE





_use_ism = false


db_block_buffers = 2048  # INITIAL


# db_block_buffers =



100


# SMALL


# db_block_buffers =



550


# MEDIUM


# db_block_buffers =



3200


# LARGE



shared_pool_size = 15728640  # INITIAL


# shared_pool_size =



3500000


# SMALL


# shared_pool_size =



5000000


# MEDIUM


# shared_pool_size =



9000000


# LARGE



large_pool_size = 614400


java_pool_size = 20971520



log_checkpoint_interval = 10000


log_checkpoint_timeout = 1800



processes = 50  # INITIAL


# processes =



50


      # SMALL



# processes =



100


      # MEDIUM



# processes =



200


      # LARGE


log_buffer = 163840  # INITIAL


# log_buffer =



32768


     # SMALL



# log_buffer =



32768


     # MEDIUM



# log_buffer =



163840


     # LARGE


audit_trail = true # if you want auditing
timed_statistics = true # if you want timed
statistics


# max_dump_file_size = 10000  # limit trace file

size to 5M each



# Uncommenting the lines below will cause

automatic archiving if archiving has


# been enabled using ALTER DATABASE ARCHIVELOG.

# log_archive_start = true

# log_archive_dest_1



= "location=/home/oracle/u01/admin/ORCL/arch"


# log_archive_format = arch_%t_%s.arc




# If using private rollback segments, place lines

of the following


# form in each of your instance-specific init.ora

files:


#rollback_segments = ( RBS0, RBS1, RBS2, RBS3,

RBS4, RBS5, RBS6 )



rollback_segments = ( IPW2 )



# If using public rollback segments, define how

many


# rollback segments each instance will pick up,

using the formula


#   # of rollback segments = transactions /

transactions_per_rollback_segment


# In this example each instance will grab 40/10 =

4:


# transactions = 40

# transactions_per_rollback_segment = 10




# Global Naming -- enforce that a dblink has same

name as the db it connects to


# global_names = false




# Edit and uncomment the following line to

provide the suffix that will be


# appended to the db_name parameter (separated

with a dot) and stored as the


# global database name when a database is

created.  If your site uses


# Internet Domain names for e-mail, then the part

of your e-mail address after


# the '@' is a good candidate for this parameter

value.


# db_domain = us.acme.com   # global database

name is db_name.db_domain



# Uncomment the following line if you wish to

enable the Oracle Trace product


# to trace server activity.  This enables

scheduling of server collections


# from the Oracle Enterprise Manager Console.

# Also, if the oracle_trace_collection_name

parameter is non-null,


# every session will write to the named



collection, as well as enabling you


# to schedule future collections from the console.

# oracle_trace_enable = true




# define directories to store trace and alert

files


background_dump_dest


= /home/oracle/u01/admin/ORCL/bdump


core_dump_dest = /home/oracle/u01/admin/ORCL/cdump


#Uncomment this parameter to enable resource

management for your database.


#The SYSTEM_PLAN is provided by default with the

database.


#Change the plan name if you have created your

own resource plan.# resource_manager_plan
= system_plan


user_dump_dest = /home/oracle/u01/admin/ORCL/udump



db_block_size = 8192



remote_login_passwordfile = exclusive



os_authent_prefix = ""



# The following parameters are needed for the

Advanced Replication Option


job_queue_processes = 4


job_queue_interval = 60


distributed_transactions = 10


open_links = 4



mts_dispatchers = "(PROTOCOL=TCP)


(PRE=oracle.aurora.server.SGiopServer)"


# Uncomment the following line when your listener

is configured for SSL


# (listener.ora and sqlnet.ora)

# mts_dispatchers = "(PROTOCOL=TCPS)



(PRE=oracle.aurora.server.SGiopServer)"



compatible = "8.1.0"


sort_area_size = 65536


sort_area_retained_size = 65536





Sent via Deja.com


http://www.deja.com/
Received on Tue Jan 02 2001 - 09:24:59 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US