Re: SQL Tuning Question

From: <rspeaker_at_my-deja.com>
Date: 2000/05/12
Message-ID: <8fhn8d$lfs$1@nnrp1.deja.com>#1/1

Mark,

at the time I posted those stats, the database had been up and running for about 30 hours. The speed of the query isn't the issue, as it takes less than 10 seconds to complete. I am simply trying to find a way to elminiate the full table scans without rewriting the code. I was wondering about enabling the keep_buffer_pool and caching the table there, but wouldn't I need enough memory allocated to that pool to store the entire table (avg row len=2000, # recs=315000 tot space=630MB?)??

or am I just simply wasting my time trying to do anything at all about it?

Thanks,
Roy

In article <8ffg8a$81c$1_at_nnrp1.deja.com>,   Mark D Powell <markp7832_at_my-deja.com> wrote:
> In article <MPG.1384d9ecc7be8a3b989680_at_news.flashcom.net>,
> jason_at_server.com (Jason) wrote:
> > Try to use hint:
> >
> > select /*+ INDEX(tableA) */
> > tableA.*, tableA.rowid
> > from tableA
> > where note IS NULL;
> >
> > This is assumed that you have a proper index.
> >
> > In article <8feu19$iu0$1_at_nnrp1.deja.com>, rspeaker_at_my-deja.com
 says...
> > > Hello all,
> > >
> > > Consider the following table description & SQL statement:
> > >
> > >
> > > Name Null? Type
> > > ------------------------------- -------- ----
> > > INCIDENT_NUMBER VARCHAR2(8)
> > > USERID VARCHAR2(8)
> > > DATE_STAMP DATE
> > > NOTE VARCHAR2(2000)
> > >
> > >
> > > select tableA.*, tableA.rowid
> > > from tableA
> > > where note IS NULL;
> > >
> > >
> > > First, please don't ask why the query is written this way or what
 it is
> > > doing -- it was written by a developer who is no longer here and
 the
> > > ones that are can't answer.
> > >
> > > Obviously, this query is generating a full table scan due to the
 IS
 NULL
> > > condition. According to my monitoring tools, both Quest and
 Oracle,
> > > this query has been executed about 250 times and generated 30,000
 disk
> > > reads. The statement is showing a hit ratio of about 0.50%. The
 table
> > > has over 300,000 records in it, of which only about 425 match this
> > > criteria.
> > >
> > > My block size is 4k, so I cannot index the note field, and even if
 I
> > > could the IS NULL would ignore it. I have made some
 recommendations
> > > back to the developers on how to fix it (set the null values to
 some
> > > dummy string and query using = 'dummy string'), but as yet they
 haven't
> > > gotten around to it. Aside from that, is there anything else I
 can
 do
> > > from the DBA standpoint to improve it's performance?
> > >
> > > I am running Oracle 8.0.5.1 on AIX 4.2.1. I have 4x166 CPU and
 640
 MB
> > > of memory. My DB_BLOCK_BUFFERS is currently set to 15,000 giving
 me
> > > roughly 60 MB of DB_BUFFERS. My hit ratio is consistent at about
> > > 45%-50% but I can't tell how much of that is being caused by this
 query.
> > > Would moving this table into the BUFFER_POOL_KEEP help?
> > >
> > > Thanks,
> > > Roy
> > >
> Jason, to use an index the indexed columns must be referenced in the
> where clause and on a single column index null values are not indexed,
> (i.e., stored in the index.)

>

> rspeaker, you did not say how long the database was running. Two
> hundred firty executions and 30,000 I/O are nothing if the database
 has
> been running for 5 days but might be important if it was running for
> only a couple of hours.

>
> More importantly how important is this query? How long does it take
 to
> run? If it is important and needs to complete more quickly here is an
> idea. Add a single byte indicator column to the table and place
 insert
> and update triggers on the table that test the note column for being
> null. When it is null the indicator column is set to a not null value
> then a single column index on this column will have only the approx.
> 450 entries in it and will provide very fast access.

>

> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --

>

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

>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri May 12 2000 - 00:00:00 CDT