crystal report
Anand, May 14, 2012 - 12:56 am UTC
Hi Tom,
We are using crystal report to generate report on daily basis which will execute function in oracle using CR oracle ODBC driver 5.0(DSN).
The question is how can i disable rule hint which uses by this DSN as in below query :
In other DSN like oracle in oradb10g_home1 have option to disable the hint rule.
If we remove the hint the query have good performance tuning.
Below is the query which execute by crystal every time we generate the report :
(select /*+ RULE */ a.owner,decode (b.object_type,'PACKAGE',concat(concat(b.object_name, '.'),a.object_name),b.object_name),
decode(a.position,0,'RETURN_VALUE',decode(a.argument_name,NULL,'UNKNOWN',a.argument_name)),
decode(a.position,0,5,decode(a.in_out,'IN',1,'IN/OUT',2,'OUT',4)),decode(a.data_type,'UNDEFINED',a.type_name,a.data_type),
a.data_precision,a.data_length,a.data_scale,a.radix,a.position
FROM ALL_ARGUMENTS a,dba_OBJECTS b WHERE (b.object_type IN ('PROCEDURE','FUNCTION','PACKAGE')) AND b.object_id=a.object_id AND a.data_level=0
AND a.owner like 'NCBSHOST'escape'\'AND b.OBJECT_NAME like 'AP_CI_R201'escape'\'
union all
select /*+ RULE */ c.owner,decode(b.object_type,'PACKAGE',CONCAT(CONCAT(c.synonym_name,'.'),a.object_name),c.synonym_name),
decode(a.position,0,'RETURN_VALUE',decode(a.argument_name,NULL,'UNKNOWN',a.argument_name)),decode(a.position,0,5,decode(a.in_out,'IN',1,'IN/OUT',2,'OUT',4)),
decode(a.data_type,'UNDEFINED',a.type_name,a.data_type),a.data_precision,a.data_length,a.data_scale,a.radix,a.position
FROM ALL_ARGUMENTS a,dba_OBJECTS b,dba_synonyms c
WHERE (c.table_owner=b.owner AND c.table_name=b.object_name) AND b.object_type IN ('PROCEDURE','FUNCTION','PACKAGE') AND b.object_id=a.object_id
AND a.data_level=0 AND c.owner like 'NCBSHOST'escape'\'AND c.synonym_NAME like 'AP_CI_R201'escape'\'
union all
select /*+ RULE */ c.owner,c.synonym_name,decode(a.position,0,'RETURN_VALUE',decode(a.argument_name,NULL,'UNKNOWN',a.argument_name)),
decode(a.position,0,5,decode(a.in_out,'IN',1,'IN/OUT',2,'OUT',4)),decode(a.data_type,'UNDEFINED',a.type_name,a.data_type),
a.data_precision,a.data_length,a.data_scale,a.radix,a.position
FROM ALL_ARGUMENTS a,dba_synonyms c WHERE (c.table_owner=a.package_name AND c.table_name=a.object_name)
AND a.data_level=0 AND c.owner like 'NCBSHOST'escape'\'AND c.synonym_NAME like 'AP_CI_R201'escape'\') order by 1,2,10;
Please help.
May 14, 2012 - 1:26 am UTC
In other DSN like oracle in oradb10g_home1 have option to disable the hint
rule.that is news to me, I've never heard of being able to disable the single hint "rule".
In any case, maybe you can issue:
alter session set "_optimizer_ignore_hints" = true;
in the Crystal Report before executing the SQL?
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> select /*+ RULE */ * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| DUAL |
----------------------------------
Note
-----
- rule based optimizer used (consider using cbo)
ops$tkyte%ORA11GR2> alter session set "_optimizer_ignore_hints" = true;
Session altered.
ops$tkyte%ORA11GR2> select /*+ RULE */ * from dual;
D
-
X
Execution Plan
----------------------------------------------------------
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
ops$tkyte%ORA11GR2> set autotrace off
the optimizer will ignore hints if you set that to true...