ASE124 Tuning ASE for PeopleSoft Applications

1
ASE124 Tuning ASE for PeopleSoft Applications
Stefan.Karlsson_at_Sybase.comASE EvangelistAugust
2003
2
This presentation
Some comments

• This presentation discusses areas of interest for
  Performance Tuning efforts involved in running
  PeopleSoft applications on Sybase ASE.
• A fair share of the content generic and youve
  probably aware of much nevertheless, another
  angle is worthwhile
• Caveats
• Should, It depends, Your mileage will vary
  - Platform, OS, resources, ASE, database,
  application, usage, customization)
• Supported and documented not everything in this
  presentation is supported or well documented.
  These features can break things and are likely to
  change. These are included to help as the
  alternative is to just include supported and
  documented features and functionality.

3
This presentation
Whats so special tuning about a PeopleSoft
database ?

• Ensure an optimized physical database design
• The product is EOLd when Im 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
• Ill have a look at the 400,000 columns
• Just look in the application log file
• With this tracing enabled its 200Mb 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
• Ill delete some and see where it blows up

4
This presentation
Agenda

• PeopleSoft Sybase alliance
• ASE 12.5.0.3
• Features most applicable to PeopleSoft
  applications
• Server tuning
• Generic resources
• Configuring resources
• Tuning
• Query tuning
• Optimizer and statistics issues
• Identifying and tuning queries
• Miscellaneous
• Recommendations
• More information

5
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
• ESA (installed) and CRM 8.8 (implementation
  underway)
• PeopleSoft is
• Sybases preferred CRM and ERP application
  partner
• A Sybase Tier 1 ISV Partner

7
A Comprehensive Alliance
8
An Alliance providing Real Solutions

• Accelerate PeopleSoft implementation Deploy
  PeopleSoft applications faster via automated data
  migration code generator.
• Integrate any data source in real time Leverage
  existing infrastructure through automated,
  business process integration (BPI) and business
  activity monitoring (BAM) technology
• Guarantee optimized, 24x7 access to data
  Deliver non-stop business continuity through
  platform-agnostic application availability,
  supporting high demand reporting environments.
• Deliver enhanced PeopleSoft operations through
  enterprise data management Run PeopleSoft
  applications faster, maximize IT personnel and
  optimize system resources with Sybases data
  management platform.
• Work with proven industry leaders!

9
Sybase Solutions for PeopleSoft

• Data Migration Convoy DM
• Speeding PeopleSoft deployment through
  streamlined data migration

Implementation

• Application Integration BPI Suite
• Facilitating integration across all applications
• Business Process Integration BPI Suite
• Comprehensive management of disparate business
  processes
• Native PeopleSoft Integration PeopleSoft Adapter
• Pre-packaged, native support for the PeopleSoft
  environment
• Native Application Integration Adapter Library
• Eliminating the borders between applications
  Siebel, SAP, etc.

Integration

• Relational Database Adaptive Server Enterprise
• 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

Operation
10
ASE 12.5.0.3
Content

• Introduction
• Multiple tempdbs
• Most relevant performance features
• Most relevant features for operation scalability
• Monitoring Tables
• DBXray

11
ASE 12.5.0.3
Introduction

• Key theme Operational Scalability and
  Manageability
• Interim Release with features
• Some very interesting for PeopleSoft applications
• ASE 12.5.0.3 ESD2 out now, ESD3 on the way
• Certified for a number of OS/PeopleTools
  combinations
• Request certification if your combination is
  currently not certified

12
ASE 12.5.0.3
Multiple TempDBs (MTDB)

• User-createdCREATE TEMPORARY DATABASE
  MyTempDBON MyDataDev ltsizegt LOG ON MyLogDev
  ltsizegt
• Bind user (sa) or application (PS psoft)EXEC
  sp_tempdb bind, LG, sa, DB, MyTempDB
• Assigned at login for duration of session
• Completely transparentSELECT db_name( _at__at_tempdbid
  )go----------------MyTempDB(1 row affected)
• Res Gov limits continue to hold

13
ASE 12.5.0.3
MTDB Benefits

• Increased availability
• Tempdb no longer single point-of-failure
• Increased productivity
• Developers can leverage MTDB instead of regular
  databases
• Prioritize users and application
• Support consolidation and service level
  agreements
• Increased performance
• Decreased contention (25) and use of fast unsafe
  devices
• Improved administration
• Administrators will always have temp areas
• Improved space management
• Add or remove space as needed for temp space

14
ASE 12.5.0.3
Some performance features

• Lazy writes for tempdbs
• No physical IO for commit, allocations, system
  table changes
• 22 in in-house test (OLTP transactions
  re-routed through SELINTO)
• SELECTINTO 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

15
ASE 12.5.0.3
Sampling for Update Statistics

• Reads rows from random pages to build
  histogramsUPDATE 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
• Why isnt it used for UPDATE STATISTICS MyTable ?
• Can dramatically speed up operation
• 2,466 s to 399 s for 23 sampling in one in-house
  test.
• Your mileage will vary
• Its a sample not a population
• So results will vary from those of a full scan
• The smaller the sample
• Out-of-bounds biggest risk
• Can specify globally using sp_configure

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

17
ASE 12.5.0.3
Monitoring Tables

• New interface to performance and diagnostics data
• Full power of SQL
• Low overhead proxy tables on native RPCs
• 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

18
ASE 12.5.0.3
Monitoring tables

• Getting information is easy
• 1gt SELECT TableName, Description FROM monTables
  WHERE TableName 'monProcessSQLText'
• 2gt go
• TableName Description
• ------------------------------
  --------------------------------------------------
  --------------------------------------------------
  --------------------------------------------------
  -----------------------
• monProcessSQLText Provides the SQL
  text that is currently being executed. The
  maximum size of the SQLText returned can be tuned
  by use of the 'max SQL text monitored'
  configuration option
• (1 row affected)

19
ASE 12.5.0.3
Monitoring Tables

• Samples use of monOpenObjectActivity
• 1gt SELECT DBID, ObjectID, IndexID,
  OptSelectCount, UsedCount
• 2gt FROM monOpenObjectActivity WHERE ORDER BY
• 3gt 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
• ---------------------------8lt---------------------
  
• 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

20
ASE 12.5.0.3
Monitoring Tables

• Sample use of monOpenObjectActivity
• 1gt SELECT O.name AS 'TableName', M.IndexID,
  M.LogicalReads, M.PhysicalReads
• 2gt FROM monOpenObjectActivity M,
  HRPAY8..sysobjects O
• 3gt WHERE DBID 4
• 4gt AND M.ObjectID O.id
• 5gt AND LogicalReads gt 10000
• 6gt 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

21
ASE 12.5.0.3
DBXray

• ASE Performance Monitoring Solution
• Graphical performance monitoring environment
• Built on Monitoring Tables so 12.5.0.3 or later
• Collaboratively developed by Sybase and BMC
• Real-time information on system resources and
  performance
• Server level, users, databases, device, network,
  data and procedure caches
• Drilldown to detailed information
• Identifies problems and provides recommendations
• Configurable limits
• Alerts
• Stand-alone or downloaded into browser
• Main screen (next slide) provides overview

22
24 hr Graph
Connections Current and HWM
Alerts and Hints
Data Cache efficiency
Errorlog
Space usage
Locks
23
ASE 12.5.0.3
Summary

• Lazy writes for tempdbs really helps with the
  sorts
• Improved housekeeper means less maintenance for
  all the DOL tables
• Monitoring Tables are really useful various
  uses during rest of presentation

24
Server tuning
Agenda

• Resources
• A discussion around disk, memory, CPU, locks and
  network
• What are these spinlocks I hear so much about ?
• Configuring ASE resources
• Issues and approaches

25
Server Tuning
Why is everybody so worried about the disks ?

• Speed comparison
• CPU 100s MHz-GHzs (1GHz means one cycle in
  1ns)
• RAM 40-80 ns
• Disk milliseconds to 10s of milliseconds, i.e.
  around a factor of a million slower
• Adrian Cockcroft, Sun Performance and Tuning,
  11 Quick Tips and Recipes
• The system will usually have a disk bottleneck
• You will often be told that the system is not
  I/O bound
• After first pass tuning, the system will still
  have a disk bottleneck
• The D in ACID
• It is the persistent storage for a database

26
Server Tuning
Disks Considerations and usage patterns

• Considerations
• JBOD vs. Intelligent array
• Controllers and On-board cache
• RAID level
• Raw vs. file system
• Devices and segments
• Random small reads
• The major workload for an OLTP system
• Sequential reads
• DSS or reporting - larger scans
• Query tuning issues
• Writes
• Logging (ACID)
• Data cache washing
• HK, checkpoint
• Splits and other system writes
• Sorts

27
Server Tuning
Memory usage

• Resources
• E.g. number of xyz, user connections
• Data Caches
• Named caches
• Cache sizing
• Cache binding
• Pool or not to pool
• APF
• Proc cache
• Plans
• Dynamic SQL
• Views
• Sort headers

28
Server Tuning
Data Caches

• When creating a cache we remove memory from
  objects!
• 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

29
Server Tuning
Other memory aspects

• Procedure cache is important do not starve
• Large amounts of memory go to resources
• E.g. number of open objects
• Sp_monitorconfig is very, very useful
• Dynamic memory configuration
• Requires explicit default data cache and
  procedure cache sizes
• Can save headroom and increase resources without
  rebooting

30
Server Tuning
CPU

• Complex to size but usually fairly
  straight-forward to monitor
• Considerations
• 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

31
Server Tuning
CPU

• 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 decreases TLB misses and
  can give 15

32
Server Tuning
Sample Sysmon

• Kernel Utilization
• ------------------
• Engine Busy Utilization
• Engine 0 94.8
  
• Engine 1 94.3
  
• Engine 2 96.5
  
• Engine 3 96.5
  
• ----------- ---------------
  ----------------
• Summary Total 382.2
  Average 95.5
• CPU Yields by Engine per sec
  per xact count of total
• ------------------------- ------------
  ------------ ---------- ----------
• Engine 0 0.2
  0.5 10 40.0
• Engine 1 0.1
  0.4 8 32.0
• Engine 2 0.1
  0.3 6 24.0
• Engine 3 0.0
  0.0 1 4.0
• ------------------------- ------------
  ------------ ----------
• Total CPU Yields 0.4
  1.2 25

33
Server Tuning
Network

• Network level
• Ensure no Collisions
• OS level
• Loopback doesnt buy much and causes some small
  amount of extra work for ASE
• Check configuration, e.g. lttcp_deferred_ack_interv
  algt
• Configure max network packet size and provide
  more memory (add nw mem or def nw pkt sz)
• Configure in application too See appendix
• VerifySELECT spid, cmd, network_pktsz FROM
  sysprocesses

34
Server Tuning
DOL or APL is not just locks.

• Difference between APL and DOL
• Contention
• Storage
• Space consumption
• Space management
• Performance
• 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
• No transaction lock contention

35
Server Tuning
So, is APL or DOL best ?

• Not taking any lock issues 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 -gt APL
• Locks table and requires space
• Potential lock conflicts
• Use Monitoring Tables and / or sp_objectstats to
  identify tables
• Is it worth it ?
• Performance-wise 10-25

36
Server Tuning
What are these spinlocks anyway ?

• 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
• Semaphores, spinlocks, queues, 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( lock )
• So, issue is
• Spinlocks are for short term locks
• When there is contention spinlocks consume lots
  of CPU

37
Server Tuning
Configuring ASE resources

• spconfig125.sql is insufficient
• Even for install
• Remedied in PT8.44
• 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
• Running out of locks during load easily fixed
• Lock promotion or data pages locking
• sp_monitorconfig a good friend
• Sample next slide

38
Server Tuning
sp_monitorconfig

• 1gt EXEC sp_monitorconfig 'all'
• Usage information at date and time Feb 20 2003
  709PM.
• 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

39
Server Tuning
Issues

• Tuning disks
• Spinlock contention
• To cache or not
• Locking issues
• Contention and deadlocks
• Spinlock contention
• APL
• Running reorg

40
Server Tuning
Tuning disks

• Basics still rule
• Separate data and log
• In tempdb too
• Raw is faster then FS
• FS with DSYNC off is very useful for tempdb
• Controller caches
• Helps writes for tempdb offset by 12.5.0.3 lazy
  writes
• Pre-fetch helps sequential scans doesnt help
  random reads
• RAID
• RAID 5 prioritizes system cost by sacrificing
  writes performance offset by controller cache
  and no de-staging
• Segments
• Helps sequential scans, APF and allocations
• More benefit from with JBOD less so with HW
  RAID and caches

41
Server Tuning
Spinlock contention

• Closely monitor using sp_sysmon
• Assess engine utilization
• Spinning causes high CPU/engine load
• Fewer engines less contention
• 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
• APL helps

42
Server Tuning
To cache or not

• You take memory away from default data cache !
• Make sure it counts
• What objects to move
• Prioritized
• Highest logical reads
• Highest logical reads per Mb (Mon Tables
  sp_spaceused)
• Tempdb
• Log
• Do not forget procedure cache
• Plans, views, prepared statements, sort headers

43
Server Tuning
Locking issues

• Running out of locks when e.g. loading ?
• Either configure lock promotion or use data pages
  locking changing between data rows and data
  pages is just updating status bit in system table
• Contention and deadlocks
• Trace with sp_objectstats, print deadlock
  information and Mon Tables
• To APL ?
• Space and performance benefits
• During cursors scans locks are held on APL tables
  not on DOL tables
• Updatable cursors requires unique index on APL
• Can take heavy hit on contention watch for DML
• Useful 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
Server Tuning
Running reorg

• HK in 12.5.0.3 helps a lot
• Fragmentation affects
• Space usage
• Index scans
• Table scans
• Clustering ratios is taken into account by
  optimizer
• System Administration Guide has guidelines when
  to reorg

45
Server Tuning
Summary

• PeopleSoft applications require lots of resources
• Dynamic memory configuration and sp_monitorconfig
  helps size and adapt
• Disk sub-systems are always important for
  databases
• Spinlock contention is very costly
• Number of engines and decompose spinlocks
• Locking scheme is not only about locking

46
Query tuning
Agenda

• Background
• What are the issues tuning queries in a
  PeopleSoft environment
• Optimizer workflow compared to application
  behavior
• Tools
• Same old and few, but important, new
• Issues and approaches

47
Query Tuning
General issues

• Sub-optimally performing queries and DML due to
• Undesirable table scans
• Wrong index
• Wrong join order
• JTC
• Subquery attachment
• Direct vs. deferred DML
• Costs, logical io vs. physical io, indexes, join
  orders
• Basics

48
Query Tuning
Specific issues

• Size
• Number of tables, columns, indexes
• Number of queries
• Getting the actual SQL
• And the values used in conditions
• So when an actual query is identified and the
  query text and plan is at hand, the most work is
  done.

49
Query Tuning
Optimizer workflow

• Get relevant conditions SARGs, ORs, joins etc
• Optimizer can't use e.g. monthly_payment 12
  144
• PeopleSoft applications generally good with this
• For each SARG found, cost it
• Some SARGs can't be costed using statistics, e.g.
  column function()
• Apps generally good with data types and
  expression etc.
• However, statistics are needed.
• When costing is done, assess join orders
• With certain limitations, try all combinations
  and go for cheapest

50
Query Tuning
Optimizer statistics

• Histograms
• Detailed information on column values
• Used when value is known, e.g. LAST_NAME
  'Smith'
• Not used for joins
• UPDATE STATISTICS ltTABLE NAMEgt only gives
  histograms on leading (1st) column
• Default 20 values
• Density (or selectivity)
• Statistic information that describes level of
  duplicates in column or combination or columnsor
• Statistic information that describes how many
  rows an equality condition returns
• Table and index level statistics
• Sizes in pages, row lengths, index levels,
  clustering ratios

51
Query Tuning
Toolset

• Much is the same few are new
• Showplan, 302, 310
• Statistics time and io
• Monitoring Tables, Monitor Server, Historical
  Server, DBXray
• OS level monitoring (disk, system time, context
  switches)
• sp_sysmon, device stats, segments, caches
• sp_help, sp_helpindex, optdiag, sp_modifystats,
  sp__optdiag, etc
• SELECT COUNT() FROM lttable listgtWHERE
  ltconditions from querygt
• SELECT ltcolumngt, COUNT()FROM lttablegt WHERE
  ltconditiongtGROUP BY ltcolumngt
• SQL Expert
• Ribo, auditing, trace 11201-5, sp_showplan,
  dbcc cursor, sp_objectstats
• dbcc pss( , , 3 ), dbcc cursor, trace files

52
Query Tuning
Sybase DB Expert option for ASE

• Identify problematic SQL
• Monitor SQL from Monitor Tables
• Analyze query plans
• Optimize SQL
• SQL transformations and optimization with
  Abstract Plans.
• Index recommendation
• Review new indexes to improve performance and
  perform impact analysis on index creation
• Analyze performance changes due to sp_configure
  changes
• Monitor query plan changes
• Monitor and alter on query plan changes
• Compare query plan changes in different database
  environments
• Backup and recover query plan performance

53
Query Tuning
Trace files

• PeopleSoft Batch Statistics
• (All
  timings in seconds)
• Encoding Scheme Used Ansi
• R e t r i e v e C o m p i
  l e E x e c u t e F e t c h STMT
  TOTALS
• Statement Count Time Count
  Time Count Time Count Time Time
  SQL
• APIBNN 0 0.00 0
  0.00 3199368 30.41 0 0.00 30.41
  1.40
• APISSB 0 0.00 0
  0.00 887154 8.60 0 0.00 8.60
  0.39
• COMMIT 0 0.00 0
  0.00 29 3.97 0 0.00 3.97
  0.18
• CONNECT 0 0.00 0
  0.00 107 7.39 0 0.00 7.39
  0.34
• DISCONNECT 0 0.00 0
  0.00 107 2.91 0 0.00 2.91
  0.13
• PSPAGERT_S_AGERT 1 2.93 1
  1.24 18 3.88 18 1.05 9.10
  0.42
• PSPCKSGL_S_LINE 1 0.00 1
  0.00 1 0.07 1 0.01 0.08
  0.00
• PSPCRLTB_S_CRLTB 7 2.78 7
  0.70 7 1.63 7 1.26 6.37
  0.29
• PSPCUPDT_I_CHK 1 0.00 1
  0.00 2815 6.63 0 0.00 6.63
  0.30
• PSPCUPDT_I_DED 1 0.08 1
  0.04 46166 70.59 0 0.00 70.71
  3.25
• PSPCUPDT_I_SPCL 1 0.01 1
  0.00 22520 31.71 0 0.00 31.72
  1.46

54
Query Tuning
Issues

• Are sub-optimally performing queries a problem
• Finding problematic queries
• Getting query text
• Finding heavy hit objects
• Optimizer issues
• Indexing
• Still no progress when and how to force plans

55
Query Tuning
Are sub-optimally performing queries the problem ?

• Short answer
• yes, without a doubt
• Indicators
• Long response times (duh)
• High CPU usage corresponding to high logical
  reads (sp_sysmon)
• Spinlock contention on default data cache
• Application tracing
• Comparison with baseline
• Any query performing worse than business
  requirements is, by definition, sub-optimally
  performing

56
Query Tuning
Finding problematic queries

• Combination
• Monitoring Tables
• Application knowledge
• Tables used
• Best is PeopleSoft trace files
• R e t r i e v e C o m p i l
  e E x e c u t e F e t c h STMT TOTALS
• Statement Count Time Count
  Time Count Time Count Time Time
  SQL
• PSPDARRY_S_LIFE 1 3.29 1
  1.40 1 111.99 81073 27.44 144.12
  6.61

57
Query Tuning
Getting query text

• Monitoring Tables
• Does not give text but plans and much much more
• Dbcc pss( ltsuidgt, ltspidgt, 3 ) see appendix
• Prints query plans for spids cursors
• Dbcc traceon( 112?? ) see appendix
• PS_SQLSTMT_TBL
• Ribo
• PeopleSoft app svr and proc scheduler trace
  files
• Trace mask 1 provides statements 2 the actual
  values
• SQL Tracing Bitfield
• Bit Type of tracing
• --- ---------------
• 1 - SQL statements
• 2 - SQL statement variables
• 4 - SQL connect, disconnect, commit and
  rollback
• 8 - Row Fetch (indicates that it
  occurred, not data)
• 16 - All other API calls except ssb

58
Query Tuning
Optimizer statistics

• Identify tables and columns through Monitoring
  Table, sp_objectstats or application trace files
• UPDATE ALL STATISTICS lttable namegt USING 200
  VALUES on 10,000 tables/300,000 is maybe not the
  path to tread
• Turn on tracing and parse file to build commands
  from table- and column names - See appendix
• Or use UPDATE ALL STATISTICS for prioritized
  tables
• Add individual column statistics !
• Assess update frequency
• How to do it faster
• Leverage parallel update statistics
• Sampling for update statistics is useful
• Consider patching using optdiag for predictable
  or well-known column histograms
• Use sp_modifystats to set density

59
Query Tuning
Costing issues

• The queries are usually SARGs where statistics
  can be used
• Histogram only on leading column
• Only 20 steps
• Good example of costing issue
• OFF_CYCLE N Hits gt99 of all rows
• Column is not indexed hence no statistics
• Optimizer estimates 10 hit rate
• This affects join order
• 1gt SELECT OFF_CYCLE, COUNT()
• 2gt FROM PS_PAY_LINE GROUP BY OFF_CYCLE ORDER BY 2
• 3gt go
• OFF_CYCLE
• --------- -----------
• Y 18
• N 938775
• (2 rows affected)

60
Query Tuning
Other optimizer related issues

• Extra conditions for transitive closure can help
• If t1.c1 t2.c2 AND t2.c2 t3.c3 then adding
  t1.c1 t3.c3 can help
• Setting sp_configure 'enable sort-merge joins and
  JTC'
• May be an alternative
• Causes longer optimization

61
Query Tuning
Indexing

• A lot of care has gone into the indexing,
  however
• Improvements yield good results
• Can drop indexes to speed up DML (use Mon Tables
  or sp_objectstats)
• Most frequent issues
• Index key order Instead of (country, state, zip)
  consider (zip, state, country) if zip is supplied
  in queries
• Make sure that useful columns are included in
  useful indexes not the same as stats on columns
• Often indexes cover queries ensure that's your
  current queries

62
Query Tuning
When and how to force plans

• Forcing plans
• does not solve the generic issue, e.g. missing
  index, insufficient statistics
• does not taking changing conditions into account
• Sometimes the only option due to
• optimizer limitations
• unreasonable amount of effort to trouble-shoot
• For SQL
• generated in the application use Abstract Query
  Plans
• stored in PS_SQLSTMT_TBL - UPDATE PS_SQLSTMT_TBL
  SET or load using Data Mover
• Forcing join orders
• Set forceplan on is not an option
• Can use AQP
• By forcing certain indexes a join order can be
  preferred even a table scan on one table can be
  better than wrong join order

63
Query Tuning
How to force plans stored in PS_SQLSTMT_TBL

• Update of PS_SQLSTMT_TBL
• UPDATE PS_SQLSTMT_TBL SET STMT_TEXT "SELECT
  A.EMPLID ,A.EMPL_RCD FROM PS_JOB A ( INDEX 0 )
  ,PS_PERSONAL_DATA P WHERE A.COMPANY1 AND
  A.PAYGROUP2 AND A.EFFDTlt3 AND A.EMPL_STATUS
  IN ('A','P','Q','U') AND P.EMPLIDA.EMPLID AND
  P.PER_STATUS'E' AND NOT EXISTS (SELECT 'X' FROM
  PS_JOB F WHERE F.EMPLIDA.EMPLID AND
  F.EMPL_RCDA.EMPL_RCD AND F.EFFDTlt4 AND
  F.EFFDTgtA.EFFDT AND (F.EFFDTlt5 OR (F.COMPANY6
  AND F.PAYGROUP7 AND F.EMPL_STATUS IN
  ('A','P','Q','U') AND F.EFFSEQ(SELECT
  MAX(G.EFFSEQ) FROM PS_JOB G WHERE
  G.EMPLIDF.EMPLID AND G.EMPL_RCDF.EMPL_RCD AND
  G.EFFDTF.EFFDT)))) AND A.EFFSEQ (SELECT
  MAX(H.EFFSEQ) FROM PS_JOB H WHERE
  H.EMPLIDA.EMPLID AND H.EMPL_RCDA.EMPL_RCD AND
  H.EFFDTA.EFFDT) AND NOT EXISTS (SELECT 'X' FROM
  PS_PAY_LINE L WHERE L.EMPLIDA.EMPLID AND
  L.EMPL_RCDA.EMPL_RCD AND L.COMPANY8 AND
  L.PAYGROUP9 AND L.PAY_END_DT10 AND
  L.OFF_CYCLE'N') ORDER BY A.EMPLID ASC"
• WHERE PGM_NAME 'PSPPARRY'
• AND STMT_TYPE 'S'
• AND STMT_NAME 'HIRE'
• go
• Using Data Mover
• SET LOG PSPSVRET.LOG
• STORE PSPPARRY_S_HIRE
• SELECT A.EMPLID ,A.EMPL_RCD

64
Query Tuning
Summary

• Tuning queries usually yield much
• Certain tasks are harder in the PeopleSoft
  environment
• Some due to number of objects and statements
  executed
• Other due to current limitations, e.g. getting
  query text
• Other tasks have good support
• Updating PS_SQLSTMT_TBL to force plan
• Trace file really has a lot of useful information
• Take good care of the statistics and the
  optimizer will be good to you

65
Recommendations

• Know thy disks
• And what goes where
• Establish statistics
• Histograms on columns in conditions
• Tune problematic queries
• Can make huge difference
• Turn tables without DML into APL
• Use Monitoring Tables or sp_objectstats
• Increase packet size
• In ASE as well as App Server and Process
  Scheduler
• Monitor performance
• Establish baseline and know impact of more load
  or tuning

66
More Information

• Sybase white papers and tech notes
• PSFT red papers and tech notes
• Misc forums
• Isug.com
• Codexchange
• http//www.isug.com/Sybase_FAQ/
• Freeware (http//www.edbarlow.com is one)
• User groups and meetings
• Discussion groups
• Customer connection
• Forums.sybase.com

67
ASE124 Tuning ASE for PeopleSoft Applications
Stefan.Karlsson_at_Sybase.comASE EvangelistAugust
2003
68
Appendix
Content

• Setting network packet size with pscfg.exe
• Small case study with Monitoring Tables
• Sample output on Finding query text
• Building update statistics commands from trace
  file output

69
Configure Network Packet Size

• In config file
• Database Options
• SybasePacketSize8192
• Or using pscfg.exe

70
Monitoring Tables

• Case study
• Mixed workload OLTP and DSS
• OLTP 200 concurrent users
• DSS 10 concurrent query streams
• Overall server information collected to check
  health
• Object level statistics collected
• Process level collected
• Detailed process level information collected
• Using maximum resources

71
Monitoring Tables

• Server level statistics
• Server level information was collected from
  querying monEngine, monDataCache, and monDeviceIO
  tables
• CPU Usage

72
Monitoring Tables

• Server level statistics (contd)
• First table show Data Cache usage, and 2nd table
  shows Procedure cache usage

73
Monitoring Tables

• Server level statistics (contd)
• Disk Usages

74
Monitoring Tables

• Object level information
• Object level information can be collected from
  monOpenObjectActivity and monProcessObject tables

75
Monitoring Tables

• Process level information
• Process level information can be collected from
  monProcess, monProcessLookUp, monProcessActivity,
  and monProcessObject tables
• User can find out all the processes using cpus
  by issuing following querySELECT S.SPID,
  S.CpuTime, S.LineNumber, T.SQLTextFROM
  monProcessStatement S, monProcessSQLText TWHERE
  S.SPID T.SPIDORDER BY S.CpuTime DESC

76
Monitoring Tables

• Process level information (contd)
• Result set (partial)

77
Query Tuning
ASE Optimizer Step 1

• Find SARGs
• SARGs
• ltcolgt value, ltcolgt gt value, ltcolgt LIKE, BETWEEN
• Non-SARGs
• Function(ltcolgt), col op. col values
• Find Joins
• Joins
• r.a s.b, r.a gt s.b
• OR, Sorts
• PeopleSoft applications are generally good with
  this

78
Query Tuning
ASE Optimizer Step 2

• Cost SARGs
• By means of statistics or default values
• Costed using statistics
• ltcolgt value, where ltcolgt has statistics
• R.a s.b, where both column a and b has
  statistics
• Costed not using statistics
• ltcolgt gt value op. value, ltcolgt gt function(value),
  ltgt

79
Query Tuning
What is cost ?

• Time to execute
• Takes physical IO into account
• Takes parallelism into account
• However
• Thinking about it in terms of logical IOs tend to
  be more productive
• But
• Don't forget physical IO that's how large IOs
  can bite back

80
Query Tuning
Cost - samples

• Indexes decreases cost of access to datarows
• The big cost and cost saving are for joins
• Join orders and when Indexes used for joins
• Trivial example with two very small tables
  PowerPointWare
• r with 3 pages and 3 rows per page
• s with 4 pages and 4 rows per page
• Two alternative join order
• Assuming nested loop
• Cost pagesouter ( rowsouter pagesinner )
• r -gt s 3 ( 9 4 ) 39 log IOs
• s -gt r 4 ( 16 3) 52 log IOs

81
Query Tuning
ASE Optimizer Step 3

• For each table, the optimizer identifies
• Best index for SARG
• Best index for join
• Then tests join orders
• Takes short-cuts due to exponentially expanding
  search space set tablecount ltintegergt
• Cost of join order depends on
• Rows in outer table
• Useful (cheap) index

82
Finding Query Text

• Dbcc pss( ltsuidgt, ltspidgt, 3 )
• 1gt dbcc traceon(3604)
• DBCC execution completed. If DBCC printed error
  messages, contact a user with System
  Administrator (SA) role.
• 1gt dbcc pss (0,111,3)
• PSS (any state) for suid 1 - spid 111
• PSS at 0xd491d230
• PSS Status fields
• (333 rows of output)
• PNETBUFP
• T-SQL command (may be truncated) BEGIN TRAN
• PCSS CURSOR top 50 chain

• Not supported and not documented
• Dont call tech support
• It will change
• It may have side effects

83
Finding Query Text

• Dbcc traceon( 11202 ) from ASE errorlog
• 0100000001112002/10/07 172819.37 server
  TDS_CURS, spid 111 command 'CURDECLARE' (134)
• 0100000001112002/10/07 172819.37 server
  TDS_CURS, spid 111 CURDECLARE text SELECT
  MESSAGE_NBR, MESSAGE_TEXT, MSG_SEVERITY,
  DESCRLONG, LAST_UPDATE_DTTM FROM PSMSGCATDEFN
  WHERE MESSAGE_SET_NBR 180 ORDER BY MESSAGE_NBR
  FOR READ ONLY
• 0100000001112002/10/07 172819.37 server
  TDS_CURS, spid 111 command 'CURINFO' (131)
• 0100000001112002/10/07 172819.37 server
  TDS_CURS, spid 111 command 'CUROPEN' (132)
• 0100000001112002/10/07 172819.40 server
  TDS_CURS, spid 111 command 'CURFETCH' (130)
• 0100000001112002/10/07 172820.72 server
  TDS_CURS, spid 111 command 'CURFETCH' (130)
• 0100000001112002/10/07 172821.00 server
  TDS_CURS, spid 111 command 'CURFETCH' (130)
• 0100000001112002/10/07 172821.01 server
  TDS_CURS, spid 111 command 'CURCLOSE' (128)

• Not supported and not documented
• Dont call tech support
• It will change
• It may have side effects

84
Finding Query Text
Sample PeopleSoft trace output

• TraceSQL135
• 170222 586 0.210 0.000 6
  RC0 Access ConnectDEC23_DS/HRPAY8/SYSADM/?
• 170222 587 0.030 0.000 6
  RC0 GETSTMT StmtPSPEARRY_S_JOB, length1871
• 170222 588 0.000 0.000 6
  RC0 COM StmtSELECT A.PAGE_NUM ,A.LINE_NUM
  ,A.SEPCHK
• ,A.BENEFIT_RCD_NBR ,A.DED_TAKEN ,A.DED_SUBSET_ID
  ,C.EMPL_TYPE ,C.LOCATION ,C.STD_HOURS
  ,C.EMPL_STATUS ,
• C.DEPTID ,C.COMP_FREQUENCY ,C.BUSINESS_UNIT
  ,C.SETID_DEPT ,C.SETID_JOBCODE ,C.COMPRATE
  ,D.NAME ,D.STATE ,D.SEX
• ,E.SMOKER ,D.HIGHLY_COMP_EMPL_C ,D.BIRTHDATE
  ,C.ANNL_BENEF_BASE_RT ,C.SHIFT_RT ,C.SHIFT_FACTOR
  ,C.FLSA_STATUS
• ,C.DIRECTLY_TIPPED ,C.GVT_LEO_POSITION
  ,C.GVT_PAY_RATE_DETER ,C.SAL_ADMIN_PLAN
  ,Q.FREQUENCY_TYPE
• ,Q.FREQ_ANNUAL_FACTOR ,Q.EFF_STATUS
  ,C.STD_HRS_FREQUENCY ,S.FREQ_ANNUAL_FACTOR
  ,S.EFF_STATUS ,A.COMPANY
• ,A.PAYGROUP ,A.PAY_END_DT ,A.OFF_CYCLE FROM
  PS_PAY_EARNINGS A ,PS_JOB C ,PS_PERSONAL_DATA D
  ,PS_PERS_DATA_EFFDT E
• ,PS_EMPLOYMENT K ,PS_FREQUENCY_TBL Q
  ,PS_FREQUENCY_TBL S WHERE A.COMPANY1 AND
  A.PAYGROUP2 AND A.PAY_END_DT3
• AND A.OFF_CYCLE4 AND A.SINGLE_CHECK_USE IN
  ('N','C') AND A.OK_TO_PAY'Y' AND
  A.PAY_LINE_STATUS IN ('I', 'P',
• 'U') AND A.EMPLIDC.EMPLID AND
  A.EMPL_RCDC.EMPL_RCD AND A.EMPLIDD.EMPLID AND
  C.EFFDT (SELECT MAX(G.EFFDT) FROM
• PS_JOB G WHERE G.EMPLIDC.EMPLID AND
  G.EMPL_RCDC.EMPL_RCD AND G.COMPANYA.COMPANY AND
  G.EFFDTlt5) AND C.EFFSEQ
• (SELECT MAX(H.EFFSEQ) FROM PS_JOB H WHERE
  H.EMPLIDC.EMPLID A ND H.EMPL_RCDC.EMPL_RCD AND
  H.EFFDTC.EFFDT) AND
• A.EMPLIDK.EMPLID AND A.EMPL_RCDK.EMPL_RCD AND
  K.SERVICE_DT IS NOT NULL AND D.EMPLID E.EMPLID
  AND E.EFFDT
• (SELECT MAX(P.EFFDT) FROM PS_PERS_DATA_EFFDT P
  WHERE P.EMPLID A.EMPLID AND P.EFFDT lt 6) AND
  Q.FREQUENCY_ID
• C.COMP_FREQUENCY AND Q.EFFDT (SELECT
  MAX(R.EFFDT) FROM PS_FREQUENCY_TBL R WHERE
  R.FREQUENCY_IDC.COMP_FREQUENCY

85
Optimizer Statistics
Building UPDSTATS commands from trace files

• Parse trace file this non-reliable code snippet
  puts each statement in a file name as the
  statement
• while read A B C D E F G H
• do
• case "H" in
• Stmtlength0-90-9)
• stmtNameH,
• stmtNamestmtNameStmt
• StmtSELECTStmtINSERTStmtUPDATEStmtDELET
  E)
• stmtTextH
• fileNamedirName/stmtName-NULL.sql
• if -n stmtName
• then
• print "stmtText" gt fileName
• else
• print "stmtText" gtgt fileName
• fi
• esac