RE: When optimizer reevaluate SQL statement

[Sakthi , Raj]

Alex ,
to answer your question ,
yes any index creation will force reparsing of the
SQL.
At least it did on my 8.1.6 on HP ;)
-RS
--- "Hillman, Alex" <[EMAIL PROTECTED]>
wrote:
> !! Please do not post Off Topic to this List !!
> 
> You mean it invalidates SQL which has references to
> the newly analyzed
> objects, not all SQL in cache - right? Also are you
> sure that creating index
> on table will invalidate SQL which references this
> table or view based on
> this table?
> 
> Alex Hillman
> 
> -Original Message-
> Sent: Friday, September 14, 2001 11:40 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> !! Please do not post Off Topic to this List !!
> 
> Chris & list,
> 
> The last time I checked, an ANALYZE also invalidates
> the SQL in the shared pool, to force a parse and
> rebuild of the execution plan, on the next execution
> of the SQL statement.
> 
> Regards,
> 
> Gaja
> 
> --- Christopher Spence <[EMAIL PROTECTED]> wrote:
> > !! Please do not post Off Topic to this List !!
> > 
> > If the statement is not exactly the same, the new
> > statement will be
> > reparsed.
> > 
> > If you are executing it under a different user the
> > statement will be
> > reparsed.
> > 
> > If you drop/create an index, it will invalidate
> the
> > explain plan if that was
> > part of the chosen path.
> > 
> > I believe statistics also invalidates the plans as
> > well, but not 100% sure
> > on that.
> > 
> > "Do not criticize someone until you walked a mile
> in
> > their shoes, that way
> > when you criticize them, you are a mile a way and
> > have their shoes."
> > 
> > Christopher R. Spence 
> > Oracle DBA
> > Phone: (978) 322-5744
> > Fax:(707) 885-2275
> > 
> > Fuelspot
> > 73 Princeton Street
> > North, Chelmsford 01863
> >  
> > 
> > 
> > -Original Message-
> > Sent: Thursday, September 13, 2001 8:11 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > !! Please do not post Off Topic to this List !!
> > 
> > No takers so far - anybody?
> > 
> > Alex Hillman
> > 
> > -Original Message-
> > Sent: Thursday, September 06, 2001 4:30 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Let's assume that SQL statement was parsed by user
> > X. If this or another
> > user reexecute this same statement what are the
> > conditions that this SQL
> > statement will be reparsed? Let's assume that
> > privileges are not changed and
> > tables and/or views are not dropped and views are
> > not changed. And optimizer
> > parameters are not changed. First come to mind is
> > dropping index. What about
> > reanalizing one of the object - theoretically
> should
> > also reparse. Anything
> > else?
> > 
> > Also is there possibility to force reparsing of
> SQL
> > statement if let say
> > index was added - short of flashing shared pool?
> > 
> > Alex Hillman
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Hillman, Alex
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- (858) 538-5051 
> FAX:
> > (858) 538-5051
> > San Diego, California-- Public Internet
> > access / Mailing Lists
> >
>

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

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

> > To REMOVE yourself from this mailing list, send an
> > E-Mail message
> > to: [EMAIL PROTECTED] (note EXACT spelling of
> > 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB
> > ORACLE-L
> > (or the name of mailing list you want to be
> removed
> > from).  You may
>

Re: dba_waiters.lock_type = "Disk Space Transaction" - significance?

[Steve Smith]

Title: dba_waiters.lock_type = "Disk Space Transaction" - significance?



Analyzing a table (analyze...estimate/compute 
statistics) will put a lock on the library cache object preventing any ddl 
changes, but any dml should be okay.
 
Analyzing to validate the structure of a 
table(analyze ...validate structure) puts an exclusive lock on the table and 
prevents any other sessions from doing any dml.  
 
Are you analyzing the indexes?  If so, that puts a share 
lock on the underlying table and that prevents any dml until the lock is 
released.  Any sessions wanting to update, insert, delete on that table 
will wait until the analyze is complete..
 
Steve Smith
Unemployed DBA ;^)
 

  - Original Message  
  From: 
  Jacques Kilchoer 
  To: Multiple recipients of list ORACLE-L 
  
  Sent: Friday, September 14, 2001 9:30 
  PM
  Subject: dba_waiters.lock_type = "Disk 
  Space Transaction" - significance?
  
  I have a background process running that's doing an analyze on 
  tables, and storing the information in another table. The table in which it's 
  trying to store information has currently reached the maximum number of 
  extents (I'm doing a test to see how the background job handles ORA- errors, 
  so far not very gracefully.)
  Now other sessions are "stuck" behind the one session doing 
  the analyze. What could be the causes for this lock_type? ("disk space 
  transaction")
  Oracle 8.1.7 - Windows 2000 
  SQL> select * from dba_waiters ; 
  WAITING_SESSION HOLDING_SESSION LOCK_TYPE --- --- -- 
  MODE_HELD  MODE_REQUESTED  LOCK_ID1  LOCK_ID2   
  12  
  21 Disk Space Transaction Exclusive Exclusive 0 0 
  
   
  17  
  21 Disk Space Transaction Exclusive Exclusive 0 0 
  
   
  20  
  21 Disk Space Transaction Exclusive Exclusive 0 0 
  

dba_waiters.lock_type = "Disk Space Transaction" - significance?

[Jacques Kilchoer]

Title: dba_waiters.lock_type = "Disk Space Transaction" - significance?





I have a background process running that's doing an analyze on tables, and storing the information in another table. The table in which it's trying to store information has currently reached the maximum number of extents (I'm doing a test to see how the background job handles ORA- errors, so far not very gracefully.)

Now other sessions are "stuck" behind the one session doing the analyze. What could be the causes for this lock_type? ("disk space transaction")

Oracle 8.1.7 - Windows 2000


SQL> select * from dba_waiters ;


WAITING_SESSION HOLDING_SESSION LOCK_TYPE
--- --- --
MODE_HELD

MODE_REQUESTED

LOCK_ID1

LOCK_ID2

 12  21 Disk Space Transaction
Exclusive
Exclusive
0
0


 17  21 Disk Space Transaction
Exclusive
Exclusive
0
0


 20  21 Disk Space Transaction
Exclusive
Exclusive
0
0

ORA-04030 error

[Big Planet]

Hi All ,
I am getting this error 
ORA-04030  out of memory when importing in 
8.1.7 Solaris 8 .  There is no data in import it is just empty tables and 
stored procs .The error comes with one stored proc 
.
Any Idea how this can be solved 
.
 
TIA ,
-BigP
 

RE: USE_NL with or without ORDERED

[Larry Elkins]

!! Please do not post Off Topic to this List !!

Ed,

Do I have any idea? Yeah! The docs can be very confusing at times ;-).

Maybe they are just clarifying the specified table is the inner table as
opposed to the outer table of an NL join (which would be rather obvious to
you or me, but, maybe not to someone else "new" to Oracle, CBO, and hints)?
But I agree, reading the comment seems to imply the use of USE_NL forces the
target to be the inner table. I guess they are assuming one has read the
preceding comment about the ORDERED hint in conjunction with
USE_NL/USE_MERGE? Still, pretty confusing and could be worded a bit better.

And forget that other stuff I wrote about why your original query, even
though driving in the correct order, decided to use an HJ. I usually
investigate things instead of making off the cuff remarks. I didn't in that
case -- shame on me. Anyway, Jonathan Lewis's posting prompted me to pursue
it further.

I *think* what you are seeing is a "sides swapped" on an HJ. Because you
didn't specify an ORDERED hint, it also evaluated join methods for a join
order of employees to courses. During the HJ calculation for this order, a
"sides swap" (meaning swapping the inner/outer inputs?) was done and was the
least costly. Do a 10053 trace on your statement where it has just the
USE_NL hint and does an HJ in the correct order of how you want to do the
NL. Take a look in the second set of join calculations in the trace file and
see if you see "sides swapped" in the HJ (HA Join) calculation section.

There isn't a lot of information out there on the 10053 traces. I have seen
some info on the Steve Adams site (www.ixora.com.au), and, there is a paper
at http://www.evdbt.com/library.htm (it's down towards the bottom). There
are two places in the comments of that paper where hash joins and swapping
the inputs is mentioned -- the second place it is referenced is what has me
going down this path. Plus, I think Jonathan Lewis once mentioned on this
list the possibility of inputs getting swapped. Searching on-line, various
white papers, etc, I haven't come up with a hit on the "sides swapped"
mentioned in the 10053 trace (I have come across info about possible inputs
switching once the hash join is underway). But, based on that 10053 paper,
and the use of the term "sides swapped" in the trace file, it sounds like a
strong possibility that this is what is happening -- it also evaluates the
joins for an employees, courses order, flipping the inputs on the HJ, sees
it is the lowest cost, and chooses it. Bingo, the order you wanted but still
using an HJ.

Of course, this is all conjecture.

Regards,

Larry G. Elkins
[EMAIL PROTECTED]
214.954.1781

> -Original Message-
> Sent: Thursday, September 13, 2001 3:51 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: USE_NL with or without ORDERED
> Hi Larry,
>
> thanks for your comments. I'm in doubt. Sorry for possible
> misunderstanding but
> the following is excerpt from the same (8i) doc:
> 
> The USE_NL hint causes Oracle to join each specified table to
> another row source
> with a nested loops join using the specified table as the *inner* table.
> 
>
> Do you have any idea?
>
> Regards,
> Ed

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Higher Consistent Gets...

[Khedr, Waleed]

!! Please do not post Off Topic to this List !!

Try this and let's know how it works:

select --+ ordered use_hash(vw1)
   pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd , 
   (select --+ no_merge 
   location_code
 from gn_location
  connect by prior location_code=parent_code
  start with location_code='3142') vw1
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code = vw1.location_code 
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Regards,

Waleed

-Original Message-
Sent: Friday, September 14, 2001 8:10 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!


Raj,

Which query is fastest?  You don't have any timing information.

Make sure that timed_statistics is on in the database:

alter system set timed_statistics = true;

Run your query with trace on:
   alter session set sql_trace = true;

Then run the resulting trace files through tkprof.

You'll have much more information to work with.

Jared




 

Raj Gopalan


ons.co.uk>   cc:

Sent by: Subject: Higher
Consistent Gets... 
[EMAIL PROTECTED]

 

 

09/14/01 09:55 AM

Please respond to

ORACLE-L

 

 





!! Please do not post Off Topic to this List !!

Hi

I am having problem with a query. This query fetches rows from a table
which
has 15 million rows.

The problem is, when I execute this query with subquery, the consistent
gets
are 4700. Where us without the subquery the consistent gets are just 400.
If
I execute the subquery alone, the consistent gets are just 5.


Here is the main query with subquery results in cons.gets of 4700:


select pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code in (select location_code
  from gn_location
connect by prior location_code=parent_code
  start with location_code='3142')
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
 96   SORT (ORDER BY)
 96SORT (GROUP BY)
 96 COUNT (STOPKEY)
 96  NESTED LOOPS
   5137   INLIST ITERATOR
   5138TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
   'FR_SEARCH_QUERY'
   8566 INDEX   GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
 96   VIEW
   5136SORT (UNIQUE)
  1 CONNECT BY
  2  INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
 'PK_GN_LOCATION' (UNIQUE)
  1  TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1  TABLE ACCESS (FULL) OF 'GN_LOCATION'


Running just the subquery results in cons.gets of just 5.


  select location_code
from gn_location
 connect by prior location_code=parent_code
   start with location_code='3142'


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  1   CONNECT BY
  2INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
   (UNIQUE)
  1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)


Both the tables, indexes are analyzed. The optimizer mode is choose.

How do I tune this or Am I missing something obivious??

Thanks

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also se

RE: Visio for ERD's ?

[Davydov, Vitaliy]

!! Please do not post Off Topic to this List !!

I have been using Visio 2000 for a while.
You can generate ERDs from existing schemas using reverse engineering
option.

Vitaliy Davydov.


-Original Message-
Sent: Friday, September 14, 2001 1:26 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

I tried to create an ERD with Visio. Maybe I didn't know enough about it but
I was doing a lot of hand entering of info.  Then I got a complementary copy
of DBArtisan and did it in 10 minutes.

Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 3:55 PM


> !! Please do not post Off Topic to this List !!
>
> Let us know how it goes, I know a few people mentioned they were going to
> play with that feature, but I never heard anything of it posted, I still
use
> ER/Win, although I have Visio.
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
>
>
> -Original Message-
> Sent: Friday, September 14, 2001 3:35 PM
> To: Multiple recipients of list ORACLE-L
>
> !! Please do not post Off Topic to this List !!
>
> I would like to know if anyone is using Visio to re-engineer ERD's from an
> Oracle DB?  Any problems, gotchas or recommendations are welcome
>
> I have Visio Prof. 5c and will be using Oracle DB 8.1.7.
>
> Thanks,
> Ken Janusz, CPIM
> Database Conversion Lead
> Sufficient Systems, Inc.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ken Janusz
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Christopher Spence
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Davydov, Vitaliy
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Tablespace (datafile reducing)

[Kishore]

!! Please do not post Off Topic to this List !!

Hello Everybody

I am thankful to everybody who participated and/or
replied to my question.

Thanks again

You guys are great!!


I got some directions to go to???


Thank you all very much


Kishore

__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kishore
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Higher Consistent Gets...

[Jared . Still]

!! Please do not post Off Topic to this List !!


Raj,

Which query is fastest?  You don't have any timing information.

Make sure that timed_statistics is on in the database:

alter system set timed_statistics = true;

Run your query with trace on:
   alter session set sql_trace = true;

Then run the resulting trace files through tkprof.

You'll have much more information to work with.

Jared




   
 
Raj Gopalan
 

ons.co.uk>   cc:   
 
Sent by: Subject: Higher Consistent 
Gets... 
[EMAIL PROTECTED]   
 
   
 
   
 
09/14/01 09:55 AM  
 
Please respond to  
 
ORACLE-L   
 
   
 
   
 




!! Please do not post Off Topic to this List !!

Hi

I am having problem with a query. This query fetches rows from a table
which
has 15 million rows.

The problem is, when I execute this query with subquery, the consistent
gets
are 4700. Where us without the subquery the consistent gets are just 400.
If
I execute the subquery alone, the consistent gets are just 5.


Here is the main query with subquery results in cons.gets of 4700:


select pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code in (select location_code
  from gn_location
connect by prior location_code=parent_code
  start with location_code='3142')
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
 96   SORT (ORDER BY)
 96SORT (GROUP BY)
 96 COUNT (STOPKEY)
 96  NESTED LOOPS
   5137   INLIST ITERATOR
   5138TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
   'FR_SEARCH_QUERY'
   8566 INDEX   GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
 96   VIEW
   5136SORT (UNIQUE)
  1 CONNECT BY
  2  INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
 'PK_GN_LOCATION' (UNIQUE)
  1  TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1  TABLE ACCESS (FULL) OF 'GN_LOCATION'


Running just the subquery results in cons.gets of just 5.


  select location_code
from gn_location
 connect by prior location_code=parent_code
   start with location_code='3142'


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  1   CONNECT BY
  2INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
   (UNIQUE)
  1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)


Both the tables, indexes are analyzed. The optimizer mode is choose.

How do I tune this or Am I missing something obivious??

Thanks

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECT

RE: Apps 11i and Developer suggestions to get up to speed quickly

[John Kanagaraj]

!! Please do not post Off Topic to this List !!

Kathy,

I won't comment on the 'Developer' part as things have changed a *lot* since
I was a 'Developer' DBA in a past life :) However, seeing that I currently
call myself an 'Applications DBA' since I deal with a mix of Apps
10.7/11.0.3/11.5.3, I guess I can comment on this. I moved last year from a
pure DBA position to an Apps type position and hence am still learning.

First off - implementing Apps 11i afresh is no mean feat. Apart from the
need for a lot of Functional folks (who map what the user wants to specific
Apps modules and screens) and Technical folks (who setup the modules under
the direction of the Func. team as well as develop customizations), you will
need some technical 'Apps' DBA assistance during the setup and initial phase
of the project. Your organization cannot afford not to have these folks, and
they constitute the bulk of the $$ spent on the project (read : they don't
come cheap!). Oracle I believe still has a 'fixed cost' implementation plan,
but they will still be costlier than doing it via a large number of smaller
organizations that do this sort of thing.

As far as DBA goes, you need to develop skills that are aptly described as
'Internet DBA skills' by Oracle. These skills are essential for managing the
Apps Technology stack which (at this time) includes the Apache server, Java
Servlets, Web-based stuff, Apps' Concurrent Manager, etc. This should be
covered in the Apps Sys Admin class. Alert class is optional, but if you
have the budget, go for it!

Prior, during and after implementation, your life will be filled with
patching, patching and more patching of Apps. So much so that it is worth
getting a Database Baby Sitter (Jr.DBA) who can blindly apply patch after
patch... (there are literally hundreds depending on the number of languages
and modules). You will also learn / apply the fine art of Apps cloning -
copying and modifying a lot of files I would suggest employing a
knowledgeable Apps DBA contractor to help in the initial setup and config.
You can then learn on the job and take over at a later point in time.

You should also take up membership in OAUG (Oracle Application User Group) -
a free list such as this is available specifically for Apps DBA. Browse for
details at http://www.oaug.org 

All the best and Hth,
John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

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


>-Original Message-
>From: Kathy Duret [mailto:[EMAIL PROTECTED]]
>Sent: Friday, September 14, 2001 1:50 PM
>To: Multiple recipients of list ORACLE-L
>Subject: Apps 11i and Developer suggestions to get up to speed quickly
>
>
>!! Please do not post Off Topic to this List !!
>
>
>My company is going to be implementing both of these within 
>the next year.  I have taken Oracle Reports 6i and have 
>practical experience in it.  I have NO Apps or Forms experience.  
>
>What Oracle Courses, books and email groups to get/subscribe too.
>
>We are going to be implementing FA, HR, Payroll and Data 
>warehousing.  Don't know about any others right now.  My guess 
>is probably BOL and BOM as well.
>
>I am going to order Kimball's' Data Warehousing toolkit and 
>the Essential Oracle 8i Data Warehouse book by Dodge and Gorman.
>
>We have a mix of HP/Solaris/NT boxes with 8.1.6.  
>
>A manager here mentioned 3 DBA developer courses and he didn't 
>know the specifics and I can't see anything for developer DBA, 
> I think maybe they were thinking about the general dba 
>administration tuning and backup/recovery classes.  Please 
>correct me if I am wrong.
>
>Is the 11i Oracle Applications System Administration class 
>worth taking?  What about 11i Oracle alert?
>
>Any suggestions, advice, etc. are greatly appreciated.
>
>Thanks mucho much,
>
>Kathy
>
>Confidential
>This e-mail and any files transmitted with it are the property
>of Belkin Components and/or its affiliates, are confidential,
>and are intended solely for the use of the individual or
>entity to whom this e-mail is addressed.  If you are not one
>of the named recipients or otherwise have reason to believe
>that you have received this e-mail in error, please notify the
>sender and delete this message immediately from your computer.
>Any other use, retention, dissemination, forwarding, printing
>or copying of this e-mail is strictly prohibited.
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Kathy Duret
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spe

RE: Locally managed tablespace

[Gogala, Mladen]

!! Please do not post Off Topic to this List !!

1) Oracle is thy database and you will not have any other databases 
   your machines.
2) There are no such things as "private rollback segments". Thou shalt
   not create rollback segments with differing extent sizes.
3) Thou shalt have all of your rollback segments in the locally managed
   tablespaces to avoid overhead incurred when the dictionary extents tables
   are managed because of extending/shrinking rollback segments.
4) Thou shalt disable shrinking of rollback segments by not setting the
"optimal"
   parameter.

Please visit me on Mt. Sinai for the next 6 commandments.
   

> -Original Message-
> From: Kathy Duret [mailto:[EMAIL PROTECTED]]
> Sent: Friday, September 14, 2001 5:36 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Locally managed tablespace
> 
> 
> !! Please do not post Off Topic to this List !!
> 
> So if I understand you correctly, I should have rollback 
> segments and temp segments different for each of the 
> different extent sizes I choice.  So if I have tablespaces 
> with 128K, 1M and 4M I should have private rollbacks segments 
> set up for each of these.  
> 
> Is the syntax for creating a rollback segment different for 
> LMT?  I know with temporary tablespaces it is different.
> 
> 
> Kathy
> 
> -Original Message-
> Sent: Friday, September 14, 2001 1:50 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> !! Please do not post Off Topic to this List !!
> 
> Since you can't usually predict which rollback segment a 
> particular transaction
> is going to use anyway, generally accepted best practice is 
> to create a
> dedicated tablespace (or usually one per instance for OPS) 
> for rollback
> segments, "enough" rollback segments, and all rollback 
> segments of the same
> size, the same optimal, and the same extent sizes (obviously 
> - there is no
> "initial" or "next" in the syntax for creating a rollback 
> segment itself).  You
> might also need a "monster" to use with "set transaction use 
> rollback..." for
> monster batch-like jobs and it could be an exception to the rule.
> 
> In the message(s) that I posted, the implication was that the 
> "small set of
> extent sizes for the entire database" would be for everything 
> except SYSTEM,
> RBS, and TEMP.  The meaning is that if your choice of extent 
> sizes was, for
> example, 128K, 4M, and 128M, it would not restrict extent 
> sizes for rollback or
> temp to one of those values.  Use extent sizes for rollback 
> and temp that are
> appropriate to each.  Extent size for the TEMP tablespace 
> should a function of
> SORT_AREA_SIZE.  (I'll let someone else take the baton on that one if
> necessary.)
> 
> -Don Granaman
> [OraSaurus - Honk if you remember UFI!]
> 
> 
> Create rollback segments as you would for
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Friday, September 14, 2001 1:10 PM
> 
> 
> > !! Please do not post Off Topic to this List !!
> >
> > How are you to create the rollback segments?  Is this the 
> same or different
> from the other LMT tablespaces.  I see where the Temporary 
> Tablespaces are
> different.
> >
> > Any other good LMT articles besides the one below?  I want 
> to change our
> database into LMT and can you believe this was a brand new 
> 8.1.6 database
> created in April and they used LONG datatypes and other old 
> architecture.
> >
> > Kathy
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Don Granaman
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> 
> Confidential
> This e-mail and any files transmitted with it are the property
> of Belkin Components and/or its affiliates, are confidential,
> and are intended solely for the use of the individual or
> entity to whom this e-mail is addressed.  If you are not one
> of the named recipients or otherwise have reason to believe
> that you have received this e-mail in error, please notify the
> sender and delete this message immediately from your computer.
> Any other use, retention, dissemination, forwarding, printing
> or copying of this e-mail is strictly prohibited.
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Kathy Duret
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> ---

RE: Stripe size for RAID 0+1

[Pablo ksksksk]

!! Please do not post Off Topic to this List !!

Hi Christopher:

"32K, 64K is good safe bet, you have write-back
caching controller?"

I don't know yet, but I'll find it out.


Thanks for the paper Deshpande 


___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Read only snapshot

[Feng, Jun]

!! Please do not post Off Topic to this List !!

All,

We are trying to implement the read only snapshot, but having a lot of
problems. Could anybody share your experience with us?

Your help is very appreciated.

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Stripe size for RAID 0+1

[Christopher Spence]

!! Please do not post Off Topic to this List !!

32K, 64K is good safe bet, you have write-back caching controller?

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 6:48 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

Thanks Christopher,

I haven't installed the application and database
yet ! so I can't do any monitoring.
I think that I could start with a standard value
and start monitoring from there. 
My question is, what would it be that value? (I
don't know 64K, 128K, ...)
I just don't know how to calculate it. 

Thanks for your assistance


___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Stripe size for RAID 0+1

[Deshpande, Kirti]

!! Please do not post Off Topic to this List !!

You will find the following paper quite useful:

"Implementing RAID on Oracle Systems" by Gaja Krishna Vaidyanatha available
at http://www.quest.com/whitepapers/

Regards,

- Kirti Deshpande 
  Verizon Information Services
   http://www.superpages.com

> -Original Message-
> From: Pablo ksksksk [SMTP:[EMAIL PROTECTED]]
> Sent: Friday, September 14, 2001 5:48 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  RE: Stripe size for RAID 0+1
> 
> !! Please do not post Off Topic to this List !!
> 
> Thanks Christopher,
> 
> I haven't installed the application and database
> yet ! so I can't do any monitoring.
> I think that I could start with a standard value
> and start monitoring from there. 
> My question is, what would it be that value? (I
> don't know 64K, 128K, ...)
> I just don't know how to calculate it. 
> 
> Thanks for your assistance
> 
> 
> 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deshpande, Kirti
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Stripe size for RAID 0+1

[Pablo ksksksk]

!! Please do not post Off Topic to this List !!

Thanks Christopher,

I haven't installed the application and database
yet ! so I can't do any monitoring.
I think that I could start with a standard value
and start monitoring from there. 
My question is, what would it be that value? (I
don't know 64K, 128K, ...)
I just don't know how to calculate it. 

Thanks for your assistance


___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: truncating snapshots

[Paul Baumgartel]

!! Please do not post Off Topic to this List !!

I don't quite understand.  You mention "fast snapshots".  There's no
such thing as a "fast snapshot", only fast refresh, which applies
changes from the snapshot log on the master table.  If you were to
truncate the underlying snapshot table and then attempt a fast refresh,
the refresh would fail. 

I think you want to do complete refreshes of your snapshots.  They
start out by truncating the snapshot table.

--- Henry Poras <[EMAIL PROTECTED]> wrote:
> 
> I need to move the changes in a table from one database to another.
> It
> seemed that fast snapshots would be a good way to do this as the
> database
> will automatically keep track of the changes for me. The question is
> that
> once I move the data to the snapshot side, I need to manipulate it,
> move it
> into another table, and start the cycle again. This means truncating
> the
> snapshot between refreshes. I tried this on a test system and it
> seesm to
> work, but I don't think Oracle supports it. I don't want updatable
> snapshots
> as this is strictly one way movement of data. Has anyone else tried
> this?
> Are there any potential problems? I think I am just truncating a
> table
> underlying the snapshot view so I don't see what could go wrong.
> 



__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Baumgartel
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Stripe size for RAID 0+1

[Christopher Spence]

!! Please do not post Off Topic to this List !!

Depends if you have cache or not, and the volume of your traffic.

Higher stripe size will give you better bandwidth and more efficient I/O but
may not utilize the stripe efficiently.  With a cache, then generally you
can usually raise your stripe size.  

You should do some monitoring and see what your average service request size
is.  And go from there.  

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 5:21 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

Hi 
   AIX 4.3 and Oracle 8, OLTP system.
   db_block_size=8K
   The question is:
What stripe size should I use to configure the
RAID 0+1? and why?

TIA

___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: more PeopleSoft8 oddities

[John Lewis]

!! Please do not post Off Topic to this List !!

Sheeze! I got a headache just reading your description ;-)

-Original Message-
Sent: Friday, September 14, 2001 3:02 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

As promised, they just keep coming. Our system was churning out tons of
archive logs (at least for us). It was generating about 40-50M an hour at a
fairly constant clip throughout the day. We are nowhere near a 24 hour shop
so this wasn't user load. It also wasn't scheduled batch jobs (and anyway,
those should have shown spikes in log generation). My guess was some wierd
chattiness of the application. 

I took 9 different archive logs ( 3 groups of 3 from different light times.
1,3,4am from 2 days) and loaded them into logminer. I was hoping the same
object(s) were being hit over and over again and I could find that via
logminer. I also checked to make sure this happened over all loaded
logfiles. No such luck, BUT there were only ~2300 DML operations and more
than 100,000 commits (about 10/sec). The session_info field in
v$logmnr_contents pointed to 3 PeopleSoft processes: PSBRKDSP, PSSUBDSP,
PSPUBDSP (I'm glad peoplesoft adds this to their session info). Going to
v$sesstat I found that the bulk of 'redo entries', 'redo size', and 'user
commits' came from 3 SIDs (The size of user commits was consistent with the
10commits/sec rate, and v$session matched these sids with the same 3
peoplesoft processes). 

Time for a SQL trace. There were basically 2 pieces of SQL being run over
and over (slightly different for each process). A count of 'log file sync'
waits in the trace file again matched the commit rate. The SQL for one of
the processes was:

SELECT TO_CHAR(LASTUPDDTTM,'-MM-DD-HH24.MI.SS."00"') FROM
PSAPMSGSUBCSYNC WHERE CHNLNAME=:1
SELECT CHNLLOCK FROM PSAPMSGSUBCLOCK WHERE CHNLNAME=:1 FOR UPDATE OF
CHNLLOCK

Where the bind variables include
ACTUAL_TIME
APE_INDUSTRY
BANK
BUDGET_POSITION
COMMIT_CNTRL_BUDGET_UPDATE
COMPANY_PROPERTY
COMPETENCY
...

Surprisingly Customer Connection had something on this. It is the default
behavior of the Publish/Subscribe feature when configuring the application
server. The suggestion is to "turn off the Broker servers if your not using
them. You can reduce the time interval for which the brokers check in for
work... this will reduce the amount of logging but will not eliminate it.
To change the Scan interval edit your Domain configuration file, through
psadmin, and change the following variable in the dispatcher sections
[PSBRKDSP_dflt (publication broker dispatcher), PSSUBDSP_dflt (subscription
contract dispatcher) and PSPUBDSP_dflt (publication contract dispatcher)]
Scan Interval=10  (currently set to 10 seconds... this is the interval at
which the dispatchers check into the database looking for work) "

Hope this helps. I'm sure they'll be more installments coming.

Henry

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Lewis
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Lock Manager in Enterprise Manager 2.1?

[Miller, Jay]

!! Please do not post Off Topic to this List !!

Hi,

I remember this being discussed a long time ago but I'm still using 1.6 and
one of the other DBAs just asked me.  Does anyone know what happened to Lock
Manager (formerly part of Diagnostics Pack in 1.6) in Enterprise Manager
2.1? 
Is it now part of a different app?

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

truncating snapshots

[Henry Poras]

!! Please do not post Off Topic to this List !!

I need to move the changes in a table from one database to another. It
seemed that fast snapshots would be a good way to do this as the database
will automatically keep track of the changes for me. The question is that
once I move the data to the snapshot side, I need to manipulate it, move it
into another table, and start the cycle again. This means truncating the
snapshot between refreshes. I tried this on a test system and it seesm to
work, but I don't think Oracle supports it. I don't want updatable snapshots
as this is strictly one way movement of data. Has anyone else tried this?
Are there any potential problems? I think I am just truncating a table
underlying the snapshot view so I don't see what could go wrong.

Thanks.

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: ORACLE-L Digest -- Volume 2001, Number 257

[Eric D. Pierce]

!! Please do not post Off Topic to this List !!

SH*T

that was supposed to go directly to Ross, not the list,
sorry.

argh.


To: [EMAIL PROTECTED] (Oracle RDBMS Community Forum)
Send reply to:  [EMAIL PROTECTED]
Date sent:  Fri, 14 Sep 2001 12:57:04 -0700

> Dude,
> 
> How are things going?
> 
> Here is my home phone# (916) 929-1747, and email:
> 
> [EMAIL PROTECTED]
> 
> If you want to chat, call (or email  me your phone number), 
> and I'll call you back.
> 
> later,
> ep
> 
> 
> 
> --
> 
> ORACLE-L Digest -- Volume 2001, Number 257
> 
> > [EMAIL PROTECTED]
> 
> 


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

more PeopleSoft8 oddities

[Henry Poras]

!! Please do not post Off Topic to this List !!

As promised, they just keep coming. Our system was churning out tons of
archive logs (at least for us). It was generating about 40-50M an hour at a
fairly constant clip throughout the day. We are nowhere near a 24 hour shop
so this wasn't user load. It also wasn't scheduled batch jobs (and anyway,
those should have shown spikes in log generation). My guess was some wierd
chattiness of the application. 

I took 9 different archive logs ( 3 groups of 3 from different light times.
1,3,4am from 2 days) and loaded them into logminer. I was hoping the same
object(s) were being hit over and over again and I could find that via
logminer. I also checked to make sure this happened over all loaded
logfiles. No such luck, BUT there were only ~2300 DML operations and more
than 100,000 commits (about 10/sec). The session_info field in
v$logmnr_contents pointed to 3 PeopleSoft processes: PSBRKDSP, PSSUBDSP,
PSPUBDSP (I'm glad peoplesoft adds this to their session info). Going to
v$sesstat I found that the bulk of 'redo entries', 'redo size', and 'user
commits' came from 3 SIDs (The size of user commits was consistent with the
10commits/sec rate, and v$session matched these sids with the same 3
peoplesoft processes). 

Time for a SQL trace. There were basically 2 pieces of SQL being run over
and over (slightly different for each process). A count of 'log file sync'
waits in the trace file again matched the commit rate. The SQL for one of
the processes was:

SELECT TO_CHAR(LASTUPDDTTM,'-MM-DD-HH24.MI.SS."00"') FROM
PSAPMSGSUBCSYNC WHERE CHNLNAME=:1
SELECT CHNLLOCK FROM PSAPMSGSUBCLOCK WHERE CHNLNAME=:1 FOR UPDATE OF
CHNLLOCK

Where the bind variables include
ACTUAL_TIME
APE_INDUSTRY
BANK
BUDGET_POSITION
COMMIT_CNTRL_BUDGET_UPDATE
COMPANY_PROPERTY
COMPETENCY
...

Surprisingly Customer Connection had something on this. It is the default
behavior of the Publish/Subscribe feature when configuring the application
server. The suggestion is to "turn off the Broker servers if your not using
them. You can reduce the time interval for which the brokers check in for
work... this will reduce the amount of logging but will not eliminate it.
To change the Scan interval edit your Domain configuration file, through
psadmin, and change the following variable in the dispatcher sections
[PSBRKDSP_dflt (publication broker dispatcher), PSSUBDSP_dflt (subscription
contract dispatcher) and PSPUBDSP_dflt (publication contract dispatcher)]
Scan Interval=10  (currently set to 10 seconds... this is the interval at
which the dispatchers check into the database looking for work) "

Hope this helps. I'm sure they'll be more installments coming.

Henry

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: ORACLE-L Digest -- Volume 2001, Number 257

[Mohan, Ross]

!! Please do not post Off Topic to this List !!

 ep, 

i'll let him know...

;-)

- rm



-Original Message-
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: 9/14/2001 4:49 PM

SH*T

that was supposed to go directly to Ross, not the list,
sorry.

argh.


To: [EMAIL PROTECTED] (Oracle RDBMS Community
Forum)
Send reply to:  [EMAIL PROTECTED]
Date sent:  Fri, 14 Sep 2001 12:57:04 -0700

> Dude,
> 
> How are things going?
> 
> Here is my home phone# (916) 929-1747, and email:
> 
> [EMAIL PROTECTED]
> 
> If you want to chat, call (or email  me your phone number), 
> and I'll call you back.
> 
> later,
> ep
> 
> 
> 
> --
> 
> ORACLE-L Digest -- Volume 2001, Number 257
> 
> > [EMAIL PROTECTED]
> 
> 

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Where's the installer

[Pablo ksksksk]

!! Please do not post Off Topic to this List !!

Hi Brian and Gerardo:

   But in this case, after installing the product and
creating my database I execute:

 select * from v$option;

And I see this:

...
Partitioning TRUE
Objects  TRUE
Parallel Server  FALSE   
<
Advanced replication TRUE
Bit-mapped indexes   TRUE


doesn't this mean that OPS is not installed?

Gerardo, I've read somewhere, (OPS Concept manual I
think), that you can install OPS in a single machine
(not neccesary a cluster) and it will work. Of course
you'll get no benefit at all, moreover the performance
will be degraded. But It can be done for testing
purposes. Am I correct??


oh, and one more thing, can anyone tell me what AIX
partches are neccesary to install OPS (AIX 4.3.3 and
ORACLE 8.1.7) 

thank you very much.




___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Locally managed tablespace

[Kathy Duret]

!! Please do not post Off Topic to this List !!

So if I understand you correctly, I should have rollback segments and temp segments 
different for each of the different extent sizes I choice.  So if I have tablespaces 
with 128K, 1M and 4M I should have private rollbacks segments set up for each of 
these.  

Is the syntax for creating a rollback segment different for LMT?  I know with 
temporary tablespaces it is different.


Kathy

-Original Message-
Sent: Friday, September 14, 2001 1:50 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Since you can't usually predict which rollback segment a particular transaction
is going to use anyway, generally accepted best practice is to create a
dedicated tablespace (or usually one per instance for OPS) for rollback
segments, "enough" rollback segments, and all rollback segments of the same
size, the same optimal, and the same extent sizes (obviously - there is no
"initial" or "next" in the syntax for creating a rollback segment itself).  You
might also need a "monster" to use with "set transaction use rollback..." for
monster batch-like jobs and it could be an exception to the rule.

In the message(s) that I posted, the implication was that the "small set of
extent sizes for the entire database" would be for everything except SYSTEM,
RBS, and TEMP.  The meaning is that if your choice of extent sizes was, for
example, 128K, 4M, and 128M, it would not restrict extent sizes for rollback or
temp to one of those values.  Use extent sizes for rollback and temp that are
appropriate to each.  Extent size for the TEMP tablespace should a function of
SORT_AREA_SIZE.  (I'll let someone else take the baton on that one if
necessary.)

-Don Granaman
[OraSaurus - Honk if you remember UFI!]


Create rollback segments as you would for
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 1:10 PM


> !! Please do not post Off Topic to this List !!
>
> How are you to create the rollback segments?  Is this the same or different
from the other LMT tablespaces.  I see where the Temporary Tablespaces are
different.
>
> Any other good LMT articles besides the one below?  I want to change our
database into LMT and can you believe this was a brand new 8.1.6 database
created in April and they used LONG datatypes and other old architecture.
>
> Kathy

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kathy Duret
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Stripe size for RAID 0+1

[Pablo ksksksk]

!! Please do not post Off Topic to this List !!

Hi 
   AIX 4.3 and Oracle 8, OLTP system.
   db_block_size=8K
   The question is:
What stripe size should I use to configure the
RAID 0+1? and why?

TIA

___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: CBO changed path - why??

[Baker, Barbara]

!! Please do not post Off Topic to this List !!


Christopher:
Unfortunately we're not on 8i yet, so dbms_stats is not an option.
I see from the tkprof that it's walking an extraordinary number of rows, but
I can't
see why.

Thanks for taking a look - I really appreciate it.
Barb


> --
> From: Christopher Spence[SMTP:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> Sent: Friday, September 14, 2001 11:15 AM
> To:   Multiple recipients of list ORACLE-L
> Subject:  RE: CBO changed path - why??
> 
> !! Please do not post Off Topic to this List !!
> 
> What I would do is use DBMS_STATS to move the production stats to the
> staging db and see if the tests lead the same results.
> 
> Are ya stats up to date?  You using analyze or dbms_stats?
> Do you have comparing explain plans, trace files?
> 
> I would highly recommend tracing it and checking which step is doing a lot
> of rows, and comparing that to determine where it is slowing down.
> 
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
> 
> Christopher R. Spence 
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
> 
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>  
> 
> 
> -Original Message-
> Sent: Friday, September 14, 2001 12:30 PM
> To: Multiple recipients of list ORACLE-L
> 
> !! Please do not post Off Topic to this List !!
> 
> 
> We have had 2 querys go wacko on us.  Both are cursors in a large
> (5000 line) pl/sql package.  This interface package runs daily.
> The cursor execution below ran in less than 30 seconds on Tues;
> ran 2 hrs 15 minutes Wed. (yikes!)  We had the same problem
> with a similar cursor 2 weeks ago.
> 
> I've fixed the query by adding more selectivity to the where clause.
> Here's the real mystery.  I pulled the 3 tables from this join
> from the production box (E4500 Solaris 2.6, Oracle 8.0.5) to our
> smaller test box.  Small test box is running identical stuff (solaris 2.6,
> Oracle 8.0.5).  The query still runs in under 30 seconds on small
> test box.  I dumped all the init parameters (SELECT NAME, VALUE FROM
> V$PARAMETER) from both databases, then did a diff in the output files.
> No significant differences that I can see.
> 
> I'm wondering why the query still runs ok on the test box, but went
> wacko on the "real" system.
> 
> These 3 tables are small (invrows 95,062 rows 21 megs; sub_ad 5,993 rows,
> 20 megs; sub_pub 45,553 rows 30 megs).  All 3 tables have identical
> indexes on both boxes; all 3 have been analyzed on both boxes.
> All 3 tables have index on column adno. optimizer is choose on both
> instances.
> 
> 
> Sorry this is so long.  I'd appreciate any insights.
> Thx!!!
> 
> Barb
> 
> 
> 
> select
> i.adno,
> 
> frominvrows i,
> sub_ad a,
> sub_pub p
> WHERE   A.RUNNO=860  and   I.ROWTYPE=4
>   and   I.ADNO=A.ADNOand   I.VNO=A.VNO
>   and   i.adno=p.adnoand   i.pubno=p.pubno
>   and   a.vno=p.vno  and   A.VNO=1
>   and   a.startdate > a.rdate
>   and   a.enddate = to_date(a.cus4name,'mm/dd/')
>   and   to_char(a.rdate,'mm/dd/') = to_char(p.mdate,'mm/dd/')
> 
> ___
> autotrace from production (BAD!!)
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313)
>10   NESTED LOOPS (Cost=1 Card=1 Bytes=313)
>21 NESTED LOOPS (Cost=56 Card=7 Bytes=1267)
>32   TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=10 Card=1 Bytes=133)
>42   TABLE ACCESS (FULL) OF 'SUB_PUB' (Cost=46 Card=5820
> Bytes=279360)
>51 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6791
> Bytes=896412)
>65   INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1
> Card=6791)
> 
> 
> 
> ___
> autotrace from test box (Good!)
> 
> Execution Plan
> --
>0  SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313)
>10   NESTED LOOPS (Cost=1 Card=1 Bytes=313)
>21 NESTED LOOPS (Cost=1 Card=1 Bytes=265)
>32   TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=11 Card=1 Bytes=133)
>42   TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1
> Card=6906
> Bytes=911592)
>54 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1
> Card=6906)
>61 TABLE ACCESS (BY INDEX ROWID) OF 'SUB_PUB' (Cost=1 Card=6911
> Bytes=331728)
>76   INDEX (UNIQUE SCAN) OF 'I_SUBPUB1' (UNIQUE)
> 
> 
> 
> ___
> tkprof from production (BAD!!)
> (The tkprof shows 129,696,658 rows returned for sub_pub when the
> entire table is only 45,000 rows.)
> 
> 
> call count   cpuelapsed   disk  querycurrent
> rows
> --- --   -- -- -- --
> --
> 

RE: 64 bit vs 32 bit Oracle

[Steve Rospo]

!! Please do not post Off Topic to this List !!


Where does this additional throughput come from?

S-

On Fri, 14 Sep 2001, Christopher Spence wrote:

> !! Please do not post Off Topic to this List !!
> 
> Depends, 64bit can certainly give you more throughput, but it is very slow
> for being patched.
> 
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
> 
> Christopher R. Spence 
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
> 
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863

> -Original Message-
> Sent: Friday, September 14, 2001 3:50 PM
> To: Multiple recipients of list ORACLE-L
> 
> !! Please do not post Off Topic to this List !!
> 
> Is there any benefit of 64 bit versions over 32 bit versions of Oracle
> beyond allowing > 2GB SGAs?  (8.1.7/Solaris 2.8 in particular)  The
> systems I'm working with are predominantly DSS queries against data sets
> that most of the servers have no hope of caching anywhere near the amount
> of data needed so huge SGAs are not that useful.  Our C++ guys tell me
> that Sun reccommends that compiling 64 bit binaries for processes that
> might take advantage of the larger memory limit.  The logic being that 64
> bit pointers take up twice as much space as 32 bit pointers and the
> processor cache density is thus lower for 64 bit processes.
> 
> Does anyone know if there is any additional trickery Oracle does on the
> 64-bit port that may provide performance benefits that would outweigh the
> potential for lower cache hit rates?  Magic data structures?  
> _make_sql_faster support?
> 
> S-
> 

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Lots and lots of redo logs

[Jonathan Lewis]

!! Please do not post Off Topic to this List !!


I love it when people explain their hypotheses - it makes
it so much easier to understand why two people can
apparently contradict each other and still be right.

Presumably the argument for putting the data at the
outside edge (could still be) correct once you've decided
that you're only going to use a quarter of the disc space
anyway.


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

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 14 September 2001 18:43


|!! Please do not post Off Topic to this List !!
|
|AIX has a unique concept of this.
|
|Given data spread on a disk. The head will spend more time over the
| middle tracks as it seeks data that is distributed across the disk.
|
|Thus for faster access, place your tablespace on the middle tracks
|of a disk because the probability that the head will be over it is
|much greater.
|
|Learned this with Syabase on AIX. It may still hold true today.
|I don't know. Technology had advanced a lot from those day. (But I
think the
|laws of physics are the same ;-))
|
|-Original Message-
|Sent: Friday, September 14, 2001 10:05 AM
|To: Multiple recipients of list ORACLE-L
|
|
|!! Please do not post Off Topic to this List !!
|
|Stealing a quote from: Optimal Storage Configuration Made Easy,
|By Juan Loaiza, Oracle Corporation
|
|"The transfer rate for a disk drive is not the same for all portions
of a
|disk.
|The outer sectors of a disk drive move by the disk head faster than
the
|inner sections
|leading to a faster transfer rate for the outer sectors.  This is
simply
|because
|of the circular shape of a disk drive."
|


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: CBO - default no of rows

[Jonathan Lewis]

!! Please do not post Off Topic to this List !!


Perhaps your best bet is to experiment with a 10053 trace.
I have just done a couple of quick tests on 8.1.7 on NT4
with a 4K block size - set up two tables with indexes,
no stats, and write SQL with the expectation of a hash
join.

The results suggest that Oracle knows the HWM, and
uses an estimated 100-byte row size to calculate the
number of rows; 30 rows per value to estimate selectivity,
and 5% as the target for a 'LIKE' clause.




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

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 14 September 2001 11:02


|My question of the day is :-
|What value does the CBO use as a default number of rows for a table.
|
|Background: -
|
|We all know that if any tables in a query have been analyzed then CBO
is
|used for the query not RBO  (couple of caveats I know but let's
continue).
|So if 3 tables are used in a query and table a has 500 rows
(analyzed) table
|b has 50 rows (never analyzed) and table c has 350 rows (never
analyzed)
|all things being equal then CBO is used but what values does the CBO
use for
|tables b or c to decide which execution plan is best.
|
|

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: 64 bit vs 32 bit Oracle

[Jonathan Lewis]

!! Please do not post Off Topic to this List !!


My experience to date is that Oracle 9i running
on 64-bit hp-ux 11 runs at about half the speed
of Oracle 8.1.7 running on 32-bit hp-ux.

I also have a test on Sun showing 9.0.1 on
64-bit Solaris running 20% slower than
8.1.7 on 64-bit Solaris; and at speed which
suggest that it is the 32/64 bit change that
causes most of the degradation.

I have yet to install 8.1.7 on 64-bit hp-ux
and compare it with the other two hp-ux
setups.


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

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com
Use spare CPU to assist in cancer research.

-Original Message-
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: 14 September 2001 20:12


|!! Please do not post Off Topic to this List !!
|
|
|Is there any benefit of 64 bit versions over 32 bit versions of
Oracle
|beyond allowing > 2GB SGAs?  (8.1.7/Solaris 2.8 in particular)  The
|systems I'm working with are predominantly DSS queries against data
sets
|that most of the servers have no hope of caching anywhere near the
amount
|of data needed so huge SGAs are not that useful.  Our C++ guys tell
me
|that Sun reccommends that compiling 64 bit binaries for processes
that
|might take advantage of the larger memory limit.  The logic being
that 64
|bit pointers take up twice as much space as 32 bit pointers and the
|processor cache density is thus lower for 64 bit processes.
|
|Does anyone know if there is any additional trickery Oracle does on
the
|64-bit port that may provide performance benefits that would outweigh
the
|potential for lower cache hit rates?  Magic data structures?
|_make_sql_faster support?
|
|S-
|
|
|
|
|--
|Please see the official ORACLE-L FAQ: http://www.orafaq.com
|--
|Author: Steve Rospo
|  INET: [EMAIL PROTECTED]
|
|Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
|San Diego, California-- Public Internet access / Mailing
Lists
|
|To REMOVE yourself from this mailing list, send an E-Mail message
|to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
|the message BODY, include a line containing: UNSUB ORACLE-L
|(or the name of mailing list you want to be removed from).  You may
|also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: ORACLE-L Digest -- Volume 2001, Number 257

[Eric D. Pierce]

!! Please do not post Off Topic to this List !!

Dude,

How are things going?

Here is my home phone# (916) 929-1747, and email:

[EMAIL PROTECTED]

If you want to chat, call (or email  me your phone number), 
and I'll call you back.

later,
ep



--

ORACLE-L Digest -- Volume 2001, Number 257

> [EMAIL PROTECTED]


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Strange performance problem

[Cherie_Machler]

!! Please do not post Off Topic to this List !!


Rachel,

The difference in rows is not significant anywhere in the explain plan.

Thanks for your reply.

Cherie


   
   
"Rachel
   
Carmichael"  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
 Subject: Re: Strange performance problem  
   
Sent by:   
   
[EMAIL PROTECTED] 
   
om 
   
   
   
   
   
09/14/01 02:55 
   
PM 
   
Please respond 
   
to ORACLE-L
   
   
   
   
   




!! Please do not post Off Topic to this List !!

>It should be an exact copy as of Aug. 16th.   I ran the query on the >copy

>and on the current production database and the resulting explain >plans
>were identical except for the number of rows returned.   Total >execution
>time and cpu times were similar.

What's the difference in the number of rows? I see buried deep in the
explain plan a Cartesian join if the numbe rows jumped significantly,
that might be the problem

>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Strange performance problem
>Date: Fri, 14 Sep 2001 11:05:29 -0800
>
>!! Please do not post Off Topic to this List !!
>
>
>I have a nightly load job that was being tracked by our developers.
>According to their nightly logs (going back months), a query was running
>as far back as they can record with a sub-second response time.
>
>Then on a particular date (Aug. 23rd), the query started taking more
>than 20 minutes to complete.   It has taken that long to complete ever
>since.
>
>I looked at the explain plan and it looks o.k.   Indexes are being used
>and there are no suspicious full table scans.  The init.ora file has not
>changed
>since then.
>
>We restored a full copy of the database to an alternate host using rman.
>It should be an exact copy as of Aug. 16th.   I ran the query on the copy
>and
>on the current production database and the resulting explain plans were
>identical except for the number of rows returned.   Total execution time
>and cpu times were similar.
>
>I looked through our change documentation and I do not see any record
>of data structure changes or any data changes at all in the database
>in question.
>
>I am sort of at a loss for what to try next.   What sort of changes might
>cause such an extreme degradation in performance as this?
>
>This is an 8.1.7 database on Sun Solaris 2.8.  The optimization is
>rule-based.
>No partitioning.   Database is about 80 Gig in size.   Following is the
>explain
>plan, if anyone is interested:
>
>SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM,
>ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM
>FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW
>
>call count   cpuelapsed   disk  querycurrent
>rows
>--- --   -- -- -- --
>--
>Parse1  0.26   0.27  0  0  0
>0
>Execute  2  0.01   0.01  0  0  1
>0
>Fetch  128982.191026.27 1454639732999  55484
>1897
>--- --   -- -- -- --
>--
>total  131982.461026.55 1454639732999  55485
>1897
>
>Rows Row Source Operation
>---  ---
>1897  FILTER
>2041   NESTED LOOPS
>2422HASH JOIN
>2341 NESTED LOOPS
>2342  NESTED LOOPS
>2338

Re: OPS: Where's the installer

[Brian McGraw]

!! Please do not post Off Topic to this List !!

Pablo -

I'm not familiar w/ AIX, but on Solaris, you don't see the OPS option
until the DLM has been installed on the OS.

Brian

Pablo ksksksk wrote:

> !! Please do not post Off Topic to this List !!
>
> Hi again
>I've got Oracle 8.1.6 EE for AIX. I run the
> installer but I don't see any option named "Parallel
> Server"
>Do I have the correct CD? Where is OPS?
>
> TIA
>
> ___
> Do You Yahoo!?
> Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
> http://messenger.yahoo.es
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Pablo=20ksksksk?=
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

--
--
| Brian McGraw -- Oracle DBA |
| Central Alabama Oracle Users Group |
||
| mailto:[EMAIL PROTECTED]  |
| http://bmcgraw.home.mindspring.com |
--


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Where's the installer

[Molina, Gerardo]

!! Please do not post Off Topic to this List !!

You will not see OPS as an option to select unless installer detects a
clustered environment.  You need to have your clustered environment
configured before running the installer.

HTH
Gerardo

-Original Message-
Sent: Friday, September 14, 2001 1:26 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Hi again
   I've got Oracle 8.1.6 EE for AIX. I run the
installer but I don't see any option named "Parallel
Server"
   Do I have the correct CD? Where is OPS?

TIA

___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Molina, Gerardo
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Visio for ERD's ?

[Ruth Gramolini]

!! Please do not post Off Topic to this List !!

I tried to create an ERD with Visio. Maybe I didn't know enough about it but
I was doing a lot of hand entering of info.  Then I got a complementary copy
of DBArtisan and did it in 10 minutes.

Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 3:55 PM


> !! Please do not post Off Topic to this List !!
>
> Let us know how it goes, I know a few people mentioned they were going to
> play with that feature, but I never heard anything of it posted, I still
use
> ER/Win, although I have Visio.
>
> "Do not criticize someone until you walked a mile in their shoes, that way
> when you criticize them, you are a mile a way and have their shoes."
>
> Christopher R. Spence
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
>
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>
>
>
> -Original Message-
> Sent: Friday, September 14, 2001 3:35 PM
> To: Multiple recipients of list ORACLE-L
>
> !! Please do not post Off Topic to this List !!
>
> I would like to know if anyone is using Visio to re-engineer ERD's from an
> Oracle DB?  Any problems, gotchas or recommendations are welcome
>
> I have Visio Prof. 5c and will be using Oracle DB 8.1.7.
>
> Thanks,
> Ken Janusz, CPIM
> Database Conversion Lead
> Sufficient Systems, Inc.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Ken Janusz
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Christopher Spence
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: I/O Performance/bottlenecks on EMC Symmetrix

[Armstrong, Michele]

!! Please do not post Off Topic to this List !!

We use EMC Symmetrics. When I got here, the folks who set things up
initially where under the impression that you will never have to worry about
placement/io with a Symm. Wrong! I underwent a painstaking process of
relaying out the disks according to conventions DBA normally use taking into
consideration index/data/volume of read/write to physical disks. Since you
are presented with logical volumes, we use sym commands on each of our hosts
connected to the symm to give us a graphical layout of all the logical
volumes on the physical disks. EMC has a tool to do this called Resource
View. EMC also has a tool called workload analyzer to analyze activity on
each fa / disk, etc. And there is also a utility called Symm Optimizer to
detect hot spots and optionally, replace that data to a different physical
disk. You should find out if you have any of these tools available to you. 

HTH 
Michele Armstrong

-Original Message-
Sent: Thursday, September 13, 2001 7:55 AM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Hi All,


Does anybody here on the list have experience with EMC/symmetrix storage
units.?

We have our databases on this machine and I have a feeling the the I/O
performance is not very good. I can not proof it since I do not have any
experience/data/access to that machine. We do however have a very
cooperative UNIX group but they also lack experience with performance on
this machine.

Who can give me pointers about I/O throughput that can be reached,
configuration pittfalls etc..

Example:
RS6000 8CPU's and 4Gb memory with storage on EMC/symmetrix. Job takes about
2 hours to complete.

F50 1 CPU 1Gb memory (TEST machine) local disks. same job takes 0.5 hours
to complete.


Jack

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

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

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

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

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





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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Armstrong, Michele
  INET: [EMAIL PROTECTED]

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

Re: Locally managed tablespace

[Don Granaman]

!! Please do not post Off Topic to this List !!

Since you can't usually predict which rollback segment a particular transaction
is going to use anyway, generally accepted best practice is to create a
dedicated tablespace (or usually one per instance for OPS) for rollback
segments, "enough" rollback segments, and all rollback segments of the same
size, the same optimal, and the same extent sizes (obviously - there is no
"initial" or "next" in the syntax for creating a rollback segment itself).  You
might also need a "monster" to use with "set transaction use rollback..." for
monster batch-like jobs and it could be an exception to the rule.

In the message(s) that I posted, the implication was that the "small set of
extent sizes for the entire database" would be for everything except SYSTEM,
RBS, and TEMP.  The meaning is that if your choice of extent sizes was, for
example, 128K, 4M, and 128M, it would not restrict extent sizes for rollback or
temp to one of those values.  Use extent sizes for rollback and temp that are
appropriate to each.  Extent size for the TEMP tablespace should a function of
SORT_AREA_SIZE.  (I'll let someone else take the baton on that one if
necessary.)

-Don Granaman
[OraSaurus - Honk if you remember UFI!]


Create rollback segments as you would for
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 1:10 PM


> !! Please do not post Off Topic to this List !!
>
> How are you to create the rollback segments?  Is this the same or different
from the other LMT tablespaces.  I see where the Temporary Tablespaces are
different.
>
> Any other good LMT articles besides the one below?  I want to change our
database into LMT and can you believe this was a brand new 8.1.6 database
created in April and they used LONG datatypes and other old architecture.
>
> Kathy

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: IOUG-A deadline --- deadline extended to 9/21

[Rachel Carmichael]

!! Please do not post Off Topic to this List !!


just checked the site, they have extended the deadline to 9/21, kinda makes 
sense.

>From: "Adams, Matthew (GEA, 088130)" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: IOUG-A deadline
>Date: Fri, 14 Sep 2001 12:09:38 -0800
>
>For those of you planning to submit papers,
>today is the deadline for the call-for-papers
>for IOUG-A 2002.
>
>see   www.ioug.org
>
>for more details


_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Apps 11i and Developer suggestions to get up to speed quickly

[Kathy Duret]

!! Please do not post Off Topic to this List !!


My company is going to be implementing both of these within the next year.  I have 
taken Oracle Reports 6i and have practical experience in it.  I have NO Apps or Forms 
experience.  

What Oracle Courses, books and email groups to get/subscribe too.

We are going to be implementing FA, HR, Payroll and Data warehousing.  Don't know 
about any others right now.  My guess is probably BOL and BOM as well.

I am going to order Kimball's' Data Warehousing toolkit and the Essential Oracle 8i 
Data Warehouse book by Dodge and Gorman.

We have a mix of HP/Solaris/NT boxes with 8.1.6.  

A manager here mentioned 3 DBA developer courses and he didn't know the specifics and 
I can't see anything for developer DBA,  I think maybe they were thinking about the 
general dba administration tuning and backup/recovery classes.  Please correct me if I 
am wrong.

Is the 11i Oracle Applications System Administration class worth taking?  What about 
11i Oracle alert?

Any suggestions, advice, etc. are greatly appreciated.

Thanks mucho much,

Kathy

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kathy Duret
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

OPS: Where's the installer

[Pablo ksksksk]

!! Please do not post Off Topic to this List !!

Hi again
   I've got Oracle 8.1.6 EE for AIX. I run the
installer but I don't see any option named "Parallel
Server"
   Do I have the correct CD? Where is OPS?

TIA

___
Do You Yahoo!?
Yahoo! Messenger: Comunicación instantánea gratis con tu gente -
http://messenger.yahoo.es
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?q?Pablo=20ksksksk?=
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Visio for ERD's ?

[Kevin Lange]

!! Please do not post Off Topic to this List !!

Our ERDs are in Visio.  Its not a facy product for this ... but its a nice
added feature to the overall product.

-Original Message-
Sent: Friday, September 14, 2001 2:56 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Let us know how it goes, I know a few people mentioned they were going to
play with that feature, but I never heard anything of it posted, I still use
ER/Win, although I have Visio.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 3:35 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

I would like to know if anyone is using Visio to re-engineer ERD's from an
Oracle DB?  Any problems, gotchas or recommendations are welcome

I have Visio Prof. 5c and will be using Oracle DB 8.1.7.

Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kevin Lange
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Strange performance problem

[Rachel Carmichael]

!! Please do not post Off Topic to this List !!

>It should be an exact copy as of Aug. 16th.   I ran the query on the >copy 
>and on the current production database and the resulting explain >plans 
>were identical except for the number of rows returned.   Total >execution 
>time and cpu times were similar.

What's the difference in the number of rows? I see buried deep in the 
explain plan a Cartesian join if the numbe rows jumped significantly, 
that might be the problem

>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Strange performance problem
>Date: Fri, 14 Sep 2001 11:05:29 -0800
>
>!! Please do not post Off Topic to this List !!
>
>
>I have a nightly load job that was being tracked by our developers.
>According to their nightly logs (going back months), a query was running
>as far back as they can record with a sub-second response time.
>
>Then on a particular date (Aug. 23rd), the query started taking more
>than 20 minutes to complete.   It has taken that long to complete ever
>since.
>
>I looked at the explain plan and it looks o.k.   Indexes are being used
>and there are no suspicious full table scans.  The init.ora file has not
>changed
>since then.
>
>We restored a full copy of the database to an alternate host using rman.
>It should be an exact copy as of Aug. 16th.   I ran the query on the copy
>and
>on the current production database and the resulting explain plans were
>identical except for the number of rows returned.   Total execution time
>and cpu times were similar.
>
>I looked through our change documentation and I do not see any record
>of data structure changes or any data changes at all in the database
>in question.
>
>I am sort of at a loss for what to try next.   What sort of changes might
>cause such an extreme degradation in performance as this?
>
>This is an 8.1.7 database on Sun Solaris 2.8.  The optimization is
>rule-based.
>No partitioning.   Database is about 80 Gig in size.   Following is the
>explain
>plan, if anyone is interested:
>
>SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID,
>ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM,
>ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM
>FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW
>
>call count   cpuelapsed   disk  querycurrent
>rows
>--- --   -- -- -- --
>--
>Parse1  0.26   0.27  0  0  0
>0
>Execute  2  0.01   0.01  0  0  1
>0
>Fetch  128982.191026.27 1454639732999  55484
>1897
>--- --   -- -- -- --
>--
>total  131982.461026.55 1454639732999  55485
>1897
>
>Rows Row Source Operation
>---  ---
>1897  FILTER
>2041   NESTED LOOPS
>2422HASH JOIN
>2341 NESTED LOOPS
>2342  NESTED LOOPS
>2338   NESTED LOOPS
>2338NESTED LOOPS
>2346 NESTED LOOPS
>2510  NESTED LOOPS
>2510   NESTED LOOPS
>2510INDEX FAST FULL SCAN (object id 17279)
>5018INDEX UNIQUE SCAN (object id 17278)
>5018   TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
>5018INDEX UNIQUE SCAN (object id 17266)
>4854  INDEX RANGE SCAN (object id 17270)
>4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN
>4682  INDEX RANGE SCAN (object id 17283)
>4674VIEW ACTIVE_EAS_RPT_PROF_VIEW
>  100491 SORT UNIQUE
>43  UNION-ALL
>  10   TABLE ACCESS FULL EAS_RPT_PROF
>  33   FILTER
>  34NESTED LOOPS
> 734 NESTED LOOPS
>  207976  NESTED LOOPS
>  207976   MERGE JOIN CARTESIAN
> 706INDEX FAST FULL SCAN (object id 17270)
>  208680SORT JOIN
> 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
>  415950   TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
>  415950INDEX UNIQUE SCAN (object id 17266)
>  208708  INDEX UNIQUE SCAN (object id 17275)
> 766 TABLE ACCESS FULL EAS_RPT_PROF
>4678   TABLE ACCESS FULL USER_SIGNON
>2341  INDEX UNIQUE SCAN (object id 17275)
> 295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
>4461VIEW ACTIVE_EAS_PERSON_VIEW
>2675205 SORT UNIQUE
>1105  UNION-ALL
> 128   NESTED LOOPS
>1107INDEX RANGE SCAN (object id 17284)
> 128TABLE ACCESS BY INDEX ROWID EAS_PERSON
>2212 INDEX UNIQUE SCAN (object id 17277)
> 977   FILTER
>1008NESTED LOOPS
>  288511 NESTED LOOPS
>  326271  MERGE JOIN CARTESIAN
>1107   INDEX RANGE SCAN (object id 17

Re: I/O Performance/bottlenecks on EMC Symmetrix

[Don Granaman]

!! Please do not post Off Topic to this List !!

Absolutely!  Every loop must have an exit!

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 12:45 PM


> !! Please do not post Off Topic to this List !!
>
> corollary to rule 2
>
> after a certain point, just stop. It ain't worth it anymore.
>
>
> >From: Christopher Spence <[EMAIL PROTECTED]>
> >Reply-To: [EMAIL PROTECTED]
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >Subject: RE: I/O Performance/bottlenecks on EMC Symmetrix
> >Date: Fri, 14 Sep 2001 05:45:20 -0800
> >
> >Rules of tuning databases.
> >
> >1. There is always a bottleneck.
> >2. Once you solve the bottle neck, refer to rule number 1.
> >
> >"Do not criticize someone until you walked a mile in their shoes, that way
> >when you criticize them, you are a mile a way and have their shoes."
> >Christopher R. Spence
> >Oracle DBA
> >Phone: (978) 322-5744
> >Fax:(707) 885-2275
> >Fuelspot
> >73 Princeton Street
> >North, Chelmsford 01863
> >
> >-Original Message-
> >Sent: Thursday, September 13, 2001 10:01 AM
> >To: Multiple recipients of list ORACLE-L
> >
> >At one site I worked using Oracle Financials we were having serious
> >performance problems at what seemed to us random intervals. Spent months
> >looking at the database after the Unix boys had said that there was no way
> >we could have I/O problems with the throughput capabililities of EMC and
> >the
> >Symetrix set up we had.
> >Eventually turned out that 3 systems were sharing the same disks and the
> >disks had not been striped. Therefore other system were causing us
> >performance problems.
> >If you have an EMC support contract which I think you must have you, the
> >SA's get all the free GUI tools that allow them to look at channels and
> >logical/physical layout. Ask them about.
> >John
> >-Original Message-
> >Sent: 13 September 01 12:55
> >To: Multiple recipients of list ORACLE-L
> >
> >!! Please do not post Off Topic to this List !!
> >Hi All,
> >
> >Does anybody here on the list have experience with EMC/symmetrix storage
> >units.?
> >We have our databases on this machine and I have a feeling the the I/O
> >performance is not very good. I can not proof it since I do not have any
> >experience/data/access to that machine. We do however have a very
> >cooperative UNIX group but they also lack experience with performance on
> >this machine.
> >Who can give me pointers about I/O throughput that can be reached,
> >configuration pittfalls etc..
> >Example:
> >RS6000 8CPU's and 4Gb memory with storage on EMC/symmetrix. Job takes about
> >2 hours to complete.
> >F50 1 CPU 1Gb memory (TEST machine) local disks. same job takes 0.5 hours
> >to complete.
> >
> >Jack
> >=
> >De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
> >uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
> >vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
> >derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
> >Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
> >volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
> >voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
> >verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
> >worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
> >Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
> >vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
> >en het origineel en eventuele kopieën te verwijderen en te vernietigen.
> >Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
> >voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
> >algemene voorwaarden worden u op verzoek kosteloos toegezonden.
> >=
> >The information contained in this communication is confidential and is
> >intended solely for the use of the individual or entity to whom it is
> >addressed. You should not copy, disclose or distribute this communication
> >without the authority of Ernst & Young. Ernst & Young is neither liable for
> >the proper and complete transmission of the information contained in this
> >communication nor for any delay in its receipt. Ernst & Young does not
> >guarantee that the integrity of this communication has been maintained nor
> >that the communication is free of viruses, interceptions or interference.
> >If you are not the intended recipient of this communication please return
> >the communication to the sender and delete and destroy all copies.
> >In carrying out its engagements, Ernst & Young applies general terms and
> >conditions, which contain a clause that limits its liability. A copy of
> >these terms and conditions is avail

RE: 64 bit vs 32 bit Oracle

[Christopher Spence]

!! Please do not post Off Topic to this List !!

Depends, 64bit can certainly give you more throughput, but it is very slow
for being patched.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 3:50 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!


Is there any benefit of 64 bit versions over 32 bit versions of Oracle
beyond allowing > 2GB SGAs?  (8.1.7/Solaris 2.8 in particular)  The
systems I'm working with are predominantly DSS queries against data sets
that most of the servers have no hope of caching anywhere near the amount
of data needed so huge SGAs are not that useful.  Our C++ guys tell me
that Sun reccommends that compiling 64 bit binaries for processes that
might take advantage of the larger memory limit.  The logic being that 64
bit pointers take up twice as much space as 32 bit pointers and the
processor cache density is thus lower for 64 bit processes.

Does anyone know if there is any additional trickery Oracle does on the
64-bit port that may provide performance benefits that would outweigh the
potential for lower cache hit rates?  Magic data structures?  
_make_sql_faster support?

S-




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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Visio for ERD's ?

[Jon Baker]

Title: RE: Visio for ERD's ?





It ain't fun.  Can only used for graphical representation.  I guess better than using pen and paper.





-Original Message-
From: Ken Janusz [mailto:[EMAIL PROTECTED]]
Sent: Friday, September 14, 2001 3:35 PM
To: Multiple recipients of list ORACLE-L
Subject: Visio for ERD's ?



!! Please do not post Off Topic to this List !!


I would like to know if anyone is using Visio to re-engineer ERD's from an
Oracle DB?  Any problems, gotchas or recommendations are welcome


I have Visio Prof. 5c and will be using Oracle DB 8.1.7.


Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]


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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

v$parameter question for 9i users

[Weaver, Walt]

!! Please do not post Off Topic to this List !!

Howdy,

We haven't yet installed 9i anywhere here, but our developers are already
coding for it.

I was hoping someone could do me a favor and query v$option in Oracle 9i,
then send the output to me (or the list, doesn't matter to me). 

I need to know what changes, if any, have been made to the view, the
parameter values, and the case sensitivity of the values from 8i.

Thanks,
--Walt Weaver
  Bozeman, Montana
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Weaver, Walt
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: When optimizer reevaluate SQL statement

[Christopher Spence]

!! Please do not post Off Topic to this List !!

There are two types of parsing remember, soft and hard. 

Soft simply checks to see if anything has really changed.

Hard is when it allocates memory in the shared pool and goes through the
entire process of parsing.

Of course the hard parses are the big problems.


If the statement is another session, it simply does a soft parse as the
statement is the exact same and all the security and ownership is the same.

If the user is different, it is mostly true that the objects are different,
so it will need to do a hard parse to generate a plan under this user.

When you parse, you store the explain plan, let's say user a has a table
which has 1 million rows, an index is very efficient, so it uses this method
with the stored plan.  Another sql which is typed word for word the same but
as a different authenticated user logs in, but the table which has the same
name is only 1000 rows, which a full table scan is probably better, so it
stores it's explain plan.  Now where there are synonyms, it still generates
a new plan and parse phase the first time.

Now where you have largely skewed values histograms may provide better
performance than bind variables and make it all worth while to have it
reparse constantly.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 3:35 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

It still has to reparse some stuff for each new user: private synonyms (is
it really the same object being queried), user privileges.

Henry

-Original Message-
Sent: Friday, September 14, 2001 12:25 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Chris:

> If you are executing it under a different user the statement will be
reparsed.

I'm puzzled. I always thought that different users submitting the identical
SQL statement would use the same plan and not need to be reparsed. I
understood that to be part of the reasoning behind bind variables and the
big advantage of cursor sharing. Please explain.

Jon Walthour
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walthour, Jon (GEAE, Compaq)
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

IOUG-A deadline

[Adams, Matthew (GEA, 088130)]

Title: IOUG-A deadline





For those of you planning to submit papers,
today is the deadline for the call-for-papers
for IOUG-A 2002.


see   www.ioug.org


for more details

RE: Visio for ERD's ?

[Christopher Spence]

!! Please do not post Off Topic to this List !!

Let us know how it goes, I know a few people mentioned they were going to
play with that feature, but I never heard anything of it posted, I still use
ER/Win, although I have Visio.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 3:35 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

I would like to know if anyone is using Visio to re-engineer ERD's from an
Oracle DB?  Any problems, gotchas or recommendations are welcome

I have Visio Prof. 5c and will be using Oracle DB 8.1.7.

Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: When optimizer reevaluate SQL statement

[Henry Poras]

!! Please do not post Off Topic to this List !!

Chris,
I would just change that to "the execution plan [may] be different". In most
cases, it will be the same since the objects and security won't change (even
though they could).

Henry

-Original Message-
Sent: Friday, September 14, 2001 3:35 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Thanks, Chris. I've learned something.

Jon Walthour

-Original Message-
Sent: Friday, September 14, 2001 1:56 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

If the Oracle executing user is different, the execution plan will be
different due to possibility of different objects and security.

Different sessions under the same user can share using bind variables.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 12:25 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

Chris:

> If you are executing it under a different user the statement will be
reparsed.

I'm puzzled. I always thought that different users submitting the identical
SQL statement would use the same plan and not need to be reparsed. I
understood that to be part of the reasoning behind bind variables and the
big advantage of cursor sharing. Please explain.

Jon Walthour
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walthour, Jon (GEAE, Compaq)
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walthour, Jon (GEAE, Compaq)
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: OPS instalation - pretty urgent

[John Kanagaraj]

!! Please do not post Off Topic to this List !!

Gopal et al,

One of the main differences between Oracle on HP-UX/Solaris/Sequent and
Oracle on IBM is their use of the Post-wait driver which uses a
'test-and-set' lightweight mechanisn instead of semaphores. The other
difference (in this OS memory structure area) is that Shared Memory segments
need not be configured - there is of course an internal limit. I have never
encountered this limit though). This greatly reduces the risk of having to
restart the OS when adding databases to an existing box and the /etc/system
needs to be changed.

I don't want to start on the ease/strength/maturity of AIX's Logical Volume
Manager lest I trigger off a your-OS-vs-mine war!

John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

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


>You are right. IBM uses a light weight process 
>(post wait driver??) instead of typical System V
>semaphores. It is dynamically allocated and
>you don't need to explicitly set them in
>sys config files (Right John??)
>
>
>Best Regards,
>K Gopalakrishnan
>Bangalore, INDIA
>
>-Original Message-
>Mladen
>Sent: Thursday, September 13, 2001 1:45 PM
>To: Multiple recipients of list ORACLE-L
>
>
>!! Please do not post Off Topic to this List !!
>
>Don, failover, dispatcher cross registration and server load balancing
>require MTS. Second, I'm not on AIX, that is why I was guessing about
>semaphores.
>Here are my versions:
>
>SQL*Plus: Release 8.1.7.0.0 - Production on Thu Sep 13 15:36:53 2001
>
>(c) Copyright 2000 Oracle Corporation.  All rights reserved.
>
>Enter password:
>
>Connected to:
>Oracle8i Enterprise Edition Release 8.1.7.1.0 - 64bit Production
>With the Partitioning and Parallel Server options
>JServer Release 8.1.7.1.0 - 64bit Production
>
>SQL>
>
>HP-UX pdev1-a B.11.00 U 9000/800 
>
>I admit that my advice might be a little bit off when it comes to IBM.
>HP uses semaphores and we do have tons of those.
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Gogala, Mladen
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>
>_
>Do You Yahoo!?
>Get your free @yahoo.com address at http://mail.yahoo.com
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: K Gopalakrishnan
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: RMAN catalog Recreation Problems/Questions ....

[Anjan Thakuria]

!! Please do not post Off Topic to this List !!

Janardhana,

We had to do this recently.. I mean some thing similar.. This is what we have
now..

Since the latest version of the db is needed for the catalog, in other words it
is not upward compatible (Am I correct here ...) anyway..

The catalog db is in the latest version. which is 8.1.7.1

We have separate schemas for 816 and 817 in the db holding separate catalogs for
these different versions.

While upgrading from 8.0.5 to 8i I was aware of a problem in which the upgrade
catalog would not work as expected to.

Just registering the db again should work.

HTH

Anjan


Janardhana Babu wrote:

> !! Please do not post Off Topic to this List !!
>
> Dear List Members,
>
> I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog
> database
>   (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I
> have
>   no problem upgrading the production DB to 8.1.7.0.0 and then to 8.1.7.1.0
> patch
>   level.
>
>   The box on which catalog DB exists has another production DB which was
> upgraded
>   to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using
> normal
>   upgrade procedure. I must upgrade to base line release first(8.1.7.0.0)
> and
>   then patch needs to be applied. Since patch was already applied, my only
> option
>   could be to recreate the catalog database in 8.1.7.1.0 and then DO THE
> FULL
>   IMPORT. My questions would be:
>
>   [1] Do I need to do the FULL IMPORT of the catalog database? and then
> issue the
>   "upgrade catalog" command twice to upgrade the catalog to work with the
> latest
>   RMAN version? [OR]
>
>   [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow
> the
>   regular procedure to create RMAN schema and register the DB and then take
> the
>   fresh cold backup to start with, followed by the regular daily incremental
>
>   backups?
>
>   [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups.
> Is the
>   8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0?
>
>   Could some one please answer my above questions and suggest an appropriate
> procedure to
>   upgrade the catalog DB to 8.1.7.1.0.
>
> Thanks,
> -- Janardhana Babu
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Janardhana Babu
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

64 bit vs 32 bit Oracle

[Steve Rospo]

!! Please do not post Off Topic to this List !!


Is there any benefit of 64 bit versions over 32 bit versions of Oracle
beyond allowing > 2GB SGAs?  (8.1.7/Solaris 2.8 in particular)  The
systems I'm working with are predominantly DSS queries against data sets
that most of the servers have no hope of caching anywhere near the amount
of data needed so huge SGAs are not that useful.  Our C++ guys tell me
that Sun reccommends that compiling 64 bit binaries for processes that
might take advantage of the larger memory limit.  The logic being that 64
bit pointers take up twice as much space as 32 bit pointers and the
processor cache density is thus lower for 64 bit processes.

Does anyone know if there is any additional trickery Oracle does on the
64-bit port that may provide performance benefits that would outweigh the
potential for lower cache hit rates?  Magic data structures?  
_make_sql_faster support?

S-




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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Lots and lots of redo logs

[John Kanagaraj]

!! Please do not post Off Topic to this List !!


I am with John on this - For those with AIX, it is essential that the
Logical volume be created first and then the filesystem 
be created on this LV. It is via this route that you will be able to place
this LV in the 'center' (default). I have had great success placing
redologs, rbs, temp and system in the 'center' and all other tablespaces on
the 'edge'/'middle' when I was 'disk constrained' (read : I had only one 9Gb
disk for *all* user/oracle binaries and database :(

John Kanagaraj
Oracle Applications DBA
DB Soft Inc
Work : (408) 970 7002

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com

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


>-Original Message-
>From: John Lewis [mailto:[EMAIL PROTECTED]]
>Sent: Friday, September 14, 2001 10:45 AM
>To: Multiple recipients of list ORACLE-L
>Subject: RE: Lots and lots of redo logs
>
>
>!! Please do not post Off Topic to this List !!
>
>AIX has a unique concept of this. 
>
>Given data spread on a disk. The head will spend more time over the 
> middle tracks as it seeks data that is distributed across the disk.
>
>Thus for faster access, place your tablespace on the middle tracks
>of a disk because the probability that the head will be over it is
>much greater.
>
>Learned this with Syabase on AIX. It may still hold true today.
>I don't know. Technology had advanced a lot from those day. 
>(But I think the
>laws of physics are the same ;-))
>
>-Original Message-
>Sent: Friday, September 14, 2001 10:05 AM
>To: Multiple recipients of list ORACLE-L
>
>
>!! Please do not post Off Topic to this List !!
>
>Christopher R. Spence said:
>"Disks are cheap and if you store things on the outer platters 
>performance
>will suffer"?
>
>Chris, I'm not sure what you mean here. 
>
>The concept of Variable Transfer Rate covers this.
>
>Stealing a quote from: Optimal Storage Configuration Made Easy,
>By Juan Loaiza, Oracle Corporation
>
>"The transfer rate for a disk drive is not the same for all 
>portions of a
>disk.  
>The outer sectors of a disk drive move by the disk head faster than the
>inner sections 
>leading to a faster transfer rate for the outer sectors.  This 
>is simply
>because 
>of the circular shape of a disk drive."
>
>Scott
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Scott Crabtree
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: John Lewis
>  INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Kanagaraj
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: When optimizer reevaluate SQL statement

[Henry Poras]

!! Please do not post Off Topic to this List !!

It still has to reparse some stuff for each new user: private synonyms (is
it really the same object being queried), user privileges.

Henry

-Original Message-
Sent: Friday, September 14, 2001 12:25 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Chris:

> If you are executing it under a different user the statement will be
reparsed.

I'm puzzled. I always thought that different users submitting the identical
SQL statement would use the same plan and not need to be reparsed. I
understood that to be part of the reasoning behind bind variables and the
big advantage of cursor sharing. Please explain.

Jon Walthour
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walthour, Jon (GEAE, Compaq)
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Henry Poras
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: When optimizer reevaluate SQL statement

[Walthour, Jon (GEAE, Compaq)]

!! Please do not post Off Topic to this List !!

Thanks, Chris. I've learned something.

Jon Walthour

-Original Message-
Sent: Friday, September 14, 2001 1:56 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

If the Oracle executing user is different, the execution plan will be
different due to possibility of different objects and security.

Different sessions under the same user can share using bind variables.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 12:25 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

Chris:

> If you are executing it under a different user the statement will be
reparsed.

I'm puzzled. I always thought that different users submitting the identical
SQL statement would use the same plan and not need to be reparsed. I
understood that to be part of the reasoning behind bind variables and the
big advantage of cursor sharing. Please explain.

Jon Walthour
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walthour, Jon (GEAE, Compaq)
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the
message BODY, include a line containing: UNSUB ORACLE-L (or the name of
mailing list you want to be removed from).  You may also send the HELP
command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walthour, Jon (GEAE, Compaq)
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Visio for ERD's ?

[Ken Janusz]

!! Please do not post Off Topic to this List !!

I would like to know if anyone is using Visio to re-engineer ERD's from an
Oracle DB?  Any problems, gotchas or recommendations are welcome

I have Visio Prof. 5c and will be using Oracle DB 8.1.7.

Thanks,
Ken Janusz, CPIM
Database Conversion Lead
Sufficient Systems, Inc.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ken Janusz
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Strange performance problem

[Richard Ji]

!! Please do not post Off Topic to this List !!

Did you check to see if there is anything else running on the server that might take
resource away from Oracle?  It has happened to me once that the SA was
running something that he shouldn't and it's using a lot of system resources.

HTH

>>> [EMAIL PROTECTED] 09/14/01 03:05PM >>>
!! Please do not post Off Topic to this List !!


I have a nightly load job that was being tracked by our developers.
According to their nightly logs (going back months), a query was running
as far back as they can record with a sub-second response time.

Then on a particular date (Aug. 23rd), the query started taking more
than 20 minutes to complete.   It has taken that long to complete ever
since.

I looked at the explain plan and it looks o.k.   Indexes are being used
and there are no suspicious full table scans.  The init.ora file has not
changed
since then.

We restored a full copy of the database to an alternate host using rman.
It should be an exact copy as of Aug. 16th.   I ran the query on the copy
and
on the current production database and the resulting explain plans were
identical except for the number of rows returned.   Total execution time
and cpu times were similar.

I looked through our change documentation and I do not see any record
of data structure changes or any data changes at all in the database
in question.

I am sort of at a loss for what to try next.   What sort of changes might
cause such an extreme degradation in performance as this?

This is an 8.1.7 database on Sun Solaris 2.8.  The optimization is
rule-based.
No partitioning.   Database is about 80 Gig in size.   Following is the
explain
plan, if anyone is interested:

SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID,
ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID,
ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID,
ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM,
ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM
FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.26   0.27  0  0  0
0
Execute  2  0.01   0.01  0  0  1
0
Fetch  128982.191026.27 1454639732999  55484
1897
--- --   -- -- -- --
--
total  131982.461026.55 1454639732999  55485
1897

Rows Row Source Operation
---  ---
   1897  FILTER
   2041   NESTED LOOPS
   2422HASH JOIN
   2341 NESTED LOOPS
   2342  NESTED LOOPS
   2338   NESTED LOOPS
   2338NESTED LOOPS
   2346 NESTED LOOPS
   2510  NESTED LOOPS
   2510   NESTED LOOPS
   2510INDEX FAST FULL SCAN (object id 17279)
   5018INDEX UNIQUE SCAN (object id 17278)
   5018   TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
   5018INDEX UNIQUE SCAN (object id 17266)
   4854  INDEX RANGE SCAN (object id 17270)
   4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN
   4682  INDEX RANGE SCAN (object id 17283)
   4674VIEW ACTIVE_EAS_RPT_PROF_VIEW
 100491 SORT UNIQUE
43  UNION-ALL
 10   TABLE ACCESS FULL EAS_RPT_PROF
 33   FILTER
 34NESTED LOOPS
734 NESTED LOOPS
 207976  NESTED LOOPS
 207976   MERGE JOIN CARTESIAN
706INDEX FAST FULL SCAN (object id 17270)
 208680SORT JOIN
295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
 415950   TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
 415950INDEX UNIQUE SCAN (object id 17266)
 208708  INDEX UNIQUE SCAN (object id 17275)
766 TABLE ACCESS FULL EAS_RPT_PROF
   4678   TABLE ACCESS FULL USER_SIGNON
   2341  INDEX UNIQUE SCAN (object id 17275)
295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
   4461VIEW ACTIVE_EAS_PERSON_VIEW
2675205 SORT UNIQUE
   1105  UNION-ALL
128   NESTED LOOPS
   1107INDEX RANGE SCAN (object id 17284)
128TABLE ACCESS BY INDEX ROWID EAS_PERSON
   2212 INDEX UNIQUE SCAN (object id 17277)
977   FILTER
   1008NESTED LOOPS
 288511 NESTED LOOPS
 326271  MERGE JOIN CARTESIAN
   1107   INDEX RANGE SCAN (object id 17284)
 327376   SORT JOIN
295TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
 614780  TABLE ACCESS BY INDEX ROWID EAS_PERSON
 652540   INDEX UNIQUE SCAN (object id 17277)
 289517 INDEX UNIQUE SCAN (object id 17275)
540   SORT AGGREGATE
287TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG
557 INDEX RANGE SCAN (object id 17276)
1346   SORT AGGREGATE
737TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP_STS_LOG
   1412   

RE: lots and lots of redo logs

[DENNIS WILLIAMS]

!! Please do not post Off Topic to this List !!

Christopher - I for one appreciate your efforts to share your knowledge. I
suppose that like most of us you have employment in addition to this list.
The best most of us can manage is to jot off a quick reply without benefit
of extensive research. The tradeoff as I see it is between the occasional
not-quite-complete answer versus many unanswered questions. And if a point
is incorrect, there is usually someone that will provide a correction. Keep
up the good work!

Dennis Williams
DBA
Lifetouch, Inc.
[EMAIL PROTECTED]


-Original Message-
Sent: Friday, September 14, 2001 12:37 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

I forgot the word default; I know 8 used to be the default.  I tend to be
doing many different things when I respond to emails.  I guess it would be
much easier if I didn't post at all.  Not like I ever ask questions on the
list.

But thanks for your flame, specially the one you sent privately stating you
wish me to get kicked off the list the other day.  Keep up the good work.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 12:21 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!


Christopher R. Spence said:
You can only have a max of 8 log groups if I remember correctly.

Gee, I thought that the DBA controlled the maximum number of redo logs
allowed for a database.
There's this thing called a database create statement: (e.g.)


CREATE DATABASE DEV
LOGFILE 'E:\Oracle\oradata\DEV\redo01.log' SIZE 16384K,
'E:\Oracle\oradata\DEV\redo02.log' SIZE 16384K,
'E:\Oracle\oradata\DEV\redo03.log' SIZE 16384K,
'E:\Oracle\oradata\DEV\redo04.log' SIZE 16384K
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
DATAFILE 'F:\Oracle\oradata\EMS\system01.dbf' SIZE 320M  REUSE AUTOEXTEND ON
NEXT 32768K
MAXDATAFILES 128
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET WE8ISO8859P1;

Funny, how reading the documentation or just looking at your create scripts
can answer lots of questions before just simply spewing email. Personally, I
try to send fewer, higher quality emails rather than dozens of things that
are the first thing that pops into my head.

Paul

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: encrption of a column in a table, for V7.3

[Christopher Spence]

Title: encrption of a column in a table, for V7.3









http://www.cybcon.com/~jkstill/util/util_master.html

 

There is some explanation of blowfish
encryption via PL/SQL.

 

 

Also look here as it has some information
of password encryption which makes some notes of v7 v8

 

http://osi.oracle.com/~tkyte/Misc/Passwords.html

 

 



"Do not criticize someone until you walked a
mile in their shoes, that way when you criticize them, you are a mile a way and
have their shoes."

Christopher R. Spence 
Oracle DBA 
Phone: (978) 322-5744 
Fax:    (707) 885-2275 

Fuelspot 
73 Princeton Street 
  North, Chelmsford 01863 
  



-Original Message-
From: Chen, Bill
[mailto:[EMAIL PROTECTED]] 
Sent: Friday, September 14, 2001 2:56 PM
To: Multiple recipients of list ORACLE-L
Subject: encrption of a column in
a table, for V7.3

 

 

Are anybody aware of any product which will encrypt a
column in a table for Oracle V7? 
Something like the
DBMS_OBFUSCATION_TOOLKIT package (available V8.1.6+). 

Thanks. 
Bill 

RE: Is Virus protection software safe on an NT / Oracle

[Paul Drake]

!! Please do not post Off Topic to this List !!

Rodd Holman wrote:

With the advent of worms like Code Red, I would recommend it.   You don't 
need users on the server anymore to get a virus.  All you need is an OS 
full of holes and the system open to the internet.

Rodd Holman


Hi.

I've found a very effective method for not needing virus protection for an
Oracle/NT Server:

1. remove all shares
2. install remote control software
3. remove the monitor
4. only give people in the ORA_DBA group the remote control password.

I do not know of any virus that travels over sql*net/Net8.
Apply the current patch sets (NOT the post SP6a roll-up), put a password on
the listeners.
If IIS tools are not installed, if a mail client is not installed, if
browsers are not used, if console access is not disabled - virus protection
software is not needed.
Dedicate the box to Oracle only.
Create bogus tablespaces to exhaust the free space, then drop them, leaving
the dead datafiles behind to be re-used later.

Paul

btw - beware - NT4 post-SP6a roll-up completely hosed my workstation.
(I'm assuming that you've already disabled unneccesary services)



>> Original Message <<


On 9/14/01, 12:50:32 PM, "Boivin," Patrice J <[EMAIL PROTECTED]>
wrote regarding RE: Is Virus protection software safe on an NT / Oracle 
server?:



> !! Please do not post Off Topic to this List !!


> It will slow down your server.


> With McAfee, you can exclude the directories that contain oracle 

datafiles,

> that minimizes the impact.


> Virus checking software is a good idea, in my opinion.


> Regards,
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)


> Acting Head
> Systems Admin & Operations | Admin. et Exploit. des systèmes
> Technology Services| Services technologiques
> Informatics Branch | Direction de l'informatique
> Maritimes Region, DFO  | Région des Maritimes, MPO


> E-Mail: [EMAIL PROTECTED] 







> -Original Message-
> From:   Smith, Ron L. [SMTP:[EMAIL PROTECTED]]
> Sent:   Friday, September 14, 2001 1:41 PM
> To: Multiple recipients of list ORACLE-L
> Subject:Is Virus protection software safe on an NT / Oracle
> server?


> !! Please do not post Off Topic to this List !!


> Our NT Server admins have been told by the Security department that
> they
> need to install virus protection software on the NT / Oracle
> servers.  Does
> anyone know of any problems between Oracle and any virus software?


> Ron Smith
> Database Administrator
> [EMAIL PROTECTED]


> --
> 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  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing
> Lists
> 



> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like 

subscribing).

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


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

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

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED]

Re:Oracle on OS390/MVS

[dgoulet]

!! Please do not post Off Topic to this List !!

Kim,

Regrettably I have no experience with an OS390 machine, but as far as Oracle
vs DB2 we did look into that option a couple of months ago.  From a cost
perspective DB2 is no cheaper than Oracle once you toss in all of the goodies
that Oracle sends along gratis.  From an operational perspective I found getting
everything configured and running smoothly a real pain in DB2 and much easier in
Oracle.

Dick Goulet

Reply Separator
Author: [EMAIL PROTECTED]
Date:   9/14/2001 10:35 AM

!! Please do not post Off Topic to this List !!

 We're looking at possibly running Oracle on our mainframe, but the 
 perception exists that a more optimum solution would be DB2.  I would 
 appreciate hearing from anyone that is running Oracle on OS390, and/or 
 made a decision between DB2 and Oracle.
 
 Thanks -
 
 Kim Thompson
 City and County of San Francisco
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: CBO - default no of rows

[John Kanagaraj]

!! Please do not post Off Topic to this List !!

John et al,

The figures below for CBO defaults was quoted by Mogens Norgaard in another
list (OAUG-DBA) in reply to a query about the CBO on 8.0.6. Apparently, this
is the most current value from a public technical note (public at least
sometime (??) back).

John Kanagaraj 
Oracle Applications DBA 
DB Soft Inc 
Work : (408) 970 7002 

Listen to great, commercial-free christian music 24x7x365 at
http://www.klove.com   

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

-- Quoted note ---

If the CBO doesn't have stats on a given table/index/column it will use
default values hard-coded in Oracle. It will not

perform any analyze...estimate operations on the objects. Here's the most
current list of hard-coded values I have seen:

=

/* defaults */

/* Default selectivities are set low to

1. keep cost values low for future resource limiter use

2. keep cost values low for permutation cutoff in kko

Defaults are used for bind variables, general expressions and

unanalyzed tables, except for equality where defaults are not

needed for bind variables.

*/

#define KKEDSREL 0.05 /* default selectivity for < <= > >= */

#define KKEDSEQ 0.01 /* default selectivity for = */

#define KKEDSNE 0.05 /* default selectivity for != */

#define KKEDSDF 0.05 /* default selectivity for all other ops */

#define KKEDSIRL 0.009 /* default selectivity for relation on indexed col */

#define KKEDSBRL 0.009 /* def sel for relation with bind var on index col*/

#define KKEDSIEQ 0.004 /* default selectivity for = on indexed col */

#define KKEDMBR 8 /* default multiblock read factor */

#define KKEDMBW 8 /* default multiblock write factor */

#define KKEDFNR 100.0 /* default - fixed table cardinality */

#define KKEDFRL 20 /* default - fixed table row length */

#define KKEDDNR 2000.0 /* default - remote table cardinality */

#define KKEDDRL 100 /* default - remote table avg row length */

#define KKEDDNB 100 /* default - default # of blocks */

#define KKEDDSC 13.0 /* default - default scan cost */

#define KKEDILV 1 /* default - default index levels */

#define KKEDILB 25 /* default - number of index leaf blocks */

#define KKEDLBK 1 /* default - number leaf blocks/key */

#define KKEDDBK 1 /* default - number of data blocks/key */

#define KKEDKEY 100 /* default - number of distinct keys */

#define KKEDCLF (KKEDDNB*8) /* default - clustering factor */

#define KKECRI 1.5 /* remote table access cost increase factor */

#define KKECFSC 1.0 /* fixed table scan cost */

#define KKECFNB 0 /* fixed table number of blocks */

#define KKECMXB 15 /* maximum byte length for normalization */

#define KKECBBS 256.0 /* base for byte sequence normalization */

#define KKECSPC ' ' /* space byte value */

#define KKECSPD 86400.0 /* seconds per day */

#define KKESROH 10.0 /* sort per row overhead in bytes */

#define KKESAUT 0.75 /* sort area utilization */

#define KKESROP 0.10 /* sort row overhead percent */

#define KKESRML 2.0 /* sort run multiple */

#define KKESTP 0x01 /* single table predicate */

#define KKETEQ 0x02 /* equi join */

#define KKETBCPJ 0x04 /* Cartesian product join */

#define KKESOK 0x08 /* input swap ok */

#define KKESWP 0x10 /* inputs swapped */

#define KKEEQP 0x20 /* equipartitioned */

#define KKELKNWC 0x01 /* LIKE no wild card */

#define KKELKTWC 0x02 /* LIKE trailing wild card */

#define KKELKEWC 0x04 /* LIKE embedded wild card */

#define KKELKLWC 0x08 /* LIKE leading wild card */

#define KKELKOWC 0x10 /* LIKE only wild card */

==

-Original Message-
Sent: Friday, September 14, 2001 3:25 AM
To: Multiple recipients of list ORACLE-L



My question of the day is :- 
What value does the CBO use as a default number of rows for a table. 

Background: - 

We all know that if any tables in a query have been analyzed then CBO is
used for the query not RBO  (couple of caveats I know but let's continue).

So if 3 tables are used in a query and table a has 500 rows (analyzed) table
b has 50 rows (never analyzed) and table c has 350 rows (never analyzed)
all things being equal then CBO is used but what values does the CBO use for
tables b or c to decide which execution plan is best.

On a development system yesterday a query was running slow. I realised that
we had put in a  very large data load (3.5M rows). I analyzed the table and
indexes and the query came back in sub second response time. I am trying to
figure what value was being used for num_rows prior to the analyze. It
cannot be based on blocks allocated/used as thet would have increased after
the dataload


Thanks 


John 


Oracle DBA 
BTcellnet 
* [EMAIL PROTECTED] 
* 0113 388 6062Desk 
* 07713 066194  BT Mobile 




**

RE: lots and lots of redo logs

[Christopher Spence]

!! Please do not post Off Topic to this List !!

It is default to 8, unless you change it by rebuilding the control file or
init.ora, depending on the version.

Although 8 is platform dependant.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 2:36 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

I think that Chris was referring to the maximum number of redo log group one
COULD create - an Oracle hard limit.  I don't know what it is, but I know
its
not 8 since I have created OPS databases with 12 redo groups for each
instance
and multiple instances.

-Don Granaman
[Orasaurus - Honk if you remember UFI!]

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 11:20 AM


> !! Please do not post Off Topic to this List !!
>
>
> Christopher R. Spence said:
> You can only have a max of 8 log groups if I remember correctly.
>
> Gee, I thought that the DBA controlled the maximum number of redo logs
> allowed for a database.
> There's this thing called a database create statement: (e.g.)
>
>
> CREATE DATABASE DEV
> LOGFILE 'E:\Oracle\oradata\DEV\redo01.log' SIZE 16384K,
> 'E:\Oracle\oradata\DEV\redo02.log' SIZE 16384K,
> 'E:\Oracle\oradata\DEV\redo03.log' SIZE 16384K,
> 'E:\Oracle\oradata\DEV\redo04.log' SIZE 16384K
[...]

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Strange performance problem

[Cherie_Machler]

!! Please do not post Off Topic to this List !!


I have a nightly load job that was being tracked by our developers.
According to their nightly logs (going back months), a query was running
as far back as they can record with a sub-second response time.

Then on a particular date (Aug. 23rd), the query started taking more
than 20 minutes to complete.   It has taken that long to complete ever
since.

I looked at the explain plan and it looks o.k.   Indexes are being used
and there are no suspicious full table scans.  The init.ora file has not
changed
since then.

We restored a full copy of the database to an alternate host using rman.
It should be an exact copy as of Aug. 16th.   I ran the query on the copy
and
on the current production database and the resulting explain plans were
identical except for the number of rows returned.   Total execution time
and cpu times were similar.

I looked through our change documentation and I do not see any record
of data structure changes or any data changes at all in the database
in question.

I am sort of at a loss for what to try next.   What sort of changes might
cause such an extreme degradation in performance as this?

This is an 8.1.7 database on Sun Solaris 2.8.  The optimization is
rule-based.
No partitioning.   Database is about 80 Gig in size.   Following is the
explain
plan, if anyone is interested:

SELECT ACTV_EAS_PERS_RPT_PROF_VIEW.LOGIN_ID,
ACTV_EAS_PERS_RPT_PROF_VIEW.ACCT_GRP_ID,
ACTV_EAS_PERS_RPT_PROF_VIEW.RPT_PROF_ID,
ACTV_EAS_PERS_RPT_PROF_VIEW.INS_DT_TM,
ACTV_EAS_PERS_RPT_PROF_VIEW.UPD_DT_TM
FROM GELCO.ACTV_EAS_PERS_RPT_PROF_VIEW

call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.26   0.27  0  0  0
0
Execute  2  0.01   0.01  0  0  1
0
Fetch  128982.191026.27 1454639732999  55484
1897
--- --   -- -- -- --
--
total  131982.461026.55 1454639732999  55485
1897

Rows Row Source Operation
---  ---
   1897  FILTER
   2041   NESTED LOOPS
   2422HASH JOIN
   2341 NESTED LOOPS
   2342  NESTED LOOPS
   2338   NESTED LOOPS
   2338NESTED LOOPS
   2346 NESTED LOOPS
   2510  NESTED LOOPS
   2510   NESTED LOOPS
   2510INDEX FAST FULL SCAN (object id 17279)
   5018INDEX UNIQUE SCAN (object id 17278)
   5018   TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
   5018INDEX UNIQUE SCAN (object id 17266)
   4854  INDEX RANGE SCAN (object id 17270)
   4682 TABLE ACCESS BY INDEX ROWID EAS_PERSON_RPT_PROF_ASSGN
   4682  INDEX RANGE SCAN (object id 17283)
   4674VIEW ACTIVE_EAS_RPT_PROF_VIEW
 100491 SORT UNIQUE
43  UNION-ALL
 10   TABLE ACCESS FULL EAS_RPT_PROF
 33   FILTER
 34NESTED LOOPS
734 NESTED LOOPS
 207976  NESTED LOOPS
 207976   MERGE JOIN CARTESIAN
706INDEX FAST FULL SCAN (object id 17270)
 208680SORT JOIN
295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
 415950   TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP
 415950INDEX UNIQUE SCAN (object id 17266)
 208708  INDEX UNIQUE SCAN (object id 17275)
766 TABLE ACCESS FULL EAS_RPT_PROF
   4678   TABLE ACCESS FULL USER_SIGNON
   2341  INDEX UNIQUE SCAN (object id 17275)
295 TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
   4461VIEW ACTIVE_EAS_PERSON_VIEW
2675205 SORT UNIQUE
   1105  UNION-ALL
128   NESTED LOOPS
   1107INDEX RANGE SCAN (object id 17284)
128TABLE ACCESS BY INDEX ROWID EAS_PERSON
   2212 INDEX UNIQUE SCAN (object id 17277)
977   FILTER
   1008NESTED LOOPS
 288511 NESTED LOOPS
 326271  MERGE JOIN CARTESIAN
   1107   INDEX RANGE SCAN (object id 17284)
 327376   SORT JOIN
295TABLE ACCESS FULL EAS_CLNT_GRP_STS_LOG
 614780  TABLE ACCESS BY INDEX ROWID EAS_PERSON
 652540   INDEX UNIQUE SCAN (object id 17277)
 289517 INDEX UNIQUE SCAN (object id 17275)
540   SORT AGGREGATE
287TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG
557 INDEX RANGE SCAN (object id 17276)
1346   SORT AGGREGATE
737TABLE ACCESS BY INDEX ROWID EAS_ACCT_GRP_STS_LOG
   1412 INDEX RANGE SCAN (object id 17270)
   3938   SORT AGGREGATE
   2066TABLE ACCESS BY INDEX ROWID EAS_PERSON_ASSGN_STS_LOG
   4035 INDEX RANGE SCAN (object id 17279)
680SORT AGGREGATE
355 TABLE ACCESS BY INDEX ROWID EAS_CLNT_GRP_STS_LOG
696  INDEX RANGE SCAN (object id 17276)
   2614 

encrption of a column in a table, for V7.3

[Chen, Bill]

Title: encrption of a column in a table, for V7.3






Are anybody aware of any product which will encrypt a column in a table for Oracle V7?
Something like the DBMS_OBFUSCATION_TOOLKIT package (available V8.1.6+).


Thanks.
Bill

Re: lots and lots of redo logs

[Don Granaman]

!! Please do not post Off Topic to this List !!

I think that Chris was referring to the maximum number of redo log group one
COULD create - an Oracle hard limit.  I don't know what it is, but I know its
not 8 since I have created OPS databases with 12 redo groups for each instance
and multiple instances.

-Don Granaman
[Orasaurus - Honk if you remember UFI!]

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 11:20 AM


> !! Please do not post Off Topic to this List !!
>
>
> Christopher R. Spence said:
> You can only have a max of 8 log groups if I remember correctly.
>
> Gee, I thought that the DBA controlled the maximum number of redo logs
> allowed for a database.
> There's this thing called a database create statement: (e.g.)
>
>
> CREATE DATABASE DEV
> LOGFILE 'E:\Oracle\oradata\DEV\redo01.log' SIZE 16384K,
> 'E:\Oracle\oradata\DEV\redo02.log' SIZE 16384K,
> 'E:\Oracle\oradata\DEV\redo03.log' SIZE 16384K,
> 'E:\Oracle\oradata\DEV\redo04.log' SIZE 16384K
[...]

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: find out the rollback

[Christopher Spence]

!! Please do not post Off Topic to this List !!

200,000 @ 50 bytes, I would say just over 40Mb of rollback.

Not sure how you came up with 21akh to 200,000 :)

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 2:20 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

200, (two hundred thousand:)

English is indeed an great language as it changes in
its nuances as geographic locations change ..its
friday finally!

srinivas for estimating rollback, consider taking
snapshots of v$rollstat.waits (specified in bytes)
before and after a sample delete on your table and
extrapolate to identify the amount of rollback you
would require for successfully completing your
operation 

hth
Deepak
--- Christopher Spence <[EMAIL PROTECTED]> wrote:
> !! Please do not post Off Topic to this List !!
> 
> What is 2lakh rows?  Never heard of that number
> metric.
> 
> "Do not criticize someone until you walked a mile in
> their shoes, that way
> when you criticize them, you are a mile a way and
> have their shoes."
> 
> Christopher R. Spence 
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
> 
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>  
> 
> 
> -Original Message-
> [mailto:[EMAIL PROTECTED]] 
> Sent: Thursday, September 13, 2001 12:45 PM
> To: Multiple recipients of list ORACLE-L
> 
> !! Please do not post Off Topic to this List !!
> 
> Hi lists
> 
> can anybody post me how much rollback (approx in
> bytes/kb/mb) will be
> generated if I delete my table that has 2 lakh rows.
> 
> the avg row len is 50.
> 
> often The query is getting failed due to the error
> unable to extend the
> rollback segment.
> 
> If I have this query, at least I will use a rollback
> segment that has
> enough extents.
> 
> thnx in advance.
> 
> Srinivas
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Tatireddy, Shrinivas (MED, Keane)
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deepak Thapliyal
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP comma

DAP and Oracle Applications Maintenance pack

[Boivin, Patrice J]

!! Please do not post Off Topic to this List !!

Fyi, 

This is the server that doesn't allow parallel downloading with Download
Accelerator Plus:



ap103aru.us.oracle.com

What's different about that one, I wonder?

Anyway, attempt #1 at downloading patch 11.5.5, 11.5.4. failed three times
so far.

I will let you know how it goes.  That server is not as busy as it was last
week, though - maybe I will be successful.

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Acting Head
Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique 
Maritimes Region, DFO  | Région des Maritimes, MPO

E-Mail: [EMAIL PROTECTED]  


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: RMAN catalog Recreation Problems/Questions ....

[Janardhana Babu]

!! Please do not post Off Topic to this List !!

Ruth,

Iam facing the follwing problems/doubts how to upgrade the catalog now. 
Please try to help me if you can:

I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog
database 
(8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I
have
no problem upgrading the production DB to 8.1.7.0.0 and then to 8.1.7.1.0
patch
level.
 
The box on which catalog DB exists has another production DB which was
upgraded
to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using normal
upgrade procedure. I must upgrade to base line release first(8.1.7.0.0) and
then patch needs to be applied. Since patch was already applied, my only
option
could be to recreate the catalog database in 8.1.7.1.0 and then DO THE FULL
IMPORT. My questions would be:

[1] Do I need to do the FULL IMPORT of the catalog database? and then issue
the
"upgrade catalog" command twice to upgrade the catalog to work with the
latest
RMAN version? [OR]

[2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow the
regular procedure to create RMAN schema and register the DB and then take
the
fresh cold backup to start with, followed by the regular daily incremental
backups?

[3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups. Is
the
8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0?

Please answer my above questions and suggest an appropriate procedure to
upgrade the catalog DB to 8.1.7.1.0

-Original Message-
Sent: Friday, September 14, 2001 11:05 AM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

I think this is what you want to know:  you should upgrade your rman
database to the highest version of the databases being backed up with rman.

Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 12:45 PM


> !! Please do not post Off Topic to this List !!
>
> Dear List Members,
>
> I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog
> database
>   (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0.
I
> have
>   no problem upgrading the production DB to 8.1.7.0.0 and then to
8.1.7.1.0
> patch
>   level.
>
>   The box on which catalog DB exists has another production DB which was
> upgraded
>   to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using
> normal
>   upgrade procedure. I must upgrade to base line release first(8.1.7.0.0)
> and
>   then patch needs to be applied. Since patch was already applied, my only
> option
>   could be to recreate the catalog database in 8.1.7.1.0 and then DO THE
> FULL
>   IMPORT. My questions would be:
>
>   [1] Do I need to do the FULL IMPORT of the catalog database? and then
> issue the
>   "upgrade catalog" command twice to upgrade the catalog to work with the
> latest
>   RMAN version? [OR]
>
>   [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow
> the
>   regular procedure to create RMAN schema and register the DB and then
take
> the
>   fresh cold backup to start with, followed by the regular daily
incremental
>
>   backups?
>
>   [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups.
> Is the
>   8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0?
>
>   Could some one please answer my above questions and suggest an
appropriate
> procedure to
>   upgrade the catalog DB to 8.1.7.1.0.
>
> Thanks,
> -- Janardhana Babu
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Janardhana Babu
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Janardhana Babu
  INET: [EMAIL PROTECTED]

Fa

RE: Is Virus protection software safe on an NT / Oracle server?

[Rodd Holman]

!! Please do not post Off Topic to this List !!

With the advent of worms like Code Red, I would recommend it.   You don't 
need users on the server anymore to get a virus.  All you need is an OS 
full of holes and the system open to the internet.

Rodd Holman

>> Original Message <<

On 9/14/01, 12:50:32 PM, "Boivin," Patrice J <[EMAIL PROTECTED]> 
wrote regarding RE: Is Virus protection software safe on an NT / Oracle 
server?:


> !! Please do not post Off Topic to this List !!

> It will slow down your server.

> With McAfee, you can exclude the directories that contain oracle 
datafiles,
> that minimizes the impact.

> Virus checking software is a good idea, in my opinion.

> Regards,
> Patrice Boivin
> Systems Analyst (Oracle Certified DBA)

> Acting Head
> Systems Admin & Operations | Admin. et Exploit. des systèmes
> Technology Services| Services technologiques
> Informatics Branch | Direction de l'informatique
> Maritimes Region, DFO  | Région des Maritimes, MPO

> E-Mail: [EMAIL PROTECTED] 






>   -Original Message-
>   From:   Smith, Ron L. [SMTP:[EMAIL PROTECTED]]
>   Sent:   Friday, September 14, 2001 1:41 PM
>   To: Multiple recipients of list ORACLE-L
>   Subject:Is Virus protection software safe on an NT / Oracle
> server?

>   !! Please do not post Off Topic to this List !!

>   Our NT Server admins have been told by the Security department that
> they
>   need to install virus protection software on the NT / Oracle
> servers.  Does
>   anyone know of any problems between Oracle and any virus software?

>   Ron Smith
>   Database Administrator
>   [EMAIL PROTECTED]

>   --
>   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  FAX: (858) 538-5051
>   San Diego, California-- Public Internet access / Mailing
> Lists
>   

>   To REMOVE yourself from this mailing list, send an E-Mail message
>   to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>   the message BODY, include a line containing: UNSUB ORACLE-L
>   (or the name of mailing list you want to be removed from).  You may
>   also send the HELP command for other information (like 
subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Boivin, Patrice J
>   INET: [EMAIL PROTECTED]

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Tablespace (datafile reducing)

[Don Granaman]

!! Please do not post Off Topic to this List !!

And I have built TB+ sized databases using no datafiles larger than 2 GB.  At
this level, larger files are worthy of consideration, but they are certainly not
required - or even critical.  "It depends..."

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 10:25 AM


!! Please do not post Off Topic to this List !!

Datafile sizing is also dependent on the size of your db.  I use 10gb datafile
size's for a 1tb database.


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Lots and lots of redo logs

[Bill Buchan]

!! Please do not post Off Topic to this List !!


Excellent.  Thanks for the information; maybe I won't fill my whole disk 
with redo now!

- Bill.

At 08:40 14/09/01 -0800, you wrote:
>!! Please do not post Off Topic to this List !!
>
>Generally IDE shows this problem much more than scsi, but on IDE you can see
>as much as 50% performance degrading, I haven't really tested the
>difference.
>
>If you put the logs on, then fill the rests of the disks with a empty file
>of that size, you can make sure that the end of the disk is filled with junk
>and the logs will never pull blocks from that area.
>

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: RMAN catalog Recreation Problems/Questions ....

[Ruth Gramolini]

!! Please do not post Off Topic to this List !!

I think this is what you want to know:  you should upgrade your rman
database to the highest version of the databases being backed up with rman.

Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 12:45 PM


> !! Please do not post Off Topic to this List !!
>
> Dear List Members,
>
> I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog
> database
>   (8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0.
I
> have
>   no problem upgrading the production DB to 8.1.7.0.0 and then to
8.1.7.1.0
> patch
>   level.
>
>   The box on which catalog DB exists has another production DB which was
> upgraded
>   to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using
> normal
>   upgrade procedure. I must upgrade to base line release first(8.1.7.0.0)
> and
>   then patch needs to be applied. Since patch was already applied, my only
> option
>   could be to recreate the catalog database in 8.1.7.1.0 and then DO THE
> FULL
>   IMPORT. My questions would be:
>
>   [1] Do I need to do the FULL IMPORT of the catalog database? and then
> issue the
>   "upgrade catalog" command twice to upgrade the catalog to work with the
> latest
>   RMAN version? [OR]
>
>   [2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow
> the
>   regular procedure to create RMAN schema and register the DB and then
take
> the
>   fresh cold backup to start with, followed by the regular daily
incremental
>
>   backups?
>
>   [3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups.
> Is the
>   8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0?
>
>   Could some one please answer my above questions and suggest an
appropriate
> procedure to
>   upgrade the catalog DB to 8.1.7.1.0.
>
> Thanks,
> -- Janardhana Babu
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Janardhana Babu
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Automanagement of extent sizing

[Don Granaman]

!! Please do not post Off Topic to this List !!

Prior to the advent of 8i, uniform extents had to be enforced through making
sure that every extent in a given tablespace was the same size by using
appropriate values in the storage clause in effect at the time of object
creation.  The easiest method was to just set  initial = next and pctincrease =
0 in the tablespace default storage clause and prohibit the use of these
particular parameters in individual object creation scripts.  The DBA often had
to review all object creation scripts and remove/fix any offending parameters.
(When using Designer, I could run custom scripts against the API to do the
"cleanup" though.)  LMTs and the ability to declaratively enforce "uniform
extents" eliminated this grunge work.  Now we just have to decide which
tablespace is appropriate.

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

PS:  When others heard about this, they often replied "But SMON won't coalesce
free space if pctincrease=0 at the tablespace level!".  My universal reply:
"Good!  I don't want it to!"

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 7:50 AM


> !! Please do not post Off Topic to this List !!
>
>
> Don,
>
> Thanks for your comprehensive reply.   Could you elaborate on how you
> enforce uniform extents through controlling the DDL?
> What do you mean by that?
>
> Thanks,
>
> Cherie


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Oracle on OS390/MVS

[Kim_Thompson]

!! Please do not post Off Topic to this List !!

 We're looking at possibly running Oracle on our mainframe, but the 
 perception exists that a more optimum solution would be DB2.  I would 
 appreciate hearing from anyone that is running Oracle on OS390, and/or 
 made a decision between DB2 and Oracle.
 
 Thanks -
 
 Kim Thompson
 City and County of San Francisco
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Locally managed tablespace

[Kathy Duret]

!! Please do not post Off Topic to this List !!

How are you to create the rollback segments?  Is this the same or different from the 
other LMT tablespaces.  I see where the Temporary Tablespaces are different.  

Any other good LMT articles besides the one below?  I want to change our database into 
LMT and can you believe this was a brand new 8.1.6 database created in April and they 
used LONG datatypes and other old architecture.  

Kathy

-Original Message-
Sent: Thursday, September 13, 2001 4:05 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

!! tsiL siht ot cipoT ffO tsop ton od esaelP !!

You can read this article to get some info:
http://www.oracle.com/oramag/oracle/00-nov/index.html?o60o8i.html

Ed

-Original Message-
Sent: Thursday, September 13, 2001 6:15 PM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Anyone here tried to use those locally managed
tablespace? Some DBA here persuade me to use the
locally managed TS for the 
rollback segment,tables, indexes, temp tablespace

Can you tell me what are the benefits of using the
locally managed tablespace, any disadvantages?

Thanks in advance.

Chris Harvest.
Creative Consulting.


__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: CC Harvest
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


* * * * * Freedom of Information Act Notice * * * * * 
The information in this email is subject to the record protection mandated
by 5 United States Code 552(b)(4) and relevant judicial opinions. 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Sherman, Edward
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Confidential
This e-mail and any files transmitted with it are the property
of Belkin Components and/or its affiliates, are confidential,
and are intended solely for the use of the individual or
entity to whom this e-mail is addressed.  If you are not one
of the named recipients or otherwise have reason to believe
that you have received this e-mail in error, please notify the
sender and delete this message immediately from your computer.
Any other use, retention, dissemination, forwarding, printing
or copying of this e-mail is strictly prohibited.
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kathy Duret
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: find out the rollback

[Deepak Thapliyal]

!! Please do not post Off Topic to this List !!

200, (two hundred thousand:)

English is indeed an great language as it changes in
its nuances as geographic locations change ..its
friday finally!

srinivas for estimating rollback, consider taking
snapshots of v$rollstat.waits (specified in bytes)
before and after a sample delete on your table and
extrapolate to identify the amount of rollback you
would require for successfully completing your
operation 

hth
Deepak
--- Christopher Spence <[EMAIL PROTECTED]> wrote:
> !! Please do not post Off Topic to this List !!
> 
> What is 2lakh rows?  Never heard of that number
> metric.
> 
> "Do not criticize someone until you walked a mile in
> their shoes, that way
> when you criticize them, you are a mile a way and
> have their shoes."
> 
> Christopher R. Spence 
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
> 
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>  
> 
> 
> -Original Message-
> [mailto:[EMAIL PROTECTED]] 
> Sent: Thursday, September 13, 2001 12:45 PM
> To: Multiple recipients of list ORACLE-L
> 
> !! Please do not post Off Topic to this List !!
> 
> Hi lists
> 
> can anybody post me how much rollback (approx in
> bytes/kb/mb) will be
> generated if I delete my table that has 2 lakh rows.
> 
> the avg row len is 50.
> 
> often The query is getting failed due to the error
> unable to extend the
> rollback segment.
> 
> If I have this query, at least I will use a rollback
> segment that has
> enough extents.
> 
> thnx in advance.
> 
> Srinivas
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Tatireddy, Shrinivas (MED, Keane)
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Deepak Thapliyal
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Is Virus protection software safe on an NT / Oracle server?

[Boivin, Patrice J]

!! Please do not post Off Topic to this List !!

It will slow down your server.

With McAfee, you can exclude the directories that contain oracle datafiles,
that minimizes the impact.

Virus checking software is a good idea, in my opinion. 

Regards,
Patrice Boivin
Systems Analyst (Oracle Certified DBA)

Acting Head
Systems Admin & Operations | Admin. et Exploit. des systèmes
Technology Services| Services technologiques
Informatics Branch | Direction de l'informatique 
Maritimes Region, DFO  | Région des Maritimes, MPO

E-Mail: [EMAIL PROTECTED]  






-Original Message-
From:   Smith, Ron L. [SMTP:[EMAIL PROTECTED]]
Sent:   Friday, September 14, 2001 1:41 PM
To: Multiple recipients of list ORACLE-L
Subject:Is Virus protection software safe on an NT / Oracle
server?

!! Please do not post Off Topic to this List !!

Our NT Server admins have been told by the Security department that
they
need to install virus protection software on the NT / Oracle
servers.  Does
anyone know of any problems between Oracle and any virus software?

Ron Smith
Database Administrator
[EMAIL PROTECTED]

-- 
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  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing
Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Boivin, Patrice J
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: When optimizer reevaluate SQL statement

[Gaja Krishna Vaidyanatha]

!! Please do not post Off Topic to this List !!

Hi Alex,

Yes, I mean invalidate the SQL that is referencing the
object(s) that was analyzed. The creation of the index
has the same effect. Which means after the index is
created, the next execution of the query to that
table, will be re-parsed, execution plan re-built and
if it makes sense, the index will be used in the plan.

Cheers,

Gaja
--- "Hillman, Alex" <[EMAIL PROTECTED]>
wrote:
> !! Please do not post Off Topic to this List !!
> 
> You mean it invalidates SQL which has references to
> the newly analyzed
> objects, not all SQL in cache - right? Also are you
> sure that creating index
> on table will invalidate SQL which references this
> table or view based on
> this table?
> 
> Alex Hillman
> 
> -Original Message-
> Sent: Friday, September 14, 2001 11:40 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> !! Please do not post Off Topic to this List !!
> 
> Chris & list,
> 
> The last time I checked, an ANALYZE also invalidates
> the SQL in the shared pool, to force a parse and
> rebuild of the execution plan, on the next execution
> of the SQL statement.
> 
> Regards,
> 
> Gaja
> 
> --- Christopher Spence <[EMAIL PROTECTED]> wrote:
> > !! Please do not post Off Topic to this List !!
> > 
> > If the statement is not exactly the same, the new
> > statement will be
> > reparsed.
> > 
> > If you are executing it under a different user the
> > statement will be
> > reparsed.
> > 
> > If you drop/create an index, it will invalidate
> the
> > explain plan if that was
> > part of the chosen path.
> > 
> > I believe statistics also invalidates the plans as
> > well, but not 100% sure
> > on that.
> > 
> > "Do not criticize someone until you walked a mile
> in
> > their shoes, that way
> > when you criticize them, you are a mile a way and
> > have their shoes."
> > 
> > Christopher R. Spence 
> > Oracle DBA
> > Phone: (978) 322-5744
> > Fax:(707) 885-2275
> > 
> > Fuelspot
> > 73 Princeton Street
> > North, Chelmsford 01863
> >  
> > 
> > 
> > -Original Message-
> > Sent: Thursday, September 13, 2001 8:11 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > !! Please do not post Off Topic to this List !!
> > 
> > No takers so far - anybody?
> > 
> > Alex Hillman
> > 
> > -Original Message-
> > Sent: Thursday, September 06, 2001 4:30 PM
> > To: Multiple recipients of list ORACLE-L
> > 
> > 
> > Let's assume that SQL statement was parsed by user
> > X. If this or another
> > user reexecute this same statement what are the
> > conditions that this SQL
> > statement will be reparsed? Let's assume that
> > privileges are not changed and
> > tables and/or views are not dropped and views are
> > not changed. And optimizer
> > parameters are not changed. First come to mind is
> > dropping index. What about
> > reanalizing one of the object - theoretically
> should
> > also reparse. Anything
> > else?
> > 
> > Also is there possibility to force reparsing of
> SQL
> > statement if let say
> > index was added - short of flashing shared pool?
> > 
> > Alex Hillman
> > -- 
> > Please see the official ORACLE-L FAQ:
> > http://www.orafaq.com
> > -- 
> > Author: Hillman, Alex
> >   INET: [EMAIL PROTECTED]
> > 
> > Fat City Network Services-- (858) 538-5051 
> FAX:
> > (858) 538-5051
> > San Diego, California-- Public Internet
> > access / Mailing Lists
> >
>

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

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

> > To REMOVE yourself from th

RE: Lots and lots of redo logs

[John Lewis]

!! Please do not post Off Topic to this List !!

AIX has a unique concept of this. 

Given data spread on a disk. The head will spend more time over the 
 middle tracks as it seeks data that is distributed across the disk.

Thus for faster access, place your tablespace on the middle tracks
of a disk because the probability that the head will be over it is
much greater.

Learned this with Syabase on AIX. It may still hold true today.
I don't know. Technology had advanced a lot from those day. (But I think the
laws of physics are the same ;-))

-Original Message-
Sent: Friday, September 14, 2001 10:05 AM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Christopher R. Spence said:
"Disks are cheap and if you store things on the outer platters performance
will suffer"?

Chris, I'm not sure what you mean here. 

The concept of Variable Transfer Rate covers this.

Stealing a quote from: Optimal Storage Configuration Made Easy,
By Juan Loaiza, Oracle Corporation

"The transfer rate for a disk drive is not the same for all portions of a
disk.  
The outer sectors of a disk drive move by the disk head faster than the
inner sections 
leading to a faster transfer rate for the outer sectors.  This is simply
because 
of the circular shape of a disk drive."

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Lewis
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: Is Virus protection software safe on an NT / Oracle server?

[Kimberly Smith]

!! Please do not post Off Topic to this List !!

We run a Virus Scanner on our NT Server that houses an Oracle database
with no issues.  The only problem I have every seen is where a bug
was in one of the downloads to update the virus information and it messed
up everything from PCs to the Exchange Server.  You may not want to
put servers on automatic updates.

-Original Message-
Sent: Friday, September 14, 2001 9:41 AM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Our NT Server admins have been told by the Security department that they
need to install virus protection software on the NT / Oracle servers.  Does
anyone know of any problems between Oracle and any virus software?

Ron Smith
Database Administrator
[EMAIL PROTECTED]

-- 
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  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kimberly Smith
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: FW:Using Oracle tools to automate hot backups...

[Ruth Gramolini]

!! Please do not post Off Topic to this List !!

We use rman here.  I have used it using the OEM Backup Manager but I find it
more convenient to run it from the OS.  I schedule jobs on OEM to run the
server commands and let the agent take care of it.

HTH,
Ruth
- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 1:36 PM


> !! Please do not post Off Topic to this List !!
>
>
>
> Hi DBA's
>
> First, as a Belizean/American, I'd like to share my sympathy for those
> touched by the terrorist attack on the US and anger towards the cowards
who
> would go to such extremes to achieve their goals.
> Life is real. Life is a journey, we must complete it.
> Peace!!
>
>
> We are running Oracle 8.0.5 on NT in ARCHIVELOGMODE.
> I recently scheduled three daily hot backups to disk.
> The process is as follows:
> 1.) Perl script moves the old backup folders and creates the three daily
> folders that hold the hot backups to disk
> 2.) Batch file runs SQL script to display the Archive Log information
> 3.) SQL scripts called by .bat files, execute the hot backups by
tablespace
> 4.) Batch file runs SQL script to display the Archive Log information
> The process is not completely automated however. Nevertheless, these
scripts
> are called by the Windows NT Scheduler from My Computer.
>
> Of course, they only work if the machine is turned on.
> It appears that if I move the system date back the scheduler gets confused
> and submissions fail.
> I want to pursue the option of setting up these tasks using the Oracle OEM
/
> RMAN or DBMS_JOB tools. Please advise me on the most efficient and
effective
> tool for this process.
>
> Thanks in advance,
>
> Denmark Weatherburne
> Belize
>
> "Knowledge is power, but it is only useful if it is shared!"
>
> _
>
>
> _
> Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Denmark Weatherburne
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Average response time

[Jared . Still]

!! Please do not post Off Topic to this List !!



Paul,

Try this:

select ( s.service_seconds + w.wait_seconds) / user_calls service_time
from (
   select
   (sum(value)/100) / ( 3600 * 24 ) service_seconds
   from v$sysstat
   where upper(name) like '%CPU%'
   and class in (1,64) -- User and SQL
) s,
(
   select
  sum(time_waited/100) wait_seconds
   from v$system_event
   where event not like '%timer'
   and event not like '%from client'
) w,
(
   select
  sum(value) user_calls
   from v$sysstat
   where name like 'user%'
) u
/

While you may find this a useful number as a DBA, I'll bet your users won't
buy it.

On my SAP system it shows a 0.025 second reponse time.  While that may be
accurate on a per call database, I don't think many queries are returned
that quickly.  :)

Could be that I'm not getting the right numbers, but I don't believe that
determining
an average response time is quite that simple.

Jared




   
 
Paul.Parker@bm 
 
wna.com  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>
Sent by: cc:   
 
[EMAIL PROTECTED]   Subject: Average response time
 
om 
 
   
 
   
 
09/14/01 09:35 
 
AM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




!! Please do not post Off Topic to this List !!

Hi List,

I am trying to calculate the average database response time for a data
center audit currently underway.  Without "expensive" monitoring tools, is
it possible to determine this from database statistics.  So far, I'm using
(Service Time + Wait Time) / calls where this translates into

Service Time = 'CPU used by this session' from v$sysstat
Wait Time= sum(time_waited) from v$system_event (excluding idle
events)
User calls   = 'user calls' from v$sysstat

Am I way off the mark here?

Interestingly, it seems as if Craig Shallahamer (www.orapub.com) is
preparing a paper which addresses this very issue - determing response time
from database statistics - but it is only due out later this year.

Anybody with any ideas or reasons why the above is not feasible?

TIA
Paul


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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: When optimizer reevaluate SQL statement

[Christopher Spence]

!! Please do not post Off Topic to this List !!

If the Oracle executing user is different, the execution plan will be
different due to possibility of different objects and security.

Different sessions under the same user can share using bind variables.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 12:25 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!

Chris:

> If you are executing it under a different user the statement will be
reparsed.

I'm puzzled. I always thought that different users submitting the identical
SQL statement would use the same plan and not need to be reparsed. I
understood that to be part of the reasoning behind bind variables and the
big advantage of cursor sharing. Please explain.

Jon Walthour
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walthour, Jon (GEAE, Compaq)
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: database disaster recovery

[Don Granaman]

!! Please do not post Off Topic to this List !!

I second that!  Market open on Monday might be somewhat revealing...

(setq minor-rant-mode ON)
I worked as a DBA in the financial/brokerage sector for years and was frequently
appalled at what were often loudly touted as "high availability" and "disaster
recovery" plans - in that sector and others.
(setq minor-rant-mode OFF)

-Don Granaman
[Orasaurus - Honk if you remember UFI!]

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 9:50 AM


> !! Please do not post Off Topic to this List !!
>
> I'm curious. With all that's going on in New York, were any
> Oracle databases lost? Has anyone had to activate a disaster
> recovery plan and bring up their database at a new location?
> If so, how did that go? It would be interesting, and
> possibly instructive, to hear some real-life stories about
> what went wrong, what went well, etc.
>
> Best regards,
>
> Jonathan Gennick
> mailto:[EMAIL PROTECTED] * 906.387.1698
> http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Jonathan Gennick
>   INET: [EMAIL PROTECTED]
>
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

FW:Using Oracle tools to automate hot backups...

[Denmark Weatherburne]

!! Please do not post Off Topic to this List !!



Hi DBA's

First, as a Belizean/American, I'd like to share my sympathy for those
touched by the terrorist attack on the US and anger towards the cowards who
would go to such extremes to achieve their goals.
Life is real. Life is a journey, we must complete it.
Peace!!


We are running Oracle 8.0.5 on NT in ARCHIVELOGMODE.
I recently scheduled three daily hot backups to disk.
The process is as follows:
1.) Perl script moves the old backup folders and creates the three daily
folders that hold the hot backups to disk
2.) Batch file runs SQL script to display the Archive Log information
3.) SQL scripts called by .bat files, execute the hot backups by tablespace
4.) Batch file runs SQL script to display the Archive Log information
The process is not completely automated however. Nevertheless, these scripts
are called by the Windows NT Scheduler from My Computer.

Of course, they only work if the machine is turned on.
It appears that if I move the system date back the scheduler gets confused
and submissions fail.
I want to pursue the option of setting up these tasks using the Oracle OEM /
RMAN or DBMS_JOB tools. Please advise me on the most efficient and effective
tool for this process.

Thanks in advance,

Denmark Weatherburne
Belize

"Knowledge is power, but it is only useful if it is shared!"

_


_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: database disaster recovery

[Rachel Carmichael]

!! Please do not post Off Topic to this List !!

Jonathan,

Don't know -- the NYOUG is trying to check on members, but we've been 
looking more towards making sure everyone is alive (so far, I have not heard 
that we lost members) and finding ways to help them than towards disaster 
recovery plans.

Give NY a few weeks and we'll find out right now, it's still people not 
process we're concerned with.

Rachel


>From: Jonathan Gennick <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: database disaster recovery
>Date: Fri, 14 Sep 2001 06:50:20 -0800
>
>!! Please do not post Off Topic to this List !!
>
>I'm curious. With all that's going on in New York, were any
>Oracle databases lost? Has anyone had to activate a disaster
>recovery plan and bring up their database at a new location?
>If so, how did that go? It would be interesting, and
>possibly instructive, to hear some real-life stories about
>what went wrong, what went well, etc.
>
>Best regards,
>
>Jonathan Gennick
>mailto:[EMAIL PROTECTED] * 906.387.1698
>http://Gennick.com * http://MichiganWaterfalls.com * http://MetalDrums.org
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Jonathan Gennick
>   INET: [EMAIL PROTECTED]
>
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from).  You may
>also send the HELP command for other information (like subscribing).


_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Automanagement of extent sizing

[Don Granaman]

!! Please do not post Off Topic to this List !!

Inline answers...

- Original Message -
To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
Sent: Friday, September 14, 2001 7:45 AM


> !! Please do not post Off Topic to this List !!
>
>
> Don,
>
> Just  to confirm a point that I think that you are making:
>
> Each tablespace should only have one extent size in it?

Yes, in any given tablespace there is ONLY one extent size.  Initial = next for
everything and every (initial extent size) = (every other initial extent size) -
for everything in the tablespace.  (Except for SYSTEM).

> Once you get too many extents, you move the object up to the next-size
> tablespace?

If you must.  Better in my opinion is to size for growth.  For example, if you
have a table that is currently 200M, but it will grow to 20G in two years,
prefer initially putting it into a tablespace appropriate for a 20 GB table.
The percentage of "wasted space" will be fairly high initially, but will
decrease as it grows - and you won't have to move it later.

Consider that, on average, one half of one extent will be as yet unused -
"wasted".  As the number of extents grows, that fixed amount of space gets to be
a smaller percentage of the total.  For example, a uniform extent policy:

A table consists of N extents
Each and every extent is of size M
Average "wasted space" = M/2
  (If anyone wants to extend this line of reasoning for parallel loads, etc. -
feel free!)
Total space in N extents = N*M
The ratio of wasted space to total space is (M/2)/(N*M) = M/[2(M*N)] = 1/(2N)
 {Sanity check!: 4 extents, 0.5 extent unused -> 1/8 of space is "wasted".
1/(2*4) = 1/8.  It checks.}
To convert to a percentage, multiply by 100.
=> Average percentage of total space for table extents that is "wasted space" W
= 100*[1/(2N)] = 50/N
As the value of N (the number of extents) increases, the value of W (the
percentage of wasted space) decreases.

[Note:  This, of course, does not consider free space within blocks, ILT space,
and all the other geeky Oracle stuff - nor should it.  All that will be present
whether you have one extent or a thousand.  It is a separate issue entirely.]

> Thanks,
>
> Cherie

You are quite welcome!

-Don Granaman
[OraSaurus - Honk if you remember UFI!]

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RMAN catalog Recreation Problems/Questions ....

[Janardhana Babu]

!! Please do not post Off Topic to this List !!

Dear List,

I have Production DB in 8.1.6.2.0(64bit) on one box, and RMAN catalog
database 
(8.1.6.2.0) on another box. I need to upgrade both the DBs to 8.1.7.1.0. I
have 
no problem upgrading the production DB to 8.1.7.0.0 and the to 8.1.7.1.0
patch 
level.
 
The box on which catalog DB exists has another production DB which was
upgraded 
to 8.1.7.1.0. and so I CAN NOT upgrade catalog DB to 8.1.7.1.0 using normal 
upgrade procedure. I must upgrade to base line release first(8.1.7.0.0) and 
then patch needs to be applied. Since patch was already applied, my only
option 
could be to recreate the catalog database in 8.1.7.1.0 and then DO THE FULL 
IMPORT. My questions would be:

[1] Do I need to do the FULL IMPORT of the catalog database? and then issue
the 
"upgrade catalog" command twice to upgrade the catalog to work with the
latest 
RMAN version? [OR]

[2] Do I just recreate the empty catalog DB in 8.1.7.1.0 and then follow the

regular procedure to create RMAN schema and register the DB and then take
the 
fresh cold backup to start with, followed by the regular daily incremental 
backups?

[3]If I follow [2], I may loose the catalog info for 8.1.6.2.0. backups. Is
the 
8.1.6.2.0 catalog data useful after upgrading the DB to 8.1.7.1.0?

Could someone Please answer my above questions and suggest an appropriate
procedure to 
upgrade the catalog DB to 8.1.7.1.0.

-- Janardhana Babu

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: CBO changed path - why??

[Christopher Spence]

!! Please do not post Off Topic to this List !!

What I would do is use DBMS_STATS to move the production stats to the
staging db and see if the tests lead the same results.

Are ya stats up to date?  You using analyze or dbms_stats?
Do you have comparing explain plans, trace files?

I would highly recommend tracing it and checking which step is doing a lot
of rows, and comparing that to determine where it is slowing down.

"Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes."

Christopher R. Spence 
Oracle DBA
Phone: (978) 322-5744
Fax:(707) 885-2275

Fuelspot
73 Princeton Street
North, Chelmsford 01863
 


-Original Message-
Sent: Friday, September 14, 2001 12:30 PM
To: Multiple recipients of list ORACLE-L

!! Please do not post Off Topic to this List !!


We have had 2 querys go wacko on us.  Both are cursors in a large
(5000 line) pl/sql package.  This interface package runs daily.
The cursor execution below ran in less than 30 seconds on Tues;
ran 2 hrs 15 minutes Wed. (yikes!)  We had the same problem
with a similar cursor 2 weeks ago.

I've fixed the query by adding more selectivity to the where clause.
Here's the real mystery.  I pulled the 3 tables from this join
from the production box (E4500 Solaris 2.6, Oracle 8.0.5) to our
smaller test box.  Small test box is running identical stuff (solaris 2.6,
Oracle 8.0.5).  The query still runs in under 30 seconds on small
test box.  I dumped all the init parameters (SELECT NAME, VALUE FROM
V$PARAMETER) from both databases, then did a diff in the output files.
No significant differences that I can see.

I'm wondering why the query still runs ok on the test box, but went
wacko on the "real" system.

These 3 tables are small (invrows 95,062 rows 21 megs; sub_ad 5,993 rows,
20 megs; sub_pub 45,553 rows 30 megs).  All 3 tables have identical
indexes on both boxes; all 3 have been analyzed on both boxes.
All 3 tables have index on column adno. optimizer is choose on both
instances.


Sorry this is so long.  I'd appreciate any insights.
Thx!!!

Barb



select
i.adno,

frominvrows i,
sub_ad a,
sub_pub p
WHERE   A.RUNNO=860  and   I.ROWTYPE=4
  and   I.ADNO=A.ADNOand   I.VNO=A.VNO
  and   i.adno=p.adnoand   i.pubno=p.pubno
  and   a.vno=p.vno  and   A.VNO=1
  and   a.startdate > a.rdate
  and   a.enddate = to_date(a.cus4name,'mm/dd/')
  and   to_char(a.rdate,'mm/dd/') = to_char(p.mdate,'mm/dd/')

___
autotrace from production (BAD!!)

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313)
   10   NESTED LOOPS (Cost=1 Card=1 Bytes=313)
   21 NESTED LOOPS (Cost=56 Card=7 Bytes=1267)
   32   TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=10 Card=1 Bytes=133)
   42   TABLE ACCESS (FULL) OF 'SUB_PUB' (Cost=46 Card=5820
Bytes=279360)
   51 TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6791
Bytes=896412)
   65   INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1
Card=6791)



___
autotrace from test box (Good!)

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=313)
   10   NESTED LOOPS (Cost=1 Card=1 Bytes=313)
   21 NESTED LOOPS (Cost=1 Card=1 Bytes=265)
   32   TABLE ACCESS (FULL) OF 'SUB_AD' (Cost=11 Card=1 Bytes=133)
   42   TABLE ACCESS (BY INDEX ROWID) OF 'INVROWS' (Cost=1 Card=6906
Bytes=911592)
   54 INDEX (RANGE SCAN) OF 'I_INVR1' (NON-UNIQUE) (Cost=1
Card=6906)
   61 TABLE ACCESS (BY INDEX ROWID) OF 'SUB_PUB' (Cost=1 Card=6911
Bytes=331728)
   76   INDEX (UNIQUE SCAN) OF 'I_SUBPUB1' (UNIQUE)



___
tkprof from production (BAD!!)
(The tkprof shows 129,696,658 rows returned for sub_pub when the
entire table is only 45,000 rows.)


call count   cpuelapsed   disk  querycurrent
rows
--- --   -- -- -- --
--
Parse1  0.01   0.01  0  0  0
0
Execute  1  0.01   0.01  0  0  0
0
Fetch7   8139.098153.17907   56669565   8361
102
--- --   -- -- -- --
--
total9   8139.118153.19907   56669565   8361
102


Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 618  (AMAX)

Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
102   NESTED LOOPS
54193272NESTED LOOPS
   5993 TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'SUB_AD'
129696658 TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'SUB_PUB'
  27213TABLE ACCESS   

Higher Consistent Gets...

[Raj Gopalan]

!! Please do not post Off Topic to this List !!

Hi

I am having problem with a query. This query fetches rows from a table which
has 15 million rows. 

The problem is, when I execute this query with subquery, the consistent gets
are 4700. Where us without the subquery the consistent gets are just 400. If
I execute the subquery alone, the consistent gets are just 5.


Here is the main query with subquery results in cons.gets of 4700:


select pd.holiday_id holidayID,   pd.package_number l_package_number,
   min(pd.tfr_price) l_tpr_price
  from fr_search_query pd
 where pd.departure_date between TO_DATE('06/10/2001','dd/mm/')
 and TO_DATE('13/10/2001','dd/mm/')
   and pd.location_code in (select location_code
  from gn_location
connect by prior location_code=parent_code
  start with location_code='3142')
   and ROWNUM < 301
 group by pd.holiday_id ,   pd.package_number
 order by pd.location_name,   pd.location_code,   pd.accom_unit_name,
  pd.accom_unit_code,   pd.departure_date,   min(pd.adult_price)

Trace results

Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
 96   SORT (ORDER BY)
 96SORT (GROUP BY)
 96 COUNT (STOPKEY)
 96  NESTED LOOPS
   5137   INLIST ITERATOR
   5138TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
   'FR_SEARCH_QUERY'
   8566 INDEX   GOAL: ANALYZED (RANGE SCAN) OF
'FR_SEARCH_QUERY_IND_1' (NON-UNIQUE)
 96   VIEW
   5136SORT (UNIQUE)
  1 CONNECT BY
  2  INDEX   GOAL: ANALYZED (FAST FULL SCAN) OF
 'PK_GN_LOCATION' (UNIQUE)
  1  TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1  TABLE ACCESS (FULL) OF 'GN_LOCATION'


Running just the subquery results in cons.gets of just 5.


  select location_code
from gn_location
 connect by prior location_code=parent_code
   start with location_code='3142'


Rows Execution Plan
---  ---
  0  SELECT STATEMENT   GOAL: CHOOSE
  1   CONNECT BY
  2INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'PK_GN_LOCATION'
   (UNIQUE)
  1TABLE ACCESS (BY USER ROWID) OF 'GN_LOCATION'
  1TABLE ACCESS (RANGE SCAN) OF 'GN_PARENT_LOCATION_CODE'
(NON-UNIQUE)


Both the tables, indexes are analyzed. The optimizer mode is choose. 

How do I tune this or Am I missing something obivious??

Thanks

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

RE: CBO - default num_rows in a table

[Hallas John]

Title: RE: CBO - default num_rows in a table





Jared,Christopher


The tuning manual states the following
"If the optimizer uses the cost-based approach for a SQL statement, and if some tables accessed by the statement have no statistics, then the optimizer uses internal information (such as the number of data blocks allocated to these tables) to estimate other statistics for these tables" 

However in the exmaple I gave the no of blacks had been increased dramatically and yet the CBO did not know about it, therefore indicating to me that it uses something other than the no of blocks. I am just trying to get an insight into whatever that something is.

John



-Original Message-
From: Jared Still [mailto:[EMAIL PROTECTED]]
Sent: 14 September 01 16:37
To: [EMAIL PROTECTED]; Hallas John
Subject: Re: CBO - default num_rows in a table




Can't recall at the moment, but if you take a look 
at the tuning manual, it will tell you what the
defaults are.


Jared



On Friday 14 September 2001 03:20, Hallas John wrote:
> My question of the day is :-
> What value does the CBO use as a default number of rows for a table.
>
> Background: -
>
> We all know that if any tables in a query have been analyzed then CBO is
> used for the query not RBO  (couple of caveats I know but let's continue).
> So if 3 tables are used in a query and table a has 500 rows (analyzed)
> table b has 50 rows (never analyzed) and table c has 350 rows (never
> analyzed) all things being equal then CBO is used but what values does the
> CBO use for tables b or c to decide which execution plan is best.
>
> On a development system yesterday a query was running slow. I realised that
> we had put in a  very large data load (3.5M rows). I analyzed the table and
> indexes and the query came back in sub second response time. I am trying to
> figure what value was being used for num_rows prior to the analyze. It
> cannot be based on blocks allocated/used as thet would have increased after
> the dataload
>
>
> Thanks
>
>
> John
>
> -Original Message-
> Sent: 13 September 01 22:22
> To: Multiple recipients of list ORACLE-L
>
>
> !! Please do not post Off Topic to this List !!
>
> Cherie - We have been using the autoextend feature for 6 months now and
> have been really pleased with it. I am now studying the Oracle White Papers
> on the locally managed and uniform extent philosophy and beginning to
> follow that scheme. I would recommend studying it carefully. We have had a
> couple of runaways that ate up a lot of disk, that is the most obvious
> downside. The upside is obvious in the title of the Oracle white paper
> "Stop Defragging and Start Living". Here are my procedures so far:
>
> 1. Use Oracle's new uniform extent recommendations to eliminate free extent
> fragmentation. Since all extents are the same size, no fragmentation can
> occur.
> 2. Use locally-managed tablespaces per Oracle's recommendation.
> 3. Set all extents in a tablespace to the same size. There are no unusable
> small free extents, free space is usable by any segment, and administration
> is minimized.
> 4. Use only 3 extent sizes: 128K, 4M, and 128M
> 5. All segments should have less than 1,024 extents. When a table
> approaches 1,024 extents, it should be moved to the next larger extent size
> tablespace. 6. Monitor archive log space.
> 7. Temporary and rollback tablespaces should be divided into 1,024 extents
> for optimal performance.
> 8. Export the table before moving it.
> 9. Use the Oracle "alter table XXX move" command.
> 10. Use the Oracle "alter index XXX rebuild" command.
>
> Let me know if you have any more questions, and please share your ideas.
>
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]



Content-Type: text/html; charset="iso-8859-1"; name="Attachment: 1"
Content-Transfer-Encoding: quoted-printable
Content-Description: 





**
This email and any attachments may be confidential and the subject of
legal professional privilege.  Any disclosure, use, storage or copying
of this email without the consent of the sender is strictly prohibited.
Please notify the sender immediately if you are not the intended
recipient and then delete the email from your inbox and do not
disclose the contents to another person, use, copy or store the
information in any medium.
**

RE: Average response time

[Stephane Faroult]

!! Please do not post Off Topic to this List !!

> Hi List,
> 
> I am trying to calculate the average database response time for a data
> center audit currently underway.  Without "expensive" monitoring tools, is
> it possible to determine this from database statistics.  So far, I'm using
> (Service Time + Wait Time) / calls where this translates into  
> 
> Service Time= 'CPU used by this session' from v$sysstat
> Wait Time   = sum(time_waited) from v$system_event (excluding idle
> events)
> User calls  = 'user calls' from v$sysstat
> 
> Am I way off the mark here?
> 
> Interestingly, it seems as if Craig Shallahamer (www.orapub.com) is
> preparing a paper which addresses this very issue - determing response time
> from database statistics - but it is only due out later this year.
> 
> Anybody with any ideas or reasons why the above is not feasible?
> 
> TIA
> Paul
> 

Paul,

  Firstly a number of statistics are meaningless in V$SYSSTAT and only
make sense in V$SESSTAT (and vice-versa) and I believe that 'CPU used by
this session' belongs to this category. Usually most resource
consumption is traceable to a very tiny fraction of SQL statements, and
I doubt that an average will lead you anywhere. My point is that I think
that you should try to apply your ideas to relatively small slices of
time (polling every minute or so) hoping to catch the real problem
queries on the fly, using global statistics to get an idea about what
you have missed, and try to do the best out of it. Another idea would be
to concentrate on V$SQLAREA and the number of executions and of buffer
reads (there is a script named peep.sql in the DBA tool kit of the
Oriole site if you need one). I think that associating some average
elapsed time to access, say, 1,000 buffers, should not be extremely
difficult to do, based on a few suitable examples. By computing the
average number of buffer accesses per execution of a query, you could
then get something looking reasonably like an average execution time for
the query. Of course, a query is not a transaction, and the user's
vision of response times may be different and include other elements.
But it may be an interesting approach to complement other metrics.
-- 
Regards,

  Stephane Faroult
  email: [EMAIL PROTECTED] 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:+44  (0) 7050-696-449 
  Performance Tools & Free Scripts
--
http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs
--
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Re: Tablespace (datafile reducing)

[Anjan Thakuria]

Chris,
"Generally more smaller data files is better
than fewer larger ones."  Can
you pl let me know if there is any paper or something throwing light on
this.
Thanks
Anjan
Gene Sais wrote:
!! Please do not post Off Topic to this List !!
Datafile sizing is also dependent on the size of your db.  I use
10gb datafile size's for a 1tb database.
>>> [EMAIL PROTECTED] 09/14/01 09:50AM >>>
!! Please do not post Off Topic to this List !!
Generally more smaller data files is better than fewer larger ones.
There is no performance problem with having 10 200Mb data files rather
than
1 2gb data file.  In fact, using 10 200mb data files will help
a little when
it comes to file locking.
"Do not criticize someone until you walked a mile in their shoes, that
way
when you criticize them, you are a mile a way and have their shoes."
Christopher R. Spence
Oracle DBA
Phone: (978) 322-5744
Fax:    (707) 885-2275
Fuelspot
73 Princeton Street
North, Chelmsford 01863
 
-Original Message-
Sent: Thursday, September 13, 2001 8:27 PM
To: Multiple recipients of list ORACLE-L
!! Please do not post Off Topic to this List !!
Hello DBAs
I  have a tablespace , who has 10 datafiles of 200MB
each, it was here before I even joined. I was thinking
for the contention and want to combine them all to one
single large datafile of 2gig.
How do I get rid off all those datafiles and ghet them
in one large datafiles??
Any help in this regard
Thank you very much
Kishore
__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Kishore
  INET: [EMAIL PROTECTED]
Fat City Network Services    -- (858) 538-5051 
FAX: (858) 538-5051
San Diego, California    --
Public Internet access / Mailing Lists

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

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You
may
also send the HELP command for other information (like subscribing).

RE: When optimizer reevaluate SQL statement

[Hillman, Alex]

!! Please do not post Off Topic to this List !!

You mean it invalidates SQL which has references to the newly analyzed
objects, not all SQL in cache - right? Also are you sure that creating index
on table will invalidate SQL which references this table or view based on
this table?

Alex Hillman

-Original Message-
Sent: Friday, September 14, 2001 11:40 AM
To: Multiple recipients of list ORACLE-L


!! Please do not post Off Topic to this List !!

Chris & list,

The last time I checked, an ANALYZE also invalidates
the SQL in the shared pool, to force a parse and
rebuild of the execution plan, on the next execution
of the SQL statement.

Regards,

Gaja

--- Christopher Spence <[EMAIL PROTECTED]> wrote:
> !! Please do not post Off Topic to this List !!
> 
> If the statement is not exactly the same, the new
> statement will be
> reparsed.
> 
> If you are executing it under a different user the
> statement will be
> reparsed.
> 
> If you drop/create an index, it will invalidate the
> explain plan if that was
> part of the chosen path.
> 
> I believe statistics also invalidates the plans as
> well, but not 100% sure
> on that.
> 
> "Do not criticize someone until you walked a mile in
> their shoes, that way
> when you criticize them, you are a mile a way and
> have their shoes."
> 
> Christopher R. Spence 
> Oracle DBA
> Phone: (978) 322-5744
> Fax:(707) 885-2275
> 
> Fuelspot
> 73 Princeton Street
> North, Chelmsford 01863
>  
> 
> 
> -Original Message-
> Sent: Thursday, September 13, 2001 8:11 PM
> To: Multiple recipients of list ORACLE-L
> 
> !! Please do not post Off Topic to this List !!
> 
> No takers so far - anybody?
> 
> Alex Hillman
> 
> -Original Message-
> Sent: Thursday, September 06, 2001 4:30 PM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Let's assume that SQL statement was parsed by user
> X. If this or another
> user reexecute this same statement what are the
> conditions that this SQL
> statement will be reparsed? Let's assume that
> privileges are not changed and
> tables and/or views are not dropped and views are
> not changed. And optimizer
> parameters are not changed. First come to mind is
> dropping index. What about
> reanalizing one of the object - theoretically should
> also reparse. Anything
> else?
> 
> Also is there possibility to force reparsing of SQL
> statement if let say
> index was added - short of flashing shared pool?
> 
> Alex Hillman
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Hillman, Alex
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX:
> (858) 538-5051
> San Diego, California-- Public Internet
> access / Mailing Lists
>

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

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

> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from).  You may
> also send the HELP command for other information
> (like subscribing).


=
Gaja Krishna Vaidyanatha
Director, Storage Management Products,
Quest Software, Inc.
Co-author - Oracle Performance Tuning 101
http://www.osborne.com/database_erp/0072131454/0072131454.shtml

__
Terrorist Attacks on U.S. - How can you help?
Donate cash, emergency relief information
http://dailynews.yahoo.com/fc/US/Emergency_Information/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.co

RE: I/O Performance/bottlenecks on EMC Symmetrix

[Rachel Carmichael]

!! Please do not post Off Topic to this List !!

corollary to rule 2

after a certain point, just stop. It ain't worth it anymore.


>From: Christopher Spence <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: RE: I/O Performance/bottlenecks on EMC Symmetrix
>Date: Fri, 14 Sep 2001 05:45:20 -0800
>
>Rules of tuning databases.
>
>1. There is always a bottleneck.
>2. Once you solve the bottle neck, refer to rule number 1.
>
>"Do not criticize someone until you walked a mile in their shoes, that way
>when you criticize them, you are a mile a way and have their shoes."
>Christopher R. Spence
>Oracle DBA
>Phone: (978) 322-5744
>Fax:(707) 885-2275
>Fuelspot
>73 Princeton Street
>North, Chelmsford 01863
>
>-Original Message-
>Sent: Thursday, September 13, 2001 10:01 AM
>To: Multiple recipients of list ORACLE-L
>
>At one site I worked using Oracle Financials we were having serious
>performance problems at what seemed to us random intervals. Spent months
>looking at the database after the Unix boys had said that there was no way
>we could have I/O problems with the throughput capabililities of EMC and 
>the
>Symetrix set up we had.
>Eventually turned out that 3 systems were sharing the same disks and the
>disks had not been striped. Therefore other system were causing us
>performance problems.
>If you have an EMC support contract which I think you must have you, the
>SA's get all the free GUI tools that allow them to look at channels and
>logical/physical layout. Ask them about.
>John
>-Original Message-
>Sent: 13 September 01 12:55
>To: Multiple recipients of list ORACLE-L
>
>!! Please do not post Off Topic to this List !!
>Hi All,
>
>Does anybody here on the list have experience with EMC/symmetrix storage
>units.?
>We have our databases on this machine and I have a feeling the the I/O
>performance is not very good. I can not proof it since I do not have any
>experience/data/access to that machine. We do however have a very
>cooperative UNIX group but they also lack experience with performance on
>this machine.
>Who can give me pointers about I/O throughput that can be reached,
>configuration pittfalls etc..
>Example:
>RS6000 8CPU's and 4Gb memory with storage on EMC/symmetrix. Job takes about
>2 hours to complete.
>F50 1 CPU 1Gb memory (TEST machine) local disks. same job takes 0.5 hours
>to complete.
>
>Jack
>=
>De informatie verzonden in dit e-mailbericht is vertrouwelijk en is
>uitsluitend bestemd voor de geadresseerde. Openbaarmaking,
>vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan
>derden is, behoudens voorafgaande schriftelijke toestemming van Ernst &
>Young, niet toegestaan. Ernst & Young staat niet in voor de juiste en
>volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch
>voor tijdige ontvangst daarvan. Ernst & Young kan niet garanderen dat een
>verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten
>worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden.
>Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u
>vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender
>en het origineel en eventuele kopieën te verwijderen en te vernietigen.
>Ernst & Young hanteert bij de uitoefening van haar werkzaamheden algemene
>voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De
>algemene voorwaarden worden u op verzoek kosteloos toegezonden.
>=
>The information contained in this communication is confidential and is
>intended solely for the use of the individual or entity to whom it is
>addressed. You should not copy, disclose or distribute this communication
>without the authority of Ernst & Young. Ernst & Young is neither liable for
>the proper and complete transmission of the information contained in this
>communication nor for any delay in its receipt. Ernst & Young does not
>guarantee that the integrity of this communication has been maintained nor
>that the communication is free of viruses, interceptions or interference.
>If you are not the intended recipient of this communication please return
>the communication to the sender and delete and destroy all copies.
>In carrying out its engagements, Ernst & Young applies general terms and
>conditions, which contain a clause that limits its liability. A copy of
>these terms and conditions is available on request free of charge.
>=
>
>
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>
>--
>Author:
>   INET: [EMAIL PROTECTED]
>Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California-- Public Internet access / Mailing Lists
>

Re: Is Virus protection software safe on an NT / Oracle server?

[Jan Pruner]

!! Please do not post Off Topic to this List !!

Ehh ... users???


:-)))

On Fri 14. September 2001 18:40, you wrote:
> !! Please do not post Off Topic to this List !!
>
> Our NT Server admins have been told by the Security department that they
> need to install virus protection software on the NT / Oracle servers.  Does
> anyone know of any problems between Oracle and any virus software?
>
> Ron Smith
> Database Administrator
> [EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jan Pruner
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).