FAQ
I would like find out your personal experience on using AUTOALLOACTE for extent management in Oracle 10g. I usually use UNIFORM extent size but wondering what are the drawbacks for using AUTOALLOACTE.

Thanks,
Ashoke

[CONFIDENTIALITY AND PRIVACY NOTICE]

Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.

To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com

Search Discussions

  • Michael Dinh at Mar 3, 2011 at 6:54 pm
    In OLTP and for application, I use AUTOALLOCATE.

    However, there are some tables where the data is transient, then I use UNIFORM 64K, but thinking it should have been 1M instead.

    In DW, I use UNIFORM 8M

    Michael Dinh

    NOTICE OF CONFIDENTIALITY - This material is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this email (and attachments) is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. Please notify the sender of the error and delete the e-mail you received. Thank you.
    From: oracle-l-bounce_at_freelists.org On Behalf Of Mandal, Ashoke
    Sent: Thursday, March 03, 2011 10:43 AM
    To: exriscer_at_gmail.com; Hemant K Chitale
    Cc: Oracle Mailinglist
    Subject: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g

    I would like find out your personal experience on using AUTOALLOACTE for extent management in Oracle 10g. I usually use UNIFORM extent size but wondering what are the drawbacks for using AUTOALLOACTE.

    Thanks,
    Ashoke

    [CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records. To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
  • Subodh Deshpande at Mar 4, 2011 at 5:09 am
    UNIFORM if record size is not going to change..
    On 4 March 2011 00:24, Michael Dinh wrote:

    In OLTP and for application, I use AUTOALLOCATE.



    However, there are some tables where the data is transient, then I use
    UNIFORM 64K, but thinking it should have been 1M instead.



    In DW, I use UNIFORM 8M



    Michael Dinh



    NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
    individual or entity to which it is addressed, and may contain information
    that is privileged, confidential and exempt from disclosure under applicable
    laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH
    INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING,
    AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI
    CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT
    NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS
    MESSAGE. If the reader of this email (and attachments) is not the intended
    recipient, you are hereby notified that any dissemination, distribution or
    copying of this communication is strictly prohibited. Please notify the
    sender of the error and delete the e-mail you received. Thank you.

    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *Mandal, Ashoke
    *Sent:* Thursday, March 03, 2011 10:43 AM
    *To:* exriscer_at_gmail.com; Hemant K Chitale
    *Cc:* Oracle Mailinglist
    *Subject:* AUTOALLOACATE vs. UNIFORM extent size in Oracle10g



    I would like find out your personal experience on using AUTOALLOACTE for
    extent management in Oracle 10g. I usually use UNIFORM extent size but
    wondering what are the drawbacks for using AUTOALLOACTE.



    Thanks,

    Ashoke

    [CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email
    is proprietary to Medtronic and is intended for use only by the individual
    or entity to which it is addressed, and may contain information that is
    private, privileged, confidential or exempt from disclosure under applicable
    law. If you are not the intended recipient or it appears that this mail has
    been forwarded to you without proper authority, you are notified that any
    use or dissemination of this information in any manner is strictly
    prohibited. In such cases, please delete this mail from your records. To
    view this notice in other languages you can either select the following link
    or manually copy and paste the link into the address bar of a web browser:
    http://emaildisclaimer.medtronic.com
    --
    ==============================
    DO NOT FORGET TO SMILE TODAY
    ==============================

    --
    http://www.freelists.org/webpage/oracle-l
  • David Fitzjarrell at Mar 4, 2011 at 2:10 pm
    I don't understand your comment as record size directly relates to db_block_size
    with regard to row chaining/migration. Variable extent sizes won't mitigate row
    chaining for variable sized records but a larger block size might, depending
    upon the length of the largest record, as there is a maximum block size of 32k
    that can be defined.



    David Fitzjarrell


    From: Subodh Deshpande
    To: mdinh_at_xifin.com
    Cc: Oracle Mailinglist
    Sent: Thu, March 3, 2011 11:09:52 PM
    Subject: Re: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g



    UNIFORM if record size is not going to change..

    On 4 March 2011 00:24, Michael Dinh wrote:

    In OLTP and for application, I use AUTOALLOCATE.
    However, there are some tables where the data is transient, then I use UNIFORM
    64K, but thinking it should have been 1M instead.

    In DW, I use UNIFORM 8M

    Michael Dinh

    NOTICE OF CONFIDENTIALITY - This material is intended for the use of the
    individual or entity to which it is addressed, and may contain information that
    is privileged, confidential and exempt from disclosure under applicable laws.
    BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION
    (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS
    FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN,
    EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR
    OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this
    email (and attachments) is not the intended recipient, you are hereby notified
    that any dissemination, distribution or copying of this communication is
    strictly prohibited. Please notify the sender of the error and delete the e-mail
    you received. Thank you.
    From:oracle-l-bounce_at_freelists.org On
    Behalf Of Mandal, Ashoke
    Sent: Thursday, March 03, 2011 10:43 AM
    To: exriscer_at_gmail.com; Hemant K Chitale
    Cc: Oracle Mailinglist
    Subject: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g

    I would like find out your personal experience on using AUTOALLOACTE for extent
    management in Oracle 10g. I usually use UNIFORM extent size but wondering what
    are the drawbacks for using AUTOALLOACTE.

    Thanks,
    Ashoke
    [CONFIDENTIALITY AND PRIVACY NOTICE] Information transmitted by this email is
    proprietary to Medtronic and is intended for use only by the individual or
    entity to which it is addressed, and may contain information that is private,
    privileged, confidential or exempt from disclosure under applicable law. If you
    are not the intended recipient or it appears that this mail has been forwarded
    to you without proper authority, you are notified that any use or dissemination
    of this information in any manner is strictly prohibited. In such cases, please
    delete this mail from your records. To view this notice in other languages you
    can either select the following link or manually copy and paste the link into
    the address bar of a web browser: http://emaildisclaimer.medtronic.com/
    --
    ==============================
    DO NOT FORGET TO SMILE TODAY
    ==============================

    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Mar 4, 2011 at 3:53 pm
    I'd would recommend AUTOALLOCATE over UNIFORM unless you require very
    large extents (>64MB). If require larger extents for a segment, just
    use the INITIAL attribute.

    On Thu, Mar 3, 2011 at 10:42 AM, Mandal, Ashoke
    wrote:
    I would like find out your personal experience on using AUTOALLOACTE for
    extent management in Oracle 10g. I usually use UNIFORM extent size but
    wondering what are the drawbacks for using AUTOALLOACTE.
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Michael Dinh at Mar 4, 2011 at 5:19 pm
    Greg,

    Tom Kytes suggest the same.

    Can you explain what happens when there is a queue table where the extents are at 1M or 8M and old data deleted are from 64K extents.

    Will those 64K extents be used if let's say no new tables are created and will this cause a little waste in storage.

    Thanks in advance for your response.

    Michael Dinh

    NOTICE OF CONFIDENTIALITY - This material is intended for the use of the individual or entity to which it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable laws. BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION (PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE. If the reader of this email (and attachments) is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. Please notify the sender of the error and delete the e-mail you received. Thank you.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of Greg Rahn
    Sent: Friday, March 04, 2011 7:53 AM
    To: ashoke.k.mandal_at_medtronic.com
    Cc: exriscer_at_gmail.com; Hemant K Chitale; Oracle Mailinglist
    Subject: Re: AUTOALLOACATE vs. UNIFORM extent size in Oracle10g

    I'd would recommend AUTOALLOCATE over UNIFORM unless you require very
    large extents (>64MB). If require larger extents for a segment, just
    use the INITIAL attribute.

    On Thu, Mar 3, 2011 at 10:42 AM, Mandal, Ashoke
    wrote:
    I would like find out your personal experience on using AUTOALLOACTE for
    extent management in Oracle 10g. I usually use UNIFORM extent size but
    wondering what are the drawbacks for using AUTOALLOACTE.
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Mar 4, 2011 at 5:54 pm
    Other than the fact that typically fragmentation and extent counts don't
    tend to matter that much really is there a reason for the recommendation
    that I'm missing. It's certainly the case that autoallocate *can* leave you
    in the old position where there is sufficient free space to extend an object
    but the extent size chosen won't fit any of the gaps left. Uniform by
    definition can't hit this issue. Given that I've as yet been unable to find
    a reason to prefer n extents over m extents where m >>> n, but I have been
    able to find plenty of reasons to limit datafile size my personal bias is
    for uniform extents (of 1mb or whatever the platform read size is but that's
    a pedantic detail). Oracle consistently recommend AUTOALLOCATE but I've not
    yet had a convincing rationale. My test from 2004 probably not run since is
    below - it only works because of the unusual pattern of extend|drop and the
    small limit filesize, IIRC the sizes chosen were such that in 2004 my 32m
    tablespace would exactly fill not error as below.. (8k db block size)

    SQL> select banner from v$version;
    BANNER

    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 -
    Production
    PL/SQL Release 11.2.0.2.0 -
    Production
    CORE 11.2.0.2.0 Production

    TNS for 32-bit Windows: Version 11.2.0.2.0 -
    Production
    NLSRTL Version 11.2.0.2.0 -
    Production
    SQL>

    SQL> create tablespace auto_alloc_test
    2 datafile 'c:\temp\auto_alloc.dbf' size 32832k
    3 extent management local;
    Tablespace created.

    SQL>
    SQL> /*
    SQL> create the tables
    SQL> */
    SQL>
    SQL> begin

    2 for i in 1..32 loop
    3 execute immediate 'create table table'||i||'(col1 number,col2 number)
    tablespace auto_alloc_test';
    4 end loop;
    5 end;
    6 /
    PL/SQL procedure successfully completed.
    SQL>

    SQL> select sum(bytes)/1024 free_k from dba_free_space where
    tablespace_name='AUTO_ALLOC_TEST';

    FREE_K

    29760
    SQL>

    SQL> begin
    2 for i in 1..15 loop

    3 for j in 1..32 loop
    4 execute immediate 'alter table table'||j||' allocate extent';
    5 end loop;

    6 end loop;
    7 end;
    8 /
    begin
    *
    ERROR at line 1:
    ORA-01653: unable to extend table SYS.TABLE18 by 8 in tablespace
    AUTO_ALLOC_TEST

    ORA-06512: at line 4

    SQL>

    SQL> select sum(bytes)/1024/1024 free_M from dba_free_space where
    tablespace_name='AUTO_ALLOC_TEST';

    FREE_M

    SQL>

    SQL> begin
    2 for i in 1..32 loop

    3 if i mod 2 = 0 then
    4 execute immediate 'drop table table'||i;
    5 end if;

    6 end loop;
    7 end;
    8 /
    PL/SQL procedure successfully completed.
    SQL>

    SQL> select sum(bytes)/1024/1024 free_mb from dba_free_space where
    tablespace_name='AUTO_ALLOC_TEST';

    FREE_MB

    15.5
    SQL>

    SQL> alter table table1 allocate extent;
    alter table table1 allocate extent
    *
    ERROR at line 1:
    ORA-01653: unable to extend table SYS.TABLE1 by 128 in tablespace
    AUTO_ALLOC_TEST

    SQL>

    SQL> drop tablespace auto_alloc_test including contents and datafiles;
    Tablespace dropped.
    SQL>

    SQL> spool off
    On Fri, Mar 4, 2011 at 3:53 PM, Greg Rahn wrote:

    I'd would recommend AUTOALLOCATE over UNIFORM unless you require very
    large extents (>64MB). If require larger extents for a segment, just
    use the INITIAL attribute.

    On Thu, Mar 3, 2011 at 10:42 AM, Mandal, Ashoke
    wrote:
    I would like find out your personal experience on using AUTOALLOACTE for
    extent management in Oracle 10g. I usually use UNIFORM extent size but
    wondering what are the drawbacks for using AUTOALLOACTE.
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l

    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Mandal, Ashoke at Mar 29, 2011 at 4:50 am
    Greetings,

    The asynchronous replication (between two databases) has broken. For some reason the repgroup at the master definition site is not accessible. I couldn't see it through replication manager. But could query as follows:

    SQL> select gname, status from dba_repgroup;

    GNAME STATUS
    ------------------------------ ---------------
    REPGROUP_1 NORMAL

    But I am not allowed to suspend the replication or do anything to cleanup the replication.

    Do any of you have the manual steps to remove and cleanup the replication from both of the databases(master definition site and master site).

    Thanks,
    Ashoke

    [CONFIDENTIALITY AND PRIVACY NOTICE]

    Information transmitted by this email is proprietary to Medtronic and is intended for use only by the individual or entity to which it is addressed, and may contain information that is private, privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please delete this mail from your records.

    To view this notice in other languages you can either select the following link or manually copy and paste the link into the address bar of a web browser: http://emaildisclaimer.medtronic.com
  • Yechiel Adar at Mar 29, 2011 at 5:47 am
    My removal script does:
    dbms_repcat.drop_master_repgroup on each database separately with
    drop_contents => false and all_sites => false.
    then I drop the replication administrator user with cascade in each
    database.

    This works several times.

    Yechiel Adar
    Israel
    On 29/03/2011 06:50, Mandal, Ashoke wrote:
    Do any of you have the manual steps to remove and cleanup the
    replication from both of the databases(master definition site and
    master site).
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 3, '11 at 6:42p
activeMar 29, '11 at 5:47a
posts9
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase