Re: Tuning "INSERT as SELECT"
Date: Thu, 2 Feb 2017 14:12:32 +0000
Message-ID: <TY1PR01MB05389100D912B48FB267515ABC4C0_at_TY1PR01MB0538.jpnprd01.prod.outlook.com>
Hi Jonathan / Mladen / Tim and All - tons of thanks for your inputs.
You are on spot . we did a "select count(*) from inline view" :-( Also tested with "+all_rows" hint in sql*developer thinking that might help , but now I feel like that's also not the right way of testing as sql*developer stops with first 50~100 rows. Will let the exact SQL run all the way in sql*plus and see long it takes.Just curious - is there any other way of doing it (don't want to see all the 80m recs in sql*plus) or how would you guys do it ?
>> ... your test starts with a truncate then it will have made the indexes valid again.
Exaclty. we made indexes "unusable" , truncated the table before I ran my tests. Did not imagine that truncate would my indexes valid. Just checked and it was so. Shocked !! Wonder why truncate does so. Thanks a million Jonathan for letting me know that.
>> please turn on SQL tracing using DBMS_MONITOR or .... Everything else is pure witchcraft.
Sure Mladen. Thanks again for the valuable piece of advice.
Will continue with my tests and keep you guys posted.
Regards,
Prem
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 02 2017 - 15:12:32 CET