Home » RDBMS Server » Performance Tuning » Facing performance issues in simple insert statement from Oracle 10g (10g)
Facing performance issues in simple insert statement from Oracle 10g [message #643711] Fri, 16 October 2015 00:21 Go to next message
toitdoctor
Messages: 6
Registered: September 2015
Location: USA
Junior Member
Hi All,


Oracle Version : Oracle Database 10g Enterprise Edition Release 10.2.0.4.0


We are facing performance issues in oracle insert statement. I have analyzed all the things for the table and insert statement.

The details of the scenario is given below


1.The gather stats job is running weekly on the table.

2.We load data in table on daily basis.The load type is truncate and then insert. The total no of rows for insert about 6 million.

3. We have checked the indexes/constraints on the table.

4. The output of ALL_TAB_STATISTICS is :



TABLE_NAME NUM_ROWS STALE_STATS
XYZ 6,756,996 YES


5. The output of ALL_TAB_MODIFICATION is :

TABLE_NAME INSERTS UPDATES DELETES TRUNCATED
XYZ 27,075,776 0 0 YES





now my questions are :

1. Should we need to gather the stats on the table daily basis?
2. We do truncate and then insert daily still we require to gather the stats on the table?
3. Change the gather stats job from weekly to daily can improve the performance ?


may i know any suggestions and thoughts for this kind of problem...


Thanks in advacne...
Re: Facing performance issues in simple insert statement from Oracle 10g [message #643713 is a reply to message #643711] Fri, 16 October 2015 00:30 Go to previous messageGo to next message
BlackSwan
Messages: 26196
Registered: January 2009
Location: SoCal
Senior Member
we need a few more details; like the actual INSERT statement itself
http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Facing performance issues in simple insert statement from Oracle 10g [message #643715 is a reply to message #643711] Fri, 16 October 2015 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 65848
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Gather statistics after each load.

Re: Facing performance issues in simple insert statement from Oracle 10g [message #643753 is a reply to message #643715] Fri, 16 October 2015 14:56 Go to previous message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:

3. We have checked the indexes/constraints on the table.

What does it mean, "checked"? Did you, by any chance, try to drop indexes before performing INSERT and see how it behaves?
Previous Topic: Any good Way to get dNFS IOPS?
Next Topic: Please help for Tuning sql Query.
Goto Forum:
  


Current Time: Fri Oct 19 12:56:30 CDT 2018