Monthly Archives: November 2017

move LOB results in unusable indexes

Posted on by
Reply

By Brian Fitzgerald

It is well known that moving a table segment causes indexes to become unusable. It is less known that moving an LOB causes indexes to become unusable.

Moving a table segment causes indexes to become unusable because indexes point to the table’s physical location. After a table move, the index still points to the old table location, so the index is invalid. Oracle marks the index as unusable.

alter table t
move
tablespace ts0002

If a table has an LOB column, you can move the table and the LOB in a single command. Again, the move result in unusable indexes.

alter table t
move
tablespace ts0002
lob ( l ) store as ( tablespace ts0002 )

If only the LOB is moved, not the table, indexes are also left unusable. This has surprised some users, but it is the expected behavior. The behavior is documented.

alter table t
move
lob ( l ) store as ( tablespace ts0002 )

Further investigation shows that when an LOB is moved, the table segment is also “moved”. The table data_object_id and the table segment header block change, and every rowid in the table segment changes. Indexes on the table become unusable.

Unusable indexes are unintuitive because the command syntax is to move an LOB column, and the column is not indexed. Why should an index become unusable if the column that is moved is not indexed?

A Google search on “move lob unusable index” turns up questions and answers on this subject, and lead to the documentation, and to Note 1228324.1, which state that moving and LOB leading to unusable indexes is the expected behavior.

Point taken.

But why?

Specifically, why does moving an LOB result in “moving” the table segment?

The reason is clear when you consider that whether the move succeeds of fails, the operation must leave the schema in a consistent state.

Moving an LOB requires updating the LOB locator in every row. It’s similar to an update of every row. Generally, updates can be done conventional or direct path. The choice is an implementation decision. Direct path can be more efficient and less dependent on instance resources such as memory and undo. Alter table is DDL. Direct path is commonly used in DDL.

A DDL operation must succeed or fail atomically. We don’t want to end up half way where some LOBs are migrated and some not. The LOBs are in a single segment, which is the smallest unit of administration. You can’t move part of a segment. The entire move must succeed or fail. This is implemented by making a copy of the LOB segment, and a copy of the table segment. When the copying is complete, the LOB and the table are switched to point to the new copies. After a successful LOB move, the LOB has been copied, the table has been copied, and the new LOB locators point to the copied LOB. If the alter table is interrupted, the copies are discarded and the state is the same as it was to start with. After a failed LOB move, the original LOB locators simply point to the original LOB.

Note that moving an LOB will require not only free space for the new LOB, but also, space for the table.

What follows is a demo of moving an LOB only. There are a few points to notice:

  • Table T LOB column L moves from tablespace TS0001 to TS0002
  • Table T tablespace remains TS0001.
  • Table T data object id changes from 74914 to 74918. The segment in fact “moved”.
  • Table T segment header block changes from 130 to 170. The segment “moved”.
  • Because the segment moved, index I is left unusable
  • tkprof shows direct path read and write, supporting the earlier comment.

Conclusions:

  • Whether you move the table, the LOB, or both, indexes will become unusable.
  • Unusable indexes are a result of a direct path LOB move implementation.
  • Space for the table and the LOB moves have to be planned for.
conn u/u
@ columnformat.sql
set linesize 32767
set trimspool on
column l format a1

drop table t purge;
create table t
(
c varchar2(1),
l clob
)
lob ( l )
store as ( tablespace ts0001 disable storage in row )
tablespace ts0001
;

insert into t ( c, l )
select 'A', 'Z'
from dual
connect by level <= 1000
;
commit;
create index i on t ( c );

select df.file_id,
df.tablespace_name
from dba_data_files df
where df.tablespace_name in ( 'TS0001', 'TS0002' );

@ trace.10046.on.sql

@ sel.t.info.sql

prompt moving the LOB only
alter table t
move
lob ( l ) store as ( tablespace ts0002 )
;

@ sel.t.info.sql
@ trace.10046.on.sql
@ tkprof.sql

SQL> @ movelob.sql
Connected.

Table dropped.

Table created.

1000 rows created.

Commit complete.

Index created.

FILE_ID TABLESPACE_NAME
---------- ------------------------------
 8 TS0001
 9 TS0002

Session altered.

Session altered.

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK EXTENTS
------------------------------ ------------------------------ ------------------ ------------------------------ ------------ ----------
U T TABLE TS0001 130 2

OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID
------------------------------ ------------------------------ ------------------------------ --------------
U T TABLE 74914

OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
U T L TS0001

TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
T I VALID

moving the LOB only

Table altered.

OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME HEADER_BLOCK EXTENTS
------------------------------ ------------------------------ ------------------ ------------------------------ ------------ ----------
U T TABLE TS0001 178 2

OWNER OBJECT_NAME OBJECT_TYPE DATA_OBJECT_ID
------------------------------ ------------------------------ ------------------------------ --------------
U T TABLE 74918

OWNER TABLE_NAME COLUMN_NAME TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
U T L TS0002

TABLE_NAME INDEX_NAME STATUS
------------------------------ ------------------------------ --------
T I UNUSABLE

Session altered.

Session altered.

TRACEFILENAME
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db12201/db12201/trace/db12201_ora_15552.trc

BASE
-----------------
db12201_ora_15552

TKPROF: Release 12.2.0.1.0 - Development on Sun Nov 19 18:20:59 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

created file:
db12201_ora_15552.tkp

********************************************************************************

alter table t
move
lob ( l ) store as ( tablespace ts0002 )

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 2 1 0
Execute 1 0.58 13.03 1004 87184 61243 1000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.59 13.03 1004 87186 61244 1000

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
 0 0 0 LOAD AS SELECT T (cr=87182 pr=1000 pw=1007 time=12586318 us starts=1)
 1000 1000 1000 TABLE ACCESS FULL T (cr=16 pr=0 pw=0 time=0 us starts=1 cost=5 size=237000 card=1000)

Elapsed times include waiting on following events:
 Event waited on Times Max. Wait Total Waited
 ---------------------------------------- Waited ---------- ------------
 PGA memory operation 3 0.00 0.00
 direct path read 999 0.88 11.40
 local write wait 8 0.00 0.00
 acknowledge over PGA limit 1 0.00 0.00
 direct path write 1 0.01 0.01
 enq: RO - fast object reuse 4 0.06 0.12
 db file sequential read 4 0.15 0.15
 enq: CR - block range reuse ckpt 4 0.14 0.14
 log file sync 1 0.00 0.00
 SQL*Net message to client 1 0.00 0.00
 SQL*Net message from client 1 0.00 0.00
********************************************************************************

 

 

 

 

 

 

 

 

 

 

SQL*Net break/reset to client

Posted on by
1

By Brian Fitzgerald

Introduction

This is a demonstration of event SQL*Net break/reset to client. This demo will assist understanding the origin of SQL*Net break/reset to client events on your database. The application details leading to SQL*Net break/reset to client may not be directly accessible to the DBA.

A session waits on SQL*Net break/reset to client when an error happens and the Oracle instance is trying to notify the client. Until the client acknowledges, the session waits on event SQL*Net break/reset to client. SQL*Net break/reset to client waits appear in the OEM display under the Application wait class.

To simulate the scenario, I run a sqlplus script that will sleep and eventually get an error. Before the error is reached, I suspend the client, so that it cannot receive any message from the instance. As a result, the session waits on SQL*Net break/reset to client.

Event SQL*Net break/reset to client is similar to SQL*Net message to client and SQL*Net more data to client in the sense that that Oracle has some information to communicate to the client. The waiting will persist if the client is preoccupied. Whereas “break/reset” is in the Application wait class, “message” and “more data” waits are in the Network wait class.

In summary, SQL*Net break/reset to client means that an error occurred and Oracle is trying to notify the client.

Demo

For this demonstration, I will open three windows:

  1. Run a PL/SQL script
  2. Suspend the batch, sleep, and resume the batch
  3. Check the event in v$session

Script

Here is the script used for the demo.

$ cat sqlnet.break.reset.sql
@ conn.pdba.u.sql
set verify off

declare
 l_rslt number;
begin
 dbms_lock.sleep(&&1);
 l_rslt := 0/0;
end;
/
quit

Oracle will sleep, reach division by zero, and try to notify the client about the error.

Window 1:

$ sqlplus /nolog @ sqlnet.break.reset.sql 20 & fg
[1] 30878
sqlplus /nolog @ sqlnet.break.reset.sql 20

SQL*Plus: Release 12.2.0.1.0 Production on Sat Nov 4 16:23:32 2017

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected.

Note that the process id is 30878.

Window 2:

Here we want to simulate an client process that should be waiting for the oracle server, but is preoccupied with something else. We do that by suspending sqlplus for 120 seconds.

$ kill -STOP 30878 ; sleep 120 ; kill -CONT 30878

Window 1:

sqlplus responds

[1]+ Stopped sqlplus /nolog @ sqlnet.break.reset.sql 20

Window 3:

In fact, Oracle has tried to notify the application (sqlplus), and is waiting on a response that will not come until the client wakes up. I.e. the session is waiting on SQL*Net break/reset to client.

SQL> select sid, sql_id, event, p2, p2text 
from v$session 
where service_name = 'u' 
and sid != sys_context('userenv','sid');

 SID SQL_ID        EVENT                           P2 P2TEXT
---- ------------- ------------------------------ --- ------
 63                SQL*Net break/reset to client    0 break?

The text for p2, in this case, is “break?”. Nonzero means break and 0 means reset, so this case is a reset, which is the more common case.

sql_id is null. The session is a running statement not now, but in the past. An error occurred, and Oracle is trying to notify the client. In most cases of SQL*Net break/reset to client, sql_id will be null.

At this point, I have staged a scenario with SQL*Net break/reset to client.

Window 1:

After 120 seconds, window 2 issues kill -CONT 30878. sqlplus wakes up. The output appears:

$ declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 5

Disconnected from Oracle Database 12c Enterprise Edition 
Release 12.2.0.1.0 - 64bit Production

Monitoring

Event SQL*Net break/reset to client appears in OEM in the Application class. Refer to the bright red peak, at right.

active

The bright red region is a hyperlink. You can drill in to the Application class by clicking on it. The display shows the wait breakdown, which shows one session waiting on SQL*Net break/reset to client.

appl

Notice that the Top SQL section is empty. No statement is active. The session appears as a hyperlink, so you can drill down by clicking on Session ID 49.

ses

Impact

One session waiting on SQL*Net break/reset to client will probably have no adverse impact on the instance, or on other sessions. If the session is in a critical batch, then resolving this wait issue will be a high priority. In some cases, SQL*Net break/reset to client appears as a result of manual operation of various tools.

The Application class

We often associate the Application class with blocking, such as the row-level locking that leads to, enq: TX – row lock contention, or the enq: TM – contention waits associated with select for update or missing foreign key indexes. Those waits are associated with statements, and with tables or indexes. SQL*Net break/reset to client, although it is in the Application class, is not associated with a SQL statement or with locking. However, the name “Application” class is a clue that resolving this wait will require coordination between the DBA and others who develop, manage, or use the application.

Root Causes in Production

In a serious production application, waits on SQL*Net break/reset to client could be a symptom of an application design issue. It could be that the application launches a query and goes off down some other code path while the query is running. When an error appears in the Oracle session, the application is not attending to it right away. In other words, there is a lack of coordination between the client and the Oracle instance. The reason for this could reside in the application design and the database driver, including version and patch status.

Note also that the name SQL*Net break/reset to client does not necessarily mean that there is a network problem. As you can see, resolution of this issue did not require assistance from the network support team, or the operating system administrator.

When investigating issues such as this one, it is helpful to talk to other people who know about other aspects of the application, and engage in a sort of “brainstorming session”.

If you understand the SQL*Net break/reset to client event, you might not be able to resolve it on your own, but you will have an opportunity to lead an investigation that will eventually yield a solution.