Forum Stats

  • 3,768,655 Users
  • 2,252,827 Discussions
  • 7,874,669 Comments

Discussions

Categories

query performance tuning

user11989619
user11989619 Member Posts: 34
edited Apr 10, 2013 4:32AM in General Database Discussions
Hi,

Need your help tuning the following select.

INSERT INTO samim_appo_accrual_cur
SELECT a.*, 2
FROM samim_appo_accrual_all a
WHERE a.trans_year IN (2013 - 1, 2013)
AND EXISTS
(SELECT b.derived_rcv_trasanction_id
FROM samim_appo_accrual_cur b
WHERE b.code_combination_id = 1158
AND a.derived_rcv_trasanction_id =
b.derived_rcv_trasanction_id)



Explain plan of the above select is as follows.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Hint=ALL_ROWS 1 871
COUNT STOPKEY
NESTED LOOPS SEMI 1 873 871
TABLE ACCESS BY INDEX ROWID samim_APPO_ACCRUAL_ALL 1 862 2
INDEX RANGE SCAN samim_APPO_ACCRUAL_ALL_IDX 1 2
TABLE ACCESS BY INDEX ROWID samim_APPO_ACCRUAL_CUR 1 11 869
INDEX RANGE SCAN samim_APPO_ACCRUAL_CUR_IDX 18 K 61


Indexes created on trans_year and code_combination_id columns. Query is taking around 6 hrs to complet.

Regards/Prasanth
Tagged:
· Share on TwitterShare on Facebook

Answers

  • Martin Preiss
    Martin Preiss Member Posts: 2,381 Gold Trophy
    two points:
    - you should use code tags to format the execution plan
    - if I interpret the plan correctly - and make the right guesses - then the CBO expects the index access on samim_APPO_ACCRUAL_ALL_IDX only to result in one row for the given predicates: so perhaps you have a problem with the statistics (perhaps with the column statistics for trans_year)
    · Share on TwitterShare on Facebook
  • Salman Qureshi
    Salman Qureshi Member Posts: 2,483
    Hi,
    Indexes created on trans_year and code_combination_id columns. Query is taking around 6 hrs to complet.
    Is query (on which insert is based) taking or complete INSERT is taking 6 hours? How much is the size (number of records and size of table) of tables involved in SELECT statement? and how much is the size increment of table after this insert?

    For your execution plan, it looks quite simple and apparently there isn't any issue with the plan.

    Salman
    · Share on TwitterShare on Facebook
  • user11989619
    user11989619 Member Posts: 34
    Thank you Martin for your reply.

    The same Insert have no problem in TEST instance. But, its is taking 6 hours in Production.
    · Share on TwitterShare on Facebook
  • user11989619
    user11989619 Member Posts: 34
    Yes Salman,

    what you said is right, i have not seen any issue with explain plan.

    But, why this SELECT is taking too much time in PRODUCTION only not in TEST instance.
    · Share on TwitterShare on Facebook
  • Salman Qureshi
    Salman Qureshi Member Posts: 2,483
    Hi,
    You did not answer my questions. You test might not have this much huge data which you have in production

    1. How much is the size (number of records and size of table) of tables involved in SELECT statement
    2. And how much is the size increment of table after this insert?

    Salman
    · Share on TwitterShare on Facebook
  • user11989619
    user11989619 Member Posts: 34
    Hi Salman,

    1. How much is the size (number of records and size of table) of tables involved in SELECT statement
    -- 300k total rows

    2. And how much is the size increment of table after this insert?
    -- only 27 rows getting inserted
    · Share on TwitterShare on Facebook
  • Nikolay Savvinov
    Nikolay Savvinov Member Posts: 1,860 Silver Trophy
    Hi,

    1) what is your 4-digit Oracle version and do you have the Diagnostic and Tuning Pack licenses?
    2) depending on your version and license, please provide one of below:
    - extended SQL ("event 10046") trace file with waits and binds enabled
    - SQL real-time monitor report (requires diagnostic/tuning pack licenses and at least 11g version)
    - dbms_xplan.display_cursor output (to make sure that A-time and A-rows columns are populated, run your statement after ALTER SESSION SET STATISTICS_LEVEL=ALL)

    Without sufficient diagnostic information it's all just guessing games: for example, we could be looking at the wrong plan (explain plan can produce a different plan from what is executed at run time), we don't know whether it's a slow SELECT or some concurrency on the DML side, we don't have any idea which of the plan operations is consuming more time etc. etc.

    Best regards,
    Nikolay
    · Share on TwitterShare on Facebook
  • user11989619
    user11989619 Member Posts: 34
    Hi Nikolay,

    We are on

    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    We don;t have the license for the Performance tuning.
    · Share on TwitterShare on Facebook
  • Nikolay Savvinov
    Nikolay Savvinov Member Posts: 1,860 Silver Trophy
    Hi,

    then trace the query or run it with STATISTICS_LEVEL = ALL and post dbms_xplan.display_cursor output with 'allstats last' format option.

    Best regards,
    Nikolay
    · Share on TwitterShare on Facebook
This discussion has been closed.