Thursday, March 15, 2012

Server Configuration Planning


1) Existing server configuration (Processor, No of CPU, RAM, Disk Capacity, … , …)

2) No. of running databases on server
3) Databases folder size of all years


select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "DB_Folder_size_GB"
from ( select sum(bytes)/1024/1024/1024 data_size
from dba_data_files) a,
( select nvl(sum(bytes),0)/1024/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size
from v$controlfile) d ;


4) Max concurrent connections in the database

Maximum concurrent connections (mcc) refers to the total number of sessions (connections) about which a device can maintain state simultaneously.

select highwater from dba_high_water_mark_statistics where name = 'SESSIONS';

select sum(inuse) from ( select name, round(sum(mb),1) mb, round(sum(inuse),1) inuse from (select case when name = 'buffer_cache' then 'db_cache_size'
when name = 'log_buffer'
then 'log_buffer'
else pool
end name,
bytes/1024/1024 mb,
case when name <> 'free memory'
then bytes/1024/1024
end inuse
from v$sgastat
)group by name );

select
(select highwater from dba_high_water_mark_statistics where name = ('SESSIONS'))*(2048576+a.value+b.value) pga_size
from
v$parameter a,
v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size'
;

5) Connections Per Second
Connections per second (c/s) refers to the rate at which a device can establish state parameters for new connections.

6) Transactions Per Second
Transactions per second (t/s) refers to the number of complete actions of a particular type that can be performed per second.

6) Weekly or monthly growth of databases.

database_monitoring_script

7) Oracle core license??

8) Network Load (Bandwidth,.........)




-------------------------------
Memory in a data warehouse is particularly important for processing memory-intensive operations such as large sorts. Access to the data cache is less important in a data warehouse because most of the queries access vast amounts of data. Data warehouses do not have memory requirements as critical as OLTP applications.

The number of CPUs provides you a good guideline for the amount of memory you need. Use the following simplified formula to derive the amount of memory you need from the CPUs you selected:

= 2 *
For example, a system with 6 CPUs needs 2 * 6 = 12 GB of memory. Most standard servers fulfill this requirement.

------------------------------

select * from dba_high_water_mark_statistics where name in ('SESSIONS','DB_SIZE');
select * from v$resource_limit;


--------maximum amount of memory allocated by the currently connected sessions
SELECT SUM (value/1024/1024) "max memory allocation" FROM v$sesstat ss, v$statname st WHERE st.name = 'session uga memory max' AND ss.statistic# = st.statistic#;


---------------------------Used SGA-----------------
select sum(inuse) from (
select name, round(sum(mb),1) mb, round(sum(inuse),1) inuse
from (select case when name = 'buffer_cache'
then 'db_cache_size'
when name = 'log_buffer'
then 'log_buffer'
else pool
end name,
bytes/1024/1024 mb,
case when name <> 'free memory'
then bytes/1024/1024
end inuse
from v$sgastat
)group by name );


-------------------pga requirement------------

select
(select highwater from dba_high_water_mark_statistics where name = ('SESSIONS'))*(2048576+a.value+b.value) pga_size
from
v$parameter a,
v$parameter b
where
a.name = 'sort_area_size'
and
b.name = 'hash_area_size'
;
-------------------------------------
http://docs.oracle.com/cd/B28359_01/server.111/b28314/tdpdw_system.htm

http://www.wdpi.com/product/used-hp/proliant-servers/ml570
http://h18004.www1.hp.com/products/quickspecs/12474_na/12474_na.html
http://en.wikipedia.org/wiki/Intel_QuickPath_Interconnect
http://www.intel.com/content/www/us/en/io/quickpath-technology/quickpath-technology-general.html
http://www.dfisica.ubi.pt/~hgil/utils/Hyper-Threading.4_Turbo.Boost.html
http://www.intel.com/content/www/us/en/architecture-and-technology/hyper-threading/hyper-threading-technology.html
http://h18000.www1.hp.com/products/quickspecs/13669_na/13669_na.html
http://www.cpubenchmark.net/multi_cpu.html

http://www.dbspecialists.com/files/presentations/mts_case_study.html

Configuration for LD DB:
2 Quad core processor 64 BIT (upgradable to 4 processor)
16 or 32 GB RAM
14 * 200 GB HDD (SAN)
Operating system : Win 2003 enterprise edition 64 bit

HP ProLiant ML570

Intel® Dual-Core 64-bit Xeon® processor 7000 sequence
Processor-4
3.00 GHz, 800MHz FSB
32 or 64 GB RAM

----------------------------------------------------------------------Eight Core Processor
Intel® Xeon® Processor E7-8837 product Family Xeon E7-8800
(24M Cache, 2.66 GHz, 6.40 GT/s Intel® QPI)
Thermal Design Power 130W (refers to the maximum amount of power the cooling system in a computer is required to dissipate.)


clock speed 2.66GHz
QPI (QuickPath Interconnect) is a point-to-point processor interconnect developed by Intel which replaces the Front Side Bus (FSB) in Xeon, Itanium, and certain desktop platforms.
Processor-2
Processor Core: Octa-core (8 Core) / Quad-core (4 core)

32 or 64 GB RAM

---------------------------
64-bit Intel® Xeon® Processor 24M Cache, 2.66 GHz, 6.40 GT/s Intel® QPI
with intel turbo boost technology / hyper threading technology

Most Used Processor: Intel® Xeon® Processor E7-8837 @ 2.66 GHz

Processor- 2 Quad-core (4 core)
32 or 64 GB RAM

-------------------------------------------------------------------Quad-Core Processors-----------------------

Intel® Xeon® E7520 (1.86GHz/4-core/18MB/95W) Processor
Memory 16 or 32 GB
Storage 8TB



--------------------------------------


The key to this dramatic claim is a feature called Turbo Boost technology. Basically, if the current application workload isn't keeping all four cores fully busy and pushing right up against the chip's TDP (Thermal Design Power) limit, Turbo Boost can increase the clock speed of each core individually to get more performance out of the chip.

---------------------------------------

Ten-Core Processors
Intel® Xeon® E7-4870 (2.40GHz/10-core/30MB/130W) Processor
Intel® Xeon® E7-4860 (2.26GHz/10-core/24MB/130W) Processor
Intel® Xeon® E7-4850 (2.00GHz/10-core/24MB/130W) Processor
Intel® Xeon® E7-8867L (2.13GHz/10-core/30MB/105W) Processor
Eight-Core Processors
Intel® Xeon® E7-8837 (2.67GHz/8-core/24MB/130W) Processor
Intel® Xeon® E7-4830 (2.13GHz/8-core/24MB/105W) Processor
Intel® Xeon® E7-4820 (2.0GHz/8-core/18MB/105W) Processor
Intel® Xeon® X7560 (2.26GHz/8-core/24MB/130W) Processor
Intel® Xeon® X7550 (2.0GHz/8-core/18MB/130W) Processor
Intel® Xeon® L7555 (1.86GHz/8-core/24MB/95W) Processor
Six-Core Processors
Intel® Xeon® E7-4807 (1.86GHz/6-core/18MB/95W) Processor
Intel® Xeon® E7540 (2.0GHz/6-core/18MB/105W) Processor
Intel® Xeon® E7530 (1.86GHz/6-core/12MB/105W) Processor
Intel® Xeon® X7542 (2.66GHz/6-core/18MB/130W) Processor
Quad-Core Processors
Intel® Xeon® E7520 (1.86GHz/4-core/18MB/95W) Processor

NOTE: New Intel Microarchitecture with Intel Virtualization Technology FlexMigration. Industry Standard Intel® 7500 Chipset with four high-speed interconnects up to 6.4GT/s.


No comments:

Post a Comment

Followers