Need help because of full table scan and Performance

[Martin Sigge]

Hi,

we have an application (Remedy Action Request System) with 100000
entrys.
For a quers, we have to use LIKE "%xxx% Order by..." and this results in
a full table scan.
This scan need 3 minutes to finish. The ORACLE 7.3.4 is on a NT Server
4.0 with 1GB of RAM
and the ora.ini is like:

db_block_buffers = 50000
db_block_size = 4096
db_files = 100
db_file_multiblock_read_count = 64
log_buffer = 3084000 # 3 MB für SGA 63 M
max_dump_file_size = 16280
sort_area_retained_size=65536
sort_area_size=524288

I think, the buffers and sort size is to small.

Is it correct?

Thank you,

Martin

[Sybrand Bakker]

Comments embedded

Hth,

Sybrand Bakker, Oracle DBA

Martin Sigge <martin...@materna.de> schreef in berichtnieuws
3934D012...@materna.de...

> Hi,
>
> we have an application (Remedy Action Request System) with 100000
> entrys.
> For a quers, we have to use LIKE "%xxx% Order by..." and this results in
> a full table scan.
> This scan need 3 minutes to finish. The ORACLE 7.3.4 is on a NT Server
> 4.0 with 1GB of RAM
> and the ora.ini is like:
>
> db_block_buffers = 50000

way too high. 50000 * 4k means 200M db_block_buffers alone. Probably you are
just forcing the server to fault

> db_block_size = 4096
> db_files = 100
> db_file_multiblock_read_count = 64

way too high, block_size times multiblock_read_count should equal 64k
You are now probably reading records you're never going to use.

> log_buffer = 3084000 # 3 MB für SGA 63 M

again way too high

> max_dump_file_size = 16280
> sort_area_retained_size=65536
> sort_area_size=524288

as this is per process you could increase it to 1M but there it should stop.
>

Please run utlbstat and utlestat and verify my comments

> Thank you,
>
> Martin
>

[cgr...@disc.com]

You could look into the option of adding the advanced indexing
capabilities of OMNIDEX from DISC to perform fast keyword lookups
instead of slow table scans for LIKE %value%.

See www.disc.com/keyword.html or contact me for more information.

Cheryl Grandy
DISC
cgr...@disc.com
303 444-4000
www.disc.com/home

OMNIDEX - for the fastest applications ever!

In article <3934D012...@materna.de>,

Martin Sigge <martin...@materna.de> wrote:
> Hi,
>
> we have an application (Remedy Action Request System) with 100000
> entrys.
> For a quers, we have to use LIKE "%xxx% Order by..." and this results
in
> a full table scan.
> This scan need 3 minutes to finish. The ORACLE 7.3.4 is on a NT Server
> 4.0 with 1GB of RAM
> and the ora.ini is like:
>
> db_block_buffers = 50000

> db_block_size = 4096
> db_files = 100
> db_file_multiblock_read_count = 64

> log_buffer = 3084000 # 3 MB für SGA 63 M

> max_dump_file_size = 16280
> sort_area_retained_size=65536
> sort_area_size=524288
>

> I think, the buffers and sort size is to small.
>
> Is it correct?
>
> Thank you,
>
> Martin
>
>

--
Cheryl Grandy
DISC
Get OMNIDEX for the fastest
applications ever

Sent via Deja.com http://www.deja.com/
Before you buy.