This discussion is archived
12 Replies Latest reply: Jan 4, 2013 12:29 PM by Hussein Sawwan-Oracle RSS

Partitioning GL_BALANCES

user646034 Newbie
Currently Being Moderated
Hi ,

Does partition improve insert query performance.

One of the insert which is accessing gl_balance and gl_code_combinations tables is taking time.

After partitioning the gl_balances tables. It still taking same time.

My question does partition improve insert query performance.

Please help,


Thanks
  • 1. Re: Partitioning GL_BALANCES
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    user646034 wrote:
    Hi ,

    Does partition improve insert query performance.

    One of the insert which is accessing gl_balance and gl_code_combinations tables is taking time.

    After partitioning the gl_balances tables. It still taking same time.

    My question does partition improve insert query performance.

    Please help,


    Thanks
    Partitioning may not necessarily improve performance.

    https://forums.oracle.com/forums/search.jspa?threadID=&q=Partitioning&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001
    https://forums.oracle.com/forums/search.jspa?threadID=&q=Partitioning+AND+gl_balances&objID=c3&dateRange=all&userID=&numResults=15&rankBy=10001

    Thanks,
    Hussein
  • 2. Re: Partitioning GL_BALANCES
    Kashif M Oracle ACE
    Currently Being Moderated
    What is the apps, DB and OS version ?
    when you started facing performance issue ? any recent change ?

    meanwhile also check following MOS notes:
    R11i GLTTRN Translation Performance Issue In INSERT INTO GL_BALANCES [ID 761898.1]
    GLTTRC GLTTRN Has Poor Performance [ID 1364252.1]

    thanks
  • 3. Re: Partitioning GL_BALANCES
    user646034 Newbie
    Currently Being Moderated
    HI ,

    Apps version - 11.5.10.2
    Db version 11.2.0.1
    OS version - IBM AIX 6.1

    We are trying to insert into a single table by joining gl_balances and gl_code_combinations.
    It is taking more than 1 hour to complete.
    I created a partition on column period_name of gl_balances but still no improvement.

    Thanks
  • 4. Re: Partitioning GL_BALANCES
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    We are trying to insert into a single table by joining gl_balances and gl_code_combinations.
    It is taking more than 1 hour to complete.
    I created a partition on column period_name of gl_balances but still no improvement.
    How big is the table? How many records are there?

    Do you have the statistics up to date?

    Have you tried to rebuild the indexes on the table and see if this helps?

    How do you insert data to the tables? Is it through an API or from the forms? Please enable trace and generate the TKPROF file.

    Thanks,
    Hussein
  • 5. Re: Partitioning GL_BALANCES
    user646034 Newbie
    Currently Being Moderated
    Hi ,

    I am running insert query from command prompt.

    Statistics are up to date.

    Will share the TKPROF report.

    Thanks
  • 6. Re: Partitioning GL_BALANCES
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    user646034 wrote:
    Hi ,

    I am running insert query from command prompt.

    Statistics are up to date.

    Will share the TKPROF report.

    Thanks
    Direct inserts are not supported and you should use an API instead.

    Thanks,
    Hussein
  • 7. Re: Partitioning GL_BALANCES
    user646034 Newbie
    Currently Being Moderated
    Hi ,

    Sorry for the late reply.

    Please find below the execution plan and trace of the materialized view.

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 1 0.01 0.03 0 0 0 0
    Execute 1 2634.25 5284.69 8624707 4929116 8025830 399289932
    Fetch 0 0.00 0.00 0 0 0 0
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 2 2634.26 5284.72 8624707 4929116 8025830 399289932

    Misses in library cache during parse: 1
    Optimizer mode: ALL_ROWS
    Parsing user id: 175 (APPS) (recursive depth: 1)

    Rows Row Source Operation
    ------- ---------------------------------------------------
    0 LOAD AS SELECT (cr=5225126 pr=8624707 pw=11684106 time=0 us)
    399289932 HASH JOIN (cr=4924053 pr=8624680 pw=3737918 time=3429039616 us cost=3604902 size=326196360 card=1812202)
    3023757 TABLE ACCESS FULL GL_CODE_COMBINATIONS (cr=63786 pr=42606 pw=0 time=1654976 us cost=49854 size=115552620 card=1283918)
    399289932 HASH JOIN (cr=4860267 pr=4844156 pw=0 time=1741350400 us cost=3443373 size=330215670 card=3669063)
    4200 HASH JOIN (cr=30 pr=0 pw=0 time=3053 us cost=32 size=120960 card=2240)
    6 TABLE ACCESS FULL GL_SETS_OF_BOOKS (cr=7 pr=0 pw=0 time=15 us cost=7 size=108 card=6)
    1120 TABLE ACCESS FULL GL_PERIODS (cr=23 pr=0 pw=0 time=497 us cost=24 size=40320 card=1120)
    399289932 TABLE ACCESS FULL GL_BALANCES (cr=4860237 pr=4844156 pw=0 time=649329536 us cost=3443340 size=11109398148 card=308594393)

    Please suugest so that it can help in improving the mv performance GL tables.

    Thanks
  • 8. Re: Partitioning GL_BALANCES
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    Is this the plan of the direct insert (which is unsupported)?

    Do you have any performance issues if you use an API or accessing this table from the application?

    Thanks,
    Hussein
  • 9. Re: Partitioning GL_BALANCES
    user646034 Newbie
    Currently Being Moderated
    Hi ,

    Jobs runs which insert the data into gl tables is fast. When we refresh the materialized view.

    It is very slow. MV is created on gl tables out of which only gl_balances has 40 crores record as per the execution plan which i posted earlier.

    After that i craeated a range partition of gl_balances and gl_code_optimizations still it is taking same time.

    The insert query which run during MV refresh is taking more than 1 hr.

    Thanks
  • 10. Re: Partitioning GL_BALANCES
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    user646034 wrote:
    Hi ,

    Jobs runs which insert the data into gl tables is fast. When we refresh the materialized view.

    It is very slow. MV is created on gl tables out of which only gl_balances has 40 crores record as per the execution plan which i posted earlier.

    After that i craeated a range partition of gl_balances and gl_code_optimizations still it is taking same time.

    The insert query which run during MV refresh is taking more than 1 hr.

    Thanks
    Is the referesh MV program a seeded program or a custom one? If it is seeded, please log a SR and upload the trace/tkprof to Oracle support.

    Thanks,
    Hussein
  • 11. Re: Partitioning GL_BALANCES
    user646034 Newbie
    Currently Being Moderated
    Hi ,

    I already SR with oracle. Oracle say it they does not support as it is a custom MV.

    Thanks
  • 12. Re: Partitioning GL_BALANCES
    Hussein Sawwan-Oracle Employee ACE
    Currently Being Moderated
    user646034 wrote:
    Hi ,

    I already SR with oracle. Oracle say it they does not support as it is a custom MV.

    Thanks
    If there are no performance issues with the seeded codes then there is nothing you need to do with the tables and you have to tune your MV code. Please make sure you use a supported API to insert/update the data rather than using a direct insert/update.

    Thanks,
    Hussein