Table & lob segment growth in ORACLE

[Former Member]

Dear Team,

Please let me  know SQL query in oracle 10g for below statements:

1. which objects (atleast top 10) has grown/consume space (in GBs) in last 2-3 days.

2. which Lob segments (atleast top 10) has grown/consume space (in GBs) in last 2-3 days.

For Lob segment, i have query, see below, i think it require to modify:

select * from (

select

l.owner,

l.table_name,

l.column_name,

l.segment_name,

round(s.bytes/1024/1024) as mb

from dba_lobs l , dba_segments s

where l.owner = s.owner

and l.segment_name = s.segment_name

order by s.bytes desc

) where rownum < 11;

[former_member189725 · Accepted solution]

The SQL you provided would give you the top 10 LOB segments based on their size.

The best way to get the data is to execute the DBACOCKPIT transaction , then Space --> Segments -->Overview . Then select the tab "Top growth" and then select the Days. Sort the Type column , you will get the growth per day for LOBS, tables etc . This is the average growth/per day. This value may not give you values of the last 2-3days , but would provide you a generic trend .

Also make sure the performance collector job runs in the system and the database statistics are updated regularly.

The best you can do is use the above SQL to get the output for top 20 tables and lobs and then generate the output again after couple of days and compare the results manually.

Regards

Ratnajit

[Former Member · Accepted solution]

  Hi Ratnajit,

I tried  DBAcockpit, but it gives change growth per day,weeks or months.

   we require, growth of  objects for last 2-3 days....

[Former Member]

Dear All,

  This is the what, I m looking for???

select c.SNAP_ID,c.END_INTERVAL_TIME SNAP_TIME,a.OWNER ,a.OBJECT_NAME, a.OBJECT_TYPE, b.DB_BLOCK_CHANGES_DELTA

from dba_objects a,

(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,

DBA_HIST_SNAPSHOT c

where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID

order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc

[Former Member]

  Dear Experts,

   Please ignore above commands,

Its giving block changes & physical writes includes insert/update/delete, we need actual growth due to inserts . Unable to derive the same ..

  Please help, to solve this issue....

[Former Member]

  Dear Experts,

  Any Update.....

[Former Member]

Hi Sumit,

You can find many resources to calculate mostly growing 10 (or "n") lobs and tables, over the internet. But the easiest way is to query MONI(DB) table by an ABAP program. It would be make it easier to use ORA_SE_HISTORY_SUPPLIER function module while reading this information.

Best regards,

Orkun Gedik

[Former Member]

Hi Orkun,

  If you check this thread, i found query on internet, but our DB team comments are below for that query:

select c.SNAP_ID,c.END_INTERVAL_TIME SNAP_TIME,a.OWNER ,a.OBJECT_NAME, a.OBJECT_TYPE, b.DB_BLOCK_CHANGES_DELTA

from dba_objects a,

(select SNAP_ID,obj#,DB_BLOCK_CHANGES_DELTA from DBA_HIST_SEG_STAT where DB_BLOCK_CHANGES_DELTA > 20000 order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc) b,

DBA_HIST_SNAPSHOT c

where a.object_id=b.obj# and object_type='TABLE' and b.SNAP_ID=c.SNAP_ID

order by snap_id desc, DB_BLOCK_CHANGES_DELTA desc

Comments by DB-> Its giving block changes & physical writes includes insert/update/delete, we need actual growth due to inserts . Unable to derive the same ..

Would you explain me how MONI table works???

[Former Member]

>> Would you explain me how MONI table works???

Execute the ORA_SE_HISTORY_SUPPLIER function module with the parameters, below;

MONIKEY_HIST_AVG = -- <DBSID> DEF501

MONIKEY_HIST = -- <DBSID> DEF503

OWNER = <DBSID>

SEGNAME = <LOBSEGMENT NAME>

SEGTYPE = LOBSEGMENT

PROCESS = D

HIST_COMPL-CHG_SIZE column will be provide what you need. The table provides 1 month data in daily basis. So, you can execute the function module and calculate average value of last 3 days for each object. This is the easiest way to get this information, but it requires ABAP know-how.

Best regards,

Orkun Gedik

[Former Member]

   Dear orkun,

  Thankyou very much for your inputs. With you support, it seems we are near to close this issue.

1. one more favour, while clicking MONIKEY_HIST_AVG, i get 3 columns: SYS, DB,Filler.

     In which we are to input <DBSID>, 

2.one import parameter PARNAME, what should  come in this?

3.  What is DEF501 & 503

Thanks

Sumit

[Former Member]

Sumit,

>> 1. one more favour, while clicking MONIKEY_HIST_AVG, i get 3 columns: SYS, DB,Filler.

     In which we are to input <DBSID>, 

SY=--

DB=DBSID

FILLER=DEF501

>> 2.one import parameter PARNAME, what should  come in this?

You can left this parameter, empty.

>> 3.  What is DEF501 & 503

It is the function module id called in the ORA_SE_HISTORY_SUPPLIER function dynamically.

DEF501=ORA_COLL_SE_GENERAL_HISTORY

DEF503=ORA_COLL_SE_SPECIFIC_HISTORY

You can find the othe function modules in the "db02_coll_plan" table.

Additionally, call the function module by "DEF551" instead of "DEF503".

Best regards,

Orkun Gedik

[Former Member]

Hi orkun,

Below are the output of Fnc modules from quality server , but no report generated:

Output with Fnc Module->503

Import parameters               Value

MONIKEY_HIST_AVG                     <DBSID>     DEF501

MONIKEY_HIST                         <DBSID>     DEF503

OWNER                           <DBSID>

SEGNAME                         *

PARNAME

SEGTYPE                         TABLE

PROCESS                         D

Export parameters               Value

DATE_ANALYSIS

TIME_ANALYSIS                   00:00:00

Tables                          Value

HIST_AVG                           0 Entries

                  Result:           0 Entries

HIST_COMPL                         0 Entries

                  Result:           0 Entries

HIST_DELTA                         0 Entries

                  Result:           0 Entries

Output with Fnc Module->551

Import parameters               Value

MONIKEY_HIST_AVG                     <DBSID>     DEF501

MONIKEY_HIST                         <DBSID>    DEF551

OWNER                           <DBSID>

SEGNAME                         *

PARNAME

SEGTYPE                         TABLE

PROCESS                         D

Export parameters               Value

DATE_ANALYSIS

TIME_ANALYSIS                   00:00:00

Tables                          Value

HIST_AVG                           0 Entries

                  Result:           0 Entries

HIST_COMPL                         0 Entries

                  Result:           0 Entries

HIST_DELTA                         0 Entries

                  Result:           0 Entries

[Former Member]

You should set the exact object name, such as SEGNAME=LIKP. You can query the object names from "dba_objects" and execute the function module for each object, in the loop.

Additionally, do not forget to set the values for MONIKEY_HIST_AVG and MONIKEY_HIST, as below;

MONIKEY_HIST_AVG

SY=--

DB=<DBSID>

FILLER=DEF501

MONIKEY_HIST

SY=--

DB=<DBSID>

FILLER=DEF551

Best regards,

Orkun Gedik

[Former Member]

Hi orkun

This time we execute in Production server, situation still same, we are taking SY=.. means 2 dot

MONIKEY_HIST_AVG

SY DB       FILLER

.. PRD      DEF501

MONIKEY_HIST

SY DB       FILLER

.. PRD      DEF551

Output screen:

Test for function group      SDBORA4

Function module              ORA_SE_HISTORY_SUPPLIER

Uppercase/Lowercase

Runtime:        152,127 Microseconds

  Import parameters               Value

  MONIKEY_HIST_AVG                   ..<DBSID>     DEF501

  MONIKEY_HIST                       ..<DBSID>      DEF551

  OWNER                           <DBSID>

  SEGNAME                         TST03

  PARNAME

  SEGTYPE                         TABLE

  PROCESS                         D

  Export parameters               Value

  DATE_ANALYSIS

  TIME_ANALYSIS                   00:00:00

  Tables                          Value

  HIST_AVG                           0 Entries

                   Result:           0 Entries

  HIST_COMPL                         0 Entries

                   Result:           0 Entries

  HIST_DELTA                         0 Entries

                   Result:           0 Entries

[Former Member]

>> This time we execute in Production server, situation still same, we are taking SY=.. means 2 dot

It is two minus "--"

[Former Member]

Hi orkun,

No success again, let me  know, some effect of  update statistics not run properly

MONIKEY_HIST

SY DB

FILLER

-- PRD

DEF551

MONIKEY_HIST_AVG

SY DB

FILLER

-- PRD

DEF501

Test for function group	SDBORA4
Function module	ORA_SE_HISTORY_SUPPLIER

Uppercase/Lowercase

Runtime:

3,005,632 Microseconds

Import parameters

Value

MONIKEY_HIST_AVG	<DBSID>	DEF501
MONIKEY_HIST	<DBSID>	DEF551
OWNER	<DBSID>
SEGNAME	SOC3

  PARNAME

SEGTYPE	TABLE
PROCESS	D

Export parameters

Value

DATE_ANALYSIS	14.08.2012
TIME_ANALYSIS	05:00:22

Tables

Value

HIST_AVG	0 Entries
	Result:	0 Entries
HIST_COMPL	0 Entries
	Result:	0 Entries
HIST_DELTA	0 Entries
	Result:	0 Entries

Test for function group	SDBORA4
Function module	ORA_SE_HISTORY_SUPPLIER

Uppercase/Lowercase

Runtime:

54 Microseconds

Import parameters

Value

MONIKEY_HIST_AVG	<DBSID>	DEF501
MONIKEY_HIST	<DBSID>	DEF551
OWNER	<DBSID>
SEGNAME	TST03

  PARNAME

SEGTYPE	TABLE
PROCESS	D

Export parameters

Value

  DATE_ANALYSIS

TIME_ANALYSIS

00:00:00

Tables

Value

HIST_AVG	0 Entries
	Result:	0 Entries
HIST_COMPL	0 Entries
	Result:	0 Entries
HIST_DELTA	0 Entries
	Result:	0 Entries

[Former Member]

If you are able to read the statistics over the path below, it means that the statistics have been collected;

DB02 -> Space -> Segments -> Detailed analysis -> Select an object -> History

So, you can read the values by using this function module.

On the other hand, to find out the parameters on your system set a break-point on the statement, below;

IF NOT mkey_hist_avg = monikey_hist_avg.

Then, execute DB02 and walk on the path until "history" tab. When the system hit the break-point you can get the function parameters during the runtime. So, by using this sample values you can call the function module.

Best regards,

Orkun Gedik

[Former Member]

It should be seems like this

[Former Member]

  Hi,

>>If you are able to read the statistics over the path below, it means that the statistics have been collected;

DB02 -> Space -> Segments -> Detailed analysis -> Select an object -> History

We are able to see statistics under this tab from the day first, that's not problem.

I have found DB02.......->history OR Fnc Module ORA_SE_HISTORY_SUPPLIER same thing

Actually, I thought by using Fnc Module ORA_SE_HISTORY_SUPPLIER, my below stated purpose would be fulfilled. 

The objective is to get the list of table with highest in size and its increment for specific time interval.

using DB02-...->history or Fnc module don't directly provide mostly growth objects for 1 day or 2 day before . we have to analyse table by table , even early watch report also n't helpful.

Looking for any SQL query or ABAP program , any table.....

[Former Member]

Hi,

The function module provides many information including change size in daily basis, as I noted in my previous messages. So, you need to call the function module for each object, for example tables, from dba_objects table in the loop and collect the statistics. Then, filter the highest size of database object, such as table, lob segment and so on. But it requires an ABAP development effort, as I noted you. So, if you could able to execute the function module, you would call the function module for each object then get the result what exactly you need. If you want to contact with me, you can see my business card.

Best regards,

Orkun Gedik

[Former Member]

One more thing that OWNER parameter should be the database owner such as SAPSR3 while calling the function module

[Former Member]

  Hi Orkun,

  If we go by Ratnajit suggestion: DBACOCKPIT transaction , then Space --> Segments -->Overview . Then select the tab "Top growth" and then select the Days.

That's somehow realted to my requirement. No  require to input any object name,object type (which i want), it directly fetch a list of top  grow objects (table, LOBsegment, index etc) for present day.

Loophole Is that:

We can see highest growth of objects for  present day i.e. today not for yesterday or 2days back  (which is also my requirement). it's possible for week or month basis.

we take "top growth" data as recommended by Ratnajit   on MS excel on daily basis, so that, as of now when there is abnormal growth in database, we can do analysis.

Because in Fnc module, we have to put object name, there are zzillions  of  object in system, how would i know which object has grown last day

--------------------------------------------------------------------------------------------------------------------------------------------

About Fnc  Module : i checked with ABAP team about parameters of module, after break points, they found we are supplying correct parameter. there  is no issue. please see below screenshot for today:

[Former Member]

>>   If we go by Ratnajit suggestion: DBACOCKPIT transaction , then Space --> Segments -->Overview . Then select the tab "Top growth" and then select the Days.

That's somehow realted to my requirement. No  require to input any object name,object type (which i want), it directly fetch a list of top  grow objects (table, LOBsegment, index etc) for present day.

It will take more time and you need to calculate all the database objects, manually.

>> We can see highest growth of objects for  present day i.e. today not for yesterday or 2days back  (which is also my requirement). it's possible for week or month basis.

No. The system stores this information for last 30 days, in the MONI table. You should be missing somethings while calling the FM. As I see at your screenshot, you are still calling the function module with OWNER=PRD. But, it should be OWNER=<DBOWNER> such as OWNER=SAPSR3, as I noted in my previous message. It would be make it eaiser to get this information by developing an ABAP program to analyze (n) days growth. On the other hand, with the manual operation you should select every object manually and paste the result into the spreadsheet. Then, calculate the result. As you know that there are tons of database objects in a SAP database, it will take a long time.

Best regards,

Orkun Gedik

[Former Member]

  Hi orkun,

  Okay, we are able to execute Fnc module with entries, see below

[Former Member]

Good. We took the step forward. So, if you check the HIST_COMPL-CHG_SIZE column, you will be able to see the change size belong to the object. At this stage, you should read "dba_objects" table,call the FM in the loop for each object and calculate average value for last (n) days. After that you will be able to get mostly grown objects when the table sorted by the value that you've calculated.

Best regards,

Orkun Gedik