ORACLE db performance tuning

Question

We are running into performance issue where I need some suggestions ( we are on Oracle 10g R2) The situation is sth like this

1) It is a legacy system.

2) In some of the tables it holds data for the last 10 years ( means data was never deleted since the first version was rolled out). Now in most of the OLTP tables they are having around 30,000,000 - 40,000,000 rows.

3) Search operations on these tables is taking flat 5-6 minutes of time. ( a simple query like select count(0) from xxxxx where isActive=’Y’ takes around 6 minutes of time.) When we saw the explain plan we found that index scan is happening on isActive column.

4) We have suggested archive and purge of the old data which is not needed and team is working towards it. Even if we delete 5 years of data we are left with around 15,000,000 - 20,000,000 rows in the tables which itself is very huge, so we thought of having table portioning on these tables, but we found that the user can perform search of most of the columns of these tables from UI,so which will defeat the very purpose of table partitioning.

so what are the steps which need to be taken to improve this situation.

if there are, say, 10mm out of 20mm records that are "active", I'm surprised a FTS wouldn't occur, but it is NOT surprising that such a query would take some time (thats not an oltp type query, more a batch/report type). Are your concerns mostly about batch/report type queries? — tbone
– tbone, Commented Apr 18, 2012 at 13:18
probably worth looking at this question, its essentially the same thing, stackoverflow.com/questions/2030011/… — Matthew Watson
– Matthew Watson, Commented Apr 18, 2012 at 14:33
1) What type of index scan? Fast Full Index Scan, Index Range Scan, something else? 2) Do you have good statistics on these tables? 3) Depending on the process to delete 5 years of data, you may be left with tables and indexes with space to hold 10 years of data. So fast full index scans and full table scans would still need to read as many blocks? 4) What percentage of isActive is 'Y'? — Shannon Severance
– Shannon Severance, Commented Apr 18, 2012 at 18:40

Rob van Wijk · Accepted Answer · 2012-04-18 11:45:34Z

3

First of all: question why you are issuing the query select count(0) from xxxxx where isactive = 'Y' in the first place. Nine out of ten times it is a lazy way to check for existence of a record. If that's the case with you, just replace it with a query that select 1 row (rownum = 1 and a first_rows hint).

The number of rows you mention are nothing to be worried about. If your application doesn't perform well when number of rows grows, then your system is not designed to scale. I'd investigate all queries that take too long using a SQL*Trace or ASH and fix it.

By the way: nothing you mentioned justifies the term legacy, IMHO.

Regards,
Rob.

answered Apr 18, 2012 at 11:45

Rob van Wijk

17.7k5 gold badges41 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bob Jarvis - Слава Україні · Accepted Answer · 2012-04-18 12:08:31Z

1

Just a few observations:

I'm guessing that the "isActive" column can have two values - 'Y' and 'N' (or perhaps 'Y', 'N', and NULL - although why in the name of Fred there wouldn't be a NOT NULL constraint on such a column escapes me). If this is the case an index on this column would have very poor selectivity and you might be better off without it. Try dropping the index and re-running your query.
@RobVanWijk's comment about use of SELECT COUNT(*) is excellent. ONLY ask for a row count if you really need to have the count; if you don't need the count, I've found it's faster to do a direct probe (SELECT whatever FROM wherever WHERE somefield = somevalue) with an apprpriate exception handler than it is to do a SELECT COUNT(*). In the case you cited, I think it would be better to do something like

BEGIN

  SELECT IS_ACTIVE
    INTO strIsActive
    FROM MY_TABLE
    WHERE IS_ACTIVE = 'Y';

  bActive_records_found := TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    bActive_records_found := FALSE;
  WHEN TOO_MANY_ROWS THEN
    bActive_records_found := TRUE;
END;

As to partitioning - partitioning can be effective at reducing query times IF the field on which the table is partitioned is used in all queries. For example, if a table is partitioned on the TRANSACTION_DATE variable, then for the partitioning to make a difference all queries against this table would have to have a TRANSACTION_DATE test in the WHERE clause. Otherwise the database will have to search each partition to satisfy the query, so I doubt any improvements would be noted.

Share and enjoy.

edited Apr 18, 2012 at 12:08

answered Apr 18, 2012 at 12:01

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

8 Comments

Ravi Over a year ago

we are using the above query to find out how many records are there in the table which satisfies this criteria. this isActive column will have two values only 'Y' or 'N'.

Bob Jarvis - Слава Україні Over a year ago

In that case you might try changing the index to contain the IsActive column plus the primary key of the table.

Ravi Over a year ago

index is there on isActive column.

Bob Jarvis - Слава Україні Over a year ago

What columns are on that particular index?

Shannon Severance Over a year ago

@Bob Jarvis: How would adding an additional column help speed up the query?

|

Collectives™ on Stack Overflow

ORACLE db performance tuning

2 Answers 2

Comments

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related