I am currently working on SQL Developer for the couple of weeks. I have specific need to check, how much time one query take to fetch all the records. Currently it can fetch only 50 records and when scroll then next 50 records and so on.
Can anybody tell me, how to set this to fetch full records
There's no setting to fetch all records. You wouldn't like SQL Developer to fetch for minutes on big tables anyway.
If, for 1 specific table, you want to fetch all records, you can do Control-End in the results pane to go to the last record. You could time the fetching time yourself, but that will vary on the network speed and congestion, the program (SQL*Plus will be quicker than SQL Dev because it's more simple), etc.
Now, what I think you really want here, is getting the execution time. If you really want to analyze the query's performance, you'd have to use the explain plan, autotrace and tkprof (see the manual for tuning and performance).
To make us life easier, the makers of SQL Developer included support of the explain plan, and as of v1.1, autotrace!
I'm sure you've got enough to work things out from here...
The F9 key return the set of 50 records at a time. You can use the F5 key to return all your records. For those of you not wanting to return all records there is a preference in 1.1, for the Script Output, that allows you to control how many records returned. i.e. you might only want to return the first 10 records, for really big tables.
In terms of assessing performance, follow the suggestions provided above.
In this case let's consider for example this simple use case:
in data grid or in SQL worksheet with a normally executed statement (F9) I wish to provide a report of the data (Excel, csv or HTML). How can I make sure that all the rows are exported?
In SQL Navigator there is an option to select all rows...
The Export Data function exports everything; if not all rows are fetched already, sqldev fetches them now. If the table's big enough, you'll see the record counter advance in the status bar.
Mind you've got an extra Where tab in the dialog. By default, you'll always get the same results you get in the data grid.
What about browsing the data: went into data grid and pressed Ctrl+End for a table with 11 million rows. The SQL Developer GUI just froze and never came back. Is there any buffer size I can increase in order to accommodate these bigger tables? In SQL Navigator I have no problem for the same table.
Sqldev vs. other tools has some serious performance issues. Fetching 11 million rows may take ages.
Additionally, for exporting, sqldev may fetch the whole lot again. In previous versions up to 3 times!
The GUI freezing up until the action is complete is a known and annoying issue.
So, you can be patient for sqldev to finish fetching, or use sqlnav.
FWIW, the team promised to look at the performance issues for next release...