ASE106: Tuning ASE for PeopleSoft Applications - PowerPoint PPT Presentation

niles
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
ASE106: Tuning ASE for PeopleSoft Applications PowerPoint Presentation
Download Presentation
ASE106: Tuning ASE for PeopleSoft Applications

play fullscreen
1 / 79
Download Presentation
ASE106: Tuning ASE for PeopleSoft Applications
535 Views
Download Presentation

ASE106: Tuning ASE for PeopleSoft Applications

- - - - - - - - - - - - - - - - - - - - - - - - - - - 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