Re: Re: please help with materialized view question

[Jonathan Lewis]

Normally you can get extra tables involved
with an MV by creating a Dimension that
describes all the relationships between the
tables in the MV and the tables outside the
MV - but the only times I've done this, the
extra tables have always been at the parent
end of a parent/child link to a table in the MV.

Given the way the 'create dimension' defines
levels and hierarchies, I think this may be a
requirement; so you may not be able to do what
you want to do.


Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html


Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
UK___November


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, December 26, 2003 6:59 PM


> I figured it out. I need some help with query re-write. Im not sure its
possible.
>
> My materialized view joins 3 tables on the primary key/foreign key. I have
a query that would join that materialized view to a third transactional
table, but that join is not on any primary key or foreign key.
>
> I cant get it to re-write my query. My query joins 4 tables. 3 are in the
materialized view. One is not.
>
> is this possible?
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  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: please help with materialized view question

[ryan_oracle]

my bad on the explanation.

I have 4 tables. 3 are non-transactional. These are joined in a primary key/foreign 
key relationship. These are going in the materialized view. 

I want to join my 4th table to my materialized view.

1. The application current has code that joins all 4 tables. I dont know if they will 
re-write this. 

2. The refresh on that materialized view is possibly time consuming. Im worried about 
stale data. I want oracle to determine if its stale or not. If I explicitly hit the 
materialized view, I have to handle that with code. We do nightly data loads, then the 
materialized view needs to be reloaded. This could take a little while. 
> 
> From: [EMAIL PROTECTED]
> Date: 2003/12/26 Fri PM 02:09:27 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Subject: Re: please help with materialized view question
> 
> Warning: I have not actually used query rewrite in this way, so take this
> with a grain of salt.
> 
> If you're joining the MV directly to a table, what is there to rewrite?
> 
> If you were joining the tables that make up the MV, and doing so
> on the same key that was used to create the MV, and joining
> that result to a transactional table, it would make sense to use
> query rewrite.
> 
> Based on your statement though, I don't see the need.
> 
> Clarification?
> 
> Jared
> 
> 
> 
> 
> 
> 
> <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
>  12/26/2003 10:44 AM
>  Please respond to ORACLE-L
> 
>  
>     To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> cc: 
> Subject:Re: please help with materialized view question
> 
> 
> I figured it out. 
> 
> I have another problem. I create my materialized view. I now want to write 
> a query that joins it to a transactional table. I want to use query 
> rewrite. Problem is the join is not on the primary key of either table. 
> 
> Is it possible to enable query rewrite without that? I have it in trusted 
> mode? 
> 
> -- 
> 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).
> 
> 
> 
> 

Warning: I have not actually used query rewrite in this way, so take this
with a grain of salt.

If you're joining the MV directly to a table, what is there to rewrite?

If you were joining the tables that make up the MV, and doing so
on the same key that was used to create the MV, and joining
that result to a transactional table, it would make sense to use
query rewrite.

Based on your statement though, I don't see the need.

Clarification?

Jared








<[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 12/26/2003 10:44 AM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: please help with materialized view question


I figured it out. 

I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table. 

Is it possible to enable query rewrite without that? I have it in trusted mode? 

-- 
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: please help with materialized view question

[Jared . Still]

Warning: I have not actually used query rewrite in this way, so take this
with a grain of salt.

If you're joining the MV directly to a table, what is there to rewrite?

If you were joining the tables that make up the MV, and doing so
on the same key that was used to create the MV, and joining
that result to a transactional table, it would make sense to use
query rewrite.

Based on your statement though, I don't see the need.

Clarification?

Jared








<[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 12/26/2003 10:44 AM
 Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: please help with materialized view question


I figured it out. 

I have another problem. I create my materialized view. I now want to write a query that joins it to a transactional table. I want to use query rewrite. Problem is the join is not on the primary key of either table. 

Is it possible to enable query rewrite without that? I have it in trusted mode? 

-- 
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: Re: please help with materialized view question

[ryan_oracle]

I figured it out. I need some help with query re-write. Im not sure its possible.

My materialized view joins 3 tables on the primary key/foreign key. I have a query 
that would join that materialized view to a third transactional table, but that join 
is not on any primary key or foreign key. 

I cant get it to re-write my query. My query joins 4 tables. 3 are in the materialized 
view. One is not. 

is this possible? 

-- 
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: please help with materialized view question

[Thomas Day]

public synonym?



   

  To:  Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>
  Sent by: cc: 

  ml-errorsSubject: please help with materialized 
view question
   

   

  12/26/2003 12:54 

  PM   

  Please respond   

  to ORACLE-L  

   

   





Im sure its a privilege issue.

1. I have 3 tables with two different owners
2. I want to create a materialized join view of these tables in a 3rd user
account.
3. I altered the session to enable query rewrite and
query_rewrite_integrity=trusted
4. I granted query rewrite enabled to every owner involved.
5. I can create the materialized view, if I do not join them to one of the
owners or leave off 'query rewrite enabled.

Here is what I get.

create materialized view test
build immediate
refresh on demand
enable query rewrite
as
select columns
from user1.table1,
 user1.table2,
 user2.table3
where table1.pk = table2.pk
  and table2.pk = table3.pk

ERROR at line 9:
ORA-00942: table or view does not exist

I have all privileges on this table otherwise. I can do a select, describe,
create materialized view without query rewrite

I take out 'query rewrite enabled' and it works.

I have granted query rewrite enabled to the user in question

--
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: Thomas Day
  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: please help with materialized view question

[ryan_oracle]

I figured it out. 

I have another problem. I create my materialized view. I now want to write a query 
that joins it to a transactional table. I want to use query rewrite. Problem is the 
join is not on the primary key of either table. 

Is it possible to enable query rewrite without that? I have it in trusted mode? 

-- 
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 help with materialized view question

[ryan_oracle]

Im sure its a privilege issue. 

1. I have 3 tables with two different owners
2. I want to create a materialized join view of these tables in a 3rd user account. 
3. I altered the session to enable query rewrite and query_rewrite_integrity=trusted
4. I granted query rewrite enabled to every owner involved. 
5. I can create the materialized view, if I do not join them to one of the owners or 
leave off 'query rewrite enabled.

Here is what I get.

create materialized view test
build immediate
refresh on demand
enable query rewrite
as 
select columns
from user1.table1,
 user1.table2,
 user2.table3
where table1.pk = table2.pk
  and table2.pk = table3.pk

ERROR at line 9:
ORA-00942: table or view does not exist

I have all privileges on this table otherwise. I can do a select, describe, create 
materialized view without query rewrite

I take out 'query rewrite enabled' and it works.

I have granted query rewrite enabled to the user in question 

-- 
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: List please help

[Jared Still]

Could be that no one has an answer.

When I set NLS_LANG=French_France.US7ASCII, everything is in
English, which is what I would expect with US7ASCII.

Are you sure that was your previous setting?

Jared

On Wed, 2003-12-24 at 17:14, [EMAIL PROTECTED] wrote:
> List , sorry to keep troubling you all but even if you are not interested in
> helping could someone just let me know if this happens by default in 9i
> release 2 or am I doing something wrong.
> 
> I am using 9.2.0.1.0 enterprise edition on windows.
> Earlier when I use to specify nls_lang=French_France.US7ASCII in 9i release
> 1
> I would get the following messages in French
> 
> c:> sqlplus
> Entrez le nom utilisateur :
> 
> 
> 
> But now when I specify nls_lang=French_France.US7ASCII , sqlplus sticks to
> english
> c:> sqlplus
> Enter user-name:
> 
> Any ideas ?
> 
> I tried out the following but to no avail :
> 1.  I  specified ora_nls33 to point to
> D:\OracleXP\Ora92\ocommon\nls\ADMIN\DATA  where D:\OracleXP\Ora92\ is my
> %oracle_home%
> 
> 2.  I set nls_lang=French_France.US7ASCII , log in as a user, checked
> NLS_SESSION_PARAMETERS.  It shows that NLS_LANGUAGE is
> FRENCH and NLS_TERRITORY is FRANCE.  When I select from a column containing
> dates , the months are in French.  sysdate also gives the month in french
> 
> 3.  Same behaviour with nls_lang=French_France.WE8MSWIN1252  and
> nls_lang=nls_lang=French_France.UTF8 (although this is an incorrect
> specification since there are no utf8 windows clients )
> 
> 4. Similarly when I specify nls_lang=ENGLISH_INDIA.WE8MSWIN1252 my queries
> pick up the correct local currency symbol.  and nls_language and
> nls_territory values in nls_session_parameters are correct.
> 
> 5.  Alert log doesn't show any errors.
> 
> My database character set is AL32UTF8 , did not specify an nchar
> characterset while creating the database.
> 
> -- 
> 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-- 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).

Ignore: List please help

[bulbultyagi]

List, I did a reinstall and got the french language support back when I
specified the languages during the reinstall.
Thanks
Merry xmas and happy new year :)
- Original Message -
To: <[EMAIL PROTECTED]>
Sent: Thursday, December 25, 2003 06:39


> List , sorry to keep troubling you all but even if you are not interested
in
> helping could someone just let me know if this happens by default in 9i
> release 2 or am I doing something wrong.
>
> I am using 9.2.0.1.0 enterprise edition on windows.
> Earlier when I use to specify nls_lang=French_France.US7ASCII in 9i
release
> 1
> I would get the following messages in French
>
> c:> sqlplus
> Entrez le nom utilisateur :
>
>
>
> But now when I specify nls_lang=French_France.US7ASCII , sqlplus sticks to
> english
> c:> sqlplus
> Enter user-name:
>
> Any ideas ?
>
> I tried out the following but to no avail :
> 1.  I  specified ora_nls33 to point to
> D:\OracleXP\Ora92\ocommon\nls\ADMIN\DATA  where D:\OracleXP\Ora92\ is my
> %oracle_home%
>
> 2.  I set nls_lang=French_France.US7ASCII , log in as a user, checked
> NLS_SESSION_PARAMETERS.  It shows that NLS_LANGUAGE is
> FRENCH and NLS_TERRITORY is FRANCE.  When I select from a column
containing
> dates , the months are in French.  sysdate also gives the month in french
>
> 3.  Same behaviour with nls_lang=French_France.WE8MSWIN1252  and
> nls_lang=nls_lang=French_France.UTF8 (although this is an incorrect
> specification since there are no utf8 windows clients )
>
> 4. Similarly when I specify nls_lang=ENGLISH_INDIA.WE8MSWIN1252 my queries
> pick up the correct local currency symbol.  and nls_language and
> nls_territory values in nls_session_parameters are correct.
>
> 5.  Alert log doesn't show any errors.
>
> My database character set is AL32UTF8 , did not specify an nchar
> characterset while creating the database.
>

-- 
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).

List please help

[bulbultyagi]

List , sorry to keep troubling you all but even if you are not interested in
helping could someone just let me know if this happens by default in 9i
release 2 or am I doing something wrong.

I am using 9.2.0.1.0 enterprise edition on windows.
Earlier when I use to specify nls_lang=French_France.US7ASCII in 9i release
1
I would get the following messages in French

c:> sqlplus
Entrez le nom utilisateur :



But now when I specify nls_lang=French_France.US7ASCII , sqlplus sticks to
english
c:> sqlplus
Enter user-name:

Any ideas ?

I tried out the following but to no avail :
1.  I  specified ora_nls33 to point to
D:\OracleXP\Ora92\ocommon\nls\ADMIN\DATA  where D:\OracleXP\Ora92\ is my
%oracle_home%

2.  I set nls_lang=French_France.US7ASCII , log in as a user, checked
NLS_SESSION_PARAMETERS.  It shows that NLS_LANGUAGE is
FRENCH and NLS_TERRITORY is FRANCE.  When I select from a column containing
dates , the months are in French.  sysdate also gives the month in french

3.  Same behaviour with nls_lang=French_France.WE8MSWIN1252  and
nls_lang=nls_lang=French_France.UTF8 (although this is an incorrect
specification since there are no utf8 windows clients )

4. Similarly when I specify nls_lang=ENGLISH_INDIA.WE8MSWIN1252 my queries
pick up the correct local currency symbol.  and nls_language and
nls_territory values in nls_session_parameters are correct.

5.  Alert log doesn't show any errors.

My database character set is AL32UTF8 , did not specify an nchar
characterset while creating the database.

-- 
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: Please help me tune this "io done" wait event

[John Kanagaraj]

Hans,

AFAIK, kproc is the slave process for AIO. How many do you see on your
system? Maybe you have too many of them and are choking on CPU scheduling...
Is your CPU stats Ok at this time? You need to have only as many AIO proces
as there are *simultaneous* requests 300 maybe overkill.

Long time since I even touched AIX (4.2 last) so I may be way off-course
here...
John Kanagaraj
DB Soft Inc
Phone: 408-970-7002 (W)

Grace - Getting something we do NOT deserve
Mercy - NOT getting something we DO deserve
Click on 'http://www.needhim.org' for Grace and Mercy that is freely
available!

** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **

>-Original Message-
>From: Hans de Git [mailto:[EMAIL PROTECTED] 
>Sent: Tuesday, October 07, 2003 8:44 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Please help me tune this "io done" wait event
>
>
>Hi All,
>
>We suffer buffer busy waits and io done waits during batch 
>processing. The 
>batch does direct-path inserts (via Sqlloader and 
>insert-append) in a 16k 
>blocksize database (JFS, AIX).
>
>Async io servers = 300, maxreqs = 16384
>
>What is the general approach to tune the "io done' wait event? The 
>explanation in the Oracle manual is -of course- not clear to me:
>
>"The session waits for an I/O to complete or it waits for a 
>slave process to 
>become available to submit the I/O request. This event occurs 
>on platforms 
>that do not support asynchronous I/O."
>
>AIX does support async IOWhat is the slave process? Which 
>io has to 
>complete? Which write has to complete?
>
>Thanks.
>
>Regards,
>Hans de Git
>
>_
>Hotmail en Messenger on the move 
>http://www.msn.nl/communicatie/smsdiensten/hotmailsmsv2/
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>-- 
>Author: Hans de Git
>  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: John Kanagaraj
  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 help me tune this "io done" wait event

[Hans de Git]

Hi All,

We suffer buffer busy waits and io done waits during batch processing. The 
batch does direct-path inserts (via Sqlloader and insert-append) in a 16k 
blocksize database (JFS, AIX).

Async io servers = 300, maxreqs = 16384

What is the general approach to tune the "io done' wait event? The 
explanation in the Oracle manual is -of course- not clear to me:

"The session waits for an I/O to complete or it waits for a slave process to 
become available to submit the I/O request. This event occurs on platforms 
that do not support asynchronous I/O."

AIX does support async IOWhat is the slave process? Which io has to 
complete? Which write has to complete?

Thanks.

Regards,
Hans de Git
_
Hotmail en Messenger on the move 
http://www.msn.nl/communicatie/smsdiensten/hotmailsmsv2/

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hans de Git
 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: materialized views - please help

[Sai Selvaganesan]

arup
 
the bug says it is fixed in 9. i run 9.2.0.4 on windows 2k.
 
still having this issue. 
 
these invalid mvs affect the performance. 
 
thanks
saiArup Nanda <[EMAIL PROTECTED]> wrote:





Sai,
 
You may want to check bug# 1188948; it sounds like your case.
 
HTH.
 
Arup

- Original Message - 
From: Sai Selvaganesan 
To: Multiple recipients of list ORACLE-L 
Sent: Tuesday, August 26, 2003 9:04 PM
Subject: materialized views

hi
 
materialized views become invalid as soon as a complete or fast refresh of the mv is done. is this how it is supposed to work?
the status is invalid in dba_objects and is set to 'N' in dba_mview_analysis. i have been struggling to understand this. i read thru few docs on metalink which talk about query rewrite enabled blah..blah.. but nothing seems to work.
can some tell me what has to be done. it is mandatory for me to validate the mvs after every refresh..
 
thanks
sai
 
 

Re: ORA-00600 with Bind variable. Please help!

[Guang Mei]

Where are the table "e" and "d" in your query?

Guang

On Wed, 6 Aug 2003, Liu, Jack wrote:

> Hi,
> I got this error when I run my procedure:
> 
> Errors in file /ora817/app/oracle/admin/ORCL/udump/orcl_ora_4345.trc
>
> ksedmp: internal or fatal error
> ORA-00600: internal error code, arguments: [15212], [1], [], [], [], [], [],
> []
>
> Here is part of my procedure:
> query:='select/*+ FIRST_ROWS */ from article a where a.aid=e.aid and
> d.eventid=e.eventid and d.verbid=''REVW'''||chr(13)||' and d.actorid=:mactor
> '||chr(13);
>
> handle:=dbms_sql.open_cursor;
> dbms_sql.parse(handle,query,dbms_sql.native);
> dbms_sql.bind_variable(handle, ':mactor', myactorid);
> dbms_sql.define_column_char(handle,11,nresult,24);
> rownum:=dbms_sql.execute(handle);
> dbms_sql.column_value_char(handle,11,myrnum);
> dbms_sql.close_cursor( handle );
>
> Thanks,
>
> Jack
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Liu, Jack
>   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: Guang Mei
  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).

ORA-00600 with Bind variable. Please help!

[Liu, Jack]

Hi,
I got this error when I run my procedure:

Errors in file /ora817/app/oracle/admin/ORCL/udump/orcl_ora_4345.trc

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15212], [1], [], [], [], [], [],
[]

Here is part of my procedure:
query:='select/*+ FIRST_ROWS */ from article a where a.aid=e.aid and
d.eventid=e.eventid and d.verbid=''REVW'''||chr(13)||' and d.actorid=:mactor
'||chr(13);

handle:=dbms_sql.open_cursor;
dbms_sql.parse(handle,query,dbms_sql.native);
dbms_sql.bind_variable(handle, ':mactor', myactorid);
dbms_sql.define_column_char(handle,11,nresult,24);
rownum:=dbms_sql.execute(handle);
dbms_sql.column_value_char(handle,11,myrnum);
dbms_sql.close_cursor( handle );

Thanks,

Jack


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Liu, Jack
  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: "direct path write" waits, please help

[Tanel Poder]

> : "
> Group by is still doing sorting, and is accounted in "sorts" stats (unless
> an index scan wasn't used to get rows in desired order).
> But yes, hash joins don't increase sort stats by themselves.
> " 
>
> I think you meant "was used"in sted of "wasn't used". Just like you said,
> is's all hash joins. It's a production system; I can only peek via the
> perfstat schema, but I know the application and instance well enough.

Yeah, that's what I meant, sorry for this "typo".

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: "direct path write" waits, please help

[Hans de Git]

 the
>  > overall response time. (ResponseTime = WaitTime + ServiceTime)
>  >
>  > If the 'CPU used by this session' is not considered in light of these
> wait
>  > times, aren't you
>  > getting ready to bark at the wrong tree?
>  >
>  > - Kirti
>  >
>  >
>  > --- John Kanagaraj <[EMAIL PROTECTED]> wrote:
>  >  > Hans,
>  >  >
>  >  > Now let me guess Your disks are all RAID 5, right? And you
> possibly
>  > are
>  >  > bottlenecking on CPU as well? It is clear from the Top 5 that 
writes
> are
>  > an
>  >  > issue across the board, to TEMP (direct path write), Redo (log file
> sync)
>  >  > and DB files (db file parallel writes). Creating a RAID 1 set of
disks
>  > and
>  >  > moving at least the TEMP, RBS, Redo (and Arch if present) to this
will
>  >  > definitely help.
>  >  >
>  >  > John Kanagaraj
>  >  > Phone: 408-970-7002 (W)
>  >  > Fax: 408 327 3086 (Call/Email prior to fax)
>  >  >
>  >  >
>  >  > -Original Message-
>  >  > Sent: Tuesday, July 29, 2003 8:54 AM
>  >  > To: Multiple recipients of list ORACLE-L
>  >  >
>  >  >
>  >  > Hi All,
>  >  >
>  >  > Please help me tune this i/o related wait event. This is my 8.1.6
>  > statspack
>  >  > top-5 wait list:
>  >  > Top 5 Wait Events
>  >  > ~ Wait
> %
>  >  > Total
>  >  > Event   Waits  Time 
(cs)
> Wt
>  >  > Time
>  >
 >   
>  >  > ---
>  >  > direct path write 304,867
35,925
>  >  > 49.83
>  >  > log file sync 145,015
23,441
>  >  > 32.52
>  >  > db file sequential read11,370
3,684
>  >  > 5.11
>  >  > file open 981
3,326
>  >  > 4.61
>  >  > db file parallel write  1,893
3,115
>  >  > 4.32
>  >  >
>  >  > You'll notice that 'direct path write' is the most expensive one in
> the
>  >  > list. I cannot find enough info on the net about this wait event,
>  > therefore
>  >  > I'm asking the real experts.
>  >  >
>  >  > What events in Oracle trigger this wait event? In what way is this
> event
>  >  > different from "db file parallel write"?
>  >  > I mostly read comments that suggest lots of sorting and parallallel
>  > queries.
>  >  >
>  >  > However, most sorts are done in memory and degree = 0 for all
tables.
>  >  >
>  >  > Any suggestions are very welcome.
>  >  >
>  >  > Thanks,
>  >  > Hans de Git
>  >  >
>  >  >
>  >
>  > __
>  > Do you Yahoo!?
>  > Yahoo! SiteBuilder - Free, easy-to-use web site design software
>  > http://sitebuilder.yahoo.com
>  > --
>  > Please see the official ORACLE-L FAQ: http://www.orafaq.net
>  > --
>  > Author: Kirtikumar Deshpande
>  >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).
>  >
>  > _
>  > Chatten met je online vrienden via MSN Messenger.
> http://messenger.msn.nl/
>  >
>  > --
>  > Please see the official ORACLE-L FAQ: http://www.orafaq.net
>  > --
>  > Author: Hans de Git
>  >   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: [EMA

Re: "direct path write" waits, please help

[Tanel Poder]

Hi!

Group by is still doing sorting, and is accounted in "sorts" stats (unless
an index scan wasn't used to get rows in desired order).
But yes, hash joins don't increase sort stats by themselves.

You should check 10046 level 8 output, find which SQL statement is doing
direct path writes, then get execution plan for these statements to see
whether they are using hash joins (since you are on 8.1.6, it can be bit
problematic, because execution path information is stored in raw trace file
starting from 8.1.7 AFAIK. Problematic in sense that, when doing explain
plan under regular session, some session parameters might be different than
using the application).

But if you find out, that statements with hash join execution plans are the
ones waiting on direct path access on temp datafiles, you should also enable
event 10104 at level 1 to get hash join trace information. Maybe your
statistics are not up to date, that CBO thinks based on ancient statistics
it's good idea to hash join because one row set is fairly small, but when it
starts building hash build partitions, they actually don't fit into hash
area, and some of the partitions have to be written to temp. Check under
PHASE 1 in 10104 trace, how many total build partitions you got and how may
of them fit into memory.

Tanel.

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


> Could it be that hash joins account for the writes to TEMP without
> increasing the sort stats? Or 'group by' statements, perhaps?
>
> In a 10 minute interval, I can see no increase in the number of sorts to
> disk, but the writes and reads from v$tempstat increase by thousands.
>
> If that's the case, then I think I should increase sort_area_size and/or
> hash_area_size (memory is not an issue...). Please correct me if i'm
wrong.
> Would it be beneficial to change optimizer_index_caching or
> optimizer_index_cost_adj to force Oracle into using more nested loops?
>
> Don't get me wrong: I'm all against throwing hardware at an application
that
> is so poorly written. But we've past that point, the supplier will not
> change its behaviour, and from a functional point of view, the end-users
are
> very satisfied. Bummer..
>
>
>
> Reply-To: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: Wed, 30 Jul 2003 01:24:24 -0800
>
> Hi!
>
> Either your 4 disk sorts are huge & generating lot's of IO or there direct
> writes aren't because of sorting.
> They could be because NOCACHE LOB access for example (also CTAS and direct
> path insert). You should view 10046 level 8 output and check in which file
> are the IOs occurring.
>
> Tanel.
>
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, July 30, 2003 11:34 AM
>
>
>  > FYI
>  >
>  > The application that is causing the wait events is a third party
product
>  > that really sucks (autocommit, no bind variables, bad data model, etc.,
>  > etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable
> on
>  > AIX; the log file sync problem is not so much of a problem; moving to
raw
>  > volumes for the redologs should put the log file sync waits down in the
>  > top-n.
>  >
>  > Indeed, the direct path writes have a neglible effect on overall
response
>  > time. I just want to get a good understanding of the 'direct path
> writes'.
>  > sorts (disk) =4
>  > physical writes direct = 2,444,555
>  > physical writes  = 2,470,809
>  >
>  > Those are statistics gathered in a two hour interval.
>  >
>  >
>  > Reply-To: [EMAIL PROTECTED]
>  > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>  > Date: Tue, 29 Jul 2003 10:14:29 -0800
>  >
>  > But, I would like to know how this seemingly high wait for 'direct path
>  > write' is affecting the
>  > overall response time. (ResponseTime = WaitTime + ServiceTime)
>  >
>  > If the 'CPU used by this session' is not considered in light of these
> wait
>  > times, aren't you
>  > getting ready to bark at the wrong tree?
>  >
>  > - Kirti
>  >
>  >
>  > --- John Kanagaraj <[EMAIL PROTECTED]> wrote:
>  >  > Hans,
>  >  >
>  >  > Now let me guess Your disks are all RAID 5, right? And you
> possibly
>  > are
>  >  > bottlenecking on CPU as well? It is clear from the Top 5 that writes
> are
>  > an
>  >  > issue across the board, to TEMP (direct pat

Re: "direct path write" waits, please help

[Hans de Git]

Could it be that hash joins account for the writes to TEMP without 
increasing the sort stats? Or 'group by' statements, perhaps?

In a 10 minute interval, I can see no increase in the number of sorts to 
disk, but the writes and reads from v$tempstat increase by thousands.

If that's the case, then I think I should increase sort_area_size and/or 
hash_area_size (memory is not an issue...). Please correct me if i'm wrong. 
Would it be beneficial to change optimizer_index_caching or 
optimizer_index_cost_adj to force Oracle into using more nested loops?

Don't get me wrong: I'm all against throwing hardware at an application that 
is so poorly written. But we've past that point, the supplier will not 
change its behaviour, and from a functional point of view, the end-users are 
very satisfied. Bummer..



Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 30 Jul 2003 01:24:24 -0800
Hi!

Either your 4 disk sorts are huge & generating lot's of IO or there direct
writes aren't because of sorting.
They could be because NOCACHE LOB access for example (also CTAS and direct
path insert). You should view 10046 level 8 output and check in which file
are the IOs occurring.
Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, July 30, 2003 11:34 AM
> FYI
>
> The application that is causing the wait events is a third party product
> that really sucks (autocommit, no bind variables, bad data model, etc.,
> etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable 
on
> AIX; the log file sync problem is not so much of a problem; moving to raw
> volumes for the redologs should put the log file sync waits down in the
> top-n.
>
> Indeed, the direct path writes have a neglible effect on overall response
> time. I just want to get a good understanding of the 'direct path 
writes'.
> sorts (disk) =4
> physical writes direct = 2,444,555
> physical writes  = 2,470,809
>
> Those are statistics gathered in a two hour interval.
>
>
> Reply-To: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: Tue, 29 Jul 2003 10:14:29 -0800
>
> But, I would like to know how this seemingly high wait for 'direct path
> write' is affecting the
> overall response time. (ResponseTime = WaitTime + ServiceTime)
>
> If the 'CPU used by this session' is not considered in light of these 
wait
> times, aren't you
> getting ready to bark at the wrong tree?
>
> - Kirti
>
>
> --- John Kanagaraj <[EMAIL PROTECTED]> wrote:
>  > Hans,
>  >
>  > Now let me guess Your disks are all RAID 5, right? And you 
possibly
> are
>  > bottlenecking on CPU as well? It is clear from the Top 5 that writes
are
> an
>  > issue across the board, to TEMP (direct path write), Redo (log file
sync)
>  > and DB files (db file parallel writes). Creating a RAID 1 set of disks
> and
>  > moving at least the TEMP, RBS, Redo (and Arch if present) to this will
>  > definitely help.
>  >
>  > John Kanagaraj
>  > Phone: 408-970-7002 (W)
>  > Fax: 408 327 3086 (Call/Email prior to fax)
>  >
>  >
>  > -Original Message-
>  > Sent: Tuesday, July 29, 2003 8:54 AM
>  > To: Multiple recipients of list ORACLE-L
>  >
>  >
>  > Hi All,
>  >
>  > Please help me tune this i/o related wait event. This is my 8.1.6
> statspack
>  > top-5 wait list:
>  > Top 5 Wait Events
>  > ~ Wait
%
>  > Total
>  > Event   Waits  Time (cs)
Wt
>  > Time
>  >   
>  > ---
>  > direct path write 304,867   35,925
>  > 49.83
>  > log file sync 145,015   23,441
>  > 32.52
>  > db file sequential read11,3703,684
>  > 5.11
>  > file open 9813,326
>  > 4.61
>  > db file parallel write  1,8933,115
>  > 4.32
>  >
>  > You'll notice that 'direct path write' is the most expensive one in 
the
>  > list. I cannot find enough info on the net about this wait event,
> therefore
>  > I'm asking the real experts.
>  >
>  > What events in Oracle trigger this wait event? In what way is this
event
>  > different from "db file parallel write"?
&

Re: "direct path write" waits, please help

[Tanel Poder]

Hi!

Either your 4 disk sorts are huge & generating lot's of IO or there direct
writes aren't because of sorting.
They could be because NOCACHE LOB access for example (also CTAS and direct
path insert). You should view 10046 level 8 output and check in which file
are the IOs occurring.

Tanel.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, July 30, 2003 11:34 AM


> FYI
>
> The application that is causing the wait events is a third party product
> that really sucks (autocommit, no bind variables, bad data model, etc.,
> etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on
> AIX; the log file sync problem is not so much of a problem; moving to raw
> volumes for the redologs should put the log file sync waits down in the
> top-n.
>
> Indeed, the direct path writes have a neglible effect on overall response
> time. I just want to get a good understanding of the 'direct path writes'.
> sorts (disk) =4
> physical writes direct = 2,444,555
> physical writes  = 2,470,809
>
> Those are statistics gathered in a two hour interval.
>
>
> Reply-To: [EMAIL PROTECTED]
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Date: Tue, 29 Jul 2003 10:14:29 -0800
>
> But, I would like to know how this seemingly high wait for 'direct path
> write' is affecting the
> overall response time. (ResponseTime = WaitTime + ServiceTime)
>
> If the 'CPU used by this session' is not considered in light of these wait
> times, aren't you
> getting ready to bark at the wrong tree?
>
> - Kirti
>
>
> --- John Kanagaraj <[EMAIL PROTECTED]> wrote:
>  > Hans,
>  >
>  > Now let me guess Your disks are all RAID 5, right? And you possibly
> are
>  > bottlenecking on CPU as well? It is clear from the Top 5 that writes
are
> an
>  > issue across the board, to TEMP (direct path write), Redo (log file
sync)
>  > and DB files (db file parallel writes). Creating a RAID 1 set of disks
> and
>  > moving at least the TEMP, RBS, Redo (and Arch if present) to this will
>  > definitely help.
>  >
>  > John Kanagaraj
>  > Phone: 408-970-7002 (W)
>  > Fax: 408 327 3086 (Call/Email prior to fax)
>  >
>  >
>  > -Original Message-
>  > Sent: Tuesday, July 29, 2003 8:54 AM
>  > To: Multiple recipients of list ORACLE-L
>  >
>  >
>  > Hi All,
>  >
>  > Please help me tune this i/o related wait event. This is my 8.1.6
> statspack
>  > top-5 wait list:
>  > Top 5 Wait Events
>  > ~ Wait
%
>  > Total
>  > Event   Waits  Time (cs)
Wt
>  > Time
>  >   
>  > ---
>  > direct path write 304,867   35,925
>  > 49.83
>  > log file sync 145,015   23,441
>  > 32.52
>  > db file sequential read11,3703,684
>  > 5.11
>  > file open 9813,326
>  > 4.61
>  > db file parallel write  1,8933,115
>  > 4.32
>  >
>  > You'll notice that 'direct path write' is the most expensive one in the
>  > list. I cannot find enough info on the net about this wait event,
> therefore
>  > I'm asking the real experts.
>  >
>  > What events in Oracle trigger this wait event? In what way is this
event
>  > different from "db file parallel write"?
>  > I mostly read comments that suggest lots of sorting and parallallel
> queries.
>  >
>  > However, most sorts are done in memory and degree = 0 for all tables.
>  >
>  > Any suggestions are very welcome.
>  >
>  > Thanks,
>  > Hans de Git
>  >
>  >
>
> __
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Kirtikumar Deshpande
>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 'Lis

RE: "direct path write" waits, please help

[Hans de Git]

FYI

The application that is causing the wait events is a third party product 
that really sucks (autocommit, no bind variables, bad data model, etc., 
etc.) We're on EMC Symmetrix. There are hardly any wait-io's measurable on 
AIX; the log file sync problem is not so much of a problem; moving to raw 
volumes for the redologs should put the log file sync waits down in the 
top-n.

Indeed, the direct path writes have a neglible effect on overall response 
time. I just want to get a good understanding of the 'direct path writes'.
sorts (disk) =4
physical writes direct = 2,444,555
physical writes  = 2,470,809

Those are statistics gathered in a two hour interval.

Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Tue, 29 Jul 2003 10:14:29 -0800
But, I would like to know how this seemingly high wait for 'direct path 
write' is affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime)

If the 'CPU used by this session' is not considered in light of these wait 
times, aren't you
getting ready to bark at the wrong tree?

- Kirti

--- John Kanagaraj <[EMAIL PROTECTED]> wrote:
> Hans,
>
> Now let me guess Your disks are all RAID 5, right? And you possibly 
are
> bottlenecking on CPU as well? It is clear from the Top 5 that writes are 
an
> issue across the board, to TEMP (direct path write), Redo (log file sync)
> and DB files (db file parallel writes). Creating a RAID 1 set of disks 
and
> moving at least the TEMP, RBS, Redo (and Arch if present) to this will
> definitely help.
>
> John Kanagaraj
> Phone: 408-970-7002 (W)
> Fax: 408 327 3086 (Call/Email prior to fax)
>
>
> -Original Message-
> Sent: Tuesday, July 29, 2003 8:54 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi All,
>
> Please help me tune this i/o related wait event. This is my 8.1.6 
statspack
> top-5 wait list:
> Top 5 Wait Events
> ~ Wait %
> Total
> Event   Waits  Time (cs)   Wt
> Time
>   
> ---
> direct path write 304,867   35,925
> 49.83
> log file sync 145,015   23,441
> 32.52
> db file sequential read11,3703,684
> 5.11
> file open 9813,326
> 4.61
> db file parallel write  1,8933,115
> 4.32
>
> You'll notice that 'direct path write' is the most expensive one in the
> list. I cannot find enough info on the net about this wait event, 
therefore
> I'm asking the real experts.
>
> What events in Oracle trigger this wait event? In what way is this event
> different from "db file parallel write"?
> I mostly read comments that suggest lots of sorting and parallallel 
queries.
>
> However, most sorts are done in memory and degree = 0 for all tables.
>
> Any suggestions are very welcome.
>
> Thanks,
> Hans de Git
>
>

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Kirtikumar Deshpande
  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).
_
Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hans de Git
 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: "direct path write" waits, please help

[Tim Gorman]

WHO LET THE DOGS OUT!!!


on 7/29/03 11:14 AM, Kirtikumar Deshpande at [EMAIL PROTECTED]
wrote:

> But, I would like to know how this seemingly high wait for 'direct path write'
> is affecting the
> overall response time. (ResponseTime = WaitTime + ServiceTime)
> 
> If the 'CPU used by this session' is not considered in light of these wait
> times, aren't you
> getting ready to bark at the wrong tree?
> 
> - Kirti 
> 
> 
> --- John Kanagaraj <[EMAIL PROTECTED]> wrote:
>> Hans,
>> 
>> Now let me guess Your disks are all RAID 5, right? And you possibly are
>> bottlenecking on CPU as well? It is clear from the Top 5 that writes are an
>> issue across the board, to TEMP (direct path write), Redo (log file sync)
>> and DB files (db file parallel writes). Creating a RAID 1 set of disks and
>> moving at least the TEMP, RBS, Redo (and Arch if present) to this will
>> definitely help.
>> 
>> John Kanagaraj
>> Phone: 408-970-7002 (W)
>> Fax: 408 327 3086 (Call/Email prior to fax)
>> 
>> 
>> -Original Message-
>> Sent: Tuesday, July 29, 2003 8:54 AM
>> To: Multiple recipients of list ORACLE-L
>> 
>> 
>> Hi All,
>> 
>> Please help me tune this i/o related wait event. This is my 8.1.6 statspack
>> top-5 wait list:
>> Top 5 Wait Events
>> ~ Wait %
>> Total
>> Event   Waits  Time (cs)   Wt
>> Time
>>   
>> ---
>> direct path write 304,867   35,925
>> 49.83
>> log file sync 145,015   23,441
>> 32.52
>> db file sequential read11,3703,684
>> 5.11
>> file open 9813,326
>> 4.61
>> db file parallel write  1,8933,115
>> 4.32
>> 
>> You'll notice that 'direct path write' is the most expensive one in the
>> list. I cannot find enough info on the net about this wait event, therefore
>> I'm asking the real experts.
>> 
>> What events in Oracle trigger this wait event? In what way is this event
>> different from "db file parallel write"?
>> I mostly read comments that suggest lots of sorting and parallallel queries.
>> 
>> However, most sorts are done in memory and degree = 0 for all tables.
>> 
>> Any suggestions are very welcome.
>> 
>> Thanks,
>> Hans de Git
>> 
>> 
> 
> __
> Do you Yahoo!?
> Yahoo! SiteBuilder - Free, easy-to-use web site design software
> http://sitebuilder.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  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: "direct path write" waits, please help

[Kirtikumar Deshpande]

But, I would like to know how this seemingly high wait for 'direct path write' is 
affecting the
overall response time. (ResponseTime = WaitTime + ServiceTime) 

If the 'CPU used by this session' is not considered in light of these wait times, 
aren't you
getting ready to bark at the wrong tree? 

- Kirti 


--- John Kanagaraj <[EMAIL PROTECTED]> wrote:
> Hans,
> 
> Now let me guess Your disks are all RAID 5, right? And you possibly are
> bottlenecking on CPU as well? It is clear from the Top 5 that writes are an
> issue across the board, to TEMP (direct path write), Redo (log file sync)
> and DB files (db file parallel writes). Creating a RAID 1 set of disks and
> moving at least the TEMP, RBS, Redo (and Arch if present) to this will
> definitely help.
> 
> John Kanagaraj
> Phone: 408-970-7002 (W)
> Fax: 408 327 3086 (Call/Email prior to fax)
> 
> 
> -Original Message-
> Sent: Tuesday, July 29, 2003 8:54 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi All,
> 
> Please help me tune this i/o related wait event. This is my 8.1.6 statspack 
> top-5 wait list:
> Top 5 Wait Events
> ~ Wait % 
> Total
> Event   Waits  Time (cs)   Wt 
> Time
>    
> ---
> direct path write 304,867   35,925   
> 49.83
> log file sync 145,015   23,441   
> 32.52
> db file sequential read11,3703,684
> 5.11
> file open 9813,326
> 4.61
> db file parallel write  1,8933,115
> 4.32
> 
> You'll notice that 'direct path write' is the most expensive one in the 
> list. I cannot find enough info on the net about this wait event, therefore 
> I'm asking the real experts.
> 
> What events in Oracle trigger this wait event? In what way is this event 
> different from "db file parallel write"?
> I mostly read comments that suggest lots of sorting and parallallel queries.
> 
> However, most sorts are done in memory and degree = 0 for all tables.
> 
> Any suggestions are very welcome.
> 
> Thanks,
> Hans de Git
> 
>

__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Kirtikumar Deshpande
  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: "direct path write" waits, please help

[John Kanagaraj]

Hans,

Now let me guess Your disks are all RAID 5, right? And you possibly are
bottlenecking on CPU as well? It is clear from the Top 5 that writes are an
issue across the board, to TEMP (direct path write), Redo (log file sync)
and DB files (db file parallel writes). Creating a RAID 1 set of disks and
moving at least the TEMP, RBS, Redo (and Arch if present) to this will
definitely help.

John Kanagaraj
Phone: 408-970-7002 (W)
Fax: 408 327 3086 (Call/Email prior to fax)


-Original Message-
Sent: Tuesday, July 29, 2003 8:54 AM
To: Multiple recipients of list ORACLE-L


Hi All,

Please help me tune this i/o related wait event. This is my 8.1.6 statspack 
top-5 wait list:
Top 5 Wait Events
~ Wait % 
Total
Event   Waits  Time (cs)   Wt 
Time
   
---
direct path write 304,867   35,925   
49.83
log file sync 145,015   23,441   
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write  1,8933,115
4.32

You'll notice that 'direct path write' is the most expensive one in the 
list. I cannot find enough info on the net about this wait event, therefore 
I'm asking the real experts.

What events in Oracle trigger this wait event? In what way is this event 
different from "db file parallel write"?
I mostly read comments that suggest lots of sorting and parallallel queries.

However, most sorts are done in memory and degree = 0 for all tables.

Any suggestions are very welcome.

Thanks,
Hans de Git

_
MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hans de Git
  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: John Kanagaraj
  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: "direct path write" waits, please help

[DENNIS WILLIAMS]

Hans
   I did a search on Google, and found this article:
http://www.faqchest.com/prgm/oracle-l/ora-02/ora-0206/ora-020638/ora02062414
_22868.html
(you'll probably have to patch the link together)
According to this article, your biggest and second biggest wait may be
connected.

Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Tuesday, July 29, 2003 10:54 AM
To: Multiple recipients of list ORACLE-L


Hi All,

Please help me tune this i/o related wait event. This is my 8.1.6 statspack 
top-5 wait list:
Top 5 Wait Events
~ Wait % 
Total
Event   Waits  Time (cs)   Wt 
Time
   
---
direct path write 304,867   35,925   
49.83
log file sync 145,015   23,441   
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write  1,8933,115
4.32

You'll notice that 'direct path write' is the most expensive one in the 
list. I cannot find enough info on the net about this wait event, therefore 
I'm asking the real experts.

What events in Oracle trigger this wait event? In what way is this event 
different from "db file parallel write"?
I mostly read comments that suggest lots of sorting and parallallel queries.

However, most sorts are done in memory and degree = 0 for all tables.

Any suggestions are very welcome.

Thanks,
Hans de Git

_
MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hans de Git
  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: "direct path write" waits, please help

[Gorbounov,Vadim]

Hi Hans, 

direct path write cause would be writing temporary segments when sorts are
too large to be performed in memory, direct path operations like INSERT /+
APPEND or LOB access.
It's hard to guess which one is causing your problems, so I'd suggest you
profiling the call having performance issues :

 alter session set events = '10046 trace name context forever, level 8';

 alter session set events = '10046 trace name context off';

Find trace file in udump directory and use tkprof then to identify SQL
statement waiting on  direct path write.  You will need 9i tkprof (any
platform is good) to report wait events,
otherwise you could review raw trace file .

HTH
Vadim



-Original Message-
Sent: Tuesday, July 29, 2003 11:54 AM
To: Multiple recipients of list ORACLE-L


Hi All,

Please help me tune this i/o related wait event. This is my 8.1.6 statspack 
top-5 wait list:
Top 5 Wait Events
~ Wait % 
Total
Event   Waits  Time (cs)   Wt 
Time
   
---
direct path write 304,867   35,925   
49.83
log file sync 145,015   23,441   
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write  1,8933,115
4.32

You'll notice that 'direct path write' is the most expensive one in the 
list. I cannot find enough info on the net about this wait event, therefore 
I'm asking the real experts.

What events in Oracle trigger this wait event? In what way is this event 
different from "db file parallel write"?
I mostly read comments that suggest lots of sorting and parallallel queries.

However, most sorts are done in memory and degree = 0 for all tables.

Any suggestions are very welcome.

Thanks,
Hans de Git

_
MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hans de Git
  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: Gorbounov,Vadim
  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).

"direct path write" waits, please help

[Hans de Git]

Hi All,

Please help me tune this i/o related wait event. This is my 8.1.6 statspack 
top-5 wait list:
Top 5 Wait Events
~ Wait % 
Total
Event   Waits  Time (cs)   Wt 
Time
   
---
direct path write 304,867   35,925   
49.83
log file sync 145,015   23,441   
32.52
db file sequential read11,3703,684
5.11
file open 9813,326
4.61
db file parallel write  1,8933,115
4.32

You'll notice that 'direct path write' is the most expensive one in the 
list. I cannot find enough info on the net about this wait event, therefore 
I'm asking the real experts.

What events in Oracle trigger this wait event? In what way is this event 
different from "db file parallel write"?
I mostly read comments that suggest lots of sorting and parallallel queries. 
However, most sorts are done in memory and degree = 0 for all tables.

Any suggestions are very welcome.

Thanks,
Hans de Git
_
MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Hans de Git
 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: Please help with ORA-01555 Snapshot too old

[Dawtrey, Lindsay L]

Title: Message



You 
can try committing less frequently in the batch load e.g. if you are committing 
every 1 rows then try every 3 (assuming of course that the you have 
space left in the RBS to allow for this) or you can try adding rollback 
segments.
Metalink docs give some good ideas as to the real causes of this 
error. The error message text itself is sometimes 
misleading.
Cheers
Lindsay 

  -Original Message-From: Smith, Ron L. 
  [mailto:[EMAIL PROTECTED]Sent: Tuesday, July 22, 2003 5:04 
  PMTo: Multiple recipients of list ORACLE-LSubject: 
  Please help with ORA-01555 Snapshot too old
  Help!
   
  I 
  have a production and a test database, both with 3000mg RBS tablespaces with 
  two datafiles.  Both have db_block_buffers set at 1000.  The only 
  difference is the production db has 7 rollback segments and the test 
  db has 8.  The rollback segments are sized at 50mg each on both 
  databases.
   
  The 
  problem is, at night when the batch load is running (by itself), the load 
  fails with a ORA-01555.  The data is the same on both loads.  
  Nothing else is going on in the database during the load.
   
  Any 
  ideas?
   
  Ron
  

 
__
For information about the Standard Bank group visit our web site <www.standardbank.co.za>__ Disclaimer 
and confidentiality note Everything in this e-mail and any attachments 
relating to the official business of Standard Bank Group Limited  is 
proprietary to the group. It is confidential, legally privileged and 
protected by law. Standard Bank does not own and endorse any other content. 
Views and opinions are those of the sender unless clearly stated as being that 
of the group. The person addressed in the e-mail is the sole authorised 
recipient. Please notify the sender immediately if it has unintentionally 
reached you and do not read, disclose or use the content in any 
way.Standard Bank can not assure that the integrity of this communication 
has been maintained nor that it is free of errors, virus, interception or 
interference.___

RE: Please help with ORA-01555 Snapshot too old

[DENNIS WILLIAMS]

Ron - I think you are feeling that one system is good and the other is bad.
My suggestion is to consider one system at the ragged edge of failing and
the other system over the edge and in the ditch. Next week with a different
set of data, they may both well fail or both succeed. With ORA-01555 errors,
you can rarely say "fixed", but I think you are getting some good
suggestions from the other postings on how to fix the underlying problem so
your likelihood of receiving these errors is much reduced on either system.


Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Tuesday, July 22, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


They are both processing the same data, the same way.

-Original Message-
Sent: Tuesday, July 22, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Ron
I'm guessing that one of the loads receives the ORA-01555 and the
other doesn't. Does the load commit occasionally? There are some pretty
good papers on Metalink for this error.



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, July 22, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L


Help!
 
I have a production and a test database, both with 3000mg RBS
tablespaces with two datafiles.  Both have db_block_buffers set at 1000.
The only difference is the production db has 7 rollback segments and the
test db has 8.  The rollback segments are sized at 50mg each on both
databases.
 
The problem is, at night when the batch load is running (by itself), the
load fails with a ORA-01555.  The data is the same on both loads.
Nothing else is going on in the database during the load.
 
Any ideas?
 
Ron

 

-- 
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: Smith, Ron L.
  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: Please help with ORA-01555 Snapshot too old

[Odland, Brad]

Snapshot too old also occurs when there is a commit across an open cursor.
Keep the commit on the outermost cursor in your PL/SQL (if you are using
PL/SQL) IN other words committing on the inside of a cursor FOR loop can
cause the cursor snapshot to be overwritten in rollback by activity on the
inside loop when a commit is issued. 

Brad O.


-Original Message-
Sent: Tuesday, July 22, 2003 10:34 AM
To: Multiple recipients of list ORACLE-L


They are both processing the same data, the same way.

-Original Message-
Sent: Tuesday, July 22, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Ron
I'm guessing that one of the loads receives the ORA-01555 and the
other doesn't. Does the load commit occasionally? There are some pretty
good papers on Metalink for this error.



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, July 22, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L


Help!
 
I have a production and a test database, both with 3000mg RBS
tablespaces with two datafiles.  Both have db_block_buffers set at 1000.
The only difference is the production db has 7 rollback segments and the
test db has 8.  The rollback segments are sized at 50mg each on both
databases.
 
The problem is, at night when the batch load is running (by itself), the
load fails with a ORA-01555.  The data is the same on both loads.
Nothing else is going on in the database during the load.
 
Any ideas?
 
Ron

 

-- 
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: Smith, Ron L.
  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: Odland, Brad
  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: Please help with ORA-01555 Snapshot too old

[Smith, Ron L.]

They are both processing the same data, the same way.

-Original Message-
Sent: Tuesday, July 22, 2003 10:15 AM
To: Multiple recipients of list ORACLE-L


Ron
I'm guessing that one of the loads receives the ORA-01555 and the
other doesn't. Does the load commit occasionally? There are some pretty
good papers on Metalink for this error.



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, July 22, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L


Help!
 
I have a production and a test database, both with 3000mg RBS
tablespaces with two datafiles.  Both have db_block_buffers set at 1000.
The only difference is the production db has 7 rollback segments and the
test db has 8.  The rollback segments are sized at 50mg each on both
databases.
 
The problem is, at night when the batch load is running (by itself), the
load fails with a ORA-01555.  The data is the same on both loads.
Nothing else is going on in the database during the load.
 
Any ideas?
 
Ron

 

-- 
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: Smith, Ron L.
  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: Please help with ORA-01555 Snapshot too old

[Daniel Fink]

Ron,
Are the sizes of the data manipulated by the batch load the same?
Is the db_block_size the same on each database?
What is the size of the rbs during processing? What is the hwmsize, shrinks, 
extends from v$rollstat?
You may be encountering a situation where the commit frequency is causing 
transaction table slots to be reused before the batch transaction can complete. In 
this case, the solution is to add rollback segments, not increase the size.

Danielbegin:vcard 
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard

RE: Please help with ORA-01555 Snapshot too old

[DENNIS WILLIAMS]

Ron
I'm guessing that one of the loads receives the ORA-01555 and the other
doesn't. Does the load commit occasionally? There are some pretty good
papers on Metalink for this error.



Dennis Williams 
DBA, 80%OCP, 100% DBA 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, July 22, 2003 10:04 AM
To: Multiple recipients of list ORACLE-L


Help!
 
I have a production and a test database, both with 3000mg RBS tablespaces
with two datafiles.  Both have db_block_buffers set at 1000.  The only
difference is the production db has 7 rollback segments and the test db has
8.  The rollback segments are sized at 50mg each on both databases.
 
The problem is, at night when the batch load is running (by itself), the
load fails with a ORA-01555.  The data is the same on both loads.  Nothing
else is going on in the database during the load.
 
Any ideas?
 
Ron

 

-- 
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 help with ORA-01555 Snapshot too old

[Smith, Ron L.]

Title: Message



Help!
 
I have 
a production and a test database, both with 3000mg RBS tablespaces with two 
datafiles.  Both have db_block_buffers set at 1000.  The only 
difference is the production db has 7 rollback segments and the test 
db has 8.  The rollback segments are sized at 50mg each on both 
databases.
 
The 
problem is, at night when the batch load is running (by itself), the load fails 
with a ORA-01555.  The data is the same on both loads.  Nothing else 
is going on in the database during the load.
 
Any 
ideas?
 
Ron

  
   

RE: Please help with ORA-12571 TNS:Packet Writer Failure.

[Sony kristanto]

Hi Abdul,

Check your tnsnames.ora configuration.

HTH,

Sony

> -Original Message-
> From: Abdul Aleem [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, February 17, 2003 2:39 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Please help with ORA-12571 TNS:Packet Writer Failure.
> 
> Hi,
> 
> I had a stand-alone machine with oracle 9i and Forms6i it was working fine
> when one morning it started giving ORA12571error only when connecting
> through developer. Database operations DML, Query, Export, Import all
> working fine. This lead me to some problem with D2K. When restarting
> services/machine didn't help I tried re-installed net8 alone and then
> complete developer but with no luck.
> 
> I have deleted (seemingly related) registry entries, reinstalled database
> and developer on same and different homes and still it is in the same
> condition. (ORA-12571 when connecting through developer)
> 
> Please help
> 
> TIA!
> 
> Aleem
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> -- 
> Author: Abdul Aleem
>   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: Sony kristanto
  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 help with ORA-12571 TNS:Packet Writer Failure.

[Abdul Aleem]

Hi,

I had a stand-alone machine with oracle 9i and Forms6i it was working fine
when one morning it started giving ORA­12571error only when connecting
through developer. Database operations DML, Query, Export, Import all
working fine. This lead me to some problem with D2K. When restarting
services/machine didn't help I tried re-installed net8 alone and then
complete developer but with no luck.

I have deleted (seemingly related) registry entries, reinstalled database
and developer on same and different homes and still it is in the same
condition. (ORA-12571 when connecting through developer)

Please help

TIA!

Aleem
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Abdul Aleem
  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: newbie question - still: please help

[Daniel Wisser]

thanks a lot 4 your replies.
/*i followed the way dick and mark suggested.*/

daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Wisser
  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[2]: newbie question - still: please help

[Mark Richard]

I think Dick is on the right track...

If you have a list of accounts which require an update, load that list into
the database - if it isn't already in the database (please don't tell me
you are creating the script from within Oracle - please don't tell me
that).  You could load this list either as a pile of inserts (barely better
than the original solution, although at least the critical update command
will run fast) or SQL*Load them.

As Dick said, you can then do a single update to the main table.

As far as performance goes...  If you are updating a significant proportion
of the ISIS_DOCAR table then an index won't be likely to benefit (a single
FTS would be used).  On the other hand, if only a smallish subset of
ISIS_DOCAR is being updated than an index on the ID column could be useful.

Cheers,
 Mark.




   

[EMAIL PROTECTED] 

om   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
Sent by: cc:   

[EMAIL PROTECTED]   Subject: Re[2]: newbie question - still: 
please help  
om 

   

   

14/01/2003 

06:31  

Please respond 

to ORACLE-L

   

   





Daniel,

After some of the younger folks in the audience have had a say, Let's
let
the "old fart" have one.

While on the one hand I can see what your DBA is croaking on, I can
also see
where you are too.  Your approach is easy to code, but can wreck hell on
the
database especially if each statement requires a full table scan.

With the DBA's view your update statement turns into a select, followed
by a
delete, and then an insert after you've processed the file.  Damn, that's a
lot
of work for a simple update and who's to say that the process handling the
interim data file is bug free as well.  As Scotty in Star Trek 3 said "The
more
you overtake the pluming, the easier it is to stop up the drain".  What
happens
when part of your application needs the data record in the middle of your
update?  Or the business logic changes?  Very convoluted pluming, many
tight
drains.

But, having a loop as you do that sets a value to a constant for
several
document id's in series, come on!!  Why not put those id's into a global
temp
table (or suitable substitute) and then use an "UPDATE ISIS_DOCAR SET
STATUS =
2000 WHERE ID in (select id from temp_table);"  One statement, several
thousand
rows updated.  Simple pluming, one very large drain.

An interim solution might be to select your data, which obviously you
did to
fill in 'n', with the "for update clause".  Then your update changes into
"UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE current of cursor x;"  Less io
on the
system, same result.  The pluming gets a little more complicated, but the
drain
is still large & free flowing.

Dick Goulet
Senior Oracle DBA & Oracle Certified 8i DBA
Vicor Corporation

Reply Separator
Author: "Tim Gorman" <[EMAIL PROTECTED]>
Date:   1/13/2003 9:40 AM

Daniel,

Your gut reaction is right on-target.  It is always a struggle to keep
certain folks from killing the entire village while trying to cure a single
case of the sniffles.  What's worse is that such folks are usually quite
bright and talented.  After, very few mediocre folks can either cure the
sniffles or kill entire villages...  ;-)

Longer response:  This is a common argument that eventually distills down
to
something like "I don't need a stupid database engine to do this.  I can do
it all in (choose one): C, C++, Perl, shell script, 

Re[2]: newbie question - still: please help

[dgoulet]

Daniel,

After some of the younger folks in the audience have had a say, Let's let
the "old fart" have one.

While on the one hand I can see what your DBA is croaking on, I can also see
where you are too.  Your approach is easy to code, but can wreck hell on the
database especially if each statement requires a full table scan.

With the DBA's view your update statement turns into a select, followed by a
delete, and then an insert after you've processed the file.  Damn, that's a lot
of work for a simple update and who's to say that the process handling the
interim data file is bug free as well.  As Scotty in Star Trek 3 said "The more
you overtake the pluming, the easier it is to stop up the drain".  What happens
when part of your application needs the data record in the middle of your
update?  Or the business logic changes?  Very convoluted pluming, many tight
drains.

But, having a loop as you do that sets a value to a constant for several
document id's in series, come on!!  Why not put those id's into a global temp
table (or suitable substitute) and then use an "UPDATE ISIS_DOCAR SET STATUS =
2000 WHERE ID in (select id from temp_table);"  One statement, several thousand
rows updated.  Simple pluming, one very large drain.

An interim solution might be to select your data, which obviously you did to
fill in 'n', with the "for update clause".  Then your update changes into
"UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE current of cursor x;"  Less io on the
system, same result.  The pluming gets a little more complicated, but the drain
is still large & free flowing.

Dick Goulet
Senior Oracle DBA & Oracle Certified 8i DBA
Vicor Corporation

Reply Separator
Author: "Tim Gorman" <[EMAIL PROTECTED]>
Date:   1/13/2003 9:40 AM

Daniel,

Your gut reaction is right on-target.  It is always a struggle to keep
certain folks from killing the entire village while trying to cure a single
case of the sniffles.  What's worse is that such folks are usually quite
bright and talented.  After, very few mediocre folks can either cure the
sniffles or kill entire villages...  ;-)

Longer response:  This is a common argument that eventually distills down to
something like "I don't need a stupid database engine to do this.  I can do
it all in (choose one): C, C++, Perl, shell script, Java."  What the person
has to realize is that those 'stupid database engines' started out as lone
programmers doing what he is describing but then running into problems such
as transaction recoverability (aka rollback), concurrency, and its close
cousin read-consistency.  Oh yeah, and then there is also what my good
friend Gary once called "DFB" or "diddly file build-up" (i.e. an excess of
"diddly files" in a file-system), which very few people see up front but
invariably grows to dominate such approaches.  After some decades of effort
by thousands of developers and designers (very few of whom are stupid), what
results is the modern database engine.  Such people who think they can
outperform database engines without losing such crucial features do not have
any sense of humility about their place in the world.  Ask him to skim
through Gray and Reuter's "Principles of Transaction Processing" to gain
some of that humility...

Shorter response:  look into using PL/SQL bulk-bind operations (i.e. FORALL,
BULK COLLECT, etc) instead of one-row-at-a-time processing.

I suspect the latter approach will be more effective...  :-)

Hope this helps...and keep up the good work!

-Tim

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, January 13, 2003 9:14 AM


> hi!
>
> a DBA inteds to speed up a script that is looping and
> sending hundred thousands of sequential update statements like:
>
> UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
> he suggests copying the table to a file, change it and then
> load it into the DB again. i am strongly convinced that this
> is nonsense.
>
> what is the best way to go for a script like this, doing tons of
> updates? (except convincing him to swith to sell burgers)
>
>
> thx
> daniel
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Wisser
>   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: Tim Gorman
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051

Re: newbie question - still: please help

[Jared Still]

Even with this little bit of information, it's obvious your DBA
is on the right track.

Any script that loops and executes 'hundred thousands of 
sequential update statements' is total nonsense.

This will bring most any database to it's knees in a hurry.

Oh yes, I've had developers pull this one on me.  How about
a 10gig text file of INSERT statements?  

Please supply more info, but from what you have here, I 
would say you should start listening to your DBA.

Jared


On Monday 13 January 2003 08:14, Daniel Wisser wrote:
> hi!
>
> a DBA inteds to speed up a script that is looping and
> sending hundred thousands of sequential update statements like:
>
> UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
> he suggests copying the table to a file, change it and then
> load it into the DB again. i am strongly convinced that this
> is nonsense.
>
> what is the best way to go for a script like this, doing tons of
> updates? (except convincing him to swith to sell burgers)
>
>
> thx
> daniel
-- 
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: newbie question - still: please help

[Tim Gorman]

Daniel,

Your gut reaction is right on-target.  It is always a struggle to keep
certain folks from killing the entire village while trying to cure a single
case of the sniffles.  What's worse is that such folks are usually quite
bright and talented.  After, very few mediocre folks can either cure the
sniffles or kill entire villages...  ;-)

Longer response:  This is a common argument that eventually distills down to
something like "I don't need a stupid database engine to do this.  I can do
it all in (choose one): C, C++, Perl, shell script, Java."  What the person
has to realize is that those 'stupid database engines' started out as lone
programmers doing what he is describing but then running into problems such
as transaction recoverability (aka rollback), concurrency, and its close
cousin read-consistency.  Oh yeah, and then there is also what my good
friend Gary once called "DFB" or "diddly file build-up" (i.e. an excess of
"diddly files" in a file-system), which very few people see up front but
invariably grows to dominate such approaches.  After some decades of effort
by thousands of developers and designers (very few of whom are stupid), what
results is the modern database engine.  Such people who think they can
outperform database engines without losing such crucial features do not have
any sense of humility about their place in the world.  Ask him to skim
through Gray and Reuter's "Principles of Transaction Processing" to gain
some of that humility...

Shorter response:  look into using PL/SQL bulk-bind operations (i.e. FORALL,
BULK COLLECT, etc) instead of one-row-at-a-time processing.

I suspect the latter approach will be more effective...  :-)

Hope this helps...and keep up the good work!

-Tim

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, January 13, 2003 9:14 AM


> hi!
>
> a DBA inteds to speed up a script that is looping and
> sending hundred thousands of sequential update statements like:
>
> UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
> he suggests copying the table to a file, change it and then
> load it into the DB again. i am strongly convinced that this
> is nonsense.
>
> what is the best way to go for a script like this, doing tons of
> updates? (except convincing him to swith to sell burgers)
>
>
> thx
> daniel
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Daniel Wisser
>   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: Tim Gorman
  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: newbie question - still: please help

[Stephane Faroult]

>hi!
>
>a DBA inteds to speed up a script that is looping
>and
>sending hundred thousands of sequential update
>statements like:
>
>UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;
>
>he suggests copying the table to a file, change it
>and then
>load it into the DB again. i am strongly convinced
>that this
>is nonsense.
>
>what is the best way to go for a script like this,
>doing tons of
>updates? (except convincing him to swith to sell
>burgers)
>
>
>thx
>daniel

Would be curious to know your DBA's background. 
The most reasonable thing might be to size rollback segments as needed, and remove the 
loop (I guess your loop is here to enable you to commit regularly). The second best 
option would be (8.1.5 and over) to load PL/SQL arrays and do bulk updates (refer to 
the PL/SQL doc for bulk updates).
Files have their use, but not this one.

Regards,

Stephane Faroult
Oriole
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroul
  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: newbie question - still: please help

[DENNIS WILLIAMS]

Daniel - Can you explain the two alternatives in a little more detail,
especially the difference between the two. What types of systems are
involved (Unix, Windows)? Thanks.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 



-Original Message-
Sent: Monday, January 13, 2003 10:14 AM
To: Multiple recipients of list ORACLE-L


hi!

a DBA inteds to speed up a script that is looping and
sending hundred thousands of sequential update statements like:

UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

he suggests copying the table to a file, change it and then
load it into the DB again. i am strongly convinced that this
is nonsense.

what is the best way to go for a script like this, doing tons of
updates? (except convincing him to swith to sell burgers)


thx
daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Wisser
  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).

newbie question - still: please help

[Daniel Wisser]

hi!

a DBA inteds to speed up a script that is looping and
sending hundred thousands of sequential update statements like:

UPDATE ISIS_DOCAR SET STATUS = 2000 WHERE ID = n;

he suggests copying the table to a file, change it and then
load it into the DB again. i am strongly convinced that this
is nonsense.

what is the best way to go for a script like this, doing tons of
updates? (except convincing him to swith to sell burgers)


thx
daniel
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Wisser
  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).

instance shutdown problem ? (please help)

[O'Neill, Sean]

John,

Off the top of my head.  Is correc tinit.ora being used and are Control
files in expected locations?

-
Seán O' Neill
Organon (Ireland) Ltd.
[subscribed: digest mode] 


>>From: john <[EMAIL PROTECTED]>
>> Date: Sat, 23 Nov 2002 15:21:26 -0800 (PST)
>> Subject: instance shutdown problem ? (please help)
>>
>>ver 8i NT4.o
>>all services have been set to automatic and start fine
>>
>>however the NT Event Viwer and the oracle "alert.log"
>>show that the instance being shutdown just after the 
>>box boots up.
>>
>>how do we rectify this problem ?
>>
>>below is from the alert.log
>>
>>ORA-205 signalled during: alter database mount exclusive..
>>Shutting down instance (abort)
>>License high water mark = 5
>>Instance terminated by USER, pid = 144
>>---
>>please note: the shutdwon was not initiated by any admin
>>or user but done by the system itself after the box boots
>>up.

This message, including attached files, may contain confidential
information and is intended only for the use by the individual
and/or the entity to which it is addressed. Any unauthorized use,
dissemination of, or copying of the information contained herein is
not allowed and may lead to irreparable harm and damage for which
you may be held liable. If you receive this message in error or if
it is intended for someone else please notify the sender by
returning this e-mail immediately and delete the message.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: O'Neill, Sean
  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: instance shutdown problem ? (please help)

[orafaq]

John

If this is a new install then why dont you just do the install again.
Otherwise you can use following

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WMPROD" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 500
MAXINSTANCES 10
MAXLOGHISTORY 3630
LOGFILE
  GROUP 1(
'/ora/redo1/wmprod/redo1a.log',
'/ora/redo2/wmprod/redo1b.log'
  ) SIZE 700M,
..
DATAFILE
  '/ora/ts4/wmprod/system01.dbf',
..
;


Justput in names of you all your redologs and data files.  You may have to
do a recovery afterwards.

-Shaleen

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, November 25, 2002 4:48 AM


> thanks to all who are helping with this problem.
> 
> control file(s) are missing. since this is a new install it might
> have been that the control files were never created. 
> the oracle docs (support) tell to use "CREATE CONTROLFILE"
> statement to create control file(s). but i need to know the
> complete procedure. would you know the complete statement and
> procedure to generate control files. do we have to change the
> db/sid name ?
> 
> 
> 
> 
> 
> 
> --- Arup Nanda <[EMAIL PROTECTED]> wrote:
> > Yes, the poster clarified that it was a missing controlfile. I
> > have a reply 
> > that catered to the missing controlfile issue.
> > 
> > 
> > 
> > 
> > 
> > 
> > >From: Jeremiah Wilton <[EMAIL PROTECTED]>
> > >Reply-To: [EMAIL PROTECTED]
> > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > >Subject: Re: instance shutdown problem ? (please help)
> > >Date: Sun, 24 Nov 2002 15:23:35 -0800
> > >MIME-Version: 1.0
> > >Received: from newsfeed.cts.com ([209.68.248.164]) by 
> > >mc6-f42.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600);
> > Sun, 24 Nov 
> > >2002 16:13:22 -0800
> > >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
> > >(8.9.3/8.9.3) with UUCP id QAA10715;Sun, 24 Nov 2002 16:06:45
> > -0800 (PST)
> > >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id
> > 0050A562; 
> > >Sun, 24 Nov 2002 15:23:35 -0800
> > >Message-ID: <[EMAIL PROTECTED]>
> > >X-Comment: Oracle RDBMS Community Forum
> > >X-Sender: Jeremiah Wilton <[EMAIL PROTECTED]>
> > >Sender: [EMAIL PROTECTED]
> > >Errors-To: [EMAIL PROTECTED]
> > >Organization: Fat City Network Services, San Diego, California
> > >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A.
> > Bergman
> > >Precedence: bulk
> > >Return-Path: [EMAIL PROTECTED]
> > >X-OriginalArrivalTime: 25 Nov 2002 00:13:22.0422 (UTC) 
> > >FILETIME=[772A6160:01C29417]
> > >
> > >Hmm, I think the error is right there in a snippet fo the alert
> > log in
> > >the poster's original message. ORA-205 "Error in identifying
> > >controlfile."
> > >
> > >The database will not start because Oracle cannot find or access
> > one
> > >or more of the controlfiles.
> > >
> > >Are all the postings today about controlfiles?
> > >
> > >--
> > >Jeremiah Wilton
> > >http://www.speakeasy.net/~jwilton
> > >
> > >On Sat, 23 Nov 2002, Arup Nanda wrote:
> > >
> > > > Well, system does not do a shutdown abort. Oracle finds a
> > problem and 
> > >then
> > > > instance aborts.
> > > >
> > > > A few things you can do here.
> > > >
> > > > (1) See if a trace file is generated around that time in
> > user_dump_dest 
> > >or
> > > > background_dump_dest that might provide clues. If you see a
> > core fiel in 
> > >any
> > > > directory named core_* in the core_dump_dest, it has cored
> > dumped.
> > > >
> > > > (2) Start the database from command line. From NT services
> > panel, make 
> > >the
> > > > services OracleServiceXXX manual, from automatic. Then reboot
> > the box. 
> > >Start
> > > > the service manually and then see what happens in the
> > process.
> > > >
> > > > (3) Start the instance and service in a command prompt window
> > using 
> > >oradim
> > > > comamnd. I am not sure of the options; but the oradim command
> > gives a 
> > >help.
> > > >
> > > > At some point you would hit a problem that is decipherable.
> > Please 

Re: instance shutdown problem ? (please help)

[orafaq]

John

If this is a new install then why dont you just do the install again.
Otherwise you can use following

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "WMPROD" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 500
MAXINSTANCES 10
MAXLOGHISTORY 3630
LOGFILE
  GROUP 1(
'/ora/redo1/wmprod/redo1a.log',
'/ora/redo2/wmprod/redo1b.log'
  ) SIZE 700M,
..
DATAFILE
  '/ora/ts4/wmprod/system01.dbf',
..
;


Justput in names of you all your redologs and data files.  You may have to
do a recovery afterwards.

-Shaleen
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, November 25, 2002 4:48 AM


> thanks to all who are helping with this problem.
>
> control file(s) are missing. since this is a new install it might
> have been that the control files were never created.
> the oracle docs (support) tell to use "CREATE CONTROLFILE"
> statement to create control file(s). but i need to know the
> complete procedure. would you know the complete statement and
> procedure to generate control files. do we have to change the
> db/sid name ?
>
>
>
>
>
>
> --- Arup Nanda <[EMAIL PROTECTED]> wrote:
> > Yes, the poster clarified that it was a missing controlfile. I
> > have a reply
> > that catered to the missing controlfile issue.
> >
> >
> >
> >
> >
> >
> > >From: Jeremiah Wilton <[EMAIL PROTECTED]>
> > >Reply-To: [EMAIL PROTECTED]
> > >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > >Subject: Re: instance shutdown problem ? (please help)
> > >Date: Sun, 24 Nov 2002 15:23:35 -0800
> > >MIME-Version: 1.0
> > >Received: from newsfeed.cts.com ([209.68.248.164]) by
> > >mc6-f42.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600);
> > Sun, 24 Nov
> > >2002 16:13:22 -0800
> > >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com
> > >(8.9.3/8.9.3) with UUCP id QAA10715;Sun, 24 Nov 2002 16:06:45
> > -0800 (PST)
> > >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id
> > 0050A562;
> > >Sun, 24 Nov 2002 15:23:35 -0800
> > >Message-ID: <[EMAIL PROTECTED]>
> > >X-Comment: Oracle RDBMS Community Forum
> > >X-Sender: Jeremiah Wilton <[EMAIL PROTECTED]>
> > >Sender: [EMAIL PROTECTED]
> > >Errors-To: [EMAIL PROTECTED]
> > >Organization: Fat City Network Services, San Diego, California
> > >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A.
> > Bergman
> > >Precedence: bulk
> > >Return-Path: [EMAIL PROTECTED]
> > >X-OriginalArrivalTime: 25 Nov 2002 00:13:22.0422 (UTC)
> > >FILETIME=[772A6160:01C29417]
> > >
> > >Hmm, I think the error is right there in a snippet fo the alert
> > log in
> > >the poster's original message. ORA-205 "Error in identifying
> > >controlfile."
> > >
> > >The database will not start because Oracle cannot find or access
> > one
> > >or more of the controlfiles.
> > >
> > >Are all the postings today about controlfiles?
> > >
> > >--
> > >Jeremiah Wilton
> > >http://www.speakeasy.net/~jwilton
> > >
> > >On Sat, 23 Nov 2002, Arup Nanda wrote:
> > >
> > > > Well, system does not do a shutdown abort. Oracle finds a
> > problem and
> > >then
> > > > instance aborts.
> > > >
> > > > A few things you can do here.
> > > >
> > > > (1) See if a trace file is generated around that time in
> > user_dump_dest
> > >or
> > > > background_dump_dest that might provide clues. If you see a
> > core fiel in
> > >any
> > > > directory named core_* in the core_dump_dest, it has cored
> > dumped.
> > > >
> > > > (2) Start the database from command line. From NT services
> > panel, make
> > >the
> > > > services OracleServiceXXX manual, from automatic. Then reboot
> > the box.
> > >Start
> > > > the service manually and then see what happens in the
> > process.
> > > >
> > > > (3) Start the instance and service in a command prompt window
> > using
> > >oradim
> > > > comamnd. I am not sure of the options; but the oradim command
> > gives a
> > >help.
> > > >
> > > > At some point you would hit a problem that is decipherable.
> > Please let
> > >us
> >

Re: instance shutdown problem ? (please help)

[Arup Nanda]

John,
 
If you ever had a running database, you did have a 
controlfile. This is a new install; but are you saying that there was never a 
database created, with datafiles, redo log files, etc. and you never stored 
data?
 
If so then there is no problem. Just use Oracle 
Database Configuration Manager and delete the instance that is running. And then 
create a new instance with the same name. You don't have to remember or write 
the controlfiel creation command.
 
However, if the database was created and you had 
data in that, then unless the database is is archivelog mode you can't just 
create a controlfile. Even if it's in archivelog mode, in order to create a 
controlfile, you need to know the exact location and names of datafiles and redo 
log files as well as block sizes, etc. I kind of assume that would be a little 
difficult to do. But if you can do it, just get the syntax from the manual. Here 
it is, just in case.
 
CREATE CONTROLFILE 
   DATABASE MYSID   LOGFILE GROUP 1 ('c:\oradata\log1.log', 'd:\oradata\log1.log') SIZE 50K, 
   GROUP 2 ('c:\oradata\log2.log', 'd:\oradata\log2.log') SIZE 50K 
   NORESETLOGS 
   DATAFILE 'c:\oradata\system.dat' SIZE 100M 
  MAXLOGFILES 5 
  MAXLOGHISTORY 100 
  MAXDATAFILES 10 
  MAXINSTANCES 2 
  ARCHIVELOG
   CHARACTER SET F7DEC; 
Of course, this is an example. Please replace the necessary details with your environment.HTHArup Nandawww.proligence.com
 
- Original Message - 
From: "john" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L" 
<[EMAIL PROTECTED]>
Sent: Monday, November 25, 2002 7:48 
AM
Subject: Re: instance shutdown problem ? (please 
help)
> thanks to all who are helping with this problem.> > 
control file(s) are missing. since this is a new install it might> have 
been that the control files were never created. > the oracle docs 
(support) tell to use "CREATE CONTROLFILE"> statement to create control 
file(s). but i need to know the> complete procedure. would you know the 
complete statement and> procedure to generate control files. do we have 
to change the> db/sid name ?> > > > > 
> > --- Arup Nanda <[EMAIL PROTECTED]> 
wrote:> > Yes, the poster clarified that it was a missing controlfile. 
I> > have a reply > > that catered to the missing 
controlfile issue.> > > > > > > > 
> > > > > > >From: Jeremiah Wilton 
<[EMAIL PROTECTED]>> > 
>Reply-To: [EMAIL PROTECTED]> > 
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>> > 
>Subject: Re: instance shutdown problem ? (please help)> > 
>Date: Sun, 24 Nov 2002 15:23:35 -0800> > >MIME-Version: 
1.0> > >Received: from newsfeed.cts.com ([209.68.248.164]) by 
> > >mc6-f42.law1.hotmail.com with Microsoft 
SMTPSVC(5.0.2195.5600);> > Sun, 24 Nov > > >2002 16:13:22 
-0800> > >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
> > >(8.9.3/8.9.3) with UUCP id QAA10715;Sun, 24 Nov 2002 
16:06:45> > -0800 (PST)> > >Received: by fatcity.com 
(26-Feb-2001/v1.0g-b72/bab) via UUCP id> > 0050A562; > > 
>Sun, 24 Nov 2002 15:23:35 -0800> > >Message-ID: <[EMAIL PROTECTED]>> > >X-Comment: Oracle RDBMS Community Forum> 
> >X-Sender: Jeremiah Wilton <[EMAIL PROTECTED]>> > 
>Sender: [EMAIL PROTECTED]> > 
>Errors-To: [EMAIL PROTECTED]> > 
>Organization: Fat City Network Services, San Diego, California> > 
>X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A.> > 
Bergman> > >Precedence: bulk> > >Return-Path: 
[EMAIL PROTECTED]> > 
>X-OriginalArrivalTime: 25 Nov 2002 00:13:22.0422 (UTC) > > 
>FILETIME=[772A6160:01C29417]> > >> > >Hmm, I think 
the error is right there in a snippet fo the alert> > log in> 
> >the poster's original message. ORA-205 "Error in identifying> 
> >controlfile."> > >> > >The database will not 
start because Oracle cannot find or access> > one> > >or 
more of the controlfiles.> > >> > >Are all the 
postings today about controlfiles?> > >> > >--> 
> >Jeremiah Wilton> > 
>http://www.speakeasy.net/~jwilton> > >> > >On Sat, 
23 Nov 2002, Arup Nanda wrote:> > >> > > > Well, 
system does not do a shutdown abort. Oracle finds a> > problem and 
> > >then> > > > instance aborts.> > > 
>> > > > A few things you

Re: instance shutdown problem ? (please help)

[john]

thanks to all who are helping with this problem.

control file(s) are missing. since this is a new install it might
have been that the control files were never created. 
the oracle docs (support) tell to use "CREATE CONTROLFILE"
statement to create control file(s). but i need to know the
complete procedure. would you know the complete statement and
procedure to generate control files. do we have to change the
db/sid name ?






--- Arup Nanda <[EMAIL PROTECTED]> wrote:
> Yes, the poster clarified that it was a missing controlfile. I
> have a reply 
> that catered to the missing controlfile issue.
> 
> 
> 
> 
> 
> 
> >From: Jeremiah Wilton <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: Re: instance shutdown problem ? (please help)
> >Date: Sun, 24 Nov 2002 15:23:35 -0800
> >MIME-Version: 1.0
> >Received: from newsfeed.cts.com ([209.68.248.164]) by 
> >mc6-f42.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600);
> Sun, 24 Nov 
> >2002 16:13:22 -0800
> >Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
> >(8.9.3/8.9.3) with UUCP id QAA10715;Sun, 24 Nov 2002 16:06:45
> -0800 (PST)
> >Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id
> 0050A562; 
> >Sun, 24 Nov 2002 15:23:35 -0800
> >Message-ID: <[EMAIL PROTECTED]>
> >X-Comment: Oracle RDBMS Community Forum
> >X-Sender: Jeremiah Wilton <[EMAIL PROTECTED]>
> >Sender: [EMAIL PROTECTED]
> >Errors-To: [EMAIL PROTECTED]
> >Organization: Fat City Network Services, San Diego, California
> >X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A.
> Bergman
> >Precedence: bulk
> >Return-Path: [EMAIL PROTECTED]
> >X-OriginalArrivalTime: 25 Nov 2002 00:13:22.0422 (UTC) 
> >FILETIME=[772A6160:01C29417]
> >
> >Hmm, I think the error is right there in a snippet fo the alert
> log in
> >the poster's original message. ORA-205 "Error in identifying
> >controlfile."
> >
> >The database will not start because Oracle cannot find or access
> one
> >or more of the controlfiles.
> >
> >Are all the postings today about controlfiles?
> >
> >--
> >Jeremiah Wilton
> >http://www.speakeasy.net/~jwilton
> >
> >On Sat, 23 Nov 2002, Arup Nanda wrote:
> >
> > > Well, system does not do a shutdown abort. Oracle finds a
> problem and 
> >then
> > > instance aborts.
> > >
> > > A few things you can do here.
> > >
> > > (1) See if a trace file is generated around that time in
> user_dump_dest 
> >or
> > > background_dump_dest that might provide clues. If you see a
> core fiel in 
> >any
> > > directory named core_* in the core_dump_dest, it has cored
> dumped.
> > >
> > > (2) Start the database from command line. From NT services
> panel, make 
> >the
> > > services OracleServiceXXX manual, from automatic. Then reboot
> the box. 
> >Start
> > > the service manually and then see what happens in the
> process.
> > >
> > > (3) Start the instance and service in a command prompt window
> using 
> >oradim
> > > comamnd. I am not sure of the options; but the oradim command
> gives a 
> >help.
> > >
> > > At some point you would hit a problem that is decipherable.
> Please let 
> >us
> > > know what came out of it.
> > >
> > > >From: john <[EMAIL PROTECTED]>
> > > >
> > > >ver 8i NT4.o
> > > >all services have been set to automatic and start fine
> > > >
> > > >however the NT Event Viwer and the oracle "alert.log"
> > > >show that the instance being shutdown just after the
> > > >box boots up.
> > > >
> > > >how do we rectify this problem ?
> > > >
> > > >below is from the alert.log
> > > >
> > > >ORA-205 signalled during: alter database mount exclusive..
> > > >Shutting down instance (abort)
> > > >License high water mark = 5
> > > >Instance terminated by USER, pid = 144
> > > >---
> > > >please note: the shutdwon was not initiated by any admin
> > > >or user but done by the system itself after the box boots
> > > >up.
> >
> >--
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >--
> >Author: Jeremiah Wilton
> >   INET: [EMAIL PROTECTED]
> >

Re: instance shutdown problem ? (please help)

[Arup Nanda]

Yes, the poster clarified that it was a missing controlfile. I have a reply 
that catered to the missing controlfile issue.






From: Jeremiah Wilton <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: instance shutdown problem ? (please help)
Date: Sun, 24 Nov 2002 15:23:35 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc6-f42.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Sun, 24 Nov 
2002 16:13:22 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id QAA10715;Sun, 24 Nov 2002 16:06:45 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050A562; 
Sun, 24 Nov 2002 15:23:35 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: Jeremiah Wilton <[EMAIL PROTECTED]>
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 25 Nov 2002 00:13:22.0422 (UTC) 
FILETIME=[772A6160:01C29417]

Hmm, I think the error is right there in a snippet fo the alert log in
the poster's original message. ORA-205 "Error in identifying
controlfile."

The database will not start because Oracle cannot find or access one
or more of the controlfiles.

Are all the postings today about controlfiles?

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Sat, 23 Nov 2002, Arup Nanda wrote:

> Well, system does not do a shutdown abort. Oracle finds a problem and 
then
> instance aborts.
>
> A few things you can do here.
>
> (1) See if a trace file is generated around that time in user_dump_dest 
or
> background_dump_dest that might provide clues. If you see a core fiel in 
any
> directory named core_* in the core_dump_dest, it has cored dumped.
>
> (2) Start the database from command line. From NT services panel, make 
the
> services OracleServiceXXX manual, from automatic. Then reboot the box. 
Start
> the service manually and then see what happens in the process.
>
> (3) Start the instance and service in a command prompt window using 
oradim
> comamnd. I am not sure of the options; but the oradim command gives a 
help.
>
> At some point you would hit a problem that is decipherable. Please let 
us
> know what came out of it.
>
> >From: john <[EMAIL PROTECTED]>
> >
> >ver 8i NT4.o
> >all services have been set to automatic and start fine
> >
> >however the NT Event Viwer and the oracle "alert.log"
> >show that the instance being shutdown just after the
> >box boots up.
> >
> >how do we rectify this problem ?
> >
> >below is from the alert.log
> >
> >ORA-205 signalled during: alter database mount exclusive..
> >Shutting down instance (abort)
> >License high water mark = 5
> >Instance terminated by USER, pid = 144
> >---
> >please note: the shutdwon was not initiated by any admin
> >or user but done by the system itself after the box boots
> >up.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jeremiah Wilton
  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).


_
MSN 8 with e-mail virus protection service: 2 months FREE* 
http://join.msn.com/?page=features/virus

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arup Nanda
 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: instance shutdown problem ? (please help)

[Jeremiah Wilton]

Hmm, I think the error is right there in a snippet fo the alert log in
the poster's original message. ORA-205 "Error in identifying
controlfile."

The database will not start because Oracle cannot find or access one
or more of the controlfiles.

Are all the postings today about controlfiles?

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Sat, 23 Nov 2002, Arup Nanda wrote:

> Well, system does not do a shutdown abort. Oracle finds a problem and then 
> instance aborts.
> 
> A few things you can do here.
> 
> (1) See if a trace file is generated around that time in user_dump_dest or 
> background_dump_dest that might provide clues. If you see a core fiel in any 
> directory named core_* in the core_dump_dest, it has cored dumped.
> 
> (2) Start the database from command line. From NT services panel, make the 
> services OracleServiceXXX manual, from automatic. Then reboot the box. Start 
> the service manually and then see what happens in the process.
> 
> (3) Start the instance and service in a command prompt window using oradim 
> comamnd. I am not sure of the options; but the oradim command gives a help.
> 
> At some point you would hit a problem that is decipherable. Please let us 
> know what came out of it.
> 
> >From: john <[EMAIL PROTECTED]>
> >
> >ver 8i NT4.o
> >all services have been set to automatic and start fine
> >
> >however the NT Event Viwer and the oracle "alert.log"
> >show that the instance being shutdown just after the
> >box boots up.
> >
> >how do we rectify this problem ?
> >
> >below is from the alert.log
> >
> >ORA-205 signalled during: alter database mount exclusive..
> >Shutting down instance (abort)
> >License high water mark = 5
> >Instance terminated by USER, pid = 144
> >---
> >please note: the shutdwon was not initiated by any admin
> >or user but done by the system itself after the box boots
> >up.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  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: please help with control file

[Jeremiah Wilton]

If you haven't created the database yet, look at the 'create database'
command. The controlfiles get created when you create the database.
If you already created a database, what happened to your controlfiles?
Anyway, if you lost them, you can always use the 'create controlfile'
command to get them back.

Also, you might want to have a look at the Oracle Concepts Manual.  It
explains all about creating a database and controlfiles.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Sun, 24 Nov 2002, john wrote:

> the location mentioned in the init.ora for control files does not
> have any control file.
> since this is a new install, please let me know how to generate or
> rather recreate control file(s). 
> [since there are no control file the system is unable to start]
> 
> since instance is getting shutdown, i cannot enter sql as well. how
> do i create these control files ?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  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 help with control file

[john]

please help me with this one

the location mentioned in the init.ora for control files does not
have any control file.
since this is a new install, please let me know how to generate or
rather recreate control file(s). 
[since there are no control file the system is unable to start]

since instance is getting shutdown, i cannot enter sql as well. how
do i create these control files ?

__
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: john
  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: instance shutdown problem ? (please help)

[Yechiel Adar]

Ora-00205 means that oracle can not find one or more control files.
Check your init.ora parameters.

Yechiel Adar
Mehish
- Original Message - 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Sunday, November 24, 2002 1:23 AM


> ver 8i NT4.o
> all services have been set to automatic and start fine
> 
> however the NT Event Viwer and the oracle "alert.log"
> show that the instance being shutdown just after the 
> box boots up.
> 
> how do we rectify this problem ?
> 
> below is from the alert.log
> 
> ORA-205 signalled during: alter database mount exclusive..
> Shutting down instance (abort)
> License high water mark = 5
> Instance terminated by USER, pid = 144
> ---
> please note: the shutdwon was not initiated by any admin
> or user but done by the system itself after the box boots
> up.
> 
> __
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: john
>   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.com
-- 
Author: Yechiel Adar
  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: instance shutdown problem ? (please help)

[Arup Nanda]

John,

Well, system does not do a shutdown abort. Oracle finds a problem and then 
instance aborts.

A few things you can do here.

(1) See if a trace file is generated around that time in user_dump_dest or 
background_dump_dest that might provide clues. If you see a core fiel in any 
directory named core_* in the core_dump_dest, it has cored dumped.

(2) Start the database from command line. From NT services panel, make the 
services OracleServiceXXX manual, from automatic. Then reboot the box. Start 
the service manually and then see what happens in the process.

(3) Start the instance and service in a command prompt window using oradim 
comamnd. I am not sure of the options; but the oradim command gives a help.

At some point you would hit a problem that is decipherable. Please let us 
know what came out of it.

HTH

Arup Nanda
www.proligence.com






From: john <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: instance shutdown problem ? (please help)
Date: Sat, 23 Nov 2002 15:23:37 -0800
MIME-Version: 1.0
Received: from newsfeed.cts.com ([209.68.248.164]) by 
mc7-f16.law1.hotmail.com with Microsoft SMTPSVC(5.0.2195.5600); Sat, 23 Nov 
2002 16:09:45 -0800
Received: from fatcity.UUCP (uucp@localhost)by newsfeed.cts.com 
(8.9.3/8.9.3) with UUCP id QAA21336;Sat, 23 Nov 2002 16:08:01 -0800 (PST)
Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 0050A280; 
Sat, 23 Nov 2002 15:23:37 -0800
Message-ID: <[EMAIL PROTECTED]>
X-Comment: Oracle RDBMS Community Forum
X-Sender: john <[EMAIL PROTECTED]>
Sender: [EMAIL PROTECTED]
Errors-To: [EMAIL PROTECTED]
Organization: Fat City Network Services, San Diego, California
X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman
Precedence: bulk
Return-Path: [EMAIL PROTECTED]
X-OriginalArrivalTime: 24 Nov 2002 00:09:45.0779 (UTC) 
FILETIME=[CB9F8830:01C2934D]

ver 8i NT4.o
all services have been set to automatic and start fine

however the NT Event Viwer and the oracle "alert.log"
show that the instance being shutdown just after the
box boots up.

how do we rectify this problem ?

below is from the alert.log

ORA-205 signalled during: alter database mount exclusive..
Shutting down instance (abort)
License high water mark = 5
Instance terminated by USER, pid = 144
---
please note: the shutdwon was not initiated by any admin
or user but done by the system itself after the box boots
up.

__
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: john
  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).


_
Tired of spam? Get advanced junk mail protection with MSN 8. 
http://join.msn.com/?page=features/junkmail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arup Nanda
 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).

instance shutdown problem ? (please help)

[john]

ver 8i NT4.o
all services have been set to automatic and start fine

however the NT Event Viwer and the oracle "alert.log"
show that the instance being shutdown just after the 
box boots up.

how do we rectify this problem ?

below is from the alert.log

ORA-205 signalled during: alter database mount exclusive..
Shutting down instance (abort)
License high water mark = 5
Instance terminated by USER, pid = 144
---
please note: the shutdwon was not initiated by any admin
or user but done by the system itself after the box boots
up.

__
Do you Yahoo!?
Yahoo! Mail Plus – Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: john
  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: New development in Cobol or PL/SQL - please help

[Deshpande, Kirti]

Babette,
We are rolling out a *major* app with 14-16 production databases. All of the *core* 
processing is in COBOL. The app has a Web front end.

COBOL won't die. 

I picked up a bumper sticker, from Fujitsu's booth at Oracle World last week,  that 
asks  "got COBOL?".  

- Kirti

PS : I did COBOL for 12 years :-) 



-Original Message-
Sent: Wednesday, November 20, 2002 5:54 PM
To: Multiple recipients of list ORACLE-L


Thanks for all the comments
I did do a little more digging.

I found out that the reason they are using Cobol
is that there are Cobol programs that have the
business logic for doing benefit calculations.
They do not want to re-write those modules.
So they are doing the entire application in Cobol
and only using the database as a place to save
the data.

As far as staff and skills, the current job market
has an excess of persons with PL/SQL skills.
So I don't think the decisions was made with any
respect to how many people will be able to maintain
this over the next 10 years.  The number of people
with COBOL skills is declining at the organization.
But if they keep doing new development in COBOL,
then maybe they will be able to convince the younger
programmers to learn it :-P

I will have to look into the MQ-Series module for
Oracle that was mentionned.

-Original Message-
WILLIAMS
Sent: Monday, November 18, 2002 8:09 AM
To: Multiple recipients of list ORACLE-L



Babette - For some code, PL/SQL will offer significantly better performance.
For other code it may not matter so much. One of an organization's biggest
hidden investments is their code. If they feel COBOL is where they prefer to
invest, that isn't the end of the world. For one thing, it doesn't tie them
as closely to Oracle as coding everything in PL/SQL would. If you want
better reasons not to do this, you need to nose around and find who the
players are and what is their motivation. Arguments succeed much better when
you know your audience.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, November 15, 2002 4:24 PM
To: Multiple recipients of list ORACLE-L


I just found out today that we have a major development initiative that is
starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across to
the mainframe for MQ series to invoke Pro*Cobol programs that will then do
the processing (accessing data and doing calculations) and then return data.

If anyone has been in this or a similar situation, please help.
I need some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

[snipped]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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.com
--
Author: Deshpande, Kirti
  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: New development in Cobol or PL/SQL - please help

[Alex]

On Wed, 20 Nov 2002, Babette Turner-Underwood wrote:

> As far as staff and skills, the current job market
> has an excess of persons with PL/SQL skills.
> So I don't think the decisions was made with any
> respect to how many people will be able to maintain
> this over the next 10 years.  The number of people
> with COBOL skills is declining at the organization.
> But if they keep doing new development in COBOL,
> then maybe they will be able to convince the younger
> programmers to learn it :-P

Only if the younger programmers are in their fifties. COBOL is a
maintainance programmers language.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Alex
  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: New development in Cobol or PL/SQL - please help

[Babette Turner-Underwood]

Thanks for all the comments
I did do a little more digging.

I found out that the reason they are using Cobol
is that there are Cobol programs that have the
business logic for doing benefit calculations.
They do not want to re-write those modules.
So they are doing the entire application in Cobol
and only using the database as a place to save
the data.

As far as staff and skills, the current job market
has an excess of persons with PL/SQL skills.
So I don't think the decisions was made with any
respect to how many people will be able to maintain
this over the next 10 years.  The number of people
with COBOL skills is declining at the organization.
But if they keep doing new development in COBOL,
then maybe they will be able to convince the younger
programmers to learn it :-P

I will have to look into the MQ-Series module for
Oracle that was mentionned.

-Original Message-
WILLIAMS
Sent: Monday, November 18, 2002 8:09 AM
To: Multiple recipients of list ORACLE-L



Babette - For some code, PL/SQL will offer significantly better performance.
For other code it may not matter so much. One of an organization's biggest
hidden investments is their code. If they feel COBOL is where they prefer to
invest, that isn't the end of the world. For one thing, it doesn't tie them
as closely to Oracle as coding everything in PL/SQL would. If you want
better reasons not to do this, you need to nose around and find who the
players are and what is their motivation. Arguments succeed much better when
you know your audience.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, November 15, 2002 4:24 PM
To: Multiple recipients of list ORACLE-L


I just found out today that we have a major development initiative that is
starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across to
the mainframe for MQ series to invoke Pro*Cobol programs that will then do
the processing (accessing data and doing calculations) and then return data.

If anyone has been in this or a similar situation, please help.
I need some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

[snipped]

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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: New development in Cobol or PL/SQL - please help

[DENNIS WILLIAMS]

Dick, others - Question for you. Consider that a company's custom-written
software is one of its largest hidden investments. To do it successfully, a
company must not only invest a lot of programmer time, but time and effort
from people all over the organization to dig in an really understand the
requirements. For many companies in retrospect COBOL has been very
successful because they haven't had to rewrite the programs in many years.
You may argue that maybe that was too long, but I would respond that it is
better to rewrite for business reasons rather than because of technological
obsolescence. You also must pick a popular language so you have many
experienced programmers available to hire. Now, which language would you
advise a large company to select? Do you feel that the "new COBOL" has
emerged, a general-purpose business language that will be around for years,
widely available?
  - Visual Basic - Hey even Microsoft is upsetting this one as it moves to
.NET. Need I elaborate on how you will be eternally wedded to MS.
  - C++ - Seems to be doing okay at the moment, but several years ago there
were a lot of articles predicting its demise.
  - PL/SQL - Excellent to cure specific performance problems, but use it for
everything? Well you better not carp about Oracle licensing fees. 
  - Java - This is the current bet here. However, it seems somewhat
cumbersome as a COBOL replacement. It seems as if your programmers need to
be fairly knowledgeable technically.
  - C# - Does it have a future beyond being a Java spoiler?
  - Of course, before you count out COBOL, you should review all the
languages over the years that were going to replace COBOL, but are now
answers to computer trivia questions - Pascal, Ada, Modula-2, and the list
just goes on.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, November 18, 2002 5:52 PM
To: Multiple recipients of list ORACLE-L


Tom,

IMHO, if Babette's organization "see themselves as *never* leaving the
Cobol
arena" then it's time to dust off the resume as that organization will
become
extinct.  No one that I know of is learning Cobol anymore and there are no
classes at the local universities on the subject.  Fortran classes and
programmers are also becoming a scarce resource to find which is why we left
ManMan and TurboImage for PeopleSoft and Oracle.

Dick Goulet

Reply Separator
Author: "Mercadante; Thomas F" <[EMAIL PROTECTED]>
Date:   11/18/2002 5:03 AM

Babette,

The decision really comes down to the organization.  If they see themselves
as *never* leaving the Cobol arena, and they have an ample supply of Cobol
programmers, then they should stay with it.

What you could do is to make friends with the applications people, and show
them how PL/SQL works.  What you will find is that they will take to PL/SQL
like a fish to water.  And pretty soon, more and more PL/SQL packages will
be written that are simply called by the Cobol programs.  Cobol would then
be a simple entry point to the database - able to interface nicely with the
operating system (reading and writing flat files, producing reports and
forms), while the majority of the logic may be written in PL/SQL.

Maybe, just maybe, the person making the decision see's no benefit to using
PL/SQL.  And given your local labor market, maybe he's right!

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Sunday, November 17, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L


"Khedr, Waleed" wrote:
> 
> Cobol! Again!:(
> 
> -Original Message-
> Sent: Friday, November 15, 2002 5:24 PM
> To: Multiple recipients of list ORACLE-L
> 
> I just found out today that we have a major development initiative that is
> starting and they are planning on using Pro*Cobol to develop the
> application. (my head is still shaking in disbelief!!!)
> 
> So we will have a Java front-end, invoking MQ series that will go across
to
> the mainframe for MQ series to invoke Pro*Cobol programs that will then do
> the processing (accessing data and doing calculations) and then return
data.
> 
> If anyone has been in this or a similar situation, please help.
> I need some really good arguments as to why we should put the business
logic
> into PL/SQL instead of Pro*Cobol.
> 
> I understand the reason we are using Oracle is that the director has 15
> years experience with it and loves it.  Aaargh!!!
> 
> thanks
> Babette
> 

May I play the devil's advocate? Even if Pro*Cobol seems to be a weird
choice, there may be a case for not coding the logic in PL/SQL :
database portability. I have heard recently of a very, very, very big
company dumping Oracle in favour of DB2. Reason ? Cost. I guess that in
such a case, porting a Pro*Cobol program is easier than PL/SQL.

Re[2]:RE: New development in Cobol or PL/SQL - please help

[dgoulet]

Gene,

Well at least I'm not the only one who got tossed out of the frypan into the
fire!!

Dick Goulet

Reply Separator
Author: "Gene Sais" <[EMAIL PROTECTED]>
Date:   11/18/2002 7:27 AM

I disagree with your last statement.  Since IBM purchased informix, we are in
battle with their so-called concurrent licensing ripoff. 

>>> [EMAIL PROTECTED] 11/17/02 09:43PM >>>
Ron,

Thankyou, I appreciate it.  And for the individual who proposed that it
might be better to do it in Pro*Cobol for database independence.  We have had
the thought of dumping Oracle for it's DB/2 competitor, until we found out that
DB/2 was no cheaper than Oracle in the end run.  Probably the only benefit is
that IBM is more slack on enforcing their licenses.

Dick Goulet

Reply Separator
Author: Ron/Sarah Yount <[EMAIL PROTECTED]>
Date:   11/16/2002 2:53 PM

In the "for what it is worth" department:

In addition to Dick's comments (with which I agree)

Be careful how you approach this situation.  If you wish to succeed, it
may be key to let the powers that be know that you are not proposing
bleeding edge solutions, and that looking down the road towards total
cost of ownership and supporting the application, it may behoove them to
consider something more mainstream.

Nobody ever truly wins a discussion by starting an argument with someone
in a higher level of authority.

Perhaps you should inquire about the "why" of the decisions so you
understand the key issues to address to propose a better one.

Good Luck,

Yep, even technical decisions require us to exercise high levels of
diplomacy :-)

-Ron-

-Original Message-
[EMAIL PROTECTED] 
Sent: Saturday, November 16, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L


Babette,

This is one of those "from this old turd to that old fart" messages.
I've been around Oracle since 1985 & I love it too.  But it sounds like
the director has a serious problem with new technology.  Doing anything
in COBOL today?  Even PeopleSoft is busy re-writing their code in C++.
Seesh his age is seriously showing.  Oracle already has an adapter to MQ
series, why re-invent the wheel when someone else has already done a
better job of it.  Then code the business logic in PL/SQL so that not
only can this application use it, but any other that comes around.  I
believe it's known as code reuse.  Another old term.  You might start
off by handing him a copy of Oracle 8i new features & functions.  BTW:
add a copy of the 9i edition as well.

Dick Goulet

Reply Separator
Author: "Babette Turner-Underwood" <[EMAIL PROTECTED]>
Date:   11/15/2002 2:24 PM

I just found out today that we have a major development initiative that
is starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across
to the mainframe for MQ series to invoke Pro*Cobol programs that will
then do the processing (accessing data and doing calculations) and then
return data.

If anyone has been in this or a similar situation, please help. I need
some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Babette Turner-Underwood
  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.com 
-- 
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.com 
-- 
Aut

Re:RE: New development in Cobol or PL/SQL - please help

[dgoulet]

Tom,

IMHO, if Babette's organization "see themselves as *never* leaving the Cobol
arena" then it's time to dust off the resume as that organization will become
extinct.  No one that I know of is learning Cobol anymore and there are no
classes at the local universities on the subject.  Fortran classes and
programmers are also becoming a scarce resource to find which is why we left
ManMan and TurboImage for PeopleSoft and Oracle.

Dick Goulet

Reply Separator
Author: "Mercadante; Thomas F" <[EMAIL PROTECTED]>
Date:   11/18/2002 5:03 AM

Babette,

The decision really comes down to the organization.  If they see themselves
as *never* leaving the Cobol arena, and they have an ample supply of Cobol
programmers, then they should stay with it.

What you could do is to make friends with the applications people, and show
them how PL/SQL works.  What you will find is that they will take to PL/SQL
like a fish to water.  And pretty soon, more and more PL/SQL packages will
be written that are simply called by the Cobol programs.  Cobol would then
be a simple entry point to the database - able to interface nicely with the
operating system (reading and writing flat files, producing reports and
forms), while the majority of the logic may be written in PL/SQL.

Maybe, just maybe, the person making the decision see's no benefit to using
PL/SQL.  And given your local labor market, maybe he's right!

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Sunday, November 17, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L


"Khedr, Waleed" wrote:
> 
> Cobol! Again!:(
> 
> -Original Message-
> Sent: Friday, November 15, 2002 5:24 PM
> To: Multiple recipients of list ORACLE-L
> 
> I just found out today that we have a major development initiative that is
> starting and they are planning on using Pro*Cobol to develop the
> application. (my head is still shaking in disbelief!!!)
> 
> So we will have a Java front-end, invoking MQ series that will go across
to
> the mainframe for MQ series to invoke Pro*Cobol programs that will then do
> the processing (accessing data and doing calculations) and then return
data.
> 
> If anyone has been in this or a similar situation, please help.
> I need some really good arguments as to why we should put the business
logic
> into PL/SQL instead of Pro*Cobol.
> 
> I understand the reason we are using Oracle is that the director has 15
> years experience with it and loves it.  Aaargh!!!
> 
> thanks
> Babette
> 

May I play the devil's advocate? Even if Pro*Cobol seems to be a weird
choice, there may be a case for not coding the logic in PL/SQL :
database portability. I have heard recently of a very, very, very big
company dumping Oracle in favour of DB2. Reason ? Cost. I guess that in
such a case, porting a Pro*Cobol program is easier than PL/SQL.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  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.com
-- 
Author: Mercadante, Thomas F
  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.com
-- 
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:RE: New development in Cobol or PL/SQL - please help

[Gene Sais]

I disagree with your last statement.  Since IBM purchased informix, we are in battle 
with their so-called concurrent licensing ripoff. 

>>> [EMAIL PROTECTED] 11/17/02 09:43PM >>>
Ron,

Thankyou, I appreciate it.  And for the individual who proposed that it
might be better to do it in Pro*Cobol for database independence.  We have had
the thought of dumping Oracle for it's DB/2 competitor, until we found out that
DB/2 was no cheaper than Oracle in the end run.  Probably the only benefit is
that IBM is more slack on enforcing their licenses.

Dick Goulet

Reply Separator
Author: Ron/Sarah Yount <[EMAIL PROTECTED]>
Date:   11/16/2002 2:53 PM

In the "for what it is worth" department:

In addition to Dick's comments (with which I agree)

Be careful how you approach this situation.  If you wish to succeed, it
may be key to let the powers that be know that you are not proposing
bleeding edge solutions, and that looking down the road towards total
cost of ownership and supporting the application, it may behoove them to
consider something more mainstream.

Nobody ever truly wins a discussion by starting an argument with someone
in a higher level of authority.

Perhaps you should inquire about the "why" of the decisions so you
understand the key issues to address to propose a better one.

Good Luck,

Yep, even technical decisions require us to exercise high levels of
diplomacy :-)

-Ron-

-Original Message-
[EMAIL PROTECTED] 
Sent: Saturday, November 16, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L


Babette,

This is one of those "from this old turd to that old fart" messages.
I've been around Oracle since 1985 & I love it too.  But it sounds like
the director has a serious problem with new technology.  Doing anything
in COBOL today?  Even PeopleSoft is busy re-writing their code in C++.
Seesh his age is seriously showing.  Oracle already has an adapter to MQ
series, why re-invent the wheel when someone else has already done a
better job of it.  Then code the business logic in PL/SQL so that not
only can this application use it, but any other that comes around.  I
believe it's known as code reuse.  Another old term.  You might start
off by handing him a copy of Oracle 8i new features & functions.  BTW:
add a copy of the 9i edition as well.

Dick Goulet

Reply Separator
Author: "Babette Turner-Underwood" <[EMAIL PROTECTED]>
Date:   11/15/2002 2:24 PM

I just found out today that we have a major development initiative that
is starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across
to the mainframe for MQ series to invoke Pro*Cobol programs that will
then do the processing (accessing data and doing calculations) and then
return data.

If anyone has been in this or a similar situation, please help. I need
some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Babette Turner-Underwood
  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.com 
-- 
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.com 
-- 
Author: Ron/Sarah Yount
  INET: [EMAIL PROTECTED] 

Fat City Network Services-- 858-538-5051 http://www.fatcity.com 
San Diego, California-- Mailing list and web hosting services
---

RE: New development in Cobol or PL/SQL - please help

[Jamadagni, Rajendra]

Title: RE: New development in Cobol or PL/SQL - please help





Rick,


You put BI into db using pl/sql anyways ... (well Java is another thing ..)


Raj
__
Rajendra Jamadagni      MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. 
QOTD: Any clod can have facts, but having an opinion is an art!



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Monday, November 18, 2002 9:19 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: New development in Cobol or PL/SQL - please help




Babette,


If they are a Cobol shop then there is nothing wrong with using Pro*Cobol.
I disagree with your statement about using PL/SQL for
the business logic. I personally think you should put as much of that in
the database.


Rick




   
    "Mercadante,   
    Thomas F"  To: Multiple recipients of list ORACLE-L    
    <[EMAIL PROTECTED]    <[EMAIL PROTECTED]> 
    ate.ny.us> cc: 
    Sent by:   Subject: RE: New development in Cobol or PL/SQL - please    
    [EMAIL PROTECTED]    help   
   
   
    11/18/2002 08:03   
    AM 
    Please respond 
    to ORACLE-L    
   
   





Babette,


The decision really comes down to the organization.  If they see themselves
as *never* leaving the Cobol arena, and they have an ample supply of Cobol
programmers, then they should stay with it.


What you could do is to make friends with the applications people, and show
them how PL/SQL works.  What you will find is that they will take to PL/SQL
like a fish to water.  And pretty soon, more and more PL/SQL packages will
be written that are simply called by the Cobol programs.  Cobol would then
be a simple entry point to the database - able to interface nicely with the
operating system (reading and writing flat files, producing reports and
forms), while the majority of the logic may be written in PL/SQL.


Maybe, just maybe, the person making the decision see's no benefit to using
PL/SQL.  And given your local labor market, maybe he's right!


Tom Mercadante
Oracle Certified Professional



-Original Message-
Sent: Sunday, November 17, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L



"Khedr, Waleed" wrote:
>
> Cobol! Again!:(
>
> -Original Message-
> Sent: Friday, November 15, 2002 5:24 PM
> To: Multiple recipients of list ORACLE-L
>
> I just found out today that we have a major development initiative that
is
> starting and they are planning on using Pro*Cobol to develop the
> application. (my head is still shaking in disbelief!!!)
>
> So we will have a Java front-end, invoking MQ series that will go across
to
> the mainframe for MQ series to invoke Pro*Cobol programs that will then
do
> the processing (accessing data and doing calculations) and then return
data.
>
> If anyone has been in this or a similar situation, please help.
> I need some really good arguments as to why we should put the business
logic
> into PL/SQL instead of Pro*Cobol.
>
> I understand the reason we are using Oracle is that the director has 15
> years experience with it and loves it.  Aaargh!!!
>
> thanks
> Babette
>


May I play the devil's advocate? Even if Pro*Cobol seems to be a weird
choice, there may be a case for not coding the logic in PL/SQL :
database portability. I have heard recently of a very, very, very big
company dumping Oracle in favour of DB2. Reason ? Cost. I guess that in
such a case, porting a Pro*Cobol program is easier than PL/SQL.


--
Regards,


Stephane Faroult
Orio

RE: New development in Cobol or PL/SQL - please help

[Rick_Cale]

Babette,

If they are a Cobol shop then there is nothing wrong with using Pro*Cobol.
I disagree with your statement about using PL/SQL for
the business logic. I personally think you should put as much of that in
the database.

Rick



   

"Mercadante,   

Thomas F"  To: Multiple recipients of list 
ORACLE-L
<[EMAIL PROTECTED]<[EMAIL PROTECTED]> 

ate.ny.us> cc: 

Sent by:   Subject: RE: New development in Cobol 
or PL/SQL - please
[EMAIL PROTECTED]help   

   

   

11/18/2002 08:03   

AM 

Please respond 

to ORACLE-L

   

   





Babette,

The decision really comes down to the organization.  If they see themselves
as *never* leaving the Cobol arena, and they have an ample supply of Cobol
programmers, then they should stay with it.

What you could do is to make friends with the applications people, and show
them how PL/SQL works.  What you will find is that they will take to PL/SQL
like a fish to water.  And pretty soon, more and more PL/SQL packages will
be written that are simply called by the Cobol programs.  Cobol would then
be a simple entry point to the database - able to interface nicely with the
operating system (reading and writing flat files, producing reports and
forms), while the majority of the logic may be written in PL/SQL.

Maybe, just maybe, the person making the decision see's no benefit to using
PL/SQL.  And given your local labor market, maybe he's right!

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Sunday, November 17, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L


"Khedr, Waleed" wrote:
>
> Cobol! Again!:(
>
> -Original Message-
> Sent: Friday, November 15, 2002 5:24 PM
> To: Multiple recipients of list ORACLE-L
>
> I just found out today that we have a major development initiative that
is
> starting and they are planning on using Pro*Cobol to develop the
> application. (my head is still shaking in disbelief!!!)
>
> So we will have a Java front-end, invoking MQ series that will go across
to
> the mainframe for MQ series to invoke Pro*Cobol programs that will then
do
> the processing (accessing data and doing calculations) and then return
data.
>
> If anyone has been in this or a similar situation, please help.
> I need some really good arguments as to why we should put the business
logic
> into PL/SQL instead of Pro*Cobol.
>
> I understand the reason we are using Oracle is that the director has 15
> years experience with it and loves it.  Aaargh!!!
>
> thanks
> Babette
>

May I play the devil's advocate? Even if Pro*Cobol seems to be a weird
choice, there may be a case for not coding the logic in PL/SQL :
database portability. I have heard recently of a very, very, very big
company dumping Oracle in favour of DB2. Reason ? Cost. I guess that in
such a case, porting a Pro*Cobol program is easier than PL/SQL.

--
Regards,

Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
  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 subscri

RE: New development in Cobol or PL/SQL - please help

[Mercadante, Thomas F]

Babette,

The decision really comes down to the organization.  If they see themselves
as *never* leaving the Cobol arena, and they have an ample supply of Cobol
programmers, then they should stay with it.

What you could do is to make friends with the applications people, and show
them how PL/SQL works.  What you will find is that they will take to PL/SQL
like a fish to water.  And pretty soon, more and more PL/SQL packages will
be written that are simply called by the Cobol programs.  Cobol would then
be a simple entry point to the database - able to interface nicely with the
operating system (reading and writing flat files, producing reports and
forms), while the majority of the logic may be written in PL/SQL.

Maybe, just maybe, the person making the decision see's no benefit to using
PL/SQL.  And given your local labor market, maybe he's right!

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Sunday, November 17, 2002 1:18 PM
To: Multiple recipients of list ORACLE-L


"Khedr, Waleed" wrote:
> 
> Cobol! Again!:(
> 
> -Original Message-
> Sent: Friday, November 15, 2002 5:24 PM
> To: Multiple recipients of list ORACLE-L
> 
> I just found out today that we have a major development initiative that is
> starting and they are planning on using Pro*Cobol to develop the
> application. (my head is still shaking in disbelief!!!)
> 
> So we will have a Java front-end, invoking MQ series that will go across
to
> the mainframe for MQ series to invoke Pro*Cobol programs that will then do
> the processing (accessing data and doing calculations) and then return
data.
> 
> If anyone has been in this or a similar situation, please help.
> I need some really good arguments as to why we should put the business
logic
> into PL/SQL instead of Pro*Cobol.
> 
> I understand the reason we are using Oracle is that the director has 15
> years experience with it and loves it.  Aaargh!!!
> 
> thanks
> Babette
> 

May I play the devil's advocate? Even if Pro*Cobol seems to be a weird
choice, there may be a case for not coding the logic in PL/SQL :
database portability. I have heard recently of a very, very, very big
company dumping Oracle in favour of DB2. Reason ? Cost. I guess that in
such a case, porting a Pro*Cobol program is easier than PL/SQL.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  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.com
-- 
Author: Mercadante, Thomas F
  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: New development in Cobol or PL/SQL - please help

[DENNIS WILLIAMS]

Babette - For some code, PL/SQL will offer significantly better performance.
For other code it may not matter so much. One of an organization's biggest
hidden investments is their code. If they feel COBOL is where they prefer to
invest, that isn't the end of the world. For one thing, it doesn't tie them
as closely to Oracle as coding everything in PL/SQL would. If you want
better reasons not to do this, you need to nose around and find who the
players are and what is their motivation. Arguments succeed much better when
you know your audience.

Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Friday, November 15, 2002 4:24 PM
To: Multiple recipients of list ORACLE-L


I just found out today that we have a major development initiative that is
starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across to
the mainframe for MQ series to invoke Pro*Cobol programs that will then do
the processing (accessing data and doing calculations) and then return data.

If anyone has been in this or a similar situation, please help.
I need some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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.com
-- 
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:RE: New development in Cobol or PL/SQL - please help

[dgoulet]

Ron,

Thankyou, I appreciate it.  And for the individual who proposed that it
might be better to do it in Pro*Cobol for database independence.  We have had
the thought of dumping Oracle for it's DB/2 competitor, until we found out that
DB/2 was no cheaper than Oracle in the end run.  Probably the only benefit is
that IBM is more slack on enforcing their licenses.

Dick Goulet

Reply Separator
Author: Ron/Sarah Yount <[EMAIL PROTECTED]>
Date:   11/16/2002 2:53 PM

In the "for what it is worth" department:

In addition to Dick's comments (with which I agree)

Be careful how you approach this situation.  If you wish to succeed, it
may be key to let the powers that be know that you are not proposing
bleeding edge solutions, and that looking down the road towards total
cost of ownership and supporting the application, it may behoove them to
consider something more mainstream.

Nobody ever truly wins a discussion by starting an argument with someone
in a higher level of authority.

Perhaps you should inquire about the "why" of the decisions so you
understand the key issues to address to propose a better one.

Good Luck,

Yep, even technical decisions require us to exercise high levels of
diplomacy :-)

-Ron-

-Original Message-
[EMAIL PROTECTED]
Sent: Saturday, November 16, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L


Babette,

This is one of those "from this old turd to that old fart" messages.
I've been around Oracle since 1985 & I love it too.  But it sounds like
the director has a serious problem with new technology.  Doing anything
in COBOL today?  Even PeopleSoft is busy re-writing their code in C++.
Seesh his age is seriously showing.  Oracle already has an adapter to MQ
series, why re-invent the wheel when someone else has already done a
better job of it.  Then code the business logic in PL/SQL so that not
only can this application use it, but any other that comes around.  I
believe it's known as code reuse.  Another old term.  You might start
off by handing him a copy of Oracle 8i new features & functions.  BTW:
add a copy of the 9i edition as well.

Dick Goulet

Reply Separator
Author: "Babette Turner-Underwood" <[EMAIL PROTECTED]>
Date:   11/15/2002 2:24 PM

I just found out today that we have a major development initiative that
is starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across
to the mainframe for MQ series to invoke Pro*Cobol programs that will
then do the processing (accessing data and doing calculations) and then
return data.

If anyone has been in this or a similar situation, please help. I need
some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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.com
-- 
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.com
-- 
Author: Ron/Sarah Yount
  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 th

Re: New development in Cobol or PL/SQL - please help

[Stephane Faroult]

"Khedr, Waleed" wrote:
> 
> Cobol! Again!:(
> 
> -Original Message-
> Sent: Friday, November 15, 2002 5:24 PM
> To: Multiple recipients of list ORACLE-L
> 
> I just found out today that we have a major development initiative that is
> starting and they are planning on using Pro*Cobol to develop the
> application. (my head is still shaking in disbelief!!!)
> 
> So we will have a Java front-end, invoking MQ series that will go across to
> the mainframe for MQ series to invoke Pro*Cobol programs that will then do
> the processing (accessing data and doing calculations) and then return data.
> 
> If anyone has been in this or a similar situation, please help.
> I need some really good arguments as to why we should put the business logic
> into PL/SQL instead of Pro*Cobol.
> 
> I understand the reason we are using Oracle is that the director has 15
> years experience with it and loves it.  Aaargh!!!
> 
> thanks
> Babette
> 

May I play the devil's advocate? Even if Pro*Cobol seems to be a weird
choice, there may be a case for not coding the logic in PL/SQL :
database portability. I have heard recently of a very, very, very big
company dumping Oracle in favour of DB2. Reason ? Cost. I guess that in
such a case, porting a Pro*Cobol program is easier than PL/SQL.

-- 
Regards,

Stephane Faroult
Oriole Software
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  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: New development in Cobol or PL/SQL - please help

[Yechiel Adar]

If I understood correctly your whole application will be on the mainframe
with web front end to interface with the users. We are a mainframe (mostly)
company and we have dozens of Cobol programmer on the mainframe and NO sql
programmer. (I have to fight the open system group to get at least ONE sql
programmer so the DBA team will not have to write whatever sql programs the
APPLICATIONS need).
So a new project on the mainframe will definitely be in Cobol. I still do
not understand why they want Oracle on the mainframe. We are working with
ADABAS and get very good result. Most other mainframe that I know use DB2. I
have not heard of anyone using Oracle on mainframe (at least under IBM MVS).

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Saturday, November 16, 2002 12:24 AM


> I just found out today that we have a major development initiative that is
> starting and they are planning on using Pro*Cobol to develop the
> application. (my head is still shaking in disbelief!!!)
>
> So we will have a Java front-end, invoking MQ series that will go across
to
> the mainframe for MQ series to invoke Pro*Cobol programs that will then do
> the processing (accessing data and doing calculations) and then return
data.
>
> If anyone has been in this or a similar situation, please help.
> I need some really good arguments as to why we should put the business
logic
> into PL/SQL instead of Pro*Cobol.
>
> I understand the reason we are using Oracle is that the director has 15
> years experience with it and loves it.  Aaargh!!!
>
> thanks
> Babette
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Babette Turner-Underwood
>   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.com
-- 
Author: Yechiel Adar
  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: New development in Cobol or PL/SQL - please help

[Khedr, Waleed]

Cobol! Again!:(

-Original Message-
Sent: Friday, November 15, 2002 5:24 PM
To: Multiple recipients of list ORACLE-L


I just found out today that we have a major development initiative that is
starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across to
the mainframe for MQ series to invoke Pro*Cobol programs that will then do
the processing (accessing data and doing calculations) and then return data.

If anyone has been in this or a similar situation, please help.
I need some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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.com
-- 
Author: Khedr, Waleed
  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: New development in Cobol or PL/SQL - please help

[Ron/Sarah Yount]

In the "for what it is worth" department:

In addition to Dick's comments (with which I agree)

Be careful how you approach this situation.  If you wish to succeed, it
may be key to let the powers that be know that you are not proposing
bleeding edge solutions, and that looking down the road towards total
cost of ownership and supporting the application, it may behoove them to
consider something more mainstream.

Nobody ever truly wins a discussion by starting an argument with someone
in a higher level of authority.

Perhaps you should inquire about the "why" of the decisions so you
understand the key issues to address to propose a better one.

Good Luck,

Yep, even technical decisions require us to exercise high levels of
diplomacy :-)

-Ron-

-Original Message-
[EMAIL PROTECTED]
Sent: Saturday, November 16, 2002 3:13 PM
To: Multiple recipients of list ORACLE-L


Babette,

This is one of those "from this old turd to that old fart" messages.
I've been around Oracle since 1985 & I love it too.  But it sounds like
the director has a serious problem with new technology.  Doing anything
in COBOL today?  Even PeopleSoft is busy re-writing their code in C++.
Seesh his age is seriously showing.  Oracle already has an adapter to MQ
series, why re-invent the wheel when someone else has already done a
better job of it.  Then code the business logic in PL/SQL so that not
only can this application use it, but any other that comes around.  I
believe it's known as code reuse.  Another old term.  You might start
off by handing him a copy of Oracle 8i new features & functions.  BTW:
add a copy of the 9i edition as well.

Dick Goulet

Reply Separator
Author: "Babette Turner-Underwood" <[EMAIL PROTECTED]>
Date:   11/15/2002 2:24 PM

I just found out today that we have a major development initiative that
is starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across
to the mainframe for MQ series to invoke Pro*Cobol programs that will
then do the processing (accessing data and doing calculations) and then
return data.

If anyone has been in this or a similar situation, please help. I need
some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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.com
-- 
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.com
-- 
Author: Ron/Sarah Yount
  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:New development in Cobol or PL/SQL - please help

[dgoulet]

Babette,

This is one of those "from this old turd to that old fart" messages.  I've
been around Oracle since 1985 & I love it too.  But it sounds like the director
has a serious problem with new technology.  Doing anything in COBOL today?  Even
PeopleSoft is busy re-writing their code in C++.  Seesh his age is seriously
showing.  Oracle already has an adapter to MQ series, why re-invent the wheel
when someone else has already done a better job of it.  Then code the business
logic in PL/SQL so that not only can this application use it, but any other that
comes around.  I believe it's known as code reuse.  Another old term.  You might
start off by handing him a copy of Oracle 8i new features & functions.  BTW: add
a copy of the 9i edition as well.

Dick Goulet

Reply Separator
Author: "Babette Turner-Underwood" <[EMAIL PROTECTED]>
Date:   11/15/2002 2:24 PM

I just found out today that we have a major development initiative that is
starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across to
the mainframe for MQ series to invoke Pro*Cobol programs that will then do
the processing (accessing data and doing calculations) and then return data.

If anyone has been in this or a similar situation, please help.
I need some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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.com
-- 
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).

New development in Cobol or PL/SQL - please help

[Babette Turner-Underwood]

I just found out today that we have a major development initiative that is
starting and they are planning on using Pro*Cobol to develop the
application. (my head is still shaking in disbelief!!!)

So we will have a Java front-end, invoking MQ series that will go across to
the mainframe for MQ series to invoke Pro*Cobol programs that will then do
the processing (accessing data and doing calculations) and then return data.

If anyone has been in this or a similar situation, please help.
I need some really good arguments as to why we should put the business logic
into PL/SQL instead of Pro*Cobol.

I understand the reason we are using Oracle is that the director has 15
years experience with it and loves it.  Aaargh!!!

thanks
Babette

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Babette Turner-Underwood
  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: Please help, comment required urgently

[Tim Gorman]

Barb,

There's nothing in the STATSPACK report itself that mentions 25% or 16% or
whatever, so your math skills are just fine...  :-)

---

It is the YAPP report (obtained by uploading STATSPACK report to
full-functionality YAPP processor within www.oraperf.com) that shows these
breakdowns.  The YAPP report first trims out the time belonging to "idle"
wait-events, with the time for the remaining wait-events plus CPU times from
V$SYSSTAT comprising "total response-time".  This total breaks first down
into "CPU Time" (a.k.a. "processing time") and "Wait Time" (a.k.a. remaining
non-idle wait-events).

For the YAPP report resulting from the STATSPACK report provided by George,
the first breakdown of CPU/Wait was roughly 57% vs 43%, if I remember
correctly (I no longer have the email or the report).  Drilling down on the
"CPU Time" (i.e. 57%) provided further breakdown into "Parse Time" (showed
less than 1%), "Recursive CPU Time" (showed less than 1%), and "Other CPU
Time" (showed greater than 99%).  So, 99% of the 57% of "total
response-time" was due to "Other CPU Time".  Drilling down into the details
of "Other CPU Time" showed a "top SQL" report sorted by buffer gets (a.k.a.
logical reads) where one particular SQL statement accounted for about 55% of
all buffer gets.  So, 57% times 99% times 55% equals roughly 25% as I did it
in my head...

...so that's where I got the "25% of total response-time" number from...

Going in the other direction, remember that 43% of total response-time was
"Wait Time".  Drilling down on that showed the "Latch Free" wait-event
consuming about 40% of all wait-time.  Drilling down on the "Latch free"
wait-event showed the "cache buffers chains" latch consuming 99% of all
"latch free" wait-events.  So, 43% times 40% times 99% equals roughly 16%
(again, when I do it in my head)...

...and that's where I got the "16% of total response-time" number from...

---

None of this talk about "breakdowns" and "drilling down" makes any sense
whatsoever unless you are looking at a YAPP report in front of you.  To
obtain one, you first have to register for a free account on
www.oraperf.com.  Once you have obtained a free account, then log in and
follow the instructions to upload the STATSPACK report to the website.  The
website will return an HTML document which is the YAPP report.  It is a
fully hyperlinked document, so my comments above about "drilling down"
should make more sense when you see the report...

Additionally, in the left-hand navigation bar there is a link for "white
papers".  There are only 5-6 white papers currently available, but the first
two are the most useful.  The first is the "YAPP Methodology" paper written
by Anjo Kolk et al.  The second is about using STATSPACK to obtain a YAPP
report, by Bjorn Engsig.  That's pretty much what we've been discussing
here...

---

As Jared had commented earlier, this report is the probably the best thing
since sliced bread.  It is very much a waste of time to attempt to make
heads or tails of the standard STATSPACK or BSTAT/ESTAT reports.  Instead,
upload the text files for those reports up to www.oraperf.com and read a
report that makes sense...

Hope this helps...

-Tim

P.S.Again, big thanks to Anjo and his friends for all the hard work in
putting together and maintaining this website...

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, October 18, 2002 12:12 PM


> HELP
> (I'm an idiot)
> OK, never claimed math was my strong suit.
> Why is this 25% of total response time?  (Truthfully, I don't really
> understand the column heading "of total.."
>
> Here are the top SQL statements ordered by physical reads per execute:
>
> Statement  Executes Physical   Reads Hash Value
> Of Total
>   Reads/Execute
> SELECT T5.CONFL 903549033  608.01 3509998681
> 47.31 %
>
> Barb
>
>
> > --
> > From: Tim Gorman[SMTP:[EMAIL PROTECTED]]
> > Reply To: [EMAIL PROTECTED]
> > Sent: Friday, October 18, 2002 7:53 AM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Re: Please help, comment required urgently
> >
> > George,
> >
> > Two things jump out together:
> >
> > * The SQL statement with hash value = 3509998681 is consuming about
> > 25% of the total response-time (i.e. total processing plus total wait)
on
> > the system.  This SQL statement is executing 900 times during the
one-hour
> > sample period...
> > * Waits on the "cache buffers chains" are consuming another 

RE: Please help, comment required urgently

[Baker, Barbara]

HELP
(I'm an idiot)
OK, never claimed math was my strong suit.  
Why is this 25% of total response time?  (Truthfully, I don't really
understand the column heading "of total.."

Here are the top SQL statements ordered by physical reads per execute: 

StatementExecutes   Physical  Reads Hash Value
Of Total
  Reads/Execute
SELECT T5.CONFL 903549033608.01 3509998681
47.31 % 

Barb


> --
> From: Tim Gorman[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, October 18, 2002 7:53 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Re: Please help, comment required urgently
> 
> George,
>  
> Two things jump out together:
> 
> * The SQL statement with hash value = 3509998681 is consuming about
> 25% of the total response-time (i.e. total processing plus total wait) on
> the system.  This SQL statement is executing 900 times during the one-hour
> sample period...
> * Waits on the "cache buffers chains" are consuming another 16% of
> total response-time
> 
> With these two things consuming 41% of everything consumed by the database
> instance during this time period, there is no chance that anything else is
> more important...
>  
> Chances are excellent that these two things are related.  Since the SQL
> statement has over 329m buffer gets and about 0.5m buffer cache-misses
> (i.e. physical reads) to it's credit, this indicates a buffer-cache
> hit-ratio of over 99.7%, which is sure proof that something is seriously
> wrong!  :-)  My guess is that the query is using an inappropriate and/or
> inefficient index for a long, long, long range-scan operation, which is
> racking up all of those buffer gets.  What do you expect from the
> rule-based optimizer?  If you were running CBO and this happened, I'd
> suggest gathering column-level "histogram" statistics on the table.  My
> guess also is that many concurrent users are running this statement during
> the course of the sample period, causing the latch contention for cache
> buffers in the Buffer Cache, thus the relationship between the two
> symptoms?
>  
> I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48;  don't know
> what CPU_COUNT is, but obviously this change has had zero impact on the
> latch contention problem.  Tuning the SQL will fix the problem;
> accomodating the problem by configuring more latches has no impact.
>  
> Tuning that one SQL statement (plus a few of it's look-alikes, also listed
> in the report) will resolve the major performance issues you are
> experiencing.  In fact, it will have a miraculous impact...
>  
> Hope this helps...
>  
> -Tim
>  
> - Original Message - 
> From: "Leonard, George" < [EMAIL PROTECTED]>
> To: "Multiple recipients of list ORACLE-L" < [EMAIL PROTECTED]>
> Sent: Friday, October 18, 2002 2:53 AM
> Subject: Please help, comment required urgently
> 
> 
> > Hi guys, I need a second opinion on the following Statspack output, I
> got my
> > suspicions but my manager and the client is not buying what I am say,
> > 
> > Not knowing anything of the system architecture please look at the
> output
> > and say what would concern you. What assumptions/recommendations you
> would
> > make.
> > 
> > Thx
> > 
> > 
> > 
> > George
> > 
> > George Leonard
> > Oracle Database Administrator
> > Dimension Data (Pty) Ltd
> > (Reg. No. 1987/006597/07)
> > Tel: (+27 11) 575 0573
> > Fax: (+27 11) 576 0573
> > E-mail:[EMAIL PROTECTED]
> > Web:   http://www.didata.co.za
> >  
> > You Have The Obligation to Inform One Honestly of the risk, And As a
> Person
> > You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> > Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
> Kill
> > or Injure Themselves as They See Fit!
> > 
> > 
> > 
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Baker, Barbara
  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: Please help, comment required urgently

[Jared . Still]

I just ran the statspack output through OraPerf as well.

This is truly an amazing resource.

Jared





"Tim Gorman" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 10/18/2002 09:19 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
    Subject:    Re: Please help, comment required urgently


To give credit where credit is due...

I've never bothered to learn how to interpret a STATSPACK report;  I've 
left
that with what little I ever knew about interpreting a BSTAT/ESTAT report.
Rather, I just send the report to the YAPP report post-processor at
www.oraperf.com and it formats everything in such a way that things just
jump out at you.  It is quite instructive to de-construct the resulting 
YAPP
report back to the original STATSPACK and BSTAT/ESTAT report (and thence
further back to the originating V$ views).

Big-time kudos to Anjo and friends for creating and maintaining this
invaluable resource!!!

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, October 18, 2002 8:34 AM


> Thanks for your contributions on this Tim (and on everything else you
> respond to).
> These little tips of how to analyze statspack reports properly all add 
up
> and whilst I did look at the report and I did glean some of you what
> suggested I certainly did not pick up all that you spotted
>
> John
>
>  -Original Message-
> Sent: 18 October 2002 14:54
> To: Multiple recipients of list ORACLE-L
>
>
>
> George,
>
> Two things jump out together:
>
> * The SQL statement with hash value = 3509998681 is consuming about
> 25% of the total response-time (i.e. total processing plus total wait) 
on
> the system.  This SQL statement is executing 900 times during the 
one-hour
> sample period...
>
> * Waits on the "cache buffers chains" are consuming another 16% of
> total response-time
>
> With these two things consuming 41% of everything consumed by the 
database
> instance during this time period, there is no chance that anything else 
is
> more important...
>
> Chances are excellent that these two things are related.  Since the SQL
> statement has over 329m buffer gets and about 0.5m buffer cache-misses
(i.e.
> physical reads) to it's credit, this indicates a buffer-cache hit-ratio 
of
> over 99.7%, which is sure proof that something is seriously wrong!  :-)
My
> guess is that the query is using an inappropriate and/or inefficient 
index
> for a long, long, long range-scan operation, which is racking up all of
> those buffer gets.  What do you expect from the rule-based optimizer? If
> you were running CBO and this happened, I'd suggest gathering 
column-level
> "histogram" statistics on the table.  My guess also is that many
concurrent
> users are running this statement during the course of the sample period,
> causing the latch contention for cache buffers in the Buffer Cache, thus
the
> relationship between the two symptoms?
>
> I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48;  don't 
know
> what CPU_COUNT is, but obviously this change has had zero impact on the
> latch contention problem.  Tuning the SQL will fix the problem;
> accomodating the problem by configuring more latches has no impact.
>
> Tuning that one SQL statement (plus a few of it's look-alikes, also 
listed
> in the report) will resolve the major performance issues you are
> experiencing.  In fact, it will have a miraculous impact...
>
> Hope this helps...
>
> -Tim
>
> - Original Message -
> [EMAIL PROTECTED]>
> To: "Multiple recipients of list ORACLE-L" <
<mailto:ORACLE-L@;fatcity.com>
> [EMAIL PROTECTED]>
> Sent: Friday, October 18, 2002 2:53 AM
>
>
> > Hi guys, I need a second opinion on the following Statspack output, I
got
> my
> > suspicions but my manager and the client is not buying what I am say,
> >
> > Not knowing anything of the system architecture please look at the
output
> > and say what would concern you. What assumptions/recommendations you
would
> > make.
> >
> > Thx
> >
> >
> >
> > George
> > 
> > George Leonard
> > Oracle Database Administrator
> > Dimension Data (Pty) Ltd
> > (Reg. No. 1987/006597/07)
> > Tel: (+27 11) 575 0573
> > Fax: (+27 11) 576 0573
> > E-mail:[EMAIL PROTECTED]
> > Web:<http://www.didata.co.za> http://www.didata.co.za
> >
> > You Have The Obligation to Inform One Honestly of the risk, And As a
> Person
> > You Are

Re: Please help, comment required urgently

[Jared . Still]

What would concern me is the 99.x% buffer cache hit ratio
along with an apparently huge number of LIO's per second.

This database is spinning its wheels, due to poor SQL
and/or poorly written procedural code.

That's my 30 second analysis.  :)

Is this a commercial or custom app?

Jared






"Leonard, George" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
 10/18/2002 01:53 AM
 Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
    cc: 
    Subject:Please help, comment required urgently


Hi guys, I need a second opinion on the following Statspack output, I got 
my
suspicions but my manager and the client is not buying what I am say,

Not knowing anything of the system architecture please look at the output
and say what would concern you. What assumptions/recommendations you would
make.

Thx



George

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:[EMAIL PROTECTED]
Web:   http://www.didata.co.za
 
You Have The Obligation to Inform One Honestly of the risk, And As a 
Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk, Every Fool Has the Right to 
Kill
or Injure Themselves as They See Fit!







7-8.lst
Description: Binary data

Re: Please help, comment required urgently

[Tim Gorman]

To give credit where credit is due...

I've never bothered to learn how to interpret a STATSPACK report;  I've left
that with what little I ever knew about interpreting a BSTAT/ESTAT report.
Rather, I just send the report to the YAPP report post-processor at
www.oraperf.com and it formats everything in such a way that things just
jump out at you.  It is quite instructive to de-construct the resulting YAPP
report back to the original STATSPACK and BSTAT/ESTAT report (and thence
further back to the originating V$ views).

Big-time kudos to Anjo and friends for creating and maintaining this
invaluable resource!!!

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, October 18, 2002 8:34 AM


> Thanks for your contributions on this Tim (and on everything else you
> respond to).
> These little tips of how to analyze statspack reports properly all add up
> and whilst I did look at the report and I did glean some of you what
> suggested I certainly did not pick up all that you spotted
>
> John
>
>  -Original Message-
> Sent: 18 October 2002 14:54
> To: Multiple recipients of list ORACLE-L
>
>
>
> George,
>
> Two things jump out together:
>
> * The SQL statement with hash value = 3509998681 is consuming about
> 25% of the total response-time (i.e. total processing plus total wait) on
> the system.  This SQL statement is executing 900 times during the one-hour
> sample period...
>
> * Waits on the "cache buffers chains" are consuming another 16% of
> total response-time
>
> With these two things consuming 41% of everything consumed by the database
> instance during this time period, there is no chance that anything else is
> more important...
>
> Chances are excellent that these two things are related.  Since the SQL
> statement has over 329m buffer gets and about 0.5m buffer cache-misses
(i.e.
> physical reads) to it's credit, this indicates a buffer-cache hit-ratio of
> over 99.7%, which is sure proof that something is seriously wrong!  :-)
My
> guess is that the query is using an inappropriate and/or inefficient index
> for a long, long, long range-scan operation, which is racking up all of
> those buffer gets.  What do you expect from the rule-based optimizer?  If
> you were running CBO and this happened, I'd suggest gathering column-level
> "histogram" statistics on the table.  My guess also is that many
concurrent
> users are running this statement during the course of the sample period,
> causing the latch contention for cache buffers in the Buffer Cache, thus
the
> relationship between the two symptoms?
>
> I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48;  don't know
> what CPU_COUNT is, but obviously this change has had zero impact on the
> latch contention problem.  Tuning the SQL will fix the problem;
> accomodating the problem by configuring more latches has no impact.
>
> Tuning that one SQL statement (plus a few of it's look-alikes, also listed
> in the report) will resolve the major performance issues you are
> experiencing.  In fact, it will have a miraculous impact...
>
> Hope this helps...
>
> -Tim
>
> - Original Message -
> [EMAIL PROTECTED]>
> To: "Multiple recipients of list ORACLE-L" <

> [EMAIL PROTECTED]>
> Sent: Friday, October 18, 2002 2:53 AM
>
>
> > Hi guys, I need a second opinion on the following Statspack output, I
got
> my
> > suspicions but my manager and the client is not buying what I am say,
> >
> > Not knowing anything of the system architecture please look at the
output
> > and say what would concern you. What assumptions/recommendations you
would
> > make.
> >
> > Thx
> >
> >
> >
> > George
> > 
> > George Leonard
> > Oracle Database Administrator
> > Dimension Data (Pty) Ltd
> > (Reg. No. 1987/006597/07)
> > Tel: (+27 11) 575 0573
> > Fax: (+27 11) 576 0573
> > E-mail:[EMAIL PROTECTED]
> > Web: http://www.didata.co.za
> >
> > You Have The Obligation to Inform One Honestly of the risk, And As a
> Person
> > You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> > Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
> Kill
> > or Injure Themselves as They See Fit!
> >
> >
> >
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> 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 offic

RE: Please help, comment required urgently

[John . Hallas]

Thanks for your contributions on this Tim (and on everything else you
respond to).
These little tips of how to analyze statspack reports properly all add up
and whilst I did look at the report and I did glean some of you what
suggested I certainly did not pick up all that you spotted
 
John
 
 -Original Message-
Sent: 18 October 2002 14:54
To: Multiple recipients of list ORACLE-L



George,
 
Two things jump out together:

*   The SQL statement with hash value = 3509998681 is consuming about
25% of the total response-time (i.e. total processing plus total wait) on
the system.  This SQL statement is executing 900 times during the one-hour
sample period... 

*   Waits on the "cache buffers chains" are consuming another 16% of
total response-time

With these two things consuming 41% of everything consumed by the database
instance during this time period, there is no chance that anything else is
more important...
 
Chances are excellent that these two things are related.  Since the SQL
statement has over 329m buffer gets and about 0.5m buffer cache-misses (i.e.
physical reads) to it's credit, this indicates a buffer-cache hit-ratio of
over 99.7%, which is sure proof that something is seriously wrong!  :-)  My
guess is that the query is using an inappropriate and/or inefficient index
for a long, long, long range-scan operation, which is racking up all of
those buffer gets.  What do you expect from the rule-based optimizer?  If
you were running CBO and this happened, I'd suggest gathering column-level
"histogram" statistics on the table.  My guess also is that many concurrent
users are running this statement during the course of the sample period,
causing the latch contention for cache buffers in the Buffer Cache, thus the
relationship between the two symptoms?
 
I can see that DB_CACHE_LRU_LATCHES has been pushed up to 48;  don't know
what CPU_COUNT is, but obviously this change has had zero impact on the
latch contention problem.  Tuning the SQL will fix the problem;
accomodating the problem by configuring more latches has no impact.
 
Tuning that one SQL statement (plus a few of it's look-alikes, also listed
in the report) will resolve the major performance issues you are
experiencing.  In fact, it will have a miraculous impact...
 
Hope this helps...
 
-Tim
 
- Original Message - 
[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L" <  
[EMAIL PROTECTED]>
Sent: Friday, October 18, 2002 2:53 AM


> Hi guys, I need a second opinion on the following Statspack output, I got
my
> suspicions but my manager and the client is not buying what I am say,
> 
> Not knowing anything of the system architecture please look at the output
> and say what would concern you. What assumptions/recommendations you would
> make.
> 
> Thx
> 
> 
> 
> George
> 
> George Leonard
> Oracle Database Administrator
> Dimension Data (Pty) Ltd
> (Reg. No. 1987/006597/07)
> Tel: (+27 11) 575 0573
> Fax: (+27 11) 576 0573
> E-mail:[EMAIL PROTECTED]
> Web: http://www.didata.co.za
>  
> You Have The Obligation to Inform One Honestly of the risk, And As a
Person
> You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
Kill
> or Injure Themselves as They See Fit!
> 
> 
> 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: Please help, comment required urgently

[Tim Gorman]

George,
 
Two things jump out together:

  The SQL statement with hash value = 3509998681 is 
  consuming about 25% of the total response-time (i.e. 
  total processing plus total wait) on the system.  This SQL statement is 
  executing 900 times during the one-hour sample period...
  Waits on the "cache buffers chains" are consuming 
  another 16% of total response-time
With these two things consuming 
41% of everything consumed by the database instance 
during this time period, there is no chance that anything else is more 
important...
 
Chances are excellent that these two things are 
related.  Since the SQL statement has over 329m buffer gets and about 0.5m 
buffer cache-misses (i.e. physical reads) to it's credit, this indicates a 
buffer-cache hit-ratio of over 99.7%, which is sure proof that something is 
seriously wrong!  :-)  My guess is that the query is using an 
inappropriate and/or inefficient index for a long, long, long range-scan 
operation, which is racking up all of those buffer gets.  What do you 
expect from the rule-based optimizer?  If you were running CBO and this 
happened, I'd suggest gathering column-level "histogram" statistics on the 
table.  My guess also is that many concurrent users are running this 
statement during the course of the sample period, causing the latch contention 
for cache buffers in the Buffer Cache, thus the relationship between the two 
symptoms?
 
I can see that DB_CACHE_LRU_LATCHES has been pushed 
up to 48;  don't know what CPU_COUNT is, but obviously this change has had 
zero impact on the latch contention problem.  Tuning the SQL will fix the 
problem;  accomodating the problem by configuring more latches has no 
impact.
 

Tuning that one SQL statement (plus a few of it's 
look-alikes, also listed in the report) will resolve the major performance 
issues you are experiencing.  In fact, it will have a miraculous 
impact...
 
Hope this helps...
 
-Tim
 
- Original Message - 
From: "Leonard, George" <[EMAIL PROTECTED]>
To: "Multiple recipients of list ORACLE-L" 
<[EMAIL PROTECTED]>
Sent: Friday, October 18, 2002 2:53 AM
Subject: Please help, comment required 
urgently
> Hi guys, I need a second opinion on the following Statspack output, 
I got my> suspicions but my manager and the client is not buying what I 
am say,> > Not knowing anything of the system architecture please 
look at the output> and say what would concern you. What 
assumptions/recommendations you would> make.> > Thx> 
> > > George> 
> George Leonard> 
Oracle Database Administrator> Dimension Data (Pty) Ltd> (Reg. No. 
1987/006597/07)> Tel: (+27 11) 575 0573> Fax: (+27 11) 576 
0573> E-mail:[EMAIL PROTECTED]> Web:   
http://www.didata.co.za> 
 > You Have The Obligation to Inform One Honestly of the risk, And 
As a Person> You Are Committed to Educate Yourself to the Total Risk In 
Any Activity!> Once Informed & Totally Aware of the Risk, Every Fool 
Has the Right to Kill> or Injure Themselves as They See Fit!> 
> > 

Re: Please help, comment required urgently

[Yechiel Adar]

Hello George

I think that you need to tune the first statement the do the most gets and
the most i/o.
The same statement also access the most rows.
It is fired up about every 5 seconds and is very resource consuming.

I am not a tuning expert but this is my 0.01$ worth.

Yechiel Adar
Mehish
- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Friday, October 18, 2002 10:53 AM


> Hi guys, I need a second opinion on the following Statspack output, I got
my
> suspicions but my manager and the client is not buying what I am say,
>
> Not knowing anything of the system architecture please look at the output
> and say what would concern you. What assumptions/recommendations you would
> make.
>
> Thx
>
>
>
> George
> 
> George Leonard
> Oracle Database Administrator
> Dimension Data (Pty) Ltd
> (Reg. No. 1987/006597/07)
> Tel: (+27 11) 575 0573
> Fax: (+27 11) 576 0573
> E-mail:[EMAIL PROTECTED]
> Web:   http://www.didata.co.za
>
> You Have The Obligation to Inform One Honestly of the risk, And As a
Person
> You Are Committed to Educate Yourself to the Total Risk In Any Activity!
> Once Informed & Totally Aware of the Risk, Every Fool Has the Right to
Kill
> or Injure Themselves as They See Fit!
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Yechiel Adar
  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: SNP uses 60% of CPU on Windows2000 - please help

[Inka Bezdziecka]

Thank you for all the help.

I agree that something is wrong and I will keep looking for it when new fires, with 
higher priorities, are put out. 

This morning another problem  occurred (luckily!!!) on that server, and the server was 
re-booted. So far CPU usage oscillates between 1% and 50%, and it is not oracle.exe, 
which utilizes it.

Once more thank you all.

inka
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Inka Bezdziecka
  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: SNP uses 60% of CPU on Windows2000 - please help

[JOSHY MON M C]

Hi All,

I ve a stored proceure and with the following cursor defined. And after
running the procedure, i see that No of open cursors  incresing. I checked
it querying v$open_cursor like this. ( SELECT sql_text from v$open_cursor). 

CURSOR all_rules (l_ticket_type_id NUMBER) IS
SELECT rule_id , workflow_id FROM rule  
  WHERE ticket_type_id =  l_ticket_type_id 
  AND ews_flag=1
  AND active_flag=1;

-- Cusror is processed as below..
FOR term_rec IN all_terms( p_rule_id ) LOOP
-- do processing
END LOOP;

Since I m using CURSOR for stemennet its closed automaticaly. right?. But it
doentnt seem closing as mentioned above ( when querried v$open_cursor). pls
reply

PS:

Also in the stored procedure I use some SELECT statements, that too is shown
when I query open_cursor as above. Do you know why no of open cursors
increasing?

Thanks in advance
Joshy
 



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: JOSHY MON M C
  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: SNP uses 60% of CPU on Windows2000 - please help

[Thomas Day]

If the end users are not complaining about response time then ORACLE using
99% of the CPU is not an issue.  However, I can't help but think that 99%
usage indicates some problem, even if it's not having a critical impact.

I'm supporting a Win2K machine that has 7 instances on it.  We're using
1160M of 1310M of RAM.  We have 2 CPUs and CPU usage is normally at 2%.  It
currently looks as if we have 96 users logged in to one or the other of the
Oracle instances.  We have the extra CPU in order to keep the CPU usage
from pinning at 100% for extended periods of time.  If it did become pinned
for more than a few minutes at a time the users would call about the poor
response time and I'd be worried that something was wrong.

But environments and business needs vary.  We have a few high-rafter bats
among our users who want their queries answered NOW.  They are willing to
spend the extra bucks to make that happen.



   

"Inka  

Bezdziecka"  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
Subject: RE: SNP uses 60% of CPU on 
Windows2000 - please help 
Sent by: root  

   

   

09/17/2002 

03:35 PM   

Please 

respond to 

ORACLE-L   

   

   





That was the starting point of entire problem. An IIS administrator came to
me complaining that Oracle.exe uses 99% of CPU. Indeed, it does.

What is actually wrong with oracle.exe using 99% of CPU? Every other
process shows 00 under CPU and small numbers under time. I am getting to
think that I was sent on a wild goose chase.

That web site is up and response time is not worse that it has always been.
The server is very slow in processing o/s management requests (starting
performance monitor or sql*plus, or something of a sort) - well ...

Thank you anyway.
inka


-Original Message-
Sent: Tuesday, September 17, 2002 2:33 PM
To: Multiple recipients of list ORACLE-L



Go to your Task Manager again and pick the "processes" tab.  Click on 'CPU'
to sort the processes by CPU usage or 'CPU Time" to get who has run up the
most CPU usage since you last booted the system.  All of your Oracle treads
run in one process, ORACLE.EXE.  The dllhost.exe should be there as a
separate process.  Though from what you show in your query I could be
wrong.




"Inka
Bezdziecka"  To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
    Subject: RE: SNP uses 60% of
CPU on Windows2000 - please help
Sent by: root


09/17/2002
01:28 PM
Please
respond to
ORACLE-L






Thanks,
my problem is that I hardly know how to spell Windows. Performance monitor
shows Paging file usage at zero.

On Oracle side I altered system and set job_queue _process = 1.

when I run
select vb.name NOME, vp.program PROCESSNAME,
vp.spid THREADID, vs.sid SID, vs.program
from v$session vs, v$process vp, v$bgprocess vb
where vb.paddr <> '00' and
vb.paddr = vp.addr and
vp.addr = vs.paddr;

I got an output:
NOME PROCESSNAMETHREADID  SID  PROGRAM
PMON ORACLE.EXE 1580 1ORACLE.EXE
DBW0 ORACLE.EXE 1404 2ORACLE.EXE
LGWR ORACLE.EXE 1584 3ORACLE.EXE
CKPT ORACLE.EXE 1588 4ORACLE.EXE
SMON ORACLE.EXE 1592 5ORACLE.EXE
RECO ORACLE.EXE 1596 6ORACLE.EXE
SNP0 ORACLE.EXE 1600 7
SNP2 ORACLE.EXE 2624 35   dllhost.exe
SNP3 ORACLE.EXE 2532 8dllhost.exe
ARC0 ORACLE.EXE 1636 11   ORACLE.EX

RE: SNP uses 60% of CPU on Windows2000 - please help

[Jeffrey Beckstrom]

Could you have a "lost" thread.  We have experienced, on multiple 
servers, running 8.1.7.3.2.  We put a patch on Oracle Applications 11i and 
only during the very large patches, CPU goes to 100% and stays there.  If 
look at the threads, the one using cpu is not known to Oracle.  Have had a 
tar open for a while on this.  Oracle believes it is a logon request 
getting stuck.  weird part is that the Oracle Apps patching program does 
not see an error and continues on.  
 
If we stop the database, cpu stays at 100% until we stop the service.
 
 
 
Jeffrey BeckstromDatabase AdministratorGreater Cleveland Regional 
Transit Authority1240 W. 6th StreetCleveland, Ohio 44113(216) 
781-4204>>> [EMAIL PROTECTED] 9/17/02 3:35:05 PM 
>>>That was the starting point of entire problem. An IIS 
administrator came to me complaining that Oracle.exe uses 99% of CPU. Indeed, it 
does.What is actually wrong with oracle.exe using 99% of CPU? Every 
other process shows 00 under CPU and small numbers under time. I am getting to 
think that I was sent on a wild goose chase.That web site is up and 
response time is not worse that it has always been. The server is very slow in 
processing o/s management requests (starting performance monitor or sql*plus, or 
something of a sort) - well ... Thank you anyway. 
inka-Original Message-Sent: Tuesday, September 17, 
2002 2:33 PMTo: Multiple recipients of list ORACLE-LGo to 
your Task Manager again and pick the "processes" tab.  Click on 'CPU'to 
sort the processes by CPU usage or 'CPU Time" to get who has run up themost 
CPU usage since you last booted the system.  All of your Oracle 
treadsrun in one process, ORACLE.EXE.  The dllhost.exe should be there 
as aseparate process.  Though from what you show in your query I could 
bewrong.   
    
"Inka  
    
Bezdziecka"  
To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
    
    
@cupe.ca>    
Subject: RE: SNP uses 60% of CPU on Windows2000 - please 
help 
    
Sent by: 
root  
   
   
    
09/17/2002 
    
01:28 
PM   
    
Please 
    
respond 
to 
    
ORACLE-L   
   
   
Thanks,my problem is that I hardly know how to spell 
Windows. Performance monitorshows Paging file usage at zero.On 
Oracle side I altered system and set job_queue _process = 1.when I 
runselect vb.name NOME, vp.program PROCESSNAME,vp.spid THREADID, vs.sid 
SID, vs.programfrom v$session vs, v$process vp, v$bgprocess vbwhere 
vb.paddr <> '00' andvb.paddr = vp.addr andvp.addr = 
vs.paddr;I got an output:NOME PROCESSNAME    
THREADID  SID  PROGRAMPMON ORACLE.EXE 1580 
1    ORACLE.EXEDBW0 ORACLE.EXE 1404 
2    ORACLE.EXELGWR ORACLE.EXE 1584 
3    ORACLE.EXECKPT ORACLE.EXE 1588 
4    ORACLE.EXESMON ORACLE.EXE 1592 
5    ORACLE.EXERECO ORACLE.EXE 1596 
6    ORACLE.EXESNP0 ORACLE.EXE 1600 
7SNP2 ORACLE.EXE 2624 35   
dllhost.exeSNP3 ORACLE.EXE 2532 8    
dllhost.exeARC0 ORACLE.EXE 1636 11   
ORACLE.EXESNP4 ORACLE.EXE 1784 18   
dllhost.exeSNP5 ORACLE.EXE 644  23   
dllhost.exeAs far as I know, dllhost.exe is ASP execution engine. So the 
culprit maybe on the application side, not Oracle.Does that make any 
sense?inka-Original Message-Sent: Tuesday, 
September 17, 2002 10:49 AMTo: Multiple recipients of list 
ORACLE-LI have had times on Win NT boxes when the CPU went to 
100%.  My experienceis that this happens when the OS is thrashing the 
swapfile (writing it intoand out of memory on a  continuous 
basis).   If the disk I/O is also veryhigh then that may be what's 
happeni

RE: SNP uses 60% of CPU on Windows2000 - please help

[Inka Bezdziecka]

That was the starting point of entire problem. An IIS administrator came to me 
complaining that Oracle.exe uses 99% of CPU. Indeed, it does.

What is actually wrong with oracle.exe using 99% of CPU? Every other process shows 00 
under CPU and small numbers under time. I am getting to think that I was sent on a 
wild goose chase.

That web site is up and response time is not worse that it has always been. The server 
is very slow in processing o/s management requests (starting performance monitor or 
sql*plus, or something of a sort) - well ... 

Thank you anyway. 
inka


-Original Message-
Sent: Tuesday, September 17, 2002 2:33 PM
To: Multiple recipients of list ORACLE-L



Go to your Task Manager again and pick the "processes" tab.  Click on 'CPU'
to sort the processes by CPU usage or 'CPU Time" to get who has run up the
most CPU usage since you last booted the system.  All of your Oracle treads
run in one process, ORACLE.EXE.  The dllhost.exe should be there as a
separate process.  Though from what you show in your query I could be
wrong.



   

"Inka  

Bezdziecka"  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
Subject: RE: SNP uses 60% of CPU on 
Windows2000 - please help 
Sent by: root  

   

   

09/17/2002 

01:28 PM   

Please 

respond to 

ORACLE-L   

   

   





Thanks,
my problem is that I hardly know how to spell Windows. Performance monitor
shows Paging file usage at zero.

On Oracle side I altered system and set job_queue _process = 1.

when I run
select vb.name NOME, vp.program PROCESSNAME,
vp.spid THREADID, vs.sid SID, vs.program
from v$session vs, v$process vp, v$bgprocess vb
where vb.paddr <> '00' and
vb.paddr = vp.addr and
vp.addr = vs.paddr;

I got an output:
NOME PROCESSNAMETHREADID  SID  PROGRAM
PMON ORACLE.EXE 1580 1ORACLE.EXE
DBW0 ORACLE.EXE 1404 2ORACLE.EXE
LGWR ORACLE.EXE 1584 3ORACLE.EXE
CKPT ORACLE.EXE 1588 4ORACLE.EXE
SMON ORACLE.EXE 1592 5ORACLE.EXE
RECO ORACLE.EXE 1596 6ORACLE.EXE
SNP0 ORACLE.EXE 1600 7
SNP2 ORACLE.EXE 2624 35   dllhost.exe
SNP3 ORACLE.EXE 2532 8dllhost.exe
ARC0 ORACLE.EXE 1636 11   ORACLE.EXE
SNP4 ORACLE.EXE 1784 18   dllhost.exe
SNP5 ORACLE.EXE 644  23   dllhost.exe

As far as I know, dllhost.exe is ASP execution engine. So the culprit may
be on the application side, not Oracle.

Does that make any sense?

inka



-Original Message-
Sent: Tuesday, September 17, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L



I have had times on Win NT boxes when the CPU went to 100%.  My experience
is that this happens when the OS is thrashing the swapfile (writing it into
and out of memory on a  continuous basis).   If the disk I/O is also very
high then that may be what's happening.  However, 800M free out of 2G does
not look too bad.  If you don't have the high disk I/O then the SGA is
probably not the issue.  I don't want to push you in the wrong direction
either.

HTH




"Inka
Bezdziecka"  To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: SNP uses 60% of
CPU on Windows2000 - please help
Sent by: root


09/16/2002
03:38 PM
Please
respond to
ORACLE-L






The server has 2GB, free at the moment 800MB. Do you think that SGA size
may have something to do with it?

-O

RE: SNP uses 60% of CPU on Windows2000 - please help

[Thomas Day]

Go to your Task Manager again and pick the "processes" tab.  Click on 'CPU'
to sort the processes by CPU usage or 'CPU Time" to get who has run up the
most CPU usage since you last booted the system.  All of your Oracle treads
run in one process, ORACLE.EXE.  The dllhost.exe should be there as a
separate process.  Though from what you show in your query I could be
wrong.



   

"Inka  

Bezdziecka"  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
Subject: RE: SNP uses 60% of CPU on 
Windows2000 - please help 
Sent by: root  

   

   

09/17/2002 

01:28 PM   

Please 

respond to 

ORACLE-L   

   

   





Thanks,
my problem is that I hardly know how to spell Windows. Performance monitor
shows Paging file usage at zero.

On Oracle side I altered system and set job_queue _process = 1.

when I run
select vb.name NOME, vp.program PROCESSNAME,
vp.spid THREADID, vs.sid SID, vs.program
from v$session vs, v$process vp, v$bgprocess vb
where vb.paddr <> '00' and
vb.paddr = vp.addr and
vp.addr = vs.paddr;

I got an output:
NOME PROCESSNAMETHREADID  SID  PROGRAM
PMON ORACLE.EXE 1580 1ORACLE.EXE
DBW0 ORACLE.EXE 1404 2ORACLE.EXE
LGWR ORACLE.EXE 1584 3ORACLE.EXE
CKPT ORACLE.EXE 1588 4ORACLE.EXE
SMON ORACLE.EXE 1592 5ORACLE.EXE
RECO ORACLE.EXE 1596 6ORACLE.EXE
SNP0 ORACLE.EXE 1600 7
SNP2 ORACLE.EXE 2624 35   dllhost.exe
SNP3 ORACLE.EXE 2532 8dllhost.exe
ARC0 ORACLE.EXE 1636 11   ORACLE.EXE
SNP4 ORACLE.EXE 1784 18   dllhost.exe
SNP5 ORACLE.EXE 644  23   dllhost.exe

As far as I know, dllhost.exe is ASP execution engine. So the culprit may
be on the application side, not Oracle.

Does that make any sense?

inka



-Original Message-
Sent: Tuesday, September 17, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L



I have had times on Win NT boxes when the CPU went to 100%.  My experience
is that this happens when the OS is thrashing the swapfile (writing it into
and out of memory on a  continuous basis).   If the disk I/O is also very
high then that may be what's happening.  However, 800M free out of 2G does
not look too bad.  If you don't have the high disk I/O then the SGA is
probably not the issue.  I don't want to push you in the wrong direction
either.

HTH




"Inka
Bezdziecka"  To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
Subject: RE: SNP uses 60% of
CPU on Windows2000 - please help
Sent by: root


09/16/2002
03:38 PM
Please
respond to
ORACLE-L






The server has 2GB, free at the moment 800MB. Do you think that SGA size
may have something to do with it?

-Original Message-
Sent: Monday, September 16, 2002 2:58 PM
To: Multiple recipients of list ORACLE-L



How much physical RAM do you have and how much are you using?  Check the
Task Manager on the "performance" tab under "MEM Usage" to get that figure.




"Inka
Bezdziecka"  To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
Subject: SNP uses 60% of CPU
on Windows2000 - please help
Sent by: root


09/16/2002
01:13 PM
Please
respond to
   

RE: SNP uses 60% of CPU on Windows2000 - please help

[Inka Bezdziecka]

Thanks,
my problem is that I hardly know how to spell Windows. Performance monitor shows 
Paging file usage at zero. 

On Oracle side I altered system and set job_queue _process = 1.

when I run 
select vb.name NOME, vp.program PROCESSNAME, 
vp.spid THREADID, vs.sid SID, vs.program
from v$session vs, v$process vp, v$bgprocess vb 
where vb.paddr <> '00' and 
vb.paddr = vp.addr and 
vp.addr = vs.paddr;

I got an output:
NOMEPROCESSNAME THREADIDSID PROGRAM
PMONORACLE.EXE  15801   ORACLE.EXE
DBW0ORACLE.EXE  14042   ORACLE.EXE
LGWRORACLE.EXE  15843   ORACLE.EXE
CKPTORACLE.EXE  15884   ORACLE.EXE
SMONORACLE.EXE  15925   ORACLE.EXE
RECOORACLE.EXE  15966   ORACLE.EXE
SNP0ORACLE.EXE  16007   
SNP2ORACLE.EXE  262435  dllhost.exe
SNP3ORACLE.EXE  25328   dllhost.exe
ARC0ORACLE.EXE  163611  ORACLE.EXE
SNP4ORACLE.EXE  178418  dllhost.exe
SNP5ORACLE.EXE  644 23  dllhost.exe

As far as I know, dllhost.exe is ASP execution engine. So the culprit may be on the 
application side, not Oracle.

Does that make any sense?

inka



-Original Message-
Sent: Tuesday, September 17, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L



I have had times on Win NT boxes when the CPU went to 100%.  My experience
is that this happens when the OS is thrashing the swapfile (writing it into
and out of memory on a  continuous basis).   If the disk I/O is also very
high then that may be what's happening.  However, 800M free out of 2G does
not look too bad.  If you don't have the high disk I/O then the SGA is
probably not the issue.  I don't want to push you in the wrong direction
either.

HTH



   

"Inka  

Bezdziecka"  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
Subject: RE: SNP uses 60% of CPU on 
Windows2000 - please help 
Sent by: root  

   

   

09/16/2002 

03:38 PM   

Please 

respond to 

ORACLE-L   

   

   





The server has 2GB, free at the moment 800MB. Do you think that SGA size
may have something to do with it?

-Original Message-
Sent: Monday, September 16, 2002 2:58 PM
To: Multiple recipients of list ORACLE-L



How much physical RAM do you have and how much are you using?  Check the
Task Manager on the "performance" tab under "MEM Usage" to get that figure.




"Inka
Bezdziecka"  To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
    Subject: SNP uses 60% of CPU
on Windows2000 - please help
Sent by: root


09/16/2002
01:13 PM
Please
respond to
ORACLE-L






Hello,
I run into a problem, which I cannot solve myself and would greatly
appreciate any help.

Oracle 8.1.7.0.0 utilises 99% of CPU on a Windows2000 server, which also
has to run MS IIS and a few applications.

There are 9 user connections (from MS IISv.5, using Oracle ODBC). There are
no waits, no locks, basic statistics values are very good.

I found the SNP process executing queued jobs using 60% of CPU. There are
no queued  jobs (current, broken or any other) in the neighbourhood, never
mind on that server.
The sql_text shows: BEGIN sys.dbms_ijob.remove(:job); END;

It seems, that the high usage is coincidental with creation of InterMedia
Text indices, which seems to b

RE: SNP uses 60% of CPU on Windows2000 - please help

[Thomas Day]

I have had times on Win NT boxes when the CPU went to 100%.  My experience
is that this happens when the OS is thrashing the swapfile (writing it into
and out of memory on a  continuous basis).   If the disk I/O is also very
high then that may be what's happening.  However, 800M free out of 2G does
not look too bad.  If you don't have the high disk I/O then the SGA is
probably not the issue.  I don't want to push you in the wrong direction
either.

HTH



   

"Inka  

Bezdziecka"  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
Subject: RE: SNP uses 60% of CPU on 
Windows2000 - please help 
Sent by: root  

   

   

09/16/2002 

03:38 PM   

Please 

respond to 

ORACLE-L   

   

   





The server has 2GB, free at the moment 800MB. Do you think that SGA size
may have something to do with it?

-Original Message-
Sent: Monday, September 16, 2002 2:58 PM
To: Multiple recipients of list ORACLE-L



How much physical RAM do you have and how much are you using?  Check the
Task Manager on the "performance" tab under "MEM Usage" to get that figure.




"Inka
Bezdziecka"  To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
        Subject: SNP uses 60% of CPU
on Windows2000 - please help
Sent by: root


09/16/2002
01:13 PM
Please
respond to
ORACLE-L






Hello,
I run into a problem, which I cannot solve myself and would greatly
appreciate any help.

Oracle 8.1.7.0.0 utilises 99% of CPU on a Windows2000 server, which also
has to run MS IIS and a few applications.

There are 9 user connections (from MS IISv.5, using Oracle ODBC). There are
no waits, no locks, basic statistics values are very good.

I found the SNP process executing queued jobs using 60% of CPU. There are
no queued  jobs (current, broken or any other) in the neighbourhood, never
mind on that server.
The sql_text shows: BEGIN sys.dbms_ijob.remove(:job); END;

It seems, that the high usage is coincidental with creation of InterMedia
Text indices, which seems to be at least peculiar.

If anyone cares to push me in the right direction, I shall be eternally
grateful.

inka
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Inka Bezdziecka
  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.com
--
Author: Thomas Day
  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

RE: Cursor : please help

[Ji, Richard]

What type of JDBC driver are u using?  I haven't used 9i yet.
But some 8i OCI drivers caches the cursor even if you close the ResultSet,
Statement object.  Thus you will see it increments.  You will have to
close the Connection object which is not good like your situation since
you are pooling them.

No to doubt your programming ability but you should also check to see
how and where the ResultSet and Statement are closed.  I have gone
through codes that the developer claim that they closed everything, but
if the statement is not at the right place, just having a closed statement
doesn't necessarily means it will get closed.

Richard Ji

-Original Message-
Sent: Tuesday, September 10, 2002 1:14 PM
To: Multiple recipients of list ORACLE-L



Hi All,
I ve a JSP application. Each time I query database ( oracle 9i) from the
page, I see that the no.of cursors increments by one. I m accessing database
using JDBC, I close resultset and statement objects after query. But I m not
closing Connection object since I use a Connection Pool.
Can anyone tell why the no,of cursors increasing each time ? 
Thanks in advance 
Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: JOSHY MON M C
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Ji, Richard
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).

Cursor : please help

[JOSHY MON M C]

Hi All,
I ve a JSP application. Each time I query database ( oracle 9i) from the
page, I see that the no.of cursors increments by one. I m accessing database
using JDBC, I close resultset and statement objects after query. But I m not
closing Connection object since I use a Connection Pool.
Can anyone tell why the no,of cursors increasing each time ? 
Thanks in advance 
Thanks

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: JOSHY MON M C
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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 Help: estimating size for materialized view Question

[Srinivas]

Hi
I am trying to find estimated size for a view using
DBMS_OLAP.ESTIMATE_SUMMARY_SIZE package.
Using the following sql (logged in as SYS). I am new
to materialized views 
and DBMS_OLAP.ESTIMATE_SUMMARY_SIZE usage. 

I am doing this through SQL*Plus logged in as SYS. 
I also set SERVEROUTPUT ON
and VAR num_bytes NUMBER
VAR num_rows NUMBER at SQL prompt in SQL*Plus.

Can someone help me how to find the estimated
size before creating a materialized view for the
following syntax for 
the following.

thanks,
Srinivas



exec DBMS_OLAP.estimate_summary_size('test_est_size',
'SELECT empno, ename, dname, loc FROM scott.emp,
scott.dept where emp.deptno=dept.deptno', 
  :num_rows, :num_bytes)

This is the output I got :

SQL> set SERVEROUTPUT ON
SQL> show serveroutput
serveroutput ON size 2000 format WORD_WRAPPED
SQL> VAR num_rows number
SQL> var num_bytes number
SQL> var
variable   mv_size
datatype   NUMBER

variable   num_bytes
datatype   NUMBER

variable   num_rows
datatype   NUMBER

SQL> exec
DBMS_OLAP.estimate_summary_size('test_est_size', 
'SELECT empno, ename, dname, loc FROM scott.emp,
scott.dept where emp.deptno=dept.deptno',
   :num_rows, :num_bytes)

PL/SQL procedure successfully completed.


SQL> print :num_rows

  NUM_ROWS
--


SQL> print :num_bytes

 NUM_BYTES
--




__
Do You Yahoo!?
Yahoo! Health - Feel better, live better
http://health.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Srinivas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: SQLLOADER - please help

[Joe Testa]

read the documentation, start in the errors manual and the utilities manual.

[EMAIL PROTECTED] wrote:

>Hallo, anyone whom can help me with this:
>
>I am running sqllodaer and gets this errormessage in the log:
>
>Record 18729: Rejected - Error on table RIK2.IMP_NIELSEN, column MONTH_DISTR_W
>Column not found before end of logical record (use TRAILING NULLCOLS)
>ORA-01631: max # extents (20) reached in table RIK2.IMP_NIELSEN
>
>What  does that mean and how can I change this ctl file,
>
>
>
>(See attached file: nielsen.ctl)
>
>Thanks in advance
>
>Roland
>
>
>  
>

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: SQLLOADER - please help

[Long . Nguyen]

The error message indicates that the current maxextents value for table IMP_NIELSEN is 
20 and this number of extents had been reached during the load. Since there was still 
more data to load and Oracle tried to get to the next extent but
could not since it had already reached the max value.

Ask the table owner or DBA to change the maxextents value for the table to a large 
value.

Long

-Original Message-
Sent: Monday, 1 July 2002 6:28 PM
To: Multiple recipients of list ORACLE-L


Hallo, anyone whom can help me with this:

I am running sqllodaer and gets this errormessage in the log:

Record 18729: Rejected - Error on table RIK2.IMP_NIELSEN, column MONTH_DISTR_W
Column not found before end of logical record (use TRAILING NULLCOLS)
ORA-01631: max # extents (20) reached in table RIK2.IMP_NIELSEN

What  does that mean and how can I change this ctl file,



(See attached file: nielsen.ctl)

Thanks in advance

Roland


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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: SQLLOADER - please help

[Alexandre Gorbatchev]

Roland,
Looks like your table run out of space and cannot be extended. The
workaround - pre-create table bigger (that's better) or set max extents
higher.
Strange that Loader complains about input file. It went crazy probably.
You don't need TRAILING NULLCOLS if you have all columns in each record
defined. Loader's suggestion "use TRAILING NULLCOLS" is errorprone and
stupid even when the problem is with row structure.

hth
Alexandre
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Monday, July 01, 2002 10:28 AM


> Hallo, anyone whom can help me with this:
>
> I am running sqllodaer and gets this errormessage in the log:
>
> Record 18729: Rejected - Error on table RIK2.IMP_NIELSEN, column
MONTH_DISTR_W
> Column not found before end of logical record (use TRAILING NULLCOLS)
> ORA-01631: max # extents (20) reached in table RIK2.IMP_NIELSEN
>
> What  does that mean and how can I change this ctl file,
>
>
>
> (See attached file: nielsen.ctl)
>
> Thanks in advance
>
> Roland
>
>
>

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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: SQLLOADER - please help <-- Attachment History Removed

[Jack van Zanen]

Hi,


Not used SQLLOADER much but it seems that your controlfile says there
should be a column named month_distr_w that is not in your data.
How to fix it:
If your sure the column is in the data check your controlfile for other
syntax errors. If the column is not in the data than the answer is given in
the error message (trailing nullcols)


Jack


   

  Roland.Skoldblom@

  ica.se   To:   Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>   
  Sent by: cc:   (bcc: Jack van 
Zanen/nlzanen1/External/MEY/NL)
  [EMAIL PROTECTED] Subject:  SQLLOADER - please help   

   

   

  01-07-2002 10:28 

  Please respond to

  ORACLE-L 

   

   





Hallo, anyone whom can help me with this:

I am running sqllodaer and gets this errormessage in the log:

Record 18729: Rejected - Error on table RIK2.IMP_NIELSEN, column
MONTH_DISTR_W
Column not found before end of logical record (use TRAILING NULLCOLS)
ORA-01631: max # extents (20) reached in table RIK2.IMP_NIELSEN

What  does that mean and how can I change this ctl file,



(See attached file: nielsen.ctl)

Thanks in advance

Roland






===
De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.

Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
en het origineel en eventuele kopieën te verwijderen en te vernietigen.

Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
algemene voorwaarden worden u op verzoek kosteloos toegezonden.
=
The information contained in this communication is confidential and is
intended solely for the use of the individual or entity to whom it is
addressed. You should not copy, disclose or distribute this communication
without the authority of Ernst & Young. Ernst & Young is neither liable for
the proper and complete transmission of the information contained in this
communication nor for any delay in its receipt. Ernst & Young does not
guarantee that the integrity of this communication has been maintained nor
that the communication is free of viruses, interceptions or interference.

If you are not the intended recipient of this communication please return
the communication to the sender and delete and destroy all copies.

In carrying out its engagements, Ernst & Young applies general terms and
conditions, which contain a clause that limits its liability. A copy of
these terms and conditions is available on request free of charge.
===






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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
-

SQLLOADER - please help

[Roland . Skoldblom]

Hallo, anyone whom can help me with this:

I am running sqllodaer and gets this errormessage in the log:

Record 18729: Rejected - Error on table RIK2.IMP_NIELSEN, column MONTH_DISTR_W
Column not found before end of logical record (use TRAILING NULLCOLS)
ORA-01631: max # extents (20) reached in table RIK2.IMP_NIELSEN

What  does that mean and how can I change this ctl file,



(See attached file: nielsen.ctl)

Thanks in advance

Roland





nielsen.ctl
Description: Binary data

RE: Prod problem, please help!!!

[Arun Chakrapani]

I am actually having the same probs now there is a database which is is
getting froze for a few secs nobody can login but we did not know what time
this was occuring.
set a recording thro 9i for every sec later we came to know it was due to
cache buffer chain. almost 90 to 100 people were waiting on latch free wait
issue.
Found that all the users are running the same sql with the same data
requests.
Looks like we have to tune the sql.Yet to analyze the sqls.

-Original Message-
Sent: Wednesday, May 01, 2002 5:42 PM
To: Multiple recipients of list ORACLE-L


> As jared says keep querying the session_Wait u will defnetly bound to 
catch
> the guy who might be causing the database to kneel down.

If the database is hung, the waits will likely show up immediately.  I've 
had 
similar problems with waits on a latch that hung the database.  If a 
session
is hung, the waits should be there the first time you query.

It may also be that you can't even query the v$ views, in which case you
must resort to the state dumps.

Jared





Arun Chakrapani <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
04/30/2002 01:18 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:    RE: Prod problem, please help!!!


If nothing works and when u know the database has frozen u can run the
following command
to take dump of the database so that u can send it to oracle for analyzing
or u can analyze it too.
As jared says keep querying the session_Wait u will defnetly bound to 
catch
the guy who might be causing the database to kneel down.
Are u flushing shared pool quite frequently. check this out(this was one 
of
the cause which was bringing the database down and was fixed in 7.2 or 7.3 
i
  INET: [EMAIL PROTECTED]



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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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.com
-- 
Author: Arun Chakrapani
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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: Prod problem, please help!!!

[Jared . Still]

> As jared says keep querying the session_Wait u will defnetly bound to 
catch
> the guy who might be causing the database to kneel down.

If the database is hung, the waits will likely show up immediately.  I've 
had 
similar problems with waits on a latch that hung the database.  If a 
session
is hung, the waits should be there the first time you query.

It may also be that you can't even query the v$ views, in which case you
must resort to the state dumps.

Jared





Arun Chakrapani <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
04/30/2002 01:18 PM
Please respond to ORACLE-L

 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: 
Subject:    RE: Prod problem, please help!!!


If nothing works and when u know the database has frozen u can run the
following command
to take dump of the database so that u can send it to oracle for analyzing
or u can analyze it too.
As jared says keep querying the session_Wait u will defnetly bound to 
catch
the guy who might be causing the database to kneel down.
Are u flushing shared pool quite frequently. check this out(this was one 
of
the cause which was bringing the database down and was fixed in 7.2 or 7.3 
i
  INET: [EMAIL PROTECTED]



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

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

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).