Hi,
I'm working on an Oracle Database 11g Release 11.1.0.6.0 - 64bit Production With the Real Application Clusters option.
The second of the top 5 timed event is the db file sequential read and the 88% of this class is consumed by an insert.
The table (t_event_data) is:
Nome Nullo Tipo
------------- -------- --------------
ID_DATA NOT NULL NUMBER
I_INOUT NOT NULL NUMBER
ID_LANG_INOUT NOT NULL NUMBER
DATE_WRITE NOT NULL DATE
VALUE VARCHAR2(1000)
it's detailed info are:
CREATED 26/02/2012 10:13:27
LAST_DDL_TIME 27/02/2012 11:38:30
OWNER DISPATCH
TABLE_NAME T_EVENT_DATA
TABLESPACE_NAME USERS
CLUSTER_NAME
IOT_NAME
STATUS VALID
PCT_FREE 10
PCT_USED
INI_TRANS 1
MAX_TRANS 255
INITIAL_EXTENT 81920
NEXT_EXTENT
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE
FREELISTS
FREELIST_GROUPS
LOGGING YES
BACKED_UP N
NUM_ROWS 162018017
BLOCKS 479029
EMPTY_BLOCKS 0
AVG_SPACE 0
CHAIN_CNT 0
AVG_ROW_LEN 36
AVG_SPACE_FREELIST_BLOCKS 0
NUM_FREELIST_BLOCKS 0
DEGREE 1
INSTANCES 1
CACHE N
TABLE_LOCK ENABLED
SAMPLE_SIZE 162018017
LAST_ANALYZED 29/02/2012 22:09:06
PARTITIONED NO
IOT_TYPE
OBJECT_ID_TYPE
TABLE_TYPE_OWNER
TABLE_TYPE
TEMPORARY N
SECONDARY N
NESTED NO
BUFFER_POOL DEFAULT
ROW_MOVEMENT DISABLED
GLOBAL_STATS YES
USER_STATS NO
DURATION
SKIP_CORRUPT DISABLED
MONITORING YES
CLUSTER_OWNER
DEPENDENCIES DISABLED
COMPRESSION DISABLED
COMPRESS_FOR
DROPPED NO
COMMENTS
The insert is simply:
INSERT INTO T_EVENT_DATA (ID_DATA, I_INOUT, ID_LANG_INOUT, DATE_WRITE, VALUE) VALUES (:B3 , :B2 , 1, SYSDATE, :B1 )
The pk is ID_DATA, I_INOUT, ID_LANG_INOUT and that's the only one index on that table.
I've also 2 foreign key on ID_DATA and on I_INOUT, ID_LANG_INOUT fields.
I issue almost 40mln of insert each day.
Is there any way to tune the insert/index/table/db
Thanks in advance,
Samuel