Home » Open Source » Programming Interfaces » Anybody using worldship and UPS - import performance brutal (11.5.10.2 apps / 12c database - Linux 6)
Anybody using worldship and UPS - import performance brutal [message #685278] Fri, 03 December 2021 08:39 Go to next message
ksd
Messages: 5
Registered: December 2021
Junior Member
Hello, we've been troubleshooting this for a month. Seems UPS is scanning the sys.all_tables view.
We have a UPS db user that we've mapped to kad_ups_import to the UPS fields using latest progress ODBC driver.
ODBC connects instantly upon testing. But worldship is brutal. Takes 5 min for the import box to come up, if it doesn't time out.

Setup
UPS schema has 2 synonyms that point to KADANT.KAD_UPS_IMPORT table

This is the SQL that seems to hang from within ups



SELECT a.owner,
a.synonym_name,
'SYNONYM',
NULL
FROM sys.all_synonyms a, sys.all_tables b
WHERE a.table_owner = b.owner
AND a.table_name = b.table_name
AND a.owner LIKE 'KADANT' ESCAPE '\'
AND a.synonym_name LIKE 'KAD_UPS_IMPORT' ESCAPE '\'
AND a.table_name NOT LIKE 'BIN$%'
UNION ALL
SELECT a.owner,
a.synonym_name,
'SYNONYM',
NULL
FROM sys.all_synonyms a, sys.all_views b
WHERE a.table_owner = b.owner
AND a.table_name = b.view_name
AND a.owner LIKE 'KADANT' ESCAPE '\'
AND a.synonym_name LIKE 'KAD_UPS_IMPORT' ESCAPE '\'
AND a.table_name NOT LIKE 'BIN$%'
UNION ALL
SELECT a.owner,
a.synonym_name,
'SYNONYM',
NULL
FROM sys.all_synonyms a
WHERE EXISTS
(SELECT *
FROM sys.all_db_links b
WHERE a.db_link = b.db_link
AND (b.owner = 'PUBLIC' OR b.owner = USER))
AND a.owner LIKE 'KADANT' ESCAPE '\'
AND a.synonym_name LIKE 'KAD_UPS_IMPORT' ESCAPE '\'
AND a.table_name NOT LIKE 'BIN$%'

Re: Anybody using worldship and UPS - import performance brutal [message #685280 is a reply to message #685278] Fri, 03 December 2021 13:58 Go to previous messageGo to next message
John Watson
Messages: 8783
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

THis is a well known bug (at least, "well known" if you've hit it before). THere may be patches for your DB release, otherwise the answer is not to use all_synonyms. For example,

https://mikedietrichde.com/2015/12/22/query-on-all_synonyms-is-slow-in-oracle-database-12c/

When I hit the problem, I did reverse engineer it: It was because the view does a check on synonyms pointing to synonyms which can get horribly recursive.
Re: Anybody using worldship and UPS - import performance brutal [message #685281 is a reply to message #685280] Fri, 03 December 2021 14:09 Go to previous messageGo to next message
ksd
Messages: 5
Registered: December 2021
Junior Member
So you are using worldship as well?
We're at a loss of how to fix this. We have the kadant.kad_ups_import table and a ups user with synonym pointing to it. I don't know why they did it this way. I'm wondering if it would be better to just go direct to the kadant user, instead of using the ups user with synonyms pointing to the kadant.kad_ups_import table.

I tried to follow the instructions in
https://www.orafaq.com/forum/t/119899/

But I don't really know what I'm doing
In the ups schema, with the synonyms, I created tables
UPS.ALL_OBJECTS, UPS.ALL_SYNONYMS, UPS.ALL_TABLES, , UPS.ALL_TAB_COLUMNS from the same titled SYS views, but only have the kadant.kad_ups_import data in the tables, nothing else. I don't know if this is what I was supposed to do.

If anyone has any other guidance regarding this, I would appreciate it. We're doing like 300 shipments a day and it takes about 2 minutes for the keyed import box to come up in Worldship. We're getting killed.
Re: Anybody using worldship and UPS - import performance brutal [message #685283 is a reply to message #685281] Sat, 04 December 2021 01:18 Go to previous messageGo to next message
John Watson
Messages: 8783
Registered: January 2010
Location: Global Village
Senior Member
Have you applied the solutions I pointed you towards?
Re: Anybody using worldship and UPS - import performance brutal [message #685299 is a reply to message #685283] Mon, 06 December 2021 07:10 Go to previous messageGo to next message
ksd
Messages: 5
Registered: December 2021
Junior Member
I'm not much of a sql guy. Tried this in toad, it doesn't work.
BEGIN
dbms_stats.gather_table_stats('SYS','OBJ$',estimate_percent
=>100, method_opt => 'for columns flags size 1, spare3 size 254, type# size 254');
END;

Re: Anybody using worldship and UPS - import performance brutal [message #685300 is a reply to message #685299] Mon, 06 December 2021 07:11 Go to previous messageGo to next message
ksd
Messages: 5
Registered: December 2021
Junior Member
Error attached
Re: Anybody using worldship and UPS - import performance brutal [message #685301 is a reply to message #685300] Mon, 06 December 2021 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You have a bug in TOAD.
Use SQL*Plus.

Re: Anybody using worldship and UPS - import performance brutal [message #685303 is a reply to message #685301] Mon, 06 December 2021 07:53 Go to previous message
ksd
Messages: 5
Registered: December 2021
Junior Member
So this is for any session once completed, correct?

SQL> ed
Wrote file afiedt.buf

1 BEGIN
2 dbms_stats.gather_table_stats('SYS','OBJ$',estimate_percent
3 =>100, method_opt => 'for columns flags size 1, spare3 size 254, type# size 254');
4* END;
SQL> /

PL/SQL procedure successfully completed.
Previous Topic: VBScript(ASP): Update from Image File data to Blob Value into Oracle DB table
Next Topic: OIC shared on network drive
Goto Forum:
  


Current Time: Thu Dec 08 10:14:16 CST 2022