Sunday, September 12, 2021

Performance Tuning - ANY_VALUE aggregate function (example)






 

                In this post, we have tried to explain the performance impact of the miscellaneous function named ANY_VALUE with simple example.

How many times we have come across below scenario (display department_name) where we are forced to use aggregate function like MIN or MAX to retrieve a static value within the group. We did this to simply avoid adding additional columns in group by clause.

 Oracle came back with a new aggregate function named “ANY_VALUE()” in 21C. See its usage below.

Oracle official documentation:

ANY_VALUE returns a single non-deterministic value of expr. You can use it as an aggregate function.

Use ANY_VALUE to optimize a query that has a GROUP BY clause. ANY_VALUE returns a value of an expression in a group. It is optimized to return the first value.

It ensures that there are no comparisons for any incoming row and also eliminates the necessity to specify every column as part of the GROUP BY clause. Because it does not compare values, ANY_VALUE returns a value more quickly than MIN or MAX in a GROUP BY query.

Source: https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ANY_VALUE.html#GUID-A3C47D5E-B145-40B2-93D2-CA3BA65C2D81

Analysis:

The interesting part is performance boost which comes with ANY_VALUE comparing to traditional way of MIN or MAX to display a department name like above. Here is the simple script which helps to test the same.

DECLARE TYPE ltest IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER; ltab_test ltest; lt1 timestamp; lt2 timestamp; BEGIN dbms_output.put_line('Print Department name using MIN'); lt1 := SYSTIMESTAMP; dbms_output.put_line('Start Time:'||lt1); FOR i IN 1 .. 100 LOOP SELECT d.deptno ||'('||MIN(dname)||')' BULK COLLECT INTO ltab_test FROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno GROUP BY d.deptno; END LOOP; lt2 := SYSTIMESTAMP; dbms_output.put_line('End Time:'||lt2); dbms_output.put_line('Elapsed Seconds: '||TO_CHAR(lt2-lt1, 'SSSS.FF')); dbms_output.put_line('Print Department name using ANY_VALUE'); lt1 := SYSTIMESTAMP; dbms_output.put_line('Start Time:'||lt1); FOR i IN 1 .. 100 LOOP SELECT d.deptno ||'('||any_value(dname)||')' BULK COLLECT INTO ltab_test FROM scott.emp e, scott.dept d WHERE e.deptno = d.deptno GROUP BY d.deptno; END LOOP; lt2 := SYSTIMESTAMP; dbms_output.put_line('End Time:'||lt2); dbms_output.put_line('Elapsed Seconds: '||TO_CHAR(lt2-lt1, 'SSSS.FF')); END;

 











Conclusion:

As I shown in above example script, when I used for lower volume data like SCOTT schema, ANY_VALUE didn’t gave the performance boost. In fact, it’s slower than MIN or MAX approach. However, with volume of data against which we perform GROUP BY increases the time taken for MIN/MAX is increased whereas time taken by ANY_VALUE is moreover same.

Hence, as per my analysis, ANY_VALUE is pretty much useful from readability perspective. However, performance improvement comes only when data volume is a high.

Hope it’s useful!!.

Any difference in thoughts, I welcome you put in comments. Its mutual learning J

Sunday, September 12, 2021 by Team search · 1

Friday, June 4, 2021

Query to fetch the User logins details in Oracle applications R12 (ICX_SESSIONS)

 

 

 

 

 

 

 

In this post, we have given the query to fetch login details of iSuppliers and employees.

Key Table:

ICX_SESSIONS

Query (iSuppliers):

SELECT hou.short_code entity, fu.user_name, pv.vendor_name, pvc.email_address, icx.first_connect, icx.last_connect, round((icx.last_connect - icx.first_connect)*24*60,2) ||' mins' duration FROM hr_operating_units hou, ap_supplier_sites_all assa, po_vendor_contacts pvc, po_vendors pv, fnd_user fu, icx_sessions icx WHERE hou.organization_id = assa.org_id AND assa.vendor_id = pvc.vendor_id AND pv.vendor_id = pvc.vendor_id AND upper(pvc.email_address) = fu.user_name AND counter < limit_connects AND icx.user_id = fu.user_id AND fu.USER_ID > 1100;

Query (Employees):

SELECT fu.user_name, papf.full_name, papf.email_address, icx.first_connect, icx.last_connect, round((icx.last_connect - icx.first_connect)*24*60,2) ||' mins' duration FROM per_all_people_f papf, fnd_user fu, icx_sessions icx WHERE papf.person_id = fu.employee_id AND sysdate between papf.effective_start_date and papf.effective_end_date AND counter < limit_connects AND icx.user_id = fu.user_id AND fu.USER_ID > 1100

Friday, June 4, 2021 by Team search · 0

Saturday, May 22, 2021

How to Encode/Decode a URL using Oracle PLSQL (UTL_URL.ESCAPE AND UTL_URL.UNESCAPE)

 

 

 

 

 

 

                In this post, we have given an example usage of UTL_URL standard package for encoding or decoding a URL. Hope it helps!!

Custom Wrapper Function:

CREATE OR REPLACE FUNCTION encode_decode_url (p_url IN VARCHAR2, p_action IN VARCHAR2 ) RETURN VARCHAR2 AS BEGIN IF p_url IS NULL THEN RETURN NULL; END IF; IF p_action = 'ENCODE' THEN RETURN utl_url.escape(p_url, TRUE); ELSIF p_action = 'DECODE' THEN RETURN utl_url.unescape(REPLACE(p_url, '+', ' ')); ELSE RETURN 'Error: Invslid Action'; END IF; END encode_decode_url;

Sample Run:

 


Saturday, May 22, 2021 by Team search · 0

Sunday, May 9, 2021

API to create a Phone Contact Point in TCA Oracle Apps R12 (HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT)

 

 

 

 

 

 

 

In this post, we have given a sample script to create a PHONE contact point for a Party in TCA (Trading Community Architecture).

API used: HZ_CONTACT_POINT_V2PUB.CREATE_PHONE_CONTACT_POINT

Tested Instance: R12.2.4

Script:

SET SERVEROUTPUT ON; DECLARE lv_init_msg_list VARCHAR2(200) DEFAULT fnd_api.g_true; lv_return_status VARCHAR2(200); ln_msg_count NUMBER; lv_msg_data VARCHAR2(2000); lv_msg VARCHAR2(2000); ln_contact_point_id NUMBER; ln_object_version_number NUMBER; lr_contact_point_rec_type hz_contact_point_v2pub.contact_point_rec_type; lr_phone_rec_type hz_contact_point_v2pub.phone_rec_type; BEGIN lr_contact_point_rec_type.actual_content_source := hz_contact_point_v2pub.g_miss_content_source_type; lr_contact_point_rec_type.owner_table_name := 'HZ_PARTIES'; lr_contact_point_rec_type.owner_table_id := 1234; lr_contact_point_rec_type.contact_point_type := 'PHONE'; lr_contact_point_rec_type.created_by_module := 'TCA_V2_API'; lr_contact_point_rec_type.primary_flag := 'N'; lr_phone_rec_type.phone_line_type := 'GEN'; lr_phone_rec_type.phone_area_code := '+91'; lr_phone_rec_type.phone_number := '1234567890'; hz_contact_point_v2pub.create_phone_contact_point ( p_init_msg_list => lv_init_msg_list, p_contact_point_rec => lr_contact_point_rec_type, p_phone_rec => lr_phone_rec_type, x_contact_point_id => ln_contact_point_id, x_return_status => lv_return_status, x_msg_count => ln_msg_count, x_msg_data => lv_msg_data ); dbms_output.put_line ('API Status: ' || lv_return_status); IF (lv_return_status <> 'S') THEN dbms_output.put_line ('ERROR :' || lv_msg_data); END IF; dbms_output.put_line ('Create Phone is completed'); COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line ('Error:' || SQLERRM); ROLLBACK; END;


Sunday, May 9, 2021 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.