Re: small table takes more time as compared to bigger table
Date: Wed, 18 May 2011 10:19:14 -0700 (PDT)
Message-ID: <aaedf361-1370-45ba-a9f1-d33bd421f6a5_at_18g2000prd.googlegroups.com>
On May 17, 8:56 am, MadhavC <choudhar..._at_gmail.com> wrote:
> 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.
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
-- _at_home.com is bogus. http://www.tmcnet.com/channels/erp-software/articles/175757-erp-software-netsuite-utilize-oracle-exadata-cloud-software.htmReceived on Wed May 18 2011 - 12:19:14 CDT