Yanıt: oracle-l Digest V5 #346
- From: tonguc.yilmaz@xxxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 14 Dec 2008 13:58:53 +0000
Bu e-posta, Turkcell BlackBerry ile gönderilmiştir.
-----Original Message-----
From: FreeLists Mailing List Manager <ecartis@xxxxxxxxxxxxx>
Date: Sun, 14 Dec 2008 02:05:08
To: oracle-l digest users<oracle-l@xxxxxxxxxxxxx>
Subject: oracle-l Digest V5 #346
oracle-l Digest Sat, 13 Dec 2008 Volume: 05 Issue: 346
In This Issue:
Re: Simple insert - 36,000 physical reads
Swap Usage
Re: Swap Usage
RE: 10g slowdown
RE: 10g slowdown
RE: Export import problem
Re: Swap Usage
----------------------------------------------------------------------
Date: Sat, 13 Dec 2008 10:06:25 +0100
From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
Subject: Re: Simple insert - 36,000 physical reads
If you grab the SQL and run it yourself in a session manually (using
autotrace or sql trace) do you see the same effect ?
For things like this a SQL trace would also reveal where the IO takes place
(file#, block#) and any recursive sql occurring through triggers, vpd, or
whatever else there might be.
Stefan
=========================
Stefan P Knecht
Senior Consultant
Systems Engineering
OPITZ CONSULTING Schweiz GmbH
Seestrasse 97
CH-8800 Thalwil
Mobile +41-79-571 36 27
stefan.knecht@xxxxxxxxxxxxxxxxxxx
http://www.opitz-consulting.ch
OCP 9i/10g SCSA SCNA
=========================
On Fri, Dec 12, 2008 at 9:55 PM, Dennis Williams <
oracledba.williams@xxxxxxxxx> wrote:
> List,
>
> Oracle 10.2.0.4, Solaris 8
>
> I've found a simple insert that has:
> 1 execution
> 2,512,102 Buffer Gets
> 36,479 Physical Reads
> 1 Parse call
> 1 Row
>
> This insert statement appears quite a few times in the AWR report, and for
> the times I see it, the statistics are pretty close to what I've listed. The
> insert just has a list of columns and a list of values. No subqueries. There
> is a TO_DATE conversion on two columns.This is a real table, not a view.
>
> Does anyone have any suggestions of why a simple insert could cause this
> much database activity?
>
> Thanks to Brandon for suggesting awrsqrpt.sql in response to another
> question. That is a great tool.
>
> Dennis Williams
>
------------------------------
Date: Sat, 13 Dec 2008 16:08:03 +0530
From: "Amit Verma" <in.amitverma@xxxxxxxxx>
Subject: Swap Usage
Dears,
How I can verify actual swap usage by the system.
Thanks in advance,
Amit Verma
------------------------------
Date: Sat, 13 Dec 2008 13:53:07 +0000
From: "Howard Latham" <howard.latham@xxxxxxxxx>
Subject: Re: Swap Usage
Version of Oracle version of os what Os
man -k swap - for linux
On 13/12/2008, Amit Verma <in.amitverma@xxxxxxxxx> wrote:
> Dears,
>
> How I can verify actual swap usage by the system.
>
> Thanks in advance,
> Amit Verma
>
--
Howard A. Latham
------------------------------
Date: Sat, 13 Dec 2008 06:56:59 -0800 (PST)
From: Yong Huang <yong321@xxxxxxxxx>
Subject: RE: 10g slowdown
William,
You can also compare all rows in v$ses_optimizer_env for the SAP session and
the sqlplus session. In case the SAP session set some events early on, find
them by SQL trace with a session logon trigger, or
oradebug setos(ora)pid xxx
oradebug dump events 1
and read the trace file.
Also, when checking another session's execution plan, explain plan should not
be used. Use v$sql_plan or dbms_xplan.display_cursor.
Yong Huang
> Compare the optimizer_env values for the sqlid when run in sqlplus
> against the ones when run in SAP and see if there are any differences.
>
> At 12:18 PM 12/12/2008, Blanchard William wrote:
> >We looked at the program in SAP and the table & indexes in Oracle and
> >nothing directly related to the query has changed. Is there anything
> >that would cause Oracle to not use the right index in SAP but works fine
> >when run in sqlplus?
>
> Regards
>
> Wolfgang Breitling
> Centrex Consulting Corporation
> www.centrexcc.com
------------------------------
From: "Mark W. Farnham" <mwf@xxxxxxxx>
Subject: RE: 10g slowdown
Date: Sat, 13 Dec 2008 10:34:51 -0500
The link below is the perfect explanation of why all y'all should vote yes
whenever presented with a chance to vote for (under various names and
aliases ever since 7.0 - it was the only behavior possible for 6.x and
earlier) "private parse" aka "non-shared parse", "reparse", that has always
be deferred because it went against the tide of maximum simultaneous user
scalability.
Imagine rejecting the use of the shared pool! That simply will not scale as
well as using the shared pool in the general case if even a small percentage
of queries are reused. But when parse storms make searching for the existing
sql text a latch nightmare, when skewed data with binds (as the article
points out in the clearest [as usual for TK] explanation and documentation
I've ever seen), or various and sundry other special cases, directing the
use of a private sql area would in fact be a panacea. Even if they only
implemented it in shared memory with some kind of canard like
sql_trace=psuedo to mark the cursor fresh and unshared this would solve all
the cases I can think of except the parse storm (and of course since you
know you're not going to reuse the shared text sql_trace in general could
skip the search anyway to solve that problem).
I'm against the routine use of hints and the like unless you have to to get
around a problem - in the long haul that puts future improvements to the CBO
in a strait jacket - but the quantitative knowledge of predictive skewness
of bind variable choices is a time machine question unlikely to be solved
any time soon now, so make up your own version of this enhancement request,
file it, and vote for it in all the forums you see.
Or explain to me a better way to solve the problem.
Thanks in advance, and regards,
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Johnson, William L (TEIS)
Sent: Friday, December 12, 2008 2:41 PM
To: William.Blanchard@xxxxxxxxxx; mfontana@xxxxxxxxxxx;
oracle-l@xxxxxxxxxxxxx
Subject: RE: 10g slowdown
Check out this article - it has some good information to help explain what
you are seeing...
www.oracle.com/technology/oramag/oracle/08-jan/o18asktom.html
<SNIP>
------------------------------
From: John Hallas <John.Hallas@xxxxxxxxxxxxxxxxxx>
Date: Sat, 13 Dec 2008 16:28:15 +0000
Subject: RE: Export import problem
Not sure of what your export parameters are but a better option is to count
objects after the import rather than size
select count(*), object_type from dba_objects where owner='PSI' group by
object_type;
select count(*), object_type from dba_objects where owner='PSI_DUP' group by
object_type; (or use owner in)
For the export check the estimate option for expdp which tells you how big the
export file is likely to be
A expdp command for a copy schema might be
nohup expdp system/password schemas=PSI flashback_scn–209618 directory=EXPORTS
dumpfile= PSI_EXPDAT_131208.DMP logfile= PSI_EXPDP_131208.LOG estimate=blocks &
copy the dmp file to the IMPORT directory if it is different from EXPORT
nohup impdp system/password schemas=PSI remap_schemas=PSI_DUP directory=IMPORT
dumpfile=PSI_EXPDAT_131208.DMP logfile=PSIDUP_IMPDP_131208.LOG &
www.jhdba.wordpress.com
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Shastry(DBA)
Sent: 12 December 2008 18:54
To: oracle-l
Subject: Export import problem
Hi all,
I have one requirement, ie. to create a schema PSI_DUP same as PSI (existing).
I did export the PSI schema first and later created PSI_DUP similar to PSI.
Overall schema size is showing as 10GB where as When i do export import I am
not getting the import successful with warnings.
Could you please help me if any suggestions?
______________________________________________________________________
Wm Morrison Supermarkets Plc is registered in England with number 358949. The
registered office of the company is situated at Gain Lane, Bradford, West
Yorkshire BD3 7DL. This email and any attachments are intended for the
addressee(s) only and may be confidential.
If you are not the intended recipient, please inform the sender by replying to
the email that you have received in error and then destroy the email.
If you are not the intended recipient, you must not use, disclose, copy or rely
on the email or its attachments in any way.
Wm Morrison Supermarkets PLC accepts no liability or responsibility for
anything said in the email or its attachments and gives no warranty as to
accuracy. It is the policy of Wm Morrison Supermarkets PLC not to enter into
any contractual or other obligations by email.
Although we have taken steps to ensure the email and its attachments are
virus-free, we cannot guarantee this or accept any responsibility,
and it is the responsibility of recipients to carry out their own virus checks.
______________________________________________________________________
------------------------------
Date: Sat, 13 Dec 2008 23:07:01 +0530
From: "Amit Verma" <in.amitverma@xxxxxxxxx>
Subject: Re: Swap Usage
Thanks Howard,
But what I want to know the actual usage of the SWAP in the below:
[great@test01 ~]$ free -t –m
total used free shared buffers cached
Mem: 32132 32107 24 0 202 26510
-/+ buffers/cache: 5393 26738
Swap: 2047 2047 0
Total: 34179 34154 24
[agilis@omzdwcdrp009 ~]$
Thanks,
AV
On Sat, Dec 13, 2008 at 7:23 PM, Howard Latham <howard.latham@xxxxxxxxx>wrote:
> Version of Oracle version of os what Os
> man -k swap - for linux
>
> On 13/12/2008, Amit Verma <in.amitverma@xxxxxxxxx> wrote:
> > Dears,
> >
> > How I can verify actual swap usage by the system.
> >
> > Thanks in advance,
> > Amit Verma
> >
>
>
> --
> Howard A. Latham
>
--
Amit Verma
Oracle DBA & System Adminstrator
E-Mail:- in.amitverma@xxxxxxxxx, v.amit@xxxxxxxxxxx
WebSite:- http://www.linkedin.com/in/vamit
IM:- verma.labs@xxxxxxxxx
Mobile:- (+91) 98910.98927
------------------------------
End of oracle-l Digest V5 #346
******************************
Other related posts:
- » Yanıt: oracle-l Digest V5 #346 - tonguc . yilmaz
