Thursday, October 24, 2013

Troubleshooting sudden tablespace growth



I started noticing that my application is inserting very high volume of data compared with previous day when we started researching why we are adding more data files to a particular tablespace.

Here is how I did the troubleshooting. First Find biggest segments consuming this TABLESPACE
SQL> col segment_name format A35
SQL> col partition_name format A35
SQL> select OWNER,SEGMENT_NAME,PARTITION_NAME,SEGMENT_TYPE,BYTES/(1024*1024) M_bytes from dba_Segments where TABLESPACE_NAME= 'ELF_TIBCO_DATA' order by 3;

OWNER                          SEGMENT_NAME                        PARTITION_NAME                      SEGMENT_TYPE          M_BYTES
------------------------------ ----------------------------------- ----------------------------------- ------------------ ----------
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48333                   LOB SUBPARTITION         1099
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48334                   LOB SUBPARTITION         1155
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48335                   LOB SUBPARTITION         1151
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48336                   LOB SUBPARTITION         1117
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48337                   LOB SUBPARTITION         1159
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48338                   LOB SUBPARTITION         1045
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48339                   LOB SUBPARTITION         1156
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48392                   LOB SUBPARTITION         1199
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48393                   LOB SUBPARTITION         1176
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48394                   LOB SUBPARTITION         1143
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48395                   LOB SUBPARTITION         1227
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48396                   LOB SUBPARTITION         1177
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48397                   LOB SUBPARTITION         1169
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48398                   LOB SUBPARTITION         1173
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48399                   LOB SUBPARTITION         1203
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48452                   LOB SUBPARTITION        22516
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48453                   LOB SUBPARTITION        22617
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48454                   LOB SUBPARTITION        22130
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48455                   LOB SUBPARTITION        22297
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48456                   LOB SUBPARTITION        22632
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48457                   LOB SUBPARTITION        22397
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48458                   LOB SUBPARTITION        22341
ELF_ADMIN                      SYS_LOB0000101461C00004$$           SYS_LOB_SUBP48459                   LOB SUBPARTITION        22341

We can easily correlate SEGMENT_NAME to TABLE_NAME if SEGMENT_TYPE is TABLE and to partition if it has partitions. But if SEGMENT_TYPE is LOB_PARTITION we need to find the table and partition associated with this lob segment.
To do that, try
SQL> col column_name format A30
SQL> select owner,TABLE_NAME , COLUMN_NAME ,SEGMENT_NAME,TABLESPACE_NAME  from dba_lobs where segment_name='SYS_LOB0000101461C00004$$';

OWNER                          TABLE_NAME                     COLUMN_NAME                    SEGMENT_NAME                        TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ----------------------------------- ------------------------------
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$           ELF_TIBCO_DATA

SQL> select TABLE_OWNER,TABLE_NAME ,COLUMN_NAME,LOB_NAME, PARTITION_NAME, LOB_PARTITION_NAME,TABLESPACE_NAME from dba_lob_partitions where table_name='TIBCO_EVENT_LOG_ATTACHMENTS';

TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                    LOB_NAME                       PARTITION_NAME                      LOB_PARTITION_NAME             TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ----------------------------------- ------------------------------ ------------------------------
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131017            SYS_LOB_P48091
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131018            SYS_LOB_P48151
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131019            SYS_LOB_P48211
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131020            SYS_LOB_P48271
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131021            SYS_LOB_P48331
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131022            SYS_LOB_P48391
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131023            SYS_LOB_P48451
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131024            SYS_LOB_P48511
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131025            SYS_LOB_P48571
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131026            SYS_LOB_P48631
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131027            SYS_LOB_P48691
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131028            SYS_LOB_P48751
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131029            SYS_LOB_P48811
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131030            SYS_LOB_P48871
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131031            SYS_LOB_P48931
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131101            SYS_LOB_P48991
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131102            SYS_LOB_P49051
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      TIB_EVT_LOG_ATT_20131103            SYS_LOB_P49111

Now we know that it is related to ELF_ADMIN.TIBCO_EVENT_LOG_ATTACHMENTS and is having big LOB attachments associated with ATTACHMENT_DATA column. As we started seeing this issue on 2013/10/23, we can
Check the details of SYS_LOB_P48451

SQL> select TABLE_OWNER,TABLE_NAME , COLUMN_NAME,LOB_NAME  , LOB_PARTITION_NAME,SUBPARTITION_NAME, LOB_SUBPARTITION_NAME from dba_lob_subpartitions where  TABLE_NAME ='TIBCO_EVENT_LOG_ATTACHMENTS' and LOB_PARTITION_NAME='SYS_LOB_P48451';

TABLE_OWNER                    TABLE_NAME                     COLUMN_NAME                    LOB_NAME                       LOB_PARTITION_NAME             SUBPARTITION_NAME              LOB_SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB1  SYS_LOB_SUBP48452
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB2  SYS_LOB_SUBP48453
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB3  SYS_LOB_SUBP48454
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB4  SYS_LOB_SUBP48455
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB5  SYS_LOB_SUBP48456
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB6  SYS_LOB_SUBP48457
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB7  SYS_LOB_SUBP48458
ELF_ADMIN                      TIBCO_EVENT_LOG_ATTACHMENTS    ATTACHMENT_DATA                SYS_LOB0000101461C00004$$      SYS_LOB_P48451                 TIB_EVT_LOG_ATT_20131023_SUB8  SYS_LOB_SUBP48459

It looks like there are 8 sub partitions associated with this LOB SEGMENT and compared with previous day we are inserting 22 times more attachments (error messages).
Now I approached app support and started investigating into the contents of this table.

3 different ways to write a SQL to get an aggregate output report



There are more than one way of doing things and it is true in Oracle SQL too. Take a look at following example where we want to generate a report with SUCCESS and ERROR count in a single line for ACTION=’Payment’.

SQL> select * from MAD_MESSAGES;

ACTION               STATUS               CREATE_TI
-------------------- -------------------- ---------
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              SUCCESS              23-OCT-13
Payment              ERROR                23-OCT-13
Payment              ERROR                23-OCT-13
Payment              ERROR                23-OCT-13
Payment              ERROR                23-OCT-13
Payment              ERROR                23-OCT-13
Reciept              SUCCESS              23-OCT-13

26 rows selected.

SQL> select action,status,count(*) from MAD_MESSAGES group by action,status;

ACTION               STATUS                 COUNT(*)
-------------------- -------------------- ----------
Payment              ERROR                         5
Payment              SUCCESS                      20
Reciept              SUCCESS                       1


First way with inline views
select a.SUCCESS_COUNT,b.ERROR_COUNT, ((b.ERROR_COUNT)/decode(a.SUCCESS_COUNT,0,1,a.SUCCESS_COUNT))*100 ERR_PCT from
(SELECT count(*) SUCCESS_COUNT FROM MAD_MESSAGES where STATUS='SUCCESS' and ACTION='Payment' and CREATE_TIME > (sysdate-1)) a,
(SELECT count(*) ERROR_COUNT FROM MAD_MESSAGES where STATUS='ERROR' and  ACTION='Payment' and CREATE_TIME > (sysdate-1)) b

SUCCESS_COUNT ERROR_COUNT    ERR_PCT
------------- ----------- ----------
           20           5         25

Plan
-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |     1 |    26 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS        |              |     1 |    26 |     6   (0)| 00:00:01 |
|   2 |   VIEW               |              |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE    |              |     1 |    24 |            |          |
|*  4 |     TABLE ACCESS FULL| MAD_MESSAGES |    20 |   480 |     3   (0)| 00:00:01 |
|   5 |   VIEW               |              |     1 |    13 |     3   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE    |              |     1 |    24 |            |          |
|*  7 |     TABLE ACCESS FULL| MAD_MESSAGES |     5 |   120 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
You could see that it is doing Full table scan 2 times.

Second way with CASE statement in select clause
select SUCCESS_COUNT,ERROR_COUNT,((ERROR_COUNT)/decode(SUCCESS_COUNT,0,1,SUCCESS_COUNT))*100 ERR_PCT
FROM
(
SELECT
count(case when STATUS='SUCCESS' THEN 1
                  else NULL
            end) SUCCESS_COUNT,
count(case when STATUS='ERROR' THEN 1
                  else NULL
            end) ERROR_COUNT
FROM MAD_MESSAGES where ACTION='Payment' and CREATE_TIME > (sysdate-1)
);

SUCCESS_COUNT ERROR_COUNT    ERR_PCT
------------- ----------- ----------
           20           5         25

Plan
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     1 |    26 |     3   (0)| 00:00:01 |
|   1 |  VIEW               |              |     1 |    26 |     3   (0)| 00:00:01 |
|   2 |   SORT AGGREGATE    |              |     1 |    24 |            |          |
|*  3 |    TABLE ACCESS FULL| MAD_MESSAGES |    25 |   600 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
It is doing only one Full table scan

Third Way using PIVOT function

SQL> select * from MAD_MESSAGES pivot( count(*) for (status) in ('SUCCESS' AS SUC,'ERROR' AS ERR) ) where ACTION='Payment';

ACTION                      SUC        ERR
-------------------- ---------- ----------
Payment                      20          5

PLAN
------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     1 |    24 |     4  (25)| 00:00:01 |
|   1 |  SORT GROUP BY PIVOT|              |     1 |    24 |     4  (25)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL | MAD_MESSAGES |    25 |   600 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Pivot function is doing only one full table scan.

Please note that you will receive ORA-00933: SQL command not properly ended in 10g as pivot function is not supported