Forum Stats

  • 3,876,212 Users
  • 2,267,082 Discussions
  • 7,912,473 Comments

Discussions

Categories

Select Query performance

13

Answers

  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 3, 2018 4:15AM

    Point# 3

    conversation_date (gl_daily_rates)  and its constraints.

    pastedImage_0.png

    RA_CUST_TRX_LINE_GL_DIST_ALL and its constraints.

    pastedImage_1.png

    Please let me know if anything missing from my side.

    · Share on TwitterShare on Facebook
  • Dom Brooks
    Dom Brooks Member Posts: 5,560 Silver Crown
    edited Oct 3, 2018 4:39AM

    So, the GLCC join is quite selective as well so perhaps we need to bring that earlier in the query filtering so that driving source is the date selections on RCTD + the join to GLCC for company/segment?

    · Share on TwitterShare on Facebook
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 3, 2018 5:29AM

    Yes for company segments

    · Share on TwitterShare on Facebook
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 3, 2018 3:01PM

    Hi ,

    Could you please help me?

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    edited Oct 3, 2018 4:22PM 
    Rajesh123 wrote:Hi ,Could you please help me?

    You haven't answered any of the questions in point 1 of my previous post.

    I've already said that the first important point to address seems to be the need to make a critical index access access in the original plan more efficient - point 2 suggests re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away.  In the interim you've been trying to produce a completely different plan and have apparently been ignoring my primary suggestion - if you want help from me you have to answer the questions I ask and supply me with the specific bits of information I ask for.

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 4, 2018 3:58AM

    1# first important point to address seems to be the need to make a critical index access in the original plan more efficient

    Could you please explain little bit more, i am completely new to this.

    2# re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go away

    Could you please explain little bit more, i am completely new to this.

    Thank you

    · Share on TwitterShare on Facebook
  • Jonathan Lewis
    Jonathan Lewis Member Posts: 10,117 Blue Diamond
    edited Oct 4, 2018 4:35AM 
    Rajesh123 wrote:1# first important point to address seems to be the need to make a critical index access in the original plan more efficient Could you please explain little bit more, i am completely new to this.2# re-ordering the column (so you might want to test with an invisible additional index and optimizer_use_invisible_indexes set to true), and we need to work out why that internal_function() is appearing and make it go awayCould you please explain little bit more, i am completely new to this.Thank you

    1# - re-read the comments Dom Brooks and I made in the early stages of this thread.

    2# - might be irrelevant, misleading, and a total waste of time, so I won't do it until you've answered the questions I reminded you about in my previous posting.  It's possible that the answers you give (especially the plan) may mean that the suggestion is irrelevant, on the other hand they may make it much easier to give you an explanation you understand of what you need to do and how to do it.

    Regards

    Jonathan Lewis

    · Share on TwitterShare on Facebook
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 4, 2018 7:26AM 
    Dom Brooks wrote:So, the GLCC join is quite selective as well so perhaps we need to bring that earlier in the query filtering so that driving source is the date selections on RCTD + the join to GLCC for company/segment?

    Hi Dom,

    Could you please explain little more ? in technical level

    · Share on TwitterShare on Facebook
  • Rajesh123
    Rajesh123 Member Posts: 1,506
    edited Oct 4, 2018 8:36AM

    I did rowsource plan using with GATHER PLAN hint in Select statement

    SELECT *

    FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

    PLAN_TABLE_OUTPUT

    SQL_ID  103z7pvxjhwgn, child number 0

    begin dbms_output.get_line(line => :line, status => :status); end;

    NOTE: cannot fetch plan for SQL_ID: 103z7pvxjhwgn, CHILD_NUMBER: 0

          Please verify value of SQL_ID and CHILD_NUMBER;

          It could also be that the plan is no longer in cursor cache (check v$sql_plan)

    · Share on TwitterShare on Facebook
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Oct 4, 2018 9:30AM 
    Rajesh123 wrote:I did rowsource plan using with GATHER PLAN hint in Select statement SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));PLAN_TABLE_OUTPUTSQL_ID 103z7pvxjhwgn, child number 0begin dbms_output.get_line(line => :line, status => :status); end;NOTE: cannot fetch plan for SQL_ID: 103z7pvxjhwgn, CHILD_NUMBER: 0 Please verify value of SQL_ID and CHILD_NUMBER;  It could also be that the plan is no longer in cursor cache (check v$sql_plan)

    Did you read the output you copied and pasted here? You tried to get the row source execution statistics for the statement:

    begin dbms_output.get_line(line => :line, status => :status); end;

    That is not the statement you care about. A google would have shown you that this statement is due to your client program trying to fetch data from the dbms_output buffer and can be turned off with

    set serverout off


    I suggest you turn off serveroutput, run the statement and run the dbms_xplan.display_cursor query again. Always take a moment to read over what you are copying and pasting first.

    Rajesh123
    · Share on TwitterShare on Facebook
This discussion has been closed.