Query Performance

Do you know columns in selection list affect use of index on table..??

I couldn't  believe this..I was always under impression that query plan is decided based on from and where clause and select clause has no impact on index usage. I was proved wrong in the Smoke Test for INT2 at midnight yesterday..

See how...

select trx_number, org_id, interface_header_attribute1      
          from ra_customer_trx_all
          where org_id = 112 
         
Plan
SELECT STATEMENT  ALL_ROWSCost: 1,947  Bytes: 219,538  Cardinality: 9,979     
    1 TABLE ACCESS FULL TABLE AR.RA_CUSTOMER_TRX_ALL Cost: 1,947  Bytes: 219,538  Cardinality: 9,979 



select trx_number, org_id      
          from ra_customer_trx_all
          where org_id = 112
         
Plan
SELECT STATEMENT  ALL_ROWSCost: 551  Bytes: 129,727  Cardinality: 9,979     
    1 INDEX SKIP SCAN INDEX AR.RA_CUSTOMER_TRX_N1 Cost: 551  Bytes: 129,727  Cardinality: 9,979 
        
Ultimately the solution to this performance issue was add one more table to query... Interesting huh?

bad query
select trx_number
          from ra_customer_trx_all
          where 1=1
          and interface_header_attribute1 = to_char(pin_order_number)
          and org_id = v_Header_Rec_Type.org_id
          and rownum = 1;

Good query

select trx_number      
          from apps.ra_customer_trx_all rct, apps.ra_customer_trx_lines_all rctl
          where rct.customer_trx_id = rctl.customer_trX_id
            and rctl.sales_order = to_char(66000120)
            and rct.org_id = 112
            and rownum < 2;

Materialized View with Fast Refresh idea

Complete refresh for materialized views may not be time effective in all cases. Moreover if the changes to data are minimal Fast refresh should be used and scheduled frequently using cron job or dbms_job.

I faced issue as there was no primary key on underlying table to create materialized view. I was under impression that there must be primary key. However there is an idea to beat this..

We can create materialized view based on rowid column as well

Below are the scripts

---------------- on Web DB  -------------------

create materialized view log on subscription.xxrh_subscription with rowid excluding new values;

grant select on subscription.MLOG$_xxrh_subscription to web;



----------------- On R12EBS environment in Web Schema  ---------------

CREATE MATERIALIZED VIEW web.xxrh_subscription_mv tablespace web_data
    REFRESH FAST ON DEMAND WITH ROWID AS
    SELECT *
    FROM subscription.xxrh_subscription@WEB_LINK;

CREATE INDEX web.XXRH_SUBSCRIPTION_MV_IDX1 ON web.XXRH_SUBSCRIPTION_MV(ORACLE_ACCOUNT_NUMBER) tablespace web_indx;

ThanksAnil Prodduturi for this knowledge!

ORA-04021 Timeout when compiling package

Sometimes we face timeout error ORA-04021 when trying to compile existing package.

Following steps can help to get rid of the error.
1) Find who is accessing the object using below sql
     SELECT * FROM v$access WHERE object = 'XXRH_SUPX_ORCL_PKG';

2) Check session details using below query. SID will be from the result of above query
    SELECT * FROM V$SESSION WHERE SID IN (1040,1529,1016)

3) Kill the session which is accessing the package . Ensure that you inform osuser of these session before you kill them!
   ALTER SYSTEM KILL SESSION '1016,58335'

Go ahead with package compilation!

Unix Find and Replace

Find and replace particular text from script .
This is useful in any install shell scripts which need paths in LDT to be changed based on instance 

mv testfindrep.sh testfindrep.old
sed 's/ebstdev/ebstst1/g' testfindrep.old > testfindrep.sh

Here
s means substitute
ebstdev expr to find
ebstst1 expr to replace with

g means globally - find all

> redirects output to the file

What are default parameters for Concurrent Program / Request Set

Use below queries to get all programs with specific default value, set as parameter value. i.e. You know default value and need to search programs using it
This is useful to find programs having paths defaulted

--- Query to get default parameters for all concurrent programs

SELECT fcp.user_concurrent_program_name, fcp.concurrent_program_name,
       fdfcu.end_user_column_name, fdfcu.DEFAULT_VALUE
  FROM fnd_descr_flex_column_usages fdfcu, fnd_concurrent_programs_vl fcp
 WHERE fcp.concurrent_program_name =
                          REPLACE (fdfcu.descriptive_flexfield_name, '$SRS$.')
   AND fdfcu.DEFAULT_VALUE LIKE '/opt/apps%'   
  
-- Query to get default parameters for all concurrent request sets
SELECT frsv.user_request_set_name, frspa.descriptive_flexfield_name,
       fdfcu.end_user_column_name, frspa.DEFAULT_VALUE
  FROM fnd_request_set_program_args frspa,
       fnd_request_sets_vl frsv,
       fnd_descr_flex_column_usages fdfcu
 WHERE 1 = 1
   AND frspa.DEFAULT_VALUE LIKE '/opt%'
   AND frspa.request_set_id = frsv.request_set_id
   AND fdfcu.descriptive_flexfield_name = frspa.descriptive_flexfield_name
   AND frspa.application_column_name = fdfcu.application_column_name

Setting Operating Unit Mode mo_global.get_current_org_id

In R12 Development, we are using mo_global.get_current_org_id profile in parameters to get default value based on operating unit.
But Most of the cases these values returning null.

Ex: techwah, techwah HK/CN programs.
 

Application Way of doing it is

Please go to System Administration --Concurrent programs-- Go to Request tab --Select Operating Unit value as Single or Multiple.

This will stamp multi_org_category in fnd_concurrent_programs . 

In case of some restrictions.. backend update also can be used

For this, from back end  we need to run below query or from application  we need to update multi org category from system administration responsibility for that particular concurrent program .

update fnd_concurrent_programs 
set multi_org_category = 'S'
where concurrent_program_name = <conc prog name> 

Pipeline Function

In some developments, situation demands to write sql query on PL/SQL table type.
Oracle provides pipe line commands to achieve this .

I found it handy to use along with Oracle Application Express ( APEX )which needs report query region to have customized data display.

How to query from PL/SQL?
Use below simple steps and its ready for query

1) Create database data type and table type.
Note the type declaration should match pl/sql record/table type so data can be "poured" into database type

create or replace type contract_lines_obj_t as object
(
line NUMBER,
instance_id NUMBER,
end_customer NUMBER,
product VARCHAR2 (240),
qty NUMBER,
description VARCHAR2 (450),
status VARCHAR2 (50),
start_date DATE,
end_date DATE,
price NUMBER,
unit_price NUMBER,
exception_status varchar2(2),
exception_code varchar2(200),
exception_information varchar2(4000)
);
/
create or replace type contract_lines_ntt as table of contract_lines_obj_t;

2) Develop a pl/sql function to get the table type populated

function contract_lines(pid in number)
return contract_lines_ntt pipelined
is
pragma autonomous_transaction;
pout_contract_lines_tab contract_lines_tab_type ;
v_exception_key number;
v_index number;

l_return_status varchar2(100);

begin
-- populate data and get pl/sql table type as out parameter
begin
xxrh_show_contract_lines( pid ,
l_return_status ,
pout_contract_lines_tab );
exception
when others
then
v_exception_key := pout_contract_lines_tab.next(pout_contract_lines_tab.last);
if v_exception_key is null
then
v_exception_key := 1;
end if;

pout_contract_lines_tab(v_exception_key).exception_status := 'Y';
pout_contract_lines_tab(v_exception_key).exception_code := SQLCODE;
pout_contract_lines_tab(v_exception_key).exception_information := SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace;
end;

if(pout_contract_lines_tab.count > 0)
then
v_index := pout_contract_lines_tab.first;
loop
exit when v_index is null;
commit;
pipe row (contract_lines_obj_t(pout_contract_lines_tab(v_index).line
, pout_contract_lines_tab(v_index).instance_id
, pout_contract_lines_tab(v_index).end_customer
....
, pout_contract_lines_tab(v_index).exception_code
, pout_contract_lines_tab(v_index).exception_information
)
);
v_index := pout_contract_lines_tab.next(v_index);
end loop;
else
commit;
pipe row (contract_lines_obj_t(null
........
, null
));
end if;
return;
exception
when others
then
commit;
pipe row (contract_lines_obj_t(null
, null
.......
, 'Y'
, SQLCODE
, SQLERRM || ' Stack: ' || dbms_utility.format_error_backtrace
));
return;
end contract_lines;

Running of above function causes data to be populated in the table type

3) Now you are ready to query data populated already

select LINE , INSTANCE_ID, END_CUSTOMER, PRODUCT , QTY , DESCRIPTION , STATUS ,START_DATE , END_DATE , PRICE , UNIT_PRICE
from table(xxrh_supx_orcl_pkg_ypatil.contract_lines(3103165));

Note the from clause

More details at : http://www.adp-gmbh.ch/ora/plsql/pipeline.html
http://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm

P.S. Oracle should have some function to clear projects pipeline as well :) to have work for all eligible hands!