Yesterday we improved the run time of our previously longest-running batch job, cutting 90% off the duration. How? We identified a table that needed data removed. But how did we know the job run time, and how did we know, or should we have known, about the table cleanup?
On a somewhat related note, I answered a question in an ASUG forum where someone asked for step-by-step instructions to setting up a successful data archiving (or data management, or information lifecycle management) project. Well, guess what? The answers are out there, you just need to know where to look.
Let’s start with the classic approach to space management. You look at the DB02 transaction, you drill into the history of the database. It’s growing. A lot. Now what? You begin to look further (I’ll use Oracle as the example here, but the methods should apply to others as well). You look at tablespaces. If you have gone with the defaults, you’re in trouble, particularly with newer ERP and other products where you get a minimal number of tablespaces. All the growing objects are spreading into each other and getting the out will be more challenging. But let’s say you find a big, fast growing accounting table.
You can then use DB02 and look at each table and related index. There’s a history, which if you have set up the logging properly, will tell you what your growth patterns are. Great; ;et’s do that for 100 tables in 10 SAP production instances, monthly, weekly, or even daily. NOT. We need automation, and we need some rules.
Joe Haynes pointed me (back) to SAP note 706478 which has guidance on finding typical growing objects that need to be managed. And I sent the above-mentioned inquirer to the ILM area of SAP. By the way, Joe and I have been collaborating on producing tips and tricks for using ST03 – see the First Steps wiki page. I’ll put more there over time on how I found the long running batch program.
As I talked to our lead DBA yesterday on the latest ways to find where objects have grown, or as she pointed out, where they have shrunk and space needs to be reclaimed, I got a few insights into ways to automate space tracking.
(1) In Oracle 10, there are a wealth of new features, which you can learn about by searching SAP notes for terms such as DBMS_SPACE. A couple example notes:
927813 “…You require information about the space utilization of segments.”
(note there are license restrictions on some of the advanced advisor tools)
I’m not able to give away our scripts, but these snippets should help you build your own analysis methods:
dbms_space.object_space_usage('&1', objectname, objecttype,
NULL, su, sa, cp);
Leading to clean up commands such as:
alter table “ADR3S2” enable row movement;
alter table “ADR3S2” shrink space compact;
alter index “ADR3S2~0” rebuild online parallel 1 ;
alter table “ADR3S2” disable row movement;
alter index “ADRC~I01” coalesce ;
alter index “ADRC~Z1” coalesce ;
(2) Use the TAANA transaction. Alas, this is only available in Netweaver 04 and above, so we don’t have it in our R/3 4.7 system. But here are SAP on line help page links found by searching on “TAANA_ANALYSE_BY_DATE”:
While TAANA is geared towards a statistical analysis of frequency distributions, to show for instance, which business unit has the most accounting documents on-line, it seems feasible to set up automated space tracking based on thresholds or lists of known objects.