Tuesday, November 18, 2014

Shrink a datafile, when ALTER TABLESPACE COALESCE is not working.

In a 11.2.0.3 database, we tried to reclaim space from USERS tablespace.
We faced a problem.
We use the following query to the the end of a particular USERS' datafile [file_id=72, filename=/oradb/SBLCCPRD/oradata4/users03.dbf]

SELECT
        A.FILE_ID,
        A.BLOCK_ID,
        A.BLOCK_ID + A.BLOCKS - 1   END_BLOCK,
        A.BLOCKS,
        --ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
        A.BLOCKS*B.BLOCK_SIZE "SIZE",
        A.OWNER,
        A.SEGMENT_NAME,
        A.PARTITION_NAME,
        A.SEGMENT_TYPE
    FROM
        DBA_EXTENTS A, DBA_TABLESPACES B
    WHERE
        A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
UNION ALL
SELECT
        A.FILE_ID,
        A.BLOCK_ID,
        A.BLOCK_ID + A.BLOCKS - 1   END_BLOCK,
        A.BLOCKS,
        --ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) MBYTES,
        A.BLOCKS*B.BLOCK_SIZE "SIZE",
        'free'          OWNER,
        'free'          SEGMENT_NAME,
        NULL            PARTITION_NAME,
        NULL            SEGMENT_TYPE
    FROM
        DBA_FREE_SPACE A, DBA_TABLESPACES B
    WHERE
        A.FILE_ID=:A AND A.TABLESPACE_NAME=B.TABLESPACE_NAME
    ORDER BY 2 DESC;


We can see there were 5 contiguous free extents of 64 Kbytes each, total 320 Kbytes.

   
FILE_IDBLOCK_IDEND_BLOCKBLOCKSSIZEOWNERSEGMENT_NAMEPARTITION_NAMESEGMENT_TYPE
7216149521614959865536freefree
7216149441614951865536freefree
7216149361614943865536freefree
7216149281614935865536freefree
7216149201614927865536freefree
7216149121614919865536AMARGARICX_OTE_LOV_BACKUP_06052014TABLE
7216149041614911865536AMARGARICX_OTE_LOV_BACKUP_06052014TABLE
7216148961614903865536EFOTEINOPCX_OTE_LOV_0106TABLE
...........................


We tried to coalesce the tablespace, but it did not. The free extents did not get merged to one of 320 Kbytes.

SQL> alter tablespace users coalesce;
Tablespace altered. 



This is the datafile's size in bytes:
SELECT FILE_NAME, BYTES
FROM DBA_DATA_FILES
WHERE FILE_ID=72;

FILE_NAMEBYTES
/oradb/SBLCCPRD/oradata4/users03.dbf13229744128


When we tried to shrink the datafile, even by just 8 Kbytes, we got ORA-03297:

SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
ALTER DATABASE DATAFILE 72 RESIZE 13229735936
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


The solution was to purge the tablespace's recycle bin, even if there were no recycled objects near the end of the datafile. Furthermore, before I begin all this procedure, I had purged the database's recycle bin, using "PURGE DBA_RECYCLEBIN".

SQL> PURGE TABLESPACE USERS;
Tablespace purged.

SQL> ALTER DATABASE DATAFILE 72 RESIZE 13229735936;
Database altered.

   
FILE_IDBLOCK_IDEND_BLOCKBLOCKSSIZEOWNERSEGMENT_NAMEPARTITION_NAMESEGMENT_TYPE
721614920161495132262144freefree
7216149121614919865536AMARGARICX_OTE_LOV_BACKUP_06052014TABLE
7216149041614911865536AMARGARICX_OTE_LOV_BACKUP_06052014TABLE
7216148961614903865536EFOTEINOPCX_OTE_LOV_0106TABLE
...........................

Monday, August 4, 2014

ORA-01653, ORA-01658, ORA-01688, ORA-03233 even when there is free space in the tablespace.

ORA-01653: Unable to extend table %s.%s by string in tablespace %s
ORA-01658: Unable to create INITIAL extent for segment in tablespace %s
ORA-01688: Unable to extend table %s.%s partition %s by %s in tablespace %s
ORA-03233: Unable to extend table %s.%s subpartition %s by %s in tablespace %s
ORA-01654: Unable to extend index %s.%s by %s in tablespace %s
ORA-01683: Unable to extend index %s.%s partition %s by %s in tablespace %s
ORA-03234: Unable to extend index %s.%s subpartition %s by %s in tablespace %s

You may get the above errors, even if the is a lot of free space in the tablespace of the segment to be extended. This is because that free space is very fragmented.
When a segment asks for a new extent, the database will try to fit it in an equal or larger free extent. It will not fit it in more than one smaller free extents.
So, for instance, it the next extent to be created will be 2 MB and the largest available free extent is 1MB, you will get the above errors, even if the tablespace has gigabytes of total free space.

To check if this is the case, run the following query, which displays the free extents of a tablespace ordered by their size descending.
SELECT  C.NAME "DATAFILE NAME",
        A.FILE_ID "DATAFILE #",
        A.BLOCK_ID "BLOCK #",
        A.BLOCKS,
        ROUND(A.BLOCKS*B.BLOCK_SIZE/1024/1024,2) "MBYTES"
    FROM
        DBA_FREE_SPACE A,
        DBA_TABLESPACES B,
        V$DATAFILE C
    WHERE
        A.TABLESPACE_NAME = '[TABLESPACE NAME]'
        AND A.TABLESPACE_NAME = B.TABLESPACE_NAME
        AND C.FILE# = A.FILE_ID
ORDER BY 4 DESC;

If this is the case, you need to create larger free extents by reorganizing the tablespace or to add a new datafile, which at the moment it is created is a continuous free extent by itself.

Sunday, September 15, 2013

Use DBMS_SQLTUNE.IMPORT_SQL_PROFILE to force a hint to a query

DBMS_SQLTUNE.IMPORT_SQL_PROFILE is actually the procedure used when you implement a SQL Tuning Advisor's recommendation involving a better execution plan. But the SQL Tuning Advisor may not recommend the execution plan you know is the correct one or may even not recommend a new execution plan. If you know the hint that will improve your query's performance, then you may want to use DBMS_SQLTUNE.IMPORT_SQL_PROFILE to force it.

Let's create a scenario. Firstly, create 2 tables and 1 index in each one.
sqlplus system

SQL*Plus: Release 11.2.0.3.0 Production on Sun Sep 15 20:32:10 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE TABLE SYSTEM.OBJECTS AS SELECT * FROM DBA_OBJECTS;
Table created.

SQL> CREATE INDEX SYSTEM.OBJ_NAME ON SYSTEM.OBJECTS(OBJECT_NAME);
Index created.

SQL> CREATE TABLE SYSTEM.SEGMENTS AS SELECT * FROM DBA_SEGMENTS;
Table created.

SQL> CREATE INDEX SYSTEM.SEG_NAME ON SYSTEM.SEGMENTS(SEGMENT_NAME);
Index created.

We will use a query that joins those tables using the indexed columns.
SQL> SET AUTOT TRACEONLY
SQL> SET TIMING ON
SQL> SET LINESIZE 200
SQL> SELECT OBJECT_ID,
       OBJECT_NAME,
       SEGMENT_TYPE,
       TABLESPACE_NAME
  FROM SYSTEM.OBJECTS, SYSTEM.SEGMENTS
 WHERE OBJECT_NAME = SEGMENT_NAME AND SEGMENT_NAME LIKE 'DBA%';

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 98178177

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   895 |   130K|     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN                   |          |   895 |   130K|     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| SEGMENTS |   784 | 54880 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SEG_NAME |   141 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| OBJECTS  |  4970 |   383K|     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | OBJ_NAME |   895 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_NAME"="SEGMENT_NAME")
   3 - access("SEGMENT_NAME" LIKE 'DBA%')
       filter("SEGMENT_NAME" LIKE 'DBA%')
   5 - access("OBJECT_NAME" LIKE 'DBA%')
       filter("OBJECT_NAME" LIKE 'DBA%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        892  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

Now, let's find our query's SQL_ID.
SQL> SELECT SQL_ID, SQL_TEXT
FROM V$SQLAREA
WHERE SQL_TEXT LIKE 'SELECT OBJECT_ID%';

SQL_IDSQL_TEXT
gy6fj888vt27ySELECT OBJECT_ID, OBJECT_NAME, SEGMENT_TYPE, TABLESPACE_NAME FROM SYSTEM.OBJECTS, SYSTEM.SEGMENTS WHERE OBJECT_NAME = SEGMENT_NAME AND SEGMENT_NAME LIKE 'DBA%'

Both indexes are used, but let's assume we want to force full table scans on both tables.

SQL> DECLARE
  2  SQL_FTEXT CLOB;
BEGIN
SELECT SQL_FULLTEXT INTO SQL_FTEXT FROM V$SQLAREA WHERE SQL_ID = 'gy6fj888vt27y';

DBMS_SQLTUNE.IMPORT_SQL_PROFILE(
  SQL_TEXT => SQL_FTEXT,
  PROFILE => SQLPROF_ATTR('FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'),
  NAME => 'PROFILE_gy6fj888vt27y',
  REPLACE => TRUE,
  FORCE_MATCH => TRUE
);
END;
/
PL/SQL procedure successfully completed.

Let's see the new execution plan.
Execution Plan
----------------------------------------------------------
Plan hash value: 1823853794 [New plan hash value]

-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   895 |   130K|   388   (2)| 00:00:05 |
|*  1 |  HASH JOIN         |          |   895 |   130K|   388   (2)| 00:00:05 |
|*  2 |   TABLE ACCESS FULL| SEGMENTS |   784 | 54880 |    53   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| OBJECTS  |  4970 |   383K|   334   (1)| 00:00:05 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("OBJECT_NAME"="SEGMENT_NAME")
   2 - filter("SEGMENT_NAME" LIKE 'DBA%')
   3 - filter("OBJECT_NAME" LIKE 'DBA%')

Note
-----
   - SQL profile "PROFILE_gy6fj888vt27y" used for this statement [The SQL profile we created, is used]


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1419  consistent gets
        192  physical reads
          0  redo size
        892  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

You've may noticed the hint's format.
/*+ FULL(OBJECTS) FULL(SEGMENTS) */ is equal to 'FULL(@"SEL$1" "OBJECTS"@"SEL$1") FULL(@"SEL$1" "SEGMENTS"@"SEL$1")'.
Similarly, /*+ INDEX(OBJECTS OBJ_NAME) */ is equal to 'INDEX(@"SEL$1" "OBJECTS"@"SEL$1" "OBJ_NAME")'.