This discussion is archived
10 Replies Latest reply: Jan 28, 2013 4:42 AM by div RSS

Write Performanc Issues.

div Newbie
Currently Being Moderated
Hi
Oracle : 11.2.0.2.0 EE.
Linux : Rhel 5.6.

Dell R720 :
EMC VNX SAN Storage :

Here is the scenario:
The dell server is connected to the SAN . We have 2 instances on this same server. When we create a tablespace on one instance it takes twice as long as creating the tablespace on the 2nd instance. The tablespaces are created on the san.
At this point we have eliminated any hardware/ SAN issues. Since both databases are on the same server and hardware, connecting to the same lun on the san.

Any help would be appreciated.
Below is the relative information of the creation scripts and init.ora parameters.

Tablesapace creation Script for ORCL1 (Notice the 8k block size corresponding to the db_block_size on the db ).
CREATE BIGFILE TABLESPACE TEST_IO BLOCKSIZE 8k LOGGING
DATAFILE '/uP01/oracle/oradata/orcl1/TEST_IO_01.dbf'
SIZE 256G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT ONLINE;


14 minutes to create

Tablespace creation script for orcl 2 (Notice the 32k block size corresponding to the db_block_size on the db ).


CREATE BIGFILE TABLESPACE TEST_IO BLOCKSIZE 32k LOGGING
DATAFILE '/uP01/oracle/oradata/orcl2/TEST_IO_01.dbf'
SIZE 256G AUTOEXTEND OFF
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO
PERMANENT ONLINE;

30minutes to create


Init.ora on orcl1 :
orcl1.__db_cache_size=494927872
orcl1.__java_pool_size=4194304
orcl1.__large_pool_size=4194304
orcl1.__oracle_base='/opt/app/oracle/ora11g'#ORACLE_BASE set from environment
orcl1.__pga_aggregate_target=536870912
orcl1.__sga_target=1073741824
orcl1.__shared_io_pool_size=0
orcl1.__shared_pool_size=515899392
orcl1.__streams_pool_size=25165824
*._optimizer_extend_jppd_view_types=FALSE
*._optimizer_group_by_placement=FALSE
*._replace_virtual_columns=FALSE
*.audit_file_dest='/opt/app/oracle/ora11g/admin/orcl1/adump'
*.audit_trail='none'
*.compatible='11.1.0.0.0'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl1'
*.diagnostic_dest='/opt/app/oracle/ora11g'
*.local_listener=''
*.open_cursors=1000
*.pga_aggregate_target=536870912
*.processes=500
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.sec_case_sensitive_logon=FALSE
*.sessions=555
*.sga_target=1073741824
*.undo_tablespace='UNDOTBS1'



orcl2
*.db_block_size=32768
*.db_cache_size=23068672000
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_files=10000
*.db_flashback_retention_target=0
*.db_keep_cache_size=134217728
*.db_name='orcl2'
*.db_unique_name='ORCL2'
*.diagnostic_dest='/opt/app/oracle/ora11g'
*.fast_start_mttr_target=1200
*.filesystemio_options='SETALL'
*.java_pool_size=268435456
*.job_queue_processes=4
*.large_pool_size=134217728
*.log_buffer=134217728
*.log_checkpoint_timeout=0
*.log_checkpoints_to_alert=TRUE
*.open_cursors=1000
*.pga_aggregate_target=31457280000
*.processes=300
*.query_rewrite_integrity='STALE_TOLERATED'
*.recyclebin='OFF'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.resumable_timeout=7200
*.sessions=335
*.shared_pool_reserved_size=134217728
*.shared_pool_size=2147483648
*.star_transformation_enabled='TRUE'
*.trace_enabled=FALSE
*.undo_retention=36000
*.undo_tablespace='UNDOTBS1'
  • 1. Re: Write Performanc Issues.
    damorgan Oracle ACE Director
    Currently Being Moderated
    My guess, based on experience with this, is the 32K block size though it is impossible to say that for sure.

    Why 32K block size? I would rarely recommend it as a good idea as any size other than 8K is essentially untested prior to release and does not correspond with your SAN O/S block size.

    Also why create a 256G tablespace ... with EMC I would create one smaller than 128G, for example 127G, and let it autoextend.
  • 2. Re: Write Performanc Issues.
    rp0428 Guru
    Currently Being Moderated
    >
    The dell server is connected to the SAN . We have 2 instances on this same server. When we create a tablespace on one instance it takes twice as long as creating the tablespace on the 2nd instance. The tablespaces are created on the san.
    At this point we have eliminated any hardware/ SAN issues. Since both databases are on the same server and hardware, connecting to the same lun on the san.
    >
    If those are really the config paramters for your instances then you may have some serious configuration issues.

    Instance 1

    1. you have shown SGA_TARGET is set (implying Automatice Shared Memory Management) but then you show the individual components have also been set: SHARED_POOL_SIZE, LARGE_POOL_SIZE, etc.

    Which are trying to use manual or automatic shared memory management?

    2. SGA_TARGET is set to only 1 GB. Why so small?

    Instance 2

    1. You have NOT set DB_nK_CACHE_SIZE but say you want to use 32 KB block size. This parameter MUST BE SET.

    2. You have set DB_CACHE_SIZE to 23 GB. Why? Your block size is 32 KB that you want to use. Only the SYSTEM tablespace will use the standard 8K blocks so why the enormous cache? You only used a cache of .5 GB and total memory target of 1 GB for instance 1.

    3. SGA_TARGET is NOT set - why not? why are you not using the same memory management as what you tried to do in instance 1.

    What is going on with these two instances that their config is so radically different?

    Daniel suggested a possible issue with the 32 KB block size. My hypothesis is that your configuration for both instances is faulty and that for instance 2 in particular your failure to provide any setting for DB_nK_CACHE_SIZE is a likely suspect for causing the issue.

    Review the DBA Guide for how to configure memory and the requirements for using non-standard block sizes.

    I would expect that once you have your instances configured properly you won't have the problem you reported.

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/memory004.htm

    About Automatic Shared Memory Management
    >
    Automatic Shared Memory Management simplifies SGA memory management. You specify the total amount of SGA memory available to an instance using the SGA_TARGET initialization parameter and Oracle Database automatically distributes this memory among the various SGA components to ensure the most effective memory utilization.
    >
    See the example for automatic management
    >
    You can take advantage of automatic shared memory management by setting Total SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following statements:

    ALTER SYSTEM SET SGA_TARGET = 992M;
    ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
    ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
    ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
    ALTER SYSTEM SET DB_CACHE_SIZE = 0;
    ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
    where 992M = 1200M minus 208M.
    >
    For non-standard block sizes see the section 'Setting the Buffer Cache Initialization Parameters'
    >
    Oracle Database supports multiple block sizes in a database. If you create tablespaces with nonstandard block sizes, you must configure nonstandard block size buffers to accommodate these tablespaces. The standard block size is used for the SYSTEM tablespace. You specify the standard block size by setting the initialization parameter DB_BLOCK_SIZE. Legitimate values are from 2K to 32K.

    If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter set. Oracle Database assigns an appropriate default value to the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default to 0, and no additional block size caches are configured.

    The sizes and numbers of nonstandard block size buffers are specified by the following parameters:

    DB_2K_CACHE_SIZE
    DB_4K_CACHE_SIZE
    DB_8K_CACHE_SIZE
    DB_16K_CACHE_SIZE
    DB_32K_CACHE_SIZE
    Each parameter specifies the size of the cache for the corresponding block size.
    >
    Don't forget - when using non-standard block sizes you MUST set both the standard cache parameter (8k) and the non-standard parameter (32k for your use case).
  • 3. Re: Write Performanc Issues.
    damorgan Oracle ACE Director
    Currently Being Moderated
    Hi rp0428 ... I agree with everything you wrote ... but I was focusing solely on the time required to perform a tablespace creation not the multitude of other issues.

    I'm not sure how all the misconfigured memory parameters in the world would affect the time for laying down a datafile.

    Once the tablespaces exists then what you pointed out would surely explain a multitude of other issues that would most certainly arise.
  • 4. Re: Write Performanc Issues.
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    you have shown SGA_TARGET is set (implying Automatice Shared Memory Management) but then you show the individual components have also been set: SHARED_POOL_SIZE, LARGE_POOL_SIZE,
    Real world experience shows reasons for setting SHARED_POOL_SIZE and DB_CACHE_SIZE even when using SGA_TARGET (ASMM). These parameters are set so as to define "floor" values below which Oracle is not permitted to go.

    You have NOT set DB_nK_CACHE_SIZE but say you want to use 32 KB block size .... Only the SYSTEM tablespace will use the standard 8K blocks so why the enormous cache?
    I see db_block_size set to 32K. Therefore, even SYSTEM is 32K.


    I am not going into a discussion whether it is right to define DB_BLOCK_SIZE as 32K. It may be, it may not be.


    Hemant K Chitale
  • 5. Re: Write Performanc Issues.
    rp0428 Guru
    Currently Being Moderated
    >
    I'm not sure how all the misconfigured memory parameters in the world would affect the time for laying down a datafile.

    Once the tablespaces exists then what you pointed out would surely explain a multitude of other issues that would most certainly arise.
    >
    Haven't tested it and don't plan to since OP can.

    My thinking was that it could be related to the amount of cacheing that Oracle might need to do and without the 32kb cache size set it might not be caching the same number of blocks that it does when it creates an 8kb block size datafile.

    The OS is doing any writing of the file that is involved so if there is any difference in speed it has to be something Oracle is doing. That is my hypothesis.

    OP can test by deleting both the 8kb and 32kb files and using the 8kb system to create the 32kb file and see if the same instance can create both in the same time or if the difference persists. If the times are comparable if only the one instance creates both then it would point to the configuration as the issue.

    I'm also wondering if all of the blocks need to be pre-formatted for a non-standard block size but not for the standard size.
  • 6. Re: Write Performanc Issues.
    damorgan Oracle ACE Director
    Currently Being Moderated
    Neither one of us can test it and, as you say, the OP certainly can. But I wouldn't be rushing out to use a non-standard blocks size unless I had a lot of metrics that proved it was a good idea ... and by "good idea" I mean more than just performance.

    I can't think of a single time I've worked with 32K blocks I didn't find myself trying to convince my customer to burn it to the ground.
  • 7. Re: Write Performanc Issues.
    div Newbie
    Currently Being Moderated
    Thank you everyone for all the replies

    "Why 32K block size? I would rarely recommend it as a good idea as any size other than 8K is essentially untested prior to release and does not correspond with your SAN O/S block size."

    We will be using the database for a datawarehousr application. Hence the 32k block size. ( Not going into why we chose 32k as it was not my choice).


    "Also why create a 256G tablespace ... with EMC I would create one smaller than 128G, for example 127G, and let it autoextend."

    This is just a quick test to measure the write performance and give a benchmark. The expectation would be that they would be close.

    Div.
  • 8. Re: Write Performanc Issues.
    div Newbie
    Currently Being Moderated
    Hi rp0428
    Was reading up on your recommendation of db_nK_cache_size, however reading further into the document I found the following note :

    " Note:

    The DB_nK_CACHE_SIZE parameters cannot be used to size the cache for the standard block size. If the value of DB_BLOCK_SIZE is nK, it is invalid to set DB_nK_CACHE_SIZE. The size of the cache for the standard block size is always determined from the value of DB_CACHE_SIZE. "

    What is your recommendation for setting this value, for the scenario that I have.

    Thanks
    Div.
  • 9. Re: Write Performanc Issues.
    JohnWatson Guru
    Currently Being Moderated
    Hello - possibly your filesystemio_options setting is slowing things down on orcl2. I have learnt to be very careful with this parameter, I believe that it can often do more harm than good if is not set appriopriately for the SAN and the file system mount options.

    I can't test on your scale, but I wouldn't think the blocksize per se is relevant (and of course you can't set db_32k_cache_size, because 32K is youR standard blocksize).
  • 10. Re: Write Performanc Issues.
    div Newbie
    Currently Being Moderated
    I did the test again with the db_cache_size set to 32k and still got the same results.

    SQL> show parameter cache;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    client_result_cache_lag big integer 3000
    client_result_cache_size big integer 0
    db_16k_cache_size big integer 0
    db_2k_cache_size big integer 0
    db_32k_cache_size big integer 0
    db_4k_cache_size big integer 0
    db_8k_cache_size big integer 0
    db_cache_advice string ON
    db_cache_size big integer 32G
    db_flash_cache_file string
    db_flash_cache_size big integer 0

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_keep_cache_size big integer 256M
    db_recycle_cache_size big integer 0
    object_cache_max_size_percent integer 10
    object_cache_optimal_size integer 102400
    result_cache_max_result integer 5
    result_cache_max_size big integer 20992K
    result_cache_mode string MANUAL
    result_cache_remote_expiration integer 0
    session_cached_cursors integer 50



    SQL> CREATE BIGFILE TABLESPACE TEST_IO BLOCKSIZE 32k LOGGING
    2 DATAFILE '/uP01/oracle/oradata/orcl2/TEST_IO_01.dbf'
    3 SIZE 256G AUTOEXTEND OFF
    4 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
    5 SEGMENT SPACE MANAGEMENT AUTO
    6 PERMANENT ONLINE;

    Tablespace created.

    Elapsed: 00:33:16.77

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points