Wednesday 11 January 2012

APEX performance issues with v()

One of the checks in the APEX Advisor is to determine if any instances of the v() function exist within your SQL.

This confirms one of the first APEX best practices I remember hearing, that you should use bind variables instead of v() within your SQL.
It makes sense, since v() is essentially making an interrogation on the session state table (wwv_flow_data).

Patrick Wolf talks more about performance with determinism here. Martin Giffy D'Souza summaries APEX variables nicely here.

In the comment thread Patrick also suggests the wonderful scalar subquery solution that works on all db versions
WHERE id = (SELECT v('MY_ITEM') FROM DUAL)

The trouble is, I think it's a difficult situation to test - how would you go about determining how many times Oracle decides to invoke the v() function, and for which parameters?

I came across this case was on a 10.2.0.4 db with Apex 3.1.2. I've added Groucho glasses to the query to protect the innocent/guilty.
It's also somewhat simplified. There were half a dozen instances of v(), but I've highlighted what turned out to be the offending line.

select * from a_6000_row_table p
where (  ...
or exists
  (select null
   from a_user_access_table aa
   where (aa.org_unit in (SELECT * FROM TABLE (InList_fn(a_pkg.genInList(p.year, p.org_unit))))
       or aa.org_unit in (SELECT * FROM TABLE (InList_fn(a_pkg.genDiffInList(p.year, p.id)))))
  and aa.inactive is null
  and aa.staff_id = v('LOCAL_USER_ID')
  and (  (aa.access_type in ('ABC','ABCXYZ'))
   or (   aa.access_type in ('DEF')
      and p.latest_status = 'YIPEE'))
  )

As it turns out line 9 wasn't alone in causing the query to take at least 5 seconds to return 1-10 of 6000 in an interactive report.
The first part of the where clause where it constructs a comma delimited list, converts it into a nested table, then interpreted as a table - also lends some form of hand in slowing down the query.

These function calls could probably be replaced with some form of WHERE EXISTS, but I think the original author had a steel plated encapsulation hat.

When first assessing this query, I thought the problem related to a stragg function in the column list which used a FOR-SELECT-LOOP, but then I spotted the obvious.
So I replaced all the v() references with bind variables, and all of a sudden the 5+ second query went sub one second.

I felt a little social, so I tweeted my little win, and Trent asked if I had more details, and I was curious to isolate the exact improvement as when I've briefly played with performance using v() functions on simple queries I came away unconvinced of any issues but happy to play it safe.

Some tests by themselves
  1. Removing the IN list functions got the query to around 0.7s
  2. Changing v() to a bind variable was around 1s
  3. Changing v() to scalar subquery also gave a 1s query.
  4. Setting entire query to use bind variables got down to about 0.5s
For those tuning experts out there who know much more about the Oracle mechanics than I do, I'd be curious to know if you can shed some light on how/why in particular these improvements came about.
For instance, what do you suppose the relationship between the IN list functions and the use of v() is?

For reference - I squeezed in a function call at line 10 that counted how many times it was called. It was always 6000 times, regardless of bind variable usage until I took out the call to the IN list, then it was 9 records. I didn't isolate the significance of that second number.

I remember when learning APEX it was difficult to determine when to use what syntax to refer to values in session state, so two lessons affirmed here today:
  1. Bind variables work! Limit the use of v() function calls to assignments and conditions in PL/SQL packages
  2. Scalar subqueries are just as awesome.
And I'll just add two more related points
  1. Never (never say never) use &ITEM. syntax in queries - I've got a post in the works on that issue.
  2. Don't forget about the nv() function, allowing you to compare the same datatype if you're dealing with numbers. You need to make your own dv function...
Scott

10 comments:

Buzz Killington said...

I still think the best practice is to use packages/procedures/function and pass everything in as parameters. Then you don't have a need to use v(). Not saying you'll never need v(), but it cuts down on its usage a bunch

Scott Wesley said...

Good point, buzz. I was so focused on the bind variable issue, I forgot about that.

I've found so much value in sending them as parameters - makes testing & debugging so much easier.

Lev said...

Hi,
I agree that parameters are the best solution but if you need/want to use v() function than you can read this link:
http://www.inside-oracle-apex.com/caution-when-using-plsql-functions-in-sql-statement/

If you decide to use wrapper deterministic function then don't forget that you have to upgrade your wrapper with each APEX upgrade.

Lev

Scott Wesley said...

But I think the question is, do we ever need to use the v() function?

If we pass actual parameters as bind variables to our pl/sql, and we use bind variables in the apex queries and conditions - the need for deterministic wrappers may be mute.

So is there ever a need to use v() ?

Unknown said...

Hi Wesley

How are things going? Nice post!

I think that the only cases where you need to use the v() function inside Apex are when you are generating the session item name dynamically and don't have at compile time the name of the item (e.g. v('P10_CODE'||i)) or a workaround for this bug (although it seems that it is only me who consider it a bug!):

https://forums.oracle.com/forums/thread.jspa?messageID=9930990

Other than that, I don't see a need to use v(), specially in queries.

Cheers,
Luis

Scott Wesley said...

Hi Luis, I had a great new year, cheers.

That's a really good example of when to use v() - I've done that myself, quite possibly at your site!
That forum entry is a good one, I've been meaning to look into that behaviour myself.

Mohan said...

I agree with Buzz Killington. I practically experienced tremendous performance improvement using package/procedures/functions and passing as parameters.

Scott Wesley said...

Indeed Mohan, passing parameters to pl/sql modules is a good practice for a number of reasons.

Good to hear you obtain so much performance benefit out of it.

Suresh said...

Hey Scotty

If you could help us in general performance problem in the apex application in a particular page, with the debug option/or other see the output and narrow down exactly portion or a region code would be nice.
Thanks Scoot
Suresh

Scott Wesley said...

Suresh, general questions lead to general answers. Do you have a particular problem?