But why would you install it for just a few bits of the
functionality? EM is a fantastic tool, but you wouldn't call it
lightweight. If all you need is covered in SQL Developer, why bother
going to the hassle of installing a much bigger product? :)
Pete
----- Original Message -----
From: jonathan@xxxxxxxxxxxxxxxxxx
To:"oracle-l@xxxxxxxxxxxxx"
Cc:
Sent:Thu, 2 Feb 2017 16:41:59 +0000
Subject:Re: Tuning "INSERT as SELECT"
I've found that many of the people who need to take advantage of just
a few bits OEM often don't have access to it.
Regards
Jonathan Lewis
________________________________________
From: Jeff Smith
Sent: 02 February 2017 15:48:13
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: RE: Tuning "INSERT as SELECT"
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@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, February 02, 2017 10:45 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: Tuning "INSERT as SELECT"
Good point - much more widely available.
Still requires the licences, of course.
Regards
Jonathan Lewis
________________________________________
From: Jeff Smith
Sent: 02 February 2017 15:43:19
To: Jonathan Lewis; Prem Khanna J; oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxxxxxxx]
Sent: Thursday, February 02, 2017 10:40 AM
To: Prem Khanna J ; oracle-l@xxxxxxxxxxxxx
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
Sent: 02 February 2017 14:12:32
To: Jonathan Lewis; oracle-l@xxxxxxxxxxxxx
Subject: Re: Tuning "INSERT as SELECT"
Hi Jonathan / Mladen / Tim and All - tons of thanks for your inputs.
first few, or a count(*) of an inline view, or what ?Did you really select and fetch 80 million rows ? or was it the
indexes valid again.... your test starts with a truncate then it will have made the
else is pure witchcraft.please turn on SQL tracing using DBMS_MONITOR or .... Everything
