1

I thought for sure this would be an easy issue, but I haven't been able to find anything. In SQL Server SSMS, if I run a SQL Statement, I get back all the records of that query, but in Oracle SQL Developer, I apparently can get back at most, 200 records, so I cannot really test the speed or look at the data. How can I increase this limit to be as much as I need to match how SSMS works in that regard?

I thought this would be a quick Google search to find it, but it seems very difficult to find, if it is even possible. I found one aricle on Stack Overflow that states:

You can also edit the preferences file by hand to set the Array Fetch Size to any value. Mine is found at C:\Users<user>\AppData\Roaming\SQL Developer\system4.0.2.15.21\o.sqldeveloper.12.2.0.15.21\product-preferences.xml on Win 7 (x64). The value is on line 372 for me and reads I have changed it to 2000 and it works for me.

But I cannot find that location. I can find the SQL Developer folder, but my system is 19.xxxx and there is no corresponding file in that location. I did a search for "product-preferences.xml" and couldn't find it in the SQL Developer folder. Not sure if Windows 10 has a different location.

As such, is there anyway I can edit a config file of some sort to change this setting or any other way?

Improve this question
2
  • 1
    Try to execute your query as script, using script execution button besides query execution button Commented Dec 1, 2020 at 5:55
  • 2
    If you are displaying many more than 200 rows to the screen, your performance testing will be meaningless - most of the time goes to output to screen (which is almost surely NOT part of your business requirement anyway). The better question is, how should you design your performance tests so that they are meaningful. (That is: make it so that the full query must be executed, no shortcuts, but yet the output is not sent to the screen, if it's tens of thousands of rows.) Commented Dec 1, 2020 at 7:55

1 Answer 1

Reset to default
1

If you're testing execution times you're already good. Adding more rows to the result screen is just adding fetch time.

If you want to add fetch time to your testing, execute the query as a script (F5). However, this still has a max number of rows you can print to the screen, also set in preferences.

Your best bet I think is the AutoTrace feature. You can tell it to fetch all the rows, you'll also get a ton of performance metrics and the actual execution plan.

Check that last box

enter image description here

Then use this button to run the scenario

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.