0

Given that

there is a single tablespace pointing to one file, and there are many schemas pointing to that tablespace and there are many simultaneous jobs doing heavy DDL operations (dropping database, dropping indexes, creating a large database from scratch with data import) using these schemas,

is it possible the tablespace somehow locked so that there would be timeouts for some jobs using these schemas?

Adam Soliński
  • 444
  • 1
  • 8
  • 19
  • 2
    People aren't dropping and creating databases -- they may be dropping and creating objects. What error are you seeing? There's no locking at the tablespace level, but there may under some situations be contention for free space allocation or other resources. – David Aldridge Apr 11 '13 at 14:31
  • The error is a timeout when there are 2 or three different jobs working on different schemas in the same tablespace. When we move each job to a schema pointing to a separate tablespace, the problem disappears. Is it somehow possible that some objects are still shared among these jobs? The creation scripts all have explicitly given schema name before the object name, however, in some cases, for some packages and tables the explicit schema is missing. On the other hand, the creation scripts are all executed using these schemas so by default this should be written to their namespaces. – Adam Soliński Apr 12 '13 at 07:08
  • when I run ADDM I can see Index Block Split and Row Lock Waits as potential problems at the time of timeouts. how is that possible? if I use different schemas for each job? Is it the root cause of the problem that some index is shared within the tablespace? – Adam Soliński Apr 12 '13 at 14:12
  • There's no issue with multiple similarly-named objects from different schemas co-existing in the same tablespace. Row lock waits makes it sound like multiple sessions are modifying the same tables, so check the scripts with respect to the objects on which you are seeing them. – David Aldridge Apr 12 '13 at 16:55
  • Hi David, the problem is that each process that creates a connection with the DB uses a different schema. Each object is created in different schema. Still, we get these timeouts (possibly locks and some starving). – Adam Soliński Apr 15 '13 at 12:00
  • And when we make the system use separate tablespaces, everything starts working. No issues at all. Everything else is as before. The main problems seem to be around TX-Transaction (row lock contention) with total wait time 4,079 s TX-Transaction (index contention) with total wait time 2,727 s – Adam Soliński Apr 15 '13 at 13:34
  • "TX-Transaction (row lock contention)" = Two users are attempting to use the same row. Nothing to do with tablespaces. I'm sure that you have an error in your code. Are your scripts connecting as the same user and then relying on different owner specifiers for object creation, or are they connecting as different users? If connecting as the same user then use "ALTER SESSION SET CURRENT_SCHEMA = ..." http://docs.oracle.com/cd/B28359_01/server.111/b28310/general009.htm – David Aldridge Apr 15 '13 at 13:42
  • No, each instance of the system is connecting using its own unique schema. There is no possibility that there would be 2 same users at the same time. I am 100% percent sure about that as this is explicitly configured by me. – Adam Soliński Apr 15 '13 at 14:13
  • Best track which objects the locks are being held on then. – David Aldridge Apr 15 '13 at 15:39

0 Answers0