RE: Tuning "INSERT as SELECT"
Date: Thu, 2 Feb 2017 07:48:13 -0800 (PST)
Message-ID: <ba732017-07d3-42d4-957e-24f5f68d007c_at_default>
Doesn't everyone have EE + all the goodies?
The tool will prompt you with a warning that you'll need the tuning pack. If you can, get the 2nd EA update - we've updated the interface to hopefully show everything you'd expect. And of course share your feedback to me directly or on our OTN Forums Space.
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent: Thursday, February 02, 2017 10:45 AM
To: oracle-l_at_freelists.org
Subject: Re: Tuning "INSERT as SELECT"
Good point - much more widely available.
Still requires the licences, of course.
Regards
Jonathan Lewis
From: Jeff Smith <jeff.d.smith_at_oracle.com> Sent: 02 February 2017 15:43:19
To: Jonathan Lewis; Prem Khanna J; oracle-l_at_freelists.org Subject: RE: Tuning "INSERT as SELECT"
FWIW, SQL Developer 4.2, currently in EA, also has a new RTSM report viewer.
View > DBA > Tuning.
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent: Thursday, February 02, 2017 10:40 AM
To: Prem Khanna J <jprem_at_outlook.com>; oracle-l_at_freelists.org
Subject: Re: Tuning "INSERT as SELECT"
In the first place I would examine the query to decide whether or not I could identify a path that was likely to be the most efficient. You indicated that the query might only reference one table, in which case that might be quite easy to do ;) but if it's one main table and several others, or if you have scalar subqueries in the select list there may be some scope for doing some mental gymnastics and then seeing whether or not Oracle agrees with your analysis.
If I couldn't work out why Oracle was taking longer than expected - and assuming I had licensed the diagnostic and performance packs I'd take advantage of the SQL Monitoring option - either through OEM, or through the procedure dbms_sqltune.report_sql_monitor. There's a comment and example in the following posting: http://jonathanlewis.wordpress.com/2015/12/21/parallel-plans-2/
The figures are updated every few seconds so you can watch the progression of your query and may get enough clues to figure out how long it's going to take, where the time goes and why. Easiest if you have the OEM screen handy, but you can dump the text version a few times.
Regards
Jonathan Lewis
From: Prem Khanna J <jprem_at_outlook.com> Sent: 02 February 2017 14:12:32
To: Jonathan Lewis; oracle-l_at_freelists.org Subject: Re: Tuning "INSERT as SELECT"
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-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Thu Feb 02 2017 - 16:48:13 CET