Re: SQL faster "direct" than CM
- From: Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 26 Apr 2011 11:27:20 +0200
Hi,
yet another reason for that could be wrong bind variables type. After we
switched to oracle bpm, we have found all the varchar2 bind variables
are bind as NCHAR type (which evaluated to nvarchar2 and in consequence
to implicit column conversion).
Of course running through other client tools like let's say Oracle SQL
Developer is just fine with plans with efficient range scans.
First solution from MOS side was to convert to UTF8, which is impossible
as this database already is in UTF8.
A workaround is simple - to use to_char on bind variables, but one needs
to change many sqls
Btw. may be You have heard about it and know a "true" solution? Some
parameter on jdbc/app server (WebLogic) side?
Regards
Remigiusz
W dniu 04/26/11 01:16, Stephen Andert pisze:
> Thanks for the suggestions everyone.
>
> No RAC.
> CM is same machine as DB.
>
> I am working with DEV folks to get some tracing done to compare explain
> plans
>
>
>
> On Mon, Apr 25, 2011 at 3:14 PM, Harel Safra <harel.safra@xxxxxxxxx
> <mailto:harel.safra@xxxxxxxxx>> wrote:
>
> Is there a difference in the execution plans? Can you trace the
> sessions?
>
> Harel Safra
> Sent from my phone.
>
> On Apr 26, 2011 12:51 AM, "Stephen Andert" <andert@xxxxxxxxx
> <mailto:andert@xxxxxxxxx>> wrote:
> > Yes, it is on a SAN. Not sure what other machines may be doing at
> the same
> > time, but even when the CM job and the TOAD job are run at the
> same time,
> > the TOAD job runs faster.
> > On Mon, Apr 25, 2011 at 2:52 PM, Stephane Faroult
> <sfaroult@xxxxxxxxxxxx <mailto:sfaroult@xxxxxxxxxxxx>>wrote:
> >
> >> Running on a SAN and other machines connected to the same
> computing their
> >> stats at the same time?
> >>
> >> Stephane Faroult
> >> RoughSea Ltd <http://www.roughsea.com>
> >> Konagora <http://www.konagora.com>
> >> RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
>
> >>
> >> On 04/25/2011 10:51 PM, Stephen Andert wrote:
> >>
> >> Do you have any ideas on why this would run in Toad in 2 minutes
> and take
> >> over 4 hours running through the concurrent manager? Actually up
> until
> >> this past week, 4 minutes is the usual run time for this job.
> >>
> >>
> >> I have looked at StatsPack for one of the 4 hour runs and other than
> >> about 75% of the time being I/O related. did not see anything out
> of line.
> >>
> >>
> >> Financials 11.5.8
> >>
> >> DB 9.2.0.4.0
> >>
> >> (what am I missing?)
> >>
> >>
>
>
--
Pole nakazi
----------------------------------------------------------------------
Remigiusz Sokolowski <remigiusz.sokolowski@xxxxxxxxxx>
pos : DBA at DIiUSI
addr : Nordea Bank Polska SA, Luzycka 6A Street, 81-537 Gdynia, Poland
phone : +48 58 667 17 43
mobile: +48 602 42 42 77
Nordea Bank Polska S.A. z siedziba w Gdyni, ul. Kielecka 2, 81-303 Gdynia,
wpisana do Rejestru Przedsiebiorców Krajowego Rejestru Sadowego pod numerem:
0000021828,
dla której dokumentacje przechowuje Sad Rejonowy Gdansk - Pólnoc w Gdansku,
VIII Wydzial Gospodarczy Krajowego Rejestru Sadowego,
o kapitale zakladowym i wplaconym w wysokosci: 277.493.500,00 zlotych,
NIP: 586-000-78-20, REGON: 190024711--
//www.freelists.org/webpage/oracle-l
Other related posts: