Database Explorer\Procedures takes long time

[Enrico Briozzi]

Hi Tomas,
I'm using SQL Workbench/J build 127.6 with openjdk version "15.0.3"
(64 bit) in Ubuntu 21.04.

I use Oracle Database 12c Standard Edition Release 12.2.0.1.0 - 64bit Production
Driver Name: Oracle JDBC driver
Driver Class: oracle.jdbc.OracleDriver
Driver Version: 12.2.0.1.0

When I go to Database Explorer\Procedures the query to retrieve the
procedure/function takes more than 20 minutes (than I stopped it).
Whit version 127.5 it takes 10 seconds or so.

Can you please check this?
Thank you very much, best regards
Enrico.-

[Thomas Kellerer]

There was indeed a change in the query to retrieve procedures to better handle overloaded functions.

You can disable the custom SQL used by SQL Workbench for this, and let the JDBC driver do the work using:

workbench.db.oracle.procedures.custom_sql=false

That might be a workaround. I will have a look at the query, maybe I can find a way to improve the performance.

Regards
Thomas

[Enrico Briozzi]

Hi Tomas,
your workaround is ok!

Thank you very much, best regards
Enrico.-

Il giorno lun 11 ott 2021 alle ore 19:33 Thomas Kellerer
<google...@sql-workbench.net> ha scritto:

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.
> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/93e23709-92cc-bf75-fa71-12b07d96553f%40sql-workbench.net.

[Thomas Kellerer]

I changed the statement that is used internally.

At least on my test database running 19c this did show some improvement - although even with over 1000 procedures and function, it took only about 4 seconds before.
The new version got this down to less than 1 second.

Would you mind running that on your system to see if that improves things for you as well?

select *
from (
select null as package_name,
user as procedure_owner,
ao.object_name as procedure_name,
null as overload_index,
null as remarks,
decode(ao.object_type, 'PROCEDURE', 1, 'FUNCTION', 2, 0) as PROCEDURE_TYPE,
ao.status,
ap.pipelined
from user_objects ao
left join user_procedures ap on ao.object_name = ap.object_name
where ao.object_type in ('PROCEDURE', 'FUNCTION')
UNION ALL
select package_name, procedure_owner, procedure_name, overload_index, remarks, procedure_type, status, pipelined
from (
select ap.object_name as package_name,
user as procedure_owner,
ap.procedure_name,
ap.overload as overload_index,
decode(ao.object_type, 'TYPE', 'OBJECT TYPE', ao.object_type) as remarks,
decode(aa.anz, 1, 2, 1 ) as procedure_type,
ao.status,
ap.pipelined,
row_number() over (partition by ap.object_name, ap.procedure_name, ap.overload order by ao.object_type desc) as rn
from user_procedures ap
join user_objects ao on ap.object_name = ao.object_name
left join (
select owner, package_name, object_name, overload, count(*) as anz
from all_arguments
where in_out = 'OUT'
and argument_name is null
group by owner, package_name, object_name, overload
) aa on aa.package_name = ap.object_name
and aa.object_name = ap.procedure_name
and coalesce(aa.overload,'none') = coalesce(ap.overload, 'none')
where ao.object_type IN ('PACKAGE BODY', 'PACKAGE', 'TYPE', 'OBJECT TYPE')
and ap.procedure_name is not null
and ap.object_name is not null
)
where rn = 1
)
ORDER BY 2,3,4

[Enrico Briozzi]

Hi Thomas,
your new query run on my system in 5.5 seconds! Good job.

Thank you very much, best regards
Enrico.-

Il giorno dom 17 ott 2021 alle ore 15:09 Thomas Kellerer

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/30679010-f5e8-13f5-9555-7c45e5d24986%40sql-workbench.net.

[Enrico Briozzi]

Hi Thomas,
I want to give you a confirmation: your new build 127.7 is ok! The
query run in near 5 seconds for 38705 procedure/function.

Thank you very much, best regards
Enrico.-

Il giorno dom 17 ott 2021 alle ore 15:09 Thomas Kellerer

> --
> You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/30679010-f5e8-13f5-9555-7c45e5d24986%40sql-workbench.net.

[Goutam Goswami]

Hi  All,

Below is my code is not working in sql workbench for PIVOT function. Can any one give me the solution?

    SELECT * FROM (

    SELECT  A.MSD_CORE_COUNTRY_CODE AS MARKET,  

        A.MSD_CORE_COUNTRY_CODE||'-'||A.MSD_CORE_MDM_ID  AS "ACC MDM ID",                

        RT.NAME AS "ACC TYPE",         

        T.NAME AS "INVIDUAL TYPE",   

        A.NAME AS "ACC NAME", 

        S1.NAME AS "PRIMARY SPECIALTY",

        S1.MSD_CORE_ENGLISH_LABEL AS "PRIMARY SPECIALTY ENGLISH",

        S2.NAME AS "SECONDARY SPECIALTY",

        S2.MSD_CORE_ENGLISH_LABEL AS "SECONDARY SPECIALTY ENGLISH",

        CHNL.MSD_CORE_CHANNEL_PREFERENCE CHANNEL_PREF,

        CHNL.MSD_CORE_CHANNEL_NAME CHANNEL_NAME

    FROM COE_ADMIN.C302_001_ACCOUNT A 

 INNER JOIN 

        (SELECT DISTINCT 

        ID,

        NAME

        FROM COE_ADMIN.C305_025_RECORDTYPE

        WHERE NAME LIKE 'MSD_CORE%Professional') RT  

        ON A.RECORDTYPEID = RT.ID 

        INNER JOIN COE_ADMIN.C302_022_LOOKUP_TABLE_TYPE T

        ON (A.MSD_CORE_COUNTRY_CODE = T.MSD_CORE_COUNTRY_CODE

        AND A.MSD_CORE_MDM_INDIVIDUAL_TYPE = T.ID)

        LEFT JOIN COE_ADMIN.C302_021_LOOKUP_TABLE S1

        ON A.MSD_CORE_COUNTRY_CODE = S1.MSD_CORE_COUNTRY_CODE     

        AND A.MSD_CORE_SPECIALTY_1 = S1.ID    

        AND S1.ISDELETED = 'N'

    LEFT JOIN COE_ADMIN.C302_021_LOOKUP_TABLE S2

        ON A.MSD_CORE_COUNTRY_CODE = S2.MSD_CORE_COUNTRY_CODE    

        AND A.MSD_CORE_SPECIALTY_2 = S2.ID

        AND S2.ISDELETED = 'N' 

       LEFT JOIN  COE_ADMIN.C302_029_MSD_CORE_CHNNL_PRFRNC CHNL

       ON A.MSD_CORE_COUNTRY_CODE = CHNL.MSD_CORE_COUNTRY_CODE AND

       A.ID=CHNL.MSD_CORE_ACCOUNT 

      WHERE  A.MSD_CORE_COUNTRY_CODE IN ('AU')

  )

PIVOT( MAX(CHANNEL_NAME)

    FOR  CHANNEL_PREF IN ('Preference - 1' ,'Preference - 2' ,'Preference - 3','Preference - 4','Preference - 5','Preference - 6','Preference - 7'  ) ) 

[Thomas Kellerer]

This is completely unrelated to with SQL Workbench/J.
You need to ask this in a forum for your database product.

And please do not hijack threads for a completely different topic.

[Goutam Goswami]

To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/b2abdb20-38b8-4800-85a6-cefdddb8f76an%40googlegroups.com.