Re: Why it takes so long.

From: Uwe Schneider <pi_at_nospam.richard-schneider.de>
Date: Fri, 26 Mar 1999 19:24:02 +0100
Message-ID: <36FBD0C2.452F0F62@nospam.richard-schneider.de>

Hello,

> 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.

This looks perfectly correct. But I estimate there is a dirrerent cause for the long response time. Even if the query would run as a full table scan it is estimated to last no longer than one second.

Is the server heavily overloaded?
Is there network saturation?
Do you have problems connecting to the database? Are other querys "fast"?

> 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

To allow for query plans and statistics you need to execute the SQL file "utlxplan.sql" located in $ORACLE_HOME/rdbms/admin. You could execute is as user SYSTEM and create a synonym in your actual schema.

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 Fri Mar 26 1999 - 12:24:02 CST