Insert problem
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Insert problem

  1. #1
    Join Date
    Mar 2002
    Posts
    301

    Insert problem

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What does the explain plan say?

    What is your insert waiting on?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Mar 2002
    Posts
    301
    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.

  4. #4
    Join Date
    Mar 2002
    Posts
    301
    Explain Plan given in OEM:

    INSERT STATEMENT
    HASH JOIN
    NESTED LOOPS
    REMOTE
    REMOTE

  5. #5
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405

    Re: Insert problem

    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.
    -nagarjuna

  6. #6
    Join Date
    Mar 2002
    Posts
    301
    The target table is empty and analyzed.
    There is no indexes and no constraints in this table.


    Thank's for help

  7. #7
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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.
    -nagarjuna

  8. #8
    Join Date
    Mar 2002
    Posts
    301
    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

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    looks more like markings made on cave walls by a neanderthal.
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width