Toad World® Forums

Query immediately returns all rows in grid

Hey all,

In 12.9.0.32, I had three sessions opened in three separate DBs, each with a Schema Browser and Editor window. After doing some ASH/AWR reporting and miscellaneous SQLs in the various connections, I ran this SQL in one of the sessions:

select *
from dba_tab_histograms

And it immediately returned the full 70K rows, seemingly ignoring the OCI Array Buffer Size setting. As a quick fix, I bounced Toad, and now everything is back to normal. In the settings, I was expecting the value for OCI Array Buffer Size to be 500, but I see at some point there’s now an “Automatic” setting (to limit LOB returns to 75, maybe?). It seems like something I did decided to either boost that value or ignore it completely, but I’m unable to replicate.

Thoughts?
Rich

Disclaimer: While I am an oxymoron, I try to put emphasis on the “oxy” part.

Did you have Auto Trace enabled by chance? That will perform a full fetch.

On 02/24/2016 11:52 AM, Rich J. wrote:

Query immediately returns all rows in grid

Thread created by Rich J.
Hey all,

In 12.9.0.32, I had three sessions opened in three separate DBs, each with a Schema Browser and Editor window. After doing some ASH/AWR reporting and miscellaneous SQLs in the various connections, I ran this SQL in one of the sessions:

select *

from dba_tab_histograms

And it immediately returned the full 70K rows, seemingly ignoring the OCI Array Buffer Size setting. As a quick fix, I bounced Toad, and now everything is back to normal. In the settings, I was expecting the value for OCI Array Buffer Size to be 500, but I see at some point there’s now an “Automatic” setting (to limit LOB returns to 75, maybe?). It seems like something I did decided to either boost that value or ignore it completely, but I’m unable to replicate.

Thoughts?

Rich

Disclaimer: While I am an oxymoron, I try to put emphasis on the “oxy” part.

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

The Automatic OCI array buffer size will scale the OCI Array Buffer size on a per-query basis so a setting somewhere between 500 and 10, depending on the width of the rows. It should never set it higher than 500. For a few versions, the OCI Array Buffer Size setting was gone completely, and I just did this “automatic” thing, but some users and support really enjoyed fiddling with the setting so I added the option of putting it back on manual for 12.7 or 12.8. Can’t remember which offhand.

There is a “FetchAll” setting on our query component that will pull all rows in a single fetch, but we don’t expose that setting to the users because it’s easy to get an “out of memory” error with that. There should be no way for you to set that, so I’m not sure how this could even be a possibility.

CTRL+End on the grid is the only way I know of that you could pull all rows, but that’s still 500 (or whatever “automatic” goes to for that table) at a time, and you’d probably know if you did a CTRL+END on the grid.

AutoTrace it is! I had forgotten that I had turned that on and it must thankfully be disabled when exiting Toad.

Thanks for the uber-quick replies, guys!

Rich