suggestion required
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: suggestion required

  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    11

    suggestion required

    Hi,
    Can anybody suggest me for this problem
    Here we have Oracle 8i on Windows NT set up.

    I run the following Query to check how many number of times full table scan is taking place
    and got the following result

    select name,value from v$sysstat where name='table scans(long tables)';

    name value
    table scans(long tables) 24519

    The value for the Parameter DB_FILE_MULTIBLOCK_READ_COUNT is 16

    My question is should I increase this value for the better performance.How much should I increase?What is the maximum limit for this parameter in Windows NT environment.

    Regards,
    Saurabh

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    32
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #3
    Join Date
    Mar 2004
    Location
    India
    Posts
    11
    Thanks for the answer.
    Can you plz also let me know the maximum size of this parameter on Windows NT and UNIX platform.

    I have one more doubt.
    I would be help full if some body can clear this doubt also.

    Before asking this question ,I would like to tell you that I am working at one of my companies client site.Here I have found that on certain big tables, indexes are made on 3-4 columns but name of the index is common for all the indexes on each column.It seams to be very strange to me.
    My question is that,how oracle optimizer will decide,which index to follow if it tries to perform index scan.
    I have checked out optimizer is performing full table scan on all these tables.The optimizer mode is "CHOOSE"
    Is this is the reason why optimizer is not able to perform index scan and doing full tble scan?

    In this case should I need to rebuild all the indexes on these tables.

    Regards,
    Saurabh

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Max I/O size on NT is 256KB to my guess.

    As far as Index having same name for 3 or 4 columns is concerned.. Such Index are called Composite Index..

    I would suggest u better RTM
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

    Re: suggestion required

    Originally posted by saurabhvtechno
    I run the following Query to check how many number of times full table scan is taking place

    To paraphrase TKyte, "all full scans are not evil, all indexes are not good".


    My question is should I increase this value for the better performance.How much should I increase?What is the maximum limit for this parameter in Windows NT environment.
    My suggestion would be to find which queries are causing FTS and tune them if appropriate. Arbitrarily increasing your mbrc can cause other queries to favor FTS over index hits (which may or may not be good).
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by abhaysk
    32
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by marist89
    32 was for "What is the maximum limit for this parameter in Windows NT environment." assuming block size is 8K ..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by saurabhvtechno
    I have checked out optimizer is performing full table scan on all these tables.The optimizer mode is "CHOOSE"
    Is this is the reason why optimizer is not able to perform index scan and doing full tble scan?
    check the value for the init parameter optimizer_index_cost_adj -- set it to somewhere between 10 and 20
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Mar 2004
    Location
    India
    Posts
    11

    High Water Mark...

    Thanks to all of you for giving me suggestion on my question.

    Today I came to know the following details about the database related to high water mark and unused blocks.
    My database contains 586 tables, out of which following tables are found with unused blocks.Kindly note that all these tables are very big tables and heavy transaction takes place on these tables.

    Following are the details.

    Table_Name UnUsed Block Total Blocks High Water Mark

    ISAG 46 192 145
    MBOI 67 128 60
    MMAT 46 512 465
    TACP 4581 6432 1850
    TDLH 74 800 725
    TDLI 7310 27360 20049
    TGLI 3590 18272 14681
    TGPI 8300 37664 29363
    TGRH 707 2496 1788
    TGRI 48 6976 6927
    TIVH 642 2496 1853
    TIVI 202 2496 2293
    TIVP 1176 27392 26215
    TSBH 104 1696 1591
    TSBI 224 18240 18015
    TSBP 15837 92320 76482
    TSOI 62 64 1

    My Question is
    Is is it possible to skip or deallocate the unused blocks because during full table scan,oracle will definetely be scanning this unused blocks also,which might be causing performance loss.
    Whether dellocating these blocks will cause any performance gain
    Should I increase the extent size also for these tables.

    Kindly guide me how can we skip/deallocate the unused blocks.
    Suggest me the neccessary actions I should perform to increase the performance in this condition.

    Please note that db_block_size is only 4KB.

    Regards,
    Saurabh

  10. #10
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    A quick look indicates that the unused blocks are above the high water mark (compare HWM with total minus unused blocks). FTS scans only up to the HWM, so no problem.

Posting Permissions

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



Click Here to Expand Forum to Full Width