[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

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
0
deve_thomos
Asked:
deve_thomos
  • 4
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now