1 / 79

ASE106: Tuning ASE for PeopleSoft Applications

ASE106: Tuning ASE for PeopleSoft Applications. Stefan Karlsson Stefan.Karlsson@Sybase.com ASE Evangelist August 15-19, 2004. Comments and Caveats. This presentation discusses areas of interest for Performance & Tuning efforts involved in running PeopleSoft applications on Sybase ASE.

niles
Download Presentation

ASE106: Tuning ASE for PeopleSoft Applications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. ASE106: Tuning ASE for PeopleSoft Applications Stefan Karlsson Stefan.Karlsson@Sybase.com ASE Evangelist August 15-19, 2004

  2. Comments and Caveats • This presentation discusses areas of interest for Performance & Tuning efforts involved in running PeopleSoft applications on Sybase ASE. • A fair share of the content is generic and should be well-known – regardless, another angle is worthwhile • Caveats: • “Should”, “It depends”, “Your mileage may vary” - Platform, OS, resources, ASE, database, application, usage, customization • Not-supported – not everything in this presentation is supported or well documented. These features can break things and are likely to change. Use the appropriate documentation. When in doubt – don’t use it!

  3. Why a PeopleSoft Specific P&T Presentation? • “Ensure an optimized physical database design” • The product is EOL’d when I’m done with the 20,000 tables • “Identify the slow-running query” • Got that, has to be one of these 50,000 that runs within the problematic half hour • “Add statistics for the columns referenced in queries” • I’ll have a look at the 400,000 columns • “Just look in the application log file” • With this tracing enabled it’s 800Mb in size • “Run the query from outside the application” • How do I get the query from a cursor opened on a prepared statement? • “APL tables help performance” • Which one can I change? Without causing lock conflicts and costly downtime. • “Unused indexes cost performance” • I’ll delete some and see where it blows up

  4. Agenda • PeopleSoft – Sybase alliance • ASE 12.5.X • Features/Featurettes most applicable to PeopleSoft applications • Server tuning • Server resources: Discussions and considerations; monitoring and tuning • Query tuning • Optimizer and statistics issues • Identifying and tuning queries • Miscellaneous • Recommendations • More information • Appendix

  5. SCM CRM HCM FMS PEOPLETOOLS INTEGRATION DATA MANAGEMENT RDBMS REPLICATION DATA MIGRATION Industry Leaders – An Alliance Get More Value, Get More Done.

  6. Strategic Alliance Partner and Customer • Sybase is… • A PeopleSoft Global Platform Partner • A PeopleSoft Global Software Partner • A PeopleSoft Customer • Sybase uses ESA and CRM 8.8 • PeopleSoft is… • Sybase’s preferred CRM and ERP application partner • A Sybase Tier 1 ISV Partner

  7. Technology/Engineering Marketing Sales Professional Services Worldwide Support A Comprehensive Alliance

  8. Sybase Solutions for PeopleSoft • Data Migration: Convoy DM • Speeding PeopleSoft deployment through streamlined data migration • Application Integration: BPI Suite • Facilitating integration across all applications • Business Process Integration: BPI Suite • Comprehensive management of disparate business processes • Native PSFT Integration: PSFT Adapter • Pre-packaged, native support for the PeopleSoft environment • Native Application Integration: Adapter Lib • Eliminating the borders between applications: Siebel, SAP, etc. • Relational Database: ASE • PeopleSoft Tier 1 RDBMS with enterprise reliability and scalability at the lowest total cost. • Data Replication: Replication Server • Guaranteeing data availability for business continuity, consolidation and live reporting Implementation Integration Operation

  9. Agenda • PeopleSoft – Sybase alliance • ASE 12.5.X • Features/Featurettes most applicable to PeopleSoft applications • Server tuning • Server resources: Discussions and considerations; monitoring and tuning • Query tuning • Optimizer and statistics issues • Identifying and tuning queries • Recommendations • More information • Appendix

  10. ASE 12.5.0.3 Performance Features • Lazy writes for tempdb’s • No physical IO for commit, allocations, system table changes • +22% in in-house test (OLTP transactions re-routed through SEL…INTO) • SELECT…INTO uses large IO pool • For all databases with almost linear scalability • Large (extent) IO becomes Allocation Unit IO • CREATE DATABASE +300% • Checkpoint performance vastly increased • +200% in in-house tests (4.3Gb data cache w lots of dirty buffers) • Optimistic index locking • For APL tables and very high load – contention on lock hash table spinlock

  11. ASE 12.5.0.3 Operational Features • Sampling for UPDATE STATISTICS • Reads rows from random pages to build histograms • UPDATE STATISTICS MyTable( MyCol) WITH SAMPLING = 10 PERCENT • Also applies to UPDATE INDEX STATISTICS and UPDATE ALL STATISTICS • Does not update density • Is not used for major attributes in columns • Housekeeper improvements • Monitoring Tables

  12. ASE 12.5.0.3 Housekeeper Improvements • Multiple HK tasks to manage different chores • HK GC at normal priority handles garbage collection • HK Wash at low priority handles cache washing • HK Chores at low priority handles e.g. statistics flushing, license usage, timed-out transaction’s detachment • Guaranteed writes of table level statistics through HK GC • Configurable: sp_configure ‘enable housekeeper GC’ • 4 or 5 enables more aggressive Housekeeper • Reduces need to run reorg

  13. ASE 12.5.0.3 Monitoring Tables • New interface to performance and diagnostics data • Full power of SQL • Low overhead – proxy tables on native RPC’s • Drilldown functionality • Meta data, server lever, database, device, network, object, process – down to SQL • Current and “Recent” • E.g. monProcessSQLText vs. monSysSQLText • Fully configurable • What should be enabled • Amount of memory dedicated • 18 parameters under section Monitoring • Only static option is ‘max SQL text monitored’ • $SYBASE/$SYBASE_ASE/scripts/installmontables

  14. ASE 12.5.0.3 Monitoring Tables (con’t) • Samples use of monOpenObjectActivity: 1> SELECT DBID, ObjectID, IndexID, OptSelectCount, UsedCount 2> FROM monOpenObjectActivity WHERE … ORDER BY … 3> go DBID ObjectID IndexID OptSelectCount UsedCount ----------- ----------- ----------- -------------- ----------- 4 745050659 1 0 0 4 809050887 1 0 0 4 809050887 3 0 0 4 841051001 2 0 0 4 841051001 3 0 0 ---------------------------8<--------------------- 4 1065051799 1 389 181619 4 137048493 1 396 450062 4 2028531229 1 402 145325 4 2060531343 1 471 173136 • N.b. monOpenObjectActivity has data since last reboot

  15. ASE 12.5.0.3 Monitoring Tables (con’t) • Sample use of monOpenObjectActivity: 1> SELECT O.name AS 'TableName', M.IndexID, M.LogicalReads, M.PhysicalReads 2> FROM monOpenObjectActivity M, HRPAY8..sysobjects O 3> WHERE DBID = 4 4> AND M.ObjectID = O.id 5> AND LogicalReads >= 10000 6> go TableName IndexID LogicalReads PhysicalReads ------------------------------ ----------- ------------ ------------- PS_PAY_LINE 0 152696753 3 PS_EMPLOYMENT 4 109162303 396 PS_DEDUCTION_BAL 2 78545288 121678 PS_PRIMARY_JOBS 1 74008624 7 PS_PAY_EARNINGS 5 58262160 840 PS_PRIMARY_JOBS 0 37477389 778 PS_STATE_TAX_TBL 0 25907611 11 PS_JOB 0 22596355 60858 PS_STATE_TAX_TBL 1 21889301 1 PS_JOB 1 17372833 802 PS_PAY_EARNINGS 3 15633747 115 PS_EARNINGS_BAL 2 11194857 58180 PS_PAY_EARNINGS 4 10238178 345 PS_TAX_BALANCE 0 9387716 11141 …

  16. ASE 12.5.1 Performance Features • TCP_NODELAY is turned on by default • Shouldn’t affect anyone since it should already be turned on! • Cache wizard sp_sysmon begin_sample sp_sysmon { end_sample | interval } [, ’cache wizard’ [, top_N [, filter] ] ] • Uses Monitoring Tables to display: • Hot objects • LIO and PIO rates • Spinlock contention • The usage of the cache and buffer pools. • The percentage of hits at a cache, buffer pool and object level • The effectiveness of large I/O • The effectiveness of APF • The cache occupancy by the various objects • Cache misses in sp_sysmon doesn’t include allocations

  17. ASE 12.5.1 Operational Features • Dynamic data caches • Add cache, increase cache size, delete cache on-line • Automatic database expansion • Dynamic listeners • LDAP user authentication • Fast recovery • Database mount and un-mount facility • Job scheduler • Misc: • 2nd and final fix for HK GC to be completely optimized for PSFT apps • 12.5.1 ESD#1 fixes CR323730

  18. ASE 12.5.2 Performance Features • Automatic cache partition tuning • For default data cache with default configuration and multiple engines • Use of columns stats in disjunctions (IN, OR) • -T301 enables optimizer to use these stats • Increased number of sub-queries in a statement • New configurable parameter ‘histogram tuning factor’ • Improves costing of high-frequent values • Improved monitoring abilities with sp_monitor • Based on Monitoring tables • Statement cache

  19. ASE 12.5.2 Operational Features • kill <spid> with statusonly • Provides rollback progress report • Security changes • Includes less default permissions on system tables • Significant performance improvement for dbcc checkstorage • IPv6 support • Backup improvements • Native compression, incl. support for remote Backup Servers • Database dumps password protection

  20. ASE 12.5.X PeopleSoft Certifications • 12.5.0.3 widely certified • Various 12.5.1 ESD#1 and 12.5.2 certifications • Certifications are done based on customer request – not automatically or proactively

  21. Agenda • PeopleSoft – Sybase alliance • ASE 12.5.X • Features/Featurettes most applicable to PeopleSoft applications • Server tuning • Server resources: Discussions and considerations; monitoring and tuning • Query tuning • Optimizer and statistics issues • Identifying and tuning queries • Recommendations • More information • Appendix

  22. Server Resources • Considerations, Monitoring and Tuning for the major resources: • Storage • Memory • CPU • Locks • Network

  23. Why is everybody so worried about the disks? • Central and critical resource for an RDBMS • The “D” in ACID – it’s the persistent storage for data • Slowest component in a computer system – by far ! • CPU – 1ns (1GHz) • L2 Cache – 4-8ns • RAM – 40-80ns • Disk – 10ms (your sub-ms IO’s are to the cache…)

  24. Storage Considerations • JBOD vs. SAN/NAS • Controller cache • RAID Level • Raw vs. file system • Devices and segments

  25. Usage Patterns • Random small reads • The major workload for an OLTP system • Sequential reads • DSS or reporting - larger scans • ASE APF’s • Query tuning issues • Writes • Logging (ACID) • Data cache washing • HK, checkpoint • Splits and other system writes • Sorts

  26. Disk Monitoring • sp_sysmon provides ASE’s point-of-view, … PROD1_DATA9 per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Reads APF 4862.2 531.4 291732 99.7 % Non-APF 12.6 1.4 753 0.3 % Writes 0.0 0.0 0 0.0 % ------------------------- ------------ ------------ ---------- ---------- Total I/Os 4874.8 532.8 292485 30.6 % • …but needs the OS perspective to be fully useful • R/s, w/s, kb r/s, kb w/s and service time are required metrics device r/s w/s kr/s kw/s wait actv svc_t %w %b sd1356 413.4 0.0 826.8 0.0 0.0 4.3 10.3 0 8 sd1414 0.0 0.2 0.0 1.6 0.0 0.0 0.5 0 0 sd1420 1686.8 0.0 3373.6 0.0 0.0 33.8 20.0 0 53 sd1477 2700.2 0.0 5400.4 0.0 0.0 31.7 11.7 0 41 sd1540 153.6 0.0 2457.6 0.0 0.0 0.2 1.4 0 21 sd1541 764.0 0.0 1528.0 0.0 0.0 7.7 10.0 0 27 sd1542 21.8 0.0 43.6 0.0 0.0 0.2 10.7 0 2 sd1598 2805.2 0.0 5610.4 0.0 0.0 31.6 11.3 0 49 sd1662 853.8 0.0 1707.6 0.0 0.0 4.8 5.6 0 24

  27. Tuning Disks • Focus is IO throughput and response time • Basics still rule • Separate data and log • In tempdb too • Raw is faster then FS • FS with DSYNC off is very useful for tempdb (more so in pre-12.5.0.3)

  28. Tuning Disks (con’t) • Device contention in ASE ? • Is Sybase mirroring turned on ? • JBOD • Number of underlying disks is key • Separate databases’ activity • Separate logs • SW RAID or Segments ?- SW RAID unless last percents are critical. • Can use RAID 5 for data – but do use RAID 1+0 for logs • SAN/NAS w controller caches • The number of disks under the LUN is key for random read performance • RAID 5 overhead is offset by controller cache when there’s no de-staging • Caches helps writes. A lot ! (for tempdb offset by 12.5.0.3 lazy writes) • Pre-fetch helps sequential scans – doesn’t help random reads • Segments help sequential scans, APF and allocations

  29. Memory Use • Data Caches • Named caches • Cache sizing • Cache binding • Pool or not to pool • APF • Proc cache • Plans • Dynamic SQL • Views • Sort headers • Resources • I.e: “number of xyz”, “user connections”

  30. Data Caches Considerations • When creating a cache we remove memory from objects • Memory is taken from default data cache so make sure it counts • Why create cache • Control/Guarantee hit rate • Minimize memory used by object • Decrease contention • Attributes to caches • Pools for large physical IO • APF percentage • Cache strategy – strict or relaxed • Partitions

  31. Data Caches Monitoring and Tuning • Trusty sp_sysmon has most of the data: Cache: default data cache per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Spinlock Contention n/a n/a n/a 39.0 % Utilization n/a n/a n/a 100.0 % Cache Searches Cache Hits 79962.2 5116.7 14393190 99.9 % Found in Wash 1094.2 70.0 196954 1.4 % Cache Misses 92.2 5.9 16603 0.1 % ------------------------- ------------ ------------ ---------- Total Cache Searches 80054.4 5122.6 14409793

  32. Data Caches Monitoring and Tuning (con’t) • Missing is per-object statistics, hence Mon Tables or Cache Wizard: default data cache ------------------ Run Size : 961.00 Mb Usage% : 99.97 LR/sec :43670.57 PR/sec : 328.48 Hit%: 99.25 Cache Partitions : 1 Spinlock Contention% : 9.00 Buffer Pool Information -------------------------------------------------------------------------------- IO Size Wash Size Run Size APF% LR/sec PR/sec Hit% APF-Eff% Usage% ------- ---------- ----------- ------ -------- -------- ------ -------- ------ 2 Kb 61440 Kb 961.00 Mb 10.00 43670.57 328.48 99.25 0.00 99.97 Object Statistics -------------------------------------------------------------------------------- Object LR/sec PR/sec Hit% Obj_Cached% Cache_Occp% ------------------------------------ ------- ------- ------ ----------- ----------- tpcc.dbo.stock 6004.50 134.13 97.77 45.94 37.49 tpcc.dbo.customer 437.97 99.75 77.22 35.98 17.61

  33. Data Caches Monitoring and Tuning (con’t) • What objects to cache ? • Same as in a non-PSFT database: • Logs, tempdb, prioritized objects • Highest LIO rate • Highest LIO/Mb rate • Size reasonably and monitor the caches after tuning • ASE Performance & Tuning manual has excellent sections on monitoring and tuning data caches

  34. Procedure Cache Considerations • Procedure cache is important – do not starve • Plans, prepared statements, views, sort headers are stored in proc cache • Monitor using sp_sysmon (below) and sp_monitorconfig (later slide) Procedure Cache Management per sec per xact count % of total --------------------------- ------------ ------------ ---------- ---------- Procedure Requests 2674.8 2.8 160490 n/a Procedure Reads from Disk 0.0 0.0 0 0.0 % Procedure Writes to Disk 0.0 0.0 0 0.0 % Procedure Removals 0.0 0.0 0 n/a Procedure Recompilations 0.0 0.0 0 n/a

  35. Memory Consuming Resources • Large amounts of memory go to resources • ‘number of open objects’, ‘…indexes’, ‘…locks’ • Dynamic memory configuration • Requires explicit default data cache and procedure cache sizes • Can save headroom and increase resources without rebooting • spconfig125.sql is insufficient • Even for install • Remedied in PT8.44, updated for PT8.45 • Open objects and open indexes need to be set so no reuse occurs during load • Data Mover complains • After load usually a smaller value suffices without causing reuse • OS, e.g. HPUX, needs kernel tuning to use all RAM • sp_monitorconfig is very, very useful • See next slide

  36. Monitoring Configurable Resources 1> EXEC sp_monitorconfig 'all' Usage information at date and time: Feb 20 2003 7:09PM. Name Num_free Num_active Pct_act Max_Used Reused ------------------------- ----------- ----------- ------- ----------- ------ additional network memory 0 0 0.00 0 NA audit queue size 100 0 0.00 0 NA heap memory per user 4096 0 0.00 0 No max cis remote connection 0 0 0.00 0 NA max memory 0 7864320 100.00 7864320 NA max number network listen 0 5 100.00 14 NA max online engines 0 7 100.00 7 NA memory per worker process 784 240 23.44 376 NA number of alarms 17 23 57.50 25 NA number of aux scan descri 200 0 0.00 0 NA number of devices 7 23 76.67 23 NA number of dtx participant 500 0 0.00 2 NA number of java sockets 0 0 0.00 0 NA number of large i/o buffe 20 0 0.00 0 NA number of locks 1000000 0 0.00 262528 NA number of mailboxes 30 0 0.00 0 NA number of messages 64 0 0.00 0 NA number of open databases 5 7 58.33 7 No number of open indexes 20763 19237 48.09 19243 No number of open objects 31011 8989 22.47 9139 No number of remote connecti 20 0 0.00 1 NA number of remote logins 20 0 0.00 1 NA number of remote sites 10 0 0.00 1 NA number of sort buffers 500 0 0.00 83392 NA number of user connection 47 3 6.00 23 NA number of worker processe 30 0 0.00 12 NA partition groups 1024 0 0.00 0 NA permission cache entries 10 5 33.33 5 NA procedure cache size 199990 10 0.01 113013 No

  37. CPU Considerations • Complex to size but usually fairly straight-forward to monitor • 10’s to 100’s online users per engine • 1-5 concurrent batch jobs per engine • Save CPU for e.g app server on same machine • Limit number of CPUs for ASE – more power to the clients • For varying workloads online and offline engines to match requirements • Issues in other areas can manifest as CPU use • Spinlock contention • Logical reads from e.g. table scan • Physical IOs

  38. CPU Monitoring and Tuning • Balance from OS-level • Binding processes to CPU decreases contention and scheduling issues, e.g. pbind, processor groups etc. • Monitor from OS-level • By process • Include system calls, reason for context switch • Correlate to other processes to find workload characteristics and potential contention issues • Monitor inside ASE • Relate to logical and physical IO, spinlock contention • Correlate OS and ASE level monitoring information • OS tools can help • Example from HP-UX: chatr on binaries to set higher page hint size decreased TLB misses and gave 15% batch processing performance gain

  39. CPU Monitoring and Tuning (con’t) Engine Busy Utilization CPU Busy I/O Busy Idle ------------------------ -------- -------- -------- Engine 0 99.7 % 0.2 % 0.2 % Engine 1 99.8 % 0.0 % 0.2 % Engine 2 99.7 % 0.0 % 0.3 % Engine 3 99.3 % 0.2 % 0.5 % ------------------------ -------- -------- -------- Summary Total 398.5 % 0.3 % 1.2 % Average 99.6 % 0.1 % 0.3 % CPU Yields by Engine per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Engine 0 0.0 0.0 0 0.0 % Engine 1 0.1 0.0 6 33.3 % Engine 2 0.1 0.0 6 33.3 % Engine 3 0.1 0.0 6 33.3 % ------------------------- ------------ ------------ ---------- Total CPU Yields 0.3 0.0 18 Network Checks Non-Blocking 712.5 0.7 42747 100.0 % Blocking 0.3 0.0 18 0.0 % ------------------------- ------------ ------------ ---------- Total Network I/O Checks 712.8 0.7 42765 Avg Net I/Os per Check n/a n/a 8.65343 n/a

  40. Locking Considerations • Difference between APL and DOL • Contention • Storage • Space consumption • Space management • Performance • Unexpected aspects like oam scans and cursor index choice and locking • APL, Allpages Locking Scheme, has been in the product since it's birth • Physical locking scheme – if you touch a page/block you lock it • Efficient storage and access methods (clustered index) • DOL, Data Only Locking Scheme, came in 11.9 • Locks logical data, by row or page – never transactional locks on indexes • RLL, Row Level Locking, is default for all PeopleSoft applications

  41. Is APL or DOL Best ? • Not taking lock contention into account, APL has best performance • Physical vs. logical locking scheme • In-memory management of APL indexes and tables • DOL (usually) requires more space management, however • Automatic, esp. w 12.5.0.3 HK • Most is on-line • Altering DOL -> APL • Locks table and requires space • Potential lock conflicts • Use Monitoring Tables and / or sp_object_stats to identify tables • Is it worth it ? • Performance-wise: 10-25%

  42. Locking Monitoring • Trusty ol’ sp_sysmon includes section on locking: Lock Summary per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Total Lock Requests 38130.2 20.4 2287811 n/a Avg Lock Contention 856.2 0.5 51374 2.2 % Deadlock Percentage 0.0 0.0 0 0.0 % • However, locking is a queue: w/o wait times it’s not fully useful • sp_object_stats has that: Object Name: MyDB..MyTable (dbid=4, objid=160000570, lockscheme=Allpages):- Page Locks SH_PAGE UP_PAGE EX_PAGE ---------- ---------- ---------- ---------- Grants: 0 0 686418 Waits: 0 0 443230 Deadlocks: 0 0 0 Wait-time: 0 ms 0 ms 3556617 ms Contention: 0.00% 0.00% 39.24% *** Consider altering MyDB..MyTable to Datapages locking. • N.B. sp_object_stats carries a significant overhead to processing!

  43. Locking Tuning • Running out of locks when e.g. loading ? • Use data pages locking when loading • Changing between data rows and data pages is just updating status bit in system table • To APL ? • Space, space management and performance benefits • Can take heavy hit on contention – watch for DML • During cursors scans locks are held on APL tables – not on DOL tables • Updatable cursors requires unique index on APL • Good use of Monitoring Tables SELECT name AS “TableName” FROM monOpenObjectActivity WHERE IndexID IN ( 0, 1 ) AND RowsInserted = 0 AND RowsUpdated = 0 and RowsDeleted = 0

  44. Spinlocks Considerations • In SMP environment two processes can alter the same data at the same time • Same issues as in a database • For different uses there are different constructions to protect code or data, critical regions • Spinlocks, semaphores, latches, etc. • Spinlocks are for short term locks • When cost of context switching is greater than executing critical region • Pseudo code, not that far off • while !test_and_set( *my_lock ) ; • So, issue is: waiting for a synchronization mechanism consumes platform resources and is not productive work

  45. Spinlocks Monitoring • Sp_sysmon is a really good tool for this: Cache: default data cache per sec per xact count % of total ------------------------- ------------ ------------ ---------- ---------- Spinlock Contention n/a n/a n/a 17.0 % ... Page & Row Lock HashTable Lookups 22770.3 2125.6 831100 n/a Avg Chain Length n/a n/a 8.39463 n/a Spinlock Contention n/a n/a n/a 22.0 % • Should never be double-digits! • In certain cases never above 5%

  46. Spinlocks Tuning • Decompose spinlocks, i.e. make more spinlocks available • Cache partitions, larger lock hashtable, spinlock ratios • Cache contention • High logical reads may be query tuning issues, e.g. table scans • Cache partitions (or named caches) • APL and OIL helps • Lock hash table contention – table, page & row, address • Adjust lock hash table size • Set appropriate spinlock ratio • Assess engine utilization: • Spinning causes high CPU/engine load • Fewer engines = less contention

  47. Network Considerations and Tuning • Network level • Ensure no Collisions • OS level • Loopback doesn’t buy much – and causes some small amount of extra work for ASE: YMMV • Check configuration, e.g. <tcp_deferred_ack_interval> • Configure ‘max network packet size’ and provide more memory (add nw mem or def nw pkt sz) • Configure in application too – See appendix • Verify:SELECT spid, cmd, network_pktsz FROM sysprocesses

  48. Running reorg • Fragmentation affects • Space usage • Index scans • Table scans • Clustering ratios and large IO efficiency is taken into account by optimizer • System Administration Guide has guidelines when to reorg • Assess using optdiag, derived_stat() or sp__optdiag • For a more complete discussion see: “Measuring and Monitoring Object-Level Fragmentation Within ASE” in ISUG Technical Journal 1Q2004 • HK in 12.5.0.3 helps a lot • Improvements in ESD’s and 12.5.1 makes it complete for PSFT apps

  49. Server Tuning Summary • Disk sub-systems are always important for DBMS performance • Use physical memory to the fullest • PeopleSoft applications require lots of resourcesDynamic memory configuration and sp_monitorconfig helps size and adapt • Use the rest for data and procedure caches • Save CPU for client and application servers • Spinlock contention is very costly • Number of engines and decompose spinlocks • Locking scheme is not only about locking • Always configure large network packet • reorg is not only for execution efficiency but also for optimizer decisions

  50. Agenda • PeopleSoft – Sybase alliance • ASE 12.5.X • Features/Featurettes most applicable to PeopleSoft applications • Server tuning • Server resources: Discussions and considerations; monitoring and tuning • Query tuning • Optimizer and statistics issues • Identifying and tuning queries • Recommendations • More information • Appendix

More Related