APPS_TS_TX_IDX growth rapidly.

[raiq1]

Dear Ermaan sir,

hope you are fine , your forum are one of the best in all oracle blog. Thanks for all of your support for oracle
&
community.

I have a issue in my production instance, we have 12.1.3 EBS apps, yesterday i have increase the size of APPS_TS_TX_IDX for 10 gb and today when i check the tablespace, it has consume all 10 gb .

it is totally unusally growth. Need your urgent advice to diagnose the issue & stop the unusual growth.


Please guide me.

Regards,

Raiq.

[ErmanArslansOracleBlog]

Thanks for the feedback raiq1. Doing my best to help..

This  APPS_TS_TX_IDX is an transactional index tablespace. So normally, if you didn't accidentally create a custom table there, that space is used by transactional apps indexes.

You can check the index growth using db_hist_snapshot and db_hist_seg_stat tables.
In dba_hist_seg_stat table we have a  table named SPACE_USED_DELTA.
So you can write a query and get the object growth report for the objects in APPS_TS_TX_IDX..

According to your findings, I can recommend an action plan for you.. But! first you need to find the object/objects stored in APPS_TS_TX_IDX tablespace and growing rapidly in last few days..

[raiq1]

Dear Ermaan sir,


I have checked with our Technical consultant & he has confirmed me that he has not create any custom table in this tablespace.


Is it possible that you provide me a query to futher investigate , Need your help to stop a unexpected  growth urgently.

After your query , i will send you a output for further diagnose.

Regards,

[ErmanArslansOracleBlog]

Your tech. consultant told you so.. okay.. You can check that as well.. dba_tables and dba_indexes have TABLESPACE_NAME columns.. So you can what do you have in that tablespace..

As for writing the growth monitor query;
currently, I don't have that time to write and test that query.. But I will share it here, "if I can find some time for it.."
In the meanwhile, you should try writing that query.. I already gave you the idea..

[raiq1]

Dear Ermaan,

Kindly check the below query, is it fullfil our requirement. Make sure i have oracle 11.2.0.4 db with 12.1.3 ebs.

please check & confirm me.


select
so.owner,
so.object_name,
--so.subobject_name,
so.object_type,
so.tablespace_name,
round(sum(ss.space_used_delta)/1024/1024) growth_mb
from
dba_hist_seg_stat ss,
dba_hist_seg_stat_obj so
where
ss.obj# = so.obj#
and ss.dataobj# = so.dataobj#
and so.owner != '** MISSING **' -- segments already gone
and so.object_name not like 'BIN$%' -- recycle-bin
and so.object_type not like 'LOB%'
and ss.snap_id > (
select min(sn.snap_id)
from dba_hist_snapshot sn
where
sn.dbid = (select dbid from v$database)
and sn.end_interval_time > trunc(sysdate) - &DAYS_BACK
)
group by
so.owner,
so.object_name,
--so.subobject_name,
so.object_type,
so.tablespace_name
order by 5 desc
fetch first &TOP rows only

[ErmanArslansOracleBlog]

Seems good.. It works good..
To be able to query the up-to-date data, you may consider taking an AWR snapshot before using it..
EXEC dbms_workload_repository.create_snapshot;

But it seems okay..

So add a condition for querying only the APPS_TS_TX_IDX 's segments and execute this query.
What is the output?

[raiq1]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

In this output, we don't see a big increase in APPS_TS_TX_IDX segments.. It is around 250 mb.. so, this doesn't tell us anything about your case..
Configure your script to query the relevant time interval that you see a big increase in APPS_TS_TX_IDX.. By looking that output, we can analyze and tell you something useful..

[raiq1]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

Use the previous query not this one..
Use that previous query but modify it to query the correct time interval..

You said you increased the tablespace size at time1
Then you said, you checked it at time2 and saw that all the newly added space was allocated..
So you should query the time interval between time1 and time2, you got the idea right?

[raiq1]

Dear ermaan sir,

As per your advice, we have to used previous query and modify the query for time 1 & time 2.

sir, is it possible to modify the query , sorry for that i am a junior level dba , not much expertise for sql level.

I am very thankful to you and it is helpful for me to future .

no problem, you take time and do it.

Regards,

[raiq1]

Dear ermaan sir,

Today also tablespace are growing very fast.

i have find one site where useful queries, check the below link and update which query is suitable for us.

http://db.geeksinsight.com/2012/10/15/scripts-databasetabletablespace-growth-report-using-awr/


main thing is which segment/objects are growing very fast in APPS_TS_TX_DATA & APPS_TS_TX_IDX , BOTH  tablespace are growing rapidly.

Kindly check above link and update me.

Regards,

[ErmanArslansOracleBlog]

Looks okay.. The important is that you need to query the relevant time interval and send me the query output where I can see the objects growing.. Thus, I may be able to see the reason by checking those growing objects and give you a recommendation or action plan.

[raiq1]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

That fast growth seems caused by the following objects;

ASDA21231
XLA_AE_LINES_N5
RA_CUST_TRX_LINE_GL_DIST_N1
AR_DISTRIBUTIONS_N3
WF_ITEM_ATTRIBUTE_VALUES_N1
WSH_DELIVERY_DETAILS_N12
IDX$$_F5BF0003
HADDAD_INVOICE_AGING
WSH_DELIVERY_DETAILS_N8
WSH_DELIVERY_DETAILS_N4

This ASDA21231 seems a custom index. What table does it belong?
XLA_AE_LINES_N5 seems also growing fast.

[raiq1]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

Sorry Raiq... Really sorry, I checked the wrong column.. The right column to check was Growth in MB..

So, the growth is caused by SYS_LOB0001144624C00040$$

What is the output of the following query ->

SELECT OBJECT_NAME, OBJECT_TYPE, STATUS FROM DBA_OBJECTS WHERE OBJECT_ID = '0001144624';

[raiq1]

CONTENTS DELETED

The author has deleted this message.

[ErmanArslansOracleBlog]

What is the size of your WF_NOTIFICATION_OUT table/queue ?
According to your query outputs, it seems lobs on wf_notification_out are growing fast..

This seems related with USER_DATA columns  WF_NOTIFICATIONS_OUT.

Check your wf_notifications_out -- its size and row count and the things stored in it, especially in user_date column.. Check these with your functional admins and analyze your workflow engine & configuration.. It seems there is a big workflow activity happening in your system..

[raiq1]

Dear Erman sir,

for wf_notification_out, the count is showing 6 rows and the user_data column are also showing a 6 rows.

for queue counting, kindly send me a query to find out.

select count(*) from APPLSYS.WF_NOTIFICATION_OUT;

  COUNT(*)
----------
         6


check the attached file.

Regards,
user_data.JPG