MIME-Version: 1.0
Message-ID: <ba732017-07d3-42d4-957e-24f5f68d007c@default>
Date: Thu, 2 Feb 2017 07:48:13 -0800 (PST)
From: Jeff Smith <jeff.d.smith@oracle.com>
To: jonathan@jlcomp.demon.co.uk, oracle-l@freelists.org
Subject: RE: Tuning "INSERT as SELECT"
References: <TY1PR01MB0538F2C3A70674B77DBBC319BC4C0@TY1PR01MB0538.jpnprd01.prod.outlook.com>>
 <<MMXP123MB0911F7009F1BED10AA6BAB08A54C0@MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>>
 <<TY1PR01MB05389100D912B48FB267515ABC4C0@TY1PR01MB0538.jpnprd01.prod.outlook.com>
 <MMXP123MB091187B141E6CCC3961E7615A54C0@MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>>
 <<9277cbb0-ab06-421e-a6e9-2fd5f80a5fe8@default>
 <MMXP123MB091169E81091B9A3948BC288A54C0@MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>
In-Reply-To: <MMXP123MB091169E81091B9A3948BC288A54C0@MMXP123MB0911.GBRP123.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: jeff.d.smith@oracle.com

Doesn't everyone have EE + all the goodies?=20

The tool will prompt you with a warning that you'll need the tuning pack. I=
f 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 dire=
ctly or on our OTN Forums Space.=20

-----Original Message-----
From: Jonathan Lewis [mailto:jonathan@jlcomp.demon.co.uk]=20
Sent: Thursday, February 02, 2017 10:45 AM
To: oracle-l@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@oracle.com>
Sent: 02 February 2017 15:43:19
To: Jonathan Lewis; Prem Khanna J; oracle-l@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@jlcomp.demon.co.uk]
Sent: Thursday, February 02, 2017 10:40 AM
To: Prem Khanna J <jprem@outlook.com>; oracle-l@freelists.org
Subject: Re: Tuning "INSERT as SELECT"


In the first place I would examine the query to decide whether or not I cou=
ld 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 yo=
u have scalar subqueries in the select list there may be some scope for doi=
ng 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 ass=
uming I had licensed the diagnostic and performance packs I'd take advantag=
e of the SQL Monitoring option - either through OEM, or through the procedu=
re dbms_sqltune.report_sql_monitor.  There's a comment and example in the f=
ollowing posting: http://jonathanlewis.wordpress.com/2015/12/21/parallel-pl=
ans-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 hand=
y, but you can dump the text version a few times.

Regards
Jonathan Lewis


________________________________________
From: Prem Khanna J <jprem@outlook.com>
Sent: 02 February 2017 14:12:32
To: Jonathan Lewis; oracle-l@freelists.org
Subject: Re: Tuning "INSERT as SELECT"

Hi Jonathan / Mladen / Tim and All - tons of thanks for your inputs.

>>Did you really select and fetch 80 million rows ?  or was it the first fe=
w, or a count(*) of an inline view, or what ?

You are on spot . we did a "select count(*) from inline view" :-(    Also t=
ested 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 lon=
g 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 t=
ests. Did not imagine that truncate would my indexes valid. Just checked an=
d it was so.
Shocked !! Wonder why truncate does so. Thanks a million Jonathan for letti=
ng 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-l