This discussion is archived
1 3 4 5 6 7 Previous Next 98 Replies Latest reply: Dec 14, 2009 11:01 PM by 695836 Go to original post RSS
  • 75. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    First of all I would like to thank you sincerely

    THANK YOU!!!!!

    I have learned so many things which I never had an idea before

    I couldnt attend any oracle performance related seminars due to various reasons and I always regret but to be frank " this has been my greatest learning experience in performance related subject"


    Coming back to the query" it runs five times faster." Let me check on Monday before closing the thread..

    Thank u MR.Tuner!!!!!!!!;-)


    Hope to work with you some time again.,,


    So many hidden facts came to limelight in this thread...especially to me....


    I would like to invite the fellow readers to post on this experiene


    Thank you JOSE!!!!!!!!

    Fellow readers can comment on this thread
  • 76. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    Maran,

    It was a pleasure to give you some hints for tuning. So, it is never "too late" to learn about tuning. For this you need quite a lot of background information about CBO and database internals. There are many excellent sources of knowledge in blogs either from my colleagues from OakTable or Oracle ACEs and many others.

    Kind regards,

    Joze Senegacnik
    Member of OakTable
    Oracle ACE
    Co-author of forthcoming book: "Expert Oracle Practices"
    http://www.dbprof.com
    http://jsenegacnik.blogspot.com
  • 77. Re: Is it possible to tune this query.....
    664883 Newbie
    Currently Being Moderated
    Hey man, verify parameters optimizer_index_cost_adj and optimizer_index_caching, update for:

    optimizer_index_cost_adj =20
    optimizer_index_caching = 65
  • 78. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Brilliant stuff... Thank you

    warm regards
    Maran
  • 79. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    jose
    1 last question


    How can we decide which plan is better without executing the sqls in production ?


    It's diffcult to get production acesss?

    Is there any hints in the plan provided so far.....

    This is a BONUS question:-)


    I am sending from mobile so apologies if anything is wrong here

    Maran

    Edited by: Maran Viswarayar on Nov 20, 2009 8:18 PM

    Edited by: Maran Viswarayar on Nov 20, 2009 8:20 PM
  • 80. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    optimizer_index_cost_adj =20
    optimizer_index_caching = 65
    Why not:

    optimizer_index_cost_adj =25
    optimizer_index_caching = 70

    Just joking! Can be any value until you really determine the right one what is not so easy.

    The parameter optimizer_index_caching is considered by CBO for IN list and NESTED LOOP operations and should be set. The optimizer_index_cost_adj could be very dangerous unless you really know what you are doing, especially when system statistics is used. It was introduced in 8i, but in later releases one should use system statistics to tell CBO what is the timing difference between single block and multiple block I/O. Jonathan Lewis has written about this on his site.

    What I really hate is that someone says: set this parameter to a certain value without considering what kind of system is that and what is even more important: without considering what are the consequences of such setting. It is like taking a wrong medicine.

    My personal opinion regarding optimizer_index_cost_adj is that one should leave it at 100 (default) and if you really understand the mechanism behind then you may experiment with it. Personally I would use it only at statement level by using OPT_PARAM hint if this would be really necessary. This way you don't make a system wide change.
  • 81. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    How can we decide which plan is better without executing the sqls in production ?
    I have no doubt about the plan without function. But, if you want to be sure, test just by running the SQL also in production before you make definite change. Is there a big difference among the test database and the production? What about indexes, if you have added some?

    There should be no hints in the SQL, remove the gather_plan_statistics hint.
  • 82. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Created a few indexes and function based index on the SUBSTR Function... and forced few indexe scans but it also switches to INDEX range scan from INDEX UNIQUE SCAN(PK_PR_CALENDAR) which is happenig now...but i couldnt a force a index scan on the PR_RECORD_DTL table which has around 80K records...but the A-time is only.28 so i just left it....

    Edited by: Maran Viswarayar on Nov 20, 2009 10:04 PM
  • 83. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    Ok, drop everything (don't forget to document what you have dropped to be able to re-create it if required) what was added in order to have the same environment as in production, and then run test again and post the result and we can check what we can do.

    Right now I don't have time to study your last response but will come back later.
  • 84. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Its all documented...i test in QA then UAT but not able to do it in production..due to access issues will do it by Monday..Long time..:)

    Thank you and have a nice weekend
  • 85. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Hi Joze,

    The query manager to run atleast 3-5 times faster..We will be moving to production by tomorrow after the approvals...will let you know by tomorrow..Did added some Function based indexes the plan looked much better but i couldnt force aa Index on the a the table(80,000 rows) under the view...I am not asking anything here but just to inform you....

    BTB..when will be book Available....looking forward....

    Edited by: Maran Viswarayar on Nov 23, 2009 9:52 PM
  • 86. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    Looking forward to see the results.

    Regarding adding indexes - be aware that each index introduces additional load due to its maintenance and slows down the insertion of new records and also maintenance but could be very beneficial in speeding up queries. So you have to decide....Tuning is almost always a trade-off process.

    The OakTable book will be published in January 2010 - see the below link to Apress.

    Regards, Joze

    Co-author of forthcoming OakTable book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680
  • 87. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    Thank you...i was reading the author names..pretty familiar names...JL,Hooper,pete,Niall, Randolf......

    Edited by: Maran Viswarayar on Nov 23, 2009 10:03 PM
  • 88. Re: Is it possible to tune this query.....
    Maran Viswarayar Pro
    Currently Being Moderated
    This is the plan with new indexes
    Plan hash value: 1407520959
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                  |                            |     2 |   482 |   452   (4)| 00:00:06 |
    |   1 |  TEMP TABLE TRANSFORMATION                        |                            |       |       |            |          |
    |   2 |   LOAD AS SELECT                                  |                            |       |       |            |          |
    |   3 |    HASH UNIQUE                                    |                            |     1 |    15 |     8  (13)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID                   | PR_CALENDAR                |     1 |    15 |     3   (0)| 00:0
    |*  5 |      INDEX RANGE SCAN                             | PR_CALENDAR_IDX4           |     1 |       |     2   (0)| 00:00:01 
    |*  6 |       TABLE ACCESS BY INDEX ROWID                 | PR_CALENDAR                |     1 |    18 |     2   (0)| 00:
    |*  7 |        INDEX RANGE SCAN                           | PR_CALENDAR_FIDX1          |     1 |       |     1   (0)| 00:00:
    |*  8 |        TABLE ACCESS BY INDEX ROWID                | PR_CALENDAR                |     1 |    19 |     2   (0)| 00
    |*  9 |         INDEX RANGE SCAN                          | PR_CALENDAR_FIDX1          |     1 |       |     1   (0)| 00:00:01 |
    |* 10 |       TABLE ACCESS BY INDEX ROWID                 | PR_CALENDAR                |     1 |    18 |     2   (0)| 00:
    |* 11 |        INDEX RANGE SCAN                           | PR_CALENDAR_FIDX1          |     1 |       |     1   (0)| 00:00:
    |  12 |   SORT ORDER BY                                   |                            |     2 |   482 |   444   (4)| 00:00:06 |
    |  13 |    VIEW                                           |                            |     2 |   482 |   443   (4)| 00:00:06 |
    |  14 |     UNION-ALL                                     |                            |       |       |            |          |
    |  15 |      HASH UNIQUE                                  |                            |     1 |    80 |   431   (3)| 00:00:06 |
    |  16 |       COUNT                                       |                            |       |       |            |          |
    |  17 |        NESTED LOOPS OUTER                         |                            |     1 |    80 |   430   (3)| 00:00:06 |
    |  18 |         VIEW                                      |                            |     1 |    17 |     2   (0)| 00:00:01 |
    |  19 |          TABLE ACCESS FULL                        | SYS_TEMP_0FD9D662B_1EFC02F |     1 |    15 |     2   (0)| 00:00:01 
    |  20 |         VIEW                                      |                            |     1 |    63 |   428   (3)| 00:00:06 |
    |* 21 |          VIEW                                     |                            |     1 |    68 |   428   (3)| 00:00:06 |
    |  22 |           SORT UNIQUE                             |                            |     1 |   104 |   428   (3)| 00:00:06 |
    |  23 |            WINDOW SORT                            |                            |     1 |   104 |   428   (3)| 00:00:06 |
    |  24 |             NESTED LOOPS                          |                            |     1 |   104 |   426   (3)| 00:00:06 |
    |* 25 |              VIEW                                 |                            |     1 |    86 |   425   (3)| 00:00:06 |
    |  26 |               SORT UNIQUE                         |                            |     1 |   176 |   425   (3)| 00:00:06 |
    |* 27 |                FILTER                             |                            |       |       |            |          |
    |  28 |                 NESTED LOOPS                      |                            |     1 |   176 |   420   (2)| 00:00:06 |
    |  29 |                  NESTED LOOPS                     |                            |     2 |   302 |   418   (2)| 00:00:06 |
    |* 30 |                   HASH JOIN                       |                            | 31220 |  4268K|   415   (2)| 00:00:05 |
    |* 31 |                    HASH JOIN                      |                            |   163 | 16463 |    19  (11)| 00:00:01 |
    |  32 |                     TABLE ACCESS FULL             | PR_MAPPING                 |   422 |  5064 |     5   (0)| 00:00:01 |
    |* 33 |                     HASH JOIN                     |                            |   163 | 14507 |    13   (8)| 00:00:01 |
    |* 34 |                      HASH JOIN                    |                            |   110 |  6380 |     8  (13)| 00:00:01 |
    |  35 |                       NESTED LOOPS                |                            |     6 |    78 |     2   (0)| 00:00:01 |
    |* 36 |                        INDEX UNIQUE SCAN          | PR_REPORT_PK               |     1 |     3 |     0   (0)| 00:00:01 |
    |  37 |                        TABLE ACCESS BY INDEX ROWID| PR_REPORT_SECTION          |     6 |    60 |     2   (0)|
    |* 38 |                         INDEX RANGE SCAN          | PR_REPORT_SECTION_PK       |     6 |       |     1   (0)| 00:00:01 |
    |  39 |                       TABLE ACCESS FULL           | PR_REPORT_DATA             |  1180 | 53100 |     5   (0)| 00:00:01 
    |  40 |                      TABLE ACCESS FULL            | PR_MAPPING_DTL             |  1490 | 46190 |     5   (0)| 00:00:01 |
    |* 41 |                    TABLE ACCESS FULL              | PR_RECORD_DTL              | 84772 |  3228K|   396   (1)| 00:00:05 |
    |* 42 |                   INDEX UNIQUE SCAN               | PK_PR_CALENDAR             |     1 |    11 |     0   (0)| 00:00:01 |
    |  43 |                  TABLE ACCESS BY INDEX ROWID      | HRM_EMPLOYEE               |     1 |    25 |     1   (0)| 00:00:0
    |* 44 |                   INDEX UNIQUE SCAN               | HRM_EMPLOYEE_PK            |     1 |       |     0   (0)| 00:00:01 |
    |  45 |                 NESTED LOOPS                      |                            |     1 |    33 |     4   (0)| 00:00:01 |
    |* 46 |                  INDEX RANGE SCAN                 | IDX_PR_RECORD_DTL          |     1 |    19 |     3   (0)| 00:00:01 |
    |* 47 |                  TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR                |     1 |    14 |     1   (0)| 00:00:0
    |* 48 |                   INDEX RANGE SCAN                | PR_CALENDAR_FIDX1          |     1 |       |     0   (0)| 00:00:01 |
    |* 49 |              TABLE ACCESS BY INDEX ROWID          | PR_CALENDAR                |     1 |    18 |     1   (0)| 00:0
    |* 50 |               INDEX RANGE SCAN                    | PR_CALENDAR_FIDX1          |     1 |       |     0   (0)| 00:00:0
    |  51 |      HASH UNIQUE                                  |                            |     1 |    64 |    13  (31)| 00:00:01 |
    |* 52 |       HASH JOIN OUTER                             |                            |     1 |    64 |    12  (25)| 00:00:01 |
    |  53 |        VIEW                                       |                            |     1 |    17 |     2   (0)| 00:00:01 |
    |  54 |         TABLE ACCESS FULL                         | SYS_TEMP_0FD9D662B_1EFC02F |     1 |    15 |     2   (0)| 00:00:01 |
    |  55 |        VIEW                                       |                            |     1 |    47 |     9  (23)| 00:00:01 |
    |  56 |         HASH UNIQUE                               |                            |     1 |    70 |     9  (23)| 00:00:01 |
    |  57 |          WINDOW SORT                              |                            |     1 |    70 |     9  (23)| 00:00:01 |
    |* 58 |           TABLE ACCESS BY INDEX ROWID             | PR_CALENDAR                |     1 |    36 |     2   (0)| 00:00:
    |  59 |            NESTED LOOPS                           |                            |     1 |    70 |     7   (0)| 00:00:01 |
    |  60 |             TABLE ACCESS BY INDEX ROWID           | PR_EMP_SETUP_REG           |     2 |    68 |     3   (0)| 
    |* 61 |              INDEX RANGE SCAN                     | PK_PR_EMP_SETUP_REG        |     2 |       |     2   (0)| 00:00:
    |* 62 |             INDEX RANGE SCAN                      | PR_CALENDAR_TEST           |     1 |       |     1   (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------------------
    Without Indexes...the Seconds one looks much better to my knowledge...
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2029808645
    
    ----------------------------------------------------------------------------------------------------
    | Id  | Operation                                         | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
    ----------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                                  |                            |    20 |  4820 |       | 39256   (1)| 00:07:52 |
    |   1 |  TEMP TABLE TRANSFORMATION                        |                            |       |       |       |            |          |
    |   2 |   LOAD AS SELECT                                  |                            |       |       |       |            |          |
    |   3 |    HASH UNIQUE                                    |                            |     1 |    15 |       |     8  (13)| 00:00:01 |
    |   4 |     TABLE ACCESS BY INDEX ROWID                   | PR_CALENDAR                |     1 |    15 |       |     3   (
    |*  5 |      INDEX RANGE SCAN                             | PR_CALENDAR_IDX4           |     1 |       |       |     2   (0)| 0
    |   6 |       TABLE ACCESS BY INDEX ROWID                 | PR_CALENDAR                |     1 |    18 |       |     2   
    |*  7 |        INDEX UNIQUE SCAN                          | PK_PR_CALENDAR             |     1 |       |       |     1   (0)| 0
    |   8 |        TABLE ACCESS BY INDEX ROWID                | PR_CALENDAR                |     1 |    19 |       |     2  
    |*  9 |         INDEX UNIQUE SCAN                         | PK_PR_CALENDAR             |     1 |       |       |     1   (0)| 00:00:0
    |  10 |       TABLE ACCESS BY INDEX ROWID                 | PR_CALENDAR                |     1 |    18 |       |     2   
    |* 11 |        INDEX UNIQUE SCAN                          | PK_PR_CALENDAR             |     1 |       |       |     1   (0)| 0
    |  12 |   SORT ORDER BY                                   |                            |    20 |  4820 |       | 39248   (1)| 00:07:51 |
    |  13 |    VIEW                                           |                            |    20 |  4820 |       | 39247   (1)| 00:07:51 |
    |  14 |     UNION-ALL                                     |                            |       |       |       |            |          |
    |  15 |      HASH UNIQUE                                  |                            |    19 |  1520 |       | 39234   (1)| 00:07:51 |
    |  16 |       COUNT                                       |                            |       |       |       |            |          |
    |  17 |        NESTED LOOPS OUTER                         |                            |    19 |  1520 |       | 39233   (1)| 00:07:51 |
    |  18 |         VIEW                                      |                            |     1 |    17 |       |     2   (0)| 00:00:01 |
    |  19 |          TABLE ACCESS FULL                        | SYS_TEMP_0FD9D662D_1EFC02F |     1 |    15 |       |     2   (0)| 0
    |  20 |         VIEW                                      |                            |    19 |  1197 |       | 39231   (1)| 00:07:51 |
    |* 21 |          VIEW                                     |                            |   321 | 21828 |       | 39231   (1)| 00:07:51 |
    |  22 |           SORT UNIQUE                             |                            |   321 | 33384 |       | 39231   (1)| 00:07:51 |
    |  23 |            WINDOW SORT                            |                            |   321 | 33384 |       | 39231   (1)| 00:07:51 |
    |* 24 |             HASH JOIN                             |                            |   321 | 33384 |       | 39229   (1)| 00:07:51 |
    |* 25 |              TABLE ACCESS FULL                    | PR_CALENDAR                |     4 |    72 |       |     5   (0)| 00:0
    |* 26 |              VIEW                                 |                            | 12832 |  1077K|       | 39224   (1)| 00:07:51 |
    |  27 |               SORT UNIQUE                         |                            | 12832 |  2205K|  4680K| 39224   (1)| 00:07:51 |
    |* 28 |                FILTER                             |                            |       |       |       |            |          |
    |  29 |                 NESTED LOOPS                      |                            | 12834 |  2205K|       |   487   (2)| 00:00:06 |
    |* 30 |                  HASH JOIN                        |                            | 25305 |  4077K|       |   485   (2)| 00:00:06 |
    |  31 |                   TABLE ACCESS FULL               | HRM_EMPLOYEE               |  3539 | 88475 |       |    69   (0)| 00:00:0
    |* 32 |                   HASH JOIN                       |                            | 31220 |  4268K|       |   415   (2)| 00:00:05 |
    |* 33 |                    HASH JOIN                      |                            |   163 | 16463 |       |    19  (11)| 00:00:01 |
    |  34 |                     TABLE ACCESS FULL             | PR_MAPPING                 |   422 |  5064 |       |     5   (0)| 00:00:
    |* 35 |                     HASH JOIN                     |                            |   163 | 14507 |       |    13   (8)| 00:00:01 |
    |* 36 |                      HASH JOIN                    |                            |   110 |  6380 |       |     8  (13)| 00:00:01 |
    |  37 |                       NESTED LOOPS                |                            |     6 |    78 |       |     2   (0)| 00:00:01 |
    |* 38 |                        INDEX UNIQUE SCAN          | PR_REPORT_PK               |     1 |     3 |       |     0   (0)| 00:
    |  39 |                        TABLE ACCESS BY INDEX ROWID| PR_REPORT_SECTION          |     6 |    60 |       |    
    |* 40 |                         INDEX RANGE SCAN          | PR_REPORT_SECTION_PK       |     6 |       |       |     1   (0)| 00:
    |  41 |                       TABLE ACCESS FULL           | PR_REPORT_DATA             |  1180 | 53100 |       |     5   (0)| 0
    |  42 |                      TABLE ACCESS FULL            | PR_MAPPING_DTL             |  1490 | 46190 |       |     5   (0)| 00
    |* 43 |                    TABLE ACCESS FULL              | PR_RECORD_DTL              | 84772 |  3228K|       |   396   (1)| 00:00
    |* 44 |                  INDEX UNIQUE SCAN                | PK_PR_CALENDAR             |     1 |    11 |       |     0   (0)| 00:00:
    |  45 |                 NESTED LOOPS                      |                            |     1 |    33 |       |     4   (0)| 00:00:01 |
    |* 46 |                  INDEX RANGE SCAN                 | IDX_PR_RECORD_DTL          |     1 |    19 |       |     3   (0)| 00:0
    |* 47 |                  TABLE ACCESS BY INDEX ROWID      | PR_CALENDAR                |     1 |    14 |       |     1   (0)|
    |* 48 |                   INDEX UNIQUE SCAN               | PK_PR_CALENDAR             |     1 |       |       |     0   (0)| 00:00
    |  49 |      HASH UNIQUE                                  |                            |     1 |    64 |       |    13  (31)| 00:00:01 |
    |* 50 |       HASH JOIN OUTER                             |                            |     1 |    64 |       |    12  (25)| 00:00:01 |
    |  51 |        VIEW                                       |                            |     1 |    17 |       |     2   (0)| 00:00:01 |
    |  52 |         TABLE ACCESS FULL                         | SYS_TEMP_0FD9D662D_1EFC02F |     1 |    15 |       |     2   (0)| 00
    |  53 |        VIEW                                       |                            |     1 |    47 |       |     9  (23)| 00:00:01 |
    |  54 |         HASH UNIQUE                               |                            |     1 |    70 |       |     9  (23)| 00:00:01 |
    |  55 |          WINDOW SORT                              |                            |     1 |    70 |       |     9  (23)| 00:00:01 |
    |  56 |           TABLE ACCESS BY INDEX ROWID             | PR_EMP_SETUP_REG           |     1 |    34 |       |     2  
    |  57 |            NESTED LOOPS                           |                            |     1 |    70 |       |     7   (0)| 00:00:01 |
    |* 58 |             TABLE ACCESS FULL                     | PR_CALENDAR                |     1 |    36 |       |     5   (0)| 00:00
    |* 59 |             INDEX RANGE SCAN                      | PK_PR_EMP_SETUP_REG        |     1 |       |       |     1   (0)|
    ----------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    With the indexes the plan looks much better with indexes.....

    Indexes were a funtion based indexes



    JFI....
  • 89. Re: Is it possible to tune this query.....
    joc Oracle ACE Director
    Currently Being Moderated
    According to the estimated time to complete the plan without indexes is better and also has lower cost. But this can sometimes be quite a bad measure so I would suggest you to run both with gathering statistics and then you will know which is really better.

    By the way, a full table scan is not always evil, sometimes it is the cheapest and the most optimal access path.

    Regards, Joze

    Co-author of forthcoming OakTable book "Expert Oracle Practices"
    http://www.apress.com/book/view/9781430226680
1 3 4 5 6 7 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points