Home » RDBMS Server » Performance Tuning » too slow select.
too slow select. [message #65434] Mon, 20 September 2004 23:39 Go to next message
Milly
Messages: 72
Registered: March 2004
Member
I have a problem:

i have a table with 50000  records and my select lasts 15 minutes!!

this is my table:

CREATE TABLE AnagTitoli_BP (
       k_Titolo_BP          VARCHAR2(32) NOT NULL,
       n_Titolo_BP          VARCHAR2(50) NULL,
       c_Lunga              VARCHAR2(100) NULL,
       e_k_TipoTitoloBP     VARCHAR2(20) NULL,
       k_ISIN               VARCHAR2(12)  NULL,
       k_Tit_Bloomberg      VARCHAR2(23) NULL,
       e_k_divisa           CHAR(3)  NULL,
       f_Quotato            VARCHAR2(20) NULL,
       k_Tipo_Obbligazione  CHAR(1) NULL,
       n_Nazionalita        VARCHAR2(50) NULL,
       n_Mercato            VARCHAR2(50) NULL,
       f_Del_Security       CHAR(1) DEFAULT 'N' NOT NULL
                                          CHECK (f_Del_Security IN ('S', 'N')),
       n_Settore            VARCHAR2(50) NULL,
       n_Classe_Fondo       VARCHAR2(50) NULL,
       n_Categoria_Fondo    VARCHAR2(50) NULL,
       n_SGR_SICAV          VARCHAR2(50) NULL,
       f_Accoppiato         CHAR(1) DEFAULT 'N' NOT NULL
                                          CHECK (f_Accoppiato IN ('S', 'N')),
       f_TitoloFittizio     CHAR(1) DEFAULT 'N' NOT NULL
                                          CHECK (f_TitoloFittizio IN ('S', 'N')), 
       d_Inserimento        DATE DEFAULT sysdate NOT NULL,
       user_Inserimento     VARCHAR2(100) DEFAULT USER NOT NULL,
       d_Modifica           DATE DEFAULT sysdate NOT NULL,
       user_Modifica        VARCHAR2(100) DEFAULT USER NOT NULL,
       f_annullato          CHAR(1) DEFAULT 'N' NOT NULL
                                          CHECK (f_annullato IN ('S', 'N')),
       rowversion           DATE DEFAULT sysdate NOT NULL
);

ALTER TABLE AnagTitoli_BP
       ADD  ( CONSTRAINT PK_AnagTitoli_BP PRIMARY KEY (k_Titolo_BP) ) ;

any idea?

thanks!
Re: too slow select. [message #65435 is a reply to message #65434] Tue, 21 September 2004 07:47 Go to previous messageGo to next message
VISHNU
Messages: 14
Registered: November 2001
Junior Member
Check this:
select * from dba_segments where segment_name = 'ANAGTITOLI_BP';

particularly check the value for "extents" field in above query. May be the table had too many updates and deletes and so the high water mark might have got set too high.
Re: too slow select. [message #65441 is a reply to message #65435] Wed, 22 September 2004 00:59 Go to previous messageGo to next message
Milly
Messages: 72
Registered: March 2004
Member
I saw that the value is 11...but I don't know what I have to do with it.
Anyway, I have many updates on the table...have you got any advice for this?
Re: too slow select. [message #65444 is a reply to message #65441] Wed, 22 September 2004 06:30 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
it depends on the query/sql you use.
check whether those indexes are used by your sql.
analyze ( gather stats) the table and index/
Re: too slow select. [message #65445 is a reply to message #65444] Wed, 22 September 2004 06:43 Go to previous messageGo to next message
Milly
Messages: 72
Registered: March 2004
Member
The sql USES the indexes...the problem is on the?? database?? server?? service??
I don't know...
Re: too slow select. [message #65447 is a reply to message #65434] Wed, 22 September 2004 07:19 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
what select?!?
Re: too slow select. [message #65448 is a reply to message #65445] Wed, 22 September 2004 07:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Please post the execution plan.
RUn a fresh analyze on table/index.
try again.
Re: too slow select. [message #65449 is a reply to message #65448] Wed, 22 September 2004 07:37 Go to previous messageGo to next message
Milly
Messages: 72
Registered: March 2004
Member
I can't do it!! I've not the privileges to do it!!
I'm desperate.....It's somenthing on the database...on the service...on the...I DON'T KNOW!!
Re: too slow select. [message #65450 is a reply to message #65449] Wed, 22 September 2004 08:58 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
without execution plan / statspack or generated statistics how can you say that the indexes are used??.
ANd you are not posting the sql involved.
If you can't post the necessary information, no forum can help.
Best of luck.
Previous Topic: Trapping error in FORALL statement
Next Topic: order by makes troubles in my select!
Goto Forum:
  


Current Time: Wed Feb 28 02:55:59 CST 2024