cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

UpgradeManager Oracle Query help

Participant

UpgradeManager Oracle Query help

One of the migrators in 9.0 M030 is taking 7 hours to delete some records. I can see the Query it is trying to run, and have written an alternative that takes about a minute to complete. I'm not an Oracle expert, but was able to find the problem and an alternative using Google. The migrator still took almost 7 hours even with the records already deleted.

Any suggestions for helping out ptc's query from the Oracle side?

maybe creating an index that might help, or telling Oracle to run my query if they see PTC's?

Their query:
DELETE FROM WTAclEntry WHERE classnamekeyA3='wt.org.WTGroup' AND idA3A3 NOT IN (SELECT idA2A2 FROM WTGroup WHERE ((disabled=0) OR (disabled IS NULL)))


My query:
DELETE FROM WTAclEntry wta WHERE classnamekeyA3='wt.org.WTGroup' and not exists (SELECT idA2A2 FROM WTGroup wtg WHERE ( ( (wtg.disabled=0) or ( wtg.disabled is null) ) and wtg.ida2a2 = wta.ida3a3))

Oracle says something like full table scan for both tables in ptc's query.
We have some 1,292,963 wtgroup entries in the WTAclEntry table.
There are some 17,903 WTAclEntry table records that are for disabled groups.

-=wes


Wes Tucker
Software Engineer
-
_____


PELCO * 3500 Pelco Way * Clovis * CA * 93612-5699



- ------------------------------------------------------------------------------
Confidentiality Notice: The information contained in this transmission is legally privileged and confidential, intended only for the use of the individual(s) or entities named above. This email and any files transmitted with it are the property of Pelco. If the reader of this message is not the intended recipient, or an employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any review, disclosure, copying, distribution, retention, or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful. If you receive this communication in error, please notify us immediately by telephone call to +1-559-292-1981 or forward the e-mail to - and then permanently delete the e-mail and destroy all soft and hard copies of the message and any attachments. Thank you for your cooperation.
- ------------------------------------------------------------------------------
2 REPLIES 2
Highlighted

AW: UpgradeManager Oracle Query help

For all of you, who can't wait till PTC has changed the code, create
following 2 indexes before executing New Access Permission Migrator



create index pdmlink.wtaclentry$classnamkey3ida3a3 on
pdmlink.wtaclentry(classnamekeya3,ida3a3);

create index pdmlink.wtgroup$ida2a2disabled on
pdmlink.wtgroup(ida2a2,disabled);



After the New Access Permission Migrator task has finished, drop the
indexes.



drop index pdmlink.wtaclentry$classnamkey3ida3a3;

drop index pdmlink.wtgroup$ida2a2disabled;



We have 400,000 wtgroup entries in WTAclEntry and with the 2 indexes
created, the statements finished in 15 minutes. Attached you can find a
performance comparison.



/

Jakob





_____

RE: UpgradeManager Oracle Query help

Wes,

I don't know about the specific query you mentioned, however we discovered that if we gather Oracle statistics between certain UpgradeManager steps, it saves hours of time. I suspect this is because the migrator is creating new tables and indices as it goes. We gather statistics before the standalone migrator step and before the server-based migrators. For us, this makes the difference between the Family Table Migrator (part 2) taking 15 minutes vs. more than 5 hours. You might want to try this approach and see if it helps.

-Charlie

Announcements
LiveWorx Call For Papers Happening Now!