Best Practices for Performance of Oracle Database for Windows .

Uploaded on:
Category: Sales / Marketing
Best Practices for Performance of Oracle Database for Windows. Alex Keh Principal Product Manager, Windows and .NET Oracle. Agenda. <Insert Picture Here>. Why Oracle DB on Windows? Architecture Best Practices for Windows (32-bit & 64-bit) Windows OS Diagnostic Tools Optimize CPU usage
Slide 1

Slide 2

Best Practices for Performance of Oracle Database for Windows Alex Keh Principal Product Manager, Windows and .NET Oracle

Slide 3

Agenda <Insert Picture Here> Why Oracle DB on Windows? Engineering Best Practices for Windows (32-bit & 64-bit) Windows OS Diagnostic Tools Optimize CPU use Optimize Network Optimize File I/O Best Practices for 32-bit Windows Optimize Memory Best Practices for 64-bit Windows

Slide 4

<Insert Picture Here> Why Oracle DB on Windows?

Slide 5

Best Price/Performance on Windows #1 TPC-C Price/Performance amongst all stages Oracle Database 11g on Windows … And Oracle on Windows holds 4 of the 5 top TPC-C Price/Performance comes about Better than Oracle on Linux TPC-C by Price/Performance 11g MS SQL Highest Ranking Benchmark 1st 7th Price/tpmC $0.50 $0.84 tpmC 239,392 82,774 As of 12/1/09: Dell PowerEdge T710, 239,392 tpmC, $.50/tpmC accessible 11/18/09 . HP ProLiant ML350G5, 82,774 tpmC, $.84 tpmC, accessible 3/27/07. Source: Transaction Processing Performance Council (TPC)

Slide 6

<Insert Picture Here> Oracle Database on Windows Architecture

Slide 7

SGA contains db cradles, log cushions shared pool, other memory distributions Oracle process SGA 3GB or 8TB aggregate Each string comprises of PGA, stack, other memory allotments Background and closer view strings Code Architecture: Thread Model

Slide 8

Database Architecture Thread model Not a straight port of Oracle\'s procedure engineering 3GB (32-bit) or 8TB (64-bit) most extreme memory per database example VLM support permits >3GB on 32-bit Runs as a Windows administration handle No breaking points on memory, associations, assets aside from those forced by OS

Slide 9

File I/O Oracle11 g bolsters offbeat I/O to a wide range of records Logical and physical crude documents and segments are completely upheld (speedier than NTFS) Full 64-bit record I/O inside (for both Win32 and Win64 OS). This implies: No 2GB or 4GB impediments on DB record sizes Maximum document size is 64GB Maximum DB size is 4 petabytes

Slide 10

File I/O Asynch I/O support on Windows is useful for both record framework and crude gadgets No compelling reason to set INIT.ORA parameter "filesystemio_options" Default estimation of "asynch" is the suggested setting

Slide 11

Large Pages in Windows Server 2003/2008 For examples with vast memory necessities, expansive page backing can enhance execution To empower, set registry parameter ORA_LPENABLE to 1 32-bit – 4KB default page size – will now be 2MB x64 – 8KB default page size – will now be 2 MB Itanium – 8KB default page size – will now be 16 MB Windows Server 2003 64-bit might be moderate to apportioning an immense measure of memory with substantial pages Especially if memory is now divided Resolution: Start Oracle before different procedures Problem determined in Windows Server 2008

Slide 12

NUMA in Windows Server 2003/2008 NUMA support for memory/booking Database insightfully allots memory and calendars strings in light of hub arrangement Best Practices: For NUMA on AMD patch to a base P5 Test well before going into creation Work with your equipment merchant and Oracle backing to empower NUMA

Slide 13

Hyperthreading Circuitry added to Intel CPUs bringing about single CPU working as 2 CPUs All renditions of Oracle are bolstered in Hyperthreaded situations

Slide 14

Additional Integration with Windows Integration with Performance Monitor Integration with Event Log

Slide 15

Direct NFS Client on Windows Network Attached Storage (NAS) utilizes Network File System (NFS) Oracle Database 11g permits direct Windows NFS v3 get to Part of DB bit in Oracle Disk Manager library Specially helpful for Windows as Kernel NFS is not locally upheld on Windows Bypasses a great deal of programming layers in OS Tailored for the particular I/O designs that Oracle utilizes

Slide 16

Direct NFS Linear adaptability of direct NFS can be accomplished with cheap NICS - and Does not require costly switches which bolster join conglomeration… Oracle loads adjusting rather depending on a switch. Parallel system ways – More NICS – more transfer speed Direct NFS is a decent arrangement from low to top of the line database servers

Slide 17

<Insert Picture Here> Best Practices for 32-bit and 64-bit Windows

Slide 18

Diagnostic Tools - Performance Monitor

Slide 19

Process Explorer

Slide 20

OS Tools tasklist, taskkill tlist (Shows summon line args with - c) driverquery diskpart (sc question state= all) regmon, filemon, procexp, tcpview Windows Services for Unix poolmon

Slide 21

ODP.NET Integration with Performance Monitor Connection Pools New Feature of ODP.NET Enable in HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\<Assembly Version> \ PerformanceCounters Counters incorporate (among numerous): HardConnectsPerSecond HardDisconnectsPerSecond SoftConnectsPerSecond SoftDisconnectsPerSecond NumberOfActiveConnection NumberOfFreeConnections

Slide 22

Client Diagnosability on 11g Integrated with Automatic Diagnostic Repository (ADR) OCI and Net following and logging utilizes ADR as a matter of course Multithreaded customer side diagnosability connection bolster First Failure Capture No compelling reason to replicate a second time to get a dump Client and Server follow record relationship Reduce one-off demonstrative patches Structure Dump Facility Dumps more than only a stack

Slide 23

Client Characteristics V$SESSION_CONNECT_INFO/GV$SESSION_CONNECT_INFO CLIENT_CHARSET (NLS character set) CLIENT_CONNECTION (Homogeneous/heterogeneous) CLIENT_OCI_LIBRARY (Home-based, Instant Client Full/Light) CLIENT_VERSION (customer RSF form) CLIENT_DRIVER (OCI/JDBC/other) OCI_ATTR_DRIVER_NAME to set outsider driver

Slide 24

Client-Side Crash Handler Goal: handle segfaults, other center dumps As of 10.2, handler just in RDBMS server With 11g, we include one customer side Generates blunder message & stack follow, and controls center dump area

Slide 25

CPU Tuning Oracle utilizes all processors accessible through the OS ORACLE_AFFINITY registry quality can be set to advise Oracle which strings to keep running on which processors (same setting for all occurrences) Use Database Resource Manager to set CPU utilization for various classes of clients For instance, one can design the db to utilize half CPU for gold clients, 30% for silver and 20% for rest Thread needs can be set in the registry utilizing the ORACLE_PRIORITY variable

Slide 26

CPU Tuning – Diagnosing High CPU Process Explorer: drill down to strings Get string id of high CPU string and after that do inquiry SELECT a.spid, b.username FROM v$process a, v$session b WHERE a.addr= b.paddr AND a.spid = <thread number>

Slide 27

Networking Best Practices Use one audience for each framework The default line size for Windows Server is 50 – increment to 200 or 300 utilizing QUEUESIZE parameter as a part of LISTENER.ORA – averts mistakes amid login storms Listener Logon Storm Handler Configurable on server side in LISTENER.ORA (RATE_LIMIT = <max conn/sec>) Use just in the event that you have logon storm issues

Slide 28

Networking Best Practices Increase SDU_SIZE in SQLNET.ORA or TNSNAMES.ORA Controls SQL*Net bundle size Default SDU_SIZE in 11g is currently 8k. For mass information exchange situations, increment SDU_SIZE in sqlnet.ora or tnsnames.ora. It can be expanded up to 32K. Any blend of 11g and 10g will make it bring down to lower of the two associates (pre-11g default is 2K) For 10g increment SDU_SIZE to 8k or higher. Basic misperception: Do not set to coordinate MTU!

Slide 29

Networking Best Practices: Shared Server versus Devoted Server Dedicated server gives absolute best execution Each customer association has it\'s own string Memory utilization is 2-4 MB for each server string Oracle utilizes committed server for OLTP benchmarks Can hit adaptability limits because of memory use Shared server spares a considerable measure of memory! Inactive associations won\'t devour much memory Latency since dispatcher hands solicitation to shared server Good for substantial number of associations with numerous inert

Slide 30

Networking Best Practices: Shared Server versus Devoted Server Recommendation: Use committed server in the event that you have enough physical memory Otherwise, utilize shared for all sessions that might be inactive for quite a while Continue to utilize committed server for a little number of superior associations/questions.

Slide 31

Networking Best Practices: Using Shared Server Client associations offer pre-produced server strings No committed inactive strings squandering assets Enable Shared Server on customer in tnsnames.ora: (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp) (HOST=sales-server)(PORT=1521)) (CONNECT_DATA= ( (SERVER=shared) )) Modify server init.ora parameters to empower shared server Rough rules: 20 or 30 Shared Servers for every 500 sessions, then tune from that point Use 1 dispatcher for each 50-100 sessions See Net Admin Guide for more subtle elements

Slide 32

Networking Best Practices: Oracle Database Resident Connection Pool Pools Oracle Dedicated Servers Shares server side association pool crosswise over mid level frameworks and procedures Co-exists in all server setups Dedicated Servers, Shared Servers, RAC Most valuable when you have numerous a great many customer forms interfacing with a database server and every procedure needs to clutch the database server session for a brief span In test environment, we could bolster more than 20,000 associations with a 2 GB Database Server Pooling is alternatively empowered by DBA on Server Client interface string likewise needs (SERVER=POOLED)

Slide 33

Networking Best Practices: Connection Timeouts Client Side association timeouts: Achieve quick failover when you have different locations in interface string TCP.CONNECT_TIMEOUT – 11g component - it can be a few moments. Not set as a matter of course. 30 seconds is useful for more cases. Tune down from that point. Too low – false p

View more...