Hello Experts,

I am working on an enterprise application and facing some issue while
working on DB queries.

I have a table - TABLE1 (having total 5 million rows)
col1, col2, col3, col4, col5, col6, col7, col8, col9, col10

I have another table - TABLE2 (having total only 1000 rows)
col1, col4, col6

TABLE2 contains redundent information for col1, col4, and col6 from
TABLE1

I run the query as below for 16000 times for different values of col4
in where clause-
select col1, col4, col6 from TABLE1 where col4='SOMEVALUE'
This finish in few milliseconds

I run the following query for 16000 times for different values of col4
in where clause
select col1, col4, col6 from TABLE2 where col4='SOMEVALUE'
Even though TABLE2 is a very small table as compared to TABLE1, this
time these 16000 queries takes around 2 seconds. Almost 5-6 times more
than that of the query for TABLE1.

TABLE1 and TABLE2 are indexed on col1.

Can you please suggest what parameters I should be looking for to
understand and fix this.

Thanks in advance for your help.

On May 17, 11:56*am, MadhavC <choudhar... (AT) gmail (DOT) com> wrote:
First, the number of rows does not really represent size very as you
should really consider number of rows times the average row size which
basically identifies how many bytes of data you have to process.

Second, look at the explain plans for the queries. Make sure the
index is being used. Old statsistcs could cause the index to not be
used. Also if you simplified the queries you posted then make sure
that no functions were placed on the table column side of the operator
in the where clause conditions instead of on the variable side.

If the statistics are current check the dba_tables.avg_row_len and
chained_cnt values. If the rows are short then chain_cnt should be
near zero as a percentage of the number of rows. If it isn;t then
past activity on the table created migrated rows and a high percentage
of migrated rows could impact performance. This is not very likely,
but I have seen it once or twice in the last decade.

You may also want to compare the table allocation to the needed
allocation. If the table is significantly larger than necessary to
hold the data including necessary block overhead (initrans, pctfree,
fixed header) then a table reorganization may be of benefit. This is
also not usually necessary or beneficial.

The use of FGAC/VPD on the table could also be a factor you may need
to check for.

HTH -- Mark D Powell --

On May 17, 11:56*am, MadhavC <choudhar... (AT) gmail (DOT) com> wrote:
make sure statistics are up to date on both tables. What does explain
plan say for both?

sqlplus
set autotrace on
set time on
select .....


OR - just use table1....

MadhavC:

# I run the query as below for 16000 times for different values of
col4 in where clause-

Don't do that ... don't run any query 16000 times ... use SQL and join
things together so that you run the query once.

Give Oracle as much work as is needed to be done in as few statements
as possible ...

On May 17, 5:42*pm, John Hurley <hurleyjo... (AT) yahoo (DOT) com> wrote:
I second that.. Sounds like you need to learn what a cursor is and
how to get all of the values at once and loop through the cursor.

From the orignial post
Sounds like you are doing 15K times more work than necessary If you
only have 1000 rows how can you have 16K different rows for
col1,col4,col6?

The 16000 times query is not at the same time.These queries are fired
as and when the application passes through this area of the code. I am
gathering the cumulative timings for these queries.

On May 18, 4:55*am, MadhavC <choudhar... (AT) gmail (DOT) com> wrote:
So you are running a query 16000 times against a table with 5 million
rows and the process completes in milliseconds? Does not compute...

On May 17, 8:56*am, MadhavC <choudhar... (AT) gmail (DOT) com> wrote:
Oracle will do things differently with a table small in relation to
the SGA size. When you look at the explain plans, you may find it is
deciding to do full table scans. Wouldn't you if you had the choice
of scanning 1000 rows in memory versus having to deal with that old
creaky spinning rust, even to ask for just a couple of blocks?

You might also look at the performance of the whole system. I have an
issue similar to this, and it shows up quite noticeably in the cpu
usage on the dbconsole top activity screen. I spent a lot of time
convincing myself Oracle was really doing the right thing. I'm also
getting TX locks when scaling up, which must be something I've done
wrong in the app...

Are you updating this small table? Oracle may have to create lots of
in-memory copies of the blocks involved, and spend some amount of time
reconstructing things from undo, and otherwise copying and moving
things around that you might not expect without looking at what it is
really doing. There might even be bugs or misfeatures involved, since
this can change severely for some versions and patch levels. It might
help if you told us about those, and your hardware configuration too,
and any optimizer settings.

Of course, application bugs can bite, especially with abnormalized
designs.

Most important to understand is that there are _no_ make_db_faster
parameters. Some enterprise vendors make you use make_db_slower
parameters.

jg
--
@home.com is bogus.
http://www.tmcnet.com/channels/erp-s...d-software.htm

On May 17, 5:56*pm, MadhavC <choudhar... (AT) gmail (DOT) com> wrote:
Hi,
provide

version of db
query
explain plan

In general
select * from tab1 WHERE ..
and
select * from tab2 WHERE ..

are not comparable, they are two different query

Regards,

Donatello Settembrino

....I noticed a few things that may be relevant to the discussion.

From the original post:
My comments:
If the WHERE clause in queries for both tables are restricted on "col4", of what relevance is it to have an index on "col1"? Whatever optimization that was introduced by indexing col1 is most likely not even used.

Rich Pascual