Notes on DEPTREE

After writing my own scripts to find either all parents or children of an object, I took a look at Oracle’s solution to find dependencies:

$ORACLE_HOME/rdbms/admin/utldtree.sql

It was actually written about 20 years ago, in 1991:
Rem rkooi 10/19/91 – Creation

Running the script, creates a number of objects:

OBJECT_AND_OBJECT_ID
----------------------------------------------------------------------------------------------------
PROCEDURE   SYS.DEPTREE_FILL    3304377
SEQUENCE   SYS.DEPTREE_SEQ    3304375
TABLE   SYS.DEPTREE_TEMPTAB    3304376
VIEW   SYS.DEPTREE    3304378
VIEW   SYS.IDEPTREE    3304379

7 rows selected.

 

The Parents And The Order Of Operations

When you build objects in a new environment, you need to build them in the right order of operations.

Until you have all the objects in place, you can’t create a procedure that references them all. And if those objects require more parent objects, they must be created first too. For example:

procedure reads
    view which reads a
       table which is composed of a
          type

—-

Recently I wrote in my other post about the parent and child dependencies. They give a lot of great information. But they only go one level in either direction. As you know, there can be many levels of objects.

What is the order of operations to build them? I’ve written some complex scripts here to find all the successive parents of an object.

Script To List Objects That Won’t Extend

Here is an Oracle DBA script to list objects that won’t extend due to no more room in the tablespace.

If the tablespace is set to autoextend (via the datafile clause(s)) this will not be a big issue. But if the datafiles are not set to autoextend, processes can stop until the DBA intervenes.

Use this script proactively.

/*
This script will list objects that won't be able to extend
due to insufficient space in the tablespace
*/

Select TableSpace_Name
, Owner
, Segment_Name
, Segment_Type
, To_Char((Next_Extent / 1024 / 1024), '999,999') || 'M' Nxt_Ext
From Sys.DBA_Segments A
Where Not Exists
(Select 'x'
From Sys.DBA_Free_Space B
Where A.TableSpace_Name = B.TableSpace_Name
And B.Bytes >= (A.Next_Extent * (1 + (A.Pct_Increase / 100)))
)
Order By TableSpace_Name, Segment_Name

Output will look similar to:

TABLESPACE_NAME OWNER
------------------------------ ------------------------------
SEGMENT_NAME SEGMENT_TYPE
--------------------------------------------------------------------------------- ------------------
NXT_EXT
---------

XDB XDB
XDB$CHOICE_MODEL TABLE
1M

XDB XDB
XDB$COLUMN_INFO TABLE
1M

DBA Script: How Full Are The Tablespaces?

Here is a useful script to show how full the tablespaces are. I’ve used it many times!  It’s ordered by Percent full, but you can order it any way you like.

/*
Script to show how full the tablespaces are:
By Rodger Lepinsky
ltsu.sql  for:  List TableSpace Use. 

*/

SELECT ts.tablespace_name,
to_char ( ( nvl( ts.total_bytes , 0 ) - nvl(used_bytes, 0) ) / 1024 / 1024 , '999,999' )
as free_megs,
to_char ( nvl(used_bytes , 0) / 1024 / 1024 , '999,999' )
as used_megs,
to_char ( nvl(ts.total_bytes, 0) / 1024 / 1024 , '999,999' )
as total_megs ,
to_char ( (( nvl(used_bytes, 0) / ( nvl(ts.total_bytes,0) )) * 100) , '999.99')
as Percent_Full
FROM
( select TABLESPACE_NAME,
sum( nvl(BYTES,0) ) as total_bytes
from dba_data_files
group by TABLESPACE_NAME
) ts,
( SELECT tablespace_name,
nvl(SUM(bytes), 0 ) used_bytes
FROM dba_extents
GROUP BY tablespace_name
) used_space
WHERE ts.tablespace_name = used_space.tablespace_name (+)
order by 5

Output will be similar to:

TABLESPACE_NAME                FREE_MEG USED_MEG TOTAL_ME PERCENT
------------------------------ -------- -------- -------- -------
TS_DP                                50        0       50     .00
TS_SALES_DATA_ENGINE_X               50        0       50     .00
DW_REFERENCE_TBS                    134        0      134     .00
DEMANTRA                          5,400        0    5,400     .00
DW_REFERENCE_IDX                    100        0      100     .00
BIA_RTL                           1,000        0    1,000     .00
DW_LOOKUP_TBS                       100        0      100     .00
DW_AGGREGATE_IDX                    100        0      100     .00
DW_MVLOG_TBS                        100        0      100     .00
OLAP_BAAD                         1,500        0    1,500     .00
TS_SIM_X                             50        0       50     .00
OWAPUB                               10        0       10     .00
...

B2B_RT                                4       38       42   90.33
APPS_TS_MEDIA                       497    4,715    5,211   90.47
RBS_MIG                               4       39       43   90.70
UDDISYS_TS                            2       19       21   90.77
CWMLITE                               2       21       23   91.85
ORABPEL                               1       11       12   95.31

69 rows selected.

Note:  this script can take a long time, if you have a slow server, many tablespaces, objects, and of course, extents, which the objects are made out of.

Oracle Apps R12: DBA Analysis Of TableSpaces, Log, And Control Files

Oracle Apps R12: More DBA Analysis Of TableSpaces, Log, And Control Files

Lots of tablespaces! 

select TABLESPACE_NAME
from dba_tablespaces
order by TABLESPACE_NAME

TABLESPACE_NAME
------------------------------
APPS_CALCLIP
APPS_OMO
APPS_TS_ARCHIVE
 

Oracle Applications R12: More Schema Analysis

Oracle Applications R12: More Schema Analysis

Some more analysis of the schemas and objects in Oracle Applications R12 sample database, VIS.

Lots of objects, triggers, and packages.

And over 57 million lines of source code in the APPS schema alone!

Poor Or No Error Trapping

More Architecture Mistakes To Avoid

One thing that should be blatantly obvious to anyone who has ever had to fix some code on the weekend, or in the middle of the night, is the importance of excellent error trapping and logging routines.

When you need to fix something, now, you don’t want to be reverse engineering the code or the whole system.  You want to know what happened, and what to do.  Now.

You need what I call, instantly digestible error messages.  I don’t want to hunt it. I don’t want to kill it.  I don’t want to clean it.  I don’t want to cook it.  I just want to eat it.  I want both the cause, and the solution, in the same message.

The error trapping routine must be totally reliable.   Let me list some strange things that I’ve seen in production.

Complex and Useless:

System Performance: Cobol Versus Database

More Architecture Mistakes To Avoid, Database Design Mistakes To Avoid, Tuning

When I started working with Oracle, I helped to replace a Cobol system. For a while, the company systems ran half on Cobol, and half on Oracle.  The Oracle 7 system was pretty slow then.  After the cold backups had run overnight, it was not unusual in the morning to see a query against a 700K row table take about 30 seconds.

I was asked to write a C program that only interfaced with the Cobol flat files, and read Cobol transaction records.  Each transaction was stored in multiple rows, and given repeating groups of records, could vary in size from a few rows, to dozens of rows.  The files contained about 700K to 800K rows altogether.  The program grabbed information out of the multiple lines, tallied the numbers up, and output one line of summary. Lots of IF statements, and loops.  About 800 lines of code.

I expected this program to take at least 30 seconds, given that database queries to similar size tables took that long, and that there was so much logic and so many lines of code.

In fact, the program ran in about 5 seconds!  I was really surprised.

But when you think of it, it should be obvious that

Ways To Get Duplicate Data

More Database Design Mistakes To Avoid

Duplicate data.  Designed into the system.  How can I count the ways?

Using the Wrong Object

More Database Design Mistakes To Avoid

Given that you need more information in or from the database, what do you do?

Do you create one of the following?

Simple Rule Of Thumb To Determine the Number of Tables In A Relationship

More on Database Design Mistakes to Avoid

Here is my rule of thumb for the number of tables, for each relationship.

Stamping Out Cartesian Products

Over the last number of years, I kept finding Cartesian Products (CP) in queries and other objects like views.

These have caused a number of serious performance issues.  A single CP in a query can make the whole server go slow.  The other problem is the result sets, which come back many multiples too big.

No gizmo tool will help you with a CP.  No query analyzer will tell you that you have a problem, or how to fix it.  Sorry mouse lovers.

Cartesian products are one problem area that only observation, knowledge, and good SQL skills will overcome.

When I have pointed out the cartesian products to the rest of the crew, many were in denial.  The really odd thing was that the more “experienced” the database person was, say, over 10 or 15 years of experience, the less obvious it was to them.  And the more they insisted on playing with statistics or indexes.  Which of course did nothing to deal with the real issues.

Oracle Performance Tuning

One of the first entries to start my notes should be the one thing I’ve already done a lot of writing on.

Since I started working with the Oracle database in 1995, I’ve always been very concerned about tuning, performance and response times. Even with the poor software and hardware that we had, I still had to make it work fast. Even though much of the conventional wisdom at the time was to just buy faster hardware, this was never an option at the companies where I worked. I made things go fast by doing what I could do; changing the code, not by buying new hardware.

Later, I was promoted from a developer, to a DBA, and a unix admin. A new job in year 2000 – 2001 gave me a daunting challenge.  The startup wanted to insert ONE BILLION rows of data into an Oracle 8i database, every day. Which is about 11,000 rows per second.  This is not difficult now, but with year 2000 hardware and Oracle 8i software, I was only getting about 6,000 row inserts per second to begin with on the hardware I had. I researched how to do this all day and evening. It took me very deep into Oracle internals and storage.

One of my “few” discoveries was that a number of the tuning paradigms at the time, after I tried them, really made absolutely no significant difference at all. Some of these concepts were whether we used RAID 0, 1, or 5, and hit ratios in Oracle’s buffers. My rigorous experiments where I modified only one variable at a time, such as the RAID setting, produced no difference in the data results at all. The data results were not supporting the paradigms.