Tuning Oracle Database for Performance Testing

-
Tuning Database for Performance Testing:
1.      USERS tablespace created with a size of 20 GB
2.      INDX tablespace created with 15 GB
3.      TEMP tablespace set to 1 GB

CREATE TABLESPACE INDX DATAFILE
  '<path>/oracle/oradata/<Instance>/INDX_1.dbf' SIZE 6100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED,
  <path>/oracle/oradata/<Instance>/INDX_2.dbf' SIZE 6100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 40K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;


ALTER TABLESPACE USERS ADD DATAFILE '/<path>/oracle/oradata/<Instance>/users02.dbf' SIZE 5120M AUTOEXTEND OFF;
ALTER TABLESPACE USERS ADD DATAFILE '<path>/oracle/oradata/<Instance>/users03.dbf' SIZE 5120M AUTOEXTEND OFF;
ALTER TABLESPACE USERS ADD DATAFILE '<path>/oracle/oradata/<Instance>/users04.dbf' SIZE 5120M AUTOEXTEND OFF;
ALTER TABLESPACE USERS ADD DATAFILE '<path>/oracle/oradata/<Instance>/users05.dbf' SIZE 5120M AUTOEXTEND OFF;


Initora settings:

<dbinstance>.__db_cache_size=1275068416
<dbinstance>.__java_pool_size=33554432
<dbinstance>.__large_pool_size=83886080
<dbinstance>.__oracle_base='/stripe201'#ORACLE_BASE set from environment
<dbinstance>.__pga_aggregate_target=956301312
<dbinstance>.__sga_target=3103784960
<dbinstance>.__shared_io_pool_size=0
<dbinstance>.__shared_pool_size=973078528
<dbinstance>.__streams_pool_size=16777216
*.audit_file_dest='<path>/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='<path>/control01.ctl','<path>/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_cache_advice='ON'
*.db_domain=''
*.db_keep_cache_size=654311424
*.diagnostic_dest='<root>'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=<instance>XDB)'
*.event=''
*.filesystemio_options='setall'
*.java_pool_size=16777216
*.job_queue_processes=1000
*.large_pool_size=83886080
*.log_buffer=37748736
*.log_checkpoint_interval=99999999
*.memory_target=4060086272
*.open_cursors=3000
*.pga_aggregate_target=902653184
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=10000
*.sessions=3024
*.sga_max_size=3103784960
*.sga_target=3103784960
*.shared_pool_size=905969664
*.sort_area_retained_size=165536
*.sort_area_size=165536
*.undo_tablespace='UNDOTBS1'
*._ash_size=30000000

Comments

  1. UnknownJune 6, 2014 at 10:01 AM

    For what system configuration of database server the above said parameters suit pls......?

    ReplyDelete

Post a Comment

Popular posts from this blog

How to use a value between two different threads in Jmeter

-
Image
How to use a value between two different threads: Let us see how we can use the extracted value in one thread can be used in a different thread: As shown below we have Two threads (Thread Group – 1 and Thread Group -2). Under Thread Group -1 we have a HTTP Request where we extracted Request_Id and Session_Id. In our example we will use the same Ids in Thread Group -2 We have BeanShell PostProcessor to log the output of the variables in the output log. The Lines in the both BeanShell is as below: log.info("Request Id in Thread 1 = " + "${Request_Id}"); log.info("Session Id in Thread 1 = " + "${Session_Id}"); Now if we run the Script we get the below output: As we see the values of both the IDs are not passed to Thread Group-2. Now we will add few lines in the BeanShell PostProcessor as below and run again: ${__setProperty(Request_Id,"${Request_Id}")}; ${__setProperty(Session_Id,"${Session_Id}")};
Read more

Steps to Analyze AWR Report in Oracle

-
Image
Steps to Analyze AWR Report in Oracle AWR -   Automatic   workload repository is a collection of persistent   system performance   statistics   owned by SYS. It resides in SYSAUX   tablespace . By default snapshot are generated once every 60 min and maintained for 7 days . Each snapshot has a unique ID know as "snap_id". Snapshot detail can be found in " dba_hist_snapshot " view.   If we have Database performance issue and not the Database machine, then AWR Report is the place to look at. AWR is not used for real-time performance monitoring like the v$ tables. It is used for historical analysis of performance. AWR complements, but doesnot replace real-time monitoring. Once AWR Report is generated in Oracle, the next task is to analyze it. By going through the AWR Report we can easily solve issues like slow database, high wait events, slow query and many more issues. Even though the report is lengthy, Analyzing or Reading relevant part of AWR Report can
Read more

Correlation and checking in Results

-
Image
Correlation and checking In Previous post we have randomly selected value for depart and arrive. Each Depart and arrive have a outboundFlight and returnFlight field, which changes according to the selected values. For E.g.: When we recorded the script with depart as “London” and arrive as “Paris” the below flight details was recorded accordingly to the selection.      /*web_submit_form("reservations.pl_2",          "Snapshot=t5.inf",          ITEMDATA,          "Name=outboundFlight", "Value=241;97;03/28/2014", ENDITEM,          "Name=returnFlight", "Value=422;102;03/29/2014", ENDITEM,          "Name=reserveFlights.x", "Value=26", ENDITEM,          "Name=reserveFlights.y", "Value=8", ENDITEM,          LAST); */ As we see above the outboundFlight and returnFlight is hard coded and needs to be correlated according to the selected value. So inserting the c
Read more