Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    39

    Unanswered: Value of db_file_multiblock_read_count

    Hello All,

    I am working in Oracle 8.1.7.4 in Linux.

    I am having some concept problem.

    The below mentioned queries are taking 4 hours :

    select count(*) into cnt from UPGR.pl_ug_bs where vk_id not in (select vk_id from UPGR.pv_kette );

    select count(*) into cnt from UPGR.pl_waiert where zb_id not in (select zb_id from UPGR.pl_ug_bs);

    Database setting are :

    sort_area_size=1048576
    optimizer_mode=CHOOSE
    db_file_multiblock_read_count=32

    When I am changing the settings to the below mentioned one it is taking only 1 hour but some other performance problem is coming :

    sort_area_size=65536
    optimizer_mode=RULE
    db_file_multiblock_read_count=8

    Please advice what could be the reason for this. Also, what other database parameters could affect the time.

    Thanks, Raj

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I am affraid this is not something about parameters, but rather missing statistics. Gather statistics on the tables involved and run the queries again with the optimizer_mode on CHOOSE, let us see the plan then.

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I would suggest changing one parameter at a time otherwise you are not likely to find out what the problem is. You also need to look at the execution plan before and after aswell as benchmarking your whole application.

    It should be noted though in Oracle 8i the db_file_multiblock_read_count directly affects the optimizer in altering the cost of full table scans versus index scans. The higher the value the more likely you are to do full table scans as the optimizer thinks it can read the whole table in less time.

    Look at the execution plan with the original settings and then do alter session to change the value. If the execution plan changes from a full table scan to an index scan then my hypothesis is likely to be correct.

    Alan

  4. #4
    Join Date
    Oct 2005
    Posts
    39

    Thanks

    Thanks for the help and suggesstions.

    For the knowledge of others who may encounter same kind of problem, I could resolve this performance problem by giving hint in the query /*+ ALL_ROWS */.

    It's taking only second in place of hours.

    Thanks for the help got from the forum.

    Regards, Rajeev

  5. #5
    Join Date
    Feb 2007
    Location
    Bratislava, Slovakia
    Posts
    85
    when you decrease mutliblock_read_count parameter, this means- prefer index scans.
    try not exists clause. when vk_id and zb_id are primary keys, (or unique index) can this help you.
    Beer contains just a small amount of vitamines - that's why it's necessary to drink lot of it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •