SQL with bind vars is very slow in toad

Toad seems to run a couple of extra PARSE-CLOSE operations too. This seems a bit strange to me, maybe John knows why?

When you execute select statements in Toad's grid, Toad asks the OCI to parse the statement first. We do this in order to determine field sizes. Once we have an idea of how wide the rows are, we adjust the OCI Array Buffer Size accordingly. This controls
the number of rows retrieved per fetch. For small rows, we set set it high - 500 rows per fetch is the default. As row size increases, we decrease rows per fetch. This adjustment optimizes fetch speed but not at the expense of memory consumption.

We also parse the SQL to look for XMLTYPE columns. If there are XMLTYPE columns and we try to retrieve them that without casting to CLOB, The OCI is going to throw an error. So If Toad detects XMLTYPE, we rewrite your SQL so that the XMLTYPE columns are cast to
CLOB.

Much obliged John, thanks. It seems I guessed right!

It's very strange that the execution plan generated by the "throw away" parse matched the good plan, but the one executed was a bad one. Something changes between parses it would seem, but exactly what, I have no idea!

I've not come across any situations where adjusting the buffersize affects the execution plan.

@Johann, in Sql Plus, do you see any difference in execution when you execute something like 'set arraysize 500' or bigger, or smaller?

Cheers,
Norm.

Hello all,

I can't see any difference in SQLPlus when i change the arraysize.

In the meanwhile
we excluded a baseline and we where able to get it stable.

For now we don't have other SQLs which an similar behavior.

But for now i'm
really unsure if i can trust toad' execution times. :-\

THX

Good Morning Johann,

I'm glad you got sorted, that's good news. We are having to do similar things (at work) with a similar problem, but Toad isn't involved - it's a Web App. Oracle picks a stupid plan for no good reason.

You are aware I hope, that Toad doesn't execute SQL? Toad passes the SQL to Oracle to execute. I noticed from your latest trace files (did yo get my response by the way?) that Toad gets Oracle to parse the statement and gets the good plan, then it parses and executes it, and this time gets the bad plan.

The first parse requested by Toad is to determine columns and bind variable sizes and precisions etc, as John explained, the second is to execute it. I'm not sure why there needs to be a second parse though. Given that Oracle returns the correct plan hash for the first parse, I'm wondering if it's possible for Toad to avoid the second parse and just execute the already parsed statement? John will know.

Something weird is going on with your SQL statements between the initial parse, with the good plan, and the almost immediate second parse which changes to the bad plan. It is unlikely to be Toad, as I suspect that the second parse doesn't change anything in the environment before it is parsed. However, Oracle is changing the plan - and it is most likely a bug, which is why we at work, are having to mess about with pinning plans etc. :frowning:

Take care and stay safe.

Cheers,
Norm. [TeamT]

We're only doing one parse in our code. The other may be happening in the 3rd party component we use to access the database. I'll investigate. Maybe one of them can be eliminated. Even if it has nothing to do with the problem, it would be nice to eliminate the extra work (if it's indeed extra)

Thanks John. Parsing is always a problem! :wink:

I'm always getting system to stop parsing, regardless of where my contract happens to be. Usually I see a 1:1 (or worse) execute:parse ratio even when the SQL in question has bind variables. Go figure!

Java is the worst offender. I think because the SQL is a local variable in a member function, and as such, is created on the stack at runtime, parsed, bound (binded?), executed, fetched and then closed each time the function is called and goes (back) out of scope.

Cheers,
Norm. [TeamT]

I should be able to remove the 2nd parse. I remember now why it was there.

The first was to determine datatypes, as I mentioned before.
Then we had to "unparse" it (make our 3rd party oracle access code forget that it was parsed in the first place) to work around a problem with displaying record numbers in the grid.
Then when Execute was finally called, our 3rd party oracle access code called parse again for its purposes.

But that bug with record number has since been fixed, and I can remove the "unparse", and then we'll be down to a single parse again. Whether or not it fixes Johann's execution path problem, I don't know.

I'll post again when the change is in beta.

Cheers John. It will be interesting to see if the change means that the execution plan from the first parse gets used.

Cheers,
Norm. [TeamT]

If the change is available in Beta, please let me know.

I can't take out that extra parse, after all, sorry. Doing so causes bigger problems. I can come back to it again in the future and take another look at it, but I for now I need to leave it in and move on.