dbms_stats.gather_schema_stats make not be computing stats

[Gillian]

This is an 8.1.7.0.0 data on Solaris 8.
 
I set monitoring to YES in all 14000+ tables.In the sys.dba_tab_modifications table there are 207 tables. Question: Does this mean that there are 207 tables with STALE staistics? From what I have read, that answer is suppose to be - yes.
When I run the following script nightly, the number of tables (all owned by DBADMIN), in the sys.dba_tab_modifications is not decreasing:
execute dbms_stats.gather_schema_stats ('DBADMIN', NULL, FALSE, 'FOR ALL COLUMNS SIZE 1', NULL, 'DEFAULT', TRUE, NULL, NULL, 'GATHER STALE', 'LIST');
Here is a sample output from the sys.dba_tab_modifications  table:TABLE_NAME TIMESTAMP-- -PS_VENDOR_WTHD_JUR 20-NOV-02PS_VNDR_ADDR_SCROL 20-NOV-02PS_VNDR_BANK_ACCT  20-NOV-02PS_VNDR_CNTCT_SCR  20-NOV-02PS_VNDR_LOC_SCROL  20-NOV-02PS_VNDR_URL    18-NOV-02PS_VOUCHER   !
!
;  20-NOV-02PS_VOUCHER_LINE    20-NOV-02PS_XE_XREF_TMW 19-NOV-02
207 rows selected.
SQL> select last_analyzed from dba_tables where table_name='PS_XE_XREF_TMW';
LAST_ANAL-20-OCT-02
Question:I do not understand why last_analyzed is Oct 20th, when I have the dbms_stats.gather_schema_stats (see above) script running nightly. Montoring is YES is dba_tables.

AAArrgghhh . . . am I missing something? 
Thanks
GillianDo you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now

Re: TEMP segments

[Tim Gorman]

No, only the original SQL*Loader DIRECT=TRUE does that (adding blocks above
HWM), which was introduced with v7.0.x...

Since then, all direct-path (a.k.a. append) operations (including SQL*Loader
DIRECT=TRUE PARALLEL=TRUE, parallel CREATE INDEX, parallel CREATE TABLE AS
SELECT, and INSERT /*+ APPEND PARALLEL */, etc) have created temporary
segments for each parallel execution "slave" process which will be merged
into eventual segment.  As Anjo commented, it makes for really fast and easy
rollback;  just let SMON clean up the temporary segments...

...don't know if it's ever really been documented;  I did include this
description in our books on data warehousing in 1997 and 1999...

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


> Tim -- that adds new blocks above the HWM?
>
> I wonder if any command that adds new blocks (vs inserting rows into
> existing ones) automatically creates the new blocks as TEMP segments.
> Logically it makes sense but I wonder if it's documented anywhere.
>
>
>
> --- Tim Gorman <[EMAIL PROTECTED]> wrote:
> > ...as does INSERT /*+ APPEND PARALLEL */...
> >   - Original Message -
> >   From: Fink, Dan
> >   To: Multiple recipients of list ORACLE-L
> >   Sent: Tuesday, November 19, 2002 1:15 PM
> >   Subject: TEMP segments
> >
> >
> >   I just found a new command that creates TEMP segments. It is well
> > known that index creations first create the index segments as TEMP
> > segments then 'convert' them to index segments upon completion. What
> > I just found out (thanks to a failed operation) is that 'CREATE TABLE
> > AS SELECT' (ctas) also creates the segments as TEMP first.
> >
> >   Dan Fink
> >
>
>
> __
> Do you Yahoo!?
> Yahoo! Web Hosting - Let the expert host your site
> http://webhosting.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
>   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: 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: 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: export in full mode but exclude particular user?

[Deshpande, Kirti]

Joe,
It may be unsupported, but it does work. One must be careful in saving the original 
catexp.sql, and running it afterwards (after the 'tweaked' export is done). I have 
been using this 'tweak' for quite some time. 

Excluding user(s) from export is a fairly easy tweak. 
Oracle already excludes a few of its own schemas from the export. Adding a new 
username in the 'exclude list' is not difficult. Hint: affected internal views are 
exu81obj and exu8usr (in 8.1.7.4). 


- Kirti


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


in the past there was a way described here on the list, TOTALLY 
UNSUPPORTED by tweaking one of the underlying views.

joe


Chris Stephens wrote:

> i'm 99% sure that can't be done.  you'll have to explicitly name all 
> the other users with owner=user_a,user_b,...  ...then you can do an 
> import with full=y  ...or flip/flop by full=y on export and 
> owner=.. on the import.
>
> ...first post to the list.
> gotta start somewhere!
>
>
>
> -Original Message-
> From: Chuan Zhang [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, November 20, 2002 5:44 PM
> To: Multiple recipients of list ORACLE-L
> Subject: export in full mode but exclude particular user?
>
>
> Dear DBA gurus,
>
>   Has anyone got such experience when export database in full mode but 
> exclude a particular user?
>
> TIA,
>
> Chuan
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Chuan Zhang
>   INET: [EMAIL PROTECTED]



--
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: Query tuning with tablename alias

[Deshpande, Kirti]

FWIW... Here is an interesting article by Jonathan Gennick: 
http://www.onlamp.com/lpt/a/2640

- Kirti


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


I am thinking that it must have been, since the previous email correctly
shows that it will not execute.  I do not have access to the actual db and
code yet, just paper copies of the queries that I was given to look over.
Thanks to everyone for their feedback on this.  Obviously this is not an
issue.  

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


Wendy - Could this SQL have been generated by a program? 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

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



Knowing about how a hint works with aliases is what made me wonder about
impacts to other areas.  I have never seen it mentioned in any of the
manuals I have read, and I just thought maybe someone else had experience
with this.  I am trying to figure out why the developer of these queries
took the time to assign alias names to all these table names in their FROM
clause, and then never used them elsewhere in the SELECT or WHERE portions.
Maybe they were copying from a template...  

I realize there is a lot to be gained by moving the remote tables locally
where possible.  We are hoping to use local snapshots instead of going to
remote instances.

Thanks again!

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


Wendy,

I have never heard of alias' providing either a performance gain or
reduction.  One thing I haven't verified though is the impact on hints.  It
has been my observation that if a table has an alias then that alias must
be used in the hint.  For example:

select /*+ index(user) */ *
from ctcs_user u;

Does not use an index, whereas:

select /*+ index(u) */ *
from ctcs_user u;

Does use an index.

If you queries are joining tables across instances, however, then I'd be
looking there for performance issues.  Oracle doesn't join across instances
really - it tends to just temporarily copy one table to a different
instance.  (Someone technical can explain this properly).

Regards,
 Mark.




 

"Hopper, Wendy

S"   To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>   
 Subject: Query tuning with
tablename alias
Sent by:

[EMAIL PROTECTED]

om

 

 

21/11/2002

05:13

Please respond

to ORACLE-L

 

 





Hello list.

I have recently been tasked with trying to optimize some slow performing
queries (Oracle 8.1.7) for an application that generates reports in a data
warehouse type environment.  I have noticed in most of the queries that the
table names have been aliased, but not used elsewhere in the query.

IE.   SELECT  table1.column1,
  table1.column2,
  table2.column1
   FROM table1 A,
  table2 B
   WHERE   table1.key = table2.key;

Are there any advantage/disadvantages to giving these tables aliases and
not using them anywhere else?   I am thinking that if the tables have been
assigned alias names, they should be referred to by alias names, but I
guess I have not seen anything documented on this officially.   These
queries join tables against remote tables in a different instance, if that
makes a difference.  Any insight on this would be great.

Thanks.


Wendy Hopper









<<>>

   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
   by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
  that do not relate to the official business of
 Transurban City Link Ltd
 shall be understood as neither given nor endorsed by it.
<<<>


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

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

Re: opinions on SAN devices for Oracle

[Tim Gorman]

EMC Celera would be a NAS, not a SAN, no?

- Original Message - 
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Wednesday, November 20, 2002 7:49 AM


> We just bought EMC Cellero.
> I do not know the reasons, just that they won the contract.
> 
> Yechiel Adar
> Mehish
> - Original Message - 
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> Sent: Tuesday, November 19, 2002 8:49 PM
> 
> 
> > Hi All,
> > 
> > We are considering the following 3 SAN storage devices.  If anyone can
> > share any info on any I would appreciate it.
> > They all have 2gig fibre channel.
> > 
> > Hitachi 9200
> > EMC CX400
> > HP EVA 2c2d
> > 
> > Thanks
> > Rick
> > 
> > 
> > -- 
> > 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: 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).


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: Dynamic views

[Jay]

Title: Dynamic views



We have a different business case but you might 
want to look into materialized views.
 
 

  - Original Message - 
  From: 
  david 
  hill 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, November 20, 2002 1:48 
  PM
  Subject: Dynamic views
  
  Hi Guys
  I'm trying to see if I can get 
  around paying oracle $50,000 for partitioning
  I have a huge table and I want 
  to partition it on date so I created 12
  tables
  _JAN
  _FEB
  And so on
  But I want to create a view that 
  will be used for inserting so it always
  Points to the current month without having to recreate the 
  view every month.
  Is there anyway anybody can 
  think of doing this?
  Thanks
  David Hill
  DBA
  Le Chateau

Re: export in full mode but exclude particular user?

[Joe Testa]

in the past there was a way described here on the list, TOTALLY 
UNSUPPORTED by tweaking one of the underlying views.

joe


Chris Stephens wrote:

i'm 99% sure that can't be done.  you'll have to explicitly name all 
the other users with owner=user_a,user_b,...  ...then you can do an 
import with full=y  ...or flip/flop by full=y on export and 
owner=.. on the import.

...first post to the list.
gotta start somewhere!



-Original Message-
From: Chuan Zhang [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 20, 2002 5:44 PM
To: Multiple recipients of list ORACLE-L
Subject: export in full mode but exclude particular user?


Dear DBA gurus,

  Has anyone got such experience when export database in full mode but 
exclude a particular user?

TIA,

Chuan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Chuan Zhang
  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: Joe Testa
 INET: [EMAIL PROTECTED]

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

RE: converting to the cost based optimizer

[Binley Lim]

That parameter does not make CBO comes up with any smarter plans - just more likely to 
choose NLJ whether its better or not. HJ can outperform all other joins in a lot of 
cases if you maintain statistics judiciously, including the appropriate use of 
histograms.

>>> [EMAIL PROTECTED] 11/21/02 12:24a.m. >>>
One quick hack is to set the "optimizer_..." params to
make indexes look super-wonderful, which then gives
you a rule-based "look and feel" with hopefully a
little smarter selection of indexes as a bonus

hth
connor

 --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote: > 
> Consider using OUTLINES for Fixed Execution plans
> especially for Batch Queries 
> 
> This will prevent a Sudden Increment in Processing
> Time due to Optimizer choosing NON-Optimal paths
> 
> 
> -Original Message-
> Sent: Wednesday, November 20, 2002 3:20 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> We will be in the same process soon. 
> We'll have to consolidate over 100 instances to
> something between 40-60 instances and switch from
> RBO
> to CBO.
> 
> Any tips are welcome.
> 
> 
>  --- Steve McClure <[EMAIL PROTECTED]> a écrit :
> >
> I am just starting to look at converting to the cost
> > based optimizer, and am
> > hoping a few of you can share insights from having
> > done so in the past.  Our
> > application is an oltp system  developed on 7.3.4.
> 
> > We made liberal use of
> > +0 and other RBO "hints", and I am wondering if
> > these are going to cause us
> > troubles when switching to CBO.
> > 
> >   We have been on 8i since April of this year, and
> I
> > am just now starting to
> > gather information on the inner workings of the
> CBO.
> >  I know it is where I
> > want to be, I am just looking to see how
> > painful/painless the transition
> > will be.
> > 
> > Steve McClure
> > 


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Binley Lim
  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: export in full mode but exclude particular user?

[Chris Stephens]

Title: RE: export in full mode but exclude particular user?





i'm 99% sure that can't be done.  you'll have to explicitly name all the other users with owner=user_a,user_b,...  ...then you can do an import with full=y  ...or flip/flop by full=y on export and owner=.. on the import.

...first post to the list.
        gotta start somewhere!




-Original Message-
From: Chuan Zhang [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 20, 2002 5:44 PM
To: Multiple recipients of list ORACLE-L
Subject: export in full mode but exclude particular user?



Dear DBA gurus,


  Has anyone got such experience when export database in full mode but exclude a particular user? 


TIA,


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

export in full mode but exclude particular user?

[Chuan Zhang]

Dear DBA gurus,

  Has anyone got such experience when export database in full mode but exclude a 
particular user? 

TIA,

Chuan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Chuan Zhang
  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: Oracle on MVS able to submit JCL ??

[Babette Turner-Underwood]

Thanks but EVERYTHING is on the mainframe (including Oracle).
As far as I know there is no such thing as PIPEs and daemon
processes on the mainframe.

I can place skeleton JCL in a proclib. The problem is getting
Oracle on the mainframe to somehome invoke the internal reader
to submit the JCL.

-Original Message-
Greg
Sent: Monday, November 18, 2002 10:04 AM
To: Multiple recipients of list ORACLE-L


Babette,

If you are on a UNIX platfrom you can ftp the JCL directly to the JES reader
to submit a job on the mainframe. You need to set the parameter "site
filetype=JES". On my web site (http://www.oracle-developer.us/code.htm) I
have code posted to allow Oracle 8i or greater to FTP directly from Oracle.

Here is an example using just UNIX:

HOW TO SUBMIT A MAINFRAME BATCH JOB FROM UNIX

STEP ONE: Create your jcl in a unix file. remember to use all upper case!

EXAMPLE: iefbr14.jcl

//M33TEST1 JOB (HRP,TEST),M33,CLASS=V,MSGCLASS=X,NOTIFY=M33
//STEP01  EXEC PGM=IEFBR14
//DD1   DD DSN=M33.TEST.FILE2,
// UNIT=HRSDA,
// SPACE=(CYL,(1,1),RLSE),
// DCB=(RECFM=FB,LRECL=080,BLKSIZE=27920,DSORG=PS),
// DISP=(NEW,CATLG,DELETE)
//SYSPRINT DD SYSOUT=*
//SYSOUT   DD SYSOUT=*
//*

STEP TWO: Create a job script to FTP your JCL to the mainframe.
  192.6.1.79 is the address of the mainframe for this example.
  'uid' should be replaced with your user id.
  'password' should be replaced with your password.
  'iefbr14.jcl' is the file that we created in step one.
  Remember to grant execute rights to the job script using chmod.


### ftp a job to be submitted on the mainframe

ftp -vn 192.6.1.79 

RE: opinions on SAN devices for Oracle

[Steve McClure]

We are using the Hitachi 9200, and have experienced no troubles in the year
or so we have had it.  I have no experience with the other solutions you
mentioned.  I lost the RAID configuration battle, and we have 3 databases,
one production(oltp), two development/reporting spread across a 5 disk raid
5.  The sales "engineer" said that with all the "pathways and cache" you
will not see any measurable IO contention, well he has been right with the
exception of times when I have been doing large OS file operations while a
developer is performing batch inserts/updates that produce gigabytes of
redo.  That last sentence may have sounded like a complaint, it wasn't, well
maybe I am complaining about losing the configuration battle, but I am a fan
of the product.

Steve McClure

-Original Message-
[EMAIL PROTECTED]
Sent: Tuesday, November 19, 2002 10:50 AM
To: Multiple recipients of list ORACLE-L


Hi All,

We are considering the following 3 SAN storage devices.  If anyone can
share any info on any I would appreciate it.
They all have 2gig fibre channel.

Hitachi 9200
EMC CX400
HP EVA 2c2d

Thanks
Rick


--
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: Steve McClure
  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: 1M STRIPE SIZE BEST?

[Khedr, Waleed]

EMC has now hardware striping. The smallest stripe size is one track (1MB).

Regards,

Waleed

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


A number of papers recommend a stripe size of 1 M (even for EMC) for volumes
containing data files.  I also have the following email from Eyal Aronoff of
Quest dated Nov 2000.  A number of the white papers are more recent.


The reasons for a larger stripe size on a non-RAID 5 device are: 
1) Sequential reads are faster if you can take advantege of the read ahead
built into the disk caching 
2) If a 64K read does not start on the first block of the stripe, two
"spindled" are locked for the duration of the read

However, lately we have been testing some EMC gear and it looks like EMC
have optimized both of those for smaller strip size too.

The bottom line - I no longer have an opinion one way or another. The
undelying technology just changes too rapidly. 

Eyal 


Your opinions/comments as far as a "best" practice in setting stripe sizes
would be greatly appreciated.

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

1M STRIPE SIZE BEST?

[Post, Ethan]

A number of papers recommend a stripe size of 1 M (even for EMC) for volumes
containing data files.  I also have the following email from Eyal Aronoff of
Quest dated Nov 2000.  A number of the white papers are more recent.


The reasons for a larger stripe size on a non-RAID 5 device are: 
1) Sequential reads are faster if you can take advantege of the read ahead
built into the disk caching 
2) If a 64K read does not start on the first block of the stripe, two
"spindled" are locked for the duration of the read

However, lately we have been testing some EMC gear and it looks like EMC
have optimized both of those for smaller strip size too.

The bottom line - I no longer have an opinion one way or another. The
undelying technology just changes too rapidly. 

Eyal 


Your opinions/comments as far as a "best" practice in setting stripe sizes
would be greatly appreciated.

Thanks,
Ethan
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  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: Query tuning with tablename alias

[Hopper, Wendy S]

I am thinking that it must have been, since the previous email correctly
shows that it will not execute.  I do not have access to the actual db and
code yet, just paper copies of the queries that I was given to look over.
Thanks to everyone for their feedback on this.  Obviously this is not an
issue.  

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


Wendy - Could this SQL have been generated by a program? 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

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



Knowing about how a hint works with aliases is what made me wonder about
impacts to other areas.  I have never seen it mentioned in any of the
manuals I have read, and I just thought maybe someone else had experience
with this.  I am trying to figure out why the developer of these queries
took the time to assign alias names to all these table names in their FROM
clause, and then never used them elsewhere in the SELECT or WHERE portions.
Maybe they were copying from a template...  

I realize there is a lot to be gained by moving the remote tables locally
where possible.  We are hoping to use local snapshots instead of going to
remote instances.

Thanks again!

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


Wendy,

I have never heard of alias' providing either a performance gain or
reduction.  One thing I haven't verified though is the impact on hints.  It
has been my observation that if a table has an alias then that alias must
be used in the hint.  For example:

select /*+ index(user) */ *
from ctcs_user u;

Does not use an index, whereas:

select /*+ index(u) */ *
from ctcs_user u;

Does use an index.

If you queries are joining tables across instances, however, then I'd be
looking there for performance issues.  Oracle doesn't join across instances
really - it tends to just temporarily copy one table to a different
instance.  (Someone technical can explain this properly).

Regards,
 Mark.




 

"Hopper, Wendy

S"   To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>   
 Subject: Query tuning with
tablename alias
Sent by:

[EMAIL PROTECTED]

om

 

 

21/11/2002

05:13

Please respond

to ORACLE-L

 

 





Hello list.

I have recently been tasked with trying to optimize some slow performing
queries (Oracle 8.1.7) for an application that generates reports in a data
warehouse type environment.  I have noticed in most of the queries that the
table names have been aliased, but not used elsewhere in the query.

IE.   SELECT  table1.column1,
  table1.column2,
  table2.column1
   FROM table1 A,
  table2 B
   WHERE   table1.key = table2.key;

Are there any advantage/disadvantages to giving these tables aliases and
not using them anywhere else?   I am thinking that if the tables have been
assigned alias names, they should be referred to by alias names, but I
guess I have not seen anything documented on this officially.   These
queries join tables against remote tables in a different instance, if that
makes a difference.  Any insight on this would be great.

Thanks.


Wendy Hopper









<<>>

   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
   by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
  that do not relate to the official business of
 Transurban City Link Ltd
 shall be understood as neither given nor endorsed by it.
<<<>


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

RE: Query tuning with tablename alias

[DENNIS WILLIAMS]

Wendy - Could this SQL have been generated by a program? 
Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

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



Knowing about how a hint works with aliases is what made me wonder about
impacts to other areas.  I have never seen it mentioned in any of the
manuals I have read, and I just thought maybe someone else had experience
with this.  I am trying to figure out why the developer of these queries
took the time to assign alias names to all these table names in their FROM
clause, and then never used them elsewhere in the SELECT or WHERE portions.
Maybe they were copying from a template...  

I realize there is a lot to be gained by moving the remote tables locally
where possible.  We are hoping to use local snapshots instead of going to
remote instances.

Thanks again!

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


Wendy,

I have never heard of alias' providing either a performance gain or
reduction.  One thing I haven't verified though is the impact on hints.  It
has been my observation that if a table has an alias then that alias must
be used in the hint.  For example:

select /*+ index(user) */ *
from ctcs_user u;

Does not use an index, whereas:

select /*+ index(u) */ *
from ctcs_user u;

Does use an index.

If you queries are joining tables across instances, however, then I'd be
looking there for performance issues.  Oracle doesn't join across instances
really - it tends to just temporarily copy one table to a different
instance.  (Someone technical can explain this properly).

Regards,
 Mark.




 

"Hopper, Wendy

S"   To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>   
 Subject: Query tuning with
tablename alias
Sent by:

[EMAIL PROTECTED]

om

 

 

21/11/2002

05:13

Please respond

to ORACLE-L

 

 





Hello list.

I have recently been tasked with trying to optimize some slow performing
queries (Oracle 8.1.7) for an application that generates reports in a data
warehouse type environment.  I have noticed in most of the queries that the
table names have been aliased, but not used elsewhere in the query.

IE.   SELECT  table1.column1,
  table1.column2,
  table2.column1
   FROM table1 A,
  table2 B
   WHERE   table1.key = table2.key;

Are there any advantage/disadvantages to giving these tables aliases and
not using them anywhere else?   I am thinking that if the tables have been
assigned alias names, they should be referred to by alias names, but I
guess I have not seen anything documented on this officially.   These
queries join tables against remote tables in a different instance, if that
makes a difference.  Any insight on this would be great.

Thanks.


Wendy Hopper









<<>>

   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
   by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
  that do not relate to the official business of
 Transurban City Link Ltd
 shall be understood as neither given nor endorsed by it.
<<<>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Richard
  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: Hopper, Wendy S
  INET: [EMAIL PROTECTED]

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

RE: Query tuning with tablename alias

[Khedr, Waleed]

Title: Message



I do 
not think Oracle will run the sql below. Once you have an alias for a table, 
only that alias could be used as an alias (not the table 
name).
 
Example:
 
Oracle9i Enterprise Edition Release 9.2.0.2.0 - ProductionWith the 
Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.2.0 - 
Production
 
SQL> desc 
dual; Name  
Null?    Type - 
 
-- DUMMY  
VARCHAR2(1)
 
SQL> select a.dummy from dual a;
 
D-X
 
SQL> select dual.dummy from dual a;select dual.dummy from dual 
a   *ERROR at line 1:ORA-00904: 
"DUAL"."DUMMY": invalid identifier
 
SQL> select dual.dummy from dual;
 
D-X
 
 
 
Waleed

  -Original Message-From: Hopper, Wendy S 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 20, 2002 
  1:13 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Query tuning with tablename alias
  Hello 
  list.
   
  I have recently 
  been tasked with trying to optimize some slow performing queries (Oracle 
  8.1.7) for an application that generates reports in a data warehouse type 
  environment.  I have noticed in most of the queries that the table names 
  have been aliased, but not used elsewhere in the 
  query.  
   
  IE.   SELECT 
   table1.column1,
    table1.column2,
    
  table2.column1
     
  FROM table1 A,
    
  table2 B
     
  WHERE   table1.key = table2.key;
   
  Are there any 
  advantage/disadvantages to giving these tables aliases and not using them 
  anywhere else?   I am thinking that if the tables have been assigned 
  alias names, they should be referred to by alias names, but I guess I 
  have not seen anything documented on this officially.   These 
  queries join tables against remote tables in a different instance, if that 
  makes a difference.  Any insight on this would be 
  great.
      
  
  Thanks.
  Wendy 
  Hopper 
   

RE: Dynamic views

[DENNIS WILLIAMS]

Jack - If I can answer without stepping on David's toes, the problem with
do-it-yourself partitioning is to avoid changing the SQL. The application
may not use PL/SQL, so dynamic SQL wouldn't be a benefit.

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED] 

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, November 20, 2002 2:04 PM
To: Multiple recipients of list ORACLE-L



David,

How about Dynamic SQL in an Instead Of trigger?

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]



 

david hill

 <[EMAIL PROTECTED]>

Sent by:  cc:

[EMAIL PROTECTED]   Subject: Dynamic views

m

 

 

11/20/2002

12:48 PM

Please respond

to ORACLE-L

 

 





Hi Guys


I'm trying to see if I can get around paying oracle $50,000 for
partitioning


I have a huge table and I want to partition it on date so I created 12


tables


_JAN


_FEB


And so on


But I want to create a view that will be used for inserting so it always


Points to the current month without having to recreate the view every
month.


Is there anyway anybody can think of doing this?


Thanks


David Hill


DBA


Le Chateau







-- 
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: 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: Query tuning with tablename alias

[Hopper, Wendy S]

Knowing about how a hint works with aliases is what made me wonder about
impacts to other areas.  I have never seen it mentioned in any of the
manuals I have read, and I just thought maybe someone else had experience
with this.  I am trying to figure out why the developer of these queries
took the time to assign alias names to all these table names in their FROM
clause, and then never used them elsewhere in the SELECT or WHERE portions.
Maybe they were copying from a template...  

I realize there is a lot to be gained by moving the remote tables locally
where possible.  We are hoping to use local snapshots instead of going to
remote instances.

Thanks again!

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


Wendy,

I have never heard of alias' providing either a performance gain or
reduction.  One thing I haven't verified though is the impact on hints.  It
has been my observation that if a table has an alias then that alias must
be used in the hint.  For example:

select /*+ index(user) */ *
from ctcs_user u;

Does not use an index, whereas:

select /*+ index(u) */ *
from ctcs_user u;

Does use an index.

If you queries are joining tables across instances, however, then I'd be
looking there for performance issues.  Oracle doesn't join across instances
really - it tends to just temporarily copy one table to a different
instance.  (Someone technical can explain this properly).

Regards,
 Mark.




 

"Hopper, Wendy

S"   To: Multiple recipients of list
ORACLE-L <[EMAIL PROTECTED]>   
 Subject: Query tuning with
tablename alias
Sent by:

[EMAIL PROTECTED]

om

 

 

21/11/2002

05:13

Please respond

to ORACLE-L

 

 





Hello list.

I have recently been tasked with trying to optimize some slow performing
queries (Oracle 8.1.7) for an application that generates reports in a data
warehouse type environment.  I have noticed in most of the queries that the
table names have been aliased, but not used elsewhere in the query.

IE.   SELECT  table1.column1,
  table1.column2,
  table2.column1
   FROM table1 A,
  table2 B
   WHERE   table1.key = table2.key;

Are there any advantage/disadvantages to giving these tables aliases and
not using them anywhere else?   I am thinking that if the tables have been
assigned alias names, they should be referred to by alias names, but I
guess I have not seen anything documented on this officially.   These
queries join tables against remote tables in a different instance, if that
makes a difference.  Any insight on this would be great.

Thanks.


Wendy Hopper









<<>>

   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
   by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
  that do not relate to the official business of
 Transurban City Link Ltd
 shall be understood as neither given nor endorsed by it.
<<<>


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Richard
  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: Hopper, Wendy S
  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 'ListGur

RE: Query tuning with tablename alias

[Jamadagni, Rajendra]

Title: RE: Query tuning with tablename alias





but if you provide hints on such statements, you better be using aliases for hints .


Aliases are used for readability ... you either use the aliases or user tablename.column but not both ... world is already confusing enough ...

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!



*This e-mail 
message is confidential, intended only for the named recipient(s) above and may 
contain information that is privileged, attorney work product or exempt from 
disclosure under applicable law. If you have received this message in error, or are 
not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 
and delete this e-mail message from your computer, Thank 
you.*1

Re: Query tuning with tablename alias

[Mark Richard]

Wendy,

I have never heard of alias' providing either a performance gain or
reduction.  One thing I haven't verified though is the impact on hints.  It
has been my observation that if a table has an alias then that alias must
be used in the hint.  For example:

select /*+ index(user) */ *
from ctcs_user u;

Does not use an index, whereas:

select /*+ index(u) */ *
from ctcs_user u;

Does use an index.

If you queries are joining tables across instances, however, then I'd be
looking there for performance issues.  Oracle doesn't join across instances
really - it tends to just temporarily copy one table to a different
instance.  (Someone technical can explain this properly).

Regards,
 Mark.




   

"Hopper, Wendy 

S"   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>   
 Subject: Query tuning with tablename 
alias
Sent by:   

[EMAIL PROTECTED] 

om 

   

   

21/11/2002 

05:13  

Please respond 

to ORACLE-L

   

   





Hello list.

I have recently been tasked with trying to optimize some slow performing
queries (Oracle 8.1.7) for an application that generates reports in a data
warehouse type environment.  I have noticed in most of the queries that the
table names have been aliased, but not used elsewhere in the query.

IE.   SELECT  table1.column1,
  table1.column2,
  table2.column1
   FROM table1 A,
  table2 B
   WHERE   table1.key = table2.key;

Are there any advantage/disadvantages to giving these tables aliases and
not using them anywhere else?   I am thinking that if the tables have been
assigned alias names, they should be referred to by alias names, but I
guess I have not seen anything documented on this officially.   These
queries join tables against remote tables in a different instance, if that
makes a difference.  Any insight on this would be great.

Thanks.


Wendy Hopper









<<>>
   Privileged/Confidential information may be contained in this message.
  If you are not the addressee indicated in this message
   (or responsible for delivery of the message to such person),
you may not copy or deliver this message to anyone.
In such case, you should destroy this message and kindly notify the sender
   by reply e-mail or by telephone on (61 3) 9612-6999.
   Please advise immediately if you or your employer does not consent to
Internet e-mail for messages of this kind.
Opinions, conclusions and other information in this message
  that do not relate to the official business of
 Transurban City Link Ltd
 shall be understood as neither given nor endorsed by it.
<<<>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mark Richard
  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 informatio

Scripting Deltas (for development-shop dba's?)

[Magaliff, Bill]

We're a development shop with a fairly robust product and some very
demanding clients, one of which would like complete error-checking and
rollback capabilities built into the delta scripts we distribute with each
new release of our product.  These releases can include 100 or more separate
mini-scripts - each mini-script can contain ddl, dml, or both - and
correspond to changes the client needs to track.

I've been playing with several different mechanisms for handling this - I've
posted info on this before - but I'm interested to hear how others do it.

I'm striving for simplicity and as much reusable code as possible.  I'm
happy to take this off-line, if it gets too detailed for the list.

Thanks

-bill
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Magaliff, Bill
  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: SQL puzzle - using where (a, b, c) in select (a, b, c from...

[Jacques Kilchoer]

Title: RE: SQL puzzle - using where (a, b, c) in select (a, b, c from...





-Original Message-
>From: Naveen Nahata [mailto:[EMAIL PROTECTED]]
>Sent: mercredi, 20. novembre 2002 03:58
>
>Why not use some suffieintly random and crap value
> lets say '~~~CRAP~~~' to replace nulls in NVL
> or DECODE for the query to work with Varchar columns?
> I think one can be reasonably sure that such a value
> will never be inserted into the column :-)
> 
>Otherwise, I don't think there is any other solution.



Thank you Naveen. The problem is that this solution will be part of a commercial product, and I would hate to have to include in the Release Notes something like "This product is not supported if your columns contain the value '~~~CRAP~~~' ". :)

Instead, I will suggest to the developer that we code a long statement allowing for null columns. e.g.
delete from widgets_copy
where rowid in
 (
   select min (rowid) from widgets_copy x
   where x.cost is null and x.sell is null
 and x.id in
    (select y.id from widgets y where y.cost is null and y.sell is null)
   group by x.id
  union
   select min (rowid) from widgets_copy x
   where x.sell is null
 and (x.id, x.cost) in
    (select y.id, y.cost from widgets y where y.sell is null)
   group by x.id, x.cost
  union
   select min (rowid) from widgets_copy x
   where x.cost is null
 and (x.id, x.sell) in
    (select y.id, y.sell from widgets y where y.cost is null)
   group by x.id, x.sell
  union
   select min (rowid) from widgets_copy x
   where (x.id, x.cost, x.sell) in
    (select y.id, y.cost, y.sell from widgets y)
   group by x.id, x.cost, x.sell
 ) ;

Re: to_date function and NLS settings on client

[Arup Nanda]

Title: to_date function and NLS settings on client



Helmut,
 
The cleint nls_date setting can be done by 

 
ALTER SESSION SET NLS_DATE_FORMAT = 
'dd.mm.';
 
The currect setting can be seen from 
v$nls_parameters and if the session has explicitly set it, the new value 
will be shown.
 
However, I feel this problem more than that. Please 
note that the DATE datatype is not just date, but timestamp as well. Your query 
is comparing a date field to '01.10.1950', which means the 00:00:00 of 1st Oct 
1950. Your data field could containt 13:30:31 of 1st Oct 1950. Therefore the 
comparison fails. Have you tried this:
 
select ... from ... where trunc(datefield1) = 
'01.10.1950'
 
or
 
select .. from .. where datefield1 between 
'01.10.1950' and '02.10.1950'
 
Hop this helps.
 
Arup Nanda
www.proligence.com

  - Original Message - 
  From: 
  Daiminger, Helmut 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, November 20, 2002 8:03 
  AM
  Subject: to_date function and NLS 
  settings on client
  
  Hello! 
  We have several Windows clients connecting to our db-server. 
  Every client issues the statement: 
  select  from thabe where date = 
  to_date('01.10.1950','DD-MM-'); 
  Some clients return the correct number of rows where as others 
  return nothing. 
  I know that this has something to to with the NLS settings on 
  the client. 
  How do I find out the NLS settings on the client? Just in the 
  registry? 
  How coem this does not work although I explicity specify a 
  format mask? 
  This is 8.1.7 on Solaris. 
  Thanks, Helmut 

Re: Dynamic views

[Jay Hostetter]

David,

  You're talking about Partitioned Views, which was the predecessor of partitioned 
tables.  This was the drawback of partitioned views - you had to insert into the 
individual tables.  You can make a sql*loader script figure out which table to insert 
into (if you're using sql*loader).  Otherwise you might what to try writing a 
procedure.  
  If you have any problems with partitioned views, expect Oracle Support to tell that 
you should be using partitioned tables.



Jay Hostetter
Oracle DBA
D. & E. Communications
Ephrata, PA  USA

>>> [EMAIL PROTECTED] 11/20/02 01:48PM >>>
Hi Guys
I'm trying to see if I can get around paying oracle $50,000 for partitioning
I have a huge table and I want to partition it on date so I created 12
tables
_JAN
_FEB
And so on

But I want to create a view that will be used for inserting so it always
Points to the current month without having to recreate the view every month.
Is there anyway anybody can think of doing this?

Thanks
David Hill
DBA
Le Chateau




**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the 
individual or entity to which they are addressed and may contain information that is 
privileged, proprietary and confidential. If you are not the intended recipient, you 
may not use, copy or disclose to anyone the message or any information contained in 
the message. If you have received this communication in error, please notify the 
sender and delete this e-mail message. The contents do not represent the opinion of 
D&E except to the extent that it relates to their official business.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jay Hostetter
  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: Dynamic views

[Khedr, Waleed]

Title: Dynamic views



Have a 
job (cron or dbms_job) that fires the first day of the month to recreate the 
view.
 
Waleed

  -Original Message-From: david hill 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 20, 2002 
  1:48 PMTo: Multiple recipients of list ORACLE-LSubject: 
  Dynamic views
  Hi Guys
  I'm trying to see if I can get 
  around paying oracle $50,000 for partitioning
  I have a huge table and I want 
  to partition it on date so I created 12
  tables
  _JAN
  _FEB
  And so on
  But I want to create a view that 
  will be used for inserting so it always
  Points to the current month without having to recreate the 
  view every month.
  Is there anyway anybody can 
  think of doing this?
  Thanks
  David Hill
  DBA
  Le Chateau

Re: Dynamic views

[JApplewhite]

David,

How about Dynamic SQL in an Instead Of trigger?

Jack C. Applewhite
Database Administrator
Austin Independent School District
Austin, Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]



   
  
david hill 
  
 <[EMAIL PROTECTED]>  
  
Sent by:  cc:  
  
[EMAIL PROTECTED]   Subject: Dynamic views   
  
m  
  
   
  
   
  
11/20/2002 
  
12:48 PM   
  
Please respond 
  
to ORACLE-L
  
   
  
   
  




Hi Guys


I'm trying to see if I can get around paying oracle $50,000 for
partitioning


I have a huge table and I want to partition it on date so I created 12


tables


_JAN


_FEB


And so on


But I want to create a view that will be used for inserting so it always


Points to the current month without having to recreate the view every
month.


Is there anyway anybody can think of doing this?


Thanks


David Hill


DBA


Le Chateau







-- 
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: Query tuning with tablename alias

[DENNIS WILLIAMS]

Wendy - I think the difference between using an alias or not is negligible.
My reasoning is that this would be easy to test (good idea if you have a
moment) and there are enough picky Oracle developers that if this was not
negligible, people would have been bragging about this as their secret
method to make their application faster, and some vendors would be selling
SQL pre-processors that would allow you to "use aliases without penalty". So
I wouldn't worry about it, but you are welcome to test this and if you can
find a difference, we would all be glad to hear about it. It has happened
before, where everyone has assumed they knew the answer and later it turned
out that something simple was a bad assumption.


Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]   

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


Hello list.
 
I have recently been tasked with trying to optimize some slow performing
queries (Oracle 8.1.7) for an application that generates reports in a data
warehouse type environment.  I have noticed in most of the queries that the
table names have been aliased, but not used elsewhere in the query.  
 
IE.   SELECT  table1.column1,
  table1.column2,
  table2.column1
   FROM table1 A,
  table2 B
   WHERE   table1.key = table2.key;
 
Are there any advantage/disadvantages to giving these tables aliases and not
using them anywhere else?   I am thinking that if the tables have been
assigned alias names, they should be referred to by alias names, but I guess
I have not seen anything documented on this officially.   These queries join
tables against remote tables in a different instance, if that makes a
difference.  Any insight on this would be great.

Thanks.

Wendy Hopper 

 

-- 
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: Dynamic views

[Arup Nanda]

Title: Dynamic views



Sure, You can write 
an INSTEAD OF trigger on the view that inserts into the right table. 

 
Arup Nanda
www.proligence.com

  - Original Message - 
  From: 
  david 
  hill 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, November 20, 2002 1:48 
  PM
  Subject: Dynamic views
  
  Hi Guys
  I'm trying to see if I can get 
  around paying oracle $50,000 for partitioning
  I have a huge table and I want 
  to partition it on date so I created 12
  tables
  _JAN
  _FEB
  And so on
  But I want to create a view that 
  will be used for inserting so it always
  Points to the current month without having to recreate the 
  view every month.
  Is there anyway anybody can 
  think of doing this?
  Thanks
  David Hill
  DBA
  Le Chateau

Help in html Output from Oracle Reports...PLease

[Kitty Luo]

Hi,
I am having problem with html output from oracle reports.I have designed a
report which looks for *.jpg pictures location in the database and then from
physical location generate report as html.For this output itself converts
*.jpg to *.gif so the output comes with pictures but very shaded pictures
which is not acceptable.No problem with pdf output.If I can get some help
how to prevent report to convert .jpg to .gif or some other way to generate
a good html output.
Thanks in Advance

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

OCI hang until TCP timeout on an established connection

[Johnston, Tim]

Hi Guys...

  I have an application with a remote client...  The client is written in
Smalltalk and connects to the database via OCI...  It establishes a
connection to the database and keeps it open...  If the network link between
the remote client and the server fails, the remote client does not realize
it until the next attempt to communicate with the database...  This is
fine...  The problem is that once it does try to communicate with the
database it waits 8 minutes after the attempt before returning the
ORA-3113...  I have done a little search and so far come up empty...  I am
hesitate to muck with the tcp parameters due to side effects (i.e.
tcp_ip_abort_interval)...  Does anyone have an idea how I can reduce the
timeout delay without changing tcp parms?  Has anyone experienced a similar
issue and solved it some other way?

Solaris 9
Oracle 9.2.0.2

Thanks in advance...

Regards,
Tim Johnston
Tel:  978-322-4226
Fax: 978-322-4100

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

Dynamic views

[david hill]

Title: Dynamic views





Hi Guys

I'm trying to see if I can get around paying oracle $50,000 for partitioning

I have a huge table and I want to partition it on date so I created 12

tables

_JAN

_FEB

And so on

But I want to create a view that will be used for inserting so it always

Points to the current month without having to recreate the view every month.

Is there anyway anybody can think of doing this?

Thanks

David Hill

DBA

Le Chateau

Query tuning with tablename alias

[Hopper, Wendy S]

Title: Message



Hello 
list.
 
I have recently been 
tasked with trying to optimize some slow performing queries (Oracle 8.1.7) for 
an application that generates reports in a data warehouse type 
environment.  I have noticed in most of the queries that the table names 
have been aliased, but not used elsewhere in the 
query.  
 
IE.   SELECT 
 table1.column1,
  table1.column2,
  
table2.column1
   
FROM table1 A,
  
table2 B
   WHERE   
table1.key = table2.key;
 
Are there any 
advantage/disadvantages to giving these tables aliases and not using them 
anywhere else?   I am thinking that if the tables have been assigned 
alias names, they should be referred to by alias names, but I guess I have 
not seen anything documented on this officially.   These queries join 
tables against remote tables in a different instance, if that makes a 
difference.  Any insight on this would be great.
    

Thanks.
Wendy 
Hopper 
 

RE: [Q] ORACLE 9i fast_start_mttr_target and log_checkpoint_inte

[M Rafiq]

Dennis,

Don't get frustrated with such remarks. You are one of the most active 
contributor/member of this list and atleast I like your signature 40%OCP. 
Yes, I agree with you that OCP is not the final word for expertise. It 
definatley force you to read most oracle material to get it certified but 
practical experience worth much more than just getting certified without any 
practical experience. It is just helping you to dealt with recruitors and 
inexperinced IT bosses who rely on it.

Regards
Rafiq
OCP 7.3,8,8i,9i








Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 20 Nov 2002 09:00:02 -0800

He he. There are 5 OCP modules. I've passed 2, hence 40%. I just thought I
should warn everyone that I'm not the expert that others on the list are.
:-) And no, I don't believe the OCP is the final word on Oracle expertise by
any means. I should probably discontinue using that signature to avoid
annoying people. Until I pass another module. :-)

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


-Original Message-
Sent: Wednesday, November 20, 2002 9:44 AM
To: Multiple recipients of list ORACLE-L
log_checkpoint_inte



Dennis Williams
DBA, 40%OCP

^^

How does one get to be a 40% OCP?  Is that like being a 40% expert?

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


_
Add photos to your messages with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: M Rafiq
 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: RMAN compatibility issues

[Freeman, Robert]

On page 204 of Oracle9i RMAN Backup and Recovery (available on Amazon!) the
answer is written thusly:


When using database version 8.1.7 
When using RMAN version 8.1.x
Catalog Schema requirement:

Oracle 8.1.x (or later) catalog database, with a catalog schema created by
an RMAN version that is equivalent to or greater than the RMAN version being
used to back up the database.

In other words, if you are using 8.1.7 RMAN, then you need to use a recovery
catalog schema created by and RMAN executable that is also 8.1.7 or later. 

RF

Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!

Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of you. 

 



-Original Message-
Sent: Wednesday, November 20, 2002 10:21 AM
To: Multiple recipients of list ORACLE-L


DBAs,

I experienced following while trying to test interoperatibility 
between 816 and 817.

Any comments, experiences?


Target -> 817 Catalog -> 816

rman rcvcat rman/rman@rcatqual
connect target

register database;

.
RMAN-06429: RCVCAT database is not compatible with this version of RMAN
.



Target -> 816 Catalog -> 817

rman rcvcat rman/rman@rcattest
connect target

register database;

.
RMAN-08006: database registered in recovery catalog
.


-Rachna
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachna Vaidya
  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: Freeman, Robert
  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: How to check/show long SQL statement?

[Sakthi , Raj]

MmmThats why order by piece is there.
As the name suggests( which is very rare in oracle
naming conventions..:) j/k ) the long SQL is broken
into pieces.
so be a sport and try it !

Cheers,
RS

--- dist cash <[EMAIL PROTECTED]> wrote:
> I don't think so.  V$sqltext even worse.  The
> v$sqltext definition
> 
> SQL> desc v$sqltext
> Name  Null?   
> Type
> -  
> 
> ADDRESS   
> RAW(4)
> HASH_VALUE
> NUMBER
> COMMAND_TYPE  
> NUMBER
> PIECE 
> NUMBER
> SQL_TEXT  
> VARCHAR2(64)
> 
> 
> It ONLY varchar2(64).
> 
> 
> 
> 
> 
> 
> >From: Connor McDonald <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> >Subject: Re: How to check/show long SQL statement?
> >Date: Wed, 20 Nov 2002 07:30:01 -0800
> >
> >v$sqltext
> >order by piece
> >
> >hth
> >connor
> >
> >  --- dist cash <[EMAIL PROTECTED]> wrote: >
> > >
> > >
> > > I need check what is SQL statement runnig on
> ORACLE.
> > >  their have two view
> > > v$sql and v$sqlare.  The entry "sql_text
> > > varchar2(1000)", it is too
> > > short to show SQL statement.  Some of the sql
> > > Statement we use > 3000
> > > characters.  Does their has way to check/show
> long
> > > SQL statement?
> > >
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
>
>_
> > > Add photos to your messages with MSN 8. Get 2
> months
> > > FREE*.
> > > http://join.msn.com/?page=features/featuredemail
> > >
> > > --
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > --
> > > Author: dist cash
> > >   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).
> >
> >=
> >Connor McDonald
> >http://www.oracledba.co.uk
> >http://www.oaktable.net
> >
> >"GIVE a man a fish and he will eat for a day. But
> TEACH him how to fish, 
> >and...he will sit in a boat and drink beer all day"
> >
> >__
> >Do You Yahoo!?
> >Everything you'll ever need on one web page
> >from News and Sport to Email and Music Charts
> >http://uk.my.yahoo.com
> >--
> >Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> >--
> >Author: =?iso-8859-1?q?Connor=20McDonald?=
> >   INET: [EMAIL PROTECTED]
> >
> >Fat City Network Services-- 858-538-5051
> http://www.fatcity.com
> >San Diego, California-- Mailing list and
> web hosting services
>
>-
> >To REMOVE yourself from this mailing list, send an
> E-Mail message
> >to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> >the message BODY, include a line containing: UNSUB
> ORACLE-L
> >(or the name of mailing list you want to be removed
> from).  You may
> >also send the HELP command for other information
> (like subscribing).
> 
> 
>
_
> MSN 8 helps eliminate e-mail viruses. Get 2 months
> FREE*. 
> http://join.msn.com/?page=features/virus
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: dist cash
>   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).


__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sakthi , Raj
  INET: [EMAIL PROTECTED]

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

RE: Oracle-L at UKOUG

[Hately, Mike (NESL-IT)]

I'm driving down on Monday morning.
Should be OK because the traffic system round Brum is fairly quiet and easy
to navigate during rush hour isn't it?

=)

Mike


-Original Message-
Sent: 20 November 2002 16:19
To: Multiple recipients of list ORACLE-L


I will be arriving on Sunday and am open to a get together that evening.
For those who have attended previously, what are good times?

-Original Message-
Sent: Wednesday, November 20, 2002 8:50 AM
To: Multiple recipients of list ORACLE-L


IIRC (and it's been at least 3 years), there was a restaurant/pub on
the "island" near the conference center that was nice. Large, decent
food and excellent beer

of course, if you just want the sugar/caffeine rush, you can always go
visit the Cadbury factory


--- Connor McDonald <[EMAIL PROTECTED]> wrote:
> I'm in - when you consider the wealth of nightlife in
> Birmingham (yeah right!) then I'm sure we can find
> somewhere to go :-)
> 
>  --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
> interested! It IS always nice to put names to
> > faces..
> > 
> > Any ideas for when/where?
> > 
> > Mark
> > 
> > -Original Message-
> > Mike (NESL-IT)
> > Sent: 20 November 2002 09:13
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > I'll be attending and it would be nice to put faces
> > to some of the names on
> > this list.
> >  
> > Mike
> > 
> > -Original Message-
> > Sent: 20 November 2002 07:28
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > 
> > I will be at UKOUG and so will be another of other
> > people of the Oaktable
> > network.
> > 
> >  
> > 
> > Anjo.
> > 
> >  
> > 
> > -Original Message-
> > Sent: Tuesday, November 19, 2002 9:19 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> >  
> > 
> > Anyone interested in an Oracle-L get together at
> > UKOUG for those of us poor
> > souls who were not able to attend Oracle World? OW
> > attendees welcome also!
> > 
> >  
> > 
> > Dan Fink
> > 
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Hately, Mike (NESL-IT)
> >   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: Mark Leith
> >   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). 
> 
> =
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
> 
> "GIVE a man a fish and he will eat for a day. But TEACH him how to
> fish, and...he will sit in a boat and drink beer all day"
> 
> __
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  INET: [EMAIL PROTECTED]

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

RE: Oracle-L at UKOUG

[Mark Leith]

The best time is most probably Monday evening. There is always a gala
evening on the Tuesday night that a lot of people attend (and speakers
always get free tickets for this as well so..). Monday night, there is
always the "reception" with plenty of booze to go around - so going for a
beer and some food after this sounds like a "natural progression" :)
Wednesday everyone just wants to leave after being hung over all day from
the previous gala evening.. lol..

-Original Message-
Sent: 20 November 2002 16:19
To: Multiple recipients of list ORACLE-L


I will be arriving on Sunday and am open to a get together that evening.
For those who have attended previously, what are good times?

-Original Message-
Sent: Wednesday, November 20, 2002 8:50 AM
To: Multiple recipients of list ORACLE-L


IIRC (and it's been at least 3 years), there was a restaurant/pub on
the "island" near the conference center that was nice. Large, decent
food and excellent beer

of course, if you just want the sugar/caffeine rush, you can always go
visit the Cadbury factory


--- Connor McDonald <[EMAIL PROTECTED]> wrote:
> I'm in - when you consider the wealth of nightlife in
> Birmingham (yeah right!) then I'm sure we can find
> somewhere to go :-)
>
>  --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
> interested! It IS always nice to put names to
> > faces..
> >
> > Any ideas for when/where?
> >
> > Mark
> >
> > -Original Message-
> > Mike (NESL-IT)
> > Sent: 20 November 2002 09:13
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > I'll be attending and it would be nice to put faces
> > to some of the names on
> > this list.
> >
> > Mike
> >
> > -Original Message-
> > Sent: 20 November 2002 07:28
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > I will be at UKOUG and so will be another of other
> > people of the Oaktable
> > network.
> >
> >
> >
> > Anjo.
> >
> >
> >
> > -Original Message-
> > Sent: Tuesday, November 19, 2002 9:19 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > Anyone interested in an Oracle-L get together at
> > UKOUG for those of us poor
> > souls who were not able to attend Oracle World? OW
> > attendees welcome also!
> >
> >
> >
> > Dan Fink
> >
> > --
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > --
> > Author: Hately, Mike (NESL-IT)
> >   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: Mark Leith
> >   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).
>
> =
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
>
> "GIVE a man a fish and he will eat for a day. But TEACH him how to
> fish, and...he will sit in a boat and drink beer all day"
>
> __
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
--
Please see th

RE: [Q] ORACLE 9i fast_start_mttr_target and log_checkpoint_inte

[DENNIS WILLIAMS]

He he. There are 5 OCP modules. I've passed 2, hence 40%. I just thought I
should warn everyone that I'm not the expert that others on the list are.
:-) And no, I don't believe the OCP is the final word on Oracle expertise by
any means. I should probably discontinue using that signature to avoid
annoying people. Until I pass another module. :-)

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


-Original Message-
Sent: Wednesday, November 20, 2002 9:44 AM
To: Multiple recipients of list ORACLE-L
log_checkpoint_inte


 
Dennis Williams 
DBA, 40%OCP 

^^

How does one get to be a 40% OCP?  Is that like being a 40% expert?

Adam
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Donahue, Adam
  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: [Q] ORACLE 9i fast_start_mttr_target and log_checkpoint_inte

[Hately, Mike (NESL-IT)]

In the docs I've just checked for 9.2 Oracle recommends that
log_checkpoint_timeout, log_checkpoint_interval and fast_start_io_target are
all set to 0 if you're using fast_start_mtr_target because (as the tuning
manual says) "..setting these parameters to active values interferes with
FAST_START_MTTR_TARGET, resulting in a different than expected value
V$INSTANCE_RECOVERY.TARGET_MTTR".

The reference manual states that FAST_START_MTTR_TARGET is overridden (when
specified) by FAST_START_IO_TARGET and LOG_CHECKPOINT_INTERVAL.

I've been playing with this a little on 9.2.
If you have log_checkpoint_interval unset then altering the value of
fast_start_mttr_target influences the value of
V$INSTANCE_RECOVERY.MTTR_TARGET. However when you set
log_checkpoint_interval the value of fast_start_mttr_target seems to become
less important. Hardly conclusive yet but certainly if I wanted to use
fast_start_mttr_target I wouldn't be setting the related parameters.

 
And anyway, what effect does this have on _spin_count? 

Regards,
Mike Hately


-Original Message-
Sent: 20 November 2002 15:24
To: Multiple recipients of list ORACLE-L
log_checkpoint_inte


Gillian - I love it! Dueling Instructors! Since you are still in class, why
don't you show him the manual and ask for clarification. Please check the
class notes on this. My copy is at home. Thanks.
 
Dennis Williams 
DBA, 40%OCP 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, November 19, 2002 5:10 PM
To: Multiple recipients of list ORACLE-L



ok - it so happens I am in the middle of the 9i New Features class. I just
clarified that with the instuctor. And he repeated that if the
log_checkpotint parameters are set, as well as the MTTR, then MTTR overides
whatever the log_checkpoint parameters are set to. 


I did not check out the official documentation. But the class manual says, 


'Internally this is translated to a setting for Log_checkpoint_interval' 


The instructor's name is Steve Bertuoi. Not sure how good he knows his
stuff. 


I guess I could play around with their test database here in the class, and
see what really happens. 


Gillian 


 DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: 


Gillian - But the documentation clearly says just the opposite. It says that
if you set the LOG_CHECKPOINT_INTERVAL parameter, then that is what is used,
rather than FAST_START_MTTR_TARGET. So I don't see how you can say that
FAST_START_MTTR_TARGET overrides the LOG_CHECKPOINT_INTERVAL. Am I missing
something here?


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

-Original Message-
Sent: Tuesday, November 19, 2002 12:59 PM
To: Multiple recipients of list ORACLE-L



Just to add: 


The MTTR parameter overides the Log_checkpoint_interval and the ...timeout
parameter. Oracle sets these log_checkpoint parameters dynamically when the
MTTR parameter is set. 


Gillian 


DENNIS WILLIAMS wrote: 


Dist -
Since nobody seems to have responded,! ! I'll take a crack at it. 
The Oracle9i documentation specifically says (in the Oracle9i Database
Reference) that either FAST_START_IO_TARGET or LOG_CHECKPOINT_INTERVAL will
override FAST_START_MTTR_TARGET. I would tend to believe them.
In the Oracle9i Performance Tuning Guide, Oracle contains a note that
you should disable or remove the FAST_START_IO_TARGET,
LOG_CHECKPOINT_INTERVAL, and LOG_CHECKPOINT_TIMEOUT parameters when using
FAST_START_MTTR_TARGET. Setting these parameters interferes with
FAST_START_MTTR_TARGET. 
I don't see where LOG_CHECKPOINTS_TO_ALERT would cause a problem, but if
you are suspicious, you might check metalink.
As I understand it, the big advantage of FAST_START_MTTR_TARGET is its
ease and accuracy. Your management provides a goal for how long your Oracle
database can take to reco! ! ver in the situation where it crashes. You
simply
convert that number to seconds and set FAST_START_MTTR_TARGET. With the
! ! LOG_CHECKPOINT parameters, the settings are derived more indirectly.
Of course, there is no free lunch. Setting a shorter recovery time may
interfere with performance.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, November 18, 2002 11:04 AM
To: Multiple recipients of list ORACLE-L
log_checkpoint_interval parameter?





I saw a document mention if log_checkpoint_interval define on ORACLE 9i,
the fast_start_mttr_target parameter will be ignore. My question is:

1. we have following on init.ora file:

log_checkpoint_interval = 1
log_checkpoint_timeout = 1800
log_checkpoints_to_alert = true

fast_start_mttr_target=300

Do I need remove all the log_checkpoint* on init.ora file?

2. what kind of ! ! benefit that fast_start_mttr_target better than
log_checkpoint* ?


Thanks



__! ! ___
The new MSN 8: smart spam protection and 2 months FREE* 
http://join.msn.com/?page=features/junkmail

-- 
Please see the official ORACLE-L FAQ: http://

Re: How to check/show long SQL statement?

[dist cash]

I don't think so.  V$sqltext even worse.  The v$sqltext definition

SQL> desc v$sqltext
Name  Null?Type
-  

ADDRESSRAW(4)
HASH_VALUE NUMBER
COMMAND_TYPE   NUMBER
PIECE  NUMBER
SQL_TEXT   VARCHAR2(64)


It ONLY varchar2(64).






From: Connor McDonald <[EMAIL PROTECTED]>
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Subject: Re: How to check/show long SQL statement?
Date: Wed, 20 Nov 2002 07:30:01 -0800

v$sqltext
order by piece

hth
connor

 --- dist cash <[EMAIL PROTECTED]> wrote: >
>
>
> I need check what is SQL statement runnig on ORACLE.
>  their have two view
> v$sql and v$sqlare.  The entry "sql_text
> varchar2(1000)", it is too
> short to show SQL statement.  Some of the sql
> Statement we use > 3000
> characters.  Does their has way to check/show long
> SQL statement?
>
>
> Thanks.
>
>
>
>
>
>
>
>
>
>
_
> Add photos to your messages with MSN 8. Get 2 months
> FREE*.
> http://join.msn.com/?page=features/featuredemail
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: dist cash
>   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).

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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


_
MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. 
http://join.msn.com/?page=features/virus

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: dist cash
 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: ASO Checksumming Parameters

[Deborah Lorraine]

Thanks for the follow up.  I set TNS_ADMIN a different way; I will check 
into this method.  BTW - I mentioned the server trace because it also 
provides information--sometimes more detailed--about the failing client 
connection.

Debi

At 06:28 PM 11/19/2002 -0800, you wrote:
Thanks. I had run "adapters"  and things look okay.  I had not run a trace 
on the server, but connecting via sql*net on the server worked.  However, 
for  a remote client, the listener.trc file complained about 
missing  parameters.  I did some digging  and located info about the ENVS 
parameter for listener.ora e.g.

ENVS = 'TNS_ADMIN=/opt/oracle/dbserver/9.0.1/network/admin'


Once this was set the remote connections began working as well.

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]


-Original Message-
Sent: Tuesday, November 19, 2002 3:34 PM
To: Multiple recipients of list ORACLE-L


Ian,

I would turn on server tracing to see if you get more info about what is
failing; also check what "adapters" on installed the server.

Debi

At 03:04 PM 11/19/2002 -0800, you wrote:
>I'm looking at configuring ASO.  I have sqlnet.ora file on both the client
>and the server which looks like
>
>trace_level_client=16
>sqlnet.encryption_server=accepted
>sqlnet.encryption_client=requested
>sqlnet.encryption_types_server=(RC4_40)
>sqlnet.encryption_types_client=(RC4_40)
>sqlnet.crypto_seed = "-kdje83kkep39487dvmlqEPTbxxe70273"
>sqlnet.crypto_checksum_server=accepted
>sqlnet.crypto_checksum_client=requested
>sqlnet.crypto_checksum_types_server = (MD5)
>sqlnet.crypto_checksum_types_client = (MD5)
>SQLNET.AUTHENTICATION_SERVICES=(NONE)
>SQLNET.AUTHENTICATION_REQUIRED=FALSE
>sqlnet.fips_140 = true
>- 
--
>I've been getting  12645 , parameter not  found errors.  The trace file
>has been very helpful specifiying the missing parameter until now.  Now
>all that
>seems relevant is
>
>nacomdp: exit
>nacomus: exit
>nacomus: entry
>nacomus: servicecrypto-checksumming
>  # of fields6
>  ORACLE error   12645.
>
>
>and
>
>nacomer: error 12645 received from crypto-checksumming service
>nacomer: failed with error 12645
>snauicomparehash: failed with error 12645
>snauicomparehash: exit
>na_client: returning status: "connection in progress"
>
>
>any idea what is wrong or missing?
>
>The client is 9.0.1.3 and the server is 9.0.1.4 both on Solaris 8
>
>Ian MacGregor
>Stanford Linear Accelerator  Center
>[EMAIL PROTECTED]
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: MacGregor, Ian A.
>   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: Deborah Lorraine
  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: MacGregor, Ian A.
  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: Deborah Lorraine
 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-

RE: RMAN compatibility issues

[DENNIS WILLIAMS]

Rachna - Yes, RMAN is VERY picky about Oracle versions. What I understand is
that you are trying to use an 816 catalog to back up an 817 database. I
think that you usually must keep your catalog at the level of the target
database or higher. In other words, you could use an 817 catalog db to back
up an 816 target, but not the other way around.

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


-Original Message-
Sent: Wednesday, November 20, 2002 9:21 AM
To: Multiple recipients of list ORACLE-L


DBAs,

I experienced following while trying to test interoperatibility 
between 816 and 817.

Any comments, experiences?


Target -> 817 Catalog -> 816

rman rcvcat rman/rman@rcatqual
connect target

register database;

.
RMAN-06429: RCVCAT database is not compatible with this version of RMAN
.



Target -> 816 Catalog -> 817

rman rcvcat rman/rman@rcattest
connect target

register database;

.
RMAN-08006: database registered in recovery catalog
.


-Rachna
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachna Vaidya
  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: [Q] ORACLE 9i fast_start_mttr_target and log_checkpoint_inte

[Rachel Carmichael]

you are evil.  :)

as far as I know, mttr overrides log_checkpoint_interval if set. That's
what it says in the docs I've looked at 

I haven't had a chance to play around with it as yet though. 


--- "Fink, Dan" <[EMAIL PROTECTED]> wrote:
> 
> By all means, play around in their test database. See how good he is
> when it
> goes crashy-crashy...
> 
> 
> -Original Message-
> Sent: Tuesday, November 19, 2002 4:10 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> ok - it so happens I am in the middle of the 9i New Features class. I
> just
> clarified that with the instuctor. And he repeated that if the
> log_checkpotint parameters are set, as well as the MTTR, then MTTR
> overides
> whatever the log_checkpoint parameters are set to. 
> 
> 
> I did not check out the official documentation. But the class manual
> says, 
> 
> 
> 'Internally this is translated to a setting for
> Log_checkpoint_interval' 
> 
> 
> The instructor's name is Steve Bertuoi. Not sure how good he knows
> his
> stuff. 
> 
> 
> I guess I could play around with their test database here in the
> class, and
> see what really happens. 
> 
> 
> Gillian 
> 
> 
>  DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: 
> 
> 
> Gillian - But the documentation clearly says just the opposite. It
> says that
> if you set the LOG_CHECKPOINT_INTERVAL parameter, then that is what
> is used,
> rather than FAST_START_MTTR_TARGET. So I don't see how you can say
> that
> FAST_START_MTTR_TARGET overrides the LOG_CHECKPOINT_INTERVAL. Am I
> missing
> something here?
> 
> 
> Dennis Williams 
> DBA, 40%OCP 
> Lifetouch, Inc. 
> [EMAIL PROTECTED] 
> 
> -Original Message-
> Sent: Tuesday, November 19, 2002 12:59 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> Just to add: 
> 
> 
> The MTTR parameter overides the Log_checkpoint_interval and the
> ...timeout
> parameter. Oracle sets these log_checkpoint parameters dynamically
> when the
> MTTR parameter is set. 
> 
> 
> Gillian 
> 
> 
> DENNIS WILLIAMS wrote: 
> 
> 
> Dist -
> Since nobody seems to have responded,! ! I'll take a crack at it. 
> The Oracle9i documentation specifically says (in the Oracle9i
> Database
> Reference) that either FAST_START_IO_TARGET or
> LOG_CHECKPOINT_INTERVAL will
> override FAST_START_MTTR_TARGET. I would tend to believe them.
> In the Oracle9i Performance Tuning Guide, Oracle contains a note that
> you should disable or remove the FAST_START_IO_TARGET,
> LOG_CHECKPOINT_INTERVAL, and LOG_CHECKPOINT_TIMEOUT parameters when
> using
> FAST_START_MTTR_TARGET. Setting these parameters interferes with
> FAST_START_MTTR_TARGET. 
> I don't see where LOG_CHECKPOINTS_TO_ALERT would cause a problem, but
> if
> you are suspicious, you might check metalink.
> As I understand it, the big advantage of FAST_START_MTTR_TARGET is
> its
> ease and accuracy. Your management provides a goal for how long your
> Oracle
> database can take to reco! ! ver in the situation where it crashes.
> You
> simply
> convert that number to seconds and set FAST_START_MTTR_TARGET. With
> the
> ! ! LOG_CHECKPOINT parameters, the settings are derived more
> indirectly.
> Of course, there is no free lunch. Setting a shorter recovery time
> may
> interfere with performance.
> Dennis Williams
> DBA, 40%OCP
> Lifetouch, Inc.
> [EMAIL PROTECTED] 
> 
> 
> -Original Message-
> Sent: Monday, November 18, 2002 11:04 AM
> To: Multiple recipients of list ORACLE-L
> log_checkpoint_interval parameter?
> 
> 
> 
> 
> 
> I saw a document mention if log_checkpoint_interval define on ORACLE
> 9i,
> the fast_start_mttr_target parameter will be ignore. My question is:
> 
> 1. we have following on init.ora file:
> 
> log_checkpoint_interval = 1
> log_checkpoint_timeout = 1800
> log_checkpoints_to_alert = true
> 
> fast_start_mttr_target=300
> 
> Do I need remove all the log_checkpoint* on init.ora file?
> 
> 2. what kind of ! ! benefit that fast_start_mttr_target better than
> log_checkpoint* ?
> 
> 
> Thanks
> 
> 
> 
> __! ! ___
> The new MSN 8: smart spam protection and 2 months FREE* 
> http://join.msn.com/?page=features/junkmail
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: dist cash
> 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! ! ial ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: DENNIS WILLIAMS
> INET: [EMAIL PROTECTED]
> 
> Fat Ci! ! ty Network Services -- 8

RE: RMAN compatibility issues

[Mercadante, Thomas F]

Rachna,

you forgot one more test:

816 version of Rman against an 817 target database using an 816 version of
catalog - should work ok.

other than that, your test results are as expected.  you can use a lower
version of rman against a higher version of the catalog, but the opposite is
not true.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, November 20, 2002 10:21 AM
To: Multiple recipients of list ORACLE-L


DBAs,

I experienced following while trying to test interoperatibility 
between 816 and 817.

Any comments, experiences?


Target -> 817 Catalog -> 816

rman rcvcat rman/rman@rcatqual
connect target

register database;

.
RMAN-06429: RCVCAT database is not compatible with this version of RMAN
.



Target -> 816 Catalog -> 817

rman rcvcat rman/rman@rcattest
connect target

register database;

.
RMAN-08006: database registered in recovery catalog
.


-Rachna
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachna Vaidya
  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: Oracle-L at UKOUG

[Rachel Carmichael]

IIRC (and it's been at least 3 years), there was a restaurant/pub on
the "island" near the conference center that was nice. Large, decent
food and excellent beer

of course, if you just want the sugar/caffeine rush, you can always go
visit the Cadbury factory


--- Connor McDonald <[EMAIL PROTECTED]> wrote:
> I'm in - when you consider the wealth of nightlife in
> Birmingham (yeah right!) then I'm sure we can find
> somewhere to go :-)
> 
>  --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
> interested! It IS always nice to put names to
> > faces..
> > 
> > Any ideas for when/where?
> > 
> > Mark
> > 
> > -Original Message-
> > Mike (NESL-IT)
> > Sent: 20 November 2002 09:13
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > I'll be attending and it would be nice to put faces
> > to some of the names on
> > this list.
> >  
> > Mike
> > 
> > -Original Message-
> > Sent: 20 November 2002 07:28
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > 
> > I will be at UKOUG and so will be another of other
> > people of the Oaktable
> > network.
> > 
> >  
> > 
> > Anjo.
> > 
> >  
> > 
> > -Original Message-
> > Sent: Tuesday, November 19, 2002 9:19 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> >  
> > 
> > Anyone interested in an Oracle-L get together at
> > UKOUG for those of us poor
> > souls who were not able to attend Oracle World? OW
> > attendees welcome also!
> > 
> >  
> > 
> > Dan Fink
> > 
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Hately, Mike (NESL-IT)
> >   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: Mark Leith
> >   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). 
> 
> =
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
> 
> "GIVE a man a fish and he will eat for a day. But TEACH him how to
> fish, and...he will sit in a boat and drink beer all day"
> 
> __
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  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: Oracle-L at UKOUG

[Fink, Dan]

I will be arriving on Sunday and am open to a get together that evening.
For those who have attended previously, what are good times?

-Original Message-
Sent: Wednesday, November 20, 2002 8:50 AM
To: Multiple recipients of list ORACLE-L


IIRC (and it's been at least 3 years), there was a restaurant/pub on
the "island" near the conference center that was nice. Large, decent
food and excellent beer

of course, if you just want the sugar/caffeine rush, you can always go
visit the Cadbury factory


--- Connor McDonald <[EMAIL PROTECTED]> wrote:
> I'm in - when you consider the wealth of nightlife in
> Birmingham (yeah right!) then I'm sure we can find
> somewhere to go :-)
> 
>  --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
> interested! It IS always nice to put names to
> > faces..
> > 
> > Any ideas for when/where?
> > 
> > Mark
> > 
> > -Original Message-
> > Mike (NESL-IT)
> > Sent: 20 November 2002 09:13
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > I'll be attending and it would be nice to put faces
> > to some of the names on
> > this list.
> >  
> > Mike
> > 
> > -Original Message-
> > Sent: 20 November 2002 07:28
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > 
> > I will be at UKOUG and so will be another of other
> > people of the Oaktable
> > network.
> > 
> >  
> > 
> > Anjo.
> > 
> >  
> > 
> > -Original Message-
> > Sent: Tuesday, November 19, 2002 9:19 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> >  
> > 
> > Anyone interested in an Oracle-L get together at
> > UKOUG for those of us poor
> > souls who were not able to attend Oracle World? OW
> > attendees welcome also!
> > 
> >  
> > 
> > Dan Fink
> > 
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Hately, Mike (NESL-IT)
> >   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: Mark Leith
> >   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). 
> 
> =
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
> 
> "GIVE a man a fish and he will eat for a day. But TEACH him how to
> fish, and...he will sit in a boat and drink beer all day"
> 
> __
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: =?iso-8859-1?q?Connor=20McDonald?=
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- 858-538-5051 http://www.fatcity.com
> San Diego, California-- Mailing list and web hosting services
> -
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).


__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  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 m

RE: [Q] ORACLE 9i fast_start_mttr_target and log_checkpoint_inte

[Donahue, Adam]

 
Dennis Williams 
DBA, 40%OCP 

^^

How does one get to be a 40% OCP?  Is that like being a 40% expert?

Adam
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Donahue, Adam
  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: How to check/show long SQL statement?

[Connor McDonald]

v$sqltext
order by piece

hth
connor

 --- dist cash <[EMAIL PROTECTED]> wrote: > 
> 
> 
> I need check what is SQL statement runnig on ORACLE.
>  their have two view
> v$sql and v$sqlare.  The entry "sql_text  
> varchar2(1000)", it is too
> short to show SQL statement.  Some of the sql
> Statement we use > 3000
> characters.  Does their has way to check/show long
> SQL statement?
> 
> 
> Thanks.
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
_
> Add photos to your messages with MSN 8. Get 2 months
> FREE*. 
> http://join.msn.com/?page=features/featuredemail
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: dist cash
>   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). 

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

RE: How to check/show long SQL statement?

[Whittle Jerome Contr NCI]

Title: RE: How to check/show long SQL statement?






First find the ADDRESS from v$sqlarea. Then run the following:


select sql_text from v$sqltext

where address = '8758'

order by piece;


It's ugly but all there. 


Jerry Whittle

ACIFICS DBA

NCI Information Systems Inc.

[EMAIL PROTECTED]

618-622-4145


-Original Message-

From:   dist cash [SMTP:[EMAIL PROTECTED]]


I need check what is SQL statement runnig on ORACLE.  their have two view

v$sql and v$sqlare.  The entry "sql_text   varchar2(1000)", it is too

short to show SQL statement.  Some of the sql Statement we use > 3000

characters.  Does their has way to check/show long SQL statement?



Thanks.

RE: converting to the cost based optimizer

[Connor McDonald]

Good examples are SAP and Documentum which "migrated"
from RBO to CBO by dropping the index adjustment from
100 to 10 (thus making virtually any index look
fantastic, ie simulating rule)

Unlike them, I would recommend a little more
dilligence (as Tim's document sets out)

hth
connor

 --- "Sakthi , Raj" <[EMAIL PROTECTED]> wrote: >
Vivek,
> As John K Suggested, take a look at Tim's paper
> "The Search for Intelligent Life in the Cost-Based
> Optimizer". He discussed the impact of these
> settings.
> 
> http://www.evergreen-database.com/
> 
> Gaja also discusses these parameters and their
> possibly optimal values in his paper. You can find
> that in quest's web site.
> 
> HTH
> Cheers,
> RS
> 
> 
> --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote:
> > 
> > What would be an Optimal Reduced Value of
> > OPTIMIZER_INDEX_COST_ADJ 
> > to make it favour index searches ?
> > 
> > Default Value is 100.
> > 
> > Thanks
> > 
> > 
> > -Original Message-
> > Sent: Wednesday, November 20, 2002 4:54 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > One quick hack is to set the "optimizer_..."
> params
> > to
> > make indexes look super-wonderful, which then
> gives
> > you a rule-based "look and feel" with hopefully a
> > little smarter selection of indexes as a bonus
> > 
> > hth
> > connor
> > 
> >  --- VIVEK_SHARMA <[EMAIL PROTECTED]>
> wrote:
> > > 
> > > Consider using OUTLINES for Fixed Execution
> plans
> > > especially for Batch Queries 
> > > 
> > > This will prevent a Sudden Increment in
> Processing
> > > Time due to Optimizer choosing NON-Optimal paths
> > > 
> > > 
> > > -Original Message-
> > > Sent: Wednesday, November 20, 2002 3:20 AM
> > > To: Multiple recipients of list ORACLE-L
> > > 
> > > 
> > > We will be in the same process soon. 
> > > We'll have to consolidate over 100 instances to
> > > something between 40-60 instances and switch
> from
> > > RBO
> > > to CBO.
> > > 
> > > Any tips are welcome.
> > > 
> > > 
> > >  --- Steve McClure <[EMAIL PROTECTED]> a
> écrit
> > :
> > > >
> > > I am just starting to look at converting to the
> > cost
> > > > based optimizer, and am
> > > > hoping a few of you can share insights from
> > having
> > > > done so in the past.  Our
> > > > application is an oltp system  developed on
> > 7.3.4.
> > > 
> > > > We made liberal use of
> > > > +0 and other RBO "hints", and I am wondering
> if
> > > > these are going to cause us
> > > > troubles when switching to CBO.
> > > > 
> > > >   We have been on 8i since April of this year,
> > and
> > > I
> > > > am just now starting to
> > > > gather information on the inner workings of
> the
> > > CBO.
> > > >  I know it is where I
> > > > want to be, I am just looking to see how
> > > > painful/painless the transition
> > > > will be.
> > > > 
> > > > Steve McClure
> > > > 
> > > > -- 
> > > > Please see the official ORACLE-L FAQ:
> > > > http://www.orafaq.com
> > > > -- 
> > > > Author: Steve McClure
> > > >   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). 
> > > 
> > > =
> > > Stéphane Paquette
> > > DBA Oracle et DB2, consultant entrepôt de
> données
> > > Oracle and DB2 DBA, datawarehouse consultant
> > > [EMAIL PROTECTED]
> > > 
> > >
> >
>
__
> > > Lèche-vitrine ou lèche-écran ?
> > > magasinage.yahoo.ca
> > > -- 
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > -- 
> > > Author: =?iso-8859-1?q?paquette=20stephane?=
> > >   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: VIVEK_SHARMA
> > >   INET: [EMAIL PROTECTED]
> > >

RE: [Q] ORACLE 9i fast_start_mttr_target and log_checkpoint_inte

[DENNIS WILLIAMS]

Gillian - I love it! Dueling Instructors! Since you are still in class, why
don't you show him the manual and ask for clarification. Please check the
class notes on this. My copy is at home. Thanks.
 
Dennis Williams 
DBA, 40%OCP 
Lifetouch, Inc. 
[EMAIL PROTECTED] 

-Original Message-
Sent: Tuesday, November 19, 2002 5:10 PM
To: Multiple recipients of list ORACLE-L



ok - it so happens I am in the middle of the 9i New Features class. I just
clarified that with the instuctor. And he repeated that if the
log_checkpotint parameters are set, as well as the MTTR, then MTTR overides
whatever the log_checkpoint parameters are set to. 


I did not check out the official documentation. But the class manual says, 


'Internally this is translated to a setting for Log_checkpoint_interval' 


The instructor's name is Steve Bertuoi. Not sure how good he knows his
stuff. 


I guess I could play around with their test database here in the class, and
see what really happens. 


Gillian 


 DENNIS WILLIAMS <[EMAIL PROTECTED]> wrote: 


Gillian - But the documentation clearly says just the opposite. It says that
if you set the LOG_CHECKPOINT_INTERVAL parameter, then that is what is used,
rather than FAST_START_MTTR_TARGET. So I don't see how you can say that
FAST_START_MTTR_TARGET overrides the LOG_CHECKPOINT_INTERVAL. Am I missing
something here?


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

-Original Message-
Sent: Tuesday, November 19, 2002 12:59 PM
To: Multiple recipients of list ORACLE-L



Just to add: 


The MTTR parameter overides the Log_checkpoint_interval and the ...timeout
parameter. Oracle sets these log_checkpoint parameters dynamically when the
MTTR parameter is set. 


Gillian 


DENNIS WILLIAMS wrote: 


Dist -
Since nobody seems to have responded,! ! I'll take a crack at it. 
The Oracle9i documentation specifically says (in the Oracle9i Database
Reference) that either FAST_START_IO_TARGET or LOG_CHECKPOINT_INTERVAL will
override FAST_START_MTTR_TARGET. I would tend to believe them.
In the Oracle9i Performance Tuning Guide, Oracle contains a note that
you should disable or remove the FAST_START_IO_TARGET,
LOG_CHECKPOINT_INTERVAL, and LOG_CHECKPOINT_TIMEOUT parameters when using
FAST_START_MTTR_TARGET. Setting these parameters interferes with
FAST_START_MTTR_TARGET. 
I don't see where LOG_CHECKPOINTS_TO_ALERT would cause a problem, but if
you are suspicious, you might check metalink.
As I understand it, the big advantage of FAST_START_MTTR_TARGET is its
ease and accuracy. Your management provides a goal for how long your Oracle
database can take to reco! ! ver in the situation where it crashes. You
simply
convert that number to seconds and set FAST_START_MTTR_TARGET. With the
! ! LOG_CHECKPOINT parameters, the settings are derived more indirectly.
Of course, there is no free lunch. Setting a shorter recovery time may
interfere with performance.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 


-Original Message-
Sent: Monday, November 18, 2002 11:04 AM
To: Multiple recipients of list ORACLE-L
log_checkpoint_interval parameter?





I saw a document mention if log_checkpoint_interval define on ORACLE 9i,
the fast_start_mttr_target parameter will be ignore. My question is:

1. we have following on init.ora file:

log_checkpoint_interval = 1
log_checkpoint_timeout = 1800
log_checkpoints_to_alert = true

fast_start_mttr_target=300

Do I need remove all the log_checkpoint* on init.ora file?

2. what kind of ! ! benefit that fast_start_mttr_target better than
log_checkpoint* ?


Thanks



__! ! ___
The new MSN 8: smart spam protection and 2 months FREE* 
http://join.msn.com/?page=features/junkmail

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: dist cash
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! ! ial ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]

Fat Ci! ! ty 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 

Re: How to check/show long SQL statement?

[Sakthi , Raj]

v$sqlare..??!
Well,
You can check v$sqltext.

Cheers,
RS
--- dist cash <[EMAIL PROTECTED]> wrote:
> 
> 
> 
> I need check what is SQL statement runnig on ORACLE.
>  their have two view
> v$sql and v$sqlare.  The entry "sql_text  
> varchar2(1000)", it is too
> short to show SQL statement.  Some of the sql
> Statement we use > 3000
> characters.  Does their has way to check/show long
> SQL statement?
> 
> 
> Thanks.
> 
> 
> 
> 
> 
> 
> 
> 
> 
>
_
> Add photos to your messages with MSN 8. Get 2 months
> FREE*. 
> http://join.msn.com/?page=features/featuredemail
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: dist cash
>   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).


__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sakthi , Raj
  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: Anyone using Net appliance near store r100

[DENNIS WILLIAMS]

Joe - Based on my admittedly meager experience, I think it would work fine
for that purpose. If you use something like RMAN that doesn't interfere with
production, then speed doesn't matter.

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


-Original Message-
Sent: Tuesday, November 19, 2002 8:24 PM
To: Multiple recipients of list ORACLE-L


Dennis, not sure how they are hooking it in yet.  There are still alot 
of questions to be answered.  This was just a preliminary question asked 
if I knew anyone who was using it.  My understanding is they are going 
to use it for backup/recovery staging area before pushing off to tape.

Thanks, joe


DENNIS WILLIAMS wrote:

>Joe - We have a Net Appliance on our test system. It works well for that
>purpose, but I wouldn't use it for a critical production application. What
>are they planning to use it for? We have had ours for maybe a year - have
>they made some improvements since then? How are you planning to hook it in
-
>fiber?
>
>
>Dennis Williams 
>DBA, 40%OCP 
>Lifetouch, Inc. 
>[EMAIL PROTECTED]   
>
>-Original Message-
>Sent: Monday, November 18, 2002 8:49 AM
>To: Multiple recipients of list ORACLE-L
>
>
>I've got a new client thats just about sold on it but they are looking for
>third party verification of anyone using it and if it works as stated.
> 
>thanks, joe
> 
>
>  
>


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

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

[Yechiel Adar]

We just bought EMC Cellero.
I do not know the reasons, just that they won the contract.

Yechiel Adar
Mehish
- Original Message - 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Tuesday, November 19, 2002 8:49 PM


> Hi All,
> 
> We are considering the following 3 SAN storage devices.  If anyone can
> share any info on any I would appreciate it.
> They all have 2gig fibre channel.
> 
> Hitachi 9200
> EMC CX400
> HP EVA 2c2d
> 
> Thanks
> Rick
> 
> 
> -- 
> 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: 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).

RMAN compatibility issues

[Rachna Vaidya]

DBAs,

I experienced following while trying to test interoperatibility 
between 816 and 817.

Any comments, experiences?


Target -> 817 Catalog -> 816

rman rcvcat rman/rman@rcatqual
connect target

register database;

.
RMAN-06429: RCVCAT database is not compatible with this version of RMAN
.



Target -> 816 Catalog -> 817

rman rcvcat rman/rman@rcattest
connect target

register database;

.
RMAN-08006: database registered in recovery catalog
.


-Rachna
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachna Vaidya
  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: Running/Licensing Hyperthreaded Intel Pentium4 Xeon CPU's on

[Orr, Steve]

I was off yesterday and came in this morning anticipating lots of feedback
on this but now I'm surprised by the lack of response from all you seasoned
gurus.  :-) I guess this is a very new thing and you aren't aware of it yet.


Anyway, here's what I've garnered so far:  

There was a discussion on slashdot where one guy said Oracle was wanting to
charge licenses on the virtual CPU's instead of the actual number of
physical CPU's. Then someone else said he had checked with Oracle for a
definitive answer BEFORE implementing it because it was a make or break deal
for him. He said Oracle was only going to charge for actual physical CPU's
and not the virtual hyperthreaded CPU's so he went ahead with his
implementation and says he got a 30% performance boost. Not the same
performance boost as would be expected from adding real CPU's but
significant nonetheless. He said it was a 30% "free" boost because he didn't
have to buy anything; all he had to do was reconfigure Linux. In another
discussion on the SUSE/Oracle list it was stated that the performance boost
is about 20% of what you would expect from adding a real processor and that
Oracle once again said the license charge would be by virtual processor and
not the actual number of CPU's in the server. It was also stated that the
max number of processors for the standard edition is 4 as decreed by his
Oracle highness so with hyperthreading you could only have 2 real CPU's.
Finally, I only got 3 hits on Metalink where Oracle support drones said you
didn't have to do anything Oracle-wise to adjust for doubling the number of
O/S visible CPU's. They did not respond to init.ora tuning questions and
they refered us to our Oracle sales drones for licensing questions as they
were clueless in Seatle. 

As usual there is controversy regarding Oracle's license confusion. Is there
anything in the licensing legalese which specifically mentions licensing on
virtual or multithreaded CPU's? Regardless, seems to me we need to have a
Boston Tea Party type rebellion and refuse to pay for licenses on virtual
CPU's. 


With righteous indignation and virtuous rebellion,
Steve Orr
Bozeman, Montana



-Original Message-
Sent: Wednesday, November 20, 2002 2:23 AM
To: Multiple recipients of list ORACLE-L
on Linu


Orr, Steve,
I am using Xeon MP as well on my DELL 6650 , and i have two
nodes. I will patch my database next evening and later turn on the logical
cpu feature on one node and leave the other node as it is.
I will compare the cpu load/application responce time change and
give you the feedback.
Good luck

Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(Chinese Oracle User Group)

=== 2002-11-18 14:25:00 ,you wrote£º===

>OK, I've got this new Dell Linux server with 4 "Hyperthreaded" Pentium4
Xeon
>CPU's. There are 4 physical CPU's but with hyperthreading the O/S sees 8
>CPU's as is reported in top. I've installed Oracle on this machine and it
>seems to run as if there really were 8 CPU's. Could that be true? Is there
a
>performance gain for Oracle? --As if I really did have 8 CPU's? How does
>this work? For init.ora tuning parameters should I count the 4 physical
>CPU's or the 8 virtual CPU's? Any reports on running Oracle/Linux on these
>hyperthreaded Pentium4 Xeon CPU's? 
>
>I'm afraid to ask Oracle about the licensing since they always answer with
>the higher number. ;-) 
>
>
>Steve Orr
>Bozeman, Montana
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Orr, Steve
>  INET: [EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
  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: Oracle-L at UKOUG

[Mark Leith]

You guys must be looking in ALL the wrong places!! ;)

-Original Message-
Robert
Sent: 20 November 2002 14:04
To: Multiple recipients of list ORACLE-L


>> I'm in - when you consider the wealth of nightlife in Birmingham ...

LOL you must have been in a different Birmingham than I was Connor! :-))
Certainly not the nightlife of London, to be sure.

The Christmas fair (or whatever it's called) was pretty nice last year.

RF


Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!

Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of you.

 



-Original Message-
Sent: Wednesday, November 20, 2002 6:19 AM
To: Multiple recipients of list ORACLE-L


I'm in - when you consider the wealth of nightlife in
Birmingham (yeah right!) then I'm sure we can find
somewhere to go :-)

 --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
interested! It IS always nice to put names to
> faces..
>
> Any ideas for when/where?
>
> Mark
>
> -Original Message-
> Mike (NESL-IT)
> Sent: 20 November 2002 09:13
> To: Multiple recipients of list ORACLE-L
>
>
> I'll be attending and it would be nice to put faces
> to some of the names on
> this list.
>
> Mike
>
> -Original Message-
> Sent: 20 November 2002 07:28
> To: Multiple recipients of list ORACLE-L
>
>
>
> I will be at UKOUG and so will be another of other
> people of the Oaktable
> network.
>
>
>
> Anjo.
>
>
>
> -Original Message-
> Sent: Tuesday, November 19, 2002 9:19 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Anyone interested in an Oracle-L get together at
> UKOUG for those of us poor
> souls who were not able to attend Oracle World? OW
> attendees welcome also!
>
>
>
> Dan Fink
>
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Hately, Mike (NESL-IT)
>   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: Mark Leith
>   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).

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

Re: opinions on SAN devices for Oracle

[Yechiel Adar]

We just bought EMC Cellero.
I do not know the reasons, just that they won the contract.

Yechiel Adar
Mehish
- Original Message - 
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Tuesday, November 19, 2002 8:49 PM


> Hi All,
> 
> We are considering the following 3 SAN storage devices.  If anyone can
> share any info on any I would appreciate it.
> They all have 2gig fibre channel.
> 
> Hitachi 9200
> EMC CX400
> HP EVA 2c2d
> 
> Thanks
> Rick
> 
> 
> -- 
> 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: 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: How to check/show long SQL statement?

[Dennis M. Heisler]

Use v$sqltext.  
  select sql_text from v$sqltext where address = xxx order by piece;



dist cash wrote:
> 
> I need check what is SQL statement runnig on ORACLE.  their have two view
> v$sql and v$sqlare.  The entry "sql_text   varchar2(1000)", it is too
> short to show SQL statement.  Some of the sql Statement we use > 3000
> characters.  Does their has way to check/show long SQL statement?
> 
> Thanks.
> 
> _
> Add photos to your messages with MSN 8. Get 2 months FREE*.
> http://join.msn.com/?page=features/featuredemail
> 
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: dist cash
>   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 M. Heisler
  INET: [EMAIL PROTECTED]

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

Oracle 7.3.4 on MS2000

[Smith, Ron L.]

I am trying to install Oracle 7.3.4 on a MS2000 machine.  I need the loader
utilities to run against an old instance of Oracle Financials.  I am getting
a "obackup.vrf OS_ERROR".  I have done this before and I did not get the
error.  Can anyone help?

R. Smith
If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited.  Please let me know
immediately by return e-mail if you have received this message by mistake,
then delete the e-mail message. Thank you.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
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: converting to the cost based optimizer

[Sakthi , Raj]

Vivek,
As John K Suggested, take a look at Tim's paper
"The Search for Intelligent Life in the Cost-Based
Optimizer". He discussed the impact of these settings.

http://www.evergreen-database.com/

Gaja also discusses these parameters and their
possibly optimal values in his paper. You can find
that in quest's web site.

HTH
Cheers,
RS


--- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote:
> 
> What would be an Optimal Reduced Value of
> OPTIMIZER_INDEX_COST_ADJ 
> to make it favour index searches ?
> 
> Default Value is 100.
> 
> Thanks
> 
> 
> -Original Message-
> Sent: Wednesday, November 20, 2002 4:54 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> One quick hack is to set the "optimizer_..." params
> to
> make indexes look super-wonderful, which then gives
> you a rule-based "look and feel" with hopefully a
> little smarter selection of indexes as a bonus
> 
> hth
> connor
> 
>  --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote:
> > 
> > Consider using OUTLINES for Fixed Execution plans
> > especially for Batch Queries 
> > 
> > This will prevent a Sudden Increment in Processing
> > Time due to Optimizer choosing NON-Optimal paths
> > 
> > 
> > -Original Message-
> > Sent: Wednesday, November 20, 2002 3:20 AM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > We will be in the same process soon. 
> > We'll have to consolidate over 100 instances to
> > something between 40-60 instances and switch from
> > RBO
> > to CBO.
> > 
> > Any tips are welcome.
> > 
> > 
> >  --- Steve McClure <[EMAIL PROTECTED]> a écrit
> :
> > >
> > I am just starting to look at converting to the
> cost
> > > based optimizer, and am
> > > hoping a few of you can share insights from
> having
> > > done so in the past.  Our
> > > application is an oltp system  developed on
> 7.3.4.
> > 
> > > We made liberal use of
> > > +0 and other RBO "hints", and I am wondering if
> > > these are going to cause us
> > > troubles when switching to CBO.
> > > 
> > >   We have been on 8i since April of this year,
> and
> > I
> > > am just now starting to
> > > gather information on the inner workings of the
> > CBO.
> > >  I know it is where I
> > > want to be, I am just looking to see how
> > > painful/painless the transition
> > > will be.
> > > 
> > > Steve McClure
> > > 
> > > -- 
> > > Please see the official ORACLE-L FAQ:
> > > http://www.orafaq.com
> > > -- 
> > > Author: Steve McClure
> > >   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). 
> > 
> > =
> > Stéphane Paquette
> > DBA Oracle et DB2, consultant entrepôt de données
> > Oracle and DB2 DBA, datawarehouse consultant
> > [EMAIL PROTECTED]
> > 
> >
>
__
> > Lèche-vitrine ou lèche-écran ?
> > magasinage.yahoo.ca
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: =?iso-8859-1?q?paquette=20stephane?=
> >   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: VIVEK_SHARMA
> >   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). 
> 
> =
> Connor McDonald
> http://www.oracledba.co.uk
> http://www.oaktable.net
> 
> "GIVE a m

Re: to_date function and NLS settings on client

[Igor Neyman]

Title: to_date function and NLS settings on client



Helmut,
 
your format is wrong, it should be:
 
to_date('01.10.1950','DD.MM.')
 
Igor Neyman, OCP DBA[EMAIL PROTECTED]  
 
 

  - Original Message - 
  From: 
  Daiminger, Helmut 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Wednesday, November 20, 2002 8:03 
  AM
  Subject: to_date function and NLS 
  settings on client
  
  Hello! 
  We have several Windows clients connecting to our db-server. 
  Every client issues the statement: 
  select  from thabe where date = 
  to_date('01.10.1950','DD-MM-'); 
  Some clients return the correct number of rows where as others 
  return nothing. 
  I know that this has something to to with the NLS settings on 
  the client. 
  How do I find out the NLS settings on the client? Just in the 
  registry? 
  How coem this does not work although I explicity specify a 
  format mask? 
  This is 8.1.7 on Solaris. 
  Thanks, Helmut 

SV: to_date function and NLS settings on client

[Johan Malmberg]

Title: to_date function and NLS settings on client




>How coem this does not work although I 
explicity specify a format mask? 
Maybe 
because the mask is wrong... ;)
try:
to_date('01.10.1950','DD.MM.')  
instead of:
 to_date('01.10.1950','DD-MM-'); 
or the 
other way around!
/Johan
 

-Ursprungligt meddelande-Från: 
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]För Daiminger, 
HelmutSkickat: den 20 november 2002 14:04Till: Multiple 
recipients of list ORACLE-LÄmne: to_date function and NLS settings on 
client

  Hello! 
  We have several Windows clients connecting to our db-server. 
  Every client issues the statement: 
  select  from thabe where date = 
  to_date('01.10.1950','DD-MM-'); 
  Some clients return the correct number of rows where as others 
  return nothing. 
  I know that this has something to to with the NLS settings on 
  the client. 
  How do I find out the NLS settings on the client? Just in the 
  registry? 
  How coem this does not work although I explicity specify a 
  format mask? 
  This is 8.1.7 on Solaris. 
  Thanks, Helmut 

RE: AIX vs Solaris

[Rich Holland]

http://www.google.com/
and search for: vmtune depth queue

 

Rich

 



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of VIVEK_SHARMA
Sent: Wednesday, November 20, 2002
12:40 AM
To: Multiple recipients of list
ORACLE-L
Subject: RE: AIX vs Solaris

 



 





Could you give Some Detail of Tuning the
Depth Queue ?





 





Our Configuration :-





Solaris Box Connected to XP512 HP
Storage (Hitachi packaged) with VXFS Filesystems 





 





Thanks





 





-Original Message-
From: Rich Holland [mailto:[EMAIL PROTECTED]]
Sent: Sunday, November 17, 2002
7:49 PM
To: Multiple recipients of list
ORACLE-L
Subject: RE: AIX vs Solaris

John,

 

You should be able to tune the SCSI device
queue depth; I’ve done this on HP-UX and Solaris systems, but
haven’t had to under AIX yet.  I don’t have an AIX system
handy to verify this, but I’m 90% sure you can do it.  AIX also
supports async I/O where HP-UX doesn’t (not sure about Sun), which is a
big win in an Oracle environment.

 

Rich Holland

Guidance Technologies, Inc.

 



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of John
Shaw
Sent: Wednesday, November 13, 2002
3:06 PM
To: Multiple recipients of list
ORACLE-L
Subject: Re: AIX vs Solaris

 

We had an aix box and ended sending
it back and going with sun - only because we had a hitachi san and there seems
to be a bug between ibm and hitachi - something about depth queue.

>>> [EMAIL PROTECTED] 11/13/02 12:48PM >>>

Hello,

What are the major differences between AIX and Solaris regarding
operating system features?

We are planning a new machine purchase; currently we are on a Sun machine
running Solaris, but IBM is making a strong proposal to management
(meaning significantly less cost), and we are wondering what would need to be
changed. We use korn shell scripts extensively, and features such as
crontab, background processing, the sqlplus <
sure what this is called). I'm fairly sure these are standard in most
flavors of unix, but I have never had contact with AIX. Does anyone know
what features differ between the two OS's?

If we went with Solaris, we would go with Solaris 9 running Oracle 9.2
on a Sun 4800.

Thanks for any responders.

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

How to check/show long SQL statement?

[dist cash]

I need check what is SQL statement runnig on ORACLE.  their have two view
v$sql and v$sqlare.  The entry "sql_text   varchar2(1000)", it is too
short to show SQL statement.  Some of the sql Statement we use > 3000
characters.  Does their has way to check/show long SQL statement?


Thanks.









_
Add photos to your messages with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: dist cash
 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: Oracle-L at UKOUG

[Freeman, Robert]

>> I'm in - when you consider the wealth of nightlife in Birmingham ...

LOL you must have been in a different Birmingham than I was Connor! :-))
Certainly not the nightlife of London, to be sure.

The Christmas fair (or whatever it's called) was pretty nice last year.

RF


Robert G. Freeman - Oracle OCP
Oracle Database Architect
CSX Midtier Database Administration
Author of several Oracle books you can find on Amazon.com!

Londo Mollari: Ah, arrogance and stupidity all in the same package. How
efficient of you. 

 



-Original Message-
Sent: Wednesday, November 20, 2002 6:19 AM
To: Multiple recipients of list ORACLE-L


I'm in - when you consider the wealth of nightlife in
Birmingham (yeah right!) then I'm sure we can find
somewhere to go :-)

 --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
interested! It IS always nice to put names to
> faces..
> 
> Any ideas for when/where?
> 
> Mark
> 
> -Original Message-
> Mike (NESL-IT)
> Sent: 20 November 2002 09:13
> To: Multiple recipients of list ORACLE-L
> 
> 
> I'll be attending and it would be nice to put faces
> to some of the names on
> this list.
>  
> Mike
> 
> -Original Message-
> Sent: 20 November 2002 07:28
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> I will be at UKOUG and so will be another of other
> people of the Oaktable
> network.
> 
>  
> 
> Anjo.
> 
>  
> 
> -Original Message-
> Sent: Tuesday, November 19, 2002 9:19 PM
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> Anyone interested in an Oracle-L get together at
> UKOUG for those of us poor
> souls who were not able to attend Oracle World? OW
> attendees welcome also!
> 
>  
> 
> Dan Fink
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Hately, Mike (NESL-IT)
>   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: Mark Leith
>   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). 

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

[Jeffery Stevenson]

Charlie,
Cary provided some really good info on some things to look at, but I'm
going on a really wild hunch here (well, okay, so I actually have a few
different thoughts on it and not just one hunch)...so some further
information would be needed.

INVENTORY_LOCATION sounds like a good associative table between
INVENTORY and LOCATION (for keeping tabs on bin locations).  If this is the
case, what are the primary key columns (and how are they defined...char,
varchar, number, number(), etc) for all three of these tables?  Do
INVENTORY or LOCATION have concatenated primary keys (and do those inherit
foreign key fields as part of the primary key from other tables)...or do the
tables use sequences for a mock primary key?  Also, does INVENTORY_LOCATION
have any child tables that have a foreign key reference to it?

Jeffery Stevenson
Chief Databeast Slayer
Medical Present Value, Inc.

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Tuesday, November 19, 2002 8:54 AM


>
> I'm search of perfection in an imperfect world.
> This problem involves a V7.3.4.5 DB on V2.6 Solaris.
>
> As part of our nightwork batch processing, a bunch of reports are run
> against the DB.
> I have a DBMS_JOB which reports when processes are waiting for events like
> "db file sequential read". I typically get an email showing about 2 dozen
> sessions
> with wait times of 3 - 15 seconds all of which are doing one block reads
> against
> P_INVLOC; which is the Primary Key for the Inventory_Location table. This
> index
> has INITRANS=31 & FREELISTS=31. This is a decent sized index at about
> 128MB.
>
> I'm open for suggestion WRT what else can & could be done to eliminate or
> reduce these waits.
>
>
> --
> 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: Jeffery Stevenson
  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: converting to the cost based optimizer

[VIVEK_SHARMA]

What would be an Optimal Reduced Value of OPTIMIZER_INDEX_COST_ADJ 
to make it favour index searches ?

Default Value is 100.

Thanks


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


One quick hack is to set the "optimizer_..." params to
make indexes look super-wonderful, which then gives
you a rule-based "look and feel" with hopefully a
little smarter selection of indexes as a bonus

hth
connor

 --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote: > 
> Consider using OUTLINES for Fixed Execution plans
> especially for Batch Queries 
> 
> This will prevent a Sudden Increment in Processing
> Time due to Optimizer choosing NON-Optimal paths
> 
> 
> -Original Message-
> Sent: Wednesday, November 20, 2002 3:20 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> We will be in the same process soon. 
> We'll have to consolidate over 100 instances to
> something between 40-60 instances and switch from
> RBO
> to CBO.
> 
> Any tips are welcome.
> 
> 
>  --- Steve McClure <[EMAIL PROTECTED]> a écrit :
> >
> I am just starting to look at converting to the cost
> > based optimizer, and am
> > hoping a few of you can share insights from having
> > done so in the past.  Our
> > application is an oltp system  developed on 7.3.4.
> 
> > We made liberal use of
> > +0 and other RBO "hints", and I am wondering if
> > these are going to cause us
> > troubles when switching to CBO.
> > 
> >   We have been on 8i since April of this year, and
> I
> > am just now starting to
> > gather information on the inner workings of the
> CBO.
> >  I know it is where I
> > want to be, I am just looking to see how
> > painful/painless the transition
> > will be.
> > 
> > Steve McClure
> > 
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Steve McClure
> >   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). 
> 
> =
> Stéphane Paquette
> DBA Oracle et DB2, consultant entrepôt de données
> Oracle and DB2 DBA, datawarehouse consultant
> [EMAIL PROTECTED]
> 
>
__
> Lèche-vitrine ou lèche-écran ?
> magasinage.yahoo.ca
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: =?iso-8859-1?q?paquette=20stephane?=
>   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: VIVEK_SHARMA
>   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). 

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED]

RE: Oracle-L at UKOUG

[Hately, Mike (NESL-IT)]

It'd be good to grab something to eat and wind down after the 'rigours' of
the day. 
Needn't be anything flash as I'll probably be in more dire need of a beer
than anything else.

Mike

-Original Message-
Sent: 20 November 2002 12:19
To: Multiple recipients of list ORACLE-L


I would suggest anywhere along Broad Street (opposite the "front" of the
ICC), or anywhere in Brindly Place (across the canal at the "back" of the
ICC). There are numerous bars/cafes/restaurants around the Broad Street
area..

I suppose the question is - do we want to have a meal, or just get together
for a beer?

M.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hately, Mike (NESL-IT)
  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: db file sequential read

[VIVEK_SHARMA]

Yes , did some small benchmarks of a Batch process running for a Few minutes & Loading 
the CPUs by 50 %

Database Exists on Veritas Filesystems (VXFS) 

truss of the process Both application & Database Calls made 

Run with Value of vxio:vol_maxio = 512 ( = 256 K [Default] )
-
Functions with the MAX Number of "calls"  & sys time in "seconds" only shown below .


syscall  seconds   calls  errors
read   23.19  355514
write  23.11  323662
brk 3.70   39354
lseek   1.13   32078
llseek  5.52  217075113
pread64 6.62  100393

NOTE - "100393" Calls made by system Function "pread64" Corresponds to "db file 
sequential read" of Oracle 

Run - with Value of vxio:vol_maxio = 16384 ( = 8M ) - repeated the Run 
---

syscall  seconds   calls  errors
read   24.05  355613
write  24.05  324018
brk 4.09   40657
lseek   1.23   32077
llseek  6.05  217076113
pread64 6.16   96573

"96573" Calls made by system Function "pread64" which are less than "100393"

You can also Check metalink for relavence of this parameter for reducing "pread64" 
system function call

HTH

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


VIVEK_SHARMA,
Hi, did you have benchmark result or other whitepaper talking about 
this thoery?
For db file sequential read, it is single block read to index and then 
to table, so i think enlarge the maxio size of the os won't help?(I just guess, did 
not test it).
Enableing AIO or using raw device maybe will help io performance, thus 
give reduced io wait, i think.
Good luck


Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(Chinese Oracle User Group)

=== 2002-11-19 23:34:00 ,you wrote£º===

>For "db file sequential read wait" Add Following to /etc/system
>On UFS 
>Setting maxphys=8388608 
>On VXFS
>set vxio:vol_maxio = 16384 ( implies 8MB )
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: VIVEK_SHARMA
  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: Oracle-L at UKOUG

[Mark Leith]

I would suggest anywhere along Broad Street (opposite the "front" of the
ICC), or anywhere in Brindly Place (across the canal at the "back" of the
ICC). There are numerous bars/cafes/restaurants around the Broad Street
area..

I suppose the question is - do we want to have a meal, or just get together
for a beer?

M.

-Original Message-
Mike (NESL-IT)
Sent: 20 November 2002 11:48
To: Multiple recipients of list ORACLE-L


Connor,
I see you're presenting this year. I'll try to catch those sessions.
Assuming that you know the format of the event better than I do (not
difficult) have you any suggestions for good meeting places/times?

Mike

-Original Message-
Sent: 20 November 2002 11:19
To: Multiple recipients of list ORACLE-L


I'm in - when you consider the wealth of nightlife in
Birmingham (yeah right!) then I'm sure we can find
somewhere to go :-)

 --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
interested! It IS always nice to put names to
> faces..
>
> Any ideas for when/where?
>
> Mark
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hately, Mike (NESL-IT)
  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: Mark Leith
  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).

to_date function and NLS settings on client

[Daiminger, Helmut]

Title: to_date function and NLS settings on client





Hello!


We have several Windows clients connecting to our db-server. Every client issues the statement:


select  from thabe where date = to_date('01.10.1950','DD-MM-');


Some clients return the correct number of rows where as others return nothing.


I know that this has something to to with the NLS settings on the client.


How do I find out the NLS settings on the client? Just in the registry?


How coem this does not work although I explicity specify a format mask?


This is 8.1.7 on Solaris.


Thanks,
Helmut

RE: SQL puzzle - using where (a, b, c) in select (a, b, c from...

[Naveen Nahata]

Title: RE: SQL puzzle - using where (a, b, c) in select (a, b, c from...) an



Jacques, 
 
Why not use some suffieintly random and crap value lets say 
'~~~CRAP~~~' to replace nulls in NVL or DECODE for the query to work 
with Varchar columns? I think one can be reasonably sure that such a value will 
never be inserted into the column :-)
 
Otherwise, I don't think there is any other solution.
 
Regards
Naveen

  -Original Message-From: Jacques Kilchoer 
  [mailto:[EMAIL PROTECTED]]Sent: Wednesday, November 20, 
  2002 1:34 AMTo: Multiple recipients of list 
  ORACLE-LSubject: RE: SQL puzzle - using where (a, b, c) in select 
  (a, b, c from...
  Thank you for your answer. 
  Your answer would solve my problem, except for the fact that i 
  want a general purpose solution that would work in any table. What if the 
  three columns in the table were varchar2?
  -Original Message- From: 
  Naveen Nahata [mailto:[EMAIL PROTECTED]] 
  Sent: lundi, 18. novembre 2002 21:59 To: Multiple recipients of list ORACLE-L Subject: RE: SQL puzzle - using where (a, b, c) in select (a, b, c 
  from...) an 
  Since Id, Cost and Sell are all NUMBERs, so they cannot 
  contain CHARs, which makes a perfect case for decode. You can use CHARs to 
  substitute for NULLs in DECODE.
   Following is the query I 
  wrote:   DELETE FROM 
  Widgets_Copy a WHERE (DECODE(a.Id, NULL, 'X', a.Id), 
  DECODE(a.Cost, NULL, 'X', a.Cost), DECODE(a.Sell, NULL, 'X', a.Sell)) 
  IN     (     SELECT DECODE(b.Id, NULL, 
  'X', b.Id), DECODE(b.Cost, NULL, 'X', b.Cost), DECODE(b.Sell, NULL, 'X', 
  b.Sell)     
  FROM Widgets b     ) AND a.Rowid !=    (     
  SELECT MIN(c.Rowid) FROM Widgets_Copy c     
  WHERE (    DECODE(c.Id, NULL, 'X', c.Id),     
  DECODE(c.Cost, NULL, 'X', c.Cost),     
  DECODE(c.Sell, NULL, 'X', c.Sell)     
  ) IN     
  ( SELECT DECODE(d.Id, NULL, 'X', d.Id),  
  DECODE(d.Cost, NULL, 'X', d.Cost),  
  DECODE(d.Sell, NULL, 'X', d.Sell) FROM Widgets d)     
  AND DECODE(c.Id, NULL, 'X', c.Id) = DECODE(a.Id, NULL, 'X', a.Id) 
      
  AND DECODE(c.Cost, NULL, 'X', c.Cost) = DECODE(a.Cost, NULL, 'X', 
  a.Cost)     
  AND DECODE(c.Sell, NULL, 'X', c.Sell) = DECODE(a.Sell, NULL, 'X', 
  a.Sell)     
  );   SQL> select id, 
  cost, sell from widgets order by 1, 2, 3 ;       
  ID   
  COST   SELL -- -- --  
  1 
  10 20  
  1 10  1   SQL> select 'ROW' || to_char (rownum) 
  as row_num,   
  2   id, cost, 
  sell   3    from 
  widgets_copy   4    order by 2, 3, 
  4   5  /   ROW_NUM 
  ID   
  COST   SELL --- -- -- 
  -- ROW3 
  1 
  10 20 ROW6 
  1 
  10 20 ROW7 
  1 
  10 30 ROW8 
  1 
  10 30 ROW2 
  1 10 ROW5 
  1 10 ROW1 
  1 ROW4 
  1   8 rows 
  selected.   SQL> 
  DELETE FROM Widgets_Copy a   2  WHERE 
  (DECODE(a.Id, NULL, 'X', a.Id), DECODE(a.Cost, NULL, 'X', a.Cost), 
  DECODE(a.Sell, NULL, 'X', a.Sell)) IN   
  3  (   
  4  SELECT DECODE(b.Id, 
  NULL, 'X', b.Id), DECODE(b.Cost, NULL, 'X', b.Cost), DECODE(b.Sell, NULL, 'X', 
  b.Sell) 
    5  
  FROM Widgets b   6  
  )   7  AND a.Rowid !=    
  (   
  8  
  SELECT MIN(c.Rowid) FROM Widgets_Copy c   
  9  
  WHERE (    DECODE(c.Id, NULL, 'X', c.Id),  10  
  DECODE(c.Cost, NULL, 'X', c.Cost),  11  
  DECODE(c.Sell, NULL, 'X', c.Sell)  12  
  ) IN  13  
  ( SELECT DECODE(d.Id, NULL, 'X', d.Id),  14   
  DECODE(d.Cost, NULL, 'X', d.Cost),  15   
  DECODE(d.Sell, NULL, 'X', d.Sell) FROM Widgets d)  16  
  AND DECODE(c.Id, NULL, 'X', c.Id) = DECODE(a.Id, NULL, 'X', a.Id) 
   17  
  AND DECODE(c.Cost, NULL, 'X', c.Cost) = DECODE(a.Cost, NULL, 'X', 
  a.Cost)  18  
  AND DECODE(c.Sell, NULL, 'X', c.Sell) = DECODE(a.Sell, NULL, 'X', 
  a.Sell)  19  
  );   3 rows 
  

RE: Negative value for Consistent gets etc. in V$Sesstat

[Anjo Kolk]

When the max value of the 4 bytes or 8 bytes have been reached the
values may become negative, if oracle keeps on adding to them.

Anjo.

-Original Message-
Nahata
Sent: Wednesday, November 20, 2002 11:34 AM
To: Multiple recipients of list ORACLE-L

Thanx Anjo,

Can you elaborate on 'values may wrap'?

Regards
Naveen

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


1) values may wrap (and there are some obscure bugs that cause negative 
values)

2) You want to get the block in mode CR and there is actually no work
needed 
(like cleanout or rollback) to get to that mode.

Anjo.

On Tuesday 19 November 2002 21:34, you wrote:
> Hi All,
>
> There is one report which takes 12 hours to run during the off hours.
And
> that too on a database 6Gb in size! The report was designed by the
> consultants and all the queries in the report were doing a nested
loops
> joins on many tables, optimizer mode was RULE. After changing the
optimizer
> mode to CHOOSE, still no effect. Then I rewrote the report to use
joins
> instead of 'SELECT a row, run query for that row, then select another
row,
> run query for that row..'(written in Oracle Reports) the run time
came
> crashing down to 10 Secs. (Hard to believe!!).
>
> Now for the question.
>
> After running the report for a few hours I terminate the report and
see the
> values in V$sesstat. It is showing me negative values for Consistent
gets
> etc When the report was running i ran the same query on v$sesstat,
at
> that moment it was showing Consistent gets - 47 million approx.
>
> 1. Why is it showing negative values?
> 2. What does stat 'no work - consistent read gets' mean?
>
> OUTPUT ONE HOUR AFTER THE REPORT STARTED:
> -
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16
AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME VALUE
> --- --
> session connect time 478385736
> process last non-idle time   478385736
> consistent gets   47024111
> session logical reads 47024106
> no work - consistent read gets36724753
> buffer is not pinned count36650911
> table fetch by rowid  36643847
> buffer is pinned count36569847
> session pga memory 1651312
> session pga memory max 1651312
> session uga memory 1589476
> session uga memory max 1589476
> bytes sent via SQL*Net to client156588
> CPU used when call started   81035
> CPU used by this session 81035
> bytes received via SQL*Net from client   48012
> sorts (rows) 16390
> table fetch continued row 6650
> SQL*Net roundtrips to/from client 2043
> user calls2033
> execute count  788
> calls to get snapshot scn: kcmgss  786
> parse count (total) 17
> opened cursors cumulative   12
> table scan blocks gotten11
> recursive calls  9
> parse count (hard)   9
> db block gets9
> opened cursors current   6
> enqueue requests 5
> enqueue releases 5
> cursor authentications   5
> parse time elapsed   4
> table scans (short tables)   3
> parse time cpu   2
> logons cumulative1
> sorts (memory)   1
> logons current   1
>
> 38 rows selected.
>
> STATS 10 HOURS AFTER THE REPORT STARTED RUNNING
> 

RE: Oracle-L at UKOUG

[Hately, Mike (NESL-IT)]

Connor,
I see you're presenting this year. I'll try to catch those sessions.
Assuming that you know the format of the event better than I do (not
difficult) have you any suggestions for good meeting places/times?

Mike

-Original Message-
Sent: 20 November 2002 11:19
To: Multiple recipients of list ORACLE-L


I'm in - when you consider the wealth of nightlife in
Birmingham (yeah right!) then I'm sure we can find
somewhere to go :-)

 --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
interested! It IS always nice to put names to
> faces..
> 
> Any ideas for when/where?
> 
> Mark
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hately, Mike (NESL-IT)
  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: converting to the cost based optimizer

[Connor McDonald]

One quick hack is to set the "optimizer_..." params to
make indexes look super-wonderful, which then gives
you a rule-based "look and feel" with hopefully a
little smarter selection of indexes as a bonus

hth
connor

 --- VIVEK_SHARMA <[EMAIL PROTECTED]> wrote: > 
> Consider using OUTLINES for Fixed Execution plans
> especially for Batch Queries 
> 
> This will prevent a Sudden Increment in Processing
> Time due to Optimizer choosing NON-Optimal paths
> 
> 
> -Original Message-
> Sent: Wednesday, November 20, 2002 3:20 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> We will be in the same process soon. 
> We'll have to consolidate over 100 instances to
> something between 40-60 instances and switch from
> RBO
> to CBO.
> 
> Any tips are welcome.
> 
> 
>  --- Steve McClure <[EMAIL PROTECTED]> a écrit :
> >
> I am just starting to look at converting to the cost
> > based optimizer, and am
> > hoping a few of you can share insights from having
> > done so in the past.  Our
> > application is an oltp system  developed on 7.3.4.
> 
> > We made liberal use of
> > +0 and other RBO "hints", and I am wondering if
> > these are going to cause us
> > troubles when switching to CBO.
> > 
> >   We have been on 8i since April of this year, and
> I
> > am just now starting to
> > gather information on the inner workings of the
> CBO.
> >  I know it is where I
> > want to be, I am just looking to see how
> > painful/painless the transition
> > will be.
> > 
> > Steve McClure
> > 
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Steve McClure
> >   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). 
> 
> =
> Stéphane Paquette
> DBA Oracle et DB2, consultant entrepôt de données
> Oracle and DB2 DBA, datawarehouse consultant
> [EMAIL PROTECTED]
> 
>
__
> Lèche-vitrine ou lèche-écran ?
> magasinage.yahoo.ca
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: =?iso-8859-1?q?paquette=20stephane?=
>   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: VIVEK_SHARMA
>   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). 

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

RE: Oracle-L at UKOUG

[Connor McDonald]

I'm in - when you consider the wealth of nightlife in
Birmingham (yeah right!) then I'm sure we can find
somewhere to go :-)

 --- Mark Leith <[EMAIL PROTECTED]> wrote: > I'm
interested! It IS always nice to put names to
> faces..
> 
> Any ideas for when/where?
> 
> Mark
> 
> -Original Message-
> Mike (NESL-IT)
> Sent: 20 November 2002 09:13
> To: Multiple recipients of list ORACLE-L
> 
> 
> I'll be attending and it would be nice to put faces
> to some of the names on
> this list.
>  
> Mike
> 
> -Original Message-
> Sent: 20 November 2002 07:28
> To: Multiple recipients of list ORACLE-L
> 
> 
> 
> I will be at UKOUG and so will be another of other
> people of the Oaktable
> network.
> 
>  
> 
> Anjo.
> 
>  
> 
> -Original Message-
> Sent: Tuesday, November 19, 2002 9:19 PM
> To: Multiple recipients of list ORACLE-L
> 
>  
> 
> Anyone interested in an Oracle-L get together at
> UKOUG for those of us poor
> souls who were not able to attend Oracle World? OW
> attendees welcome also!
> 
>  
> 
> Dan Fink
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Hately, Mike (NESL-IT)
>   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: Mark Leith
>   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). 

=
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net

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

__
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Connor=20McDonald?=
  INET: [EMAIL PROTECTED]

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

Re: TEMP segments

[Rachel Carmichael]

Tim -- that adds new blocks above the HWM?  

I wonder if any command that adds new blocks (vs inserting rows into
existing ones) automatically creates the new blocks as TEMP segments.
Logically it makes sense but I wonder if it's documented anywhere.  



--- Tim Gorman <[EMAIL PROTECTED]> wrote:
> ...as does INSERT /*+ APPEND PARALLEL */...
>   - Original Message - 
>   From: Fink, Dan 
>   To: Multiple recipients of list ORACLE-L 
>   Sent: Tuesday, November 19, 2002 1:15 PM
>   Subject: TEMP segments
> 
> 
>   I just found a new command that creates TEMP segments. It is well
> known that index creations first create the index segments as TEMP
> segments then 'convert' them to index segments upon completion. What
> I just found out (thanks to a failed operation) is that 'CREATE TABLE
> AS SELECT' (ctas) also creates the segments as TEMP first.
> 
>   Dan Fink
> 


__
Do you Yahoo!?
Yahoo! Web Hosting - Let the expert host your site
http://webhosting.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Rachel Carmichael
  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: Negative value for Consistent gets etc. in V$Sesstat

[Naveen Nahata]

Thanx Anjo,

Can you elaborate on 'values may wrap'?

Regards
Naveen

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


1) values may wrap (and there are some obscure bugs that cause negative 
values)

2) You want to get the block in mode CR and there is actually no work needed 
(like cleanout or rollback) to get to that mode.

Anjo.

On Tuesday 19 November 2002 21:34, you wrote:
> Hi All,
>
> There is one report which takes 12 hours to run during the off hours. And
> that too on a database 6Gb in size! The report was designed by the
> consultants and all the queries in the report were doing a nested loops
> joins on many tables, optimizer mode was RULE. After changing the optimizer
> mode to CHOOSE, still no effect. Then I rewrote the report to use joins
> instead of 'SELECT a row, run query for that row, then select another row,
> run query for that row..'(written in Oracle Reports) the run time came
> crashing down to 10 Secs. (Hard to believe!!).
>
> Now for the question.
>
> After running the report for a few hours I terminate the report and see the
> values in V$sesstat. It is showing me negative values for Consistent gets
> etc When the report was running i ran the same query on v$sesstat, at
> that moment it was showing Consistent gets - 47 million approx.
>
> 1. Why is it showing negative values?
> 2. What does stat 'no work - consistent read gets' mean?
>
> OUTPUT ONE HOUR AFTER THE REPORT STARTED:
> -
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME VALUE
> --- --
> session connect time 478385736
> process last non-idle time   478385736
> consistent gets   47024111
> session logical reads 47024106
> no work - consistent read gets36724753
> buffer is not pinned count36650911
> table fetch by rowid  36643847
> buffer is pinned count36569847
> session pga memory 1651312
> session pga memory max 1651312
> session uga memory 1589476
> session uga memory max 1589476
> bytes sent via SQL*Net to client156588
> CPU used when call started   81035
> CPU used by this session 81035
> bytes received via SQL*Net from client   48012
> sorts (rows) 16390
> table fetch continued row 6650
> SQL*Net roundtrips to/from client 2043
> user calls2033
> execute count  788
> calls to get snapshot scn: kcmgss  786
> parse count (total) 17
> opened cursors cumulative   12
> table scan blocks gotten11
> recursive calls  9
> parse count (hard)   9
> db block gets9
> opened cursors current   6
> enqueue requests 5
> enqueue releases 5
> cursor authentications   5
> parse time elapsed   4
> table scans (short tables)   3
> parse time cpu   2
> logons cumulative1
> sorts (memory)   1
> logons current   1
>
> 38 rows selected.
>
> STATS 10 HOURS AFTER THE REPORT STARTED RUNNING
> ---
>
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME   VALUE

Re: RE: db file sequential read

[chao_ping]

VIVEK_SHARMA,
Hi, did you have benchmark result or other whitepaper talking about 
this thoery?
For db file sequential read, it is single block read to index and then 
to table, so i think enlarge the maxio size of the os won't help?(I just guess, did 
not test it).
Enableing AIO or using raw device maybe will help io performance, thus 
give reduced io wait, i think.
Good luck





Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(Chinese Oracle User Group)

=== 2002-11-19 23:34:00 ,you wrote£º===

>For "db file sequential read wait" Add Following to /etc/system
>On UFS
>Setting maxphys=8388608
>On VXFS
>set vxio:vol_maxio = 16384 ( implies 8MB )
>
>-Original Message-
>[mailto:[EMAIL PROTECTED]]
>Sent: Tuesday, November 19, 2002 8:24 PM
>To: Multiple recipients of list ORACLE-L
>
>
>
>I'm search of perfection in an imperfect world.
>This problem involves a V7.3.4.5 DB on V2.6 Solaris.
>
>As part of our nightwork batch processing, a bunch of reports are run
>against the DB.
>I have a DBMS_JOB which reports when processes are waiting for events like
>"db file sequential read". I typically get an email showing about 2 dozen
>sessions
>with wait times of 3 - 15 seconds all of which are doing one block reads
>against
>P_INVLOC; which is the Primary Key for the Inventory_Location table. This
>index
>has INITRANS=31 & FREELISTS=31. This is a decent sized index at about
>128MB.
>
>I'm open for suggestion WRT what else can & could be done to eliminate or
>reduce these waits.
>
>
>--
>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: VIVEK_SHARMA
>  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: chao_ping
  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: Running/Licensing Hyperthreaded Intel Pentium4 Xeon CPU's on Linu

[chao_ping]

Orr, Steve,
I am using Xeon MP as well on my DELL 6650 , and i have two nodes. I 
will patch my database next evening and later turn on the logical cpu feature on one 
node and leave the other node as it is.
I will compare the cpu load/application responce time change and give you the 
feedback.
Good luck





Regards
zhu chao
Eachnet DBA
86-21-32174588-667
[EMAIL PROTECTED]
www.happyit.net
www.cnoug.org(Chinese Oracle User Group)

=== 2002-11-18 14:25:00 ,you wrote£º===

>OK, I've got this new Dell Linux server with 4 "Hyperthreaded" Pentium4 Xeon
>CPU's. There are 4 physical CPU's but with hyperthreading the O/S sees 8
>CPU's as is reported in top. I've installed Oracle on this machine and it
>seems to run as if there really were 8 CPU's. Could that be true? Is there a
>performance gain for Oracle? --As if I really did have 8 CPU's? How does
>this work? For init.ora tuning parameters should I count the 4 physical
>CPU's or the 8 virtual CPU's? Any reports on running Oracle/Linux on these
>hyperthreaded Pentium4 Xeon CPU's?
>
>I'm afraid to ask Oracle about the licensing since they always answer with
>the higher number. ;-)
>
>
>Steve Orr
>Bozeman, Montana
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Orr, Steve
>  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: chao_ping
  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: Oracle-L at UKOUG

[Mark Leith]

I'm interested! It IS always nice to put names to faces..

Any ideas for when/where?

Mark

-Original Message-
Mike (NESL-IT)
Sent: 20 November 2002 09:13
To: Multiple recipients of list ORACLE-L


I'll be attending and it would be nice to put faces to some of the names on
this list.
 
Mike

-Original Message-
Sent: 20 November 2002 07:28
To: Multiple recipients of list ORACLE-L



I will be at UKOUG and so will be another of other people of the Oaktable
network.

 

Anjo.

 

-Original Message-
Sent: Tuesday, November 19, 2002 9:19 PM
To: Multiple recipients of list ORACLE-L

 

Anyone interested in an Oracle-L get together at UKOUG for those of us poor
souls who were not able to attend Oracle World? OW attendees welcome also!

 

Dan Fink

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hately, Mike (NESL-IT)
  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: Mark Leith
  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: [Q] ORACLE 9i fast_start_mttr_target and log_checkpoint_inte

[Hately, Mike (NESL-IT)]

Heh, naw it was an early morning pre-coffee reply. So probably nothing to
write home about =)

Mike



-Original Message-


Sorry Mike. I was going from memory. I'm sure your reply was better than
mine, not hard to do.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED] 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hately, Mike (NESL-IT)
  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: Oracle-L at UKOUG

[Hately, Mike (NESL-IT)]

I'll be attending and it would be nice to put faces to some of the names on
this list.
 
Mike

-Original Message-
Sent: 20 November 2002 07:28
To: Multiple recipients of list ORACLE-L



I will be at UKOUG and so will be another of other people of the Oaktable
network.

 

Anjo.

 

-Original Message-
Sent: Tuesday, November 19, 2002 9:19 PM
To: Multiple recipients of list ORACLE-L

 

Anyone interested in an Oracle-L get together at UKOUG for those of us poor
souls who were not able to attend Oracle World? OW attendees welcome also!

 

Dan Fink

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Hately, Mike (NESL-IT)
  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: Negative value for Consistent gets etc. in V$Sesstat

[Anjo Kolk]

1) values may wrap (and there are some obscure bugs that cause negative 
values)

2) You want to get the block in mode CR and there is actually no work needed 
(like cleanout or rollback) to get to that mode.

Anjo.

On Tuesday 19 November 2002 21:34, you wrote:
> Hi All,
>
> There is one report which takes 12 hours to run during the off hours. And
> that too on a database 6Gb in size! The report was designed by the
> consultants and all the queries in the report were doing a nested loops
> joins on many tables, optimizer mode was RULE. After changing the optimizer
> mode to CHOOSE, still no effect. Then I rewrote the report to use joins
> instead of 'SELECT a row, run query for that row, then select another row,
> run query for that row..'(written in Oracle Reports) the run time came
> crashing down to 10 Secs. (Hard to believe!!).
>
> Now for the question.
>
> After running the report for a few hours I terminate the report and see the
> values in V$sesstat. It is showing me negative values for Consistent gets
> etc When the report was running i ran the same query on v$sesstat, at
> that moment it was showing Consistent gets - 47 million approx.
>
> 1. Why is it showing negative values?
> 2. What does stat 'no work - consistent read gets' mean?
>
> OUTPUT ONE HOUR AFTER THE REPORT STARTED:
> -
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME VALUE
> --- --
> session connect time 478385736
> process last non-idle time   478385736
> consistent gets   47024111
> session logical reads 47024106
> no work - consistent read gets36724753
> buffer is not pinned count36650911
> table fetch by rowid  36643847
> buffer is pinned count36569847
> session pga memory 1651312
> session pga memory max 1651312
> session uga memory 1589476
> session uga memory max 1589476
> bytes sent via SQL*Net to client156588
> CPU used when call started   81035
> CPU used by this session 81035
> bytes received via SQL*Net from client   48012
> sorts (rows) 16390
> table fetch continued row 6650
> SQL*Net roundtrips to/from client 2043
> user calls2033
> execute count  788
> calls to get snapshot scn: kcmgss  786
> parse count (total) 17
> opened cursors cumulative   12
> table scan blocks gotten11
> recursive calls  9
> parse count (hard)   9
> db block gets9
> opened cursors current   6
> enqueue requests 5
> enqueue releases 5
> cursor authentications   5
> parse time elapsed   4
> table scans (short tables)   3
> parse time cpu   2
> logons cumulative1
> sorts (memory)   1
> logons current   1
>
> 38 rows selected.
>
> STATS 10 HOURS AFTER THE REPORT STARTED RUNNING
> ---
>
> SQL> SELECT NAME, VALUE FROM V$SESSTAT A, V$STATNAME B WHERE A.SID=16 AND
> VALUE != 0
>   2  AND A.STATISTIC# = B.STATISTIC#
>   3  ORDER BY VALUE DESC
>   4  /
>
> NAME   VALUE
> - --
> session connect time   478385736
> process last non-idle time

Re: SQL Trace

[Anjo Kolk]

On Tuesday 19 November 2002 22:03, you wrote:
> Qs What is the Cause in particular (or in General) of Time Difference
> between "cpu" & "elapsed" Columns in the following Query ?
>
e = c + wait time (of anykind) (+ rounding errors)

> Qs Is there Any Scope for improvement in the following Query ?
>
There probably is.

> Qs Is there any Best practise of working with Such Tables ?
>
> NOTE -
> 1) (tran_date , tran_id , part_tran_srl_num) fields form the unique
> index on the Table
> 2) Some Other Columns of the Table are also indexed
> 3) The Table is a Very Huge History Table to which only INSERT & SELECT
> Operations happen
> 4) The Table is the Largest of ALL Tables in the Database With a Size of
> about 100 GB
>
>
> 
> 
>
> select del_flg, tran_type, tran_sub_type, part_tran_type,
> gl_sub_head_code,
>   acid, TO_CHAR(value_date,'DD-MM- HH24:MI:SS'),
>   tran_amt||'!'||tran_crncy_code, tran_particular, entry_user_id,
>   pstd_user_id, vfd_user_id, TO_CHAR(entry_date,'DD-MM-
> HH24:MI:SS'),
>   TO_CHAR(pstd_date,'DD-MM- HH24:MI:SS'),
> TO_CHAR(vfd_date,'DD-MM-
>   HH24:MI:SS'), rpt_code, ref_num, instrmnt_type, TO_CHAR(instrmnt_date,
>   'DD-MM- HH24:MI:SS'), instrmnt_num, instrmnt_alpha, tran_rmks,
> pstd_flg,
>prnt_advc_ind, amt_reservation_ind,
> reservation_amt||'!'||tran_crncy_code,
>   restrict_modify_ind, lchg_user_id, TO_CHAR(lchg_time,'DD-MM-
>   HH24:MI:SS'), rcre_user_id, TO_CHAR(rcre_time,'DD-MM-
> HH24:MI:SS'),
>   cust_id, voucher_print_flg, module_id, br_code,
>   fx_tran_amt||'!'||crncy_code, rate_code, TO_CHAR(rate), crncy_code,
>   navigation_flg, tran_crncy_code, ref_crncy_code,
>   ref_amt||'!'||ref_crncy_code, sol_id, bank_code, trea_ref_num,
>   TO_CHAR(trea_rate), NVL(ts_cnt,0), rowid
> FROM
>  TBA_CUM_TRAN_DETAIL_TBL  WHERE  tran_date = TO_DATE( :1 ,'DD-MM-
>   HH24:MI:SS')  AND tran_id =  :2   AND part_tran_srl_num =  :3
>
>
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> Parse1  0.02   0.02  1  0  1
> 0
> Execute  2  2.62   2.43  0  0  0
> 0
> Fetch2  7.10   8.79   7705 11  0
> 2
> --- --   -- -- -- --
> --
> total40001  9.74  11.24   7706 11  1
> 2
>
> Misses in library cache during parse: 1
> Optimizer goal: RULE
> Parsing user id: 20  (TBAGEN)
>
> Rows Row Source Operation
> ---  ---
>   2  TABLE ACCESS BY INDEX ROWID CUM_TRAN_DETAIL_TABLE
>   4   INDEX UNIQUE SCAN (object id 10353)
>
>
> Rows Execution Plan
> ---  ---
>   0  SELECT STATEMENT   GOAL: RULE
>   2   TABLE ACCESS (BY INDEX ROWID) OF 'CUM_TRAN_DETAIL_TABLE'
>   4INDEX (UNIQUE SCAN) OF 'IDX_CUM_TRAN_DETAIL_TABLE' (UNIQUE)

-- 

Anjo Kolk
http://www.oraperf.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Anjo Kolk
  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: db file sequential read

[VIVEK_SHARMA]

For "db file sequential read wait" Add Following to /etc/system
On UFS 
Setting maxphys=8388608 
On VXFS
set vxio:vol_maxio = 16384 ( implies 8MB )

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Tuesday, November 19, 2002 8:24 PM
To: Multiple recipients of list ORACLE-L



I'm search of perfection in an imperfect world.
This problem involves a V7.3.4.5 DB on V2.6 Solaris.

As part of our nightwork batch processing, a bunch of reports are run
against the DB.
I have a DBMS_JOB which reports when processes are waiting for events like
"db file sequential read". I typically get an email showing about 2 dozen
sessions
with wait times of 3 - 15 seconds all of which are doing one block reads
against
P_INVLOC; which is the Primary Key for the Inventory_Location table. This
index
has INITRANS=31 & FREELISTS=31. This is a decent sized index at about
128MB.

I'm open for suggestion WRT what else can & could be done to eliminate or
reduce these waits.


-- 
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: VIVEK_SHARMA
  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: Oracle-L at UKOUG

[Anjo Kolk]

I will be at UKOUG and so will be another
of other people of the Oaktable network.

 

Anjo.

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Fink,
Dan
Sent: Tuesday, November 19, 2002
9:19 PM
To: Multiple recipients of list
ORACLE-L
Subject: Oracle-L at UKOUG

 



Anyone interested in an Oracle-L get
together at UKOUG for those of us poor souls who were not able to attend Oracle
World? OW attendees welcome also!





 





Dan Fink

RE: TEMP segments

[Anjo Kolk]

It did that for the longest time. It
creates a temp segment so that if the create table table
fails near the end, there doesn’t have to be a complete rollback. SMON
can come along and cleanup the segment. If the create table as has completed,
the temp segment will be converted to a ordinary
segment in the data dictionary.

 

Anjo.

 

-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]] On Behalf Of Fink,
Dan
Sent: Tuesday, November 19, 2002
9:16 PM
To: Multiple recipients of list
ORACLE-L
Subject: TEMP segments

 



I just found a new command that
creates TEMP segments. It is well known that index creations first create the
index segments as TEMP segments then 'convert' them to index segments upon
completion. What I just found out (thanks to a failed operation) is that
'CREATE TABLE AS SELECT' (ctas) also creates the segments as TEMP first.





 





Dan Fink