Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 03 Nov 2007 07:09:18 -0700
Message-ID: <1194098964.194429@bubbleator.drizzle.com>

Galen Boyer wrote:
> On Sat, 3 Nov 2007, hasta_l3_at_hotmail.com wrote:
>

>> I dont think that a dba will find out alone.
>>
>> And I dont think that a dba/developper team will 
>> find out within reasonable time, if developpers 
>> are not allowed to look.

>
> You have succintly stated the #1 issue.

The issue is not finding it ... I've handed it to you on a platinum platter. If I wanted to be nasty I'd have posted a StatsPack. This is a very simple problem. Why can't one of you see it and fix it?

And please understand I am not trying to throw mud here. I am just trying to illustrate my point about whether developers belong in production. Here I have already done the heavy lifting. I've sifted through massive amounts of metrics to identify one simple stored procedure as the issue. And not one of you just wrote the solution.

Sybrand, and others, are probably having a hard time resisting the urge to tell you what is in plain site. I will post the solution tomorrow if no one gets it first.

And just because I'm in the mood here's another one I give my students. Here's the setup.

CREATE TABLE t (
doc_id VARCHAR2(10),
person_id NUMBER(7),
doc_name VARCHAR2(45));

CREATE INDEX ix_t_person_id
ON t(person_id);

CREATE SEQUENCE seq_t;

DECLARE
  t_docname dbms_sql.VARCHAR2_TABLE;

CURSOR c IS
  SELECT object_name
  FROM dba_objects;
BEGIN
   OPEN c;
   LOOP
     FETCH c BULK COLLECT INTO t_docname LIMIT 250;

     FORALL i IN 1..t_docname.COUNT
     INSERT INTO t
     (doc_id, person_id, doc_name)
     VALUES
     (seq_t.NEXTVAL, MOD(seq_t.CURRVAL, 233), t_docname(i));

     EXIT WHEN c%NOTFOUND;

   END LOOP;
   COMMIT;
   CLOSE c;
END;
/

exec dbms_stats.gather_table_stats(USER, 'T', CASCADE=>TRUE);

That's was the set up. Here's the query identified as the problem.

SELECT doc_name
FROM t
WHERE person_id = 221;

Hint: You can get the cost to 1 with CPU = 0.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Nov 03 2007 - 09:09:18 CDT