from:"Riyaj_Shamsudeen"

Your error message indicates file #1 associated with the system tablespace and the trace file indicates the file #6. I would do the following
1. Check the OS error message for any hardware errors.
2. Find what objects are corrupted. To get this information use dba_extents and the given file and block#.Here is the script for the same:
---
accept h_file_id prompt ' Enter file_id ==>'
accept h_block_id prompt ' Enter block_id==>'
set verify off
column owner format A10
column segment_name format A20
column segment_type format A10
column hdrfile format
column curfile format
column curblk format
column hdrblock format
select owner, segment_name, segment_type, file_id,block_id from dba_extents
where file_id = &&h_file_id and
block_id <= &&h_block_id and
block_id + blocks > &&h_block_id;
set verify on

3. Since the errors are in two different files, check whether these files are in the same disk or controller..
4. Decide the course of action depending upon the above outcome.. It is possible for the disk /controller to give back the bad data. If it is an hardware problem, correct the hardware problem and then try again.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

Kevin Bass <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/03/01 11:50 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Corrupted data blocks in tablespace SYSTEM

I have just started a project and have encountered a database problem. The previous DBA did not create any backup scripts or backup and recovery plans. Is there a way to get around this problem without re-creating the database?
When executing my script to get corrupted data block information , I get the following:
ORA-01578: ORACLE data block corrupted (file # 1, block # 2660)
ORA-01110: data file 1: '/u02/oradata/TIE/system01.dbf'
Trace file information.
Dump file /u01/app/oracle/admin/TIE/udump/tie_ora_11859.trc
Oracle8 Release 8.0.5.0.0 - Production
PL/SQL Release 8.0.5.0.0 - Production
ORACLE_HOME = /u01/app/oracle/product/8.0.5
System name: SunOS
Node name: edi-01
Release: 5.7
Version: Generic_106541-17
Machine: sun4u
Instance name: TIE
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 11859, image: oracleTIE
*** SESSION ID:(13.50289) 2001.11.30.15.00.00.000
***
Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.
Bad header found during buffer read
Data in bad block - type:0. format:0. rdba:0x00090255
last change scn:0x.3c07e0da seq:0x0 flg:0xb1
consistancy value in tail 0x
check value in block header: 0x10, check value not calculated
spare1:0x0, spare2:0x0, spare2:0xd40
Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data
***
Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.
Bad header found during buffer read
Data in bad block - type:0. format:0. rdba:0x00090255
last change scn:0x.3c07e0da seq:0x0 flg:0xb1
consistancy value in tail 0x
check value in block header: 0x10, check value not calculated
spare1:0x0, spare2:0x0, spare2:0xd40
Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data
***
Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.
Bad header found during buffer read
Data in bad block - type:0. format:0. rdba:0x00090255
last change scn:0x.3c07e0da seq:0x0 flg:0xb1
consistancy value in tail 0x
check value in block header: 0x10, check value not calculated
spare1:0x0, spare2:0x0, spare2:0xd40
Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data
***
Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.
Bad header found during buffer read
Data in bad block - type:0. format:0. rdba:0x00090255
last change scn:0x.3c07e0da seq:0x0 flg:0xb1
consistancy value in tail 0x
check value in block header: 0x10, check value not calculated
spare1:0x0, spare2:0x0, spare2:0xd40
Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data
***
Corrupt block relative dba: 0x0180af98 file=6. blocknum=44952.
Bad header found during buffer read
Data in bad block - type:0. format:0. rdba:0x00090255
last change scn:0x.3c07e0da seq:0x0 flg:0xb1
consistancy value in tail 0x
check value in block header: 0x10, check value not calculated
spare1:0x0, spare2:0x0, spare2:0xd40
Reread of rdba=180af98 file=6. blocknum=44952. found same corupted data
Kevin

RE: Determinants of control file


All right, Here is the complete query to get the information from the control file.

select
decode (indx, 3,'MAXLOGFILES',4,'MAXDATAFILES',2,'MAXINSTANCES',9,'MAXLOGHISTORY
') , rsnum from x$kccrs
where indx in (3,4,2,9)
union all
select 'MAXLOGMEMBERS ',dimlm from x$kccdi
/

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Riyaj Shamsudeen
12/03/01 09:46 AM


        To:        [EMAIL PROTECTED]
        cc:        
        Subject:        RE: Determinants of control fileLink

Hi
        v$controlfile_record_section is based on the fixed table x$kccrs. This does provide values for all the columns except  maxlogmembers. Here is the query to pull the information..

select
decode (indx, 3,'MAXLOGFILES',4,'MAXDATAFILES',2,'MAXINSTANCES',9,'MAXLOGHISTORY') , rsnum from x$kccrs
where indx in (3,4,2,9)
/
DECODE(INDX,3      RSNUM
- --
MAXINSTANCES              8
MAXLOGFILES                 32
MAXDATAFILES         1022
MAXLOGHISTORY       907

which is matching with my controlfile dump.

CREATE CONTROLFILE REUSE DATABASE "mydb" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022
    MAXINSTANCES 8
    MAXLOGHISTORY 907

        I am not sure where maxlogmembers is stored. I will do some more digging to find that..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Nirmal Kumar  Muthu Kumaran <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/01/01 08:25 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Determinants of control file


No. i already checked the view v$control_record_section, 
This view does not provide any information regarding determinants of controlfile like MAXDATAFILES, MAXLOGFILES, etc
Nirmal. 
-Original Message- 
t size=1 face="Arial">K Gopalakrishnan [SMTP:[EMAIL PROTECTED]] 
Sent:   Thursday, November 29, 2001 11:27 PM 
To:     Multiple recipients of list ORACLE-L 
oman"> RE: Determinants of control file 
Other than dumping/ tracing the control file, you can find the required details in the 
dynamic view V$controlfile_record_section. This view has all the information you want 
  
  
Best Regards, 
K Gopalakrishnan 
  
  
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tatireddy, Shrinivas (MED, Keane)
Sent: Thursday, November 29, 2001 3:21 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Determinants of control file 
  
Hi Nirmal, 
  
   Use 
  
alter database backup controlfile to trace; 
    
    You can find these details in the trace file, that will be dumped to your udump destination. 
  
HTH 
Srinivas. 
  
   
-Original Message-
From: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 29, 2001 7:52 AM
To: Multiple recipients of list ORACLE-L
Subject: Determinants of control file 
Hi all, 
The determinants(size) of control file are maxdatafiles, maxlogfiles. maxlogmemebers, etc., 
But after creation of control files, where should i get details about these parameter values?... 
i checked in v$controlfile and v$database... i didn't get enough info on it. 
And i found controlfile_sequence# column i found in v$database view. I multiplexed the control files.
Where will i get the sequence# of other control files then... 
Nirmal,

RE: Determinants of control file


Hi
        v$controlfile_record_section is based on the fixed table x$kccrs. This does provide values for all the columns except  maxlogmembers. Here is the query to pull the information..

select
decode (indx, 3,'MAXLOGFILES',4,'MAXDATAFILES',2,'MAXINSTANCES',9,'MAXLOGHISTORY') , rsnum from x$kccrs
where indx in (3,4,2,9)
/
DECODE(INDX,3      RSNUM
- --
MAXINSTANCES              8
MAXLOGFILES                 32
MAXDATAFILES         1022
MAXLOGHISTORY       907

which is matching with my controlfile dump.

CREATE CONTROLFILE REUSE DATABASE "mydb" NORESETLOGS ARCHIVELOG
    MAXLOGFILES 32
    MAXLOGMEMBERS 2
    MAXDATAFILES 1022
    MAXINSTANCES 8
    MAXLOGHISTORY 907

        I am not sure where maxlogmembers is stored. I will do some more digging to find that..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Nirmal Kumar  Muthu Kumaran <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/01/01 08:25 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Determinants of control file


No. i already checked the view v$control_record_section, 
This view does not provide any information regarding determinants of controlfile like MAXDATAFILES, MAXLOGFILES, etc
Nirmal. 
-Original Message- 
t size=1 face="Arial">K Gopalakrishnan [SMTP:[EMAIL PROTECTED]] 
Sent:   Thursday, November 29, 2001 11:27 PM 
To:     Multiple recipients of list ORACLE-L 
oman"> RE: Determinants of control file 
Other than dumping/ tracing the control file, you can find the required details in the 
dynamic view V$controlfile_record_section. This view has all the information you want 
  
  
Best Regards, 
K Gopalakrishnan 
  
  
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Tatireddy, Shrinivas (MED, Keane)
Sent: Thursday, November 29, 2001 3:21 AM
To: Multiple recipients of list ORACLE-L
Subject: RE: Determinants of control file 
  
Hi Nirmal, 
  
   Use 
  
alter database backup controlfile to trace; 
    
    You can find these details in the trace file, that will be dumped to your udump destination. 
  
HTH 
Srinivas. 
  
   
-Original Message-
From: Nirmal Kumar Muthu Kumaran [mailto:[EMAIL PROTECTED]]
Sent: Thursday, November 29, 2001 7:52 AM
To: Multiple recipients of list ORACLE-L
Subject: Determinants of control file 
Hi all, 
The determinants(size) of control file are maxdatafiles, maxlogfiles. maxlogmemebers, etc., 
But after creation of control files, where should i get details about these parameter values?... 
i checked in v$controlfile and v$database... i didn't get enough info on it. 
And i found controlfile_sequence# column i found in v$database view. I multiplexed the control files.
Where will i get the sequence# of other control files then... 
Nirmal,

Re: Buffer Busy Waits -- Sanity check please


Jared
        Say, process A is interested in reading a block, then it hashes the data block address of the block to find the hash bucket in the buffer cache. If that specific block is in the buffer cache, then it must be attached with that hash bucket. Holding the hash bucket latch, the process A will look for the buffer in that hash chain with that data block address . If the buffer is found in the buffer cache, then that process has to examine the state of the buffer before proceeding further.
        If another process B is operating on the buffer, i.e. reading a database block from the disk in to the buffer (for FTS or otherwise), then the process B will pin the buffer and the buffer is not available until the read is completed. So, the process A will wait for the buffer to be unpinned, posting 'buffer busy event'. Since this event can happen in various points in the buffer lifecycles, p3 indicates details about the wait itself.
        Point being that, two processes can not  operate on the same buffer simultaneously. Even though readers do not block readers in terms of locks, they could be blocked due to buffer unavailability, but this event is usually very brief.
        As malcolm suggested, probably, the processes are chasing one another.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Jared Still <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
12/02/01 10:15 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: Buffer Busy Waits -- Sanity check please



Interesting.  Any idea of what the point is in preventing other processes
from reading a block in the buffer?  

Jared

On Saturday 01 December 2001 20:10, MacGregor, Ian A. wrote:
> The P3 value of 130 on the buffer busy waits does indicate that the block
> is being read by another process as Malcolm stated that's the process doing
> the scattered read (Full table scan).  Oracle needs to protect the block
> while it is being read.   The others sessions are waiting until the read of
> that block is complete.
>
> For a definition of the P3 values see Steve Adam's website
> http://www.ixora.com.au/
>
> His full explanation of P3 id 130 is
>
>
>    1013    Block is being read by another session and no other
>  or 130    suitable block image was found, so we wait until the read
>            is completed. This may also occur after a buffer cache
>            assumed deadlock. The kernel can't get a buffer in a
>            certain amount of time and assumes a deadlock. Therefore it
>            will read the CR version of the block.
>
>
> Ian MacGregor
>
> -Original Message-
> Sent: Saturday, December 01, 2001 6:20 PM
> To: Multiple recipients of list ORACLE-L
>
>
>
> Malcolm,
>
> The paragraph below would indicate that readers are blocking.
>
> Readers don't block in Oracle.  The only reason I can think of at
> the moment for a SELECT to cause buffer busy waits is delayed
> block cleanout, of which there has been a lot of discussion lately.
>
> I could be all mixed up here I guess,  it's Saturday and I dont' want
> to think too hard about all this. Don't have time to break out the FM
> so I'll just sit back and wait for you to agree or refute.  ;)
>
> Jared
>
> On Tuesday 27 November 2001 00:25, Thorns, Malcolm (NESL-IT) wrote:
> > Jeff,
> >
> > The 3 sessions are doing the same (or similar) queries.  In this case
> > count(*) which is forcing a full table scan of the table in each session.
> > The 3 sessions are thus trying to access the same blocks from the SGA, 
> > in the same order.  Only 1 session can access a block in the SGA at a
> > time - this is the session showing 'db file scattered read'. The other 2
> > sessions need to wait for the block (these waits show as 'buffer busy
> > waits' - ie waiting for the block in the SGA).  You will see the block id
> > (and perhaps the file id) changing as the FTS's progress.  Thus the
> > sessions are 'chasing' each other through the blocks - holding each other
> > up with SGA block contention - which shows up as 'buffer busy waits'. 
> > Hope that explains things.
> >
> > Regards,
> >
> > Malcolm
> >
> > -Original Message-
> > Sent: Monday, November 26, 2001 11:21 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> >
> > We recently had a new website go live.   Since then, I'm seeing constant
> > buffer busy waits
> > and after a period of time, I see sessions hung on the same block#.   
> > The SQL query
> > is always a COUNT(*) (below).   It's almost as though one session has a
> > lock
> >
> > of some sort in the buffer cache and other sessions are blocked.
> > Although, I've checked and
> > there's no DML ongoing, so I'm unsure as to why we would see this.   Note
> > that v$session shows
> > 78 and 393 to be INACTIVE, while 159 is ACTIVE.    So it's like 159 can't
> > write to
> > the buffer cache because 78 and 393 have a lo

Re: strange NOLOGGING error

2001-11-28 Thread Riyaj_Shamsudeen


Tom
        This is because the index was created using the nologging option after the hot backup was taken. If the table was involved in a nologging operation , that also will have the same problem.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Terrian, Tom" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
11/28/01 02:31 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        strange NOLOGGING error


Can anyone explain this?

I got the following error after restoring a hotbackup data file:

ORA-01578: ORACLE data block corrupted (file # 13, block # 379)
ORA-01110: datafile 13: '/u11/oradata/grp1/DRMS_SM_01.dbf'
ORA-26040: Data block was loaded using the NOLOGGING option

Since the corruption was on an index, no problem, I just rebuilt it.  However, I
was wondering why the table was not also corrupted with the index since they are
on the same file?  Since someone must have done a direct load, why was the
recovery of the index corrupted but not the table?

Oracle 8.1.6.3.0
HPUX 11.0

Tom  

Tom Terrian
Oracle DBA
WPAFB - DAASC
[EMAIL PROTECTED]
937-656-3844 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Terrian, Tom
  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 8i Installation Problem

2001-11-16 Thread Riyaj_Shamsudeen

Bishwa
You may be running in to Pentium P4 bug. Refer to doc. ids 115160.996 and 15730.996. If I remember correctly, you have to copy the whole CD in to hard drive, download and modify few files. You will be able to do the installation from the hard drive..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/16/01 11:16 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Oracle 8i Installation Problem

Dear Gurus,

We are currently using for our project Oracle 8i Personal Edition on
Microsoft Windows 2000 professional server.

Recently we bought a new computer, a Compaq Evo W8000 Workstation with
Pentium Xeon 1.7 GHZ, RAM 512 MB
VideoRAM 64 MB, Hard Disk SCSI 36 GB and DVD ROM etc. etc. and operating
System Windows 2000 Professional Edition with service Pack 2. We want to
migrate our Oracle system and Database to this new Computer.

I have installed other programs (Arc/Info, MS Office etc)in this new
computer without glitch. When trying to install Oracle 8i, the universal
installer donot start. I am really baffled by this. If I introduce the
Oracle 8i Personal edition CD to this new computer, the AutoRun provides me
the first screen but if I press the button "install/deinstall products", the
screen simply vanishes. Well, I know, Oracle 8i needs and install JRE
during installation, but I want to know if the problem is due to this.

Anyway, if I introduce the same CD on old Computer (with Windows 2000
Profession Edition SP1), it runs without any
problem. Here are the few things I have done over this week.

1. Install the Windows 2000 professional edition (SP 1) to the new computer
as dual operating system and tried to
install Oracle -- No result or let us say same result as previous.
2. Downloaded Oracle 8i personal edition from Oracle Server and try to
install -- No Result.
3. Try to install other Oracle Product that came bundled with for evaluation
purpose when we buy Personal edition (for example Oracle 8i Standard
edition, Oracle 8i Enterprise Edition, Online Documentation etc. etc. ) --
No result
4. Download from the Microsoft Site Windows Virtual Machine(!!??) --- No
result

Oracle 8i installtion is pretty straightforward (until now) with the
Universal Installer. I am, somehow, couldnot start universal installer in
the new machine.

Well I think I have exhausted all the resources trying to install Oracle in
the new machines. As a last resort, I am thinking of switching the hard disk
from the old computer to the new one. This solution, in my opinion, is sort
of drastic.

Can anybody suggest me what is wrong and please help me find the solution.

I am afraid my mail is too long for all of you busy persons but I am pretty
much baffled by the event.

Regards

--
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: extent deallocation question

2001-10-29 Thread Riyaj_Shamsudeen

Steve
        I thought the drop/truncate of the objects will do 'checkpoint object ' call and DBW in turn will do extent based checkpointing. I am not even going to try argue with you :-) Could you please explain to us : What are the reasons for 'checkpoint object call ' and  ' reuse block range calls ? What statement triggers them ?

Thanks in advance,
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

"Steve Adams" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/29/01 05:00 AM
Please respond to ORACLE-L

        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: extent deallocation question

Hi Jeremiah,

The cross instance call is a "reuse block range" call, not a "checkpoint object" call. Thus a separate scan of the cache
is needed for every extent. The reason why dirty blocks from dropped extents cannot be allowed to stay in cache is that
the blocks might immediately be allocated to another object.

@   Regards,
@   Steve Adams
@   http://www.ixora.com.au/              -  For DBAs
@   http://www.secularislam.org/call.htm  -  For Muslims
@   http://www.christianity.net.au/       -  For all

-Original Message-
Sent: Monday, 29 October 2001 13:55
To: Multiple recipients of list ORACLE-L

Thanks for the reply.  This is the first time I have heard of an
object-level checkpoint.

I still don't understand a couple things about this.  If the wait is
for checkpointing of the blocks composing the deallocated extents, why
does a 2Gb segment with 10 extents drop so much faster than a 2Gb
segment with 20,000 extents?  Does the session performing the DDL
require the object checkpoint to complete for every extent
deallocated, before the DDL proceeds?

Why should such a checkpoint be necessary?  Why can't the blocks just
go on the LRUW list like all the other dirty blocks?

If the speed of these object checkpoints is limited by the checkpoint
batch portion of the DBWR write batch, can they be sped up by
increasing the value of _db_block_checkpoint_batch to a larger
proportion of the DBWR write batch (as ascertained from x$kvii)?

It seems like if they just put the blocks from deallocated extents on
the dirty list, then the checkpointing could be automatically made
more agressive through use of the db_block_max_dirty_target feature.
That way as soon as a lot of dirty blocks got put on the LRUW list,
the checkpoint portion of the write batch could be automatically
increased.

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

On Sun, 28 Oct 2001, [EMAIL PROTECTED] wrote:

>         When you truncate or drop an object, all the extents/buffers
> associated with that object need to be flushed to the disk, meaning an
> object checkpoint has to take place. DBW recieves an object checkpoint
> call, from the client process. Client process then waits for the IPC
> message from the DBW process for the object checkpoint call completion.
> The wait event you see is that the client is waiting for this IPC message
> from DBW. Once DBW completes flushing all the buffers associated with the
> objects, DBW will send back an IPC message to the client.
>         Interestingly, there is are two lists( main and auxcillary lists)
> in 8i buffer cache just for this object checkpoint call, which is supposed
> to improve the performance of these object checkpoint calls. If you have
> very large buffer cache, DBW can take long time to flush these buffers.
>         If you truncate a table, then all the indexes associated with
> these tables (an their buffers) need to be checkpointed. Further, only
> fraction of writes are dedicated for these checkpoints and that could be
> another reason for longer waits.
>
> Jeremiah Wilton <[EMAIL PROTECTED]>
>
> Using dictionary-managed tablespaces, it can sometimes take a very
> long time to drop or truncate a segment with many tens of thousands of
> extents, because Oracle takes a long time to update UET$ and FET$.
>
> This can be a serious problem for some people because the session
> performing the DDL holds the ST enqueue for the duration of the extent
> deallocation.
>
> During these long extent deallocations, I observe the session
> performing DDL waiting on IPC with the database writer.  Can anyone
> tell me why these long extent allocations spend the vast majority of
> their time waiting on DBW0?  Does every block in UET$ and FET$ have to
> get written out before the deallocate can proceed?  If so, why?
>
> Using a normal application, I can delete and insert tens of thousands
> of rows in just a few seconds.  What takes Oracle so long with UET$
> and FET$?
>
> BTW, this is not a problem that I personally have - it is a purely
> academic question.

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

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet acc

Re: extent deallocation question

2001-10-28 Thread Riyaj_Shamsudeen

Hi Jeremiah
When you truncate or drop an object, all the extents/buffers associated with that object need to be flushed to the disk, meaning an object checkpoint has to take place. DBW recieves an object checkpoint call, from the client process. Client process then waits for the IPC message from the DBW process for the object checkpoint call completion. The wait event you see is that the client is waiting for this IPC message from DBW. Once DBW completes flushing all the buffers associated with the objects, DBW will send back an IPC message to the client.
Interestingly, there is are two lists( main and auxcillary lists) in 8i buffer cache just for this object checkpoint call, which is supposed to improve the performance of these object checkpoint calls. If you have very large buffer cache, DBW can take long time to flush these buffers.
If you truncate a table, then all the indexes associated with these tables (an their buffers) need to be checkpointed. Further, only fraction of writes are dedicated for these checkpoints and that could be another reason for longer waits.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

Jeremiah Wilton <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
10/28/01 03:35 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: extent deallocation question

Using dictionary-managed tablespaces, it can sometimes take a very
long time to drop or truncate a segment with many tens of thousands of
extents, because Oracle takes a long time to update UET$ and FET$.

This can be a serious problem for some people because the session
performing the DDL holds the ST enqueue for the duration of the extent
deallocation.

During these long extent deallocations, I observe the session
performing DDL waiting on IPC with the database writer. Can anyone
tell me why these long extent allocations spend the vast majority of
their time waiting on DBW0? Does every block in UET$ and FET$ have to
get written out before the deallocate can proceed? If so, why?

Using a normal application, I can delete and insert tens of thousands
of rows in just a few seconds. What takes Oracle so long with UET$
and FET$?

BTW, this is not a problem that I personally have - it is a purely
academic question.

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

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

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

Re: Fast Full Index Scan in 8.1.7

2001-09-07 Thread Riyaj_Shamsudeen

Cherie        
        You could turn it off using _fast_full_scan_enabled=false at the instance level. fast_full_scan_enabled is simply an underscore parameter in 8.1.7. But, I would turn on the event 10053 and run the statement to find why CBO is not using.. Could it be possible that this behaviour is due to poor index selectivity ? 
        alter session set events '10053 trace name context forever, level 1';

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
09/07/01 06:55 AM
Please respond to ORACLE-L

        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: Fast Full Index Scan in 8.1.7

I wanted to turn it off in testing so I could try some other indexes as an
alternative.   I'm not sure that I trust it to make a good decision.

Even with an alternative index hint, it's not using the index I'm trying to
get it to use, but rather the fast full index scan.  I can't see any typo
but
maybe it just can't use that index with full full index scan enabled.

That's why I wanted to try and disable it and see what would happen.

CBO does some interesting things.  Every time I think I have a handle
on some aspect of it, it changes and starts doing something different.

Thanks for your reply.

Cherie

                    Jared Still                                                                                    

                    n.com>               cc:                                                                       
                                         Subject:     Re: Fast Full Index Scan in 8.1.7                            
                    09/07/01 12:04                                                                                 
                    AM                                                                                             

The alternative is probably a full table scan.

Are you sure you want to turn it off?

I think you need to hint your SQL to do that.

Jared

On Thursday 06 September 2001 13:21, [EMAIL PROTECTED] wrote:
> I had been using the FAST_FULL_SCAN_ENABLED initialization parameter in
an
> 8.0.4 database.
>
> Apparently this parameter is now obsolete in 8.1.7.2.   Seems as though I
> am getting the fast full index
> scans automatically now.  I'm not sure if I want them in all cases.   Is
> there a way to turn them off?
>
> Thanks,
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network

-- 
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: Parallelism in oracle8i

2001-08-30 Thread Riyaj_Shamsudeen


Hi
        What does your explain plan say with and without parallelism ? Analyzing the explain plan will be a good start..
        Also, You will benefit from parallelism only if the PQ slave processes operate on different disks /different controllers etc. The performance (of this particular query, at least ) depends upon how the table partitions and their indexes are laid out physically. Do you have any indexes on this column ? What kind of indexes are they( local, global etc)? Are those index partitions on separate disks ?
        If these partitions are not physically separated then due to the overhead associated with the message transfer between the PQ slave processes, the performance could be worser than "noparallel" query.
HTH
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Soman Manoj <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/30/01 06:15 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Parallelism in oracle8i


Hi,

We are doing testing of oracle 8i parallelism features
on unix HP 11 .
We have done 2 partitions on the table ib020mb_3
on a field adt_date with the following details
(partition p1 values less than('22-apr-2000')
tablespace CONTRACT,
 partition p2 values less
than('01-jUn-2000')tablespace CONTRACT1).

Each of these partitions are in different tablespace.
Each of this tablespace are on different disks(data
files on different
disk ) in unix environment. We have 4 processors and 4
controllers
(like disk drives) to write on disk.

With this details we are running the following query
with and without 
degree of parallelism specified.

select  adt_date from ib020mb_3
where adt_date <'01-jun-2000'

But we are taking more time with parallelism. We have
tried the query with 
different degrees of parallelism. Currently we are
fetching approx. 15 lac records.
We have tested with a different table and different
set of records but still we
are taking more time with degree of parallelism
specified.

Could you please suggest why we are taking more time .

The following are the tkprof generated for this
queries:

==
1) Without parallelism:

select  adt_date from ib020mb_3
where adt_date <'01-jun-2000'

call     count       cpu    elapsed   disk  query
current   rows
--- --   -- -- --
-- ---
Parse        1      0.12       0.31     21    204     
5       0
Execute      1      0.00       0.00      0      0     
0       0
Fetch   101340     11.37      16.48   7107 107826     
0 1520078
--- --   -- --- --
-
total   101342     11.49      16.79   7128 108030     
5 1520078

Total time taken : 28.28
2)With parallelism:

select  /*+parallel(ib020mb_3,2)*/ adt_date from
ib020mb_3
where adt_date <'01-jun-2000'

call     count       cpu    elapsed   disk  query
current    rows
--- --   -- -- ---
--  --
Parse        1      0.13       0.61     21    243     
 6       0
Execute      1      0.00       0.00      0      0     
 0       0
Fetch   101340     11.72      16.79   7107 107826     
 0 1520078
--- --   --
-
total   101342     11.85      17.40   7128 108069     
 6 1520078
                      

Total time taken :29.25                    
==
===
                           

Thanks In Advance




__
Do You Yahoo!?
Get email alerts & NEW webcam video instant messaging with Yahoo! Messenger
http://im.yahoo.com
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Soman Manoj
  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: !! Very important Oracle-L message !!

2001-08-29 Thread Riyaj_Shamsudeen

-->I am willing to donate up to $5 per month for up to 6 months paid either monthly or a lump sum depending on what he needs.
-->Just like in Public Television or Radio, I challenge everyone on the list to join me with an equal pledge.

Count me in...

Jarad/Bruce
Please let us know how you want us to pay.. We will act accordingly.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"John Carlson" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/29/01 12:51 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Re: !! *Very* important Oracle-L message !!

As I see it, he needs $500 per month for 4 or 5 months. Lets just round that to 6 months times $500 or $3000.
I don't know how many people are on his 60+ mailing list but just guessing, I believe if only 1/4th the people contributed $1 per month for 6 months or just $6 that would cover it.
I am willing to donate up to $5 per month for up to 6 months paid either monthly or a lump sum depending on what he needs.
Just like in Public Television or Radio, I challenge everyone on the list to join me with an equal pledge.
Regards,
John Carlson
[EMAIL PROTECTED]
http://www.cj.com

>>> [EMAIL PROTECTED] 08/28/01 03:28PM >>>

Ok, now that I have your attention, please read the following message from
Bruce Bergman,
owner of fatcity.com. It concerns the future of this list.

Please read the entire message before replying.

Thank you.

Jared Still ( List Owner )

---

Hello ORACLE-L list members --

I would like to take a few minutes of your time for a "State of the Union"
address, if you can call it that. This is a rather serious message, and I
request that you take the time to read this message entirely. I've got a
problem that needs resolution this week, and I'm hoping you folks can help
me come to a conclusion. This message turned out to be fairly
long-winded, so please excuse me for that. :-)

Fat City (fatcity.com) is my company, and I am the provider of the list
services that you receive ORACLE-L through. I've been hosting Internet
mailing lists for over 10 years, and carry quite a few lists, including a
big selection of Oracle-related lists (14, in fact). Fat City has always
been a labor of love for me. Over the years it has rarely generated any
income at all, and it usually does not cover my expenses -- most of the
time
it is a monthly loss for me. The vast majority of my 60+ mailing lists
have
been hosted for free, at my expense (as is the ORACLE-L list, for example).
Yet I have absorbed these costs because I enjoy hosting discussion lists
and I know a lot of lists can't afford to pay for quality service.

I am soon to be one of the Rhythms Orphans. Rhythms, my DSL provider, has
gone bankrupt and will be out of business by 10-Sep-2001. I need to find
another provider of high-speed Internet in order to continue operations. I
am at the point of either folding shop completely and going out of business
myself, or trying to make Fat City a viable, money-making endeavor. I need
to make this decision by this Friday (for financial and planning reasons).
After Friday, I am committed one way or the other.

My upstream ISP and I are on very good terms, and in fact, I've been a
customer of theirs since 1985! As a result, they have graciously offered
to help me out of this situation at considerable expense to themselves.
I'm not at liberty to discuss the terms of the contract I would engage in
with them, but suffice to say that is one of those once-in-a-lifetime
opportunities that I'd be a fool to pass up. I have this offer personally
from the President of that company, if I want it. Simply said, my ability
to take them up on their offer and switch Fat City over to a dedicated T1
or
better solution revolves around being able to generate quite a bit of
additional
income per month, and very soon. In an effort to be open and up-front with
you folks about this, I basically need to generate an additional $500 more
per
month. Right now that seems daunting, but I've been working hard to line up
that
additional income, and I believe I can do it. Just not right away. My
estimates are that it will take me four or five months to come up to that
level. I need to get out there and sell website hosting and list hosting
packages, and get new customers. That takes time, unfortunately. I need a
buffer to enable me to get past this startup phase.

After some discussions with Jared (the list owner), and some encouragement
from him, I am coming to you to ask for you help in keeping Fat City
financially afloat until I can be self sufficient. I'm not asking for a
specific dollar amount from anyone, nor am I *expecting* anything from you
folks. But if it is within your power to contribute some money towards
this
end (and yes, I know these are hard financial times for eve

RE: OT : kernel using 75% of CPU

2001-08-28 Thread Riyaj_Shamsudeen


Hi jerry
        Since the most of the cpu is being used in system mode, I would not suspect latch contention at all. If you have any latch contention the usage will be in user% mode instead of system mode. May be you/ your sysadmin need to check the error logs in the EMC or in the server itself. 75% CPU is way high, only hardware errors such as disk problems / controller problems will cause this..
        You could truss the Oracle processes with 'truss -p pid' utility and see what system calls the processes are making and that may give a clue..        
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Kevin Lange <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/28/01 05:20 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: OT : kernel using 75% of CPU


Jerry;
  Has there been any system parameter changes lately ??
 
I don't know about on your system, but on our AIX box there was a parameter called MBUFs that dealt with Communication Buffers.  Now, you would not think this would have any consideration on the database, but it did.
 
MBUFS is the Maximum Allowable Communication Buffer on an AIX system.   We thought that it dealt with the networking and set it up to its maximum of 64 Meg.   We also did not think this would bother Oracle boy were we wrong.
 
Apparently, its also Interprocess comminications as well.  And, since Oracle is greedy, it likes to take all the memory the system will give it.  It turned out that each process that was started (i.e. every user who logged on ) grabbed the maximum memory setup by MBUFS.  So, take a 900 Meg SGA and add onto it a 1 Meg Sort area per user and a 64 Meg MBUF per user and 350 users and you can see why our 4GB of memory went real fast.
 
Sometimes, things that don't appear to be related can all of a sudden jump up and bite you.
 
Kevin
-Original Message-
From: Jerry C [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, August 28, 2001 4:46 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: OT : kernel using 75% of CPU

Guy,
 
Thank you very very much, this is a great explanation, and is much appreciated.
 
To answer some of your questions (and add a few!):
 
Yes, our client is experiencing performance problems.
 
vmstat and swap -s seem to show some swapping:
 
csuaor46> vmstat 2 10
 procs     memory            page            disk          faults      cpu
 r b w   swap  free  re  mf pi po fr de sr s6 s1 s1 s5   in   sy   cs us sy id
 2 0 0  14536 14776  67 1514 15 228 957 62760 262 0 2 2 0 640 115 931 25 25 50
 2 0 0 6179304 62416 36 1893 0 744 5500 56488 1126 0 4 4 0 988 6159 917 23 55 22
 1 0 0 6177696 62600 20 908  0 1032 11808 56488 2488 0 6 6 0 917 3781 667 24 51 25
 0 0 0 6181688 62960 89 1528 4 288 444 56488 37 0 6 6 0 1076 19029 862 23 54 23
 1 0 0 6181336 64432 15 1269 0 140 576 56488 110 0 1 1 0 456 8550 493 14 46 40
 0 0 0 6182376 63776 18 2976 4 368 1008 62760 328 0 8 8 0 594 6163 831 14 54 32
 2 0 0 6180800 63072  9 1746 0 300 1296 62760 202 0 1 1 0 661 4441 693 12 65 23
 0 0 0 6178120 62728 47 1311 4 612 2272 56488 464 0 3 2 0 829 5535 801 34 38 28
 2 0 0 6179944 64616 36 1322 0 364 764 62760 70 0 0 0 0 996 4786  739 13 69 18
 0 0 0 6183112 62560 40 856  4 340 1444 62760 339 0 1 2 0 822 4107 707 10 40 50
csuaor46> swap -s
total: 2602216k bytes allocated + 19960k reserved = 2622176k used, 6177752k available
I would assume swapping operations would be included under "kernel"? The app also uses java, is there any way to determine if Java is performing any wacky system calls?
 
There are 3 databases on this box, which has 4 Gb. of memory:
 
csuaor46> ps -ef |grep ora_ |grep smon
  oracle   867     1  0   Aug 16 ?        0:13 ora_smon_tstrn
  oracle   981     1  0   Aug 16 ?        0:14 ora_smon_tsdmo
  oracle 19561     1  0   Aug 23 ?        0:35 ora_smon_tsprd
The main db (tsprd) has an SGA of 1.7 Gb., the other 2 are ~180 Mb. each. - so that's ~2.1 Gb. There are only 49 connections to the 3 databases:
 
csuaor46> ps -ef |grep LOCAL |wc
      49     447    3418
 
We are not using MTS. Is there any way to determine the amount of real memory that these dedicated connections are using? I can't see how the whole 4 Gb. would be used, causing the system to swap... ?
 
The primary database:
- has an SGA of 1.7GB
    shared pool  550Mb.
    buffer cache 640Mb.
    java pool    470Mb.! (>460Mb. of which is free)
- logical I/O rate ~3,000 blocks/sec.
- physical I/O rate 500-1,000 I/O/sec (disk is EMC, RAID 1+0 I think)
 
Everything internal to the db doesn't look that bad, although I'm guessing they don't need so much java pool and the shared pool could be downsized...
 
Still stumped
 
Thanks again.
 
csuaor46> iostat -xtc 15 20
                               extended device statistics      tty         cpu
device    r/s  w/s   kr/s   kw/s wait actv  svc_t  %w  %b  tin tout us sy wt id
sd6

Re: ENQUEUE RESOURCES and DML LOCKS

2001-08-15 Thread Riyaj_Shamsudeen


v$resource_limit. Look at the max_utilitization and initial_allocation columns..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Post, Ethan" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/15/01 02:51 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        ENQUEUE RESOURCES and DML LOCKS


What would I see if these are set too low and where would I find it?
 
- Ethan
- http://www.geocities.com/epost1  

--
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law.  If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy.   Thank you.

==
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Post, Ethan
  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: Performance analysis (enqueue and buffer busy waits)

2001-08-15 Thread Riyaj_Shamsudeen


Ethan
         You are committing 50 times / second which is quite a bit high. So, LGWR has to write out the buffers at least 50 times in a second (excluding the group commits). I didn't quite follow the initial threads, but are you using file system based log files ? For this type of high activity, I would recommend using async IO and raw / Quick IO log files. You may need to read the tip by Steve Adams..
        http://www.ixora.com.au/tips/creation/raw_log_files.htm

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Post, Ethan" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/15/01 01:53 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Performance analysis (enqueue and buffer busy waits)


12 CPU machine redo logs on separate disks and controller, nothing else on
them.  Application puts PK's on data tablespace so might be some contention
there, other indexes are on index tablespace which is separated from data
disks.  

The job causes:

1. Between 37 and 48 MB of rollback per minute
2. 600-700 allocation retries per minute
3. 250,000 log buffer entries per minute
4. 600-700 log buffer spaces waits per minute
5. Almost no disk sorts
6. 3000 commits per minute, this will have to go much higher to meet
requirements...ouch.
7. 2-3 redo log space requests per minute
8. 300-400 buffer busy waits per minute, v$waitstat shows they are for data
blocks
9. Average log write size shows around 22K at the moment but was not
tracking during test.  I will set up 
a metric to track this next time.

Thanks for all the help.  I will be setting event 10046 for the next test
and setting up a few more metrics.  I guess the real question is how fast
can this job ultimately go if the above stats represent a paltry 230 sales
order lines per minutes.  I think redo is going to be the big bottleneck as
I don't see a lot of ways to reduce it since we are talking about a packaged
ERP app unless the client wants to pay to have the job rewritten which is
going to be big $.  



>-Original Message-
>From: Jonathan Lewis [mailto:[EMAIL PROTECTED]]
>Sent: Wednesday, August 15, 2001 4:36 AM
>To: Multiple recipients of list ORACLE-L
>Subject: Re: Performance analysis (enqueue and buffer busy waits)
>
>
>Ignore (or at least treat very lightly) the comments about DBWm.  
>
>Excessive database writes can cause log file sync waits, as
>dbwr calls lgwr to write the log protecting the blocks it is
>about to write.  In this case, you will see v$session_event
>for the db writers showing log file waits.  Unfortunately I 
>have a mental block that makes me explain this phenomenon
>100% the wrong way round every 6 months or so. And that
>is what I did last night.  
>
>I suspect you are also going to tell me that the anomalous
>difference between CPU and elapsed time on the one big
>update is because everything else is done by triggers,
>and the update is waiting for triggers to complete ;(
>
>4GB of redo log in 50 minutes is quite a lot.
>Unless I've done the arithmetic wrong, that's
>close to 1.4MB per second (or 2.8 since you are
>using Oracle duplexing). It seems a little odd that 
>you are getting 'log file sync' as a problem
>without getting (in your case) 'log buffer space'
>and 'log file ... write'.
>
>What is your average log file write size ?
>(redo blocks written / redo writes). and
>what do you other 'redo%' stats look like
>over the period ?
>
>How many CPUs ?
>
>
>Jonathan Lewis
>
>Seminars on getting the best out of Oracle
>Last few places available for Sept 10th/11th
>See http://www.jlcomp.demon.co.uk/seminar.html
>
>
>-- 
>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).
>

--
This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law.  If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy.   Thank you.

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

Fat City

RE: Speed up Truncate tables

2001-08-15 Thread Riyaj_Shamsudeen


Not true, at least in 8i. Using 'alter table move ' you could change the initial extent size..

SQL>alter table test move storage (initial 2M);

Table altered.

Only catch here is that 'move' command doesn't apply to tables having long ,long raw, LOB etc..Initial extent can be changed for the remaining tables..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Christopher Spence <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/15/01 02:22 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Speed up Truncate tables


You are correct, unless your using 9i, you cannot alter the initial extent
without dropping the table.

"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: Wednesday, August 15, 2001 3:07 PM
To: Multiple recipients of list ORACLE-L


Thomas,
are your sure you can change the initial extent?  My senior dba told me once
it is not possible; you have to drop and recreate table if there is a need
to change Initial extent. I am going to play with it today.

-Original Message-
Sent: Wednesday, August 15, 2001 1:27 PM
To: Multiple recipients of list ORACLE-L


Chuan,

Kevin is correct.  If your truncate table is taking a *long* time (and the
table is not locked by another process), it's because your storage params
are incorrect for the amount of data you are holding.

Look at initial and next in comparison with the number of extents
(DBA_EXTENTS view) for the table in question, and modify them before you
load the data. You can modify the INITIAL extent by issuing an 'alter table
allocate extent(size x)' command to grow the INITIAL extent.  
You can also modify the NEXT extent by issuing an 'alter table storage (next
x)' command to change the NEXT extent.

hope this helps.

Tom Mercadante
Oracle Certified Professional


-Original Message-
Sent: Wednesday, August 15, 2001 11:02 AM
To: Multiple recipients of list ORACLE-L


I had the same problem when truncating a huge table (24 Mill rows).   It
turned out that the reason my table was taking so long was the amount of
extents I had on it.  I could look at what was actually happening during a
truncate and it had to go and take each individual block and put them back
in the available lists.  

Well, after changing the settings on the table to make larger extents (and
therefore fewer) the truncates on that table went hundreds of times faster
(we had real bad settings on that table before).

You might investigate your storage parms and see just how many extents you
do have on that table.

-Original Message-
Sent: Wednesday, August 15, 2001 3:10 AM
To: Multiple recipients of list ORACLE-L


Hi All,

    Is there any way to speed up the truncating a big table with 12 million
rows?

Basically, I implemented truncating that big table on Production, but it
affected the performance much, so I had to stop it in the middle of way. All
the rows were truncated but the HWM was not shrunk at all. I want to do it
again to get the space back. Is there any way to speed up this process?

Platform: Oracle EE8.0.6 and Solaris 2.7

Thanks a lot in advance.

Chuan

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

Fat City Network Services    -- (858) 538-5051  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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mercadante, Thomas F
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, Califo

Re:RE: Re[2]: How can I get my DB back if I lost one of my r




Hi all
        I did some research in my test database and here is what I found
out: I had events 10500,10221 and 10218 set in the database to trace the
instance recovery. 10218 was particularly useful since it was dumping the
uba of applied undo.
        a. Shutdown abort did not rollback any transactions( as expected).
Shutdown immediate signalled  smon and smon rolled back all the uncommitted
transactions. I could see that the smon was applying uba in the trace file:
-

uba: 0x0100010d.0003.11
uba: 0x0100010d.0003.10
uba: 0x0100010d.0003.0f
uba: 0x0100010d.0003.0e
uba: 0x0100010d.0003.0d
uba: 0x0100010d.0003.0c

        b. Inserted few thousands rows, did a shutdown immediate, renamed
the rollback segment data file and tried to open the database. Of course
DBW was not happy, created a backup control file to trace and offline
dropped the datafile. Still complained about the rollback entries from the
init.ora file, so commented out those entries and was able to open the
database.
        c. Inserted few thousands rows again, did a shutdown abort this
time, renamed the rollback segment data file and tried to open the
database. Did the same steps as in step (b). Even after commenting out
rollback segments, still smon was complaining about "unable to access the
rollback segment" and database did not open at all.

SMON: about to recover undo segment 2
SMON: mark undo segment 2 as needs recovery
SMON: about to recover undo segment 3
SMON: mark undo segment 3 as needs recovery
SMON: about to recover undo segment 4
SMON: mark undo segment 4 as needs recovery

        Recreated the control file with the roll back segment datafile and
now database opens fine..This explains Dick's experience. In my case, the
database crashed and the rollback segment file was corrupted and had to use
_corrupted_rollback_segments to solvage the data. If you shutdown clean
(immediate or normal), losing rollback segment is no big deal, otherwise
say your prayers...

HTH,
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com
|++|
||   [EMAIL PROTECTED] ||
||   Sent by: |           To:  |
||   [EMAIL PROTECTED] |   Multiple recipients  |
|||   of list ORACLE-L |
||   08/14/01 01:27 PM|   <[EMAIL PROTECTED]|
||   Please respond to|   >|
||   ORACLE-L |           cc:  |
|||           Subject: |
|||   Re:RE: Re[2]: How can|
|||   I get my DB back if I|
|||   lost one of my r |
|++|






Lisa,

   I believe that is why using "shutdown abort" is not truly endorsed by
Oracle, except as a last resort.  What I've noticed on the list is the
desire by
some to over use this method of shutting down the DB.  Now I will admit
that
"shutdown normal" is a real pain in the back side since every one HAS to
log off
before the DB will shutdown.  And "shutdown immediate" does have a tendency
to
hang around way too long on occasion.  Using abort though as the fix all is
a
really bad thing to do.  One item I've taken is to allow the DB to stay in
the
"shutdown immediate" state for at least 10 minutes before aborting it.  The
reason is that during that time the background processes have the
opportunity to
rollback/commit all of the transactions that were taking place thereby
cleaning
out the rollback segments.  If by the time 10 minutes has expired your DB
has
not shutdown, you can be 99% confident that the problem is a truly dead
process/client who is no longer talking to Oracle and who's transactions
have
been handled.  It's just a matter that Oracle cannot handshake with the
client
anymore that's holding up the shutdown.  Then one can abort the instance
with
every confidence that it will restart later.  Experience here has proved
that
the startup in this case will occur 5 times faster than just firing off a
"shutdown abort" without the time period.  Therefore to my SA's chagrin,
all of
my scripts call "shutdown immediate" and "shutdown abort" is left to a
manual
process.

In my last response, privately, to Riyaj I stated that I had repaired a DB
that
lost it's rollback tablespace to a disk failure.  The problem arose on db
startup after a normal cold backup.  Since the DB was shutdown with the
immediate vs. abort option all of the rollback was cleaned out before
Oracle
shutdown, and it did so normally.  When the restart failed it was just a
matter
of dumping the datafile offline and the tablespace

Re: How can I get my DB back if I lost one of my rollback segment (rb


Hi
        Recovery process will read from undo$ table to identify all the rollback segments that were online. Even if you comment out the rollback segments from the init file, still Oracle knows what rollback segments were online from the undo$ table. If i remember correctly, after reading the undo$ table, the process will read  at least the rollback segment header and then only it proceed to open the database. If that rollback segment is not available, then you will not be able to open the database without using the underscore parameters.
        IMHO, Oracle support need to be contacted for further resolution..
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Rachel Carmichael" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/14/01 08:40 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: How can I get my DB back if I lost one of my rollback segment (rb


Hermanto,

some more information would be helpful -- why are you restoring? are you 
restoring from a cold or hot backup?

if it was from a cold backup, you can comment out the rollback_segments 
parameter in the init.ora and start the database. Then I would drop the 
rollback tablespace and recreate it, with the rollback segments you want. 
Then change the init.ora and shutdown and restart the database

Rachel

>From: Hermanto P <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: How can I get my DB back if I lost one of my rollback segment (rb
>Date: Tue, 14 Aug 2001 00:35:21 -0800
>
>Hi gurus,
>Recently, I have forgotten to backup one of rollback segment file
>(rbs02.dbf).
>When I want to restore..the db can not startup normally.
>Anyone can give me advise..
>Thanks in advance.
>
>Warm regards,
>Hermanto
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Hermanto P
>   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: Indexes Used per Query


Yes. It can. In fact, AND_EQUAL hint can be specified to use multiple indexes and merge them for single table access path..

From tuning guide:
AND_EQUAL
        This hint explicitly chooses an execution plan that uses an access path that merges the scans on several single column indexes.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Walter K <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/14/01 09:06 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Indexes Used per Query


Will the optimizer (CBO) ever use more than one index
in the access path for a table in a query? I have
never seen this behavior and have always been curious.

__
Do You Yahoo!?
Make international calls for as low as $.04/minute with Yahoo! Messenger
http://phonecard.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Walter K
  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: How can I get my DB back if I lost one of my rollback segment (rb

Hermanto
Rollback segment data files are *needed* for recovery. Rollback segments are the key component for consistent media/instance recovery. If you lose any of the rollback segment files and if any extents from that datafile are needed then the database can not do the recovery consistently and will give you errors. Is it the only data file associated with the rollback segment tablespace. If that is the case, there will be extents from the rollback segments. If there are no extents from the rollback segments then you could potentially offline drop the datafile, do the media recovery and do open resetlogs. What errors are you getting ? Stuck recovery ? Errors will tell you whether the recovery process is looking for rollback segments or the data file.
Please contact Oracle support if there are any rollback extents in this datafile. _offline_rollback_segments and _corrupted_rollback_segments parameters can be used to hard open the database. But DO expect data consistency errors. In fact, only thing you should do is open this way, extract the data out and import in to a new database. Contact Oracle Support before you do this since there are serious consequences of doing this.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

Hermanto P <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/14/01 03:35 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: How can I get my DB back if I lost one of my rollback segment (rb

Hi gurus,
Recently, I have forgotten to backup one of rollback segment file
(rbs02.dbf).
When I want to restore..the db can not startup normally.
Anyone can give me advise..
Thanks in advance.

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

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

Re: X$_kglcursor.kglnaobj

2001-08-13 Thread Riyaj_Shamsudeen


Ian
        These rows relate to the library cache pin on the metadata about the objects. Metadata for the objects need to be pinned during the execution of the statement to prevent the invalidation of the dependent objects. Since each one of this sid has this object pinned, I think, you have objects with these names in your database. Does your database have these objects ?

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"MacGregor, Ian A." <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/10/01 06:45 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        X$_kglcursor.kglnaobj


I use the following statment when monitoring the database

select   sid, s.username,s.osuser,   c.kglnaobj from   sys.x$_kglpn  p,   sys.x$_kglcursor  c,   v$_session  s
where   p.kglpnhdl = c.kglhdadr and   p.kglpnses = s.saddr;

Here is some output showing sid and kglnaobj:

       28
table_1_0_116_0_0_

       80
SELECT LOC,RACK,SLOT,ELE,CONNNUM,PIN,RLOC,RRACK,RELE,RSLOT,RCONNNUM,RPIN FROM CAPTAR.LINKTERM WHERE
(LOC=:1) and (RACK=:2) and (CONNNUM=:3) and (PIN LIKE '%L') order by loc, rack, ele, slot, connnum,
orderpin, pin

       28
table_1_0_112_0_0_

       28
table_1_0_11a_0_0_

       82
table_1_0_112_0_0_

       28
table_1_0_11a_0_0_

       82
table_1_0_112_0_0_


What is the meaning of  the lines which start with "table_"?

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: MacGregor, Ian A.
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- (858) 538-5051  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: O/S copies of datafiles

2001-08-09 Thread Riyaj_Shamsudeen

Sujatha
Yes, it will work assuming your production and test boxes are of the same OS, version etc. Technically, at least with the same endianness. You should take copy all the files including control files , init files, data files and redo log files to the test server. Then you could bring up the database. Since you are running 8.0.6 in the test server and the database you have restored is 8.0.5, you need to upgrade it from 8.0.5 to 8.0.6. I guess, you have to run upgrade script u0800050 to upgrade. Refer to the migration document for this upgrade.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

Sujatha Madan <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
08/08/01 09:05 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: O/S copies of datafiles

Hi,

This might be a stupid question, but I'll give it a go anyway.

I have two Unix boxes - a production and a newly created test box. The
production box has 8.0.5 and the test has 8.0.6 installed.

Is it possible to stop the database, take a backup of all the datafiles
(through the operating system) and then move them to the test server (in the
same directory structure they were in in the production box). Would the
database open successfully?

Thanks in advance for all your help.

Regards,

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

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

Re: disk corruption?

2001-06-28 Thread Riyaj_Shamsudeen


Hi Jerry
        block # 262144 is the last block in a 2GB file assuming 8k block size. There are few Oracle bugs associated with this..Refer docid 110573.1. Also, refer bugs  1063586 or 1152315. It is to do with files beyond 2GB.  Also What version of Oracle you are running ?


         Also you may want to truss Oracle shadow process and see what call is failing..




Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Jerry C" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/28/01 11:23 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        disk corruption?


Hi all,

I'm getting a disk error:

ORA-01115: IO error reading block from file 9 (block # 262144)
ORA-01110: data file 9: '/orafiles/data01/xlepmprd/ewlarge.dbf'
ORA-27072: skgfdisp: I/O error
SVR4 Error: 25: Inappropriate ioctl for device
Additional information: 262143

My sysadmin tells me that the disk is ok, since there are no errors in
/var/adm/messages (I didn't see any, either). Is there a way to verify that
the disk is free of errors?? Any other log files to look at? The platform is
Sun:

SunOS msubxm01 5.6 Generic_105181-12 sun4u sparc SUNW,Ultra-4


Thanks!

Jerry





_
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: Jerry C
  INET: [EMAIL PROTECTED]

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

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

Re: db won't mount

2001-06-27 Thread Riyaj_Shamsudeen

Hi Henry
You may be hitting a bug 1399885 due to timer overflow. Can you check the uptime ? Also check the clock ticks per second. Due to a timer overflow problem, when the machine is up and running for more than 248 days (ticks=1000) or 24 days (ticks=100) then the database will hang and startup and shutdown will hang.

On Solaris, the command "/usr/bin/getconf CLK_TCK" will return the
number of clock ticks per second. If the number is 1000, the system is
impacted 24 days after reboot. If the number is 100, the system is
impacted 248 days after reboot.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

Henry Poras <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/27/01 04:54 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: db won't mount

I've got a cute one here. I saw something like this years ago, but I can't
remember what I did. The database is running Oracle 8.0.6 on Solaris 2.6. I
was just brought into this problem, so I don't know too much about the
database. It is shutdown nightly for a cold backup, and by the looks of
things it never started up again after yesterday's shutdown. After that,
numerous attempts at shutdown/startup appear in the alert.log and
rdbms/audit. These are the facts as I know them:

I did a shutdown abort succsessfully.
Startup nomount worked.
Alter database mount sits forever. (right now it has been sitting for 3
hours. Looking at the alert logs, it sat for about 12 hours between last
nights attempt to startup and the first shutdown run today). The whole
database appears to be 600M (unless some files are hidden in funny spots).
No errors in the alert.log, no trace files, no core dumps, nothing in cdump,
udump, bdump, $ORACLE_HOME/rdbms/log
CPU time for the background processes is 0. It is 1 second for the svrmgrl
process. No change over the last 3 hours.
fuser shows the control files opened by ckpt and svrmgrl process, but the
data and log files are not opened.
The alert log shows the shutdown prior to the problem being a shutdown
immediate so no recovery or rollbacks should have been necessary.

Has anyone seen this before? 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

Re: Re[2]: Common Oracle RDBMS Misconceptions

2001-06-26 Thread Riyaj_Shamsudeen

==>The only part of the data file that is "un-writable" is the datafile header block, which gets frozen ==>until the hot backup of that tablespace completes.

Logically correct, but internally it does update the file header block. Here is how it works: The file header contains several structures which keeps track of hot backup activity and the structures relevant to hot backup are: 
1.        Checkpoint counter and status 
2.        Online checkpoint structure 
3.        Backup checkpoint structure 

While the tablespaces are NOT in backup mode, then both online and backup checkpoint structure are updated, when the tablespaces are in backup mode, only the backup checkpoint structure is updated and the online checkpoint structure is frozen. Checkpoint counter is updated regardless of whether the tablespaces are in backup mode or not. Also, the status of the file is updated to 0x01 (hot backup in progress). Using the above structures, status of the file from the file header and the checkpoint structures from the control files are used to drive the media/instance recovery.

File  header dumps before the hot backup command: Notice both online and backup checkpoint structures are kept current.

status:0x4 root dba:0x chkpt cnt: 984 ctl cnt:983
begin-hot-backup file size: 12928
Checkpointed at scn:  0x.10e83b12 05/30/2001 12:15:27
thread:1 rba:(0x2de.2.10)
Backup Checkpointed at scn:  0x.10e83b12 05/30/2001 12:15:27
thread:1 rba:(0x2de.2.10)

Checkpoint cnt:984 scn: 0x.10e83b12 05/30/2001 12:15:27

File header dumps while in hot backup mode. Notice "checkpointed SCN" structure is not updated whereas the "backup checkpointed" structure is updaed.

status:0x1 root dba:0x chkpt cnt: 988 ctl cnt:987
begin-hot-backup file size: 12928
Checkpointed at scn:  0x.10e83b14 05/30/2001 12:29:08
thread:1 rba:(0x2de.3.10)
Backup Checkpointed at scn:  0x.10e83b17 05/30/2001 12:37:20
thread:1 rba:(0x2e1.2.10)

Checkpoint cnt:988 scn: 0x.10e83b14 05/30/2001 12:29:08

Hope this helps!

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
06/26/01 08:46 AM
Please respond to ORACLE-L

        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re[2]: Common Oracle RDBMS Misconceptions

Joe & company,

    Jeremiah has it exactly correct.  The only part of the data file that is
"un-writable" is the datafile header block, which gets frozen until the hot
backup of that tablespace completes.  Look at it this way, when the "start
backup" command gets issued Oracle is in fact freezing that datafile(s) at that
point in time and assuming that all changes to the datafile(s) have not been
written.  When you restore the file, you then apply redo from your archive logs
(the reason you MUST be in archive mode) from the start of the backup till the
end of the recovery just as if those changes to the file(s) had never been made
in the first place.  In fact Oracle knows the changes were made during the
backup, it just has no idea if the change was written to the file before or
after your backup software copied that portion of the file to tape or wherever. 
Therefore, simple solution assume it was after.  In practice the process is very
simple, does slow the database down a tad, and works as advertised.  (Been
there, done that several times.)

BTW: IMHO, don't waste your money on any of the SAMS books.  They are full of
similar misconceptions.

Dick Goulet

Reply Separator
Author: "Joseph S. Testa" <[EMAIL PROTECTED]>
Date:       6/26/2001 4:00 AM

Well i dont know about everyone else, but i knew thats how the hot
backup worked, but then again, i've not attended oracle education
classes either, just some hard core reading and have gotten all of my
backup/recovery concepts from Rama Velpuri's book.  An excellent book if
you dont have it.

joe

> On Jun 26, 2001 at 01:05:59AM, novicedba wrote:
> > Hi everyone,
> > I visited Jeremiah Wilton's web page http://www.speakeasy.net/~jwilton
> > I was shocked to read Hot backup mode explained
> > If this is true then I may be a victim of a disease called
> > 'Common Oracle RDBMS Misconceptions' . Somebody help me!! (Jim carrey-MASK
style)
> > Please help me. If some one has few more articles like this enlighten me

-- 
Joe Testa  http://www.oracle-dba.com
Performing Remote DBA Services, need some backup DBA support?
For Sale: Oracle-dba.com domain, its not going cheap but feel free to
ask :)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joseph S. Testa
  INET: [EMAIL PROTECTED]

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

To REMOVE yourself from this mailing list, send an E-Mail mess

OCI : 32 bit -> 64 bit


Hi
        One of our developers are planning to migrate from 32 bit OCI to 64 bit OCI. Have any of you encountered any issues with that ? How much performance improvement can we expect ?
        
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

RE: Deadlock Detection

Hi
        I think, you may be hitting another parameter before you exceed distributed transactions limit. If you look at your original trace the enqueue is DX-003b-. Here 3b is hex representation of 59. Considering the initial allocation is 60, this may be the last element in the distributed transactions array. So, why would a process select a last element of the array and wait for that transaction to complete. I think, there are two other parameters open_links and open_links_per_instance need to be increased. Default value for these parameters are 4 and 4.  So, I think, if the instance does not have enough links, then it pseudo randomly selects a distributed transaction slot array element and waits for it. Again, this is my guess and it would be worth a try to increase these two parameters and see whether that fixes the problem or !
!
not.

Mr. Steve Adams,
        Can you please explain this to us ?
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

"Mercadante, Thomas F" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/22/01 02:48 PM
Please respond to ORACLE-L

        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Deadlock Detection

Anita & Riyaj,

Thanks for the replies.  My email server "went away" for awhile, so I am just getting your replies now.

Anita, I am able to modify INITRANS for the tables (I tried this on a test table), and I will try this.  Right now, we are performing other stress tests to narrow down the problem to be sure that it is update related (has to be, right?).

Riyaj, I checked the value of  max_utilization vs initial_Allocation in the v$resource_Limit view for distributed transactions.  Max_util=5, while init_alloc=61.  Is the value of 61 a default?  I do not have it set in my init.ora (maybe I should! - something else to try).  The value of Transactions is max=5, init=247 and limit=247.

It doesn't seem to be stressed according to these values, right?

As for distributed Trans, we are using VB calling ADO using COM+ connecting to Oracle.  My impression is that ADO and COM+ are performing the distributed trans, but that the ADO performs the commits?  

I'm not at all familiar with how ADO/COM+ works, but I guess I better bring a book home and figure it out.

Any other ideas, I would greatly appreciate it.

thanks
Tom Mercadante 
Oracle Certified Professional 
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Friday, June 22, 2001 2:17 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: Deadlock Detection

Hi Anita 
        If it is an ITL problem, then the resource type would be TX instead of DX. If the ITL table is full then the process requesting an ITL entry in that block will randomly select one of the transaction holding an ITL entry and wait for that process to complete or rollback. Since the process will wait for a transaction and as you are well aware of, the transaction id is a slot in a rollback segment and hence the enqueue type would be TX. Since this trace indicates that it is a DX type enqueue I would incline to think that this is a distributed transaction problem. Feel free to correct me if I am missing something.. 

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com 

"A. Bardeen" <[EMAIL PROTECTED]> 
Sent by: [EMAIL PROTECTED] 
06/22/01 10:50 AM 
Please respond to ORACLE-L 

        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 
        cc:         
        Subject:        Re: Deadlock Detection

Tom,

The information you need should be in the trace file,
but it's not in the excerpt you've listed, but the
true problem is listed:

> Rows waited on:
> Session 27: no row

"No row" indicates that the deadlock is due to a lack
of available ITL slots in the datablock.

Unfortunately resolving this requires recreating the
object with either a high initrans value (to
explicitly reserve space for more ITL slots) or a
higher pctfree value (to give the ITL table more room
to grow).

Depending on the application it could also be that
multiple sessions are acquiring the same block off the
free list so using multiple free lists could also
help.  Starting with 8.1.6 this setting can be changed
dynamically; prior to that you must recreate the
object.

Note: 62365.1 also has some good info

HTH,

-- Anita

--- "Mercadante, Thomas F" <[EMAIL PROTECTED]>
wrote:
> All,
> 
> My current application (still under development) is
> experiencing Oracle
> deadlock problems.  The applications people are
> performing stress testing
> where the application is being repeatedly called
> simulating actual users
> hitting the database.
> 
> The application is written using VB thru ADO and
> COM, Oracle 816 on NT.
> 
> My problem is that, while I can review the trace
> file produced, I can't
> figure out what the actual deadlock is occurring on.
>  I

Re: OPS in Sun E10K

Bala
I don't see any responses. So, please ignore if this is already answered.
First of all, In Sun, you have to use RAW for an Oracle Parallel Server database. (I am not sure whether you can use Veritas Quick I/O to configure OPS, my guess would be no). Second, if it is a performance critical database with high concurrent activity, even in a non-OPS set up, I would go for RAW or Quick IO.
There is always the discussion about the raw vs file system, but the administration overhead you will face is negligible compared to the performance boost you can achieve. I agree that you need to preconfigure the volumes and plan the size etc.., but in a production database that needs to be done anyway. Most of the backup software don't care whether it is a file system or raw either. On the other hand you avoid double copying, double buffering, vnode lock etc. Specifically, if you use multiple DBWRs also to simulate async IO, vnode lock will serialize your writes degrading the scalability. You can also achieve true asynchronous IO also if you raw disk based database.
Couple of years ago, at my previous employer, we converted few databases to raw and our performance was much better. Particularly, considering that we had I/O intensive OLTP application, we were able to scale beyond the requirements easily.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

Balakrishnan Subramanian <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/22/01 10:10 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: OPS in Sun E10K

System : Sun E10K
OS : Sun solaris 2.8
Oracle : 8i Rel 3 64bit option
Database : OPS
Type : OLTP &
# concurrent users : 1000 (including US and international users Europe, Asia
& Australia)

Our System admin suggesting us to go for File system (veritas, without
Veritas quick i/o) instead of raw devices, the reason is ease of
maintenance. He says, with OS and Oracle tuning, we can bring OPS upto the
level of raw device performance.

I appreciate if you can share your experience.

Thanks,

Bala.

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

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

Re: Rule base optimizer selecting different execution path ( Tuning )


Brijesh
        There are few conditions where the optimizer will select CBO even when the optimizer_mode is set to rule. Do you have any objects in the schema with a parallelism >1 (tables and indexes) ? One of the common problem is that DBAs rebuild the index with higher parallelism and forget to change the parallelism back to noparallel. Parallelism >1 will turn on CBO. What is your optimizer_mode ? I assume it is rule. Let us know if not.
        Seeing hash_join in your second plan, I am positive that the CBO is being turned on. Do you have any hints in the code ? Does your session change the optimizer_goal at the session level by any chance ?

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Gupta, Brijesh" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/22/01 01:12 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Rule base optimizer selecting different execution path ( Tuning )


Hi All, 
        Can somebody help me in understanding this. 
We have a query which runs faster on development and not on production. 
When I checked the trace , it using different execution path on both database. 
Database is 8.0.5 running oracle application and the optimizer is RULE. 
Does anybody know why two execution path oracle optimizer has selected when its a RULE base optimizer. 
I know that is possible if its cost base optimizer but in rule it should be same right ( All the indexes are same on both database )
Only thing I did was rebuild some of the indexes on Test instance. 
Thanks 
Brijesh 
Here is the explain plan of both the database. 


* Development Database *** 
Optimizer goal: RULE 
Parsing user id: 45  (APPS) 
 
select 
substr(mil.segment1||'.'||mil.segment2||'.'||mil.segment3,1,45) Locator, 
mmt.subinventory_code Subinventory, 
msi.segment1 PART_NO, 
mmt.transaction_uom UOM, 
sum(mmt.transaction_quantity) 
from 
mtl_item_locations mil, 
mtl_transaction_types typ, 
mtl_material_transactions mmt, 
mtl_system_items msi, 
mtl_parameters mp 
where 
mp.organization_code='768' 
and mp.organization_id+0=msi.organization_id 
and msi.organization_id=mmt.organization_id 
and msi.inventory_item_id=mmt.inventory_item_id 
and trunc(mmt.transaction_date) between '01-APR-01' and '31-MAY-01' 
and mmt.transaction_type_id=typ.transaction_type_id 
and typ.transaction_type_name='Account alias receipt' 
and mmt.locator_id=mil.inventory_location_id(+) 
and mmt.organization_id=mil.organization_id(+) 
and mil.subinventory_code = 'CORROSIVE' 
group by mil.segment1, 
mil.segment2, 
mil.segment3, 
mmt.subinventory_code, 
msi.segment1, 
mmt.transaction_uom 
call     count       cpu    elapsed       disk      query    current        rows 
--- --   -- -- -- --  -- 
Parse        1      0.01       0.01          0          0          0           0 
Execute      1      0.00       0.00          0          0          0           0 
Fetch        2     51.68     901.33      85134     395083          0           4 
--- --   -- -- -- --  -- 
total        4     51.69     901.34      85134     395083          0           4 
Misses in library cache during parse: 0 
Optimizer goal: RULE 
Parsing user id: 45  (APPS) 
Rows     Execution Plan 
---  --- 
      0  SELECT STATEMENT   GOAL: RULE 
      4   SORT (GROUP BY) 
     33    FILTER 
     32     NESTED LOOPS (OUTER) 
     33      NESTED LOOPS 
    132       NESTED LOOPS 
   2445        NESTED LOOPS 
      1         TABLE ACCESS (BY INDEX ROWID) OF 'MTL_PARAMETERS' 
      2          INDEX (RANGE SCAN) OF 'ORGANIZATION_CODE' 
                     (NON-UNIQUE) 
   2445         TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                    'MTL_SYSTEM_ITEMS' 
   2446          INDEX   GOAL: ANALYZED (RANGE SCAN) OF 
                     'MTL_SYSTEM_ITEMS_N1' (NON-UNIQUE) 
 194874        TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                   'MTL_MATERIAL_TRANSACTIONS' 
 846350         AND-EQUAL 
 500017          INDEX (RANGE SCAN) OF 'ORGANIZATION_ID' 
                     (NON-UNIQUE) 
 348778          INDEX (RANGE SCAN) OF 'INVENTORY_ITEM_ID' 
                     (NON-UNIQUE) 
    132       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF 
                  'MTL_TRANSACTION_TYPES' 
    132        INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                   'MTL_TRANSACTION_TYPES_U1' (UNIQUE) 
     32      TABLE ACCESS (BY INDEX ROWID) OF 'MTL_ITEM_LOCATIONS' 
     32       INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 
                  'MTL_ITEM_LOCATIONS_U1' (UNIQUE)

Re: Deadlock Detection

Hi Anita
        If it is an ITL problem, then the resource type would be TX instead of DX. If the ITL table is full then the process requesting an ITL entry in that block will randomly select one of the transaction holding an ITL entry and wait for that process to complete or rollback. Since the process will wait for a transaction and as you are well aware of, the transaction id is a slot in a rollback segment and hence the enqueue type would be TX. Since this trace indicates that it is a DX type enqueue I would incline to think that this is a distributed transaction problem. Feel free to correct me if I am missing something..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

"A. Bardeen" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/22/01 10:50 AM
Please respond to ORACLE-L

        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: Deadlock Detection

Tom,

The information you need should be in the trace file,
but it's not in the excerpt you've listed, but the
true problem is listed:

> Rows waited on:
> Session 27: no row

"No row" indicates that the deadlock is due to a lack
of available ITL slots in the datablock.

Unfortunately resolving this requires recreating the
object with either a high initrans value (to
explicitly reserve space for more ITL slots) or a
higher pctfree value (to give the ITL table more room
to grow).

Depending on the application it could also be that
multiple sessions are acquiring the same block off the
free list so using multiple free lists could also
help.  Starting with 8.1.6 this setting can be changed
dynamically; prior to that you must recreate the
object.

Note: 62365.1 also has some good info

HTH,

-- Anita

--- "Mercadante, Thomas F" <[EMAIL PROTECTED]>
wrote:
> All,
> 
> My current application (still under development) is
> experiencing Oracle
> deadlock problems.  The applications people are
> performing stress testing
> where the application is being repeatedly called
> simulating actual users
> hitting the database.
> 
> The application is written using VB thru ADO and
> COM, Oracle 816 on NT.
> 
> My problem is that, while I can review the trace
> file produced, I can't
> figure out what the actual deadlock is occurring on.
>  I have seen deadlock
> trace files that clearly state "table blah", but in
> this case, I get:
> 
> 
> *** 2001-06-21 14:32:03.841
> *** SESSION ID:(27.31211) 2001-06-21 14:32:03.810
> DEADLOCK DETECTED
> Deadlock graph:
>                        -Blocker(s)
> -Waiter(s)-
> Resource Name          process session holds waits 
> process session holds
> waits
> DX-003b-        22      18     X        
>     24      27
> X
> session 18: DID 0001-0018-003C                 session 27: DID
> 0001-0018-003C
> Rows waited on:
> Session 27: no row
> *** 2001-06-21 14:32:03.857
> ksedmp: internal or fatal error
> ORA-00060: deadlock detected while waiting for
> resource
> 
> Is there something like TKPROF that will process the
> trace file and give me
> more info on what is happening?  It looks like I am
> waiting for a resource
> to be freed, but which one is the question.
> 
> thanks for any help.
> 
> Tom Mercadante
> Oracle Certified Professional
> 
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Mercadante, Thomas F
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services    -- (858) 538-5051  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 personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: A. Bardeen
  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: Deadlock Detection


Hi 
        Your deadlock problem seems to be related to distributed transactions since your processes are holding and waiting for DX type locks. DX enqueues are taken for distributed queries. Do you have queries accessing the remote tables over the database links ?
        As far as I know, even queries take DX lock for distributed transaction. So you need to commit or rollback after every distributed query/transaction to release the DX lock. Also look at the v$resource_limit to find the high water mark for the distributed transaction slot usage. If the max_utilitization equals to initial_allocation, then you may need to increase the number of transaction slots also in addition to adding commit/rollback.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Mercadante, Thomas F" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/22/01 08:35 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Deadlock Detection


All,

My current application (still under development) is experiencing Oracle
deadlock problems.  The applications people are performing stress testing
where the application is being repeatedly called simulating actual users
hitting the database.

The application is written using VB thru ADO and COM, Oracle 816 on NT.

My problem is that, while I can review the trace file produced, I can't
figure out what the actual deadlock is occurring on.  I have seen deadlock
trace files that clearly state "table blah", but in this case, I get:


*** 2001-06-21 14:32:03.841
*** SESSION ID:(27.31211) 2001-06-21 14:32:03.810
DEADLOCK DETECTED
Deadlock graph:
                       -Blocker(s)
-Waiter(s)-
Resource Name          process session holds waits  process session holds
waits
DX-003b-        22      18     X             24      27
X
session 18: DID 0001-0018-003C                 session 27: DID 0001-0018-003C
Rows waited on:
Session 27: no row
*** 2001-06-21 14:32:03.857
ksedmp: internal or fatal error
ORA-00060: deadlock detected while waiting for resource

Is there something like TKPROF that will process the trace file and give me
more info on what is happening?  It looks like I am waiting for a resource
to be freed, but which one is the question.

thanks for any help.

Tom Mercadante
Oracle Certified Professional

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

Fat City Network Services    -- (858) 538-5051  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: Waits on cache buffers chains latch

2001-06-21 Thread Riyaj_Shamsudeen


Hi Bruce
        You need to find what blocks are hanging from the particular latch. You could get this information by joining x$bh and v$session_wait tables while the sessions are waiting for this latch. 
select distinct ts#, file#, dbarfil,dbablk from x$bh
where hladdr in
  (select p1raw from v$session_wait where to_char(p2)=66
  and lower(event) like 'latch free%'
)
/
(or) You could use the other method since you know the child latch#.

select distinct ts#, file#, dbarfil,dbablk from x$bh
where hladdr in
 (select addr from v$latch_children where latch#=66 and child#=242)

        From thie file#, dbarilf and dbablk you could find the segment that the latch is protecting. Please note here 66 is the latch# for "cache buffers chains" latch and check that is correct from v$latch in your environment.

        I would check the SQL statements also and make sure that they are not doing any full table scans. If you seen any waits for 'Db file scattered read' events in the v$session_wait then that could cause secondary cache buffers chains latch contention.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Reardon, Bruce (CALBBAY)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/21/01 01:15 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Waits on cache buffers chains latch


Hi,

Our database is experiencing a very large number of waits on the cache
buffers chains latch.
I know the child latch# is 242 (details below).

We are on Oracle 8.1.5.1.1 on NT4.

The problems seem to have started appearing after starting large scale use
of Global Temporary tables (GTT).
Our temp tablespace was of type temporary but a suggestion from Oracle was
to change this to Permanent (due to GTT related bugs in 815).

This was done and the database was restarted but the waits are still
occurring.

What else should I try to look for?

Thanks,
Bruce Reardon
mailto:[EMAIL PROTECTED]



Our top waits in general are:

SQL> @system_times

EVENT                                                            TIME_WAITED
 ---
PX Idle Wait                                                       581928737
PX Deq: Execution Msg                                              278990599
CPU used by this session                                             3812597
latch free                                                            202949
db file sequential read                                               200926
SQL*Net more data to client                                            73342
db file scattered read                                                 59797
enqueue                                                                19755

Using Ixora's latch_sleeps script I get the following output:

SQL> @latch_sleeps
LATCH TYPE                                 IMPACT SLEEP RATE WAITS HOLDING
LEVEL
- --- -- -
-
cache buffers chains                     12642171      0.39%           804
1
library cache                                7122      0.00%         48564
5
Checkpoint queue latch                       7034      0.03%         24370
7
session allocation                            346      0.04%           762
5
parallel query stats                          198      4.31%             0
8
messages                                      113      0.01%          1499
8
shared pool                                    88      0.00%           320
7
cache buffers lru chain                        41      0.00%         20765
3
process queue reference                        14      0.00%           276
4
query server freelists                          2      0.01%            10
6
multiblock read objects                         1      0.00%             6
3
redo writing                                    1      0.00%           165
5
parallel query alloc buffer                     1      0.00%           206
6

13 rows selected.

and from latch_gets.sql

LATCH TYPE                            SIMPLE GETS            SPIN GETS
SLEEP GETS
-- -- 

cache buffers chains           3149562975  97.08%   82063196  2.53%
12562449  0.39%



Virtually all of these waits are on the 1 child latch:

LATCH TYPE                                       SLEEPS
LATCHES
 
--
cache buffers chains                             0
951
                                                 1 to 2
61
                                                 3 to 5
5
                                                 10 to 13
3
                                                 131
1
                                                 652
1

RE: Oracle 8i installation problem

2001-06-17 Thread Riyaj_Shamsudeen


Pankaj
        Always try to keep the shmmax value around 80% to 90% of the available physical memory. So your setting for shmmax would be
set shmsys:shminfo_shmmax=209715200
        You said it failed ? What failed ? Software installation, Database creation ? If your database creation failed, then look under $ORACLE_HOME/rdbms/log directories.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Rajesh Dayal <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/17/01 02:25 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Oracle 8i installation problem


You have to increase this value.
The default value would be too small. I'm not
aware of default on solaris, but on TRU64 it's
somewhere around 45 MB ( don't remember exactly)...
So just increase this value to 1-2 GB and try 
once more, this should succeed.

Rajesh

-Original Message-
Kumar
Sent: Sunday, June 17, 2001 10:55 AM
To: Multiple recipients of list ORACLE-L


Hi Rajesh,

Thanks for the info. I was not aware what shmmax value
was, though, I knew it is related to memory.

I had even left the /etc/system file unchanged during 
my 2nd installation attempt (I had copied system to
system.org) and tried. But it fails there too.

Thanks,
gopi


--- Rajesh Dayal <[EMAIL PROTECTED]> wrote:
> Hi Pankaj,
>                  With 256 MB RAM, you may not be able to set  
> 
> set shmsys:shminfo_shmmax=4294967295 
> 
>                  Try setting it to lower values 1-2 GB, It should
> resolve the issue. Of course it shdn't have any
> impact on
> Oracle's functioning ( If you have just 256 MB, you
> can't
> allocate 4 GB to one single Mem Segment right??). 
> 
> HTH,
> Rajesh
> -Original Message-
> Kumar
> Sent: Sunday, June 17, 2001 8:40 AM
> To: Multiple recipients of list ORACLE-L
> 
> 
> Hi All, 
> 
> Before I explain what I did, here is the system
> configuration details : 
> 
> Hardware : 
> CPU : Intel Pentium III 500 Mhz 
> RAM : 256MB 
> HDD : 15GB 
> 
> Software : 
> OS : Solaris 8 for Intel 
> Oracle : Oracle 8.1.7 Rel 3 
> 
> I first changed the /etc/system file by adding these
> entries as it is given in the install.htm file. But
> it
> 
> failed. 
> 
> set shmsys:shminfo_shmmax=4294967295 
> set shmsys:shminfo_shmmin=1 
> set shmsys:shminfo_shmmni=100 
> set shmsys:shminfo_sshmseg=10 
> set semsys:seminfo_semmni=100 
> set semsys:seminfo_semmsl=100 
> set semsys:seminfo_semmns=200 
> set semsys:seminfo_semopm=100 
> set semsys:seminfo_semvmx=100 
> 
> I tried it for the second time by using the original
> system generated file (the default) and it fails
> even
> then. I suppose it is due to the shm parameters in 
> the /etc/system file. 
> 
> Does anyone know what combination of shm parameters
> to
> 
> use with my HW configuration ? 
> 
> Is it possible to copy these files onto a floppy
> with
> DOS format from Solaris. This way, I Can email you
> the
> 
> files atleast. 
> 
> I know that my Solaris skills are pretty bad. I'm
> waiting for the Solaris 8 installation books to be
> released so that I can get myself certified. 
> 
> Any idea which are the trace & log files that I need
> to look into. 
> 
> Thanks, 
> 
> __
> Do You Yahoo!?
> Spot the hottest trends in music, movies, and more.
> http://buzz.yahoo.com/
> -- 
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> -- 
> Author: Pankaj Kumar
>   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: Rajesh Dayal
>   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!?
Spot the hottest trends in music, movies, and more.
http://buzz.yahoo.com/
-- 
Please see t

Re: the conversion is from Mainframe flat file to Unix Flat file - b

2001-06-15 Thread Riyaj_Shamsudeen


Raymond
        EBCDIC is the format few mainframe computer uses. Unix systems typically uses ASCII. I am not sure about the type of the file, but you may have to convert the file from EBCDIC to ASCII to make any meaning ful use of the file. You could use dd command for this conversion..
dd if=input file of=outfile conv=ascii bs=128k

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Raymond Lee Meng Hong <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/15/01 09:40 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        the conversion is from Mainframe flat file to Unix Flat file - b


what is EBCDIC format

I have a customer from IBM DB2 mainframe going to pass data to my oracle
system for matching of record ,what they told me is the tape is in EBCDIC
format ? I don't have any experience on these format ? can anyone please
explain /guildline on these ?


Raymond fall asleep in waiting for the Q.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Raymond Lee Meng Hong
  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: SMP/MPP and PQO

2001-06-12 Thread Riyaj_Shamsudeen

Hi Lisa

SMP - In SMP many CPUs share the same memory. Typical example would be E450, 4 processors, 4GB memory. Only L2 cache in the CPUs need to be co-ordinated when a memory buffer is changed or intended to change.
MPP - In MPP each CPU has its own memory and its own disks that they control (typically). An example would be nCUBE or IBM RS6000, where each processor runs its own OS and messaging is used heavily to coordinate the access to the resources.
NUMA - Nun -uniform memory access - Group of CPUs share the same memory and the messaging is used between the group of the CPUs to coordinate the access to the resource. An example would be Sequent NUMA-Q, where there are multiple quads and each quad has 4 cpus and 2GB memory (typically) and IQ-link coordinates the access to the resources between the quads. HP has ccNUMA for this.

Now, I don't think, E10k domain would qualify for a MPP, since there is no coordination or access restrictions between the domains ( at least that I know of). Each domain can manipulate its own disk / memory /devices without any messaging between the domains, in a non-clustered environment.
PQ architecture splits the FTS or long running operations on non-partitioned tables, in to multiple chunks based upon the rowid ranges. It is not a simple split though. First, work is divided in to degree of parallelism. For example, if the QC process determines that it has to scan 100,000 blocks and parallelism is determined as 4 then 25000 would be the split. But not all 25000 block is handed over to the slave processes. QC uses 9/13 rule. QC hands over 9/13 of the work (i.e. 25000 * 9/13=17307) blocks equally to the slave processes. When the slave processes finishes up the 9/13 work, then they get 3/13 of the work (25000 *3/13=5769) blocks and 1/13 of the work when the second set of 3/13s are exhausted. This is done to evenly distribute the load. For example, there could data skew such that one process could finish up its 9/13 work much faster than other processes and hence those processes can take up the remain!
!
ing work.

The suitability of PQO parallelism should be defined by IO sub system in conjunction with CPU power. If you have many spindles in 2 cpu server my take on it would be to use parallelism of 2 to4 depending upon the cpu clock speed, disk speed, memory etc..
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"Koivu, Lisa" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/12/01 09:17 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: SMP/MPP and PQO

Hello everyone,
I was reading up on the differences between SMP, MPP and how they may affect PQO (Parallel Query).
My understanding is that MPP is a host with defined domains (like an e10k with virtual machines on it). SMP is a standalone host with no domains and multiple processors. I am not considering clustering here.
It seemed to me the only requirement that you really need to run PQO is to have available resources to power it. For example, a little 2-cpu box that is pinned a majority of the time is only going to suffer if PQO is turned on. However, if we had a 16-cpu box with abundant resources, turning on PQO would help fts and large index scans in a dw-type environment. (At least this is what I saw in the past).
Also, I was taught that PQO should not be used when a table/index is not partitioned. However, upon reading the doco, it states that the slaves split up work by blocks (or was it extents?). Seems to me this could cause more problems than it's worth (i/o contention?) and partitioning, if done carefully, would be the smarter way to go. Would the slaves really be smart enough to divy up work intelligently on a non-partitioned object? My initial thought is NO.
In addition, on metalink they even went so far as to state it is OK to use PQO on a 2-processor NT machine. Seems to me the statement that 'PQO provides no benefit on a SMP machine' is not warranted, unless Oracle Support was just pacifying the customer who wanted to see PQO work.
Maybe my idea of SMP is too simple. If I am off my rocker can someone please set me straight?
Thanks
Lisa Koivu
Oracle Database Administrator
954-935-4117
The information in the electronic mail message is Cendant confidential and may be legally privileged, it is intended solely for the addressee(s) access to this internet electronic mail message by anyone else is unauthorized. If you are not the intended recipient, any disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it is prohibited and may be unlawful.
The sender believes that this E-mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmi

RE: Surrogate keys

2001-06-12 Thread Riyaj_Shamsudeen


One more cons : If the surrogate keys are populated from a single sequence, then the primary key index of that table becomes right hand index. Meaning all the new values will go in to the right most leaf block of the index due to the nature of the monotonically increasing or decreasing values. If you have numerous concurrent transactions inserting in to the table, then that leaf block becomes a bottleneck (hot block) since all the transactions has to manipulate that leaf block. In high concurrency environments / or in OPS environments, this problem is very cpu intensive. Free lists and free list groups will not help in this case, since all the changes will be done to the single block even if you have multiple free lists/ groups.

        Reverse key indexes are supposed to solve this problem, even though it is not an effective solution. Using some logic to use multiple sequences, one can avoid this problem too.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Christopher Spence <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/12/01 08:26 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Surrogate keys


Cons
Primary Key has no bearing on data
Primary involves a sequence


Pros
Primary key will never risk change
Primary key will never risk being null
Multiple columns will not migrate to other tables on relationships
Very simple to implement
More efficient index use (as you don't have to use Primary key in order of
index build)
Index size is smaller than concatenated primary keys


I generally use Surrogate keys more often than most dbas, I find them great.
But when there is that "perfect primary key" I will actually opt for a more
intelligent primary key.  But when there is any doubt, what so ever how ever
slim, I generally lean to surrogate keys.

Really good article on Surrogate keys:
http://www.dbpd.com/vault/9805xtra.htm


"Walking on water and developing software from a specification are easy if
both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot 



-Original Message-
Sent: Tuesday, June 12, 2001 9:00 AM
To: Multiple recipients of list ORACLE-L


Hi All,

Can someone explain pros/cons to using surrogate keys(i.e.,sequences) vs
non-surrogate keys?

Thanks
Rick
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Cale, Rick T (Richard)
  INET: [EMAIL PROTECTED]

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

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

Ian
Quick IO does bypass the unix buffer cache completely, thereby avoiding few problems such as double buffering, double copying, vnode locks associated with the ufs,xfs or veritas non-quick I/O files. Oracle can use Quick I/O capability and asynchrous I/O on these Quick I/O files are also supported.
As per the veritas benchmarks Quick I/O is very close in performance with raw volumes. Raw volumes are marginally better than quick I/O as per veritas.
IMHO, I would vote for raw, even though Quick I/O is a viable option too.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"MacGregor, Ian A." <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/08/01 02:30 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Veritas Quick I/0 and Oracle/ Asycnchronous I/O

Will Oracle use the Quick I/O capability of Veritas on database writes; that is, will it bypass any file system buffer cache and write directly to disk? Is the implementation of asynchronous I/O imnproved in Solaris 8; does one have to use raw disks or does it now work properly with UFS or Veritas?

I need to configure a machine to provide for the maximum number of transactions per second. Our Accelerator Controls folks are at it again, testing how much data they can push into Oracle. They have backed off the plan of having 6000 Beam Position Monitors sampling at 120 Hz write into the database; although., 720,000 transactions per seconds might be fun to try. But they do want to see what they can do. Obviously, the programs which collect the data from BPM's and other sensors needs to do some buffering. But when they dump to the database I need the writes to happen as quickly as possible.

The current method of handloing this via ring buffers and doubly-linked lists. They want to look at replacing the lists with an Oracle database. Our initial tests will be done using a 4 processor ES-450.

Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: MacGregor, Ian A.
INET: [EMAIL PROTECTED]

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

Re: recovery puzzler


That is interesting. It should start with sequence 66 logically.  Is it possible that some or few of the tablespaces were left in the hot backup mode on sunday/monday morning ? Please check the alert log. My guess is that, one of the tablespace was in the backup mode, say from Sunday at least, and 66 was the log sequence when the tablespace went in to the backup mode. Then, your begin backup command on Monday evening would have failed and hence the recovery is asking for 66.

BTW, Did you use the existing control file or did you use backup/new control file ?

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Adams, Matthew (GEA, 088130)" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/08/01 03:05 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        recovery puzzler


This is puzzling me. 
On monday morning, the log file switched from sequence 66 to 67. 
On monday night, a hot backup was taken. 
On Friday, this backup was written to an alternate machine 
an attempt was made to do a point-in-time-recovery to Friday 
Morning. 
Why did it ask for archive file 66?  I should have thought it 
would start with file 67. 
 
R. Matt Adams  - GE Appliances - [EMAIL PROTECTED] 
  Meddle not in the affairs of troff, 
  for it is subtle and quick to anger.

Re: parallel query performance


Hi Elain
        I had similar questions some time ago and went through a small experiment/analysis to understand.
        When you start a parallel query process, following happens:
                1. Current SCN is captured for the consistent or snapshot SCN.
                2. Objects accessed by the query is flushed to the disk.
        Now, there are multiple scenarios / issues with this approach:

PQ architecture: Parallel Query

        1. Say, Current SCN/consistent  is 150 and there is a session modified the block ( with SCN 140) that PQ slave interested but not committed.
        In this case, object level checkpoint will flush the modified block and hence the direct reads will read the updated block and go back to rollback segment to get the consistent copy of the block. So, it seems to me that PQ slaves may have to go back to buffer cache in few cases. Can anybody confirm this ?
        2. Say, Current SCN/consistent  is 150 and there is a session modified the block ( with SCN 140) that PQ slave interested but committed
        In this case, object level checkpoint will flush the modified block and hence the direct reads will read the correct block version. 

        After the object level checkpoint,  the PQ slave started reading the blocks. Before PQ slave can read a specific block, another session came in and read a block in to the buffer cache and modified the block. Since PQ slave does not need to see the modified block, this does not matter.
        So, I don't see any consistency issues with this.

PX architecture: Parallel Execution.

        In case of PDML, there are two variations:
        
        1. For partitioned tables: After the object checkpoint, the Query coordinator takes exclusive locks on the partitions. so, there is no issues here, like PQ.
        2. For non-partitioned tables: PQ slaves allocates temporary segments above the high water mark and hence there is no issues here either.

        Now, the question is what if I have a normal DML within the same transaction as my PDML ? Since regular DML will read the block in to the buffer cache and update and since my transaction holds the locks on the table, how does this is coordinated ? Well, you can't have any other DML in the same transaction as PDML:-) 
        Also, the PX message buffers are allocated in shared pool if the parallel_automatic_tuning is set to false and allocated in large pool if the parallel_automatic_tuning parameter is true. 

        Listers, Feel free to correct me/educate me if I have missed something..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"elain he" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/08/01 11:17 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: parallel query performance


Riyaj,
Thanks for your reply. I have a follow up question. What happen when a  
transaction is commited and the block has not been flushed out to disk yet? 
If I have parallel query processes that need to read the commited blocks in 
the buffer cache, then what happens. Since PQ processes do not read from 
buffer cache, how do the processes access the updated blocks? The disk does 
not have the updated information yet.

Thanks.

elain


>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re: parallel query performance
>Date: Fri, 08 Jun 2001 06:40:27 -0800
>
>Hi Elain
>         Yes, that is true. SGA is useful if you want to cache the block,
>so that many sessions can avoid the disk reads. But, in case of Parallel
>Query architecture, mostly, only the parallel query slave is interested in
>the block. That's why slave process directly reads the blocks in to the
>PGA bypassing the Buffer cache. Of course, object level checkpoint occurs
>and there are some optimization in 8i versions.  That is the reason, there
>is no point in increasing the buffer cache, if most of the work is done
>through parallel query slaves.
>
>But, the communication buffers between the slave processes are stored in
>the shared pool though.
>
>Thanks
>Riyaj "Re-yas" Shamsudeen
>Certified Oracle DBA
>i2 technologies   www.i2.com
>
>
>
>
>"elain he" <[EMAIL PROTECTED]>
>Sent by: [EMAIL PROTECTED]
>06/08/01 07:40 AM
>Please respond to ORACLE-L
>
>
>         To:     Multiple recipients of list ORACLE-L 
><[EMAIL PROTECTED]>
>         cc:
>         Subject:        parallel query performance
>
>
>Hi,
>Is it true that when running parallel query, the parallel query slaves do
>not read from buffer cache even though the data resides in the buffer
>cache
>ie the slaves read directly from disk.
>
>Does not make sense to me. Can someone clarify that?
>
>Thanks.
>
>elain
>_
>Get your FREE download of MSN Explorer at http://explorer.msn.com
>
>--
>Please se

Re: parallel query performance

Hi Elain
Yes, that is true. SGA is useful if you want to cache the block, so that many sessions can avoid the disk reads. But, in case of Parallel Query architecture, mostly, only the parallel query slave is interested in the block. That's why slave process directly reads the blocks in to the PGA bypassing the Buffer cache. Of course, object level checkpoint occurs and there are some optimization in 8i versions. That is the reason, there is no point in increasing the buffer cache, if most of the work is done through parallel query slaves.

But, the communication buffers between the slave processes are stored in the shared pool though.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"elain he" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/08/01 07:40 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: parallel query performance

Hi,
Is it true that when running parallel query, the parallel query slaves do
not read from buffer cache even though the data resides in the buffer cache
ie the slaves read directly from disk.

Does not make sense to me. Can someone clarify that?

Thanks.

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

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

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

RE: HELP: Hanging Autotrace

2001-06-07 Thread Riyaj_Shamsudeen


Nope. Not at all. In fact, analyze_database analyzes data dictionary. I don't see why it would hurt performance to analyze the sys schama.

Here is the catch though:- do not analyze the sys tables using 'analyze command' since analyzing the few internal tables can cause deadlocks. But the analyze_schema ('SYS') is fine since it avoid analyzing those internal tables correctly and using the analyze_database is not good, since it does NOT  avoid those internal tables due to a bug..Now, my head hurts...:-)

All right, are you saying that "exact" explain plan statement for the local and remote users work differently ? If that is the case,it can not be Data dictionary problem. Then you may want to turn on 10046 and see the bottlleneck..BTW, if you don't like the statistics in the DD, you can always go back and 'analyze_schema('SYS','DELETE')'..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Koivu, Lisa" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/07/01 04:33 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: HELP: Hanging Autotrace


Are you kidding?  I thought that Oracle adamantly stated that you are NOT to analyze the data dictionary...? 
Plus the user who is local can do it in a snap.  I can't. 
I can explain plan on a very small query, yes.  
List, am I off track with thinking NOT to analyze the data dictionary?? 
-Original Message- 
t size=1 face="Arial">[EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] 
Sent:   Thursday, June 07, 2001 5:14 PM 
To:     Multiple recipients of list ORACLE-L 
oman"> RE: HELP: Hanging Autotrace 

Lisa
        Simple test: Can you do explain plan on some small query ? If it takes long time, then you know that your data dictionary uses CBO and access to DD is being slow. Try analyzing the data dictionary using 'dbms_utility.analyze_schema('SYS','COMPUTE') and in my case it fixed the problem...

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com



        "Koivu, Lisa" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED] 
06/07/01 02:16 PM
Please respond to ORACLE-L
       
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: HELP: Hanging Autotrace 


Hi Jared, yes, good point. 
And the answer is the latter, they don't care.  I'm the only one trying to help the users and I'm getting the same treatment as them (I don't expect queen treatment.  Just for them to recognize that the USERS NEED ATTENTION and I can help).  Oh well, this place is so close to home I can entertain myself with other things until I get to create a database in the next few months.  I have never spent so much time reading doco :) 
Thanks for you reply.
Lisa 
-Original Message-
Sent:   Thursday, June 07, 2001 1:40 PM
To:     [EMAIL PROTECTED]; Koivu, Lisa



Lisa, 
Have you tried approaching this politically rather
than technically? 
Let the manager of the local users know what you are
trying to do, and why you can't do it. 
Then maybe some pressure can be applied to get you the
privs you need.  You don't even need DBA privs, just
SELECT ANY TABLE. 
If the local folks don't care, or don't want to pursue
it, then find something more interesting to do.  :) 
Jared 
On Thursday 07 June 2001 06:12, Koivu, Lisa wrote:
> Good morning everyone,
>
> I'm trying to help out our user community here by tuning up some queries.
> Should be easy, right?  Well I don't have dba privs and I have a crappy old
> W98 PC w/200-some processor.  when I issue SET AUTOTRACE TRACEONLY EXPLAIN
> and start a query, it takes a lng time to come back.  It took me 30
> minutes to get a trace back.  The dba (in Arkansas) gets his autotraces to
> come back in three seconds, of course he is where the host is.  They are
> not willing to give me a unix account.  Gee, I wouldn't trust me either.
> I'm such an OS hacker (yea, right) and a huge threat (puh-lease) and for
> petes sake I am trying to help the users - something him and his team
> absolutely do not do.
>
> Has anyone seen this before?
>
> > Lisa Koivu
> > Oracle Database Administrator
> > 954-935-4117
> > * Allen Iverson Rules! *
> >
> > The information in the electronic mail message is Cendant confidential
> > and may be legally privileged, it is intended solely for the addressee(s)
> > access to this internet electronic mail message by anyone else is
> > unauthorized. If you are not the intended recipient, any disclosure,
> > copying, distribution or any action taken or omitted to be taken in
> > reliance on it is prohibited and may be unlawful.
> >
> > The sender believes that this E-mail and any attachments were free of any
> > virus, worm, Trojan horse, and/or malicious code when sent. This message
> > and its attachments could have been infected during transmission. By
> > reading the message and opening any atta

RE: HELP: Hanging Autotrace

2001-06-07 Thread Riyaj_Shamsudeen


Lisa
        Simple test: Can you do explain plan on some small query ? If it takes long time, then you know that your data dictionary uses CBO and access to DD is being slow. Try analyzing the data dictionary using 'dbms_utility.analyze_schema('SYS','COMPUTE') and in my case it fixed the problem...

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Koivu, Lisa" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/07/01 02:16 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: HELP: Hanging Autotrace


Hi Jared, yes, good point. 
And the answer is the latter, they don't care.  I'm the only one trying to help the users and I'm getting the same treatment as them (I don't expect queen treatment.  Just for them to recognize that the USERS NEED ATTENTION and I can help).  Oh well, this place is so close to home I can entertain myself with other things until I get to create a database in the next few months.  I have never spent so much time reading doco :)
Thanks for you reply. 
Lisa 
-Original Message- 
Sent:   Thursday, June 07, 2001 1:40 PM 
To:     [EMAIL PROTECTED]; Koivu, Lisa 


Lisa, 
Have you tried approaching this politically rather 
than technically? 
Let the manager of the local users know what you are 
trying to do, and why you can't do it. 
Then maybe some pressure can be applied to get you the 
privs you need.  You don't even need DBA privs, just 
SELECT ANY TABLE. 
If the local folks don't care, or don't want to pursue 
it, then find something more interesting to do.  :) 
Jared 

On Thursday 07 June 2001 06:12, Koivu, Lisa wrote: 
> Good morning everyone, 
> 
> I'm trying to help out our user community here by tuning up some queries. 
> Should be easy, right?  Well I don't have dba privs and I have a crappy old 
> W98 PC w/200-some processor.  when I issue SET AUTOTRACE TRACEONLY EXPLAIN 
> and start a query, it takes a lng time to come back.  It took me 30 
> minutes to get a trace back.  The dba (in Arkansas) gets his autotraces to 
> come back in three seconds, of course he is where the host is.  They are 
> not willing to give me a unix account.  Gee, I wouldn't trust me either. 
> I'm such an OS hacker (yea, right) and a huge threat (puh-lease) and for 
> petes sake I am trying to help the users - something him and his team 
> absolutely do not do. 
> 
> Has anyone seen this before? 
> 
> > Lisa Koivu 
> > Oracle Database Administrator 
> > 954-935-4117 
> > * Allen Iverson Rules! * 
> > 
> > The information in the electronic mail message is Cendant confidential 
> > and may be legally privileged, it is intended solely for the addressee(s) 
> > access to this internet electronic mail message by anyone else is 
> > unauthorized. If you are not the intended recipient, any disclosure, 
> > copying, distribution or any action taken or omitted to be taken in 
> > reliance on it is prohibited and may be unlawful. 
> > 
> > The sender believes that this E-mail and any attachments were free of any 
> > virus, worm, Trojan horse, and/or malicious code when sent. This message 
> > and its attachments could have been infected during transmission. By 
> > reading the message and opening any attachments, the recipient accepts 
> > full responsibility for taking protective and remedial action about 
> > viruses and other defects. Cendant Corporation or Affiliates are not 
> > liable for any loss or damage arising in any way from this message or its 
> > attachments. 
 
Content-Type: text/html; name="Attachment: 1" 
Content-Transfer-Encoding: quoted-printable 
Content-Description:

Re: Lock Problem

2001-06-06 Thread Riyaj_Shamsudeen


Look at v$session_wait. It should tell you what the users are waiting for.. Usually, SQL*NET waits are ignorable..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






hamid alavi <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/06/01 01:58 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Lock Problem


Hi List,

We have an application running on Oracle database
8.0.5 on NT, this application developed for 1500 users
but after 15 connections every thing is locked, they
asked me to have a look and find out what's the
problem.
If any body have any clue which part must check first
and most likley what sort of problem it is, i really
appreciate.

Thanks in advance.

=
Hamid Alavi
4268 Flintlock LN
Westlake Village 91631
PH: 818-8790966

__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35 
a year!  http://personal.mail.yahoo.com/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: hamid alavi
  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: Check/Remove stray shared_memory_segment and semaphore

2001-06-03 Thread Riyaj_Shamsudeen


Yep. That's what I meant :-) In fact, if you select parallel server option, relink fails. There is a flag missing and not documented anywhere other than one metalink post..:-) Took two weeks just to install the software and talk about  " fire all your DBAs" crap..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Jared Still <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/02/01 05:56 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: Check/Remove stray shared_memory_segment and semaphore



Not necessary to use glibc-2.1, Oracle has a patch for using
with glibc-2.2.  It's on the download page for linux Oracle, it's
at technet.oracle.com.

It is a set of stubs that point 2.1 calls to 2.2 functions.  It 
requires relinking Oracle and includes instructions.

Jared


On Friday 01 June 2001 19:15, [EMAIL PROTECTED] wrote:
> Hi
>         Look under $ORACLE_HOME/bin for oracle executable. Do you see the
> file ? There was an installation bug and the relink fails with out
> creating the executable. If you do see the file then, check under
> $ORACLE_HOME/rdbms/log for any alert messages or traces. Also, Did you
> follow the procedure to use glibc-2.1 instead of glibc-2.2 ?
>
> Thanks
> Riyaj "Re-yas" Shamsudeen
> Certified Oracle DBA
> i2 technologies   www.i2.com
>
>
>
>
> "Apps Sol" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 06/01/01 11:44 AM
> Please respond to ORACLE-L
>
>
>         To:     Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>         cc:
>         Subject:        Check/Remove stray shared_memory_segment and
> semaphore
>
>
> Folks,
>
> I am trying to start a new db on one of my new Linux box
> Dell 2450 , RHAT 7.0 and 8.1.6 .. 1GB ram and huge disk space ..
>
> but I get a error at SVRMGR
> ORA-03113: end-of-file on communication channel
>
> when I further checked and changed my SID to a new one .. no luck
> I even tried DBASSIST (Hate to do it though) no luck ..
> there is no error in my alert log
>
> Can any one tell me what are the things i need to look into ..
>
>
> Cheers
> RK


Content-Type: text/html; charset="us-ascii"; name="Attachment: 1"
Content-Transfer-Encoding: 7bit
Content-Description: 

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jared Still
  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: Check/Remove stray shared_memory_segment and semaphore

2001-06-01 Thread Riyaj_Shamsudeen

Hi
Look under $ORACLE_HOME/bin for oracle executable. Do you see the file ? There was an installation bug and the relink fails with out creating the executable. If you do see the file then, check under $ORACLE_HOME/rdbms/log for any alert messages or traces. Also, Did you follow the procedure to use glibc-2.1 instead of glibc-2.2 ?
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"Apps Sol" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/01/01 11:44 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: Check/Remove stray shared_memory_segment and semaphore

Folks,

I am trying to start a new db on one of my new Linux box
Dell 2450 , RHAT 7.0 and 8.1.6 .. 1GB ram and huge disk space ..

but I get a error at SVRMGR
ORA-03113: end-of-file on communication channel

when I further checked and changed my SID to a new one .. no luck
I even tried DBASSIST (Hate to do it though) no luck ..
there is no error in my alert log

Can any one tell me what are the things i need to look into ..

Cheers
RK

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

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

Re: Public Synonyms & Performance

2001-06-01 Thread Riyaj_Shamsudeen


You could get more information about this subject in Steve Adams website..Here is the few lines from his site:
" Public synonyms should be entirely avoided in high concurrency environments. The memory wasted is proportional to the number of public synonyms in use, times the number of users. The performance cost is mainly in terms of increased latching in the shared pool and library cache. This script shows the number of users for each public synonym in use. This indicates whether there is any risk of potential performance problems due to public synonyms. 
This script tends to understate the severity of the problem, because it looks for the library cache structures used to represent non-existent objects while resolving public synonyms, and these structures can be freed from the cache very quickly under some circumstances. 
"
http://www.ixora.com.au/scripts/library.htm
http://www.ixora.com.au/q+a/0012/05112945.htm
http://www.ixora.com.au/newsletter/2001_05.htm

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Pablo ksksksk <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
06/01/01 07:35 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Public Synonyms & Performance


HI all,

I think someone ask this question before, but I don't
have any mails about this.

What are the performance implications in the use of
PUBLIC synonyms ? (What about private synonyms?)

How does Oracle resolve internally a public synonym
use?


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: About parallel server

2001-05-31 Thread Riyaj_Shamsudeen


Ray
        I think, you are referring to RAC, Real Application Cluster. Which is same as OPS with cache fusion for all scenarios. Please see my previous mail thread with the same subject..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Ray Stell <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/31/01 11:36 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: About parallel server




I have heard from iouga attendees that there was is something
called "Rack" from oracle/compaq on the horizon with 9i which
is the next replication solution.  Can folks comment on this?
What is the architecture and how does this play with oracle?
Is this just an OPS on some alphastation?  Our compaq sales
droid didn't know the answer.  Thanks. 



On Wed, May 30, 2001 at 02:07:10PM -0800, Fernando Papa wrote:
> 
> Hi everybody!
> 
> I have some questions about parallel server. Now we have only one instance
> "stand-alone" (no parallel), but we are thinking to switch to parallel
> server because we have a couple of sparc 3500 and nobody are using it, and
> we think it's good for increase our processing power.
> 
> The problem is I didn't work with parallel server and I have a lot of
> questions about it:
> 
> 1) Is mandatory to use raw devices for control files, redo logs & data
> files?
> 2) How we transfer our cooked data files to raw devices data files?
> import/export? or exist another better (fast) method?
> 3) If I start with only one node, performance will be the same of one single
> instance (no parallel)?
> 4) Somebody know how to work with raw devices under solaris? any link? I try
> to found someting in metalink but there's no samples...
> 5) What about backup? I can't put tablespaces in backup mode and copy with
> cp... maybe it's time to use rman?
> 
> Thanks in advance!
> 
> --
> Fernando O. Papa
> DBA
> El Sitio - Infraestructura
> (54-11) 4339-3854
> 
> -- 
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> -- 
> Author: Fernando Papa
>   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).

-- 
===
Ray Stell   [EMAIL PROTECTED]     (540) 231-4109     KE4TJC    28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  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:About parallel server

2001-05-31 Thread Riyaj_Shamsudeen


Dick
        As far as I know, at least in Sun, sequent, hp and linux, you need to have control files and redo log files in raw disk. Every instance needs access to other thread's redo log files to do instance recovery for the failed instances. Only if the data  is in the raw disk, multiple nodes can share the same disks. Control file is updated by all the instances. Out of curiosity, what OS were you using ? I just read Scott Heisey's email too and realized that you don't need to use raw disks for few platforms such as True64. May be you were one of those platforms?
        Rachel is absolutely correct and we have learnt this lesson hard way. But Oracle 9i Real Application cluster ( aka OPS) is supposedly remove these barriers. Cache fusion is a new feature introduced in 8i, by which disk writes due to true ping is avoided by transferring the data directly from one instance to another instance cache,( only for consistent reads in 8i). Oracle 9i apparently introduces cache fusion for read/read, read/write,write/write scenarios also. In this case, there is a probability that the required block to be transferred between the instances just from the buffer cache itself avoiding the disk access. Also Oracle claims that Real application cluster does not need any change in the application design. I am not sure how all this going to play in the real field, but interesting to know.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Rachel Carmichael" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/30/01 09:50 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re:About parallel server



Even more importantly, if you are planning on implementing parallel server 
just "to increase your processing power" then you are going to be in 
trouble!

If you haven't specifically designed your application for parallel server, 
you can end up DECREASING performance by increasing locking and pings.

This is not something you do lightly once an app has been installed into 
production.

Rachel

>From: [EMAIL PROTECTED]
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: Re:About parallel server
>Date: Wed, 30 May 2001 14:48:03 -0800
>
>Fernando,
>
>     Replies included in your original mail, but in addition:
>
>     Parallel server is a separately priced option from Oracle and it is 
>pricey.
>Second you may need specific software from you OS vendor to coordinate the 
>file
>sharing between the servers, again an additional expense.
>
>Dick Goulet
>
>Reply Separator
>Author: "Fernando Papa" <[EMAIL PROTECTED]>
>Date:       5/30/2001 2:07 PM
>
>
>Hi everybody!
>
>I have some questions about parallel server. Now we have only one instance
>"stand-alone" (no parallel), but we are thinking to switch to parallel
>server because we have a couple of sparc 3500 and nobody are using it, and
>we think it's good for increase our processing power.
>
>The problem is I didn't work with parallel server and I have a lot of
>questions about it:
>
>1) Is mandatory to use raw devices for control files, redo logs & data
>files?
>-->  Data files yes, redo and control files can be on cooked file system.
>
>2) How we transfer our cooked data files to raw devices data files?
>import/export? or exist another better (fast) method?
>--> To the best of my knowledge your going to have to rebuild the database 
>from
>scratch so imp/exp is your only option.
>
>3) If I start with only one node, performance will be the same of one 
>single
>instance (no parallel)?
>--> Yes and NO, raw devices run a little faster than cooked files since the 
>OS's
>buffer cache is not in the middle.
>
>4) Somebody know how to work with raw devices under solaris? any link? I 
>try
>to found someting in metalink but there's no samples...
>--> Working with raw devices is very different from cooked file systems.  
>If you
>don't have an experienced Unix admin you could be in serious trouble.
>
>5) What about backup? I can't put tablespaces in backup mode and copy with
>cp... maybe it's time to use rman?
>--> Rman can handle the backups, but a file system level backup is 
>different.
>CP does not work anymore, nor does fbackup, or tar.  You'll need 
>specialized
>software for the purpose.
>
>Thanks in advance!
>
>--
>Fernando O. Papa
>DBA
>El Sitio - Infraestructura
>(54-11) 4339-3854
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Fernando Papa
>   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 me

Re: alter session set events 'immediate trace name redohdr level 10'

2001-05-30 Thread Riyaj_Shamsudeen

Hi Linda
Welcome to Oracle! I am aware that db2 has wealth of documentation and manuals, but you would n't find these information any where in the Oracle documentation..
Anyway , here is the info that you are looking for: Again, this is from my memory so use caution..

siz: Indicates the size of the log file in log block size. So your log file size would be 20480 * 512 =10M
seq: Log sequence # in hex
hws: heck, I don't remember this:-( Been a while looking at this..
bsz:log block size. same as 'select lebsz from x$kccle'
nab:next available block.
flg:Status of the log, like current etc. If I remember correctly, this is a bitmap to indicate various statuses.
dup: # of members in the group.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

"Hagedorn, Linda" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/30/01 03:38 PM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: alter session set events 'immediate trace name redohdr level 10'

Hello,
I'm looking for documentation to name and label the output from the dump of redo headers and logs. For example,
siz: 0x5000 seq: 0x1087 hws: 0x2 bsz: 512 nab: 0x5001 flg: 0x0 dup: 2
The size parameter is what, the header size in hex? x5000 = decimal 20480?
In the DB2 world, this kind of information is readily available and provided to licensed customers in the DB2 Diagnosis Manual. I would think the Oracle diagnosis info should be as available as IBM's. Yes? A search in MetaLink for this list returned zero hits: siz seq hws bsz nab flg dup
Any information or referral to documentation is appreciated. Thanks, Linda. Oracle DBA, former IBM DB2 L2.

Re: io size of Unix machine

2001-05-30 Thread Riyaj_Shamsudeen





Are you trying to find maximum size of the IO possible in Sun/Solaris ?
Kernel parameter maxphys parameter determines the maximum size of the IO
possible from the OS. By default, it is around 128k depending upon what
hardware you use(sun4d=124k, sun4u=128ketc ). If you user veritas volume
manager, then vol_maxio parameter is the maximum size of the IO possible
through veritas layer. If you use disk suite then, md_maxphys is the
maximum IO size. So, if you want to set your IO size, then set both
parameters maxphys and vol_maxio to that value (assuming you are using
veritas ). Also, I think, veritas has hard limit of 256k and Oracle has
hard limit of 512k.

Your values should depend upon the type of application, type of disk
drives, stripe width etc..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com
|++|
||   TAG DBA  ||
|||   Multiple recipients  |
||   Sent by: |   of list ORACLE-L |
||   [EMAIL PROTECTED] |   <[EMAIL PROTECTED]|
|||   >|
||   05/30/01 03:56 AM|           cc:  |
||   Please respond to|           Subject: |
||   ORACLE-L |   io size of Unix  |
|||   machine  |
|++|






How can I find the logical or physical I/O size on a Sun Solaris machine ?
Thanks,
~aslam


 =?iso-8859-2?Q?attphuo7.dat?=

RE: Analyze table and locking

2001-05-30 Thread Riyaj_Shamsudeen

Analyze index validate structure takes shared lock on the table disallowing any changes to the table or indexes. That's the only way to validate the structures.

In earlier versions (7.3 ?) 'analyze ..compute' used to take table level locks. From 8 onwards, compute or estimate does not take any table level or row level locks at all...

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies www.i2.com

Robertson Lee - lerobe <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/30/01 10:25 AM
Please respond to ORACLE-L

To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Analyze table and locking

Ok, now I'm confused

One reply
-
It depends. For instance, 'analyze index validate structure' takes out
'shared' lock on the table (preventing inserts/deletes/updates), while
'analyze index compute/estimate statistics' does not.

Another
---
Compute option locks a table, but 'estimate' doesn't

Anyone have a definitive answer.

PS. Thanks to the guys who have replied up to now.

Lee

The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged. If the reader
of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or
copying of this communication is strictly prohibited.
If you have received this communication in error, please
re-send this communication to the sender and delete the
original message or any copy of it from your computer
system.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Robertson Lee - lerobe
INET: [EMAIL PROTECTED]

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

Re: catalog.sql catproc.sql

2001-05-22 Thread Riyaj_Shamsudeen


I think, you may be better off doing this during the maintenance window. When you execute catolog.sql it invalidates the standard package which in turn invalidates many, many other packages. You may run in to major performance problems if you do this, while users are using the database..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






Ray Stell <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/22/01 09:22 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        catalog.sql catproc.sql


While it doesn't seem like a real great idea, can I get away with
running catalog.sql and catproc.sql while db has users and jobs
running?  I have a real hard time getting a maintenance window, 
Some docs I've seen explicitly say to do a startup rather 
than startup restrict.
===
Ray Stell   [EMAIL PROTECTED]     (540) 231-4109     KE4TJC    28^D
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ray Stell
  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: import oddity

2001-05-18 Thread Riyaj_Shamsudeen


You could also use indexfile=filename option during import. This will not import any data but will write DDL to the index file. Create table statments will be REM med in the index file. Just remove those REM from the index file, change the storage parameter for the tables and indexes and run the file.. I use this approach all the time..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Rachel Carmichael" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/18/01 08:35 PM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        Re: import oddity



you can create the table in the database before the import with the size 
parameters you want and then import with ignore=y to ignore errors. Note 
that this will ignore ALL errors

>From: Dennis Taylor <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: import oddity
>Date: Fri, 18 May 2001 15:55:23 -0800
>
>I'm trying to do an import into a database. I've done this before, and it's
>always worked. Now it's failing with an error 1658 on a particular table.
>Teh error text includes this:
>
>"pctfree 10 pctused 40 initrans 1 maxtrans 255 storage (initial 1274224640
>next 637075456 minextents 1 maxextents 121" ... etc..
>
>To my inexperienced eye, it looks like the import is trying to create the
>table with an initial size of 1.3 GB or so. The table is *not* that big. I
>think it's around 200MB at most. As my tablespace datafiles are only 1GB
>each, I can see why the create fails (assuming I'm not misunderstanding
>everything).
>
>Is there anything I can do to tell the import not to create the table this
>big? Or to tell the database not to? Or other alternatives?
>
>
>
>
>Dennis Taylor
>
>CRASH:
>
>                 An audible warning that it's "downtime time" again.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Dennis Taylor
>   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

-- 
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: index FS vs index FFS - what's the difference ?

2001-05-17 Thread Riyaj_Shamsudeen


Hi Ed
        I think, For full index scan, oracle server process reads the root block and branch blocks 1 block at a time. It reads the leaf blocks at a db file multiblock -read count blocks per read call.  For the fast full scan, it reads all the block from the segment header to high water mark at a db_file_multiblock_read count blocks per read call. So, root blocks and branch blocks are read 1 block at a time, hence the db file sequential read. Where as for the fast full index scan you will see sequential read events only for the root block. Also, for the full index scan apparently knows about the extent boundary and if the next group of blocks to be read (in the current extent) is less than the db file multiblock read count , then it tries to read until the end of the current extent. That's why 11 blocks instead of 8 blocks.
        For the 'db file parallel read' the segment read are file 1 block 11, which is a system rollback segment extent. If you dump the block, you could see the redo for index leaf operations. I guess, this is due to delayed instance recovery. 
        All these, I found from dumping blocks and doing research myself. 

Steve,
        Can you please clarify ?

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Shevtsov, Eduard" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/17/01 03:40 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        index FS vs index FFS - what's the difference ?


Hi List,

I've made two corresponding dumps and ... I must admit I have much less
understanding than I had before.
Please look at them.

=
PARSING IN CURSOR #1 len=68 dep=0 uid=32 oct=3 lid=32 tim=219058914
hv=168660475 ad='aa69f79c'
select /*+ INDEX(documents pk_documents) */ count(*)
from documents
END OF STMT
PARSE #1:c=12,e=29,p=27,cr=322,cu=6,mis=1,r=0,dep=0,og=4,tim=219058914
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=219058914
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=108810 p3=1
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=109485 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=108811 p3=1
WAIT #1: nam='db file scattered read' ela= 1 p1=22 p2=108812 p3=11
[skip]

WAIT #1: nam='db file parallel read' ela= 1 p1=1 p2=11 p3=11
WAIT #1: nam='db file parallel read' ela= 0 p1=1 p2=11 p3=11

[skip]

WAIT #1: nam='db file scattered read' ela= 0 p1=22 p2=109472 p3=11
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=109483 p3=1
WAIT #1: nam='db file sequential read' ela= 0 p1=22 p2=109486 p3=1
WAIT #1: nam='db file parallel read' ela= 0 p1=1 p2=11 p3=11

=
PARSING IN CURSOR #1 len=72 dep=0 uid=32 oct=3 lid=32 tim=219108177
hv=3669603672 ad='aa6a4f84'
select /*+ INDEX_FFS(documents pk_documents) */
count(*)
from documents
END OF STMT
PARSE #1:c=11,e=13,p=27,cr=322,cu=6,mis=1,r=0,dep=0,og=4,tim=219108177
EXEC #1:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=4,tim=219108177
WAIT #1: nam='SQL*Net message to client' ela= 0 p1=1413697536 p2=1 p3=0
WAIT #1: nam='file open' ela= 0 p1=0 p2=0 p3=0
WAIT #1: nam='db file sequential read' ela= 1 p1=22 p2=108809 p3=1
WAIT #1: nam='db file scattered read' ela= 2 p1=22 p2=108810 p3=8
WAIT #1: nam='db file scattered read' ela= 0 p1=22 p2=108818 p3=8
[skip, all next lines are the same]

My questions are

1. Why I get 'db file scattered read' waits for index full scan ?
2. Why p3 for this event is 11 even though my db_file_multiblock_read_count
= 8 ?
3. What does mean 'db file parallel read' event? 

Anjo Kolk's paper doesn't describe it (guess the wait is Oracle8-specific).
Oracle 8i Reference says:

 This happens during recovery. Database blocks that need to be
changed as 
part of recovery are read in parallel from the database.

But it seems not my case.

4. Finally, what is the difference between the two index paths in that case
?

I'm on 8.1.7.0 Solaris, file-based system, LMT


Thanks in advance,
Ed
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Shevtsov, Eduard
  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: Imedia query tuning

2001-05-16 Thread Riyaj_Shamsudeen


May be you could rewrite your queries like this..

select /*+ ORDERED use_nl(b) index(a index_on_pk_category_id) index(b index_on_fk_category) */
  depth, title, description, url, editor_choice 
  from category a,site b
   where b.fk_category = a.pk_category_id and
         a.status=0 and
         b.status=0 and    
         ((contains (title, 'box') > 0) or 
         (contains (description, 'box' ) > 0))
 order by editor_choice desc; 

        Enter correct index name in the above index hints..This also assumes that # of rows with status=0 is probably very less (less than 10%). Sorry if this is a duplicate post..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






"Koivu, Lisa" <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/16/01 09:36 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        RE: Imedia query tuning


Henrik, FYI 
The ordered hint refers to the order in which the tables are accessed in the query.  It has nothing to do with an index. 
  
Lisa Rutland Koivu 
Oracle Database Administrator 
Certified Self-Important Database Deity 
Slayer of Unix Administrators 
Wanton Kickboxing Goddess 
[EMAIL PROTECTED] 
  
 NeoMedia 
  
2201 Second St., Suite 600 
Fort Myers, FL 33901, USA 
Phone: 941-337-3434 
Fax: 941-337-3668 
www.neom.com  
www.paperclick.com  
www.qode.com  
  
P a p e r C l i c k . c o m  
  

 Enter Your PaperClick Code Here! 
  
  

-Original Message- 
Sent: Wednesday, May 16, 2001 8:56 AM 
To: Multiple recipients of list ORACLE-L 

Hello, 
I'm not a guru : I just try to help you. :-> 
Hint for Query 2 : 
Have you try to hint so you run on the editor_choice index. If it 
works. Can you remove the Order command. Because the index is already 
ordered. 


Regards 
Henrik E. 

On Wed, 16 May 2001, Ranganath K wrote: 
> Dear DBA Gurus, 
> 
>       I have the following two queries along with execution plan and statistics. 
> The first one is taking a long time to execute.  The second query is taking 
> a long time to execute when I use the order by clause.  Is there any way I 
> can reduce the execution time as these queries will be used by a search 
> engine?  Any help in this regard will be greatly appreciated. 
> 
> SQL> select depth, count(*) a from category, site 
>   2  where (site.fk_category in (select pk_category_id from category 
>   3  where category.status = 0)) and site.status = 0 
>   4  and ((contains (title,'box') > 0) or 
>   5  (contains (description, 'box') > 0)) 
>   6  and pk_category_id = fk_category group by depth order by a desc; 
> 
> 467 rows selected. 
> 
> Elapsed: 00:00:16.43 
> 
> Execution Plan 
> -- 
>    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=214936 Card=22470 By 
>           tes=136572660) 
> 
>    1    0   SORT (ORDER BY) (Cost=214936 Card=22470 Bytes=136572660) 
>    2    1     SORT (GROUP BY) (Cost=214936 Card=22470 Bytes=136572660) 
>    3    2       NESTED LOOPS (Cost=19 Card=22470 Bytes=136572660) 
>    4    3         NESTED LOOPS (Cost=4 Card=15 Bytes=60945) 
>    5    4           TABLE ACCESS (BY INDEX ROWID) OF 'SITE' (Cost=2 Ca 
>           rd=1 Bytes=4037) 
> 
>    6    5             BITMAP CONVERSION (TO ROWIDS) 
>    7    6               BITMAP OR 
>    8    7                 BITMAP CONVERSION (FROM ROWIDS) 
>    9    8                   SORT (ORDER BY) 
>   10    9                     DOMAIN INDEX OF 'SITE2TITLEIDEX' (Cost=1 
>           ) 
> 
>   11    7                 BITMAP CONVERSION (FROM ROWIDS) 
>   12   11                   SORT (ORDER BY) 
>   13   12                     DOMAIN INDEX OF 'SITE1DESCIDX' (Cost=1) 
>   14    4           TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost= 
>           1 Card=1499 Bytes=38974) 
> 
>   15   14             INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE) 
>   16    3         TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY' (Cost=1 
>           Card=149802 Bytes=301851030) 
> 
>   17   16           INDEX (UNIQUE SCAN) OF 'SYS_C001314' (UNIQUE) 
> 
> Statistics 
> -- 
>         786  recursive calls 
>          40  db block gets 
>        5919  consistent gets 
>        1389  physical reads 
>           0  redo size 
>     1829532  bytes sent via SQL*Net to client 
>       69737  bytes received via SQL*Net from client 
>         920  SQL*Net roundtrips to/from client 
>          13  sorts (memory) 
>           0  sorts (disk) 
>         467  rows processed 
> 
> SQL> select depth, title, description, url, editor_choice from category,site 
>   2  where (site.fk_category in (select pk_category_id from category 
>   3  where category.status = 0)) and site.status = 0 
>   4  and site.fk_category = category.pk_category_id 
>   5  and (

Re: PL/SQL Question

2001-05-11 Thread Riyaj_Shamsudeen


dbms_lock.sleep will do this...

Thanks

Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com






[EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/11/01 10:20 AM
Please respond to ORACLE-L

        
        To:        Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
        cc:        
        Subject:        PL/SQL Question


I need to whip out a PL/SQL procedure real quick today and have a quick
question for fellow-listers ( since today is Friday, hopefully I don't get
RTFMed on this one :) )
The purpose of my procedure is to collect stats from v$session_wait
periodically ( every second for example) and pump the data into a stats
table.
But how to make the procedure to wait for a specified time? I know of the
option of using dbms_jobs to handle this. But I am wondering if there is a
similar
function in PL/SQL similar as the Unix 'sleep' command.

TIA

Dennis Meng
Database Administrator
Focal Communications
847-954-8328

-- 
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: V$session

2001-05-02 Thread Riyaj_Shamsudeen



Hi Seema
 v$session does not have os process id. v$process has the column spid
which is the unix process id. You could join v$session and v$process using
the columns paddr and addr respectively.

select sid,b.serial#,b.program,b.username  from v$process a,v$session b
where a.addr=b.paddr
and a.spid=&proc_id

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
 
"Seema Singh"  
 
<[EMAIL PROTECTED]>   
 
Sent by:  cc:  
 
[EMAIL PROTECTED]Subject: V$session   
 
om 
 
   
 
   
 
05/02/01 11:00 
 
AM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




Hi
Which column in v$session indicate os process?
Thanks
-seema
_
Get your FREE download of MSN Explorer at http://explorer.msn.com

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
  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: Anybody getting ORA-00600 [729] [space leak] on a regular base?`

2001-05-02 Thread Riyaj_Shamsudeen



Hi
 We had ORA-00600 [729] problem on a regular basis too. I am not sure
about your database errors, but in out database these errors are due to
audit_trail being turned on. In the PGA Ptvt. Audit Info memory chunks are
not being considered and they are being falsely identified as space leak.
If you look at the trace file and add up all the chunk sizes for the Prvt.
Audit Info chunks, you could see that they match to the bytes reported in
the ORA-600 parameter.

grep "Pvt. audit info"  otmxp02_ora_10424.trc |awk '{sum+=$4}END {print
sum}'

25256

>From the trace file:

*** 2001.04.17.09.29.42.000
*** SESSION ID:(30.22436) 2001.04.17.09.29.42.000
 ERROR: UGA memory leak detected 25256 
**

Hope this helps!

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   

"Nuno Souto"   

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

Sent by: cc:   

[EMAIL PROTECTED] Subject: Anybody getting ORA-00600 
[729] [space leak] on a
 regular base?`

   

05/02/01 09:19 AM  

Please respond to  

ORACLE-L   

   

   





In the alert log?
HP9K, HP-UX, Oracle 8.0.6.0.
I'm getting about 6 a day.
No apparent harm to the DB.
Metalink as usual says: "can't reproduce
problem" to most of these bugs.
Couldn't find a concrete fix to this anywhere.
Nope, I'm not using direct export.

TIA for any feedback.
Cheers
Nuno Souto
[EMAIL PROTECTED]
http://www.users.bigpond.net.au/the_Den

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

library cache pin problem.

2001-05-02 Thread Riyaj_Shamsudeen


Hi
 I am trying to grant execute on dbms_pipe package to user userA. My
session was hanging. So I went to v$session_wait to find why my session is
hanging. My session was waiting for library cache pin. I looked at x$kglpn,
x$kglob and saw that my statement was requesting the library cache pin on
exclusive mode(3) and few sessions were executing PL/SQL blocks with calls
to dbms_pipe.unpack_message (so holding the same library cache pin in
shared mode). My questions is why would 'grant' will request the library
cache pin in exclusive mode. I am not even granting to the same user who is
holding the pin. Any ideas ?

SQL> l
  1  SELECT
  2  a.KGLPNMOD, a.KGLPNREQ, b.username, c.KGLNAOBJ,
  3  c.KGLOBTYP
  4  FROM
  5  x$kglpn a,
  6  v$session b,
  7  x$kglob c
  8  WHERE
  9  a.KGLPNUSE = b.saddr and
 10  --upper(c.KGLNAOBJ) like upper('%testpkg%') and
 11  a.KGLPNHDL = c.KGLHDADR
 12* and c.kglnaobj='DBMS_PIPE'
SQL> /
  KGLPNMOD   KGLPNREQ USERNAME KGLNAOBJ   KGLOBTYP
-- --   --
 2  0 TEST_USER3   DBMS_PIPE 9
 2  0 TEST_USER3   DBMS_PIPE11
 2  0 TEST_USER3   DBMS_PIPE 9
 2  0 TEST_USER3   DBMS_PIPE11
 2  0 TEST_USER3   DBMS_PIPE 9
 2  0 TEST_USER3   DBMS_PIPE11
 0  3 SYS  DBMS_PIPE 9
 2  0 TEST_USER3   DBMS_PIPE 9
 2  0 TEST_USER3   DBMS_PIPE11

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

-- 
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: RESOURCE role

2001-04-30 Thread Riyaj_Shamsudeen



Hi
 Unlimited tablespace privilege is granted with resource role. This
privilege is granted directly to the user rather than through the resource
role. Hence you won't see this in dba_sys_privs view.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"Hand, 
   
Michael T"   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
  Subject: RESOURCE role
   
Sent by:   
   
root@fatcity.  
   
com
   
   
   
   
   
04/30/01   
   
04:41 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Listers,
8.0.6
What privilege granted to the RESOURCE role allows a grantee to create a
table in any tablespace?  I didn't see any relevent system privilege in the
view DBA_SYS_PRIVS nor in the various other view.  Am I looking in the
wrong
place?

Thanks,
Mike Hand
Polaroid Corp.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Hand, Michael T
  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: redo size and log_checkpoint_interval

2001-04-30 Thread Riyaj_Shamsudeen



Primary reason not to switch more often then 30 minutes to 1hour, is to
reduce the checkpointing frequency. If you switch every 3 to 4 minutes,
then waits for checkpoint related activity will be higher. Hence you may
want to increase the log file size and also set your
log_checkpoint_interval, and timeout to more than 30 minutes / log file
size, so that checkpoint will occur only during the log switches.

Checkpointing has changed slightly from Oracle8 onwards. In Oracle 8, the
SGA has a checkpoint queue which is an ordered list of buffers by their low
RBA. So DBW is continually checkpointing and writing the buffers from the
checkpoint queue and the ckpt process keeps a heartbeat about this
checkpoint progress in the control file. (check out x$kcccp table). So, the
effect of checkpoints in newer versions of Oracle is not quite dramatic as
previous versions. Due to this continuous checkpointing activity, the
thread recovery time is also minimized. But still it is better to keep the
log switching  frequency at 30 minutes to 1 hour since there are other
overhead associated with log switches.

So, I would increase the log file size and disable the
log_checkpoint_interval and log_checkpoint_timeout parameters by setting
them to an high value.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"Mitchell" 
   
  
net.ca>  cc:   
   
Sent by: Subject: redo size  and 
log_checkpoint_interval  
root@fatcity.  
   
com
   
   
   
   
   
04/30/01   
   
04:10 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Dear DBA

I have 130 gb database running on 7/24 basis. redo logfile size is 40mb and
oracle create about most to 400 archivive log. that mean log switch happend
at 3-4 min at peak time.

As oracle suggest, it is better log switch between 30min. (Why, if so that
need more recovery time?)
If  I will increase the size to 100mb and make time around 30 min. How
about
Log_checkpooint_interval. My currently setting is 2097152 (20mb).
log_checkpoint_timeout = 0

As my understand, the interval based on volume. If setting is 100mb, mean 5
check point will occured at 20mb interval. I am right?

Any suggest?

Mitchell



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

2001-04-24 Thread Riyaj_Shamsudeen



Yep. Rollup is a key word introduced in 8i and it is the variants
introduced with group by clause...Also they are  declared as dummy function
in standard package and parser will see that function before your table
name and hence the error..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"Morton,   
   
Ronald D"To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
 Subject: PLS-00225
   
Sent by:   
   
root@fatcity.  
   
com
   
   
   
   
   
04/24/01   
   
03:47 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hi,

I just ran into a problem with v8.1.6 on Linux whereby my stored procedure
won't compile because it claims that a table reference is out of scope.  It
complains about the definition of the arguments to the stored procedure
which are as follows:

CREATE OR REPLACE PROCEDURE rollup_insert
(
vv_rollup_idOUTrollup.rollup_id%TYPE,
vv_warrant_id   IN rollup.warrant_id%TYPE,
vv_rollup_limit_idINlimit.limit_id%TYPE,
vv_rollup_time  IN VARCHAR2,
vv_rollup_emp_badgeIN rollup.rollup_emp_badge%TYPE,
vv_exec_idOUTINT,
vv_rc   OUT INT
)
AS
...

The specific error message complains that "rollup" is out of scope.  If I
substitute base level types for these, the procedure will compile.  I have
included the "rollup" table definition and ownership below for reference.
It is the TWS user trying to execute this procedure.

desc rollup


-
ROLLUP_ID   NOT NULLNUMBER
WARRANT_ID NOT NULLNUMBER
ROLLUP_TIMENOT NULLDATE
ROLLUP_TIME_TZ NOT NULLVARCHAR2(3)
ROLLUP_EMP_BADGE  NOT NULLVARCHAR2(4)
ROLLUP_LIMIT_ID NOT NULLNUMBER
DATESTAMP  DATE

select owner, object_name, object_type from dba_objects where object_name
like '%ROLLUP%';

OWNER  OBJECT_NAME  OBJECT_TYPE



TWSROLLUP  TABLE
TWSROLLUP_INSERT  PROCEDURE

As you can see, the table and the procedure are owned by the same user.
The
table exists and the columns referenced in the arguments to the procedure
also exist.

What am I missing here?  Is there a new keyword called ROLLUP somewhere?

Any help would be very much appreciated.

TIA

Ron Morton
Database Architect / Administrator
Union Switch & Signal Inc
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Morton, Ronald D
  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

RE: Awfully annoying SQLLDR problem



Hi
 Apparently, it is complaining with 942. I would set event  '942 trace
name context forever errorstack, level 15' in the init.ora file and restart
the database, then reproduce the problem. Look at the trace file and
errorstack will probably give you what table the server is trying to
access.
 Is there any triggers or snapshot logs on the table that you are
trying to load ?

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
[EMAIL PROTECTED]  
   
Sent by: To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
root@fatcity.cc:   
   
com  Subject: RE: Awfully annoying SQLLDR 
problem 
   
   
   
   
04/23/01   
   
01:06 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




No you are reading correctly.  I have the data on a server with a 7.3.2.3
database and am using that databases' binary sqlldr to load into the 8.1.5
database.  The problem is a timing issue.  I need to execute the load from
the
7.3.2.3 server as part of a larger script.  I cannot ensure that the load
will
be executed i a timely manner if it is run on the distant server.

Whether or not you agree with the timing issue I describe, I'd really
appreciate
an answer to the Oracle problem.  I don't want to get into the larger issue
of
why I have to run it from one server vs. another.  From an Oracle POV it
should
work either way.

thanks,

..tom



> -Original Message-
> From: Mohan, Ross [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, April 23, 2001 12:27 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:   RE: Awfully annoying SQLLDR problem
>
> Ok,maybe i am lazy or a poor reader, but are you
> using 732 sqlldr binaries to load an 815 db?
>
> Why not the 815 sqlldr binaries?
>
> I mean, it sounds like you have to ship data over the
> wire no matter what you do, so...why not use the latest
> version of the sqlldr bits?
>
>
>
> <==>   -Original Message-
> <==>   From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
> <==>   Sent: Monday, April 23, 2001 11:10 AM
> <==>   To: Multiple recipients of list ORACLE-L
> <==>   Subject: Awfully annoying SQLLDR problem
> <==>
> <==>
> <==>   I am executing a SQLLDR shell file from a server ::
> <==>   sourceserv; RDBMS 7.3.2.3
> <==>   AIX 4.3.2
> <==>   into a database on server:: destserv; RDBMS 8.1.5 AIX 4.3.3
> <==>   using a SQL*Net connect string destserv_destSID.
> <==>
> <==>   This connect string is defined in the local tnsnames.ora
> <==>   and works.  I confirmed
> <==>   this by doing sqlplus user/pass@destserv_destSID and
> <==>   verifying that I am
> <==>   connected to the correct instance.
> <==>
> <==>   The target table exists.  I am connecting as the owner
> <==>   of the target table. That
> <==>   owner has DBA.
> <==>
> <==>   but when I run
> <==>
> <==>   sqlldr userid=tabowner/pass@destserv_destSID
> <==>   CONTROL=localcontrolfile.ctl
> <==>   (controlfile is below)
> <==>
> <==>   I get "SQL*Loader-925: Error while parsing a cursor (via ocisq3)
> <==>   ORA-00942: table or view does not exist"
> <==>
> <==>   Help!  Does anyone have any ideas?
> <==>
> <==>   tia,
> <==>
> <==>   ..tom
> <==>
> <==>   here's the controlfile
> <==>
> <==>   LOAD DATA
> <==>   INFILE data1.dat BADFILE load.bad
> <==>   DISCARDFILE load.dsc
> <==>   INSERT
> <==>   INTO TABLE OWNER.TEST_LOAD
> <==>   FIELDS TERMINATED BY ','
> <==>   TRAILING NULLCOLS
> <==>   (COL1,COL2,COL3)
> <==>
> <==>
> <==>   --
> <==>   Please see the official ORACLE-L FAQ: http://www.orafaq.com
> <==>   --
> <==>   Author:
> <==> INET: [EMAIL PROTECTED]
> <==>
> <==>   Fat City Network Services-- (858) 538-5051  FAX:
> <==>   (

RE: SQLTrace



Hi Lisa
 This is an underscore parameter. Hence v$parameter will not show this
parameter.. You got to look at x$ksppi and x$psppcv fixed tables to see
this parameter..Here is the SQL used if you are interested:
--cut
set linesize 80 pagesize 50 newpage 0
col param_dflt form a5 head 'Dflt?'
col param_name form a41 head 'Parameter'
col param_value form a31 head 'Value'

select decode (ksppstdf, 'FALSE', 'NO', ' ') param_dflt,
ksppinm param_name,ksppdesc descp, ksppstvl param_value
from x$ksppi, x$ksppcv
where x$ksppi.indx = x$ksppcv.indx
order by ksppinm
-cut
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"Koivu, Lisa"  
   
  
com> cc:   
   
Sent by: Subject: RE: SQLTrace 
   
root@fatcity.  
   
com
   
   
   
   
   
04/23/01   
   
11:40 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hi Riyas,


I had used this in previous versions.  I'm on 8.1.6 now.  I looked before
sending this response and I didn't see this in v$parameter.  What version
are you on?





-Original Message-
Sent: Monday, April 23, 2001 11:56 AM
To: Multiple recipients of list ORACLE-L






You can also set   _trace_files_public=TRUE in the init. ora file.This will

create trace files with 644 file permissions..
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com
"My opinions. Use at your risk"







"Koivu, Lisa"


com> cc:

Sent by: Subject: RE: SQLTrace

root@fatcity.

com



04/23/01

09:10 AM

Please

respond to

ORACLE-L









Dave, you can also set a command in the cron to chmod the trace files
frequently so everyone can access them.


Lisa Rutland Koivu
Oracle Database Administrator
Certified Self-Important Database Deity
Slayer of Unix Administrators
Wanton Kickboxing Goddess





[EMAIL PROTECTED]


 NeoMedia


2201 Second St., Suite 600
Fort Myers, FL 33901, USA
Phone: 941-337-3434
Fax: 941-337-3668
www.neom.com 
www.paperclick.com 
www.qode.com 


P a p e r C l i c k . c o m 









 Enter Your PaperClick Code Here!










-Original Message-
Sent: Monday, April 23, 2001 9:26 AM
To: Multiple recipients of list ORACLE-L








You can also set the user_dump_dest in init.ora to /tmp/oracle where all
users have access.





>>> [EMAIL PROTECTED] 04/20/01 05:27PM >>>
If I remember correctly the user can set autotrace on in the session and
the output if sent to the terminal after the sql command is completed.





Give it a try.. You might have to set up permessions or privileges to get
it to work for a user.
ROR mª¿ªm





>>> [EMAIL PROTECTED] 04/20/01 03:54PM >>>
I've got a developer that says they need the unix password for oracle so
they can do a trace and run tkprof without having to jump through a bunch
of hoops. Anyone set up trace so someone other than oracle can do this?





The permissions on the trace files only allow reading by the file owner, so


setting the user into the dba group won't help.





Thanks, Dave Turner





--

And you thought James Bond's watch was cool... http://www

Re: ORA-04030



How big is your shared pool size ? Do you have many dynamic SQLs ? You may
want to keep the sys packages in the shared pool, so that your shared pool
won't be fragmented by dynamic SQLs.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   

"Dorozhkin, Anton" 

  
l-life.ru>cc:  

Sent by: [EMAIL PROTECTED] Subject: ORA-04030   

   

   

04/21/01 07:05 AM  

Please respond to ORACLE-L 

   

   





I've got a such error when run a PL/SQL scripts
on Windows 2000, Orcale 8.1.7 with 128MB memory

ORA-04030: out of process memory when trying to allocate 257280 bytes (PLS
non-lib hp,PAR.C:parchk:ptb)

I've tried to change settings in init.ora but this didn't help.


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



You can also set   _trace_files_public=TRUE in the init. ora file.This will
create trace files with 644 file permissions..
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com
"My opinions. Use at your risk"



   
   
"Koivu, Lisa"  
   
  
com> cc:   
   
Sent by: Subject: RE: SQLTrace 
   
root@fatcity.  
   
com
   
   
   
   
   
04/23/01   
   
09:10 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Dave, you can also set a command in the cron to chmod the trace files
frequently so everyone can access them.

Lisa Rutland Koivu
Oracle Database Administrator
Certified Self-Important Database Deity
Slayer of Unix Administrators
Wanton Kickboxing Goddess


[EMAIL PROTECTED]

 NeoMedia

2201 Second St., Suite 600
Fort Myers, FL 33901, USA
Phone: 941-337-3434
Fax: 941-337-3668
www.neom.com 
www.paperclick.com 
www.qode.com 

P a p e r C l i c k . c o m 






 Enter Your PaperClick Code Here!







-Original Message-
Sent: Monday, April 23, 2001 9:26 AM
To: Multiple recipients of list ORACLE-L





You can also set the user_dump_dest in init.ora to /tmp/oracle where all
users have access.


>>> [EMAIL PROTECTED] 04/20/01 05:27PM >>>
If I remember correctly the user can set autotrace on in the session and
the output if sent to the terminal after the sql command is completed.


Give it a try.. You might have to set up permessions or privileges to get
it to work for a user.
ROR mª¿ªm


>>> [EMAIL PROTECTED] 04/20/01 03:54PM >>>
I've got a developer that says they need the unix password for oracle so
they can do a trace and run tkprof without having to jump through a bunch
of hoops. Anyone set up trace so someone other than oracle can do this?


The permissions on the trace files only allow reading by the file owner, so

setting the user into the dba group won't help.


Thanks, Dave Turner


--

And you thought James Bond's watch was cool... http://www.tellme.com
Call 1-800-555-TELL for stocks, sports, news...& much more!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: David Turner
  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: Ron Rogers
  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 of

Re:v$session and v$process -- Simple one

2001-04-07 Thread Riyaj_Shamsudeen


Hi
 This is the behavior of autotrace. The session has done 'set autotrace
on' and the autotrace creates a separate session using the same
process.

[tmatrix2161:RSHAMSUD@TMXDEV2] SQL> /
Enter value for username: RSHAMSUD
   SIDSERIAL#PID SPID  MACHINE   PROGRAM
-- -- -- - -

52  15994 39 28472 tmatrix2161
sqlplus@tmatrix2161 (TNS V1-V3)

[tmatrix2161:RSHAMSUD@TMXDEV2] SQL> show user
USER is "RSHAMSUD"
[tmatrix2161:RSHAMSUD@TMXDEV2] SQL> set autotrace on
[tmatrix2161:RSHAMSUD@TMXDEV2] SQL> /
Enter value for username: RSHAMSUD

   SIDSERIAL#PID SPID  MACHINE   PROGRAM
-- -- -- - -

16  31425 39 28472 tmatrix2161
sqlplus@tmatrix2161 (TNS V1-V3)
52  15994 39 28472 tmatrix2161
sqlplus@tmatrix2161 (TNS V1-V3)


Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
dgoulet@vicr.  
   
com  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
Sent by: cc:   
   
root@fatcity.Subject: Re:v$session and v$process -- 
Simple one
com
   
   
   
   
   
04/06/01   
   
12:35 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Looks to me like you've either got two users connected to the database from
Windows or one user with two sessions.  And if I'm not totally mistaken I
believe that 'OraUser' is a generic entry when the client computer does not
have
it's computer name set.

Dick Goulet

Reply Separator
Author: =?iso-8859-1?q?Pablo=20ksksksk?= <[EMAIL PROTECTED]>
Date:   4/6/2001 8:45 AM

Hello List,

I've a simple question.
Why do I see this output when I execute the following
query:

SQL> l
  1  select a.USERNAME,a.SPID "unix
pid",b.SID,b.serial#,b.osuser,b.program,a.program
  2  from v$process a, v$session b
  3  where a.spid='&unixpid'
  4* and a.addr=b.paddr
SQL>



USERNAMEunix pidSID   SERIAL# OSUSER
--- - - -
---
PROGRAM
PROGRAM


oracle  18463   102  1954 OraUser
C:\ORAWIN\BIN\F45RUN.EXE
oracle@fincons (TNS V1-V2)

oracle  18463   142  1263 OraUser
C:\ORAWIN\BIN\F45RUN.EXE
oracle@fincons (TNS V1-V2)


Why does it show me 2 oracle SIDs ???

This happens only on some unixpids.

TIA



___
Do You Yahoo!?
Envía mensajes instantáneos y recibe alertas de correo con
Yahoo! Messenger - 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:
  INET: [EMAIL PROTECTED]

Fat City Network Services-

Re: DBMS_REPAIR package usage

2001-03-22 Thread Riyaj_Shamsudeen



I would not decide whether a block is corrupted or not, just using dbv
utility. dbv reports corruption, even when analyze, exp and FTS goes
through fine without any problem. dbv reported a data dictionary corruption
in our case. We ran analyze, exp and FTS, no problem. But still dbv was
reporting corruption even after the database was down.

Further the database has to be down or the tablespace has to be offline
normal for dbv to work somewhat correctly (?)

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
Winnie_Liu@in  
   
fonet.comTo: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
Sent by: cc:   
   
root@fatcity.Subject: DBMS_REPAIR package usage
   
com
   
   
   
   
   
03/22/01   
   
04:22 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   






To all,

I have a datafile in my production box (a user data tablespace), when I run
dbv against it, it showed that 5 blocks are "influxed"

Page 458784 is influx - most likely media corrupt
***
Corrupt block relative dba: 0x24070020 file=0. blocknum=458784.
Fractured block found during dbv:
Data in bad block - type:0. format:0. rdba:0x
last change scn:0x. seq:0x0 flg:0x00
consistancy value in tail 0x0003c204
check value in block header: 0x0, check value not calculated
spare1:0x0, spare2:0x0, spare2:0x0

We can copy this file to tape, dd this file. On the OS disk level, the OS
does not treat this as corrupted. But it is corrupted on the oracle
(software) level.

I've checked and can't find any object associate with these 5 corrupted
blcok.

That means that there is no data inside those blocks.

Since the tablespace is about 12 GB on a highly active system (which only
got 3 hours maintance window each month), export/import (then drop the
tablespace)
which Oracle support suggested is mostly out of the question. (Especially,
it is very hard for me to convince the sysadmin that the blocks are
corrupted
as they don't see any I/O error associate with this file and the developers
don't see any problem with the application either!)

I am currently thinking about upgrading this database to 8.1.6 to make use
of the DBMS_REPAIR package to make those blocks as "unusable". But I
am not sure that if the DBMS_REPAIR package can run against the blocks
which do not belong to any objects!! Can someone  give me some
guidences?

thanks

Winnie



--
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 remov

RE: Log writer

2001-03-22 Thread Riyaj_Shamsudeen



I agree that the log buffer is too big. But why do you want to decrease the
log file size from 32M to 5MB. This will increase the checkpoint frequency
and will create performance hit due to increased checkpoints. Also, I would
add log files to use all three disks rather than just 2 disks.

   GROUP 11 (
 'E:\ORACLE\ORADATA\JAVA16\LOG1101.LOG',
 'F:\ORACLE\ORADATA\JAVA16\LOG1102.LOG'
   ) SIZE 32M,
   GROUP 12 (
 'G:\ORACLE\ORADATA\JAVA16\LOG1201.LOG',
 'E:\ORACLE\ORADATA\JAVA16\LOG1202.LOG'
   ) SIZE 32M,
   GROUP 13 (
 'F:\ORACLE\ORADATA\JAVA16\LOG1301.LOG',
 'G:\ORACLE\ORADATA\JAVA16\LOG1302.LOG'
   ) SIZE 32M

 Further your application IO is 25% FTS and 75% index based access. I
 would definitely pull the sql statements out of the v$sqlarea and tune
 them..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com
"My opinions and use at your own risk"


   
   
"Mohan, Ross"  
   
  
-SMI.com>cc:   
   
Sent by: Subject: RE: Log writer   
   
root@fatcity.  
   
com
   
   
   
   
   
03/22/01   
   
01:30 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Uh.67,676 kb. ?   as in   67 MB?  for a redo log buffer?  Is this a
result
of our new president being a texan?

On your system,

1) one by one add three (3) new groups of redo log files. Make them 5MB in
size, each.
2) ALTER system switch logfile until all the old redo logs show INACTIVE in
V$LOG,
3) ALTER system checkpoint;
4) DELETE the 32MB log file groups. With prejudice. ( If they don't die,
call in Dick Goulet.)
5) Edit the init.ora and make the log_buffer...oh...say 2M. Bounce the
instance.

Let us know what happens.

Please.

- Ross
 -Original Message-
 From: Kevin Kostyszyn [mailto:[EMAIL PROTECTED]]
 Sent: Thursday, March 22, 2001 1:23 PM
 To: Multiple recipients of list ORACLE-L
 Subject: RE: Log writer

 thanks Ross,
 MTS, I didn't know it was on?  Isn't that just an init.ora
 setting?  I am looking in to finding out about the cache, but no luck
 yet.  The redo log buffer is 67,676 kb.  Yes, this isn't some normal
 little system where the duhvelopers are just querying the db, they are
 constantly doing DML, lots of FTS.  I have found so many indexes in
 the USERS tablespace which puts them on the same HD as the tables they
 are accessing.  I move them, but somehow more seem to always
 appear...hmmm..wonder why that is.
 But I am curious, when you say network/app tuning, what exactly do
 you mean by that?  I know that two disks aren't that great, but not my
 idea, it was done before my dumbass got here, now I am just trying to
 fix it.  Everything else looks good on the system though,
 library_cache hit ratio, buffer cache hit ratio all very high.  It's
 just this program occasionally, about every 10 minutes is telling me
 the logwriter is a slouch.
 What about my other question though,

 MAXLOGFILES 32
 MAXLOGMEMBERS 2
 MAXDATAFILES 32
 MAXINSTANCES 16
 MAXLOGHISTORY 4764
 LOGFILE
   GROUP 11 (
 'E:\ORACLE\ORADATA\JAVA16\LOG1101.LOG',
 'F:\ORACLE\ORADATA\JAVA16\LOG1102.LOG'
   ) SIZE 32M,
   GROUP 12 (
 'E:\ORACLE\ORADATA\JAVA16\LOG1201.LOG',
 'F:\ORACLE\ORADATA\JAVA16\LOG1202.LOG'
   ) SIZE 32M,
   GROUP 13 (
 'E:\ORACLE\ORADATA\JAVA16\LOG1301.L

RE: identifying shared memory segments

2001-03-21 Thread Riyaj_Shamsudeen



I think, your library path is not set correctly.
(ksh)
In sun
 export LD_LIBRARY_PATH=$ORACLE_HOME/lib
In HP
 export  SHLIB_PATH=$ORACLE_HOME/lib
IN AIX
 export LIBPATH=$ORACLE_HOME/lib
and then try again...

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
Mandar 
   
GhosalkarTo: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
 Subject: RE: identifying shared memory 
segments  
Sent by:   
   
root@fatcity.  
   
com
   
   
   
   
   
03/21/01   
   
12:28 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




David,
this is what i am getting. any ideas?

$ sysresv
ld.so.1: sysresv: fatal: libclntsh.so.8.0: open failed: No such file or
directory
Killed
$

-Mandar
> -Original Message-
> From: Lord David [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 21, 2001 11:03 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: identifying shared memory segments
>
>
> Under unix (Solaris at least): set your oracle environment and run
> $ORACLE_HOME/bin/sysresv.
> No idea about windoze.
>
> HTH
> David Lord
>
> -Original Message-
> Sent: 21 March 2001 16:02
> To: Multiple recipients of list ORACLE-L
>
>
> Me too!
>
> Thanks,Ruth B. Gramolini
> ORACLE & DB2  DBA
> VT Dept. of Taxes
> ph# 802.828.5708
> fax# 802.828..3754
> [EMAIL PROTECTED]
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, March 21, 2001 10:00 AM
>
>
> > A while back someone posted a method for determining which
> shared memory
> segments belonged to an instance. Can someone repost that and
> CC me? Thanks.
> >
> >
> > -
> > Do You Yahoo!?
> > Yahoo! Mail Personal Address - Get email at your own domain
> with Yahoo!
> Mail.
>
> --
> 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).
> **
> This email and any files transmitted with it are confidential and
> intended solely for the use of the individual or entity to
> whom they
> are addressed. If you have received this email in error please notify
> the system manager.
>
> This footnote also confirms that this email message has been swept by
> MIMEsweeper for the presence of computer viruses.
>
> www.mimesweeper.com
> **
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Lord David
>   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 mes

Re: EMC TimeFinder, and EMC TimeFinder vs Hot Standby

2001-03-21 Thread Riyaj_Shamsudeen



Hi
 For each physical device a BCV is associated. This is at a physical
level rather than at logical level. Timefinder is the product which
synchronizes these devices and bcvs. Each of these devices and BCVs has a
bit map track table to keep track of the disk tracks. This bit map
indicates whether a particular disk track has changed or not after the last
BCV synchronization operation. During BCV synchronization, timefinder
doesn't do dumb copy all the data from primary to BCV. Instead it uses the
bit map of disk tracks  and copies only the disk tracks that are changed.
In a typical VLDB only few percentage of the database changes every day and
hence the number of tracks changed are very minimal between backups. Hence
the synch process is much faster than regular OS based synchronization
mechanism. Not only the backup is faster, also the recovery is faster since
only the tracks changed need to be copied from the  BCVs to the primary
disks.
 SRDF is the product to keep the primary and secondary symmetrix unit
in synch using SRDF links, mostly for disaster/site recovery operations. If
you set up two sym units to be primary and secondary then all the writes to
the primary are propagated to the secondary (synchronously or
asynchronously depending upon the setup) and they are kept in  synch. For
example, if you have primary database in one symmetrix unit and the
secondary database in the second symmetrix unit, then since every write to
the redo log files are propagated, you could activate the standby database
without any data loss (or very minimal loss in rare cases). All these
operations are done without any host involvement. You could set up this
SRDF writes such that host write system calls will succeed only after the
secondary SRDF write confirms the receipt of the data to the primary. But
that means performance hit.
 Hope this helps!!

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com
" These are my opinions. Use at your risk"


   
   
Yosi@comhill.  
   
com  To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
Sent by: cc:   
   
root@fatcity.Subject: EMC TimeFinder, and EMC 
TimeFinder vs Hot Standby   
com
   
   
   
   
   
03/21/01   
   
05:10 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hi All,

Can anyone give me a quick (free!) lesson on the concepts
behind timefinder? How does this differ from their standard
SRDF which (to my understanding) is to split the mirror and
back it up.

Or is it that they add their BCV stuff to SRDF so you can
access the data while the mirror is split? Then, is it like
a Hot Standby DB?

(We used to get something in high school that was some sort
of mixture between fish and potatoes, and we could never
figure out if it was fish or if it was potatoes, or both,
or neither. Somehow, this is reminding me of that.)

Thanks loads,

Yosi
--
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

Re: Svrmgrl "connect internal" asks for password??

2001-03-07 Thread Riyaj_Shamsudeen



Do you have a password file set up for the 8.1.6 database ? Check your
init.ora file to see whether you have specified any password file to store
the internal password (parameter remote_login_passwordfile ..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   

"Fowler, Kenneth R"

<[EMAIL PROTECTED]To: Multiple recipients of 
list ORACLE-L <[EMAIL PROTECTED]>  
fizer.com>cc:  

Sent by: [EMAIL PROTECTED] Subject: Svrmgrl "connect 
internal" asks for password??  
   

   

03/07/01 11:00 AM  

Please respond to ORACLE-L 

   

   





List,


I have an installation of Oracle 8.1.6 on a DBA test server where svrmgrl
"connect internal" seems to want a password...


Sun Microsystems Inc.   SunOS 5.8   Generic February 2000


Configured oracle environment aliases...

Alias SID   Oracle Home
- ---   ---
dba816dba816/Volumes/app/oracle/product/8.1.6
dba817dba817/Volumes/app/oracle/product/8.1.7

$ id
uid=301(oracle) gid=300(dba)
$ groups
dba
$ env | grep ORACLE
ORACLE_BASE=/Volumes/app/oracle
$ dba816
dba816 $ env | grep ORACLE
ORACLE_BASE=/Volumes/app/oracle
ORACLE_SID=dba816
ORACLE_HOME=/Volumes/app/oracle/product/8.1.6
dba816 $ cdcreate
/Volumes/app/oracle/admin/dba816/create
dba816 $ svrmgrl

Oracle Server Manager Release 3.1.6.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.6.2.0 - Production
With the Partitioning option
JServer Release 8.1.6.2.0 - Production

SVRMGR> connect internal;
Password:
Password:
ORA-01031: insufficient privileges
SVRMGR> ^D
Server Manager complete.
dba816 $ dba817
dba817 $ env | grep ORACLE
ORACLE_BASE=/Volumes/app/oracle
ORACLE_SID=dba817
ORACLE_HOME=/Volumes/app/oracle/product/8.1.7
dba817 $ cdcreate
/Volumes/app/oracle/admin/dba817/create
dba817 $ svrmgrl

Oracle Server Manager Release 3.1.7.0.0 - Production

Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

SVRMGR> connect internal;
Connected.
SVRMGR> ^D
Server Manager complete.


I know that the usual reason for this is that the account running svrmgrl
is
not a member of the appropriate unix group (usually "dba" or as specified
at
installation time) but this is not the case here.  On this server I have
Oracle 8.1.6 (Home = /Volumes/app/oracle/product/8.1.6) and Oracle 8.1.7
(Home = /Volumes/app/oracle/product/8.1.7).  My login script sets up
aliases
for each SID listed in /var/opt/oracle/oratab.  These aliases use the
oraenv
script with environment variable ORAENV_ASK=NO to set appropriate values
for
ORACLE_SID, ORACLE_HOME and PATH etc.  Other than that, the environment is
exactly the same regardless of what SID I switch to (see log above).
However "connect internal" asks for a password for Oracle 8.1.6 but does
not
ask for one for Oracle 8.1.7.  I am pretty sure that I specified that the
group name be "dba" for 8.1.6 and 8.1.7 so that should not be the issue.


Can anybody give me some pointers of other things to look at to try and
resolve the problem???


Thanks,
Ken
_
Clinical and Regulatory Informatics - Groton/New London
Coordinator, Business and Technical Services
Tel: (860) 732-0026Fax: (860) 715-8346
Email: [EMAIL PROTECTED]


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

RE: Old INACTIVE and KILLED sessions

2001-03-06 Thread Riyaj_Shamsudeen



Yes. Sniped sessions are not being cleaned out properly by pmon. We kill
the os process associated with the connection using the following script
and pmon cleans up after that .

$ORACLE_HOME/bin/sqlplus -s / < $TMPFILE
set pages 0
set feedback off
select p.spid from v\$process p,v\$session s
where s.paddr=p.addr
and s.status='SNIPED';
exit
EOF
for X in `cat $TMPFILE`
do
kill -9 $X
done

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"Trassens, 
   
Christian"   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
   Subject: RE: Old INACTIVE and KILLED 
sessions
Sent by:   
   
root@fatcity.  
   
com
   
   
   
   
   
03/06/01   
   
05:35 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Beware that SNIPED sessions could last forever when you limit the IDLE_TIME
whether the users couldn't connect through the same connection. F.e. when
you use sqlplus. Although the other profile limits work fine.

Regards.

> -Mensaje original-
> De:   Tim Onions [SMTP:[EMAIL PROTECTED]]
> Enviado el:martes 6 de marzo de 2001 12:00
> Para: Multiple recipients of list ORACLE-L
> Asunto:   RE: Old INACTIVE and KILLED sessions
>
> Indeed I'd forgotten about them! However, in my experience this will only
> mark the session as being SNIPED - the disconnect will only happen when
> the
> user tries to do somthing after the SNIP has occurred. This can be a pain
> if
> the session is idle due to network failure, application GPF etc. However,
> if
> the problem is down to users simply leaving the application running then
> for
> ages it can work.
>
> I guess it's all down to the nature of the particular problem!
>
> -Original Message-
> Sent: 06 March 2001 09:46
> To: Multiple recipients of list ORACLE-L
>
>
> You can also use profiles to do this. With profiles you can not only
limit
> the amount of time connected, but also the ammount of resources the user
> can
> take.
>
> Before you start playing with these, make sure that you set the init
> parameter:
> RESOURCE_LIMIT = TRUE
>
> Then to create a profile:
>
> create profile {profile_name} limit
> {resource_name) {value}..
> ..
> ..
>
> Examples of resource names are:
>
> CONNECT_TIME - value in minutes
> IDLE_TIME - Again in minutes
> PRIVATE_SGA - Kb or Mb
> CPU_PER_SESSION - limits the CPU time for a session I think in hundreths
> of
> seconds.
>
> so fo your needs you need to do something like
>
> create profile connect_timeout limit
> CONNECT_TIME 1440
> IDLE_TIME 720
>
> This will disconnect active users lasting longer than 24 hours, and all
> idle
> users after they have been idle for 12 hours.
>
> HTH
>
> Mark
>
>
> -Original Message-
> Ghosalkar
> Sent: Tuesday, March 06, 2001 02:05
> To: Multiple recipients of list ORACLE-L
>
>
> Henry,
>
> set the following parameter in the sqlnet.ora on the server and not on
the
> client.
> the unit is minutes. 10 mins shld be enuf. test with ur environment. if
> set
> less then maybe it may hv an impact on SQL*Net performance.
>
>
> SQLNET.EXPIRE_TIME= 10
>
>
> -Mandar
>
> > -Original Message-
> > From: "HENRY, Benoît" [mailto:[EMAIL PROTECTED]]
> > Sent: Monday, March 05, 2001 12:36 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Old INACTIVE and KILLED sessions
> >
> >
> >
> > Hi there,
> >
> >
> > I'm new to Oracle, coming from (gulp!) Informix.
> > I hope to find on this newsgroup a great help from you.
> > (it was the cas

Re: Differences between CTAS and alter table MOVE

2001-03-06 Thread Riyaj_Shamsudeen



Hi
If you have constraints enabled (primary, unique and referential), then
CTAS approach needs reenabling them in the new table. This will take
longer. But alter table does not need to disable or enable the constraints
and hence alter table move would be more preferable..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
johnm9563@net  
   
scape.netTo: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
Sent by: cc:   
   
root@fatcity.Subject: Differences between CTAS and 
alter table MOVE   
com
   
   
   
   
   
03/06/01   
   
07:41 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




HI:

Can anyone the pros and cons of using  the CTAS approach to reorganizing a
table as compared to the alter table move command

Oracle 8.1.5  tables size roughly 8gb

Thanks

__
Get your own FREE, personal Netscape Webmail account today at
http://webmail.netscape.com/
--
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: Drop a column in a 9 million records table

2001-03-05 Thread Riyaj_Shamsudeen



Hi
 When you drop a column the data in that column need to be removed,
which is a change to the database block and hence Oracle has to create undo
for the change. So rollback segment gets filled. You could control this
"commit" behavior using the checkpoint option..

alter table table_name  drop column bar checkpoint 250;

 If the statement fails then you have to use 'alter table foo drop
columns continue' command to continue the operation..
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
CC Harvest 
   
  
hoo.com> cc:   
   
Sent by: Subject: Drop a column in a 9 million 
records table  
root@fatcity.  
   
com
   
   
   
   
   
03/05/01   
   
10:10 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




The database is Oracle8.1.7. I used the syntax:
alter table foo drop column bar;

Why it gives run out of RBS error? Is alter table a
DDL? How does it use RBS?

TIA

Chris Harvest

__
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
--
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).




-- 
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: process parameter

2001-02-27 Thread Riyaj_Shamsudeen

I found one article..

ftp://oss.sgi.com/www/projects/postwait/download/postwait.3.txt

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com

jkstill@cybco  

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

root@fatcity.Subject: RE: process parameter

com

02/27/01   

12:16 PM   

Please 

respond to 

ORACLE-L   

Christian,

Do you know of a good article on the web to explain
the workings of post wait drivers?

Thanks

Jared

On Tue, 27 Feb 2001, Trassens, Christian wrote:

> AIX doesn't use semaphores. You must run the script rootpre.sh to create
the
> /etc/loadext This is because AIX uses post-wait drivers.
>
> Regards.
>
> > -Mensaje original-

--
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: How to find a listener service without running lsnrctl servic

2001-02-21 Thread Riyaj_Shamsudeen



I would create a separate listener for each database and name the listener
accordingly. For e.g. for the orcl database create a listener,
lsnr_orcl_1521 and let your users do ps -ef and grep for the pattern
lsnr_$ORACLE_SID_1521 in their shell script..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com
"Usual disclaimer apply..Use at your risk"



   
 
"Rao, Maheswara"   
 
  
ardp3.com> cc: 
 
Sent by:   Subject: RE: How to find a listener 
service without running  
[EMAIL PROTECTED]   lsnrctl servic  
 
   
 
   
 
02/21/01 08:11 AM  
 
Please respond to  
 
ORACLE-L   
 
   
 
   
 




Hi Djordje,

Thank you for the suggestion.

Doing grep for a specific service on an output produced by lsnrctl
services,
yes, I could get the answer.  However, I am trying to create a shell script
through which the user will find whether a specific listener service is up
or not.  The problem is I do not want to give the password of listener.
Also, by greping the output of lsnrctl --- this output could have been
produced some time back but at the time the user is querying the service
might be down.

Please suggest a way to do the above.

Thanks,

Rao
[EMAIL PROTECTED]

-Original Message-
Sent: Tuesday, February 20, 2001 6:50 PM
To: Multiple recipients of list ORACLE-L

Why not running it from a script, and grep and awk from output wht you need
to see ?

Djordje

- Original Message -
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Sent: Tuesday, February 20, 2001 6:00 PM


> Hi DBAs,
>
> I would like to find out whether a specific listener service is up or not
without running the command ---> lsnrctl services.  Is there any way I can
do this through a shell script or unix command?
>
> My environment : Solaris 2.7 : Oracle 8.0.4
>
> Thanks for your help,
>
> Rao
>
> [EMAIL PROTECTED]
>
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rao, Maheswara
  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: fastscan and maxpgio in Solaris 8?

2001-02-20 Thread Riyaj_Shamsudeen



Hi
 Here is the url:
http://www.sun.com/software/solutions/blueprints/0101/SunOracle.pdf

 kaio returning ENOSETUP is okay ? That really a surprise and going
against few documents. If I understand correctly, kaio does not support I/O
buffer size greater then maxphys or  vol_maxio kernel parameters. If the
I/O buffer size is greater than these values then kaio call returns
ENOSETUP  (errno=28). IO buffer size for the db writes is the product of
db_file_direct_io_count * db_block_size post-oracle8. The default value for
maxphys and vol_maxio is 128k. The default value for
db_file_direct_io_count is 64 and ONLY for 2k db_block_size the kaio calls
should work correctly (out of the box ) [ some task for me to test this
out]. Hence if you want to make kaio calls work correctly, then you have to
either decrease the  db_file_direct_io_count or increase the maxphys and
vol_maxio parameter to match this product OR disable the async IO
completely.
 BTW, in my opinion, the overhead of lwp based IO calls are very high.
I created one tablespace of size 100M in my database to prove this. With
Asynch_IO turned on (that is with lwp processes since we haven't set up
these parameters yet due to other nontechnical reasons..), the tablespace
creation took 9 seconds and with asynch_io turned off it took less than 1
second.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"zabair
   
ahmed"   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
 Subject: Re: fastscan and maxpgio in 
Solaris 8?  
Sent by:   
   
root@fatcity.  
   
com
   
   
   
   
   
02/20/01   
   
02:20 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hi

Where can i get this Sun/Oracle Best Practices paper.

TIA

Zabair Ahmed



>From: "Louis Avrami" <[EMAIL PROTECTED]>
>Reply-To: [EMAIL PROTECTED]
>To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
>Subject: fastscan and maxpgio in Solaris 8?
>Date: Mon, 19 Feb 2001 15:45:57 -0800
>
>
>I have a question concerning some tunables in /etc/system associated
>with priority paging and Solaris 8.
>
>In the Sun/Oracle Best Practices paper, it mentions that, in
>addition to 'set priority_paging=1', that a couple of other tunables
>be defined:
>
>set fastcan=131072
>set maxpgio=65536
>
>Is fastscan and maxpgio still applicable in Solaris 8?
>
>Thanks,
>
>Lou Avrami ( [EMAIL PROTECTED] )
>
>--
>
>  From: David Miller <[EMAIL PROTECTED]>
>  Date: Thu, 25 Jan 2001 17:10:01 -0600 (CST)
>  Subject: RE: New Sun/Oracle Blueprint available
>
>Hi Ross,
>
>I forwarded your comments on to the author, Bob Sneed, who replies
>below.
>
>Dave Miller
>Sun Microsystems, Inc.
>
> >
> >- Begin Forwarded Message -
> >
> >Date: Thu, 25 Jan 2001 09:58:14 -0800
> >To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> >X-Comment: Oracle RDBMS Community Forum
> >X-Sender: "Mohan, Ross" <[EMAIL PROTECTED]>
> >From: "Mohan, Ross" <[EMAIL PROTECTED]>
> >Subject: RE: New Sun/Oracle Blueprint available
> >X-ListServer: v1.0f, build 69; ListGuru (c) 1996-2000 Bruce
>A. Bergman
> >Mime-Version: 1.0
> >
> >Dave,
> >
> >The doc looks useful, so thanks for posting this URL. There
>is plenty
>in the
> >document to recommend it.
> >
> >
> >But, here is this gem from the document itself:
> >
#
>
> >"An AIO problem has often been perceived when truss is used
>to o

Re: lampert server

2001-02-19 Thread Riyaj_Shamsudeen



Yes. This is harmless. Lamport scheme is used for SCN generation.
Particularly applicable to OPS databases since the SCN has to be genarated
across all the instances uniquely. Conceptually, determines how the recent
SCN will be propogated between the instances.

Refer to OPS concept guide, if you need detailed information..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"orclbabu" 
   
  
mail.com>cc:   
   
Sent by: Subject: lampert server   
   
root@fatcity.  
   
com
   
   
   
   
   
02/19/01   
   
10:21 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hi List

I recently got this message in my alert log : Picked Lapert Server scheme
to generate SCNs.

I guess its harmless and more of a information only entry. But does anyone
know more about it?

Babu



-- 
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: Corrupt block - Bad header found during buffer read

2001-02-16 Thread Riyaj_Shamsudeen



Hi
 I am not sure why this corruption occurred. If you look at the
consistent value in tail, it is matching with the block header, meaning the
consistency value in tail is 0x96b20602 which is a combination of lower 4
bytes of the last change scn + format + type. This is just one part of the
consistency check and there could be someother problems.
 Please find out what segment these blocks belong to. Also, it may be
worthwhile to check the OS logs for any hardware problem.  Let us know what
you find. We will go from there. Also, dump the blocks using these commands

 alter system dump datafile 60 block 628658;
 alter system dump datafile 61 block 323407;

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"Antonio   
   
Idone"   To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
 Subject: Corrupt block - Bad header found 
during buffer read 
Sent by:   
   
root@fatcity.  
   
com
   
   
   
   
   
02/16/01   
   
09:05 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hi all,

help me

i have the problem with my database  ver. 8.1.5 64 bit

on HP 9000 (datawarehouse with parallel query)  i.e:

  Current log# 5 seq# 10474 mem# 0: /u2ddsp/oradata/ddsp/redo0301new.rdo
  Current log# 5 seq# 10474 mem# 1: /u2ddsp/oradata/ddsp/redo0302new.rdo
***
Corrupt block relative dba: 0x0f0997b2 file=60. blocknum=628658.
Bad header found during buffer read
Data in bad block - type:6. format:2. rdba:0x0f021c8b
last change scn:0x018b.bc5096b2 seq:0x2 flg:0x00
consistancy value in tail 0x96b20602
check value in block header: 0x0, check value not calculated
spare1:0x0, spare2:0x0, spare2:0x0
***
Corrupt block relative dba: 0x0f44ef4f file=61. blocknum=323407.
Bad header found during buffer read
Data in bad block - type:6. format:2. rdba:0x0f41b50d
last change scn:0x018b.bc5096b5 seq:0x2 flg:0x00
consistancy value in tail 0x96b50602
check value in block header: 0x0, check value not calculated
spare1:0x0, spare2:0x0, spare2:0x0

Thank
Antonio
___
Visit http://www.visto.com/info, your free web-based communications center.
Visto.com. Life on the Dot.

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Antonio Idone
  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: Off Topic: Message for you

2001-02-15 Thread Riyaj_Shamsudeen



Message for magesh:


SELECT TRANSLATE(')81$!.;%-', '1234567890!@#$%^&*()-=_+;,.',
 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ' )
 from dual;

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
 
"Rao, Maheswara"   
 
  
ardp3.com> cc: 
 
Sent by:   Subject: RE: Off Topic: Message for 
you  
[EMAIL PROTECTED]   
 
   
 
   
 
02/15/01 08:00 AM  
 
Please respond to  
 
ORACLE-L   
 
   
 
   
 




Great and Very nice!!!





Rao



[EMAIL PROTECTED]





 -Original Message-
 From: magesh [mailto:[EMAIL PROTECTED]]
 Sent: Wednesday, February 14, 2001 9:10 PM
 To: Multiple recipients of list ORACLE-L
 Subject: Off Topic: Message for you








 Dear All





 Execute the statement on a SEQUEL prompt and see the message for you.


 SELECT TRANSLATE('_9(8.-.=5*;.81^^;.=1@5$)9$5.41;',
 '1234567890!@#$%^&*()-=_+;,.',
 'ABCDEFGHIJKLMNOPQRSTUVWXYZ ' )
 from dual


 Regards


 **Magesh
















-- 
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: Urgent! Change Character Set from WE8IS088591 to UTF8

2001-02-14 Thread Riyaj_Shamsudeen



In my opinion, it is not a good idea to change from WE8ISO8859P1 to UTF8.
Even though, there are two methods (updating in props$ table in pre-oracle8
and using alter database in after 8) available to change the characterset,
for these two methods to work correctly without introducing corruption,
there are two pre-existing conditions.

 1. The characterset 'from' should be a subset of the 'to'
characterset.
 2. Not just subset, also a byte by byte compatible subset. Meaning say
if A is represented as hex value 65 in the 'from' characterset then 65
should be represented as 'A' in 'to' characterset too.

 In reality, as far as I know, going from ASCII to WE8ISO8859P? is the only
one that will satisfy this.

 Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"Usual disclaimers apply"


   
   
Jorma.Vuorio@  
   
nokia.comTo: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
Sent by: cc:   
   
root@fatcity.Subject: RE: Urgent! Change Character Set 
from WE8IS088591 to
com  UTF8  
   
   
   
   
   
02/14/01   
   
06:00 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




How about:

SVRMGR> startup mount
SVRMGR> alter system ENABLE RESTRICTED SESSION;
Statement processed.
SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
Statement processed.
SVRMGR> ALTER DATABASE OPEN;
Statement processed.
SVRMGR> ALTER DATABASE CHARACTER SET ;
Statement processed
SVRMGR> ALTER DATABASE NATIONAL CHARACTER SET **;
Statement processed.
SVRMGR> shutdown immediate
SVRMGR> startup

Answer from this list some time ago :)

Br.
Jorma

> -Original Message-
> From: ext Martin Kendall [mailto:[EMAIL PROTECTED]]
> Sent: 14 February, 2001 13:21
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Urgent! Change Character Set from WE8IS088591 to UTF8
>
>
> DO NOT do this in 8i as you will not be able to restart the database.
>
> Martin
>
> -Original Message-
> Sent: 14 February 2001 10:31
> To: Multiple recipients of list ORACLE-L
>
>
> i once hear someone said that:
> update sys.props$ set value='utf8' where name ='NLS_characterset';
> and it seems it do work,but not konw whether it is legal,and
> no experience
> in production database.
> - Original Message -
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Wednesday, February 14, 2001 7:40 AM
>
>
> > I have a 8.1.6.0.0 database.
> >
> > What is the best way to change Character Set from
> WE8ISO8859P1 to UTF8
> >
> >  from:
> > NLS_CHARACTERSET WE8ISO8859P1
> >
> > To:
> > NLS_CHARACTERSET UTF8
> >
> > Thanks
> >
> > Larry
> >
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Larry Taylor
> >   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).
> >Ws±ëzØ^¡÷âr&¥9,BÅm¶YÿÃ
> (§Ú©Ê&ëa¢·!jSbz
> <á¹ÈZ¢(tm).ÛçzÑZ´(È×ÂSäIêï?Ç¬óY9ßÎtçQ@_Î|ç9Ó?Rjpâz
> jXY¢¹âhû>-'z×«?ëZqÇ¬³óSX§EUR¸¬¶ÄèDCTL¨º»*÷ë¢kaSÉsSX§'X¬¶Ç
> §u©Ä1¨¥(tm)ë,j
>  ¸¬´k«¹ör+rr?§¢×"\"²-¥-)à¡òâ²Ñ®®æ§v)í...éz²Æ
> xfb)Ü-ç^jX§yÊ'µ¨§Sx5%

Re: What are the Commonly Used SGA Sizes on VLDBs ?

2001-02-14 Thread Riyaj_Shamsudeen



Hi
There is a 4GB virtual memory limit / process in 32 bit unix systems.
Oracle attaches the shared memory segments at 0x8000 by default and
grows upwards. Since the dlopen calls attaches at 0xEF00, the limit is
around 1.73 GB. But you could change the ksms.s module sgabeg variable and
ask Oracle to attach at say 0x100 and which would give you around
3.7GB. It is pretty simple. Search metalink for the details steps.
Basically, you edit the ksms.s file change the sgabeg variable value to
0x1000 and recompile all the objects.

Limitation is because in 32 bit OS the limit is 4GB. If you want SGA
size beyound this, then you have to go to 64 bit, where the size is
virtually limitless.
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"Usual disclaimers apply"


   
   
"Gene Sais"
   
<[EMAIL PROTECTED]To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
ach.fl.us>   cc:   
   
Sent by: Subject: Re: What are the Commonly 
Used SGA Sizes on VLDBs ? 
[EMAIL PROTECTED]   
   
   
   
   
   
02/14/01 06:55 AM  
   
Please respond to  
   
ORACLE-L   
   
   
   
   
   






+++
Gene Sais
Senior Oracle DBA/Systems Admin
http://www.pbcountyclerk.com
[EMAIL PROTECTED]
561.355.6579  [phone]
561.355.2600  [fax]
+++

>>> [EMAIL PROTECTED] 02/14 5:05 AM >>>

What are the Commonly Used SGA Sizes on  VLDBs ?
Answers below:

On SUN E6500 (4u)  model , Solaris 2.6 , we are Unable to increase the SGA
Beyond 1.7 GB on Oracle 7.3.4.5

-->Max size on 32 bit OS and oracle, 2.6 is 32 bit

Qs. What are the ways of increasing the SGA beyond this Limit ?
--> Upgrade your OS and Oracle to 8i

Qs. What are the Negative Implications of of RE-Creating the Oracle Exe
With
a Lowered Base Address to Allow Bigger SGA ?
--> Good luck on this one

NOTE - This may Give us a Only a Few Hundred MB . What if we Need MORE ?

Qs. Would this Limitaition be on Oracle 8i too ?
It is a limitation on 32bit vs 64bit oracle and os

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




-- 
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 ORACL

Re: listner.log cannot be generated



Hi
I think, you removed the listener log file while the listener is up and
running. When you removed the listener log, listener was having a file
pointer open. Since the file pointer is not closed, the listener is still
writing to the same node.

If you stop and start the listener, you would see the log file.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"These are my opinions and usual disclaimers apply"


   
   
[EMAIL PROTECTED]   
   
Sent by: To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
root@fatcity.cc:   
   
com  Subject: listner.log cannot be generated  
   
   
   
   
   
02/13/01   
   
02:29 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   






Hi list,

About a month ago, I moved and compressed listener.log file to a different
directory because
this file accumulated to a huge size over past year or two.

Since that point, nothing is able to write to listenr.log file even though
i did
 not change an writing permission
of this file and its parent directory. The file size is keeping 0 over one
month
 period. I do not know how to make
this file writeable.

I issued 'lsnrctl status command' to check the destination of this file:

Listener Parameter File
/u01/app/oracle/product/8.0.5/network/admin/listener.ora
Listener Log File
/u01/app/oracle/product/8.0.5/network/log/listener.log
Services Summary...
  extproc   has 1 service handler(s)
  idms  has 1 service handler(s)
  orac  has 3 service handler(s)
  tsp   has 1 service handler(s)
  vign  has 2 service handler(s)
The command completed successfully

Then I go to corresponding directory and find the size of this file is 0.


nnidb1:/export/home/oracle@orac> cd
/u01/app/oracle/product/8.0.5/network/log
nnidb1:/u01/app/oracle/product/8.0.5/network/log@orac> ls -laF
total 4488
drwxr-xr-x   4 oracle   dba  512 Jan 23 09:51 ./
drwxr-xr-x  16 oracle   dba  512 May 13  1999 ../
drwxr-xr-x   2 oracle   dba  512 Jan  5 10:09 2000/
drwxr-xr-x   2 oracle   dba  512 Jan  5 09:57 2001/
-rw-rw-rw-   1 oracle   dba0 Jan  5 14:24 listener.log
-rw-rw-rw-   1 oracle   dba  2278569 Jan 23 09:55 sqlnet.log


Will someone in the list enlight me?

Thanks

Eveleen


--
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: db_block_checkpoint_batch



Hi elain
 I have not personally encountered performance degradation setting this
to high value. But I will attempt to help you.
 DBWR process is waiting for the IPC messages from the background
process or from the server processes, for the requests to write the dirty
buffers. That's how the processes communicate among themselves and wiating
for an IPC message is normal. Also DBWR is the process which scans the
buffer cache to create a list of buffers to write and then it distributes
that list to the slave process to write. So slave process has to wait for
the null event during no work. So, this is fine too.
 db_block_checkpoint_batch determines what portion of db writes can be
used for the slow checkpoints (in 7.3). By setting this to high value, the
checkpoint will complete faster, but the speed at which DBWR cleans the
buffer cache may be slightly slower. So more free buffer waits. You may
have to play around these parameters little bit to determine the optimum
value for your environment. I would set checkpoint batch size to be around
1/8 th or 1/10th of Internal batch write size and go upwards. Also make
sure that your log buffer is small enough and the DBW doesn't wait too much
for the  log file sync event. Converting the file system based database to
raw database and turning on async IO are other options.
 In fact, Steve Adams has written a paper about this DBWR tuning.
Search for db_block_checkpoint in his website www.ixora.com.au. You will
find his paper.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not legally bind me or my employer. Use at
your own risk"


   
   
"elain he" 
   
  
ail.com> cc:   
   
Sent by: Subject: db_block_checkpoint_batch
   
root@fatcity.  
   
com
   
   
   
   
   
02/13/01   
   
08:26 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hi,
Has anyone seen database performance degradation by setting this to a high
value?

We set this value to 128 from the default value 4 and noticed a lot more
free buffer waits event.

All database writers (DB01-10) are waiting for Null Event. The
seconds_in_wait time from v$session_wait is 129614(~1.5days), ie the same
time since the database started. The parent DBWR process is waiting on
rdbms
ipc message and the seconds_in_wait time is 0.

Here's the configuration:
DB 7.3.4
db_writer=10
async_io disable
Solaris 2.6/Sun T3 Array
_db_block_write_batch=512
db_block_checkpoint_batch=128 (512/4)

Appreciate any help you can provide.
Thanks.

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

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

Re: No recovery situation



Hi
How did you find you have corrupt blocks ? Did you have any ORA-1578
errors ? Any alert log messages  for the same ?
I would first find out which objects exist in that particular corrupted
block (or ) alternatively you could analyze all the objects in the system
tablespace like this.
set pages 0
spool /tmp/exec_as.sql
select 'analyze table '||owner||'.'||table_name||' validate structure
cascade;'
from dba_tables
where tablespace_name='SYSTEM'
/
spool off
spool /tmp/exec_as1.out
@/tmp/exec_as.sql
spool off
spool /tmp/exec_as.sql
select 'analyze cluster '||owner||'.'||cluster_name||' validate structure
cascad
e;'
from dba_clusters
where tablespace_name='SYSTEM'
/
spool off
spool /tmp/exec_as2.out
@/tmp/exec_as.sql
spool off

If the corrupted object is an index, depending upon what index is
corrupted, you could potentially drop and recreate the index..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
Beatriz
   
Martinez To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
Jimenez  cc:   
   
  
   
Sent by:   
   
root@fatcity.  
   
com
   
   
   
   
   
02/13/01   
   
06:30 AM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Hello list,
I know that the answer to my question it´s going to be a negative one,
but I have to try to do all that I can do.
Until yesterday I was doing some attempt with an Oracle installation,
without a backup copy. And, unfortunately, I had to format  my computer
(!!!).
Now I have the data associated with this installation,  I could save the
datafiles (*dbf). The database was in a non archivelog mode, and what is
the worst thing of all, it had some corrupt blocks in the system.dbf
file.
Could I do something with them, or are they totally unworhty?
Could anybody give me an advice?
Maybe it would be easier for me to start from the beginning again
A lot of thanks,

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Beatriz Martinez Jimenez
  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: Off Topic: Row Locking - Row Id



Hi Johan
 Will this work ?

 Person A books the product he wants with 'select * from t1 for
update'. At this point, he has lock on the row.
 Person B comes in, looks at the product and does a 'select * from t1
for update nowait'
   If the row is locked for update then person B will get ORA-0054. If
Person B gets an ORA-0054 then, you know that the person A has booked the
product but not bought yet.

The locks hold by the person A will be cleaned by the pmon if the process
dies away.

 But the above will only work if each of the web user gets its own database
connections. I don't know about your environment, but in most environment,
the connection to the database is shared among the web sessions. If that is
the case, then you may have to use the flags to track the state changes.

 If you want to show only rows that are locked then you could use this
undocumented feature:
 'select * from t1 skip locked'. This will skip all the rows that have
been locked.


Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"These are my opinions and does not bind my employer. Use at your risk"



   
   
"Johan 
   
Locke@i-CommeTo: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
rce Services"cc:   
   

rce Services"cc:


Sent by: cc:

root@fatcity.Subject: Deadlock
Interpretation Assistance Requested
com





02/10/01

07:00 PM

Please

respond to

ORACLE-L









Listers,

HP-UX 11.0, V7.3.4.3. Deadlock trace file snippet:

SELECT *   FROM UNIT_STATISTICS  WHERE UNIT_ID = :b1  AND MONTH = :b2  AND
YEAR = :b3  AND RANK_CODE = :b4 FOR UPDATE OF QUANTITY

Deadlock graph:
   -Blocker(s)  -Waiter(s)
--
---
Resource Name  process session holds waits  process session holds
waits
TX-00180008-42d6   837 635 X784 481
S
TX-00160010-4412   784 481 X837 635
X
Rows waited on:
Session 481: no row
Session 635: obj - rowid = 0722 - 0289.0033.0102

I've never really encountered all that many deadlocks before. The ones I
*have* seen in the past were the "classic" TX locks where user A has a row
locked that user B needs and vice versa and the mode requested was X. On
Friday, the DBA's sent me a trace file from a deadlock (with the info above
from that trace file) and asked me to investigate. The deadlocks they had
seen in the past were due to application coding issues, hence their tossing
this to the development side of the house.

After a lot of research on Metalink, the Steve Adams site
(http://www.ixora.com.au), and Usenet archive searches (www.deja.com), the
S
mode wait for session 481 (and no row) makes me think this isn't the
typical
application induced deadlock due to the way and order in which locks are
acquired.

There are 3 foreign keys on the table, and, each of them are indexed. There
is no bitmap index. PCT_FREE is 10 and PCT_USED is 40. I don't really know
all that much about how heavily DML is issued against the table. But, after
reading material on when the wait is in S mode, I wonder if this might be
an
ITL issue. From what I've read the past 2 days, there could be other
reasons
for the S mode wait, but, waits for Unique/PK enforcement, insufficient ITL
slots, and bitmap index were the most common reasons mentioned. Because the
statement reported was a SELECT FOR UPDATE, I've eliminated (correctly?)
the
check for uniqueness wait during inserts, and, with no bitmap index on the
table, that leaves the ITL slots as the main candidate.

What I need to do is determine if this is indeed an application coding
issue, or, if I need to kick this back to the DBA's and let them research
it. And I don't mean that in a finger pointing way. The DBA's and
developers
there work well together. From what I've read and learned so far, this
deadlock doesn't seem to be an application coding issue. I am thinking
about
saying that and asking them (if they haven't already) to open a TAR and
provide the trace file to Oracle Support.

If anyone has any comments or suggestions, I would appreciate hearing them
(because if this could still be due to an application coding issue, more
research needs to be done on the development and/or my side of the house).

Regards,

Larry G. Elkins
[EMAIL PROTECTED]

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

Fat City Network Services

Re: Consistent Gets?

2001-02-12 Thread Riyaj_Shamsudeen



Hi
   This may be due to commit cleanout mechanism. After populating the
table, your commit simply marks the transaction as completed in the
rollback segment header and does not clean the rows in the block. So the
flags in the row header portion of the block indicates that the transaction
is open and active. When you do a select on those rows Oracle sees that the
transaction is open and goes to the rollback segment header to check the
status of the transaction, and then marks the row headers to committed
state.
When you do the select second time, since the row headers indicates the
commit status, the session doesn't need to do that much work to get the
consistent data.
   To verify this behavior, do the first select again and you could see
comparable consistent gets.

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not bind my employer. Use at your own risk"



   
 
Paul Parker
 
  
yahoo.com> cc: 
 
Sent by:   Subject: Consistent Gets?   
 
[EMAIL PROTECTED]
 
m  
 
   
 
   
 
02/12/01 11:30 
 
AM 
 
Please respond 
 
to ORACLE-L
 
   
 
   
 




Hi all,

Could someone attempt to explain the difference
in the no. of "consistent gets" reported for
these 2 queries?

I have a table (TEST1) made up of 11,333 blocks.
No indexes on this table.  I run two queries,
both reported to do full table scans (as
expected), one returning all the rows from the
table and one with a bogus condition resulting in
no rows returned.  I expected, that since both
queries did full table scans, that the amount of
IO would be the same.  Yet the query which
returned data did 3 times as much IO as the one
which did not.  Output follows :


12:08:16 T10-SERVCBO-CH> @p2
12:08:22 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:08:22 T10-SERVCBO-CH> select
12:08:22   2  *
12:08:22   3  from
12:08:22   4  test1
12:08:22   5  where
12:08:22   6  pay_dealer_date >= '01/01/2000'
12:08:22   7  -- and state_code = 'AB' 
BOGUS CONDITION
12:08:22   8  ;

375043 rows selected.

Elapsed: 00:00:55.46

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
   10   TABLE ACCESS (FULL) OF 'TEST1'

Statistics
--
  0  recursive calls
 15  db block gets
  35581  consistent gets
  10575  physical reads
  0  redo size
   66817080  bytes sent via SQL*Net to client
2775646  bytes received via SQL*Net from
client
  25004  SQL*Net roundtrips to/from client
  0  sorts (memory)
  0  sorts (disk)
 375043  rows processed


12:09:18 T10-SERVCBO-CH> ed p2

12:09:33 T10-SERVCBO-CH> @p2
12:09:35 T10-SERVCBO-CH> set autotrace traceonly
exp stat
12:09:35 T10-SERVCBO-CH> select
12:09:35   2  *
12:09:35   3  from
12:09:35   4  test1
12:09:35   5  where
12:09:35   6  pay_dealer_date >= '01/01/2000'
12:09:35   7  and state_code = 'AB'   BOGUS
CONDITION
12:09:35   8  ;

no rows selected

Elapsed: 00:00:03.43

Execution Plan
--
   0  SELECT STATEMENT Optimizer=CHOOSE
   10   TABLE ACCESS (FULL) OF 'TEST1'



Statistics
--
  0  recursive calls
 15  db block gets
  11337  consistent gets
  10573  physical reads
  0  redo size
   1860  bytes sent via SQL*Net to client
313  bytes received via SQL*Net from
client
  1  SQL*Net roundtrips to/from client

Re: Off Topic: Row Locking - Row Id

2001-02-12 Thread Riyaj_Shamsudeen



Hi
You could get the following columns from the v$session table and then
use dbms_rowid.rowid_create to construct the rowid:

 ROW_WAIT_OBJ#NUMBER
 ROW_WAIT_FILE#   NUMBER
 ROW_WAIT_BLOCK#  NUMBER
 ROW_WAIT_ROW#NUMBER

   Session that is waiting will have this information in its v$session view
and the session holding will have -1 in the row_wait_obj#.
ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
 ------
 -
 926325 13
   1
-1 00
0

Hope this helps!!

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"This is my opinion and does not bind my employer. Use at your own risk"



   
   
"Johan 
   
Locke@i-CommeTo: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
rce Services"cc:   
   

Sent by: cc:

root@fatcity.Subject: Deadlock
Interpretation Assistance Requested
com





02/10/01

07:00 PM

Please

respond to

ORACLE-L









Listers,

HP-UX 11.0, V7.3.4.3. Deadlock trace file snippet:

SELECT *   FROM UNIT_STATISTICS  WHERE UNIT_ID = :b1  AND MONTH = :b2  AND
YEAR = :b3  AND RANK_CODE = :b4 FOR UPDATE OF QUANTITY

Deadlock graph:
   -Blocker(s)  -Waiter(s)
--
---
Resource Name  process session holds waits  process session holds
waits
TX-00180008-42d6   837 635 X784 481
S
TX-00160010-4412   784 481 X837 635
X
Rows waited on:
Session 481: no row
Session 635: obj - rowid = 0722 - 0289.0033.0102

I've never really encountered all that many deadlocks before. The ones I
*have* seen in the past were the "classic" TX locks where user A has a row
locked that user B needs and vice versa and the mode requested was X. On
Friday, the DBA's sent me a trace file from a deadlock (with the info above
from that trace file) and asked me to investigate. The deadlocks they had
seen in the past were due to application coding issues, hence their tossing
this to the development side of the house.

After a lot of research on Metalink, the Steve Adams site
(http://www.ixora.com.au), and Usenet archive searches (www.deja.com), the
S
mode wait for session 481 (and no row) makes me think this isn't the
typical
application induced deadlock due to the way and order in which locks are
acquired.

There are 3 foreign keys on the table, and, each of them are indexed. There
is no bitmap index. PCT_FREE is 10 and PCT_USED is 40. I don't really know
all that much about how heavily DML is issued against the table. But, after
reading material on when the wait is in S mode, I wonder if this might be
an
ITL issue. From what I've read the past 2 days, there could be other
reasons
for the S mode wait, but, waits for Unique/PK enforcement, insufficient ITL
slots, and bitmap index were the most common reasons mentioned. Because the
statement reported was a SELECT FOR UPDATE, I've eliminated (correctly?)
the
check for uniqueness wait during inserts, and, with no bitmap index on the
table, that leaves the ITL slots as the main candidate.

What I need to do is determine if this is indeed an application coding
issue, or, if I need to kick this back to the DBA's and let them research
it. And I don't mean that in a finger pointing way. The DBA's and
developers
there work well together. From what I've read and learned so far, this
deadlock doesn't seem to be an application coding issue. I am thinking
about
saying that and asking them (if they haven't already) to open a TAR and
provide the trace file to Oracle Support.

If anyone has any comments or suggestions, I would appreciate hearing them
(because if this could still be due to an application coding issue, more
research needs to be done on the development and/or my side of the house).

Regards,

Larry G. Elkins
[EMAIL PROTECTED]

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

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

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

Re: Deadlock Interpretation Assistance Requested

2001-02-11 Thread Riyaj_Shamsudeen



Hi
In my opinion, this is an ITL issue. When a process need an  ITL and
have to wait for it , then it pseudo randomly selects a locked row (from
that block)  and enqueues itself in to the waiters queue. But the
row_waited information in v$session will be null. In rare cases, it is
possible for the deadlock to occur if the ITL waiter holds the row that is
needed by the other process.
I would ask, what is the frequency of this deadlock ? Is this the first
occurrence ? If it is the first occurrence, then I would wait for the next
occurrence and then spend time and resource.
   Hope this helps!!
Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"These are my opinions and does not bind my employer. Use at your risk"



   
   
elkinsl@flash  
   
.net To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
Sent by: cc:   
   
root@fatcity.Subject: Deadlock Interpretation 
Assistance Requested
com
   
   
   
   
   
02/10/01   
   
07:00 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Listers,

HP-UX 11.0, V7.3.4.3. Deadlock trace file snippet:

SELECT *   FROM UNIT_STATISTICS  WHERE UNIT_ID = :b1  AND MONTH = :b2  AND
YEAR = :b3  AND RANK_CODE = :b4 FOR UPDATE OF QUANTITY

Deadlock graph:
   -Blocker(s)  -Waiter(s)
--
---
Resource Name  process session holds waits  process session holds
waits
TX-00180008-42d6   837 635 X784 481
S
TX-00160010-4412   784 481 X837 635
X
Rows waited on:
Session 481: no row
Session 635: obj - rowid = 0722 - 0289.0033.0102

I've never really encountered all that many deadlocks before. The ones I
*have* seen in the past were the "classic" TX locks where user A has a row
locked that user B needs and vice versa and the mode requested was X. On
Friday, the DBA's sent me a trace file from a deadlock (with the info above
from that trace file) and asked me to investigate. The deadlocks they had
seen in the past were due to application coding issues, hence their tossing
this to the development side of the house.

After a lot of research on Metalink, the Steve Adams site
(http://www.ixora.com.au), and Usenet archive searches (www.deja.com), the
S
mode wait for session 481 (and no row) makes me think this isn't the
typical
application induced deadlock due to the way and order in which locks are
acquired.

There are 3 foreign keys on the table, and, each of them are indexed. There
is no bitmap index. PCT_FREE is 10 and PCT_USED is 40. I don't really know
all that much about how heavily DML is issued against the table. But, after
reading material on when the wait is in S mode, I wonder if this might be
an
ITL issue. From what I've read the past 2 days, there could be other
reasons
for the S mode wait, but, waits for Unique/PK enforcement, insufficient ITL
slots, and bitmap index were the most common reasons mentioned. Because the
statement reported was a SELECT FOR UPDATE, I've eliminated (correctly?)
the
check for uniqueness wait during inserts, and, with no bitmap index on the
table, that leaves the ITL slots as the main candidate.

What I need to do is determine if this is indeed an application coding
issue, or, if I need to kick this back to the DBA's and let them research
it. And I don't mean that in a finger pointing way. The DBA's and
developers
there work well together. From what I've read and learned so far, this
deadlock doesn't seem to be an application coding issue. I am thinking
about
saying that and asking them (if they haven't already) to open a TAR and
prov

Re: Priority Paging on Solaris



Hi
   I have enabled the priority paging on Solaris in my earlier job.
priority paging is particularly effective for the file system based
databases. The system performance used to take a nose dive during hot
backup and enabling and playing around cachefree parameter helped us to
resolve the problem. But I don't have any real number for this..

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
"Steve Orr"
   
<[EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L 
<[EMAIL PROTECTED]>  
om>  cc:   
   
Sent by: Subject: Priority Paging on Solaris   
   
root@fatcity.  
   
com
   
   
   
   
   
02/09/01   
   
03:45 PM   
   
Please 
   
respond to 
   
ORACLE-L   
   
   
   
   
   




Has anyone enabled priority paging on Solaris and did you experience any
performance gains as a result? (10-300% performance gains possible
according
to Sun.) For a description here's a link:

http://www.sun.com/sun-on-net/performance/priority_paging.html

Comments? Recommendations?


Steve Orr

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Steve Orr
  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: TU Enqueue types



>From the metalink...Even the reference guide, does not mention this
enqueue.. Document bug.
   
   
   JI "Enqueue used during 
AJV
   snapshot refresh"   
   
   The JI lock was 
introduced in  
   Oracle8.1   
   
   
   
   How Many Resources: One 
per
   snapshot
   
   
   
   How Many Locks: One per 
   
   snapshot
   
   
   
   When & How Used: Used 
to   
   serialize snapshot 
refresh for 
   a snapshot / mview. 
   
   
   
   
   

TC seems to be an enqueue related with PQO.


Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
i2 technologies   www.i2.com


   
   
[EMAIL PROTECTED]  
   
ms.osd.mil   To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
Sent by: cc:   
   
[EMAIL PROTECTED] Subject: RE: TU Enqueue types 
   
   
   
   
   
02/09/01 12:57 PM  
   
Please respond to  
   
ORACLE-L   
   
   
   
   
   





 TX is transaction lock.  Can't find any info on the
other
2...

 --Scott Shafer
   San Antonio, TX


>  -Original Message-
>  From:  Hagedorn, Linda [SMTP:[EMAIL PROTECTED]]
>  Sent:  Thursday, February 08, 2001 10:36 PM
>  To:   Multiple recipients of list ORACLE-L
>  Subject:   Enqueue types
>
>  Does anyone recognize enqueues JI, TC, and TX?  (Steve, are you
on?)
>
>
>  SELECT *
>  FROM x$ksqst
> WHERE ksqstget > 0;
>
>
>  EVENT
> TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED AVERAGE_WAIT
>  
> --- -- --- 
>  enqueue
> 144378  7871825112607   173.936521
>
>
>  ADDR   INDXINST_ID KS   KSQSTGET   KSQSTWAT
>   -- -- -- -- --
>  000399F11808 69  1 CF 179654 39
>  000399F11820 72  1 CI  65152  0
>  000399F11880 84  1 CU1043538 55
>  000399F11938107  1 DL444  0
>  000399F11968113  1 DR   5569  0
>  000399F11988117  1 DV  46432  0
>  000399F11998119  1 DX  95350  0
>  000399F11B70178  1 FS  1  0
>  000399F11D90246  1 HW  98917  0
>

Re: !!**** Urgent HELP (Table Deleted In Production )



Hi Saroj
 If the table has been dropped or truncated then you have to do media
recovery which means that you have to:
 1. restore the backup
 2. Do the point-in-time recovery and then stop the recovery just
before the table deletes.
 3. Open the database using resetlogs.
This also means that you will lose all the transaction occurred after
the table deletes.
 If you can not afford to lose the transaction, then
 1. you could restore the system, rollback and the datafile in which
the table has extents to a DIFFERENT server and
 2. recover the database just before the table deletes,
 3. open the database using resetlogs
 4. export the table from this database to the actual production
database..

   If the database is in the backup mode then the Media recovery becomes
slightly complicated and you need to switchoff the backup mode just before
opening the database using alter database datafile 'file name' end backup
command.

Hope this helps!!

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA
"These are my comments and does not bind me or my employer. Use at your own
risk"



   
  
"Dash, Saroj   
  
(CAP,CEF)"  To: Multiple recipients of list 
ORACLE-L <[EMAIL PROTECTED]>  
  Subject: !! Urgent HELP (Table 
Deleted In Production )   
Sent by:   
  
[EMAIL PROTECTED]   
  
   
  
   
  
02/09/01 11:06 
  
AM 
  
Please respond 
  
to ORACLE-L
  
   
  
   
  




Dear Friends
In product databace one of the developer had deleted a table and commited
it
,
database is in online backup but no export backup or table backup
Please tell me how can the table be recovered
please
guide me the exact detaied steps torecover the table
After getting the reply i will start it

Thanks and Best regards

I tried this command
recover database until time '2001-02-09:22:00:00' ;

it gived the following error
 recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '/db/oracle/oradata/tlisuser/system01.dbf'
SVRMGR> recover database
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '/db/oracle/oradata/tlisuser/system01.dbf'
SVRMGR> recover database until time '2001-02-09:22:00:00' ;
ORA-00283: recovery session canceled due to errors
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '/db/oracle/oradata/tlisuser/system01.dbf'
SVRMGR> select * from v$backup ;

then again i shut down the database then up it again i tried but same
errors


Plese reply soon
saroj dash




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Dash, Saroj  (CAP,CEF)
  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: [EMAI

RE: ORA-04020: deadlock detected while trying to lock object...