Re: Test - Please Ignore

[Joe Testa]

viraj2 wrote:

Ignore

 

--
Joseph S Testa
Chief Technology Officer
Data Management Consulting
614-791-9000
It's all about the "CACHE"
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Joe Testa
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Test - Please Ignore

[viraj2]

Ignore

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: viraj2
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: an article comparing Oracle to other databases

[DENNIS WILLIAMS]

Patrice - By technical, do you mean a feature comparison or a performance
comparison. You might want to research the eWeek benchmark for the three a
couple of years ago. They made extensive efforts to produce an objective
performance analysis, but even at that they had another article later about
the things they had overlooked. These are really complex, sophisticated
products that are used in really complex, sophisticated ways at a wide
variety of sites, so I think it is very difficult to say anything that
applies to many situations.
   On a perverse note, it is interesting to note that almost every
deficiency can be viewed from a different perspective. SQL Server has a
reputation for costing less, which means many corporations view it as
"cheap". Oracle has a reputation of being harder to manage, which may mean
that Oracle DBA salaries are a little higher. 

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Thursday, November 27, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L


http://www.ecommercetimes.com/perl/story/32200.html

I have yet to see an objective, detailed comparison of Oracle, DB2 and SQL
Server.  From a technical (i.e. what can it do) as well as from an
organisational (i.e. how is it to manage) point of view.

Even 3rd party "think tanks" seem to walk on egg shells when evaluating
software from major vendors, possibly to avoid alienating any of them.

Patrice.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: OS Level Defrag

[DENNIS WILLIAMS]

Sujatha - If it helps, we have Tru64 and 8.1.6 here. My sys admin has
defragged the disk quite a few times with no apparent ill effects.



Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Thursday, November 27, 2003 7:59 PM
To: Multiple recipients of list ORACLE-L


Hi,
 
Does anyone here do an O/S level defrag of their Oracle filesystems???
 
Background: (Tru64/8.1.7.4)
 
Sysadmin here were adamant that the Oracle domains were running out of
extents and were highly fragmented (O/S level). DBA was adamant that the
Oracle filesystems should not be defragmented. I lost the battle and the
sysadmins are defragging the domains. 
 
I now have a corruption on a table partition with 100 million plus rows on a
50G datafile. I am wondering if the defrag has caused this corruption.
 
The only way I can think of finding out is:
 
Finding the approx date of the corruption using the query 
SELECT ROWID,  from ;
(which will do the full tablescan row by row).
 
And then finding when the defrag utility was hitting the particular datafile
that is corrupted.
 
But this reasoning is flawed 
 
Does anyone have another method of trying to pinpoint if the O/S defrag
caused the corruption
 
Regards,
 
Sujatha
 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Oracle to MS-SQL Replication

[Nikhil Khimani]

L&G,
 
Any pointers, white 
papers, URL, etc. on how to replicate data from Oracle 8i/9i to 
M$-SQL2K?
 
Many 
thanks,
 
Nick Khimani

RE: migration sequence oci problem

[Jeroen van Sluisdam]

It's solved. The oci-code
must be changed when going from oracle 7 to 

Oracle 8 or 9. When a sequence.nextval is called using
a float_type you get returned

Duplicates.  Not only the type of
column returned must be changed but the buffer etc .. also

 

Thanks,

 

Jeroen

 

-Oorspronkelijk
bericht-
Van: Jeroen
 van Sluisdam [mailto:[EMAIL PROTECTED]]

Verzonden: donderdag 27 november 2003 19:09
Aan: Multiple recipients of list
ORACLE-L
Onderwerp: RE: migration sequence
oci problem

 

We could narrow it down to the value of the sequence exceeding
16777216 (2 to the power of 24)
This looks familiar as described in bug 2573172

This bug describes
to change the type in the odefin call to type 3 but this doesn't help. 

I know this is not
really a dba issue now anymore but I'm desperate for any advice because I don't
have any developers

left with
oci-knowledge

Tnx, 

Jeroen

 

 

-Oorspronkelijk bericht-
Van: Jeroen van Sluisdam 
Verzonden: Wednesday, November 26,
2003 16:40
Aan: '[EMAIL PROTECTED]'
Onderwerp: migration sequence oci
problem

 

 

Hi,

 

We're
testing an oracle 9.2.0.4 database with an oracle 7 client.

This
is a C++ client, using OCI to go to oracle.

We
see strange behaviour when using a sequence which worked 

nicely
before. The sequence is not incremented when issueing

"select res_id.nextval from dual

 

When I test this with an oracle 7 sqlplus client this works also

as expected.

 

Is there any known bug or issue known with migrating oci-applications.

According to manuals it shouldn't be a problem and I cannot find any
problems

on metalink about this.

 

Thanks in advance,

 

Jeroen

Re: an article comparing Oracle to other databases

[Mladen Gogala]

Well, all of those benchmarks could be summarized in a single sentence:
Oracle is the best, ite missa est.
On 11/28/2003 12:19:25 PM, Jared Still wrote:
> Perhaps you missed the sarcasm in my response.
> 
> On Fri, 2003-11-28 at 03:14, Tanel Poder wrote:
> > > And since we all know that memory accesses are
> > > ~14k times faster than disk, these benchmarks
> > > just drive the point home.
> > 
> > Of course you talk about "raw" memory access vs. "raw" disk access here...
> > When you have several memory protection and disk caching mechanisms these
> > figures will change..
> > 
> > Tanel.
> > 
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Tanel Poder
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> > 
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Jared Still
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain confidential, 
proprietary or legally privileged information.  No confidentiality or privilege is 
waived or lost by any mistransmission.  If you receive this message in error, please 
immediately delete it and all copies of it from your system, destroy any hard copies 
of it and notify the sender.  You must not, directly or indirectly, use, disclose, 
distribute, print, or copy any part of this message if you are not the intended 
recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to 
monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where 
the message states otherwise and the sender is authorized to state them to be the 
views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

OT - MVS / Java / Oracle 9iAS

[babette.turnerunderwood]

Title: OT - MVS / Java / Oracle 9iAS







I am a Java-idiot.

I am hoping some of the brilliant people on this list can help me

A developer came to me with a problem they are having.


He is setting up a JVM to run under USS (Unix System Services) on OS/390 v2.10

He has java code that uses (RMI)IIOP to talk to a EJB on 9iAS running on HPUX 

This is failing with UFT-8 error.


The mainframe code page is IBM-1047. JDK is IBM 1.3.1


HP code page is ISO88859-1 using JDK 1.4.1


This did not work, so he copied the JVM from Oracle download to replace the one in USS

(6 libraries / files or something like that) and it still did not work.


If any one has ANY ideas of what I can do to help him,

where to look, etc


TIA - Babette

RE: Cobol redefine in SQL

[Carel-Jan Engel]

At 10:29 28-11-03 -0800, you wrote:
Sorry, it was a typo, I meant a solution that does not involve creating 
many tables per
 redefine statement.

Stéphane
Allright, success!

Regards, Carel-Jan

-- There will allwasy be another 10 last bugs -- 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Carel-Jan Engel
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Cobol redefine in SQL

[Stephane Paquette]

Sorry, it was a typo, I meant a solution that does not involve creating many tables 
per 
 redefine statement.

Stéphane

-Original Message-
Carel-Jan Engel
Sent: 28 novembre, 2003 11:49
To: Multiple recipients of list ORACLE-L


At 06:49 28-11-03 -0800, you wrote:
>And I thought that all "old" people knew cobol  ;-)
>
>I'll try to think of a solution that does involve creating many tables per 
>redefine statement.

I thought you needed every redefine in the same table? So you mean 'many 
into table clauses' ISO 'many tables'?


Regards, Carel-Jan

-- There will allwasy be another 10 last bugs -- 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Carel-Jan Engel
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: RE: Parse Vs Execute

[Jared Still]

By using DBMS_SQL you can open a cursor and re-execute as many
times as needed.

You can't do that with execute immediate.

On Wed, 2003-11-26 at 12:04, [EMAIL PROTECTED] wrote:
> i remember in tom kytes new book there is a 'softer parse' he was referring to using 
> dbms_sql instead of execute immediate. Im not referring to using dbms_sql when you 
> have to loop and use the same cursor repeatedly so you eliminate all parsing. 
> 
> he didnt go into great detail on this just gave benchmarks. do you know anymore? 
> > 
> > From: "Cary Millsap" <[EMAIL PROTECTED]>
> > Date: 2003/11/26 Wed PM 02:39:39 EST
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > Subject: RE: Parse Vs Execute
> > 
> > Don't do this:
> > 
> > Loop
> > Parse
> > Execute
> > Fetch
> > End loop
> > 
> > Do this:
> > 
> > Parse
> > Loop
> > Execute
> > Fetch
> > End loop
> > 
> > If you parse inside your loop, then all that using bind variables will
> > gain you is a reduced "hard parse" count. If you parse outside the loop
> > (in which case, you MUST use bind variables), then you reduce your
> > number of parse calls. A "soft parse" is a little cheaper than a "hard
> > parse." NO PARSE is a lot cheaper than a "soft parse."
> > 
> > 
> > Cary Millsap
> > Hotsos Enterprises, Ltd.
> > http://www.hotsos.com
> > 
> > Upcoming events:
> > - Performance Diagnosis 101: 12/16 Detroit, 1/27 Atlanta
> > - SQL Optimization 101: 12/8 Dallas, 2/16 Dallas
> > - Hotsos Symposium 2004: March 7-10 Dallas
> > - Visit www.hotsos.com for schedule details...
> > 
> > 
> > -Original Message-
> > [EMAIL PROTECTED]
> > Sent: Wednesday, November 26, 2003 12:14 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > Hi List,
> > 
> > Almost fro all SQLs I am getting Prase count is same as Execute count.
> > How to reduce parse count?
> > 
> > 1) We are using bind variable
> > 2) session_cached_cursors set to 100
> > 
> >   
> >   call count   cpuelapsed   disk  querycurrent
> > rows
> > --- --   -- -- -- --
> > --
> > Parse   11  0.01   0.02  0  0  0
> > 0
> > Execute 11  0.00   0.00  0  0  0
> > 0
> > Fetch   22  0.01   0.00  0 33 44
> > 110
> > --- --   -- -- -- --
> > --
> > total   44  0.02   0.02  0 33 44
> > 110
> > 
> > Any somebody give more hint on this?
> > 
> > Thanks
> > Jay
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: 
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> > 
> > -- 
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > -- 
> > Author: Cary Millsap
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> > San Diego, California-- Mailing list and web hosting services
> > -
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
> > 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: <[EMAIL PROTECTED]
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California

Re: an article comparing Oracle to other databases

[Jared Still]

Perhaps you missed the sarcasm in my response.

On Fri, 2003-11-28 at 03:14, Tanel Poder wrote:
> > And since we all know that memory accesses are
> > ~14k times faster than disk, these benchmarks
> > just drive the point home.
> 
> Of course you talk about "raw" memory access vs. "raw" disk access here...
> When you have several memory protection and disk caching mechanisms these
> figures will change..
> 
> Tanel.
> 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Tanel Poder
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Cobol redefine in SQL

[Carel-Jan Engel]

At 06:49 28-11-03 -0800, you wrote:
And I thought that all "old" people knew cobol  ;-)

I'll try to think of a solution that does involve creating many tables per 
redefine statement.
I thought you needed every redefine in the same table? So you mean 'many 
into table clauses' ISO 'many tables'?

Regards, Carel-Jan

-- There will allwasy be another 10 last bugs -- 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Carel-Jan Engel
 INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Who Wrote ...

[KENNETH JANUSZ]

Michael:

You can find this layout in the book "Oracle8i DBA Handbook" by Kevin Loney
& Marlene Theriault.  Chapter 4 discusses this scenario in detail.  Going
from the optimum of 22 disks to the minimum of 7.  You can probably find it
in other text books.

Hope this helps,
Ken Janusz, CPIM


- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, November 26, 2003 2:19 PM


> I remember seeing a paper delivered at an IOUG wa back, that spoke
> of the 22-disk solution.  I know it's old and outdated, but I'd like to
> find a copy.  Does anyone remember who wrote and presented this paper?
>
> Thanks, and Happy Thanksgiving!
> Mike
>
> ---
>
===
> Michael P. Vergara
> Oracle DBA
> Guidant Corporation
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Vergara, Michael (TEM)
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: KENNETH JANUSZ
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: OS Level Defrag

[Brian_P_MacLean]

Sorry can't help with the "when did it happened" question.

But in defense of the SysAdmin, I have seen the benefits of defraging on
both Unix and NT.

In the NT case the defraging was caused by lots of small fragments caused
by the datafile being auto extended in short increments, and that being
mixed on the same file system with other file updates/additions/deletes.

In the Unix case, I was doing a lot of restores to a testing system.  When
I did the restores I had 5 or 6 uncompress commands outputting to the same
/u mount point.  After doing this my wio times when through the roof.  On
the next restore I restricted my restore to one uncompress at a time to
each of my /u mount points ... the wio problem went away.

The last point I'd like to make is "trust no one" with your data.  Always
sum and/or cksum files before and after moving them around (there are gnu
versions of these commands for non Unix systems).  And 3 last words -
backup backup backup.




   

  "Sujatha Madan"  

  <[EMAIL PROTECTED]To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>   
  tus.net.au>  cc: 

  Sent by: Subject:  OS Level Defrag   

  [EMAIL PROTECTED]

  .com 

   

   

  11/27/2003 06:59 

  PM   

  Please respond to

  ORACLE-L 

   

   





Hi,

Does anyone here do an O/S level defrag of their Oracle filesystems???

Background: (Tru64/8.1.7.4)

Sysadmin here were adamant that the Oracle domains were running out of
extents and were highly fragmented (O/S level). DBA was adamant that the
Oracle filesystems should not be defragmented. I lost the battle and the
sysadmins are defragging the domains.

I now have a corruption on a table partition with 100 million plus rows on
a 50G datafile. I am wondering if the defrag has caused this corruption.

The only way I can think of finding out is:

Finding the approx date of the corruption using the query
SELECT ROWID,  from ;
(which will do the full tablescan row by row).

And then finding when the defrag utility was hitting the particular
datafile that is corrupted.

But this reasoning is flawed 

Does anyone have another method of trying to pinpoint if the O/S defrag
caused the corruption

Regards,

Sujatha





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Negative impact of SESSION_CACHED_CURSORS parameter

[jaysingh1]

Hi List,

In our DB we see huge number of softparses during the load test. So I thought of 
setting SESSION_CACHED_CURSORS parameter to positive number(100) to take 
advantage of SOFTER SOFT PARSE. But I am getting negative impact.
Kindly advice me what is going on here.

Version :8.1.7.3
OS: Sun Solaris

tkprof output
=
 SELECT /*+ cached cursors 0  */FIRST_NAME,LAST_NAME,CUSTOMERID,COUNTRYABBREV
 FROM  T1 P,T2 E,T3 C  WHERE P.T1ID =   E.T1ID  AND P.BUSINESS_COUNTRY_ID = 
C.COUNTRYABBREV


 call count   cpuelapsed   disk  querycurrent
rows
 --- --   -- -- -- --  
--
 Parse 2000  1.76   1.77  0  0  0   
0
 Execute   2000  0.84   0.74  0  0  0   
0
 Fetch0  0.00   0.00  0  0  0   
0
 --- --   -- -- -- --  
--
 total 4000  2.60   2.51  0  0  0   
0

 Misses in library cache during parse: 0
 Optimizer goal: CHOOSE
 Parsing user id: 165 (recursive depth: 1)

 Rows Row Source Operation
 ---  ---
   0  HASH JOIN
   0   INDEX FAST FULL SCAN (object id 76648)
   0   HASH JOIN
   0TABLE ACCESS FULL T2
   0TABLE ACCESS FULL T1




 SELECT /*+ cached cursors 100  */FIRST_NAME,LAST_NAME,CUSTOMERID,   
COUNTRYABBREV
 FROM  T1 P,T2 E,T3 C  WHERE P.T1ID =   E.T1ID  AND P.BUSINESS_COUNTRY_ID = 
C.COUNTRYABBREV
 
 
 call count   cpuelapsed   disk  querycurrent
rows
 --- --   -- -- -- --  
--
 Parse 2000  2.05   1.99  0  0  0   
0
 Execute   2000  0.82   0.74  0  0  0   
0
 Fetch0  0.00   0.00  0  0  0   
0
 --- --   -- -- -- --  
--
 total 4000  2.87   2.73  0  0  0   
0
 
 Misses in library cache during parse: 0
 Optimizer goal: CHOOSE
 Parsing user id: 165 (recursive depth: 1)
 
 Rows Row Source Operation
 ---  ---
   0  HASH JOIN
   0   INDEX FAST FULL SCAN (object id 76648)
   0   HASH JOIN
   0TABLE ACCESS FULL T2
   0TABLE ACCESS FULL T1
 
 


Program used to generate the above trace file.
==

alter session set SQL_TRACE=true;
alter session set session_cached_cursors=0;
declare
type rc is ref cursor;
C rc;
n number :=0;
begin
n := dbms_utility.get_time;
for i in 1 .. 2000 loop
open C for select /*+ cached cursors 0 */ 
first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where 
p.t1id=e.t1id and p.business_country_id=c.countryabbrev;
close C;
end loop;
dbms_output.put_line( dbms_utility.get_time - n );
end;
/
alter session set session_cached_cursors=100;
declare
type rc is ref cursor;
C rc;
n number :=0;
begin
n := dbms_utility.get_time;
for i in 1 .. 2000 loop
--open C for select /*+ cached_cursors 100 */ * from dual;
open C for select /*+ cached cursors 100 */ 
first_name,last_name,customerid,countryabbrev from t1 p,t2 e,t3 c where 
p.t1id=e.t1id and p.business_country_id=c.countryabbrev;
close C;
end loop;
dbms_output.put_line( dbms_utility.get_time - n );
end;
/
 

  SQL> @x
  
 Session altered.
  
  
 Session altered.
  
 394
  
 PL/SQL procedure successfully completed.
  
  
 Session altered.
  
 413
  
 PL/SQL procedure successfully completed.
  
  SQL>  


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Cobol redefine in SQL

[Stephane Paquette]

And I thought that all "old" people knew cobol  ;-)

I'll try to think of a solution that does involve creating many tables per redefine 
statement.


Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 499-7999 poste 7470 
[EMAIL PROTECTED]  



-Original Message-
Jared Still
Sent: 27 novembre, 2003 15:09
To: Multiple recipients of list ORACLE-L


Carel-Jan,

Well, go ahead and spoil all our fun.  ;)

Seriously though, thanks for posting which part of the fine
manual to peruse.  If I *knew* COBOL, perhaps I could offer
something useful rather than just whining.

Now if anyone needs to move from Data-Flex to Oracle, I
can offer some ideas.

Jared

On Thu, 2003-11-27 at 11:44, Carel-Jan wrote:
> At 05:44 27-11-03 -0800, you wrote:
> >I'm looking for a solution that is using only one table not multiple tables.
> >In fact a dynamic column redefinition feature (cobol redefines) is what I 
> >need in Oracle SQL.
> >
> >Stephane
> 
> Hi Stephane,
> 
> Instead of flaming Cobol I'll try help you with a solution. Please download 
> the utilities manual, as I advised last time, look at the SQL*Loader part 
> and take case study 5. It will tell you exactly what you want. Redefines, 
> Occurs, all is covered. I think this will solve your problem. You can have 
> as many INTO TABLE clauses as you like, one for each redefine. Every INTO 
> TABLE might refer to the same table. The WHEN clause helps you to determine 
> what INTO TABLE clause to use.
> 
> 
> To the others in this thread, IMHO Cobol might not be state of the art, 
> whithout it IT might not have been what it is now. I hate the language, 
> actually never ever wrote a working program in it due to some mental blocks 
> connected with the syntax, but I've done many data-conversions of files, 
> based on the record-layouts. Redefines might be ugly, for those of you who 
> do not remember the amazing discovery of a 5 MB (spelled MegaByte) 
> harddisk, and spending 3 hours discussing with 4 programmers how to use 
> this amazing amount of diskspace in a usefull way some hsitorical awareness 
> might help. Cobol, coming from the early sixties AFAIK, doesn't support 
> null-values. Actually, when they started using it, there were no disks at 
> all. Costs of storage were sky-high, and every byte saved was welcome. So, 
> redefines, and repeating groups (OCCURS 10 TIMES in a field definition) 
> really helped people writing usefull programs, based on serial access to 
> data stored on ticker tapes and magnetic tapes. It is about time to get 
> some awareness of the history of our trade, before it all fades away.
> 
> Visit Miracle's Oracle museum, running Oracle V5 on a 286 / Dos 6.0 system, 
> or similar old-fashioned. Do you condemn the T-Ford as well? That doesn't 
> meet today's standards, but was most important to the development of a 
> certain country in this world.
> 
> 
> Regards, Carel-Jan
> 
> -- There will allwasy be another 10 last bugs -- 
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Carel-Jan
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jared Still
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP co

RE: How to stop XDB

[DENNIS WILLIAMS]

Tanel - Thanks! That is sort of what I did. I shut down the listener and let
them start the other process that wanted port 8080. Then I restarted the
listener and it griped because it couldn't have that port, but everything
else seemed to come up okay. I don't like rude surprises just as I had my
coat to leave for a holiday. I also don't like clever little features I
don't use being added automatically. Especially clever little features that
have security vulnerabilities posted. I like your idea of removing it from
listener.ora.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, November 26, 2003 4:59 PM
To: Multiple recipients of list ORACLE-L


Hi!

Check note 171658.1 from metalink, it has series of steps for removing XML
stuff from database.

Alternatively (if I recall correctly) you can just remove the XDB entry from
listener.ora.

Tanel.

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Thursday, November 27, 2003 12:24 AM


> My networking people just notified me that XDB has port 8080 locked. Since
I
> wasn't aware that Oracle 9.2 had an XDB running, this quite has me
baffled.
> Is this something I can stop? Oracle has a lot of documentation on how to
> configure it, etc., but I don't see a thing on stopping it. Any thoughts
> appreciated.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: DENNIS WILLIAMS
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Semi-OT...dbazine

[ryan_oracle]

maybe its running on sql server? 
> 
> From: Connor McDonald <[EMAIL PROTECTED]>
> Date: 2003/11/28 Fri AM 08:34:25 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Semi-OT...dbazine
> 
> Anyone noticed www.dbazine.com...
> 
> "This domain has temporarily been disabled.
> To restore the domain, contact your Customer Support."
> 
> Ooops...
> 
> =
> Connor McDonald
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: [EMAIL PROTECTED]
> 
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he 
> will sit in a boat and drink beer all day"
> 
> 
> Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs
> http://www.yahoo.co.uk/robbiewilliams
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <[EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: analyze after truncate

[Whittle Jerome Contr NCI]

Title: RE: analyze after truncate






I once took over an old database which hadn't been analyzed in nearly a year, so I started analyzing it every two weeks. For the most part things stayed the same or got faster; however, one procedure would sometimes, only sometimes, take a lot longer. Come to find out there was a large 'work' table to hold data for reports. Sometimes this table would have a few hundred thousand records in it and other times it would be empty. If I happen to kick off the analyze when the table was empty, the CBO would not use indexes even though the table was now populated with a lot of records. The Rule hint helped but still not perfect as this table had many indexes and the data was used different ways. In our case, having the table analyzed after populating the table as part of the procedure worked best.

Jerry Whittle

ASIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From:   [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]


Hello All,

  We have a huge table both is terms of number of columns and rows. It is

  list partitioned. it has 7 mill rows and its avg row length is 600

  bytes. One of the developers has come up with a method of truncating

  and reloading the table on a daily basis thorough sqlloader. everyday

  there are about 50,000 rows insert and 100,000 rows update. My question

  is do i need to reanalyze this table on a daily basis. Even though we

  truncate the table the statistics still stays on with the table and

  since the table goes thorugh only minimal change, is it advisable to

  analyze it on a daily basis or can i analyze it on a weekly basis.


Please advice..


Thanks,


Sat.

Re: Semi-OT...dbazine

[Tanel Poder]

Yep, and it has been like that for several days already

Tanel.

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, November 28, 2003 3:34 PM


> Anyone noticed www.dbazine.com...
>
> "This domain has temporarily been disabled.
> To restore the domain, contact your Customer Support."
>
> Ooops...
>
> =
> Connor McDonald
> web: http://www.oracledba.co.uk
> web: http://www.oaktable.net
> email: [EMAIL PROTECTED]
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to fish,
and...he will sit in a boat and drink beer all day"
>
> 
> Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs
> http://www.yahoo.co.uk/robbiewilliams
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Semi-OT...dbazine

[Connor McDonald]

Anyone noticed www.dbazine.com...

"This domain has temporarily been disabled.
To restore the domain, contact your Customer Support."

Ooops...

=
Connor McDonald
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: [EMAIL PROTECTED]

"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will 
sit in a boat and drink beer all day"


Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs
http://www.yahoo.co.uk/robbiewilliams
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: an article comparing Oracle to other databases

[Tanel Poder]

> And since we all know that memory accesses are
> ~14k times faster than disk, these benchmarks
> just drive the point home.

Of course you talk about "raw" memory access vs. "raw" disk access here...
When you have several memory protection and disk caching mechanisms these
figures will change..

Tanel.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: ORA-1000 and pl/sql cursor cache

[Lord David]

Jared

Thanks for the response. I've had a play and here are the conclusions: -

1) The cache controlled by session_cached_cursors is entirely separate from
the pl/sql static cursor cache.  You can turn the former off by setting
session_cached_cursors to zero, but you can only turn the latter off by
logging out, dropping or recompiling the pl/sql block.

2) The latter cache only operates on *named* blocks: procedure, function or
package (not sure about triggers).  Hence, your script will not show the
behaviour since it uses an anonymous block.

3) _close_cached_open_cursors does indeed close the cursors on commit.
Handy, but I don't really want to commit every time I run a query (plus it
starts with an underscore:-O).

--
David Lord 

> -Original Message-
> From: Jared Still [mailto:[EMAIL PROTECTED] 
> Sent: 27 November 2003 20:05
> To: Multiple recipients of list ORACLE-L
> Subject: RE: ORA-1000 and pl/sql cursor cache
> 
> 
> Try playing with alternately setting session_cached_cursors 
> to 0 and some non-zero value and run the following script.
> 
> Try setting _close_cached_open_cursors to both true and
> false, changing which 'commit' is used, and omitting the 
> 'commit' altogether.
> 
> On 9i I find that with session_cached_cursors = 0, and
> a 'commit' following the pl/sql block, the only cursor 
> appearing in v$open_cursor is the final 'commit'.
> 
> HTH
> 
> Jared
> 
> 
> --
> alter session set "_close_cached_open_cursors" = false;
> 
> declare
> xyz varchar2(30);
> begin
> select user into xyz from dual;
> commit;
> end;
> /
> 
> --commit;
> --
> 
> On Thu, 2003-11-27 at 01:09, Lord David wrote:
> > Barb
> > 
> > Thanks for the link.  Unfortunately, it again hints at the 
> behaviour, 
> > but doesn't really come out with it.
> > 
> > What's happening is that when you execute a *static* sql statement 
> > such as 'select user into xyz from dual', the cursor 
> remains open (as 
> > shown in
> > v$open_cursors) after the statement has finished.  This 
> also happens with
> > explicit cursors, even if you close them!  And it happens whether
> > session_cached_cursors is set to zero or not.  I assume 
> that the cursors are
> > cached within the session context for the package or 
> procedure since it only
> > happens for named pl/sql blocks and they get closed if you 
> recompile the
> > block.
> > 
> > My guess is that it is a deliberate performance optimisation within 
> > the pl/sql engine, but it does mean that to avoid ora-1000 
> errors, you 
> > need to set open_cursors to be greater than the *total* number of 
> > static sql statements that a session can open in its lifetime, not 
> > just the number concurrently open.  Of course you have also got to 
> > include room for dynamic and recursive sql as well as 
> cursors cached 
> > using session_cached_cursors.
> > 
> > --
> > David Lord
> > 
> > > -Original Message-
> > > From: Barbara Baker [mailto:[EMAIL PROTECTED]
> > > Sent: 26 November 2003 16:49
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: Re: ORA-1000 and pl/sql cursor cache
> > > 
> > > 
> > > David:
> > > I don't really know if this will help you, but it
> > > might be worth a try.  You could try setting 
> session_cached_cursors.
> > > 
> > > Bjørn Engsig's white paper "Efficient use of bind variables, 
> > > cursor_sharing and related cursor parameters" describes this 
> > > parameter a bit.  It can be found at http://miracleas.dk in the 
> > > Technical Information section)  ( Guy Harrison's tuning book
> > > also talks about this parameter. )
> > > 
> > > good luck!
> > > Barb
> > > 
> > > 
> > > 
> > > --- Lord David <[EMAIL PROTECTED]> wrote:
> > > > Hi
> > > > 
> > > > Does anyone know whether its possible to control the
> > > > size of the pl/sql
> > > > static cursor cache.
> > > > 
> > > > I'm running into ORA-01000: maximum number of open cursors 
> > > > exceeded errors and part of the problem (apart from the usual
> > > > developers not closing
> > > > explicit cursors) is that _all_ static sql
> > > > statements in compiled pl/sql
> > > > units seem to be getting cached.  I can't find any 
> documentation of 
> > > > this feature apart from a few hints in the pl/sql and
> > > > application development
> > > > docs.  Here's an example from an 8.1.7 database: -
> > > > 
> > > > SQL>create or replace procedure foobar is
> > > >   2 v_result varchar2(30);
> > > >   3  begin
> > > >   4 select user into v_result from dual;
> > > >   5  end;
> > > >   6  /
> > > > 
> > > > Procedure created.
> > > > 
> > > > SQL>
> > > > SQL>select b.sql_text
> > > >   2  from v$session a, v$open_cursor b
> > > >   3  where a.sid = b.sid
> > > >   4  and a.audsid = userenv('SESSIONID')
> > > >   5  /
> > > > 
> > > > SQL_TEXT
> > > >
> > > 
> > > > SELECT SYS_CONTEXT(:b1,:b2)   FROM SYS.DUAL
> > > > select b.sql_text from v$s

RE: SQL and PL/SQL tuning template document required urgently

[Dunscombe, Chris]

Dennis,

Excellent recommendation, Guy Harrison's book (2nd Edition) is excellent the
best I've seen on SQL tuning. I've used it for a number of years. I had the
1st edition and then bought the 2nd when it came out.

Cheers,

Chris

-Original Message-
Sent: 29 October 2003 15:49
To: Multiple recipients of list ORACLE-L


Ranganath
   Since you mentioned proactive and reactive query tuning, I think the
philosophy with which one approaches the tuning exercise means everything.
Wrong philosophy and you spend your time spinning your wheels. All of us
have only a limited amount of time to devote, so the best approach will make
the best use of that time. 
   Get "Optimizing Oracle Performance" by Cary Millsap. It doesn't take long
to read the important parts. Implement Cary's approach to locate the queries
where you will get the most bang for the buck. Then use books like Guy
Harrison's (Ryan's suggestion) for pointers on making those queries perform
better.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
Sent: Wednesday, October 29, 2003 8:24 AM
To: Multiple recipients of list ORACLE-L


Hi there,

Does any body have a template for proactive and reactive query
tuning which can be used as a guideline/report while tuning simple, medium
complex and complex SQL queries and PL/SQL stored procedures?  If so, can
you please forward the same to me please?  If not, can anybody suggest as to
how to go about doing one?  Any help in this regard is very much
appreciated.

Thanks and Regards,

Ranganath
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ranganath K
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: DENNIS WILLIAMS
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Dunscombe, Chris
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

test mail -- plz ignore

[Prem Khanna J]

 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Prem Khanna J
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).