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

Loading in 2 Seconds...

play fullscreen
1 / 79

ASE106: Tuning ASE for PeopleSoft Applications - PowerPoint PPT Presentation


  • 513 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'ASE106: Tuning ASE for PeopleSoft Applications' - niles


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1

ASE106: Tuning ASE for PeopleSoft Applications

Stefan Karlsson

Stefan.Karlsson@Sybase.com

ASE Evangelist

August 15-19, 2004

comments and caveats
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!
why a peoplesoft specific p t presentation
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
agenda
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
industry leaders an alliance

SCM

CRM

HCM

FMS

PEOPLETOOLS

INTEGRATION

DATA MANAGEMENT

RDBMS

REPLICATION

DATA MIGRATION

Industry Leaders – An Alliance

Get More Value, Get More Done.

strategic alliance partner and customer
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
a comprehensive alliance

Technology/Engineering

Marketing

Sales

Professional Services

Worldwide Support

A Comprehensive Alliance
sybase solutions for peoplesoft
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

agenda1
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
ase 12 5 0 3 performance features
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
ase 12 5 0 3 operational features
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
ase 12 5 0 3 housekeeper improvements
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
ase 12 5 0 3 monitoring tables
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
ase 12 5 0 3 monitoring tables con t
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
ase 12 5 0 3 monitoring tables con t1
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

ase 12 5 1 performance features
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
ase 12 5 1 operational features
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
ase 12 5 2 performance features
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
ase 12 5 2 operational features
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
ase 12 5 x peoplesoft certifications
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
agenda2
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
server resources
Server Resources
  • Considerations, Monitoring and Tuning for the major resources:
    • Storage
    • Memory
    • CPU
    • Locks
    • Network
why is everybody so worried about the disks
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…)
storage considerations
Storage Considerations
  • JBOD vs. SAN/NAS
  • Controller cache
  • RAID Level
  • Raw vs. file system
  • Devices and segments
usage patterns
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
disk monitoring
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

tuning disks
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)
tuning disks con t
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
memory use
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”
data caches considerations
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
data caches monitoring and tuning
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

data caches monitoring and tuning con t
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

data caches monitoring and tuning con t1
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
procedure cache considerations
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

memory consuming resources
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
monitoring configurable resources
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

cpu considerations
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
cpu monitoring and tuning
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
cpu monitoring and tuning con t
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

locking considerations
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
is apl or dol best
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%
locking monitoring
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!
locking tuning
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

spinlocks considerations
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
spinlocks monitoring
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%
spinlocks tuning
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
network considerations and tuning
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
running reorg
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
server tuning summary
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
agenda3
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
query tuning
Query Tuning
  • 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
general query tuning issues
General Query Tuning 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
specific query tuning issues
Specific Query Tuning 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
optimizer workflow
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.
    • More commonly, e.g. column_wo_stats = values
    • Statistics are needed
  • When costing is done, assess join orders
    • With certain limitations, try all combinations and go for cheapest
    • Cheapest is calculated based on above costing: with or w/o statistics
    • Includes all SARG’s: indexed or not, stats or not, …
optimizer statistics
Optimizer Statistics
  • Histograms
    • Detailed information on column values
    • Used when value is known, e.g. LAST_NAME = 'Smith'
    • Not used for joins
    • UPDATE STATISTICS <TABLE NAME> 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
query tuning toolset
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 <table list>WHERE <conditions from query>
    • SELECT <column>, COUNT(*)FROM <table> [WHERE <condition>]GROUP BY <column>
    • SQL Expert
    • Ribo, auditing, trace 1120[12], sp_showplan, dbcc cursorinfo, sp_objectstats, dbcc pss( , , 3 ), trace files
sybase db expert option for ase
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
application trace files
Application 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

PSPCUPDT_S_CHK_TYP 1 0.01 1 0.00 2815 6.00 2815 2.55 8.56 0.39

PSPCUPDT_U_ERN 1 0.00 1 0.00 15201 30.99 0 0.00 30.99 1.42

PSPCUPDT_U_ERN_SGL 1 0.01 1 0.00 2815 6.87 0 0.00 6.88 0.32

PSPCUPDT_U_OTH 1 0.00 1 0.00 21957 31.95 0 0.00 31.95 1.47

PSPDARRY_S_ARR 1 0.50 1 0.17 1 0.75 1 0.00 1.42 0.07

PSPDARRY_S_DED 1 0.22 1 0.00 1 5.68 37159 4.58 10.48 0.48

PSPDARRY_S_DISB 1 1.53 1 1.40 1 19.52 27025 7.74 30.19 1.39

PSPDARRY_S_FSA 1 3.19 1 2.24 1 12.76 20269 5.84 24.03 1.10

PSPDARRY_S_HLTH 1 3.73 1 1.87 1 41.87 28714 16.47 63.94 2.93

PSPDARRY_S_JOBS1 1 1.13 1 0.45 2815 8.32 5630 6.57 16.47 0.76

PSPDARRY_S_JOBS2 1 0.84 1 0.13 2815 7.68 2815 2.86 11.51 0.53

PSPDARRY_S_LIFE 1 3.29 1 1.40 1 111.99 81073 27.44 144.12 6.61

PSPDARRY_S_PENS 1 0.01 1 0.00 1 1.13 1 1.09 2.23 0.10

PSPDARRY_S_PROC 1 0.00 1 0.01 1 0.38 1 0.45 0.84 0.04

query tuning issues agenda
Query Tuning Issues Agenda
  • 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
is sub optimally performing queries a problem
Is sub-optimally performing queries a 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
finding problematic queries
Finding problematic queries
  • Combination:
    • Monitoring Tables
    • Application knowledge
    • Tables used
  • Best is application 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

getting query text
Getting query text
  • Monitoring Tables
    • Does not give text but plans and much much more
  • dbcc pss( <suid>, <spid>, 3 ) – see appendix
    • Prints query plans for spid’s cursors
  • dbcc traceon( 11202 ) – 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

; 32 - Set Select Buffers (identifies the attributes of columns

; to be selected).

; 64 - Database API specific calls

; 128 - COBOL statement timings

; 256 - Sybase Bind information

; 512 - Sybase Fetch information

TraceSQL=131

optimizer statistics1
Optimizer statistics
  • Identify tables and columns through Monitoring Table, sp_objectstats or application trace files
    • UPDATE ALL STATISTICS <table name> USING 200 VALUES on 10,000 tables/300,000 column is maybe not the path to tread
    • But UPDATE ALL STATISTICS can be used for prioritized tables
    • Turn on tracing and parse file to build commands from table- and column names - See appendix
  • 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
    • Watch your ‘number of sort buffers’ setting
costing issues
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 >99% of all rows
    • Column is not indexed hence no statistics
    • Optimizer estimates 10% hit rate
    • This affects join order

1> SELECT OFF_CYCLE, COUNT(*)

2> FROM PS_PAY_LINE GROUP BY OFF_CYCLE ORDER BY 2

3> go

OFF_CYCLE

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

Y 18

N 938775

(2 rows affected)

other optimizer related issues
Other Optimizer Related Issues
  • Optimizer takes operationally related areas into account
    • Large IO’s
    • Fragmentations
    • Cache vs. result set size
  • 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
indexing
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
when and how to force plans
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
forcing plans stored in ps sqlstmt tbl
Forcing 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.COMPANY=:1 AND A.PAYGROUP=:2 AND A.EFFDT<=:3 AND A.EMPL_STATUS IN ('A','P','Q','U') AND P.EMPLID=A.EMPLID AND P.PER_STATUS='E' AND NOT EXISTS (SELECT 'X' FROM PS_JOB F WHERE F.EMPLID=A.EMPLID AND F.EMPL_RCD=A.EMPL_RCD AND F.EFFDT<=:4 AND F.EFFDT>A.EFFDT AND (F.EFFDT<=:5 OR (F.COMPANY=:6 AND F.PAYGROUP=:7 AND F.EMPL_STATUS IN ('A','P','Q','U') AND F.EFFSEQ=(SELECT MAX(G.EFFSEQ) FROM PS_JOB G WHERE G.EMPLID=F.EMPLID AND G.EMPL_RCD=F.EMPL_RCD AND G.EFFDT=F.EFFDT)))) AND A.EFFSEQ= (SELECT MAX(H.EFFSEQ) FROM PS_JOB H WHERE H.EMPLID=A.EMPLID AND H.EMPL_RCD=A.EMPL_RCD AND H.EFFDT=A.EFFDT) AND NOT EXISTS (SELECT 'X' FROM PS_PAY_LINE L WHERE L.EMPLID=A.EMPLID AND L.EMPL_RCD=A.EMPL_RCD AND L.COMPANY=:8 AND L.PAYGROUP=:9 AND L.PAY_END_DT=:10 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

;

query tuning summary
Query Tuning Summary
  • Tuning queries usually yield much
  • Certain tasks are (much) 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
agenda4
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
recommendations
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
  • Balance CPU from OS perspective
    • Do not starve the application
more information
More Information
  • Sybase white papers and tech notes
  • PSFT red papers and tech notes
  • Misc forums
    • www.ISUG.com
    • Codexchange
    • www.isug.com/Sybase_FAQ/
    • Freeware (www.sypron.nl and www.edbarlow.com are two examples)
    • User groups and meetings
  • Discussion groups
    • Customer connection
    • news://forums.sybase.com
slide73

ASE106: Tuning ASE for PeopleSoft Applications

Stefan.Karlsson@Sybase.com

ASE Evangelist

August 2004

appendix
Appendix
  • Setting network packet size with pscfg.exe
  • Sample output on Finding query text
  • Building update statistics commands from trace file output
configuring network packet size
Configuring Network Packet Size
  • In config file

[Database Options]

SybasePacketSize=8192

  • Or using pscfg.exe
finding query text
Finding Query Text

Dbcc pss( <suid>, <spid>, 3 )

1> dbcc traceon(3604)

DBCC execution completed. If DBCC printed error messages, contact a user with System Administrator (SA) role.

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

CSS (0xd7f7e000) cnext=0x0 cstmt=0xd7f69528 cdseg=0x0 cstep=0x0 cid=7275038 cname='CURSOR01' cnlen=8 cproc_name='' …

… (17 rows of output)

CURSOR cstmt plan:

QUERY PLAN FOR STATEMENT 1 (at line 1).

STEP 1

The type of query is INSERT.

The update mode is direct.

Worktable1 created, in allpages locking mode, for DISTINCT.

FROM TABLE

PSOPRDEFN

A

Nested iteration.

Using Clustered Index.

Index : PS_PSOPRDEFN

Forward scan.

… (rest of this plan and potentially 49 more cursors)

Not supported and not documented:

  • Don’t call tech support
  • It will change
  • It may have side effects
finding query text1
Finding Query Text
  • Dbcc traceon( 11202 ) – from ASE errorlog

01:00000:00111:2002/10/07 17:28:19.37 server TDS_CURS, spid 111: command 'CURDECLARE' (134)

01:00000:00111:2002/10/07 17:28:19.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

01:00000:00111:2002/10/07 17:28:19.37 server TDS_CURS, spid 111: command 'CURINFO' (131)

01:00000:00111:2002/10/07 17:28:19.37 server TDS_CURS, spid 111: command 'CUROPEN' (132)

01:00000:00111:2002/10/07 17:28:19.40 server TDS_CURS, spid 111: command 'CURFETCH' (130)

01:00000:00111:2002/10/07 17:28:20.72 server TDS_CURS, spid 111: command 'CURFETCH' (130)

01:00000:00111:2002/10/07 17:28:21.00 server TDS_CURS, spid 111: command 'CURFETCH' (130)

01:00000:00111:2002/10/07 17:28:21.01 server TDS_CURS, spid 111: command 'CURCLOSE' (128)

Not supported and not documented:

  • Don’t call tech support
  • It will change
  • It may have side effects
finding query text2
Finding Query Text

TraceSQL=135

17:02:22 586 0.210 0.000 #6 RC=0 Access Connect=DEC23_DS/HRPAY8/SYSADM/?

17:02:22 587 0.030 0.000 #6 RC=0 GETSTMT Stmt=PSPEARRY_S_JOB, length=1871

17:02:22 588 0.000 0.000 #6 RC=0 COM Stmt=SELECT 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.COMPANY=:1 AND A.PAYGROUP=:2 AND A.PAY_END_DT=:3

AND A.OFF_CYCLE=:4 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.EMPLID=C.EMPLID AND A.EMPL_RCD=C.EMPL_RCD AND A.EMPLID=D.EMPLID AND C.EFFDT= (SELECT MAX(G.EFFDT) FROM

PS_JOB G WHERE G.EMPLID=C.EMPLID AND G.EMPL_RCD=C.EMPL_RCD AND G.COMPANY=A.COMPANY AND G.EFFDT<=:5) AND C.EFFSEQ=

(SELECT MAX(H.EFFSEQ) FROM PS_JOB H WHERE H.EMPLID=C.EMPLID A ND H.EMPL_RCD=C.EMPL_RCD AND H.EFFDT=C.EFFDT) AND

A.EMPLID=K.EMPLID AND A.EMPL_RCD=K.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 <= :6) AND Q.FREQUENCY_ID=

C.COMP_FREQUENCY AND Q.EFFDT= (SELECT MAX(R.EFFDT) FROM PS_FREQUENCY_TBL R WHERE R.FREQUENCY_ID=C.COMP_FREQUENCY

AND R.EFFDT<=:7 ) AND S.FREQUENCY_ID=C.STD_HRS_FREQUENCY AND S.EFFDT= (SELECT MAX(T.EFFDT) FROM PS_FREQUENCY_TBL

T WHERE T.FREQUENCY_ID=C.STD_HRS_FREQUENCY AND T.EFFDT<=:8 ) ORDER BY A.COMPANY ASC ,A.PAYGROUP ASC ,A.PAY_END_DT

ASC ,A.OFF_CYCLE ASC ,A.PAGE_NUM ASC ,A.LINE_NUM ASC ,A.SEPCHK ASC

17:02:22 589 0.000 0.000 #6 RC=0 Bind-1, type=SQLPBUF, length=3, value=GBI

17:02:22 590 0.000 0.000 #6 RC=0 Bind-2, type=SQLPBUF, length=3, value=P10

17:02:22 591 0.000 0.000 #6 RC=0 Bind-3, type=SQLPDTE, length=10, value=2000-12-15

17:02:22 592 0.000 0.000 #6 RC=0 Bind-4, type=SQLPBUF, length=1, value=N

17:02:22 593 0.000 0.000 #6 RC=0 Bind-5, type=SQLPDTE, length=10, value=2000-12-15

17:02:22 594 0.000 0.000 #6 RC=0 Bind-6, type=SQLPDTE, length=10, value=2000-12-15

17:02:22 595 0.000 0.000 #6 RC=0 Bind-7, type=SQLPDTE, length=10, value=2000-12-15

17:02:22 596 0.000 0.000 #6 RC=0 Bind-8, type=SQLPDTE, length=10, value=2000-12-15

17:02:33 597 10.650 0.000 #7 RC=0 Access Connect=DEC23_DS/HRPAY8/SYSADM/?

17:02:33 598 0.060 0.000 #7 RC=0 GETSTMT Stmt=PSPEARRY_S_STATE, length=225

17:02:33 599 0.000 0.000 #7 RC=0 COM Stmt=SELECT A.STATE FROM PS_STATE_TAX_DATA A WHERE

A.EMPLID=:1 AND A.COMPANY=:2 AND A.EFFDT= (SELECT MAX(B.EFFDT) FROM PS_STATE_TAX_DATA B WHERE B.EMPLID=A.EMPLID

AND B.COMPANY=A.COMPANY AND B.EFFDT<=:3 ) AND A.UI_JURISDICTION='Y'

17:02:33 600 0.000 0.000 #7 RC=0 Bind-1, type=SQLPBUF, length=11, value=E2_00005068

17:02:33 601 0.000 0.000 #7 RC=0 Bind-2, type=SQLPBUF, length=3, value=GBI

17:02:33 602 0.000 0.000 #7 RC=0 Bind-3, type=SQLPDTE, length=10, value=2000-12-15

parsing trace files
Parsing 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

Stmt*length=[0-9][0-9]*)

stmtName=${H%%,*}

stmtName=${stmtName#Stmt*=}

;;

Stmt=SELECT*|Stmt=INSERT*|Stmt=UPDATE*|Stmt=DELETE*)

stmtText=${H#*=}

fileName=$dirName/${stmtName:-NULL}.sql

if [[ -n ${stmtName} ]]

then

print "${stmtText}" > $fileName

else

print "${stmtText}" >> $fileName

fi

;;

esac

done < $inputFile

  • Get table and column names from the file(s)
  • Build update statistics statements from lists of tables and columns

SELECT

'UPDATE STATISTICS ' + O.name + ' (' + C.name + ' ) ‘ + ' USING 200 VALUES ‘ + ' WITH CONSUMERS = 8 ‘ + char(10) + 'go'

FROM sysobjects O, syscolumns C

WHERE O.id = C.id

AND O.name IN (

'PSACCESSPRFL'

, 'PS_TAXGR_CMPNT_TBL'

)

AND C.name IN (

'ADDL_NBR'

, 'VDI_EFF_TAX_YR'

)

go