OraFAQ Forum: Performance Tuning » Balanced Decision on Index Creation

Home » RDBMS Server » Performance Tuning » Balanced Decision on Index Creation

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Balanced Decision on Index Creation [message #164933]

Mon, 27 March 2006 12:44

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hi

I have a table with 9 column, right now there is only 1 Unique index on Primary Key Column, Daily inserts around 50000 rows in this table through Procedure from Front End, Out of these 9 column 4 columns are frequently used in Queries and select statements, Now if i create indexes
will insertion get slow and performance of Queries will be better or vice versa..
How can i balance myself and decide whether to create index or not,If i create insertion will be slow, if i don't Select and queries are slow, How to take balanced decision.

Thanks

[Updated on: Mon, 27 March 2006 12:52]

Report message to a moderator

Re: Balanced Decision on Index Creation [message #164935 is a reply to message #164933]

Mon, 27 March 2006 12:58

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> Daily inserts around 50000 rows in this table throuhg Procedure from Front End
What kind of 'front end' will insert so much of data everyday?
As you said, presence of index will slow the inserts and are good to selects. But to what extend? Is it really "slow"? It depends on what you do and your data.
If this is a scheduled job, you can
disable/drop indexes before load and rebuild them again after load and again collect statistics. Ofcourse, this process will take longer time and partial downtime is required.
If 50000 records are inserted across the day/business hours, you cannot afford to do the above. You have to live with it~. Just collect the statisitcs frequently.
I would first try it in a test environment. Measure the performance. See how much 'slower' your inserts will happen.

Report message to a moderator

Re: Balanced Decision on Index Creation [message #164936 is a reply to message #164935]

Mon, 27 March 2006 13:19

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hello

Thanks Mahesh Sir

Well it is a C# front end, using Oracle Stored Procedure, As the figure 50000 i said is maximum per day, it gets' client details and region and loads data in this table, It is not a Batch job,
Performance queries are bit slow takes time, and insertion also takes time as per developer, so first look i thouhgt indexes missing.

If i understand you correctly

"Just collect the statisitcs frequently.
I would first try it in a test environment. Measure the performance. See how much 'slower' your inserts will happen.
"

You are suggesting to create the index and try to see the behaviour of Queries and insertion !.

Thanks

Report message to a moderator

Re: Balanced Decision on Index Creation [message #164937 is a reply to message #164936]

Mon, 27 March 2006 13:28

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Almost yes.
I would try in test enviornment. There are a lot of factors here.
what i am saying is, Indexes will slow down the insert operation.
But how bad? If it is in the acceptable limit, let it be so.
ELse we can look into further options. So please try it in a test area, measure everything and come to a conclusion.

Report message to a moderator

Re: Balanced Decision on Index Creation [message #164947 is a reply to message #164937]

Mon, 27 March 2006 14:29

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Mahesh Sir

Please have a look at this query,
Even though all the column in where clause is having indexes this statement is doing FTS,what is wrong

SELECT DISTINCT WRK_TYPE_CD FROM WRK_QU WHERE SRC_NODE_NM = :B2
AND INPT_QU_STS_CD= 'd' AND WRK_TYPE_CD <> 'U' AND FILE_RCV_TS <
= :B1

PLAN

Operation                      | PHV/Object Name     |  Rows | Bytes|   Cost |
--------------------------------------------------------------------------------
|SELECT STATEMENT                |----- 2112212973 ----|       |      |   1691 |
|SORT UNIQUE                     |                     |     1 |   21 |   1691 |
| TABLE ACCESS FULL              |WRK_QU               |    23K|  487K|   1635 |

The DBA said "22 million physcial reads in one hour"

What can be done to avoid FTS so that it improves.

Thanks

Report message to a moderator

Re: Balanced Decision on Index Creation [message #164961 is a reply to message #164947]

Mon, 27 March 2006 18:06

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

What is the count of records in the table?
how many distinct values in these columns?
How much you are querying out?
The high disk reads may be becuase of HWM. Is this table frequently inserted/deleted with Huge volumes of data?
And are the statistics of table/indexes updated?
Please the forum for these keywords
HWM,Histograms, statistics.
One such.
http://www.orafaq.com/forum/m/158591/42800/?srch=HWM#msg_158591

Read the sticky and collect the stats as said by Mr.SMartin.
http://www.orafaq.com/forum/t/51267/42800/

Report message to a moderator

Re: Balanced Decision on Index Creation [message #165141 is a reply to message #164961]

Tue, 28 March 2006 08:28

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hello Mahesh Sir

The answers are

Count :- 450000
Distinct :- 800
This Query will get maximum of 5 rows
How to check for HWM or reduce Disk/Physical Reads.
Stats are updated daily at 5 A.M.

Any thing i can do on this.

Thanks

Report message to a moderator

Re: Balanced Decision on Index Creation [message #165147 is a reply to message #165141]

Tue, 28 March 2006 08:38

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

I suspect this is HWM.
if you have a test environemnt try this.
Alter table table_name move tablespace same_tablespace_as_it_was_before;
rebuild the constraints\indexes.
Collect stats ( with histograms).
Try your query again.

[Updated on: Tue, 28 March 2006 08:41]

Report message to a moderator

Re: Balanced Decision on Index Creation [message #165163 is a reply to message #165147]

Tue, 28 March 2006 11:25

faiz_hyd
Messages: 294
Registered: February 2006

Senior Member

Hello Mahesh Sir

One more thing i wanted to add on to it is from this deletes are also happening at regular interval of time from the procedure, let's say around 10000-20k rows per day , at a time maximum of 450000 rows will be there.
If i do alter table movetable space does index goes, so how i have to keep track of it, Does altering table will help
solving HWM.

I added Hints so plan got changed, it is doing Index Scan and avoiding FTS also.

SELECT /*+ Index(a,XIE_WRKQU_FILRCVTS)*/  DISTINCT  WRK_TYPE_CD,SRC_NODE_NM ,INPT_QU_STS_CD 
 FROM WRK_QU a WHERE FILE_RCV_TS <=to_date('10-mar-2006')  AND WRK_TYPE_CD <> 'U' 
 and SRC_NODE_NM = 'NEO1W014'
AND INPT_QU_STS_CD= 'd'



Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		36  	 	245284  	 	      	             	 
  SORT UNIQUE		36  	756  	245284  	 	      	             	 
    TABLE ACCESS BY INDEX ROWID	R1APP.WRK_QU	19 K	395 K	245237  	 	      	             	 
      INDEX RANGE SCAN	R1APP.XIE_WRKQU	_FILRCVTS	396 K	 	1056

Thanks

[Updated on: Tue, 28 March 2006 11:37]

Report message to a moderator

Re: Balanced Decision on Index Creation [message #165164 is a reply to message #165163]

Tue, 28 March 2006 11:57

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

The concept is,
When you frequently delete data, data is deleted. But their physical location ( or storage) is not released back. Say you deleted all the data in table. But when you query Oracle will physically check every block for data , will come back and say, NO data found. The HWM is reset only when you truncate or move or rebuild the table. Since you can afford to truncate every time
Your options are to this frequently ( and rebuild indexes,constraints and collect statistics)
To delete data efficiently as discussed here
http://www.orafaq.com/forum/m/130643/0/?srch=HWM#msg_130643
http://www.orafaq.com/forum/t/51172/0/
Does HINTS work better for you? May be or may not be.
I would use hints as last resort.

scott@9i > select count(*) from emp;

  COUNT(*)
----------
    360448


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=261 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=360448)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2706  consistent gets
          0  physical reads
          0  redo size
        493  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@9i > delete from emp;

360448 rows deleted.


Execution Plan
----------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=261 Card=360448 Byte
          s=2883584)

   1    0   DELETE OF 'EMP'
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=360448 Bytes
          =2883584)





Statistics
----------------------------------------------------------
        535  recursive calls
    1473850  db block gets
       2972  consistent gets
          0  physical reads
  175917720  redo size
       1014  bytes sent via SQL*Net to client
       1028  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
     360448  rows processed

scott@9i > commit;

Commit complete.

scott@9i >  select count(*) from emp;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=261 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=261 Card=360448)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2706  consistent gets
          0  physical reads
          0  redo size
        490  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

scott@9i > alter table emp move tablespace users;

Table altered.

scott@9i >  select count(*) from emp;

  COUNT(*)
----------
         0


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'EMP'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        490  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Report message to a moderator