parse/execute/fetch in tkprof

[satish]

Dear erman,

Our version R12.2.5
DB version 12.1.0.2

Below sql identified in tkprof of a long running concurrent program.Can you please suggest how can we tune this

SQL ID: 67264jh510f32 Plan Hash: 368250971

SELECT description FROM FND_FLEX_VALUES_VL WHERE flex_value = :seg_val
  AND flex_value_set_id = :vset_id


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse   29398824    184.91     186.19          0          0          0           0
Execute 29398823    489.00     482.94          0          2          0           0
Fetch   29398824    421.24     418.72         22  235190814          0    29398824
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   88196471   1095.15    1087.86         22  235190816          0    29398824

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173  (APPS)   (recursive depth: 2)
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  NESTED LOOPS  (cr=8 pr=1 pw=0 time=7791 us cost=6 size=83 card=1)
         1          1          1   NESTED LOOPS  (cr=7 pr=1 pw=0 time=4922 us cost=6 size=83 card=1)
         1          1          1    TABLE ACCESS BY INDEX ROWID BATCHED FND_FLEX_VALUES (cr=4 pr=0 pw=0 time=2992 us cost=4 size=38 card=1)
         1          1          1     INDEX RANGE SCAN FND_FLEX_VALUES_N1 (cr=3 pr=0 pw=0 time=28 us cost=3 size=0 card=1)(object id 398655)
         1          1          1    INDEX UNIQUE SCAN FND_FLEX_VALUES_TL_U1 (cr=3 pr=0 pw=0 time=1928 us cost=1 size=0 card=1)(object id 544756)
         1          1          1   TABLE ACCESS BY INDEX ROWID FND_FLEX_VALUES_TL (cr=1 pr=0 pw=0 time=2865 us cost=2 size=45 card=1)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   NESTED LOOPS
      1    NESTED LOOPS
      1     TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID BATCHED) OF
                'FND_FLEX_VALUES' (TABLE)
      1      INDEX   MODE: ANALYZED (RANGE SCAN) OF
                 'FND_FLEX_VALUES_N1' (INDEX)
      1     INDEX   MODE: ANALYZED (UNIQUE SCAN) OF
                'FND_FLEX_VALUES_TL_U1' (INDEX (UNIQUE))
      1    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF
               'FND_FLEX_VALUES_TL' (TABLE)

********************************************************************************

SQL ID: 27kxk9bpbnsqg Plan Hash: 1570865191


Thanks,
SG

[ErmanArslansOracleBlog]

What concurrent program is that?
Is it custom?
If it is custom, did you /or your developers write that query ? If so, can you just bypass this standard  FND_FLEX_VALUES_VL view and get the description from the tables that this view relies on.. It can be seen from the execution plan, that view goes to  FND_FLEX_VALUES and FND_FLEX_VALUES_TL.
This is a standard with and I can tell you anything for modifying it.