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
deve_thomosAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
sdstuberCommented:
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?
0
deve_thomosAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
where you have

wm_concat(x)

change it to

LISTAGG(x, ',') WITHIN GROUP (ORDER BY x)


I recognized the json,  can you post some sample data for your tables? and expected output?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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

0
deve_thomosAuthor Commented:
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
0
sdstuberCommented:
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;
0
deve_thomosAuthor Commented:
i will post later...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.