Re: Finding full-table scans?

From: Howard J. Rogers <howardjr20002_at_yahoo.com.au>
Date: Tue, 8 Apr 2003 08:43:00 +1000
Message-ID: <o%mka.9259$1s1.160992@newsfeeds.bigpond.com>

"Mike Ault" <mikerault_at_earthlink.net> wrote in message
> We are all entitled to our opinions. However, whether or not the
> advice given in past postings was correct or not bears little on
> whether the information currently posted is good or not. In this case,
> the script does as specified. if a small bit of self promotion is a
> crime then all of us are guilty.
>
> Besides, I don't see you answering the question, so perhaps you should
> just keep quiet unless you have something constructive to say.
>
> Mike Ault

My inbox has been fair brimming with mail from people claiming I've been too harsh on Don Burleson, just as Mike did here. After all, he provided a script which works, doesn't it?? And he is a Very Important Person who edits the Oracle Internals magazine, so he surely knows more than me. Surely??

Does the script work? I thought I'd check.

Don's script has this little nugget of a where clause:

where

   a.address = p.address
   and
   t.owner = s.owner
   and
   t.table_name = s.segment_name
   and
   t.table_name = p.name
   and
   t.owner = p.owner
   and
   t.owner not in ('SYS','SYSTEM')

Now the interesting bit is "t.table_name = s.segment_name", where t = dba_tables and s=dba_segments.

Can you think of any circumstances under which a table_name doesn't match a segment_name?? There are several.

Here's one for starters:

SQL> connect scott/tiger
SQL> create table blah (
  2 col1 number primary key)
  3 organization index;

SQL> select table_name from dba_tables where table_name='BLAH'; TABLE_NAME

---

BLAH SQL> select segment_name from dba_segments where segment_name='BLAH';

no rows selected

So, Don's script doesn't work at all for Index Organized Tables, simply because when you create an IOT, the segment that gets created is named after your primary key constraint, and doesn't use the table name included in the 'create table' clause at all.

Any others?

Try this one:

SQL> create table sales (
  2 col1 number primary key,
  3 col2 char(5))
  4 partition by range(col1)
  5 (partition p1 values less than (100),   6 partition p2 values less than (200));

Table created.

SQL> select table_name from dba_tables where table_name='SALES';

TABLE_NAME

---

SALES SQL> select segment_name from dba_segments where segment_name='SALES';

SEGMENT_NAME

---

SALES
SALES There are two segments called SALES, both owned by Scott, because my create table statement creates two partitions. Simply asking for table_name to be equal to segment_name means Don's pearl of a script doesn't work properly for partitioned tables, therefore.

Dare we ask how we go with clusters??

SQL> create cluster people
  2 (deptno number(2))
  3 size 512;

Cluster created.

SQL> create index people_idx on cluster people;

Index created.

SQL> create table dept10
  2 cluster people (deptno)
  3 as select * from emp where deptno=10;

Table created.

SQL> create table dept20
  2 cluster people (deptno)
  3 as select * from emp where deptno=20;

Table created.

SQL> select table_name from dba_tables where table_name='PEOPLE';

no rows selected

SQL> select segment_name from dba_segments where segment_name='PEOPLE';

SEGMENT_NAME

---

PEOPLE The reason this time, of course, is that 'PEOPLE' is the cluster name: the cluster physically exists, so is listed in DBA_SEGMENTS. But the tables which logically exist within the cluster are called DEPT10 and DEPT20, so it's these names which are listed as table_name in DBA_TABLES.

So, Don's script is useable provided you don't implement any of the 'advanced' features of Oracle segment handling that have appeared since about version 7.

Oh... I forgot to mention. If you are concerned about full scan operations, where in Don's script will you find out about index fast full scans? Er, nowhere.

OK, OK... so maybe Don was just being nice, and assuming that the original poster doesn't have anything so advanced as an IOT or a partitioned table.

What else is wrong with it, then? Not much, except that he collects everything from v$sql_plan and *only then* throws away the information concerning SYS and SYSTEM. Usual best practice, surely -and especially from the world's leading performance tuning expert- is to throw away early, not after you've gone to the effort of collecting it.

I'm not even going to start on the huge impact querying from dba_segments will have on a database with even a reasonable number of segments; nor the hideous number of library cache latch hits his little query will induce.

All this, remember, from someone who describes themselves as "one of the world's top Oracle Database experts", as having "more than 20 years of full-time DBA experience", and as having been "chosen by Oracle Corporation to write the authorized Oracle Press books Oracle 9i High Performance Tuning...".

But his script only works if you use tables, tables and nothing but tables... and preferably no indexes either. It's shoddy. My DBA Fundamentals I students could have come up with something comparable.

So no, I haven't been unduly harsh on Don. His technical prowess is demonstrably pathetic. As I originally said: Humbug Alert.

Regards
HJR Received on Mon Apr 07 2003 - 17:43:00 CDT