Solving event I/O and Cluster wait and freeing wasted space in LOBs

Issue: I/O and Cluster wait for LOBs

Sometimes in ADDM Report you can finding in "Top SQL Statements" following action:

Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID 
      "2adh0x2quv6uh". Additionally, investigate this statement for possible 
      performance improvements. You can supplement the information given here 
      with an ASH report for this SQL_ID.
      Related Object
         SQL statement with SQL_ID 2adh0x2quv6uh.
         INSERT INTO USERINFO (SESSIONID, USERID, DICTIONARY, EXPIRES) 
         VALUES(:1 , :2 , :3 , :4)
   Rationale
      I/O and Cluster wait for LOB "SCOTT.SYS_LOB0000155472C00008$$" with 
      object ID 154473 consumed 60% of the database time spent on this SQL 
      statement.

Segment Advisor recommend shrink table SESSIONINFORMATION for reclaim space, but shrinking does not work for LOBs::

ALTER TABLE "SCOTT"."USERINFO" ENABLE ROW MOVEMENT
ALTER TABLE "SCOTT"."USERINFO" SHRINK SPACE

Solution

Reclaiming Wasted Space:

1. Use CASCADE option for shrink a table and all of its dependent segments (including LOB segments).
   
   When you specify COMPACT, Oracle Database defragments the segment space and compacts the table rows
   but postpones the resetting of the high water mark and the deallocation of the space
   until you reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE COMPACT;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;
   

2. Check free unused bytes, returns information about the position of the high water mark and the amount of unused space in a segment.

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   VARIABLE total_blocks NUMBER
   VARIABLE total_bytes NUMBER
   VARIABLE unused_blocks NUMBER
   VARIABLE unused_bytes NUMBER
   VARIABLE lastextf NUMBER
   VARIABLE last_extb NUMBER
   VARIABLE lastusedblock NUMBER
   exec DBMS_SPACE.UNUSED_SPACE('SCOTT', 'USERINFO', 'TABLE', :total_blocks, -
       :total_bytes,:unused_blocks, :unused_bytes, :lastextf, -
       :last_extb, :lastusedblock)
   print
   

3. Reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete.
   

   ALTER TABLE SCOTT.USERINFO ENABLE ROW MOVEMENT;
   ALTER TABLE SCOTT.USERINFO SHRINK SPACE CASCADE;
   ALTER TABLE SCOTT.USERINFO DISABLE ROW MOVEMENT;
   

4. Check unused bytes again, script from step 2.
5. Issue deallocate unused space. Prior to deallocation, you can run the UNUSED_SPACE procedure, script from step 2.
   

   ALTER TABLE SCOTT.USERINFO  DEALLOCATE UNUSED;
   

6. Check unused bytes again, script from step 2

The LOBSEGMENT SYS_LOB0000155472C00008$$ has been reclaimed wasted space.

Resources

Oracle 10g New Features: Oracle Reclaiming Unused Space
Space, Object, and Transaction Management in Oracle Database 10g: Online Segment Shrink

Extract duplicate keywords from string

SQL example using wm_concat function:

 

select wm_concat(distinct keyword) keywords
  from (
        SELECT LEVEL                                 AS keyword_no
           ,      REGEXP_SUBSTR(keywords, '[^,]+', 1, LEVEL) AS keyword
           --, str     
           FROM  (
                  SELECT ROWNUM AS id
                  ,      'SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT' as keywords
                  FROM   dual
                 )
           CONNECT BY
                  -- INSTR for compatibility with Oracle 10g 
                  --INSTR(keywords, ',', 1, LEVEL-1) > 0
                  -- From Oracle 11g may used regexp_count 
                  LEVEL <= regexp_count(keywords,',') + 1
                  AND id = PRIOR id
                  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
  )          
;

KEYWORDS                  
--------------------------------------------
12345,ALLEN,KING,SCOTT                      
1 row selected.

SQL example using listagg function:

 

select listagg(keyword, ',') WITHIN GROUP (ORDER BY keyword) keywords 
  from (
        select distinct keyword
          from (
                SELECT LEVEL                                 AS keyword_no
                   ,      REGEXP_SUBSTR(keywords, '[^,]+', 1, LEVEL) AS keyword
                   --, str     
                   FROM  (
                          SELECT ROWNUM AS id
                          ,      'SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT' as keywords
                          FROM   dual
                         )
                   CONNECT BY
                          -- INSTR for compatibility with Oracle 10g 
                          --INSTR(keywords, ',', 1, LEVEL-1) > 0
                          -- From Oracle 11g may used regexp_count 
                          LEVEL <= regexp_count(keywords,',') + 1
                          AND id = PRIOR id
                          AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
          )
       )                
;

KEYWORDS                                                                        
--------------------------------------------------------------------------------
12345,ALLEN,KING,SCOTT                                                          
1 row selected.

Using PL/SQL function example:

 

DECLARE
    l_keywords varchar2(32767) := '10,20,30,40, 10, 20,SCOTT,ALLEN,KING,SCOTT,12345,SCOTT,12345,ALLEN,SCOTT';
    
    function extract_duplicate_keywords(p_keywords varchar2) return varchar2
    is   
       l_result varchar2(32767);
       cnt pls_integer;
       
       --l_str varchar2(4000);
       TYPE   nested_nbr_type IS TABLE OF varchar2(4000);
       nnt2   nested_nbr_type := nested_nbr_type();
       --nnt3   nested_nbr_type;
       answer nested_nbr_type;
       j pls_integer;
    BEGIN
       nnt2 := nested_nbr_type();
       
       cnt := regexp_count(p_keywords, '[^,]+');
       --dbms_output.put_line('Count: ' || cnt);
       for i in 1 .. cnt + 1
       loop
         nnt2.extend;
         nnt2(nnt2.last) := trim(REGEXP_SUBSTR(p_keywords,'[^,]+', 1, i));
         --dbms_output.put_line(i || ': ' || l_str);
       end loop;
       --nnt3 := nnt2;
       answer := nnt2 MULTISET INTERSECT DISTINCT nnt2;
       
       j := answer.first;
       while j is not null
       loop
         if answer(j) is not null then
           l_result := l_result || ',' ||answer(j);
           --dbms_output.put_line(answer(j));
         end if;
         j := answer.next(j);
       end loop;
       return ltrim(l_result, ',');
    END;
BEGIN
   l_keywords := extract_duplicate_keywords(l_keywords);
   dbms_output.put_line(l_keywords);
END;

How to connect SQLPlus without tnsnames.ora

Connect using one of service_name:

sqlplus username/password@//host[:port][/service_name]

where service_name can be one of simple name or domain name specified in service_names parameter:

show parameter service_names
ALTER SYSTEM SET service_names = 'ORCL', 'us.acme.com' COMMENT='Add new services' SCOPE=BOTH;

Procedure to write to alert.log

Link to original article Stored Proc to write to database alert logs author Charles Kim.

From the application or from database triggers, if I want to record anything to the alert log, I can simply invoke this stored procedure and write error messages to the alert log.

Most of the DBAs have implemented alert log scanners and will send alert notifications if new ORA- error messages pop up in the alert log file.
 

create or replace procedure write_msg_to_alert_log (
  p_message varchar2,
  p_oranum number default 20001
)
is
/*
  create procedure under system user
*/
begin
  sys.dbms_system.ksdddt;
  sys.dbms_system.ksdwrt(2, 'ORA-' || to_char(p_oranum) || ': ' || p_message);
end;
/
  
--grant execute on write_msg_to_alert_log to public;
grant execute on write_msg_to_alert_log to scott;

create public synonym write_msg_to_alert_log for system.write_msg_to_alert_log;

Recovery using the SWITCH DATABASE TO COPY command

Recover database from copy

rm users*.dbf
 
rman target /
RMAN> shutdown abort
 
# Recover database from copy
RMAN> startup mount;
RMAN> switch database to copy;
RMAN> recover database;
RMAN> alter database open;
 
SQL> select name,status from v$datafile;

Recover tablespace from copy

rm users*.dbf
 
rman target /
 
# Recover tablespace from copy
RMAN> sql 'alter tablespace users offline immediate';
RMAN> switch tablespace users to copy;
RMAN> recover tablespace users;
RMAN> sql 'alter tablespace users online';
 
SQL> select file_name from dba_data_files where tablespace_name='USERS';
NAME
----------------------------
/data/users01.dbf
/backup/users02.dbf
 
 
# Move datafile from /backup/$ORACLE_SID to /data/$ORACLE_SID directory
# while the Database is online
SQL> alter tablespace users offline;
 
# Move the file /db_backup/ptdb1/users02.dbf using operating system command
cp /backup/users02.dbf /data/users02.dbf
 
# Update the database data dictionary
SQL> alter database rename file '/backup/users02.dbf' to '/data/users02.dbf';
 
# Take tablespace USERS online:
SQL> alter tablespace users online;
 
# Delete file from old location using operating system command
rm /backup/users02.dbf