This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Oct 3, 2010 10:20 PM by OraDBA02 Go to original post RSS
  • 15. Re: db_file_multiblock_read_count not in effect
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    OraDBA02 wrote:
    Hi Randolf,

    Many thanks for replying back.
    I again captured 10046 (level 12) trace and here is TKPROF and AUTOT outout:
    Well, I think here is a misunderstanding. I was not after the trace of a simple full table scan but I was talking about your join operation - that is what I tried to express previously: Your join might be spending time in different activities than only reading the data from the segments, that is why I would like you to trace your actual statement that includes the join operation.

    What you've shown now is that it takes some time to fetch 65 million rows to the client, but in this case the majority of the time was spent on transferring the data to the client rather than waiting for I/O.

    Ideally you should trace the join and try not to transfer all data to the client but wrap your query either inside another query that performs some aggregation like MAX(col) that doesn't allow the optimizer to take a short cut. You could also nest your query inside a anonymous PL/SQL block that fetches all rows on the server side in a FOR c in (query) LOOP null; END LOOP; to avoid the overhead of fetching all rows to the client, except for the case that in your application you really need to fetch all rows to the client of course.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    Co-author of the "OakTable Expert Oracle Practices" book:
    http://www.apress.com/book/view/1430226684
    http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684
  • 16. Re: db_file_multiblock_read_count not in effect
    CharlesHooper Expert
    Currently Being Moderated
    Rakesh jayappa wrote:
    Please correct me if i am wrong, If driving table must be small and driven table must conatin index on a column then optimzer will favor nested loop join.

    Let me know your answer

    Kind Regards,
    Rakesh
    Rakesh,

    Your statement might become lost in this thread, which is about a completely different topic. I believe that your statement is a little to broad-brush (too wide of a scope) to be completely true, and is likely to be incorrect most of the time, at least with Oracle Database 11.1.0.7. See the following blog article for a test case script that shows why:
    http://hoopercharles.wordpress.com/2010/10/01/first-table-is-550mb-second-table-is-26gb-nested-loops-or-full-table-scan/

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 17. Re: db_file_multiblock_read_count not in effect
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Rakesh jayappa wrote:

    Please correct me if i am wrong, If driving table must be small and driven table must conatin index on a column then optimzer will favor nested loop join.
    Rakesh,

    What about a table of currency codes (ca. 650 rows) with current exchange rates joined to a table of banking transactions (say 120M rows) by currency code. If you wanted to sum current value by currency you wouldn't want to do a nested looped index driven join from currency to transaction. A hash join sounds much more appropriate.

    Regards
    Jonathan Lewis
  • 18. Re: db_file_multiblock_read_count not in effect
    askraks Pro
    Currently Being Moderated
    Thanks Jonathan and let me do some practice on the same.

    Kind Regards,
    Rakesh
  • 19. Re: db_file_multiblock_read_count not in effect
    PavanKumar Guru
    Currently Being Moderated
    Hi Charles,

    I had tested some test case with some slight changes carried from your test case.Request to kindlygo through and let me your valuable comments so that it would help for us to understand things went.

    http://oracleinternals.blogspot.com/2010/10/dbfilemultiblockreadcount-not-in-effect.html

    - Pavan Kumar N
  • 20. Re: db_file_multiblock_read_count not in effect
    CharlesHooper Expert
    Currently Being Moderated
    Pavan,

    That was a good idea to try the test case with a couple of changes. It is interesting that you received a sort merge join, and it is also interesting that your test case shows the optimizer favoring nested loops joins over hash joins. I plan to post another follow-up article soon that explores some of the suggestions found in the comments on my blog article. I will also vary the column width to see if it makes a difference.

    We are probably moving a bit off the subject of this thread. However, I am a little curious about your test results. What Oracle Database release did you use for your test case? What modified parameters do you have set:
    SHOW PARAMETER OPTIMIZER
     
    SHOW PARAMETER PGA
     
    SHOW PARAMETER HASH
    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 21. Re: db_file_multiblock_read_count not in effect
    PavanKumar Guru
    Currently Being Moderated
    Hi Charles,

    I was very much eargly waiting for your reply from couple of hours.. within this time I am working on some of test cases from that concept as we started. Still if it is deviating from the original forum question - perhaps it a learning curve @end of day we are gaining - I think so.

    Please find the details at requested which i missed in the my post @ blog.
    SQL> select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL> SHOW PARAMETER OPTIMIZER
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    optimizer_capture_sql_plan_baselines boolean     FALSE
    optimizer_dynamic_sampling           integer     2
    optimizer_features_enable            string      11.2.0.1
    optimizer_index_caching              integer     0
    optimizer_index_cost_adj             integer     100
    optimizer_mode                       string      ALL_ROWS
    optimizer_secure_view_merging        boolean     TRUE
    optimizer_use_invisible_indexes      boolean     FALSE
    optimizer_use_pending_statistics     boolean     FALSE
    optimizer_use_sql_plan_baselines     boolean     TRUE
    SQL>
    SQL> SHOW PARAMETER PGA
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    pga_aggregate_target                 big integer 0
    SQL>
    SQL> SHOW PARAMETER HASH
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hash_area_size                       integer     131072
    SQL> show parameter sga
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    lock_sga                             boolean     FALSE
    pre_page_sga                         boolean     FALSE
    sga_max_size                         big integer 744M
    sga_target                           big integer 0
    - Pavan kumar N
    ORACLE - OCP 9i/10g
    RHCE - Enterprize Linux 5.4
  • 22. Re: db_file_multiblock_read_count not in effect
    CharlesHooper Expert
    Currently Being Moderated
    Pavan Kumar wrote:
    Hi Charles,

    I was very much eargly waiting for your reply from couple of hours.. within this time I am working on some of test cases from that concept as we started. Still if it is deviating from the original forum question - perhaps it a learning curve @end of day we are gaining - I think so.

    Please find the details at requested which i missed in the my post @ blog.
    SQL> SHOW PARAMETER PGA
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    pga_aggregate_target                 big integer 0
    SQL>
    SQL> SHOW PARAMETER HASH
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- ------------------------------
    hash_area_size                       integer     131072
    - Pavan kumar N
    ORACLE - OCP 9i/10g
    RHCE - Enterprize Linux 5.4
    You might have just tested Hemant K Chitale suggestion regarding the HASH_AREA_SIZE parameter. For your session it is defaulting to twice the value of the SORT_AREA_SIZE, which defaults to 64KB. You are not using the PGA_AGGREGATE_TARGET, so the value of these parameters could have an impact. Try setting the HASH_AREA_SIZE parameter to a larger value at the session level, such as 40M to see how that impacts your results. My follow up test article will vary the PGA_AGGREGATE_TARGET between 2000M and 100M - I would have to set the PGA_AGGREGATE_TARGET to a much smaller value to simulate a HASH_AREA_SIZE of 128KB, so I probably will not do that.

    Edit:
    -------------------------------
    Pavan,

    I was able to obtain a couple of sort merge joins when I set OPTIMIZER_INDEX_COST_ADJ to a value of 10 or 20 (you apparently have not modified that parameter), but those sort merge joins disappeared when OPTIMIZER_INDEX_COST_ADJ was set to 100, 50, or 1. I posted a follow up blog article with a couple of other changes to the original test case just to see what I could do to cause the optimizer to prefer nested loops joins over hash joins. There might be a third article in the series eventually.
    -------------------------------

    Charles Hooper
    Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
    http://hoopercharles.wordpress.com/
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.

    Edited by: Charles Hooper on Oct 2, 2010 3:14 PM
    Added test results presented in a second blog article.
  • 23. Re: db_file_multiblock_read_count not in effect
    OraDBA02 Newbie
    Currently Being Moderated
    I got all answers that i was looking for.
    Thanks everyone for your time and valuable inputs...
    Many thanks to OTN for this wonderful forum.
1 2 Previous Next

Legend

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