asked on

how to tune this query ?

Hello Expert,
below is my query. it is taking lots of time . can you please help me how can i optimize this query ?

SELECT utype_fnc(p_uname ,p_visit) INTO l_utype FROM dual;
IF l_utype = 'OL' THEN
  --start of app id
  IF p_app_id = '3' THEN
  WITH setup_aud AS
    (SELECT
      (SELECT org_name
      FROM org_t
      WHERE oid = aud.tid
      )                                                 AS leader__company ,
      aud.leader                                       AS leader ,
      aud.vid                                      AS vid ,
      util.get_user_full_name(aud.leader) AS tpa_leader ,
      aud.is_lead
    FROM setup aud
    WHERE vid = p_visit
    ),
    aud1 AS
    (SELECT vid ,
      '{"'
      || leader__company
      || '":{"leaders":[' AS leader__company ,
      wm_concat(get_audasn_fnc(vid ,leader))
      || ']}}' asn_det
    FROM setup_aud
    GROUP BY leader__company ,
      vid
    )
  SELECT '"leader__company":['
    || wm_concat(leader__company
    || asn_det)
    || ']' ac_aud_ans
  INTO l_result
  FROM aud1
  GROUP BY vid;

Open in new window

Regards
Thomos

Guy Hengel [angelIII / a3]

You will need to find out where exactly the query is slow....
Setup table needs afor example a index on vid column. You will need to learn about EXPLAIN PLAN for queries.

Sean Stuber

first - don't use WM_CONCAT - it's not and never has been a supported function, and unlikely it ever will be

if you are using 11.2.0.1 or higher use LISTAGG

If you are using a lower version please specify with full version (i.e. don't say something like 9 or 9i, say 9.2.0.6)

how many distinct aud.leader values will you return?

what do your other functions do?

deve_thomos

ASKER

Hi expert,
can you please help how can i use listagg here ?

i am using 11.2.0.1 .

actually i am making here Json ouput.

Regards
Thomos

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

something else to consider - isolate the sql from your pl/sql

how long does this query take to run? I've removed your function calls, so the query is just the data. If it's fast, then the functions are you problem.

If it's slow, then the functions might still be a problem, but the query needs work

WITH setup_aud
     AS (SELECT (SELECT org_name
                   FROM org_t
                  WHERE oid = aud.tid)
                    AS leader__company,
                aud.leader AS leader,
                aud.vid AS vid,
                --util.get_user_full_name(aud.leader) AS tpa_leader,
                aud.is_lead
           FROM setup aud
          WHERE vid = p_visit),
     aud1
     AS (  SELECT vid,
                  '{"' || leader__company || '":{"leaders":[' AS leader__company,
                  LISTAGG(vid || leader, ',') WITHIN GROUP (ORDER BY x) || ']}}' asn_det
             FROM setup_aud
         GROUP BY leader__company, vid)
  SELECT    '"leader__company":['
         || LISTAGG(leader__company || asn_det,',') WITHIN GROUP (ORDER BY leader__company || asn_det)
         || ']'
             ac_aud_ans
    FROM aud1
GROUP BY vid;

Open in new window

deve_thomos

ASKER

Hi Ststuber,
here the problem if i am getting data more than 4000 characters in json output, listagg is not working.

Any other way can i optimize this sql ?

Regards
Thomos

Sean Stuber

WITH setup_aud
AS (SELECT (SELECT org_name
FROM org_t
WHERE oid = aud.tid)
AS leader__company,
aud.leader AS leader,
aud.vid AS vid,
--util.get_user_full_name(aud.leader) AS tpa_leader,
aud.is_lead
FROM setup aud
WHERE vid = p_visit),
aud1
AS (SELECT vid,
'{"' || leader__company || '":{"leaders":[' AS leader__company,
RTRIM(
EXTRACT(
XMLAGG(XMLELEMENT("x", vid || leader || ',') ORDER BY vid || leader),
'/x/text()'
).getclobval(),
','
)
|| ']}}'
asn_det
FROM setup_aud
GROUP BY leader__company, vid)
SELECT '"leader__company":['
|| RTRIM(
EXTRACT(
XMLAGG(
XMLELEMENT("x", leader__company || asn_det || ',')
ORDER BY leader__company || asn_det
),
'/x/text()'
).getclobval(),
','
)
|| ']'
ac_aud_ans
FROM aud1
GROUP BY vid;

deve_thomos

ASKER

i will post later...