Solved

How to optimize this query ?

Posted on 2014-01-19
14
327 Views
Last Modified: 2014-02-03
Hi expert,
i  have below query. my question  is  in below query i have all the function to get display value
but the problem is query is taking more time when i am pulling from data base.
how i can optimize this query. because every time  this query is hitting to data base .so  only   i am thinking it is taking more time.

below is my query??


note: but expert i cant change any thing in my function.
if i will not use this function i.e ok and if we have any alternative  its  ok but we canot change any thing in this function.

so please help me in this.

select
 ms_apps_utilities.get_display_value(100000,'MS SRA VISIT TYPE',visit_type) visit_type,
 ms_apps_utilities.get_display_value(100000,'MS SRA VISIT STATUS', visit_status) visit_status_name
 from MS_SRA_VISIT_SETUP ;

 ms_apps_utilities.get_display_value(100000,'MS SRA VISIT TYPE',visit_type) visit_type,
 ms_apps_utilities.get_display_value(100000,'MS SRA VISIT STATUS', visit_status) visit_status_name
 from MS_SRA_VISIT_SETUP ;

Open in new window


below is the the function n
FUNCTION get_display_value (
      i_enterprise_id     IN   NUMBER DEFAULT NULL,
      i_lov_name          IN   VARCHAR2,
      i_stored_value      IN   VARCHAR2,
      i_org_entity_id     IN   NUMBER DEFAULT 1,
      i_locale_id         IN   NUMBER DEFAULT NULL,
      i_enterprise_name   IN   VARCHAR2 DEFAULT NULL
   )
      RETURN VARCHAR2
   IS
      x_display_value   ms_qs_list_of_values.displayed_value%TYPE;
      x_enterprise_id   NUMBER                             := i_enterprise_id;
   BEGIN
      IF (i_enterprise_id IS NULL AND i_enterprise_name IS NULL)
      THEN
         RETURN NULL;
      ELSIF (i_enterprise_id IS NULL AND i_enterprise_name IS NOT NULL)
      THEN
         x_enterprise_id := get_enterprise_id (i_enterprise_name);
      END IF;

      --This statement finds the Enterprise name if the lov name is enterprise.
      IF (i_lov_name = ms_apps_constants.g_lov_enterprise)
      THEN
         SELECT enterprise_name
           INTO x_display_value
           FROM si_ent_v
          WHERE enterprise_id = i_stored_value;

         RETURN x_display_value;
      --This statement finds the org entity name if the lov name is orgentity.
      ELSIF (i_lov_name = ms_apps_constants.g_lov_orgentity)
      THEN
         SELECT org_entity_name
           INTO x_display_value
           FROM si_org_entities
          WHERE org_entity_id = i_stored_value
            AND enterprise_id = x_enterprise_id;

         RETURN x_display_value;
      --This statement finds the org hierarchy name if the lov name is orghierarchy.
      ELSIF (i_lov_name = ms_apps_constants.g_lov_orghierarchy)
      THEN
         SELECT org_hierarchy_name
           INTO x_display_value
           FROM si_org_hierarchy_levels
          WHERE org_hierarchy_id = i_stored_value
            AND enterprise_id = x_enterprise_id;

         RETURN x_display_value;
      --This statement finds the location name if the lov name is location.
      ELSIF (i_lov_name = ms_apps_constants.g_lov_location)
      THEN
         SELECT location_name
           INTO x_display_value
           FROM si_org_locations
          WHERE location_id = i_stored_value
            AND enterprise_id = x_enterprise_id;

         RETURN x_display_value;
      --This statement gives the user name if the lov name is user.
      ELSIF (i_lov_name = ms_apps_constants.g_lov_user)
      THEN
         SELECT user_name
           INTO x_display_value
           FROM si_users
          WHERE user_id = i_stored_value AND enterprise_id = x_enterprise_id;

         RETURN x_display_value;
      --This statement finds the dispalyed value by considering the lov name and stored value.
      ELSE
         SELECT displayed_value
           INTO x_display_value
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = i_org_entity_id
            AND lovvalue.enterprise_id = x_enterprise_id
            AND lovvalue.stored_value = i_stored_value
            AND lovname.enterprise_id = x_enterprise_id
            AND lovname.lov_name = i_lov_name
            AND lovvalue.locale_id =
                   NVL (i_locale_id,
                        NVL (si_db_locale_sv.getvalue,
                             ms_apps_constants.g_default_locale_id
                            )
                       );

         RETURN x_display_value;
      END IF;
   EXCEPTION
      WHEN OTHERS
      THEN
         RETURN NULL;
   END get_display_value;

Open in new window

if further information is required please let me know.

Thanks
Thomos
0
Comment
Question by:deve_thomos
  • 8
  • 6
14 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39792958
Remember "faster" isn't a tuning goal.

How long is it currently taking and what is your goal?

First:  Make sure your statistics are up to date.

>>its  ok but we canot change any thing in this function.

Odds are it is the function taking the time.   If you cannot change the function then your options are pretty limited.

I would start off generating execution plans on the individual select statements that are inside the function and tune them individually.  Once you get them as good as they can be, if it is still to slow, add more hardware.
0
 

Author Comment

by:deve_thomos
ID: 39793586
I think I asked the question little wrong.

Here is what I am looking for.

My Original SQL is as below:

select 
 ms_apps_utilities.get_display_value(100000,'MS SRA VISIT TYPE',visit_type) visit_type,
 ms_apps_utilities.get_display_value(100000,'MS SRA VISIT STATUS', visit_status) visit_status
 from MS_SRA_VISIT_SETUP ;

Open in new window


Now my visit_type and visit_status columns are having the stored values i.e. in number.

I want to get the store value for those records.

The function internally calls the below SQL :

 SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name
           -- INTO x_display_value
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
           -- AND LOVVALUE.STORED_VALUE = I_STORED_VALUE
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name in ('MS SRA VISIT TYPE','MS SRA VISIT STATUS' ) --i_lov_name
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            )
                      -- )
                   
                       ;
                       

Open in new window


This gives me the below result :

display values result

Now for example my main table i.e.  "MS_SRA_VISIT_SETUP"  is having 100 records and 50 for visit_status and 50 records for visit_type .

Now the function is getting executed 100 times to get the display  values.

What I am looking for is is there any way we can run the below SQL inside and fucntion or procedure or package where I will get storing the result of the display values in buffer and when ever I execute the main SQL I should not execute the function 100 times it should execute once and store the values.

later on by comparing if the column is visit type then pull the display  values from buffer against the store values and same for visit_status column too.


Keep in buffer for store values:
 SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name
           -- INTO x_display_value
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
           -- AND LOVVALUE.STORED_VALUE = I_STORED_VALUE
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name in ('MS SRA VISIT TYPE','MS SRA VISIT STATUS' ) --i_lov_name
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            )
                      -- )
                   
                       ;

Open in new window



Hope this time I am litter clear.
0
 

Author Comment

by:deve_thomos
ID: 39796063
Hello Experts,

Can you guide me which will be better to have using a with clause or a function using result cache :

Using WITH:

WITH dis AS 
(SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name 
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name IN ('MS SRA VISIT TYPE','MS SRA VISIT STATUS' ) --i_lov_name
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            )
  )
  SELECT 
 (SELECT DISPLAYED_VALUE FROM dis WHERE STORED_VALUE = visit_type AND lov_name ='MS SRA VISIT TYPE') visit_type,
 (select DISPLAYED_VALUE from dis where STORED_VALUE = visit_status and lov_name ='MS SRA VISIT STATUS')  visit_status_name
 FROM MS_SRA_VISIT_SETUP;

Open in new window


UNING FUNCTION:

create or replace 
FUNCTION get_display_value (
                               p_lov_name IN ms_qs_lov_org_values_v.lov_name%TYPE 	
                               ,p_stored_value IN ms_qs_lov_org_values_v.stored_value%TYPE 
               ) RETURN VARCHAR2
                                RESULT_CACHE
                                 RELIES_ON (ms_qs_list_of_values) IS
       v_display_val VARCHAR2(4000);
    BEGIN
 
     SELECT DISPLAYED_VALUE into v_display_val
     
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name =p_lov_name --IN ('MS SRA VISIT TYPE','MS SRA VISIT STATUS' ) --i_lov_name
            and LOVVALUE.STORED_VALUE =  p_stored_value
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            );
 
      RETURN v_display_val ;
      end;
      
SQL>   select 
 get_display_value('MS SRA VISIT TYPE',visit_type) visit_type,
 get_display_value('MS SRA VISIT STATUS', visit_status) visit_status_name
 from MS_SRA_VISIT_SETUP ;

Open in new window


Out of the two which will execute once for getting the display value ?  And would be faster .

Or can you suggest me any other method ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39798113
My 'guess' is the WITH will execute faster.  The best way to answer this is to test it for yourself.

If the values don't change often then the result cache might be quicker but I've had issues with the result cache returning incorrect results using 11.2.0.2.  Hopefully they have fixed the bug in a later release but cannot move to a later release right now.

A lot to do with tuning is theory.  When it comes down to it you needs to test against your system since most systems behave differently.
0
 

Author Comment

by:deve_thomos
ID: 39799521
Both are taking the same time and also the number if execution on the base SQL are also same based on the records present on the tables .

Can we do something like pulling all the lovs in to a collection and match it based on the status  and type form the SQL .. may not be possible in single SQL but can anyone provide some examples on this in PLSQL ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39799866
You can do whatever you want.

If you are generating a list of values for several objects at the same time, I would probably generate one XML document with all of them then parse out the individual lists at the app level.

If you can provide a simple test case with sample data and expected results we can probably come up with an example for you.

I never saw an answer to the question I asked above:
How long is it currently taking and what is your goal?

Without setting a realistic goal what you are after may never be 'fast enough'.
0
 

Author Comment

by:deve_thomos
ID: 39799956
My intension is just that the main SQL which is used to get the display values for two lov name s should hit the DB only once.

Here is the sample data.

/*create table script*/

CREATE TABLE MY_TEST
  (
    ID      VARCHAR2(100 CHAR) ,
    NAME    VARCHAR2(2000 CHAR) ,
    ID_TYPE VARCHAR2(100 CHAR)
  ) ;
/
CREATE TABLE MY_TARGET_TABLE
  (
    VISIT_STATUS VARCHAR2(100) ,
    VISIT_TYPE   VARCHAR2(100)
  );
/



/*Insert script for my_test*/
begin
delete from my_test;
Insert into my_test (ID,NAME,ID_TYPE) values ('1','Compliance Team','MS SRA VISIT TYPE');
Insert into my_test (ID,NAME,ID_TYPE) values ('1','Pre-Audit - Audit Planned','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('6','Pending Final Report','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('10','CAP Pending Review','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('2','EHS Team','MS SRA VISIT TYPE');
Insert into my_test (ID,NAME,ID_TYPE) values ('3','LHR Team','MS SRA VISIT TYPE');
Insert into my_test (ID,NAME,ID_TYPE) values ('2','Pre-Audit - Facility Profile Requested','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('3','Pre-Audit - Facility Profile Received','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('4','Audit In Progress','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('5','Pending TPA Submission','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('D','Audit Cancelled','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('8','CAP Requested Due','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('9','CAP Requested PAST Due','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('11','CAP Rejected','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('12','CAP Approved','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('13','CAP Closed Pending CAV','MS SRA VISIT STATUS');
Insert into my_test (ID,NAME,ID_TYPE) values ('14','CAP Closed','MS SRA VISIT STATUS');
commit;
end;

/*Insert script for my_target_table*/


begin
delete from my_target_table;
Insert into my_target_table (VISIT_STATUS,VISIT_TYPE) values ('4','1');
Insert into my_target_table (VISIT_STATUS,VISIT_TYPE) values ('D','1');
Insert into my_target_table (VISIT_STATUS,VISIT_TYPE) values ('D','1');
Insert into my_target_table (VISIT_STATUS,VISIT_TYPE) values ('D','2');
Insert into my_target_table (VISIT_STATUS,VISIT_TYPE) values ('5','3');
Insert into my_target_table (VISIT_STATUS,VISIT_TYPE) values ('D',null);
Insert into my_target_table (VISIT_STATUS,VISIT_TYPE) values ('2',null);
commit;
end;

Open in new window


My expected result would be like the result of the below SQL:

select (select name from my_test where id_type ='MS SRA VISIT STATUS' and id=a.visit_status )visit_status,
(select name from my_test where id_type ='MS SRA VISIT TYPE' and id=a.visit_type )visit_type
from my_target_table a
;

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39800705
Thanks for the text example.  I now understand what you are after.

I really wouldn't have designed the LOV table this way.

Do you have a multi-column index on my_test(id_type,id)?  If not, can you try one?

I have a way to only hit the tables once using XML but it actually performed worse than the SQL you provided so I really don't want to provide it.  I'm still playing around to see what other tricks I might be able to come up with.

Can you post the execution plan for the SQL you have against the tables you have?

from a SQL prompt:
explain plan for
select ...the rest of your select
/

Then:
select * from table(dbms_xplan.display);


No images or screen shots.  Just paste the text.
0
 

Author Comment

by:deve_thomos
ID: 39800760
>>Do you have a multi-column index on my_test(id_type,id)?  If not, can you try one?

No I don't.

Its not about making the SQL faster . The objective is to make this in such a way that ..
We should be able to hit the DB once for getting the name from 2 or 3 or n number of types.
Then from the buffer we should be able get the display values out of the buffer against the store value.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39800788
>>Then from the buffer we should be able get the display values out of the buffer against the store value.

The values are likely already in memory when you query them form the database.  This is what Oracle does:  It keeps frequently access blocks in memory to reduce physical reads from disk.  So reducing the number of database calls probably won't buy you much.

Now if you are making the database calls from a remote application like a website, then caching the data locally might help performance but only if the network between the application and database server is slow.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39800921
I added 10,000 rows to my_test in your test case above and the following index seems to make a pretty big difference on the SQL you provided:
create index my_test_idx on my_test(id,id_type);
0
 

Author Comment

by:deve_thomos
ID: 39801966
There will be maximum 200 records in "my_test" table but there would be 1750 records in my_target_table.

So my name and values details are stored in  "my_test" table but the main visit details table is the my target table.
0
 

Author Comment

by:deve_thomos
ID: 39802656
Here is my main SQL which I am using :

/*Checking the explain plan on sample visit setup table which has almost 1800 records */

explain plan for
SELECT 
 MS_APPS_UTILITIES.GET_DISPLAY_VALUE(100000,'MS SRA VISIT TYPE',VISIT_TYPE) VISIT_TYPE,
 MS_APPS_UTILITIES.GET_DISPLAY_VALUE(100000,'MS SRA VISIT STATUS', VISIT_STATUS) VISIT_STATUS
 from SR_MS_SRA_VISIT_SETUP ;
 
 
 
SELECT PLAN_TABLE_OUTPUT  
      FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',NULL,'typical +outline')); 


Plan hash value: 132758680
 
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |  1988 |   784K|    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| SR_MS_SRA_VISIT_SETUP |  1988 |   784K|    12   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

/*Now creted a view MS_SRA_DISPLAY_LOV without an index */
 CREATE VIEW ms_sra_display_lov as 
SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name 
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
           AND lovvalue.locale_id = NVL (si_db_locale_sv.getvalue,1009 );
 
 
 
 /*Now checking the plan */
 explain plan for 
select (select displayed_value from ms_sra_display_lov where lov_name ='MS SRA VISIT STATUS' and stored_value=a.visit_status )visit_status,
(select displayed_value from ms_sra_display_lov where lov_name ='MS SRA VISIT TYPE' and stored_value=a.visit_type )visit_type
from sr_ms_sra_visit_setup a; 
           
           
           
 Plan hash value: 723341790
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |  1988 |   784K|    12   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                |                         |     1 |  4306 |    12   (9)| 00:00:01 |
|*  2 |   HASH JOIN                     |                         |     1 |  2278 |    10  (10)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL            | MS_QS_LOV_NAMES         |     1 |    34 |     2   (0)| 00:00:01 |
|   4 |    VIEW                         |                         |     5 | 11220 |     7   (0)| 00:00:01 |
|   5 |     NESTED LOOPS                |                         |     5 |   205 |     7   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL          | MS_QS_LIST_OF_VALUES_T  |     5 |   175 |     6   (0)| 00:00:01 |
|   9 |   TABLE ACCESS FULL             | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|* 10 |  HASH JOIN OUTER                |                         |     1 |  4306 |    12   (9)| 00:00:01 |
|* 11 |   HASH JOIN                     |                         |     1 |  2278 |    10  (10)| 00:00:01 |
|* 12 |    TABLE ACCESS FULL            | MS_QS_LOV_NAMES         |     1 |    34 |     2   (0)| 00:00:01 |
|  13 |    VIEW                         |                         |     5 | 11220 |     7   (0)| 00:00:01 |
|  14 |     NESTED LOOPS                |                         |     5 |   205 |     7   (0)| 00:00:01 |
|* 15 |      TABLE ACCESS BY INDEX ROWID| SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|* 16 |       INDEX UNIQUE SCAN         | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|* 17 |      TABLE ACCESS FULL          | MS_QS_LIST_OF_VALUES_T  |     5 |   175 |     6   (0)| 00:00:01 |
|  18 |   TABLE ACCESS FULL             | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|  19 |  table access full              | sr_ms_sra_visit_setup   |  1988 |   784K|    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------



/*Now creating the index on the view then check the plan */

-- Now making a virtual index on the view
ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

--

create index ms_sra_lov_idx_1 on ms_qs_lov_names(lov_name);


Plan hash value: 2585871074
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                         |  1988 |   784K|    12   (0)| 00:00:01 |
|*  1 |  HASH JOIN OUTER                |                         |     1 |  4306 |    11  (10)| 00:00:01 |
|*  2 |   HASH JOIN                     |                         |     1 |  2278 |     9  (12)| 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID  | MS_QS_LOV_NAMES         |     1 |    34 |     1   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN            | MS_SRA_LOV_IDX_1        |     1 |       |     1   (0)| 00:00:01 |
|   5 |    VIEW                         |                         |     5 | 11220 |     7   (0)| 00:00:01 |
|   6 |     NESTED LOOPS                |                         |     5 |   205 |     7   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      TABLE ACCESS FULL          | MS_QS_LIST_OF_VALUES_T  |     5 |   175 |     6   (0)| 00:00:01 |
|  10 |   TABLE ACCESS FULL             | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|* 11 |  HASH JOIN OUTER                |                         |     1 |  4306 |    11  (10)| 00:00:01 |
|* 12 |   HASH JOIN                     |                         |     1 |  2278 |     9  (12)| 00:00:01 |
|* 13 |    TABLE ACCESS BY INDEX ROWID  | MS_QS_LOV_NAMES         |     1 |    34 |     1   (0)| 00:00:01 |
|* 14 |     INDEX RANGE SCAN            | MS_SRA_LOV_IDX_1        |     1 |       |     1   (0)| 00:00:01 |
|  15 |    VIEW                         |                         |     5 | 11220 |     7   (0)| 00:00:01 |
|  16 |     NESTED LOOPS                |                         |     5 |   205 |     7   (0)| 00:00:01 |
|* 17 |      TABLE ACCESS BY INDEX ROWID| SI_LOCALES              |     1 |     6 |     1   (0)| 00:00:01 |
|* 18 |       INDEX UNIQUE SCAN         | SI_LOCALES_U1           |     1 |       |     1   (0)| 00:00:01 |
|* 19 |      TABLE ACCESS FULL          | MS_QS_LIST_OF_VALUES_T  |     5 |   175 |     6   (0)| 00:00:01 |
|  20 |   TABLE ACCESS FULL             | MS_QS_LIST_OF_VALUES_TL |     1 |  2028 |     2   (0)| 00:00:01 |
|  21 |  TABLE ACCESS FULL              | SR_MS_SRA_VISIT_SETUP   |  1988 |   784K|    12   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------


-- No change seen because of the STORED_VALUE as this is also retrived from another view.

So cannot make the index on both columns.

/*Checking the function on RESULT CACHE */



create or replace 
FUNCTION get_display_value (
                               p_lov_name IN ms_qs_lov_org_values_v.lov_name%TYPE 	
                               ,p_stored_value IN ms_qs_lov_org_values_v.stored_value%TYPE 
               ) RETURN VARCHAR2
                                RESULT_CACHE
                                 RELIES_ON (ms_qs_list_of_values) IS
       v_display_val VARCHAR2(4000);
    BEGIN
 
     SELECT DISPLAYED_VALUE into v_display_val
     
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name =p_lov_name 
            and LOVVALUE.STORED_VALUE =  p_stored_value
            AND lovvalue.locale_id =
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            );
 
      RETURN v_display_val ;
      end;
      


/*Now let us check the plan*/

Plan hash value: 132758680
 
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |  1988 |   784K|    12   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| SR_MS_SRA_VISIT_SETUP |  1988 |   784K|    12   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 



/*Using a with clause */


explain plan for 
WITH dis AS 
(SELECT DISPLAYED_VALUE , LOVVALUE.STORED_VALUE , lovname.lov_name 
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = 1
            AND LOVVALUE.ENTERPRISE_ID = 100000
            AND LOVNAME.ENTERPRISE_ID = 100000
            AND lovname.lov_name IN ('MS SRA VISIT TYPE','MS SRA VISIT STATUS' ) --i_lov_name
            AND lovvalue.locale_id =
                   --NVL (NULL,
                        NVL (si_db_locale_sv.getvalue,
                             1009
                            )
  )
  SELECT 
 (SELECT DISPLAYED_VALUE FROM dis WHERE STORED_VALUE = visit_type AND lov_name ='MS SRA VISIT TYPE') visit_type,
 (select DISPLAYED_VALUE from dis where STORED_VALUE = visit_status and lov_name ='MS SRA VISIT STATUS')  visit_status_name
 FROM sr_ms_sra_visit_setup;
 
 

Plan hash value: 2974812435
 
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                            |  1988 |   784K|    24   (5)| 00:00:01 |
|*  1 |  VIEW                             |                            |     9 | 21654 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL               | SYS_TEMP_0FD9D69EF_9B5B5EE |     9 | 18288 |     3   (0)| 00:00:01 |
|*  3 |  VIEW                             |                            |     9 | 21654 |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL               | SYS_TEMP_0FD9D69EF_9B5B5EE |     9 | 18288 |     3   (0)| 00:00:01 |
|   5 |  TEMP TABLE TRANSFORMATION        |                            |       |       |            |          |
|   6 |   LOAD AS SELECT                  | SYS_TEMP_0FD9D69EF_9B5B5EE |       |       |            |          |
|*  7 |    HASH JOIN RIGHT OUTER          |                            |     9 | 36972 |    12   (9)| 00:00:01 |
|   8 |     TABLE ACCESS FULL             | MS_QS_LIST_OF_VALUES_TL    |     1 |  2028 |     2   (0)| 00:00:01 |
|*  9 |     HASH JOIN                     |                            |     9 | 18720 |    10  (10)| 00:00:01 |
|* 10 |      TABLE ACCESS FULL            | MS_QS_LOV_NAMES            |     2 |    68 |     2   (0)| 00:00:01 |
|  11 |      VIEW                         |                            |  1304 |  2605K|     7   (0)| 00:00:01 |
|  12 |       NESTED LOOPS                |                            |  1304 | 53464 |     7   (0)| 00:00:01 |
|* 13 |        TABLE ACCESS BY INDEX ROWID| SI_LOCALES                 |     1 |     6 |     1   (0)| 00:00:01 |
|* 14 |         INDEX UNIQUE SCAN         | SI_LOCALES_U1              |     1 |       |     1   (0)| 00:00:01 |
|* 15 |        TABLE ACCESS FULL          | MS_QS_LIST_OF_VALUES_T     |  1304 | 45640 |     6   (0)| 00:00:01 |
|  16 |   TABLE ACCESS FULL               | SR_MS_SRA_VISIT_SETUP      |  1988 |   784K|    12   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

Open in new window




Here I have tried all the possible ways to get the better one.


But  the main SQL is ok for me the only issue is if the repords in my visit table is having 100 then 100 times the "MS_APPS_UTILITIES.GET_DISPLAY_VALUE" function is been called and also shows in the AWR report as maximum number of CPUs are been utilized by this SQL which is under this function i.e. :

SELECT displayed_value
           INTO x_display_value
           FROM ms_qs_list_of_values lovvalue, ms_qs_lov_names lovname
          WHERE lovvalue.lov_id = lovname.lov_id
            AND lovvalue.org_entity_id = i_org_entity_id
            AND lovvalue.enterprise_id = x_enterprise_id
            AND lovvalue.stored_value = i_stored_value
            AND lovname.enterprise_id = x_enterprise_id
            AND lovname.lov_name = i_lov_name
            AND lovvalue.locale_id =
                   NVL (i_locale_id,
                        NVL (si_db_locale_sv.getvalue,
                             ms_apps_constants.g_default_locale_id
                            )
                       );

Open in new window

0
 

Author Closing Comment

by:deve_thomos
ID: 39829550
thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now