Insert problem

[bensmail]

I h've following problem:

I run a insert ....select.... in my DB and it take forever.
the execution of the select statement is immediate, but the insert 2 hours.
This insert statement is run every day and it took 13 minutes for 3 days while a month.
I don't make changes in my database or my server and the number of line is the same.


Bensmail

[marist89]

What does the explain plan say?

What is your insert waiting on?

[bensmail]

I reformulate my problem:
it's very strange: When i run this query in RULE optimizer Mode it's run successfully, but not in choose mode.
All other query's work well in choose mode. And i can't run only this query in rule optimizer mode.

OS:Solaris 8
Oracle:8.1.7

Please help.

[bensmail]

Explain Plan given in OEM:

INSERT STATEMENT
HASH JOIN
NESTED LOOPS
REMOTE
REMOTE

[nagarjuna]

Originally posted by bensmail
I h've following problem:

I run a insert ....select.... in my DB and it take forever.
the execution of the select statement is immediate, but the insert 2 hours.
This insert statement is run every day and it took 13 minutes for 3 days while a month.
I don't make changes in my database or my server and the number of line is the same.


Bensmail

Insert WAS working fine, but not now.. hummm.. Check for indexes.. try rebuilding them. And check for storage clauses of the table that is getting inserted. check for number of extents.

[bensmail]

The target table is empty and analyzed.
There is no indexes and no constraints in this table.


Thank's for help

[nagarjuna]

Originally posted by bensmail
The target table is empty and analyzed.
There is no indexes and no constraints in this table.

Thank's for help

Extent sizes?? and expected size of table after insert??? If possible post the whole query.

[bensmail]

The query is:
INSERT INTO BASE_SAS.T_SAS_GL_JE_LINES (JE_HEADER_ID, JE_LINE_NUM, LAST_UPDATE_DATE, LAST_UPDATED_BY, SET_OF_BOOKS_ID, CODE_COMBINATION_ID, PERIOD_NAME, EFFECTIVE_DATE, STATUS, CREATION_DATE, CREATED_BY, LAST_UPDATE_LOGIN, ENTERED_DR, ENTERED_CR, ACCOUNTED_DR, ACCOUNTED_CR, DESCRIPTION, DESCRIPTION0, DESCRIPTION1, DESCRIPTION2, REFERENCE_1, REFERENCE_2, REFERENCE_3, REFERENCE_4, REFERENCE_5, ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18, ATTRIBUTE19, ATTRIBUTE20, CONTEXT, CONTEXT2, INVOICE_DATE, TAX_CODE, INVOICE_IDENTIFIER, INVOICE_AMOUNT, NO1, STAT_AMOUNT, IGNORE_RATE_FLAG, CONTEXT3, USSGL_TRANSACTION_CODE, SUBLEDGER_DOC_SEQUENCE_ID, CONTEXT4, SUBLEDGER_DOC_SEQUENCE_VALUE, REFERENCE_6, REFERENCE_7, REFERENCE_8, REFERENCE_9, REFERENCE_10, GLOBAL_ATTRIBUTE_CATEGORY, GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3, GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6, GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9, GLOBAL_ATTRIBUTE10, JGZZ_RECON_STATUS, JGZZ_RECON_DATE, JGZZ_RECON_ID, JGZZ_RECON_REF, JGZZ_RECON_CONTEXT, TAXABLE_LINE_FLAG, TAX_TYPE_CODE, TAX_CODE_ID, TAX_ROUNDING_RULE_CODE, AMOUNT_INCLUDES_TAX_FLAG, TAX_DOCUMENT_IDENTIFIER, TAX_DOCUMENT_DATE, TAX_CUSTOMER_NAME, TAX_CUSTOMER_REFERENCE, TAX_REGISTRATION_NUMBER, TAX_LINE_FLAG, TAX_GROUP_ID, LINE_TYPE_CODE, ID_CHARGEMENT, LIGNE_VALIDE, DATE_INSERT) SELECT G_T1.JE_HEADER_ID, G_T1.JE_LINE_NUM, G_T1.LAST_UPDATE_DATE, G_T1.LAST_UPDATED_BY, G_T1.SET_OF_BOOKS_ID, G_T1.CODE_COMBINATION_ID, G_T1.PERIOD_NAME, G_T1.EFFECTIVE_DATE, G_T1.STATUS, G_T1.CREATION_DATE, G_T1.CREATED_BY, G_T1.LAST_UPDATE_LOGIN, G_T1.ENTERED_DR, G_T1.ENTERED_CR, G_T1.ACCOUNTED_DR, G_T1.ACCOUNTED_CR, G_T1.DESCRIPTION, SUBSTR(G_T1.DESCRIPTION,34 ) , SUBSTR(G_T1.DESCRIPTION,34 ,1 ) , SUBSTR(G_T1.DESCRIPTION,1 ,10 ) , G_T1.REFERENCE_1, G_T1.REFERENCE_2, G_T1.REFERENCE_3, G_T1.REFERENCE_4, G_T1.REFERENCE_5, G_T1.ATTRIBUTE1, G_T1.ATTRIBUTE2, G_T1.ATTRIBUTE3, G_T1.ATTRIBUTE4, G_T1.ATTRIBUTE5, G_T1.ATTRIBUTE6, G_T1.ATTRIBUTE7, G_T1.ATTRIBUTE8, G_T1.ATTRIBUTE9, G_T1.ATTRIBUTE10, G_T1.ATTRIBUTE11, G_T1.ATTRIBUTE12, G_T1.ATTRIBUTE13, G_T1.ATTRIBUTE14, G_T1.ATTRIBUTE15, G_T1.ATTRIBUTE16, G_T1.ATTRIBUTE17, G_T1.ATTRIBUTE18, G_T1.ATTRIBUTE19, G_T1.ATTRIBUTE20, G_T1.CONTEXT, G_T1.CONTEXT2, G_T1.INVOICE_DATE, G_T1.TAX_CODE, G_T1.INVOICE_IDENTIFIER, G_T1.INVOICE_AMOUNT, G_T1.NO1, G_T1.STAT_AMOUNT, G_T1.IGNORE_RATE_FLAG, G_T1.CONTEXT3, G_T1.USSGL_TRANSACTION_CODE, G_T1.SUBLEDGER_DOC_SEQUENCE_ID, G_T1.CONTEXT4, G_T1.SUBLEDGER_DOC_SEQUENCE_VALUE, G_T1.REFERENCE_6, G_T1.REFERENCE_7, G_T1.REFERENCE_8, G_T1.REFERENCE_9, G_T1.REFERENCE_10, G_T1.GLOBAL_ATTRIBUTE_CATEGORY, G_T1.GLOBAL_ATTRIBUTE1, G_T1.GLOBAL_ATTRIBUTE2, G_T1.GLOBAL_ATTRIBUTE3, G_T1.GLOBAL_ATTRIBUTE4, G_T1.GLOBAL_ATTRIBUTE5, G_T1.GLOBAL_ATTRIBUTE6, G_T1.GLOBAL_ATTRIBUTE7, G_T1.GLOBAL_ATTRIBUTE8, G_T1.GLOBAL_ATTRIBUTE9, G_T1.GLOBAL_ATTRIBUTE10, G_T1.JGZZ_RECON_STATUS, G_T1.JGZZ_RECON_DATE, G_T1.JGZZ_RECON_ID, G_T1.JGZZ_RECON_REF, G_T1.JGZZ_RECON_CONTEXT, G_T1.TAXABLE_LINE_FLAG, G_T1.TAX_TYPE_CODE, G_T1.TAX_CODE_ID, G_T1.TAX_ROUNDING_RULE_CODE, G_T1.AMOUNT_INCLUDES_TAX_FLAG, G_T1.TAX_DOCUMENT_IDENTIFIER, G_T1.TAX_DOCUMENT_DATE, G_T1.TAX_CUSTOMER_NAME, G_T1.TAX_CUSTOMER_REFERENCE, G_T1.TAX_REGISTRATION_NUMBER, G_T1.TAX_LINE_FLAG, G_T1.TAX_GROUP_ID, G_T1.LINE_TYPE_CODE, :1 , 'OUI' , :2 FROM GL.GL_JE_LINES@INFOCENTRE_GL_LK G_T1 , GL.GL_JE_HEADERS@INFOCENTRE_GL_LK G_T2 , GL.GL_JE_CATEGORIES@INFOCENTRE_GL_LK G_T3 WHERE G_T2.JE_HEADER_ID = G_T1.JE_HEADER_ID AND G_T3.JE_CATEGORY_NAME = G_T2.JE_CATEGORY AND G_T3.USER_JE_CATEGORY_NAME = 'ON_ENGAGEMENT' AND G_T2.ACTUAL_FLAG = 'E'


1. Extend size:100M
other extents: 256K
db_block_size=8192
Nb extents after load: I'will post it soon

[adewri]

looks more like markings made on cave walls by a neanderthal.

[marist89]

You are going over a database link. I would look at where the join is happening, locally or remotely. Also, two-phase commit may be slowing some operations down. Check http://download-west.oracle.com/docs...a76960/toc.htm for details.