From the time I was an APPS DBA on largest EBS project in Croatia, I learned that monitoring database has important role of any DBA task. This is not only because of storage planning but also to see if anything irregular is happening in database. Here is the true story and how I managed that unpleasant situation and help developers to find their bugs and calm down managers about capacity planning and budgeting!
All here mentioned (including data) are real values from one EBS instance so many experienced APPS DBA may compare their sizes with here shown and make some conclusions.
The story
At the beginning, for the non experienced EBS people let me tell that in EBS 11.5.10.2 there are more then 22.100 tables in database, so knowing them or monitoring (like it is on systems with several hundred tables) is more then not possible without good tool or some automated help.
Case nr. 1.
The problem arise when my EBS instance grow in two months for more then 50%, from 105 GB to 155GB. I have create SR and ask them what should I suspect on and get an answer: what has grow up from previous period? I was astonished that they have no clue for their lead product what could really grow so fast. The best of all was when a lady from SR said: "The best should be that you contact your application provider about details what is really growing!"... !??
The situation was outrageous when my database fall in severity 1, which was direct circumstance of uncontrolled grow of some tables (which should be purged and controlled in any mean)! Situation from that moment look like:
Top 5 objects by NUMBER of records row. Monitored periods 20070901-20070701
OWNER SEGMENT_NAME SEGMENT_TYPE NR_RECORDS REC_GROW %
-------------------------------------------------------------------------------------------------------------------------------------
XXHY XXHY_CC_PAY_PLAN_AMOUNTS_ALL TABLE 41.613.708 8.010.912 23,84
APPLSYS WF_ITEM_ATTRIBUTE_VALUES TABLE 7.709.606 7.591.294 6.416,33
APPS XXHY_CC_PAYMENT_PLAN_DISCO_MV TABLE 39.684.371 6.288.759 18,83
GL GL_JE_LINES TABLE 11.314.140 4.550.960 67,29
Top 5 objects by SIZE grow. Monitored periods 20070901-20070701
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE [MB] grow [MB] %
----------------------------------------------------------------------------------------------------------------------------------
AR AR_TAX_EXTR_SUB_COM_EXT TABLE 3.804,75 3.804,63 3.043.700,00
AR AR_TAX_EXTRACT_SUB_ITF TABLE 3.202,13 3.202,00 2.561.600,00
XXHY XXHY_HLK_POSTING_CARD_XML TABLE 2.435,13 2.434,88 973.950,00
GL GL_JE_LINES TABLE 2.510,63 1.330,00 112,65
After enabling some purging concurrents, as I was having 24x7 Oracle people on site, and they didn’t show any special interest about my complaints on database grow subject, I left this case
beside for "some better time".
Case nr. 2.
This time came when 7 months later, database size raised from 85GB to 250 GB with average growing of 30-40 GB per month (with every month bigger grow!). Situation from that moment looks like:
Top 5 objects by NUMBER of records grow. Monitored periods 20080101-20070701
OWNER SEGMENT_NAME SEGMENT_TYPE NR_RECORDS REC_GROW %
-------------------------------------------------------------------------------------------------------------------------------------
XXHY XXHY_CC_PAY_PLAN_AMOUNTS_ALL TABLE 59.309.318 25.706.522 76,50
GL GL_JE_LINES TABLE 28.111.423 21.348.243 315,65
APPS XXHY_CC_PAYMENT_PLAN_DISCO_MV TABLE 53.388.260 19.992.648 59,87
GL GL_IMPORT_REFERENCES TABLE 17.497.831 17.457.633 43.429,11
AR AR_DISTRIBUTIONS_ALL TABLE 16.409.478 11.893.345 263,35
Top 5 objects by SIZE grow. Monitored periods 20080101-20070701
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE [MB] grow [MB] %
----------------------------------------------------------------------------------------------------------------------------------
GL GL_JE_LINES TABLE 6.995,25 5.814,63 492,50
APPS XXHY_CC_PAYMENT_PLAN_DISCO_MV TABLE 11.014,00 3.904,00 54,91
GL GL_IMPORT_REFERENCES TABLE 2.758,13 2.752,25 46.846,81
AR AR_RECEIVABLE_APPLICATIONS_ALL TABLE 3.129,75 2.406,38 332,66
XXHY XXHY_CC_PAY_PLAN_AMOUNTS_ALL TABLE 4.750,63 2.105,63 79,61
And
GL.GL_JE_LINES history data looks like:
GL.GL_JE_LINES(TABLE)
Period SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------- -----------------------------------------------------------
20070701 1.180,63 1.180,63 0,00 0,00 6.763.180 6.763.180 0,00 0,00
20070901 2.510,63 1.330,00 112,65 112,65 11.314.140 4.550.960 67,29 67,29
20071101 5.174,38 2.663,75 106,10 338,27 21.853.222 10.539.082 93,15 223,12
20080101 6.995,25 1.820,87 35,19 492,50 28.111.423 6.258.201 28,64 315,65
20080301 8.678,25 1.683,00 24,06 635,05 33.910.408 5.798.985 20,63 401,40
20080501 10.009,75 1.331,50 15,34 747,83 38.756.116 4.845.708 14,29 473,05
20080701 10.841,00 831,25 8,30 818,24 41.629.108 2.872.992 7,41 515,53
20080802 13.160,13 2.319,13 21,39 1.014,67 49.606.017 7.976.909 19,16 633,47
20080901 13.677,50 517,37 3,93 1.058,49 51.380.285 1.774.268 3,58 659,71
20081001 14.812,63 1.135,13 8,30 1.154,64 55.277.203 3.896.918 7,58 717,33
===========================================================================================================================
Now with DSS tool it is very easy to see that
GL_JE_LINES is growing very rapidly (first in Top 5 for longer period!) without any known reason. In that time I was having some primitive SQLs for monitoring size and with this primitive tool I saw that
GL_JE_LINES growing-I was not aware how fast and what is the trend of grow. Unfortunatelly I was not aware what else is growing and the impact of that to the performance.
When I say my findings to Oracle people, firstly they defend that our employees have entered manually all those entries. After some math calculation, because number or inserted records was really huge, it was obvious that this is not possible to do manually in such a short period. After that I contacted directly Oracle’s lead developer. He realize very soon that "Oracle feature" was in our environment reproduced as a bug.
The feature is that Oracle automatically tries book out unallocated items in the general ledger. This is done through one concurrent which runs every night (right now I cannot remember the name). This concurrent is programmed to work in a way that firstly inserts unallocated items and when finds out that there are irresolvable errors, it insert all inserted items again with contra sign, creating in this case two times of all unallocated items on every job. If I say that in our database we have huge number of such an unknown items, every night
GL_JE_LINES was getting bigger and bigger without reason. And users were complaining. So I was the initiator of bug resolving without knowledge what is going on!
In that moment I was determent to start reacting regardless all! This is the time I wrote first version of
Database Size Stat solution. All after is nice history….
The solution
Database Size Stat (DSS) solution is set of two tables and one package. Role of package (
db_size_pkg) is to collect data from database and use as common storage for all exposed procedures. Collection is taken on some regular basis into one table (
db_size). In mine case 2 months were enough for my needs…but you can make run it more often if you want.
When data are collected (for at least two different dates), then with many other procedures from
db_size_pkg package anyone it is easy to see anything about database size: size, grow, trend, history comparison, new/dropped objects and all other possible statistic in database which I ound interesting.
DSS is tested against 10/11g databases and is not compatible with lower versions of Oracle because analytic function calls that I use in package.
The implementation
Implementation is really easy and takes only 4 steps.
- Create db_size.tbl index organized table
- Create db_size_gtt.tbl global temporary table
- Install db_size_pkg.pks package source
- Install db_size_pkb.plb wrapped package body (10g and onwards)
- If you want you to automate the process of taking snapshots (I recommend that) you may do that through ordinary Oracle job. This example is based on one month window:
DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job => X
,what => 'db_size_pkg.create_snapshot;'
,next_date => to_date('01.09.2008 00:01:00','dd/mm/yyyy hh24:mi:ss')
,interval => 'TRUNC(LAST_DAY(SYSDATE)) + 1+1/1440'
,no_parse => FALSE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
All those objects should be placed in schema with enough privileges to compile package. There is no need to accent that SYS schema may be gracefully avoided as much as possible for purposes like this. Reason is more then known...
The common part
Several things are
conditio sine qua non to do before running any other procedure.
"create_snapshot" procedure
The whole snapshot is done through single procedure -
create_snapshot. Create snapshot iterate through
dba_segments view and collect statistic on all
segment_names. Runing the procedure is very easy:
PROCEDURE create_snapshot (p_period in varchar2 DEFAULT 'TODAY');
However, running snapshots should be taken with sysdba role user or user with enough privileges to run
select queries on many
dba_ views.
Because snapshot is connected with period in all following text I’ll referenced snapshot as term of period.
If no p_period is not set then sysdate is taken as default.
Period in all examples and
varchar2 type of data and always in format
YYYYMMDD regardless NLS settings of database/instance/session you run from. In this way you'll always get the correct sort order and less formatting functions in result outputs.
In mine case I was initially restoring database from 1st July 2007 to 1st November 2008 in two moths periods, taking snapshot one by one. That was very annoying work, but beside checking backups and RMAN catalogs (you have done that of course!?), it was essential to get initial data to start using DSS with full lungs.
handle "create_snapshot" errors
If you run "create_snapshot" with non sysdba role user, some of objects might not be accessible. In previous version the whole snapshot action was roll backed. From version 1.4 in such a cases, analyzed object get a number of records "-1" and size "0" and procedure continue to run. This allows DBA for later select of such an objects and grant appropriate right to user that run snapshot.
Beside that, if you run "create_snapshot" procedure from sqlplus like tool, in a case of any error you get a complete description of error. You can achieve this in a way:
SQL> set serveroutput on size 123456;
SQL> exec db_size_pkg.create_snapshot;
----------------------------------------------------
ORA-01031: insufficient privileges
SELECT count(*) FROM
"SYS"."LINK$"
ORA-06512: at "TOOLS.DB_SIZE_PKG", line 994
----- PL/SQL Call
Stack -----
object line object
handle number
name
0x800000017e95ded0 1000 package body
TOOLS.DB_SIZE_PKG
0x800000017e2abd70 1 anonymous block
----------------------------------------------------
----------------------------------------------------
ORA-01031: insufficient privileges
SELECT count(*) FROM
"SYS"."USER_HISTORY$"
ORA-06512: at "TOOLS.DB_SIZE_PKG", line 994
----- PL/SQL
Call Stack -----
object line object
handle number
name
0x800000017e95ded0 1000 package body
TOOLS.DB_SIZE_PKG
0x800000017e2abd70 1 anonymous block
----------------------------------------------------
Snapshot added 12648 new records in db_size table
*************************************************************
Database Size Stat 1.4
(c) 1992-2010 Damir Vadas, All Rights Reserved.
Non commercial/production use only!
All others use only with author permission.
*************************************************************
PL/SQL procedure successfully completed.
SQL>
If you run snapshot from job, you will get no error,but later you may check result with query that search for "nr_records=-1":
SQL> select * from db_size where nr_records=-1;
PERIOD OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME PART_NAME NR_BYTES NR_BLOCKS NR_EXTENTS NR_RECORDS
-------- ------ ---------------- ---------------- --------------- ----------- -------- --------- ---------- ----------
20100517 SYS LINK$ TABLE SYSTEM NP 65536 8 1 -1
20100517 SYS USER_HISTORY$ TABLE SYSTEM NP 65536 8 1 -1
SQL>
Granting select privilege on such an objects, you may fix this problem.
"nls_num" number format variable
Number formatting is presented through
nls_num variable declared in public part of
db_size_pkg. Initial value is based on Croatian NLS number format…but you can change to USA format easy in a way:
nls_num VARCHAR2(30) := 'NLS_NUMERIC_CHARACTERS = '',.''';
Example:
SQL> begin
2 db_size_pkg.nls_num := 'NLS_NUMERIC_CHARACTERS = '',.''';
3 db_size_pkg.dropped_objects();
4 db_size_pkg.nls_num := 'NLS_NUMERIC_CHARACTERS = ''.,''';
5 db_size_pkg.dropped_objects();
6 end;
7 /
***************************************************************************************************
0 TOTAL dropped 0,000 MB 0 records
***************************************************************************************************
***************************************************************************************************
0 TOTAL dropped 0.000 MB 0 records
***************************************************************************************************
The core procedures
First an main procedure is
get_db_size. The main purpose is to show in variety of outputs which describes size of database through periods.
PROCEDURE get_db_size (p_period IN VARCHAR2 DEFAULT null,
p_list_tblspace IN VARCHAR2 DEFAULT 'N');
Examples: exec db_size_pkg.get_db_size;
same as
exec db_size_pkg.get_db_size(null,'N');
exec db_size_pkg.get_db_size(null,'T');
exec db_size_pkg.get_db_size(null,'H');
exec db_size_pkg.get_db_size('20080501');
same as
exec db_size_pkg.get_db_size('20080501,'T');
same as
exec db_size_pkg.get_db_size('20080501','N');
exec db_size_pkg.get_db_size('20080501,'H');
If no parameter is taken then this is the result:
SQL> exec db_size_pkg.get_db_size;
PERIOD SIZE MB TREND MB RECORDS TREND REC REC/1 MB TR REC/1 MB
==========================================================================================================
20070701 75.241,79 0,00 294.112.834 0 3.908,90 0,00
20070901 103.479,93 28.238,14 346.960.155 52.847.321 3.352,92 1.871,49
20071101 155.292,23 51.812,30 420.892.538 73.932.383 2.710,33 1.426,93
20080101 139.995,03 -15.297,20 438.604.712 17.712.174 3.133,00 -1.157,87
20080301 157.205,12 17.210,09 498.120.194 59.515.482 3.168,60 3.458,17
20080501 168.775,83 11.570,71 562.508.069 64.387.875 3.332,87 5.564,73
20080701 181.967,17 13.191,34 592.960.740 30.452.671 3.258,61 2.308,53
20080802 209.103,60 27.136,43 669.988.712 77.027.972 3.204,10 2.838,54
20080901 216.381,21 7.277,61 683.941.298 13.952.586 3.160,82 1.917,19
20081001 229.736,98 13.355,77 729.811.222 45.869.924 3.176,73 3.434,47
20091201 338.285,95 108.548,98 1.052.294.418 322.483.196 3.110,67 2.970,85
==========================================================================================================
Ass you can see, with such an output all important data on the global level-database. Size grow, grow trend, number of records, records trend and very interesting number of records for 1 MB grow and grow trend. As you can see this procedure gives you very easy way to predict database grow in the future. In our case the main point was contract. So if you connect number of contracts in certain date it is really easy to predict how many MB or records will be if number of contracts grow for 10%.
Another way is to run
get_db_size with some
p_period parameter value. According parameter is taken some additional output data are shown:
SQL> exec db_size_pkg.get_db_size('20080501');
PERIOD
===========================
===========================
20080501
===========================
Tablespace % SIZE MB RECORDS
-----------------------------------------------------------------------
APPS_TS_ARCHIVE 0,463 781 364.090
APPS_TS_CONVERSION 0,925 1.561 3.806.325
APPS_TS_INTERFACE 1,175 1.983 926.861
APPS_TS_MEDIA 1,813 3.059 62.158
APPS_TS_MVIEWS 8,003 13.508 66.472.066
APPS_TS_NOLOGGING 0,610 1.029 1.268.554
APPS_TS_QUEUES 0,892 1.506 446.396
APPS_TS_SEED 1,335 2.253 6.690.545
APPS_TS_SUMMARY 0,645 1.089 2.070.682
APPS_TS_TX_DATA 38,543 65.052 382.452.012
APPS_TS_TX_IDX 30,288 51.118 4.974
CTXD 0,082 139 404.347
DISCO 0,025 42 15.827
DWH 8,947 15.100 43.085.706
FOGLIGHT_TS 0,001 3 8.159
ODM 0,006 10 31.774
PORTAL 0,000 0 1
SYSAUX 0,984 1.661 6.025.698
SYSTEM 5,118 8.638 46.732.984
TOOLS 0,136 230 1.626.886
XX_COUNTER 0,009 15 12.024
-----------------------------------------------------------------------
100,000 168.776 562.508.069
-----------------------------------------------------------------------
Such an output gives very nice view on tablespace statistic in chosen period.
If you put "H" parameter with period in front then you get the whole
history of all tablespaces in that period:
SQL> exec db_size_pkg.get_db_size('20080501','H');
PERIOD
===========================
===========================
20080501
===========================
APPS_TS_ARCHIVE
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20070701 9,218 793 793 0,00 0,00 1.090.993 1.090.993 0,00 0,00
20070901 8,031 691 -102 -12,88 -12,88 524.483 -566.510 -51,93 -51,93
20071101 8,335 717 26 3,78 -9,58 693.288 168.805 32,19 -36,45
20080101 8,785 756 39 5,40 -4,70 865.615 172.327 24,86 -20,66
20080301 9,071 781 25 3,26 -1,59 193.206 -672.409 -77,68 -82,29
20080501 9,073 781 0 0,02 -1,58 364.090 170.884 88,45 -66,63
20080701 9,074 781 0 0,02 -1,56 150.694 -213.396 -58,61 -86,19
20080802 9,074 781 0 0,00 -1,56 457.235 306.541 203,42 -58,09
20080901 9,074 781 0 0,00 -1,56 565.322 108.087 23,64 -48,18
20081001 9,076 781 0 0,02 -1,54 650.492 85.170 15,07 -40,38
20091201 11,189 963 182 23,29 21,39 1.960.769 1.310.277 201,43 79,72
====================================================================================================================================
...
... intentionally shorten output ...
...
XX_COUNTER
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20070701 7,619 13 13 0,00 0,00 1.713 1.713 0,00 0,00
20070901 7,821 13 0 2,65 2,65 3.703 1.990 116,17 116,17
20071101 8,032 14 0 2,70 5,42 5.759 2.056 55,52 236,19
20080101 8,358 14 1 4,06 9,70 8.253 2.494 43,31 381,79
20080301 8,895 15 1 6,42 16,75 9.833 1.580 19,14 474,02
20080501 9,010 15 0 1,29 18,25 12.024 2.191 22,28 601,93
20080701 9,478 16 1 5,20 24,40 14.133 2.109 17,54 725,04
20080802 9,561 16 0 0,87 25,48 15.088 955 6,76 780,79
20080901 9,726 17 0 1,73 27,65 15.837 749 4,96 824,52
20081001 9,900 17 0 1,79 29,94 16.922 1.085 6,85 887,86
20091201 11,599 20 3 17,15 52,22 29.240 12.318 72,79 1.606,95
====================================================================================================================================
Here output was shorten because full output will have no sense.
With all other combination you can wide your analyze and the whole examples are in this txt file
"periods" procedure
To see the list of periods (list of taken snapshots) use
periods procedure.
PROCEDURE periods;
SQL> exec db_size_pkg.periods;
Periods
--------
20070701
20070901
20071101
20080101
20080301
20080501
20080701
20080802
20080901
20081001
20091201
========
"get_last_period" function
Another procedure that deals with periods is
get_last_period. This which returns last period in
db_size table (last snapshot). If you use p_period parameter then function returns penultimate period.
FUNCTION get_last_period (p_period IN VARCHAR DEFAULT NULL
) RETURN VARCHAR2;
Example:
20091201 db_size_pkg.get_last_period;
20080301 db_size_pkg.get_last_period('20080501');
null as parameter for period
In all procedures where period is parameter, if you leave it as
null then last two periods are taken. That was very convenient to me because mostly queries was run against last two snapshots/periods.
Another limitation is that period1 must be greater then period2...but this is checked in each procedure internally and result with appropriate error message in any wrong usage.
New/dropped objects procedures
Monitoring of new and dropped objects are performed through two procedures with the similar names. Third one
both_objects is union of first two. Showing it's output will explain the output result of all of them:
PROCEDURE new_objects (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
p_segment_type IN VARCHAR2 DEFAULT null) ;
PROCEDURE dropped_objects (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
p_segment_type IN VARCHAR2 DEFAULT null) ;
PROCEDURE both_objects (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
p_segment_type IN VARCHAR2 DEFAULT null) ;
Examples:
exec db_size_pkg.both_objects;
exec db_size_pkg.both_objects('20080501','20080301');
exec db_size_pkg.both_objects(null,null,'TABLE PARTITON');
exec db_size_pkg.both_objects('20091201','20070701','LOBSEGMENT');
SQL> exec db_size_pkg.both_objects('20080501','20080301');
List of dropped INDEX in period: 20080501-20080301
------------------------------------------------------------------------------
DAMIRV.AUDIT_DDL_LOG_PK(INDEX)
Period SIZE MB TREND MB TREND % CUMUL %
-------- --------------------------------------------------
20080101 1,42 1,42 0,00 0,00
20080301 0,10 -1,32 -92,96 -92,96
============================================================
XXHY.XXHY_DEBUG_LOG_N1(INDEX)
Period SIZE MB TREND MB TREND % CUMUL %
-------- --------------------------------------------------
20070701 1.548,38 1.548,38 0,00 0,00
20070901 1.985,38 437,00 28,22 28,22
20071101 102,58 -1.882,80 -94,83 -93,38
20080101 1.246,98 1.144,40 1.115,62 -19,47
20080301 1.255,21 8,23 0,66 -18,93
20091201 2.791,75 1.536,54 122,41 80,30
============================================================
------------------------------------------------------------------------------
2 INDEX dropped -125,531 MB
******************************************************************************
List of dropped TABLE in period: 20080501-20080301
------------------------------------------------------------------------------
DAMIRV.AUDIT_LOGON_LOG(TABLE)
Period SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------- -----------------------------------------------------------
20080101 100,04 100,04 0,00 0,00 2 2 0,00 0,00
20080301 100,04 0,00 0,00 0,00 43 41 2.050,00 2.050,00
===========================================================================================================================
DAMIRV.AUDIT_LOGON_LOG_ARCH(TABLE)
Period SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------- -----------------------------------------------------------
20080101 100,04 100,04 0,00 0,00 0 0 0,00 0,00
20080301 100,04 0,00 0,00 0,00 0 0 0,00 0,00
===========================================================================================================================
---------------------------------------------------------------------------------------------------
2 TABLE dropped -20,008 MB -43 records
***************************************************************************************************
***************************************************************************************************
4 TOTAL dropped -145,539 MB -43 records
***************************************************************************************************
List of new INDEX in period: 20080501-20080301
------------------------------------------------------------------------------
APPS.XXHY_HLK_HANFA_CONTRACTS_I1 0,188 MB
APPS.XXHY_HLK_HANFA_OS_I1 0,038 MB
APPS.XXHY_HLK_HANFA_RM_I1 0,075 MB
APPS.XXHY_HLK_PARTNER_MAPPING_I1 0,013 MB
IGNITE_C.SYS_C00304984 0,010 MB
XXHY.XXHY_AR_RECEIVABLE_APPL_U1 36,088 MB
XXHY.XXHY_CASH_RECEIPTS_N3 0,025 MB
XXHY.XXHY_MTL_CATEGORIES_B_N2 0,138 MB
XXHY.XXHY_REC_UNPAID_INVOICE_N1 8,625 MB
XXHY.XXHY_REC_UNPAID_INVOICE_N2 8,888 MB
XXHY.XXHY_REC_UNPAID_INVOICE_N3 6,788 MB
------------------------------------------------------------------------------
11 INDEX new 60,873 MB
******************************************************************************
List of new LOBINDEX in period: 20080501-20080301
------------------------------------------------------------------------------
APPS.SYS_IL0000815672C00036$$ 0,013 MB
------------------------------------------------------------------------------
1 LOBINDEX new 0,013 MB
******************************************************************************
List of new LOBSEGMENT in period: 20080501-20080301
------------------------------------------------------------------------------
APPS.SYS_LOB0000815672C00036$$ 0,013 MB
------------------------------------------------------------------------------
1 LOBSEGMENT new 0,013 MB
******************************************************************************
List of new TABLE in period: 20080501-20080301
------------------------------------------------------------------------------
APPS.RA_CUSTOMER_TRX_ALL_080304 0,013 MB 2 records
APPS.RA_CUSTOMER_TRX_ALL_080312 0,013 MB 1 records
APPS.RA_CUSTOMER_TRX_ALL_080402 0,013 MB 1 records
APPS.SR6696689994_040408 0,013 MB 78 records
APPS.SR6696689994_160408 0,013 MB 66 records
APPS.SR6835236_040422 0,013 MB 72 records
APPS.XXHY_FX_CALCULATION_ALL210208 0,013 MB 6 records
APPS.XXHY_FX_CALCULATION_ALL_080304 0,013 MB 4 records
APPS.XXHY_FX_CALCULATION_ALL_080312 0,013 MB 1 records
APPS.XXHY_FX_CALCULATION_ALL_080402 0,013 MB 3 records
APPS.XXHY_FX_CALCULATION_ALL_170308 0,013 MB 2 records
APPS.XXHY_FX_CALCULATION_ALL_180208 0,013 MB 2 records
APPS.XXHY_HLK_HANFA_CONTRACTS 0,938 MB 84.663 records
APPS.XXHY_HLK_HANFA_RM 0,125 MB 27.234 records
IGNITE_C.CONREPOS 0,010 MB 1 records
XXHY.XXHY_HLK_PARTNER_MAPPING 0,013 MB 245 records
XXHY.XXHY_REC_UNPAID_INVOICE_ALL 64,813 MB 2.707.789 records
---------------------------------------------------------------------------------------------------
17 TABLE new 66,048 MB 2.820.170 records
***************************************************************************************************
***************************************************************************************************
30 TOTAL new 126,945 MB 2.820.170 records
***************************************************************************************************
***************************************************************************************************
Total new/dropped in period 20080501-20080301 -18,594 MB 2.820.127 records
***************************************************************************************************!
If you compare this output with grow size from
get_db_size procedure's output, you’ll see that these numbers will allmost never be the same because many objects that were not dropped or are new, are growing or lessen, so this is where difference comes from. These procedures were used for quick analyze if some indexes are missing or some tables dropped.
All three wider examples can be downloaded from:
- new_objects example
- dropped_objects example
- both_objects example
"get_segment_type_size" procedure
What is
get_db_size on database level, this procedure is for segments. When I say segments I mean on
segment_type values from
dba_segments view.
Procedure has only one parameter-segment_type. Here is the output for
PROCEDURE get_segment_type_size (p_segment_type IN VARCHAR2);
SQL> exec db_size_pkg.get_segment_type_size('TABLE');
Segemnt type: TABLE
PERIOD DB % SIZE MB TREND MB RECORDS TREND REC REC/MB TR REC/MB
==============================================================================================================
20070701 4,04 45.079,32 0,00 282.668.350 0 6.270,47 0,00
20070901 5,91 65.994,95 20.915,63 346.427.320 63.758.970 5.249,30 3.048,39
20071101 9,64 107.608,90 41.613,95 419.978.531 73.551.211 3.902,82 1.767,47
20080101 7,69 85.784,48 -21.824,42 437.905.606 17.927.075 5.104,72 -821,42
20080301 8,68 96.828,87 11.044,39 497.165.036 59.259.430 5.134,47 5.365,57
20080501 9,16 102.203,18 5.374,31 561.157.134 63.992.098 5.490,60 11.907,03
20080701 8,29 92.450,63 -9.752,56 548.065.355 -13.091.779 5.928,20 1.342,39
20080802 9,63 107.404,54 14.953,91 615.772.383 67.707.028 5.733,21 4.527,71
20080901 9,98 111.393,84 3.989,31 629.305.426 13.533.043 5.649,37 3.392,33
20081001 10,54 117.614,01 6.220,16 671.326.805 42.021.379 5.707,88 6.755,67
20091201 16,44 183.426,42 65.812,41 959.912.847 288.586.042 5.233,23 4.384,98
==============================================================================================================
What is also practical to this kind of analyze is that you can easy calculate database grow based on number of records of such an segment type (REC/MB and TR REC/MB columns).
List of possible segments can be easily retrieved as:
select distinct segment_type from db_size where period=&p_period
Example can be downloaded from txt file
The tablespace part
Three procedures deals with tablespace statistic. As mentioned before, global look is always the best point to start investigation. So after
get_db_size this is second set of procedures that DBA should analyze in searching for problems.
"grow_hist_one_tblspc" procedure
Main procedure is
grow_hist_one_tablespace which is the core procedure for other two.
PROCEDURE grow_hist_one_tblspc (p_tablespace_name IN VARCHAR2);
Example:
SQL> exec db_size_pkg.grow_hist_one_tblspc('APPS_TS_INTERFACE');
APPS_TS_INTERFACE
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20070701 0,752 902 902 0,00 0,00 636.072 636.072 0,00 0,00
20070901 8,904 10.674 9.773 1.084,05 1.084,05 17.023.234 16.387.162 2.576,31 2.576,31
20071101 40,240 48.242 37.568 351,95 5.251,29 83.099.807 66.076.573 388,16 12.964,53
20080101 1,275 1.528 -46.714 -96,83 69,52 839.979 -82.259.828 -98,99 32,06
20080301 1,334 1.599 71 4,62 77,36 844.414 4.435 0,53 32,75
20080501 1,654 1.983 384 23,99 119,91 926.861 82.447 9,76 45,72
20080701 0,992 1.190 -793 -40,00 31,95 964.430 37.569 4,05 51,62
20080802 3,963 4.751 3.562 299,43 427,04 5.898.281 4.933.851 511,58 827,30
20080901 4,106 4.923 171 3,60 446,03 6.288.246 389.965 6,61 888,61
20081001 4,326 5.186 263 5,35 475,24 6.298.268 10.022 0,16 890,18
20091201 32,455 38.908 33.722 650,28 4.215,90 35.296.815 28.998.547 460,42 5.449,19
====================================================================================================================================
Idea of this output is to isolate output to one particular tablespace and see grow history.
Example can be downloaded from txt file
"n" procedures
For all produres that have "n" in its name, "n" represent number of output rows/elemnts to show. In all cases can be avoided and use default value for particular procedure.
Only in these two examples n=100000 means "all tablesapces" to facilitate user command interface (this was convinient to me).
It is nice to see that column beside periods shows tablespace percentage in database size.
TEMP and UNDO tablespaces are not excluded in DSS tool in any output..
"top_n_size_grow_hist_tblspc" procedure
This procedure shows tablespaces ordered by its
grow (TREND MB colum).
PROCEDURE top_n_size_grow_hist_tblspc (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 100000);
Examples:
exec db_size_pkg.top_n_size_grow_hist_tblspc ;
exec db_size_pkg.top_n_size_grow_hist_tblspc ('20080501','20080301');
exec db_size_pkg.top_n_size_grow_hist_tblspc ('20080501','20080301',5);
SQL> exec db_size_pkg.top_n_size_grow_hist_tblspc(null,null,3);
Tablespaces by SIZE grow. Monitored periods 20091201-20081001
APPS_TS_TX_DATA
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20070701 3,451 24.952 24.952 0,00 0 157.766.811 157.766.811 0,00 0,00
20070901 5,167 37.357 12.404 49,71 50 215.247.823 57.481.012 36,43 36,43
20071101 5,454 39.430 2.074 5,55 58 222.711.817 7.463.994 3,47 41,17
20080101 6,814 49.264 9.834 24,94 97 288.586.226 65.874.409 29,58 82,92
20080301 7,846 56.726 7.462 15,15 127 333.761.926 45.175.700 15,65 111,55
20080501 8,997 65.052 8.326 14,68 161 382.452.012 48.690.086 14,59 142,42
20080701 9,360 67.673 2.621 4,03 171 394.916.616 12.464.604 3,26 150,32
20080802 10,696 77.335 9.662 14,28 210 443.468.997 48.552.381 12,29 181,09
20080901 11,174 80.792 3.456 4,47 224 467.668.935 24.199.938 5,46 196,43
20081001 11,978 86.601 5.809 7,19 247 497.345.922 29.676.987 6,35 215,24
20091201 19,064 137.835 51.234 59,16 452 823.561.368 326.215.446 65,59 422,01
====================================================================================================================================
APPS_TS_INTERFACE
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20070701 0,752 902 902 0,00 0 636.072 636.072 0,00 0,00
20070901 8,904 10.674 9.773 1.084,05 1.084 17.023.234 16.387.162 2.576,31 2.576,31
20071101 40,240 48.242 37.568 351,95 5.251 83.099.807 66.076.573 388,16 12.964,53
20080101 1,275 1.528 -46.714 -96,83 70 839.979 -82.259.828 -98,99 32,06
20080301 1,334 1.599 71 4,62 77 844.414 4.435 0,53 32,75
20080501 1,654 1.983 384 23,99 120 926.861 82.447 9,76 45,72
20080701 0,992 1.190 -793 -40,00 32 964.430 37.569 4,05 51,62
20080802 3,963 4.751 3.562 299,43 427 5.898.281 4.933.851 511,58 827,30
20080901 4,106 4.923 171 3,60 446 6.288.246 389.965 6,61 888,61
20081001 4,326 5.186 263 5,35 475 6.298.268 10.022 0,16 890,18
20091201 32,455 38.908 33.722 650,28 4.216 35.296.815 28.998.547 460,42 5.449,19
====================================================================================================================================
APPS_TS_TX_IDX
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20070701 3,466 17.913 17.913 0,00 0 4.972 4.972 0,00 0,00
20070901 4,686 24.219 6.306 35,20 35 4.972 0 0,00 0,00
20071101 5,581 28.846 4.627 19,10 61 4.974 2 0,04 0,04
20080101 7,531 38.927 10.082 34,95 117 4.974 0 0,00 0,04
20080301 8,669 44.809 5.882 15,11 150 4.974 0 0,00 0,04
20080501 9,890 51.118 6.309 14,08 185 4.974 0 0,00 0,04
20080701 9,989 51.631 513 1,00 188 4.974 0 0,00 0,04
20080802 11,187 57.823 6.192 11,99 223 4.974 0 0,00 0,04
20080901 11,679 60.364 2.542 4,40 237 4.974 0 0,00 0,04
20081001 12,535 64.791 4.426 7,33 262 4.974 0 0,00 0,04
20091201 14,788 76.438 11.647 17,98 327 4.974 0 0,00 0,04
====================================================================================================================================
Example can be downloaded from txt file
"top_n_size_perc_hist_tblspc" procedure
This procedure shows tablespaces ordered by its
percentage grow (TREND % colum). This one is interesting becase some small tablespaces may explode and you’ll never easy know that this happening without this kind of output. Percentage grow order may very differ from size grow order!
PROCEDURE top_n_size_perc_hist_tblspc (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 100000);
Examples:
exec db_size_pkg.top_n_size_perc_hist_tblspc;
exec db_size_pkg.top_n_size_perc_hist_tblspc('20080501','20080301');
exec db_size_pkg.top_n_size_perc_hist_tblspc('20080501','20080301',5);
SQL> exec db_size_pkg.top_n_size_perc_hist_tblspc (null,null,3);
Tablespaces by PERCENTAGE grow. Monitored periods 20091201-20081001
DWH09
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20080701 1,942 200 200 0,00 0 266.512 266.512 0,00 0,00
20080802 1,942 200 0 0,00 0 18.065.692 17.799.180 6.678,57 6.678,57
20080901 1,942 200 0 0,00 0 313.393 -17.752.299 -98,27 17,59
20081001 1,942 200 0 0,00 0 316.021 2.628 0,84 18,58
20091201 92,233 9.500 9.300 4.650,00 4.650 24.787.383 24.471.362 7.743,59 9.200,66
====================================================================================================================================
CTXD
Period DATABS % SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------------------- --------------------------------------------------------
20070701 1,694 16 16 0,00 0 114.176 114.176 0,00 0,00
20070901 5,281 50 34 211,77 212 1.136.081 1.021.905 895,03 895,03
20071101 7,248 69 19 37,25 328 1.922.159 786.078 69,19 1.583,51
20080101 10,199 97 28 40,72 502 2.945.038 1.022.879 53,22 2.479,38
20080301 14,625 139 42 43,40 763 3.990.317 1.045.279 35,49 3.394,88
20080501 14,625 139 0 0,00 763 404.347 -3.585.970 -89,87 254,14
20080701 1,468 14 -125 -89,96 -13 26.355 -377.992 -93,48 -76,92
20080802 1,993 19 5 35,77 18 260.894 234.539 889,92 128,50
20080901 3,044 29 10 52,70 80 624.025 363.131 139,19 446,55
20081001 4,094 39 10 34,51 142 973.403 349.378 55,99 752,55
20091201 35,730 340 301 772,73 2.010 5.386.868 4.413.465 453,41 4.618,04
====================================================================================================================================
Example can be downloaded from txt file
The "top_n" segment procedures
Four procedures of these type are third level of global scope when analyze database grow. They show trend in plain table output with minimal data. Idea is to clear up the main thing: grow in size, grow in percent, grow in records and records percentage grow on segment any level.
"top_n_size_grow" procedure
Order of segments are based on "grow [MB]" column.
PROCEDURE top_n_size_grow (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 100);
Examples:
exec db_size_pkg.top_n_size_grow;
exec db_size_pkg.top_n_size_grow ('20080501','20080301');
exec db_size_pkg.top_n_size_grow ('20080501','20080301',10);
SQL> exec db_size_pkg.top_n_size_grow ('20080501','20080301',10);
Top 10 objects by SIZE grow. Monitored periods 20080501-20080301
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE [MB] grow [MB] %
----------------------------------------------------------------------------------------------------------------------------------
GL GL_JE_LINES TABLE 10.009,75 1.331,50 15,34
XXHY XXHY_GL_CC_DRILL_DOWN_V_TT TABLE 14.300,00 1.300,00 10,00
APPS XXHY_CC_PAYMENT_PLAN_DISCO_MV TABLE 13.507,56 1.285,56 10,52
XXHY XXHY_CC_PAY_PLAN_AMOUNTS_ALL TABLE 6.310,50 903,88 16,72
AR RA_CUSTOMER_TRX_LINES_ALL TABLE 3.204,13 602,63 23,16
AR RA_CUST_TRX_LINE_GL_DIST_ALL TABLE 2.854,25 599,63 26,60
GL GL_IMPORT_REFERENCES TABLE 4.206,38 595,25 16,48
XXHY XXHY_CC_PAY_PLAN_AMOUNTS_U1 INDEX 2.766,38 360,25 14,97
APPLSYS DR$FND_LOBS_CTX$I TABLE 473,25 323,50 216,03
AR RA_CUSTOMER_TRX_ALL TABLE 1.437,50 298,38 26,19
Example can be downloaded from txt file
"top_n_size_perc" procedure
Order of segments are based on "%" column. Important part is that only 10MB < segment sizes are involved.
Partition are placed in db_size as several records but in output all partition are treated as one segment!
PROCEDURE top_n_size_perc (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 100);
Examples:
exec db_size_pkg.top_n_size_perc;
exec db_size_pkg.top_n_size_perc ('20080501','20080301');
exec db_size_pkg.top_n_size_perc ('20080501','20080301',10);
SQL> exec db_size_pkg.top_n_size_perc ('20080501','20080301',10);
Top 10 objects by PERCENTAGE grow. Monitored periods 20080501-20080301
Only objects WITH size > 10MB are included!
OWNER SEGMENT_NAME SEGMENT_TYPE SIZE [MB] grow [MB] %
----------------------------------------------------------------------------------------------------------------------------------
APPLSYS DR$FND_LOBS_CTX$X INDEX 175,38 131,88 303,16
APPLSYS DR$FND_LOBS_CTX$I TABLE 473,25 323,50 216,03
GL GL_INTERFACE_N2 INDEX 122,75 75,13 157,74
GL GL_INTERFACE_N1 INDEX 60,50 36,75 154,74
GL GL_INTERFACE_N4 INDEX 64,75 37,13 134,39
GL GL_INTERFACE TABLE 395,63 224,00 130,52
JTF SYS_LOB0000200028C00018$$ LOBSEGMENT 22,63 10,63 88,54
APPLSYS WF_NOTIFICATION_OUT_N1 INDEX 19,63 8,88 82,56
APPLSYS WF_NOTIFICATION_OUT TABLE 407,63 181,38 80,17
XXHY XXHY_RM_CALCULATIONS_A_N3 INDEX 56,13 24,50 77,47
It is really important to understand that some small segments (lower then 10 MB in EBS is peace of cake so they are not counted in output!) may explode very silently and this is really nice view to catch them! In this example it is obviously that grow of "DR$" indexes is probably connected with physical grow of parent table or in case when large records are dropped without proper rebuilding of indexes.
Example can be downloaded from txt file
"top_n_rec_grow" procedure
Order of segments are based on "REC_GROW" column. "REC_GROW" column tells how much records has been grown in monitored period.
PROCEDURE top_n_rec_grow (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 100);
Examples:
exec db_size_pkg.top_n_rec_grow;
exec db_size_pkg.top_n_rec_grow ('20080501','20080301');
exec db_size_pkg.top_n_rec_grow ('20080501','20080301',10);
SQL> exec db_size_pkg.top_n_rec_grow ('20080501','20080301',10);
Top 10 objects by NUMBER of records row. Monitored periods 20080501-20080301
OWNER SEGMENT_NAME SEGMENT_TYPE NR_RECORDS REC_GROW %
-------------------------------------------------------------------------------------------------------------------------------------
XXHY XXHY_CC_PAY_PLAN_AMOUNTS_ALL TABLE 78.422.940 10.990.267 16,30
APPS XXHY_CC_PAYMENT_PLAN_DISCO_MV TABLE 66.472.066 6.750.522 11,30
XXHY XXHY_GL_CC_DRILL_DOWN_V_TT TABLE 38.711.757 4.870.904 14,39
GL GL_JE_LINES TABLE 38.756.116 4.845.708 14,29
AR RA_CUST_TRX_LINE_GL_DIST_ALL TABLE 19.620.633 4.184.139 27,11
GL GL_IMPORT_REFERENCES TABLE 26.560.744 3.775.827 16,57
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 3.601.021 3.587.396 26.329,51
APPLSYS DR$FND_LOBS_CTX$I TABLE 3.819.293 2.758.699 260,11
AR RA_CUSTOMER_TRX_LINES_ALL TABLE 10.691.695 2.131.972 24,91
AR AR_DISTRIBUTIONS_ALL TABLE 20.299.280 1.287.305 6,77
Example can be downloaded from txt file
"top_n_rec_perc" procedure
Order of segments are based on "%" column which represent percentage grow based on records in monitoring periods.
Only segments with nr_records > 10.000 records are included in this statistic!
PROCEDURE top_n_rec_perc (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 100);
Examples:
exec db_size_pkg.top_n_rec_perc;
exec db_size_pkg.top_n_rec_perc ('20080501','20080301');
exec db_size_pkg.top_n_rec_perc ('20080501','20080301',10);
SQL> exec db_size_pkg.top_n_rec_perc ('20080501','20080301',10);
Top 10 objects by PERCENTAGE records grow. Monitored periods 20080501-20080301
Only tables WITH nr_records > 10.000 records are included!
OWNER SEGMENT_NAME SEGMENT_TYPE NR_RECORDS REC_GROW %
-------------------------------------------------------------------------------------------------------------------------------------
SYS WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 3.601.021 3.587.396 26.329,51
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 893.265 849.158 1.925,22
SYS WRH$_ACTIVE_SESSION_HISTORY TABLE PARTITION 235.092 212.023 919,08
SYS WRH$_LATCH_MISSES_SUMMARY TABLE PARTITION 76.343 59.534 354,18
APPLSYS DR$FND_LOBS_CTX$I TABLE 3.819.293 2.758.699 260,11
APPLSYS FND_STATS_HIST TABLE 266.821 181.515 212,78
XXHY XXHY_RP_UNPAID_INVOICE_LMT_ALL TABLE 68.333 45.169 195,00
SYS SQLA$SQLTEXT TABLE 58.105 35.498 157,02
CN CN_SRP_PER_QUOTA_RC_ALL TABLE 20.448 9.303 83,47
CN CN_SRP_PERIOD_QUOTAS_EXT_ALL TABLE 27.328 12.428 83,41
Only segments with nr_records > 10.000 records are included in this statistic!
Example can be downloaded from txt file
The segment hist procedures
These are the lowest level of statistic with most detail of each segment-the whole history of it’s presence in database. They all are based on one core procedure (like in tblspc part)-
grow_hist_one_segment.
"grow_hist_one_segment" procedure
Similar as in tablespaces, this is core procedure for all "hist" based procedures that deal with segments. It shows all needed data to monitor trend and cumulative numbers in total and percentage for size and records as well. Idea is to have most detailed viws on one segment level for all the periods.
PROCEDURE grow_hist_one_segment (p_owner IN VARCHAR2,
p_segment_name IN VARCHAR2,
p_segment_type IN VARCHAR2 DEFAULT 'TABLE');
Examples:
SQL> exec db_size_pkg.grow_hist_one_segment ('APPLSYS','WF_LOCAL_ROLES','TABLE PARTITION');
APPLSYS.WF_LOCAL_ROLES(TABLE PARTITION)
Period SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------- -----------------------------------------------------------
20070701 19,75 19,75 0,00 0,00 1.192.422 1.192.422 0,00 0,00
20070901 20,63 0,88 4,46 4,46 90.451 -1.101.971 -92,41 -92,41
20071101 21,63 1,00 4,85 9,52 94.483 4.032 4,46 -92,08
20080101 22,50 0,87 4,02 13,92 98.711 4.228 4,47 -91,72
20080301 23,38 0,88 3,91 18,38 102.880 4.169 4,22 -91,37
20080501 24,38 1,00 4,28 23,44 107.357 4.477 4,35 -91,00
20080701 24,50 0,12 0,49 24,05 111.376 4.019 3,74 -90,66
20080802 25,75 1,25 5,10 30,38 113.368 1.992 1,79 -90,49
20080901 26,00 0,25 0,97 31,65 114.858 1.490 1,31 -90,37
20081001 25,75 -0,25 -0,96 30,38 116.496 1.638 1,43 -90,23
20091201 28,75 3,00 11,65 45,57 127.384 10.888 9,35 -89,32
===========================================================================================================================
Example can be downloaded from txt file
Like in previous section there are four procedures that do the rest of jobs.
"top_n_size_grow_hist" procedure
Similar as
top_n size_grow but here every segment is present instead of one row with full history.
PROCEDURE top_n_size_grow_hist (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 10);
Examples:
exec db_size_pkg.top_n_size_grow_hist;
exec db_size_pkg.top_n_size_grow_hist ('20080501','20080301');
exec db_size_pkg.top_n_size_grow_hist ('20080501','20080301',3);
SQL> exec db_size_pkg.top_n_size_grow_hist ('20080501','20080301',3);
Top 3 objects by SIZE grow. Monitored periods 20080501-20080301
GL.GL_JE_LINES(TABLE)
Period SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------- -----------------------------------------------------------
20070701 1.180,63 1.180,63 0,00 0,00 6.763.180 6.763.180 0,00 0,00
20070901 2.510,63 1.330,00 112,65 112,65 11.314.140 4.550.960 67,29 67,29
20071101 5.174,38 2.663,75 106,10 338,27 21.853.222 10.539.082 93,15 223,12
20080101 6.995,25 1.820,87 35,19 492,50 28.111.423 6.258.201 28,64 315,65
20080301 8.678,25 1.683,00 24,06 635,05 33.910.408 5.798.985 20,63 401,40
20080501 10.009,75 1.331,50 15,34 747,83 38.756.116 4.845.708 14,29 473,05
20080701 10.841,00 831,25 8,30 818,24 41.629.108 2.872.992 7,41 515,53
20080802 13.160,13 2.319,13 21,39 1.014,67 49.606.017 7.976.909 19,16 633,47
20080901 13.677,50 517,37 3,93 1.058,49 51.380.285 1.774.268 3,58 659,71
20081001 14.812,63 1.135,13 8,30 1.154,64 55.277.203 3.896.918 7,58 717,33
20091201 24.228,25 9.415,62 63,56 1.952,15 88.233.655 32.956.452 59,62 1.204,62
===========================================================================================================================
XXHY.XXHY_GL_CC_DRILL_DOWN_V_TT(TABLE)
Period SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------- -----------------------------------------------------------
20071101 8.200,00 8.200,00 0,00 0,00 0 0 0,00 0,00
20080101 11.300,00 3.100,00 37,80 37,80 28.096.387 28.096.387 0,00 0,00
20080301 13.000,00 1.700,00 15,04 58,54 33.840.853 5.744.466 20,45 20,45
20080501 14.300,00 1.300,00 10,00 74,39 38.711.757 4.870.904 14,39 37,78
===========================================================================================================================
APPS.XXHY_CC_PAYMENT_PLAN_DISCO_MV(TABLE)
Period SIZE MB TREND MB TREND % CUMUL % RECORDS TREND REC TREND % CUMUL %
-------- -------------------------------------------------- -----------------------------------------------------------
20070701 7.110,00 7.110,00 0,00 0,00 33.395.612 33.395.612 0,00 0,00
20070901 8.326,00 1.216,00 17,10 17,10 39.684.371 6.288.759 18,83 18,83
20071101 9.542,00 1.216,00 14,60 34,21 45.989.776 6.305.405 15,89 37,71
20080101 11.014,00 1.472,00 15,43 54,91 53.388.260 7.398.484 16,09 59,87
20080301 12.222,00 1.208,00 10,97 71,90 59.721.544 6.333.284 11,86 78,83
20080501 13.507,56 1.285,56 10,52 89,98 66.472.066 6.750.522 11,30 99,04
20080701 14.740,00 1.232,44 9,12 107,31 72.912.475 6.440.409 9,69 118,33
20080802 15.316,00 576,00 3,91 115,41 76.097.530 3.185.055 4,37 127,87
20080901 15.700,00 384,00 2,51 120,82 78.322.225 2.224.695 2,92 134,53
20081001 16.212,00 512,00 3,26 128,02 80.885.613 2.563.388 3,27 142,20
20091201 0,06 -16.211,94 -100,00 -100,00 0 -80.885.613 -100,00 0,00
===========================================================================================================================
Here is important to notice that numbers that counts are in monitored periods (
colored in red) so order of segments is based on their values (regardless they have now or before)!
All others numbers shows only it’s history in database until recent snapshot. All mentioned here will be share the same logic in other three "hist" procedures.
Example can be downloaded from txt file
"top_n_size_perc_hist" procedure
Similar as
top_n size_perc but every segment is present instead of one row with full table history.
PROCEDURE top_n_size_perc_hist (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 10);
Examples:
exec db_size_pkg.top_n_size_perc_hist;
exec db_size_pkg.top_n_size_perc_hist ('20080501','20080301');
exec db_size_pkg.top_n_size_perc_hist ('20080501','20080301',3);
Because showing real example will take to much space, full example can be downloaded from txt file
"top_n_rec_grow_hist" procedure
Similar as
top_n_rec_grow but every segment is present instead of one row with full table history.
PROCEDURE top_n_rec_grow_hist (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 10);
Examples:
exec db_size_pkg.top_n_rec_grow_hist;
exec db_size_pkg.top_n_rec_grow_hist ('20080501','20080301');
exec db_size_pkg.top_n_rec_grow_hist ('20080501','20080301',3);
Because showing real example will take to much space full example can be downloaded from txt file
"top_n_rec_perc_hist" procedure
Similar as
top_n_rec_perc but every segment is present instead of one row with full table history.
PROCEDURE top_n_rec_perc_hist (p_period1 IN VARCHAR2 DEFAULT null,
p_period2 IN VARCHAR2 DEFAULT null,
n IN pls_integer DEFAULT 10);
Examples:
exec db_size_pkg.top_n_rec_perc_hist;
exec db_size_pkg.top_n_rec_perc_hist ('20080501','20080301');
exec db_size_pkg.top_n_rec_perc_hist ('20080501','20080301',3);
Again, because showing real example will also take to much space, full example can be downloaded from txt file
The "top_n_tables" ever/now procedures
Last four procedures that deals with size or number of records are based on "biggest ever" or "biggest now". "Biggest ever" means biggest in all periods and "biggest now" mean biggest in last taken snapshot. All is done for size and records with history type of output.
All these procedures deal
only with table segments because (beside partitions which had by me less important meanings) these segments would really be most interesting to keep highly under control.
Idea is to monitor kings and aces in database …
"top_n_tables_by_size_ever" procedure
PROCEDURE top_n_tables_by_size_ever (n in pls_integer default 10);
Because showing real example can be downloaded from txt file
"top_n_tables_by_records_ever" procedure
PROCEDURE top_n_tables_by_rec_ever (n in pls_integer default 10);
Example will take to much space example can be downloaded from txt file
"top_n_tables_by_size_now" procedure
PROCEDURE top_n_tables_by_size_now (n in pls_integer default 10);
Example will also take to much space so full example can be downloaded from txt file
"top_n_tables_by_records_now" procedure
PROCEDURE top_n_tables_by_rec_now (n in pls_integer default 10);
Example will take to much space example can be downloaded from txt file
The end
I hope that you had enough patience to come to this point and see all benefits of DSS tool. Even thought this tool is free for use, if you find it interesting please let me know…especially if you have made some improvements or found some bugs.
All implementation files (mentioned before) are packed together in one RAR file
If you want to play with procedures and have no data in
db_size table, in rar file
are the same data as in all examples, exported with Oracle expdp utility. Used PARFILE exp_db_size.dat is also in rar, so you can very easy import data in any schema you like.
If you want to analyze all examples locally (or compare with your EBS instance!) all data are also packed in one RAR file
Cheers!
CBO ... So much words has been said for Oracle most curious technology today and still most of us know very little about it. 
