This discussion is archived
7 Replies Latest reply: Jan 15, 2010 7:18 AM by CharlesHooper RSS

How to fetch row faster when I have around 1 lakh row.

Rafi (Oracle DBA) Pro
Currently Being Moderated
Hi All,
If I need to fetch my row faster.Is there any way?


Thanks,
Rafi.
  • 1. Re: How to fetch row faster when I have around 1 lakh row.
    SatishKandi Guru
    Currently Being Moderated
    Rafi (Oracle DBA) wrote:
    Hi All,
    If I need to fetch my row faster.Is there any way?
    Using better access path to fetch that row.

    In absence of any other information, difficult to extend any sort of help here.
  • 2. Re: How to fetch row faster when I have around 1 lakh row.
    Rafi (Oracle DBA) Pro
    Currently Being Moderated
    I asked in a general way.I mean I should get results quick.


    Thanks,
    Rafi.
  • 3. Re: How to fetch row faster when I have around 1 lakh row.
    SatishKandi Guru
    Currently Being Moderated
    Rafi (Oracle DBA) wrote:
    I asked in a general way.I mean I should get results quick.
    Use Index based or ROWID based access path to the table (assuming you want to query some rows out of that table with ~0.1 million records and not the entire table).
  • 4. Re: How to fetch row faster when I have around 1 lakh row.
    Neev Explorer
    Currently Being Moderated
    General way Answer -YES
  • 5. Re: How to fetch row faster when I have around 1 lakh row.
    Rafi (Oracle DBA) Pro
    Currently Being Moderated
    Hi Satish,
    You got it right to some extend.


    Thanks,
    Rafi.
  • 6. Re: How to fetch row faster when I have around 1 lakh row.
    538849 Newbie
    Currently Being Moderated
    Hi,

    The most precise way for fetching rows faster can be attained in number of ways.

    1. The first way is apply indexes and in case indexes got large number of deletions then rebuild it.
    2. The next way is the optimizer you are choosing.

    Literaly these parameters are effective then this thing will automatically lead to faster fetching.

    Thanks
    Gursimran
  • 7. Re: How to fetch row faster when I have around 1 lakh row.
    CharlesHooper Expert
    Currently Being Moderated
    Gursimran Singh wrote:
    Hi,

    The most precise way for fetching rows faster can be attained in number of ways.

    1. The first way is apply indexes and in case indexes got large number of deletions then rebuild it.
    2. The next way is the optimizer you are choosing.

    Literaly these parameters are effective then this thing will automatically lead to faster fetching.

    Thanks
    Gursimran
    Gursimran, could you please clarify your suggestions for improving the precise way of fetching rows faster?
    1. Are you suggesting that the OP should rebuild indexes to improve how quickly Oracle is able to find rows when there were a lot of deletions in the table? There is a fun series of blog articles here that might help before the OP attempts to rebuild indexes: http://richardfoote.wordpress.com/category/index-rebuild/

    2. Are you suggesting that the OP switch between the RULE based optimizer and the COST based optimizer (or vice-versa)?

    For the OP: I am not sure how many rows are being requested from the database (I am guessing 10,000,000), but:
    1. What about changing the array fetch size (number of rows fetched in a single fetch request)?
    2. Why are you selecting so many rows - will a large number of the rows be eliminated in the client-side application. Is it possible to reduce the number of rows returned from the database by aggregating the data, filtering the data, or processing the data on the server?
    3. Are there any columns being returned from the database that are not needed? If so, remove those columns.
    4. Is there a high latency WAN connection, or a slow LAN connection between the server and the client? If so, repeat the test again when connected at gigabit speeds.
    5. Are table columns included in inline views in the SQL statement that are not used (discarded, not returned to the client) outside the inline view? If so, get rid of those columns - there is no sense in carrying those columns through a join, group by, or sort operation if the columns are never used. The same applies to statically defined views accessed by the SQL statement.
    6. Assuming that the cost-based optimizer is in use, have you checked the various optimizer parameters - have you done something silly like setting OPTIMIZER_INDEX_COST_ADJ to 1 and set OPTIMIZER_MODE to FIRST_ROWS?
    7. Have you set other parameters to silly values, like setting DB_FILE_MULTIBLOCK_READ_COUNT to 0, 1, 8, 16, etc?
    8. Have you not collected system (CPU) statistics, if available on your Oracle version (what is the Oracle version number, ex: 8.1.7.3, 9.2.0.7, 11.2.0.1, etc.).
    9. Have you examined an explain plan (or better yet, a DBMS_XPLAN with 'ALLSTATS LAST' as the format parameter)?
    10. Have you captured a 10046 trace at level 8, and either manually reviewed the file or passed it through TKPROF (or another utility)?
    11. Have you tried to re-write the SQL statement into an equivalent, but more efficient form?
    12. Have you collected a 10053 trace for a hard parse of the SQL statement?
    13. Have you recently collected table and index statistics for the objects?

    What about finding the root cause of the performance problem? Sure, it might be fun to blindly try things to see if they help, but how do you know if what you have tried has helped withour measuring?

    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.

Legend

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