| Anybody using worldship and UPS - import performance brutal [message #685278] |
Fri, 03 December 2021 08:39  |
 |
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 #685281 is a reply to message #685280] |
Fri, 03 December 2021 14:09  |
|
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 #685303 is a reply to message #685301] |
Mon, 06 December 2021 07:53 |
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
