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: Performance Issue

Re: Performance Issue

From: Steve Johnson <johnst_at_ncs.com>
Date: Tue, 3 Sep 2002 08:44:00 -0500
Message-ID: <un9f534ul76j56@corp.supernews.com>

Sounds to me like a High Water Mark issue. If you're query is doing a full table scan it has to read all of the way to the HWM. So if your table use to have 1,000,000 rows and you've deleted all but 1 row it still has to read the same number of blocks as it use to when it had 1,000,000 rows.

Truncating the table resets the HWM. Try truncating the table and reloading it with data and running the query again.

You can use the DBMS_Space built in to find the HWM.

HTH
Steve...

"TC3 Racer" <gordonchapman_at_hotmail.com> wrote in message news:gb1d9.4100$J47.310606_at_stones...
> I have a table that a select Count(*) .... takes 5-10 secs
> A table that is a copy of this with only 4 columns of the above comes back
> instantly.
>
> Full Table
>
> DOUBLE_METAPHONE NOT NULL VARCHAR2(50)
> ORIGINAL_STRING NOT NULL VARCHAR2(50)
> PHONETIC_STRING VARCHAR2(100)
> POI_TYPE NOT NULL VARCHAR2(50)
> ROAD_NAME VARCHAR2(100)
> TOWN_NAME VARCHAR2(50)
> COUNTY_NAME VARCHAR2(50)
> SRS_NAME NOT NULL VARCHAR2(50)
> X_COORDINATE NOT NULL NUMBER
> Y_COORDINATE NOT NULL NUMBER
> SEGMENT_ID NOT NULL NUMBER(38)
> ROAD_MATCH NOT NULL NUMBER(1)
> PRIORITY_LEVEL NOT NULL NUMBER(2)
>
> Copy of
>
> NAME NOT NULL
 VARCHAR2(50)
> TYPE NOT NULL
 VARCHAR2(50)
> X NOT NULL NUMBER
> Y NOT NULL NUMBER
>
> Any suggestions as to what the possible problem could be or what i should
> do to investigate further
>
> Many Thanks
>
> GC
>
>
>
>
>
Received on Tue Sep 03 2002 - 08:44:00 CDT

Original text of this message

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