Re: Oracle 11G Upgrade
- From: Jinwen Zou <zjworacle@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Sat, 29 Mar 2014 08:41:35 +1100
Raising exception and catching exception are heavy processes, seems oracle
has to run different(or much longer) code path to handle it internally.
High context switch or CPU is much easier to happen in high cocurrency
enviornments even when locking/contention, data pattern change or execution
plan is not an issue. Replacing this kind of exception processing with
select/if or checking update return code when possible became kind of best
practice in my opinion.
Probably check num of executions for data profile, execution plan and
cost(LIO, CPU) per execution(for RI and other behaviour change) of the 2
SQL in AWR to see if there are dramatic changes first. If no change, check
the CPU gap between procedure and the sum of the 2 SQL, if it's big,
replace with select/if or update check return code would help.
Regards,
Jinwen
On Sat, Mar 29, 2014 at 3:37 AM, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx
> wrote:
>
>
> A further thought for the OP.
>
> 11.2 gives you much higher precision for tracing. If this code is being
> called as an anonymous block that appears in v$sql you can find its SQL_ID
> and trace it system wide for a while by doing something like:
>
> alter system set events '
> sql_trace[SQL:5s4pt15sk27x7]
> plan_stat=all_executions,
> wait=true,
> bind=false'
> ;
>
> The nice thing about this is that it seems to enable tracing of the SQL
> inside the PL/SQL.
> In the past I've hit a pl/sql block with this - set the event for a couple
> of minutes, then switched it off - and found everything I needed to see in
> a small number of trace files.
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle
>
> ________________________________________
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> behalf of Jonathan Lewis [jonathan@xxxxxxxxxxxxxxxxxx]
> Sent: 28 March 2014 16:29
> To: dombrooks@xxxxxxxxxxx; rjoralist3@xxxxxxxxxxxxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: RE: Oracle 11G Upgrade
>
> Good point - and that's just scratching the surface.
>
> But the focus on the concurrency raises another couple of issues for the
> OP.
>
> 1) How is the data generated for the 30 concurrent processes - perhaps the
> data sets that each process gets are now in a different order because of
> (e.g.) a change in the execution plan of the query that generated the data;
> such a change might introduce contention that didn't previously exist.
>
> 2) Locking (especially relating to tables with RI) has changed a few times
> between 9i and 11g - is it possible that processes are spendng time waiting
> for locks that they didn't have to worry about in 9i ?
>
>
>
> Regards
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
> @jloracle
>
> ________________________________________
> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> behalf of Dominic Brooks [dombrooks@xxxxxxxxxxx]
> Sent: 28 March 2014 14:04
> To: rjoralist3@xxxxxxxxxxxxxxxxxxxxx
> Cc: oracle-l@xxxxxxxxxxxxx
> Subject: Re: Oracle 11G Upgrade
>
> "Update else insert" behaves differently than "insert else update" under
> concurrent activity.
>
> Similarly merge is different again.
>
> http://orastory.wordpress.com/2011/10/13/concurrent-merge
>
>
> Cheers,
> Dominic
>
> Sent from my iPhone
>
> On 28 Mar 2014, at 13:53, "Rich Jesse" <rjoralist3@xxxxxxxxxxxxxxxxxxxxx>
> wrote:
>
> >> Jack, have you considered just changing the logic to perform the update
> >> first and check the cursor count number of rows updated. If zero
> perform
> >> the insert.
> >
> > An INSERT, followed by an UPDATE on fail should be more efficient. I
> can't
> > remember where I saw that years ago (asktom?), but my own testing
> confirmed
> > it in some older version of Oracle (9 or 10).
> >
> > I was thinking along the same lines though and wondering if a MERGE
> > statement in place of the INSERT/UPDATE would work...
> >
> > Rich
> >
> > --
> > //www.freelists.org/webpage/oracle-l
> >
> >
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Regards,
Jinwen Zou
Other related posts:
