Home » SQL & PL/SQL » SQL & PL/SQL » Evaluating a sys_context call in a predicate (DB 19.22)
Evaluating a sys_context call in a predicate [message #689519] Wed, 31 January 2024 11:10 Go to next message
John Watson
Messages: 8935
Registered: January 2010
Location: Global Village
Senior Member
I have a performance issue with a query that uses SYS_CONTEXT as a filter predicate. The cardinality estimate is terrible (25, whereas it is actually 280000). The estimate is being calculated as the number of rows in the table divided by the number of distinct values. I have histograms of course on the columns and appropriate extensions but they aren't being used. Dynamic sampling is not working either. I'm assuming the problem is that the SYS_CONTEXT call is being evaluated after the execution plan is developed and after any sampling. Is there any way to force Oracle to expand the SYS_CONTEXT before developing the plan?

I've been trying to get a better plan with OPT_ESTIMATE or LEADING hints but I can't get that working; I suspect because of too many layers of views. No hint reports - known bugs, it seems. Furthermore, I would prefer to let the optimizer do its job rather than trying to force it.

Thankyou for any insight.
Re: Evaluating a sys_context call in a predicate [message #689520 is a reply to message #689519] Thu, 01 February 2024 11:02 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
Senior Member
Have you tried a push_pred hint?

Or, this may be a little outdated and undocumented, but is it possible to isolate the predicate that uses sys_context in a separate inner sub-query with a cardinality hint and/or materialize hint?

Re: Evaluating a sys_context call in a predicate [message #689521 is a reply to message #689520] Fri, 02 February 2024 07:06 Go to previous messageGo to next message
John Watson
Messages: 8935
Registered: January 2010
Location: Global Village
Senior Member
Thank you for replying. Predicate pushing can't help: the view is already being merged. I think a hint for the cardinality would work, but I can't get it in there. There is layer upon layer of views, and it is an APEX application which wraps things up in more layers.
I was wondering about a SQL macro, and have just wasted half an hour trying to make a SQL scalar macro do it, but have just found out the scalar macros don't exist in release 19. So that's no good. And anyway, I don't know when a macro is evaluated.

So now I'll try to get around it with SQL baselines (if I can ever get the plan I want...) and/or Statistics Feedback, which doesn't seem to be kicking in for some reason.

In case anyone is interested, this is an example of the problem:
conn scott/tiger

create table names(c1 varchar2(10));

begin for i in 1..1000 loop
insert into names values('TIGER');
end loop;
end;
/

insert into names values('SCOTT');

exec dbms_stats.gather_table_stats(user,'names',method_opt=>'for all columns size skewonly');

set autot on exp

select count(*) from names where c1='SCOTT';
select count(*) from names where c1='TIGER';
select count(*) from names where c1=sys_context('userenv','current_user');

Re: Evaluating a sys_context call in a predicate [message #689524 is a reply to message #689521] Sat, 03 February 2024 02:13 Go to previous message
John Watson
Messages: 8935
Registered: January 2010
Location: Global Village
Senior Member
I have a solution! VPD. Which does need Enterprise Edition. Building on my example above:
orclz>
orclz> create or replace function vpdfunc
  2  (object_schema varchar2,
  3  object_name varchar2)
  4  return varchar2
  5  as
  6  retval varchar2(10);
  7  begin
  8  retval := sys_context('userenv','current_user');
  9  return 'c1 = '||''''|| retval||'''';
 10  end;
 11  /

Function created.

orclz> select vpdfunc('a','b') from dual;

VPDFUNC('A','B')
----------------------------------------------------------------------------------
c1 = 'SCOTT'


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

orclz> exec dbms_rls.add_policy('SCOTT','NAMES','P1','SCOTT','VPDFUNC');

PL/SQL procedure successfully completed.

orclz> select count(*) from names;

       COUNT(*)
---------------
              1


Execution Plan
----------------------------------------------------------
Plan hash value: 1341785398

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     6 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |       |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| NAMES |     1 |     6 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("C1"='SCOTT')

orclz>
Previous Topic: What is the difference between varchar2 and nvarchar2?
Next Topic: oracle10g and utl_http
Goto Forum:
  


Current Time: Mon May 27 01:48:17 CDT 2024