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