Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: RE: 8.1.7 slow on AIX 4.3.3

Re: RE: 8.1.7 slow on AIX 4.3.3

From: Cyril Thankappan <cyril_thank_at_rediffmail.com>
Date: 18 Jan 2001 19:26:07 -0000
Message-Id: <10745.127059@fatcity.com> 





Hi!



I agree with Gogala!







 From 7.3.4 to 8i is an absolute 
  


In 7.3.4 we did with 10-15M SGA


in 8i we had to 'maintain' minimum of 50M SGA
(the shared_pool_size was increased


 other things kept same)


to get 'back' 7.3.4 performance!!!!



I am very curious to know if 


u r also moving from RULE based optimiser (in 7.3.4)
to cost based optimiser (in 8.1.7)..



Please let me know how u 'finally' solved it
Thanks


------------- Original Message --------------
"Gogala, Mladen" <MGogala_at_oxhp.com> wrote:
To:Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
From:"Gogala, Mladen" <MGogala_at_oxhp.com>
Date:Thu, 18 Jan 2001 07:56:46 -0800


Subject:RE: 8.1.7 slow on AIX 4.3.3



    
  
  1.   See if you have oracle_trace parameter enabled. 
  
  2.   Check your network setup.
  
  3.   See whether the machine is thrashing or not.
  
  4.   Enlist a good SA to help you out.






-----Original Message-----


Sent: Thursday, January 18, 2001 9:47 AM
To: Multiple recipients of list ORACLE-L




I am currently testing an 8.1.7 database(enterprise edition) on AIX 4.3.3.
It's really slow (it can take up to 15 seconds to connect) compared to the
same 7.3.4.5 database.  Do you have any ideas about what could be the cause
of it ?  Any new init parameter that I should have specified ?  Any old
init parameter that I should have modified ?




Louis Brouillette


Analyste en informatique (DBA)


Universite du Quebec a Trois-Rivieres


Tel: (819) 376-5011 ext. 2435


Email: Louis_Brouillette_at_uqtr.uquebec.ca


-- 



Please see the official ORACLE-L FAQ: http://www.orafaq.com


-- 



Author: Louis BROUILLETTE


  INET: Louis_Brouillette_at_UQTR.UQuebec.CA


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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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: Gogala, Mladen


  INET: MGogala_at_oxhp.com


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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).





Chat with your friends as soon as they come online. Get Rediff Bol at
http://bol.rediff.com











 From: "MacGregor, Ian A." <ian_at_SLAC.Stanford.EDU>
 Date: Thu, 18 Jan 2001 11:47:31 -0800


 Subject: RE: Moving table to different tablespace



alter table <table_name> move tablespace <tablespace_name>;



Beware all indexes on the table will be marked as unusable and will need to be rebuilt.  I don't belive it will work with tables that contain longs or long raws, but I haven't tested that assertion.



Ian




-----Original Message-----


From: Rahul Dandekar [mailto:orcldba_at_hotmail.com]
Sent: Thursday, January 18, 2001 9:50 AM
To: L; L


Subject: Moving table to different tablespace




Can I do it without export import?


Think there is a 8i feature or something?



Need to move replication table out of system tablespace. But don't want to
do


export/import.....



Thanks



-Rahul






Think you know someone who can answer the above question? Forward it to them!
to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com 
Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl







 From: Alex Hillman <alex_hillman_at_physia.com>
 Date: Thu, 18 Jan 2001 14:43:47 -0500


 Subject: RE: A 50 MB  System Tablespace !? Ruminations on System Tablespac



This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.



------_=_NextPart_001_01C08186.F92ADD60


Content-Type: text/plain;


        charset="iso-8859-1"



View definitions, stored procedures, packages, etc. are stored in the system
tablespace so when one create them a lot it will require a lot of space and
if they are dropped this space is not freed.



Alex Hillman



-----Original Message-----


From: MacGregor, Ian A. [mailto:ian_at_SLAC.Stanford.EDU]
Sent: Thursday, January 18, 2001 1:01 PM
To: Multiple recipients of list ORACLE-L
Subject: A 50 MB System Tablespace !? Ruminations on System Tablespace
Fr




Who, these days can get along with a 50 MB system tablespace?  I just moved
a database from 7.3.4 to 8.1.6 via export/import.  I precreated all the
tablespaces allocating 1000 MB for


system.  If I look at dbms_free_space for the system tablespace  :



SQL> select bytes/1024/1024 from dba_free_space where tablespace_name =
'SYSTEM';


BYTES/1024/1024





      478.96094



I didn't order by block_id because I cannot fathom how the system database
gets fragmented.  Fragmentation  comes from the dropping of database
objects.  One never drops anything from the system tablespace; unless, it
was something which should not have been there in the first place.  The idea
of uniform extents is that when an object is dropped, or moved to another
tablespace, the holes  can be filled by the recreated object or a new one.
This plugging cannot be done with objects of different extent sizes which
are not multiples of each other.  Eventually a hole will be too small to be
used and "permanent" fragmentation results.
But again this shouldn't happen in the system tablespace because objects are
not dropped.



The other part of the  system tablespace problem is that objects and extent
sizes become too


large due to the 50% pctincrease value.



SQL> SELECT SUM(UNUSED_BLOCKS) FROM OBJECT_INFO

  2  WHERE TABLESPACE_NAME = 'SYSTEM' AND RUN# = 138

  3  /



SUM(UNUSED_BLOCKS)





              1429




OBJECT_INFO is a table I use to track object growth.  It is appended to via
dbms_space nightly.



SQL> SELECT 1429 * 8 /1024 FROM DUAL;


1429*8/1024




  11.164063



I use an 8k block size.   So there are about  11 MB in unused blocks in the
tablespace. 


Looks like the tablespace could be crammed into a little over 500 MB.  This
is not a huge database.  Total size of all objects in the database is
slightly less that 27 GB.  



Note in 8i not all objects in the system  tablespace use a 50% pctincrease.



SQL> SELECT PCT_INCREASE, COUNT(PCT_INCREASE) FROM 

  2  DBA_SEGBY PCT_INCREASE



  4  /MENTS WHERE TABLESPACE_NAME = 'SYSTEM'

  3  GROUP 


PCT_INCREASE COUNT(PCT_INCREASE)


------------ -------------------
           0                  31
          50                 384






AND the maximum number of extents  differs as well



MAX_EXTENTS COUNT(MAX_EXTENTS)


----------- ------------------
          0                  1
         99                  1
        249                  1
        505                379
       4096                  4
  2.147E+09                 29





Wow  an object  which can have no more than 0 extents.  That's the type sort
Received on Thu Jan 18 2001 - 13:26:07 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US