TOAD implementation

Posted on 2005-04-26
Last Modified: 2013-12-12
Hey experts!

I have a general question - IF your Oracle (9.2 or 10g) database has say, a terabyte of data in a single table, and you say select * from that table;  TOAD somehow manages to return the first rows of the query, and let you page through the rest...

If you drag the scroll bar to the bottom, you will wait a long time..  then finally see the last screen full of records...

The question is: how is this accomplished?  It seems that all the results are not returned to the client, but instead are kept on the server...  I'm thinking as a snapshot(?).  If this is true, how do you page through a snapshot result set?  any ideas?

alternately, what is the best way to not kill your app with out of memory errors on absurdely large result sets?

thanks for comments.
Question by:randyd
    LVL 11

    Assisted Solution

    TOAD uses the hint FIRST_ROWS to retrive some rows first. So your query is not executed fully. When you scroll down, the quey's remaining part is executed, so you ssee the time consumed. You can do the same trick by usingg FIRST_ROWS hint. For example,

    SELECT /*+ FIRST_ROWS */ distinct customer_name FROM customer;


    Accepted Solution

    Firstly toad does not immediately download the whole database just because you have run some code, this is common across allmost all database browsers, you'll find an ODBC linked table or query will behave in the same way in MS Access. There are some very obvious reasons why this is not desireable.

    Now the hint /*+ FIRST_ROWS */ merely tells Oracle that it should concentrate on returning the first row of the result set as quickly as possible, this is usefull if you are feeding the result set into another process or as in your case you wish to brwse some data.

    Alternatively oracle can concentrate on returning the last record as quickly as possible such as in CTAS (Create table as select ...) statement where the dataset is of no use to you untill the table is fully created.

    One of the basic concepts within Oracle is that all records in a result set must be concurrent in line with the database being a transactional design this requires Oracle to keep track of what changes are happenign in the database whilst your query is running and still return the data as it was when your querry started. In theory it could do this be taking a copy of the data in each table in your querry and then using these for your job, in practice this is not possible as it would use up a lot of space and the tables would not be updatable by other users whilst being copied so Oracle is slightly more celever that that, merely reataining copies of rows that are altered by another user during your query.

    Occaisionaly you may receive a "Snapshot too old" error meaning that other users have made so many changes to the data in your table that you are using that it has run out of temporary space to keep track of all these changes.

    When the query runs then it generates a temporary table containing the resultset that is accessible only to the client from which it has run, the client can then page through the resultset. When you drag down to the bottom of teh result grid, TOAD loads each batch of records until it runs out.

    The default is to load only 25 records at a time, this can be altered in the options under the OCI Array Buffer Size entry. This means that when you scroll all the way to the bottom TOAD loads the next 25 records, deletes the previous 25 records, loads the next 25 records.... untill it runs out. Tweaking this setting up a bit won't hurt s 25 rows is very small.
    LVL 3

    Author Comment

    it turns out that TOAD does in fact store the entire result set in memory when you scroll to the bottom.

    What got me on this tamgent was a message that stated Snapshot too old after i failed to scroll to the bottom for awhile...  sorta makes sense that the internal mechanism is a snapshop - but its just a read consistent cursor to the client.

    anyway, i was aware of first_rows hints etc.  but will split points among all respondents.. thanks

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to recover a database from a user managed backup

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now