Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: why administrator refuse to give permission on PLUSTRACE

Re: why administrator refuse to give permission on PLUSTRACE

From: DA Morgan <damorgan_at_psoug.org>
Date: Sun, 04 Nov 2007 12:00:02 -0800
Message-ID: <1194206398.972648@bubbleator.drizzle.com>


Hasta wrote:

> In article <1194121577.959656_at_bubbleator.drizzle.com>, 
> damorgan_at_psoug.org says...

>> Hasta wrote:
>>>> 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);
>>> The main problem is : this table lacks a primary key
>> A primary key has nothing to do with performance. A constraint only
>> affects data integrity. The index, above, does everything that would
>> or could be done by creating a PK.
>>
>>>> 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.
>>> I get cost 2 with an index on (person_id, doc_name)
>>>
>>> If more is really needed, I would dig out tom kyte's books
>>> and consider playing with the index, using another table
>>> organization, etc...
>>>
>>> Calmly and very carefully.
>> Not every table should be a heap table. This one shouldn't be
>> if you want the most efficient query.
>>
>> 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);
>>
>> EXPLAIN PLAN FOR
>> SELECT doc_name
>> FROM t
>> WHERE person_id = 221;
>>
>> SELECT * FROM TABLE(dbms_xplan.display);
>>
>> --------------------------------------------------------------------
>> | Id | Operation | Name | Rows | Bytes | Cost
>> %CPU)| Time |
>> --------------------------------------------------------------------
>> | 0 | SELECT STATEMENT | | 298 | 8642 | 1
>> (0)| 00:00:01 |
>> |* 1 | INDEX RANGE SCAN| SYS_IOT_TOP_73394 | 298 | 8642 | 1
>> (0)| 00:00:01 |
>> --------------------------------------------------------------------
>>
>> Now give this challenge to all of the developers you know and see how
>> many get it? From my experience it won't be 5 in 100.
> 
> Looks like a fair figure.  And ?  What is your point, Daniel ?
> 
> Supplementing Peter's answer, I get cost 3 with the IOT above.
> (recall: cost 2 with an index)
> 
> Both may be pretty good or pretty bad. We dont know, since we 
> are not given a spec of desired response time nor an upper
> bound on the number of rows.

My point was that while the developers here seemed more than willing to point fingers at incompetent DBAs from my experience the developers are equally guilty of not keeping their skills current. Not more so ... equally so.

-- 
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 Sun Nov 04 2007 - 14:00:02 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US