Re: Why it takes so long.

Sounds like you don't have the explain table in the schema that you are running this query in. You need to run utlxplan.sql (That's what its called on NT) to be able to use 'set autotrace on'

Hope this helps get you one step closer to your ultimate answer.

Manmohan Mann

Peizhong Wu wrote:
>
> Hi,
>
> The table is:
> my_usr(
> usr_id NUMBER PRIMARY KEY,
> login_id VARCHAR2(8),
> password VARCHAR2(8),
> cluster_id NUMBER,
> lastname
> firstname
> ....
> );
>
> And the index I created is:
>
> CREATE INDEX usr_login_pw_cl_index
> ON kgs_usr( login_id, password, cluster_id ) TABLESPACE index05;
>
> The query is:
>
> SELECT * FROM my_usr
> WHERE login_id = l_login_id
> AND password = l_password
> AND cluster_id != 0;
>
> It is just very simple query. But I do not know why it take so long. The entry
> in this table is: 6350.
>
> However, I can not set autotrace on in my server. The following is the error
> message:
>
> SQL> set autotrace on
> Unable to verify PLAN_TABLE format or existence
> Error enabling EXPLAIN report
> Cannot find the Session Identifier. Check PLUSTRACE role is enabled
> Error enabling STATISTICS report
>
> Thanks
>
> Peizhong
>
> Uwe Schneider wrote:
>
> > Hello,
> >
> > >
> > > When one user login, the user login ID and Password is entered and
> > > compared with the record in database. But it take so long , about 5
> > > seconds.
> > >
> > > I have created index for login ID and password for it, but still can not
> > > solve the problem.
> > >
> > > The system check show that the CPU is 100% for almost 5s, while IO is
> > > almost idle.
> > >
> >
> > Perhaps you want to tell us your SQL query and the description and
> > indexes of your table.
> >
> > Have you traced the query (sqlplus: set autotrace on)? What are the
> > results?
> >
> > Uwe
> >
> > --
> > Uwe Schneider | Telefon +49 7251 / 82587
> > Karlsdorfer Str. 31 | Mail pi_at_richard-schneider.de
> > DE-76646 Bruchsal |
> > Linux - OS al dente!
Received on Thu Mar 25 1999 - 19:48:54 CST