Skip to content

TASM

TASM

  • Account String = PG + Account Name = PG + [Account ID] + Expansion Symbols
  • Objects apply to Filters/Throttles:
    • Context objects ("who"): user, profile, PG, acct name, acct string (PG + acct name)
    • Query objects ("where"): DB, TB, VW, Macros, SP. Must meet ALL selected
      • Sub: FT Scan, Join Type, Min/Max Rows, Min Step Time
    • Query characteristics (what): Stmt Type (DML/DDL/Sel), All AMP, Step Row Count, Final Row Count, Est proc time, Join Type (No/Prod/etc), FT Scan
  • Mandatory workloads: Default, R,H,M,L
  • TASM evaluates at request level. filters and throttles accept/reject requests, not stmt
  • Rules: sys filters, sys throttles, WD
    • Appliances (26xx) have no workloads, exceptions, states
    • have fixed attributes such as classification rules, object rules etc
    • have working values such as enabled, sys throttle limits etc that depend on
      • state: enabled/disabled session control, filters, sys throttles lim, psf weights
      • planned env: SLGs, exception enable/disable
    • Filters/Throttles, unless specified as global, will be ignored if no associated objects
  • Sessions
    • query sessions: limit # of logged on sessions per group. collective/individual/member
    • utility limits: overrides MaxLoadTask
    • utility sessions: # of sessions allocated to utilities
Policy Obj Acc Filter Qry Rsrc Filter Obj Throttle Utility Throttle
Enforcement Reject Reject Reject/Delay Reject/Delay
Eval Dispatcher Dispatcher After WD assign After WD assign
SQL Type (All/DDL/DML/Sel) Yes Yes Yes No
Filter * SQL Type (DML/DDL/Sel)
Objects Ctx+DB Ctx+DB Ctx+DB+PG Util Type
Warn Yes Yes No No
Restrict Obj Comb Yes Yes No No
Throttle Collectively No No Yes No
Confidence Lvl No Yes Only step time N/A
Resource Limit None Any Only Step Time N/A
Bypass Users Yes Yes Yes No
  • *: Max Rows, Final Rows, Max Proc Time, Join Type, No FTS, Queryband

Events and State Matrix

  • Unplanned System Events:
    • Unqualified: Node, AMP, PE, Gateway failures.
    • qualified events: Avail AWT, Flow control, CPU Util, CPU Skew and User
  • TD14+ Unplanned Workload Events: Active Req, Arrivals, AWT Wait time, CPU Util, Delay Q depth/time, SLG Resp/throughput
  • Planned events: Time of day, day of month/week, month of year
  • When system transitions to new state, and if delay queue limits are smaller, delay queue reevaluations will let existing work to complete

Filters

  • Types: Obj Acc, Qry Resource. Scope: Global or object specific
  • Query Resource
    • SQL Type: All/DDL/DML/SEL
    • Query Resource conditions (either AND/OR)
    • Enable by default, Global Rule, Limit users that are members (of Ctx objects)
  • can use Users, Accounts (Name only, no PG) and Profiles to group users
  • query access sys filters: based on object names
  • query resource sys filter: based on resource usage estimates
  • Can be associated with >1 objects.
  • If multiple context+query objects used, then any will trigger it, unless Restrict Object Combination is selected, then all possible pairs of query and context objects are formed
  • ROT: Use Join Type or FTS as as sub-criteria of specific target instead of classification

Throttles

  • Types: Obj, Utility
  • Applies only if all 3 below are true for at least one step of the query
    • Has an all-AMP operation
    • Involves processing of data
    • Estimated processing time > Step Time Threshold
  • object throttles must be single obj (can be macro/SP). Available even no TASM
  • if >1 obj throttles apply, one with lowest limit is applied
  • If WD rules are active, any throttles against PG are ignored.
  • Throttle Collectively => the limit applies to collection instead of each member
  • object throttles share single queue. Each WD throttle has its own queue
  • If throttle specifies limiting session rather than queries, it can only be rejected.
  • A throttled utility logon is subject to both, utility throttle limit and obj throttle session limit. If Obj throttle session limit is exceeded, it is always rejected.
  • Cleanup of FEXP+MLOAD is done using separate logon subject to Obj Session limit
  • TENACITY+SLEEP params are ignored when utilities are throttled.
  • If system query/utility throttles are active, MaxLoadTask/MaxLoadAWT are ignored
  • Step Time Threshold affects queries having at least one step exceeds the time
  • ROT: Don’t add SQL type DML or DDL to throttle with Step Time Threshold. With no estimated time, it will always be below throttle and always run immediately.
  • A delayed query might violate a Filter rule later and thus it can be rejected
  • A query can be delayed by both throttles, first by obj throttle and then by WD throttle
  • Query getting demoted won’t honor throttle of the WD being demoted to.
  • TD13+: one throttle counter per Obj+Rule. TD12-: One counter per object. If obj has >1 rules, it’s limit will keep changing based on the last rule that was applied.
  • TD13.10+: All throttles eval at same time.
  • TD13.10-: System+Utility > query classification > Workload throttles. Eg, a system throttle caps 20 BI queries, a workload throttle only 6 big queries. 20 big BI queries will not let any small BI queries to run, since system throttle thinks all 20 big BI queries are running.
  • ROT: Use minimum step time
  • ROT: TD13.10-, use utility throttles and WD throttles to throttle utilities
  • ROT: TD13.10+, utility classification only applies to end-loading, end-epxort or check-workload-end and end-mload. Ok to use WD throttles to allow throttling by users, profiles etc. Define new WD for pre/post and restart log updates.
  • Each SP stmt and Call counts as one request. So throttle limit of 4 with 5 nested calls will block

WD

  • Criteria types:
    • Source/Who criteria is cheapest, evaluated once/session. Others once/query
    • Target/What, database, table, view etc
    • Query Characteristics/What, Stmt type (DDL/DML), AMP limits, row-counts etc
    • Utility/Which:
  • Multiple criteria of different types are ANDed and same type ORed
    • TD15.10+ USER and PROFILEs can be ORed
    • Where (target db, tables etc) are always ORed by database
  • if a query involves PE only work, it gets WDID as NULL in DBQL
  • Stmts that have 0 EstProcTime as far as TASM is concerned: CALL, UPDATE, CREATE INDEX, COLL STAT, when no estimated cost
  • MLOAD is treated as single AMP, must evaluate by util type higher eval order
  • TD14.10+: Optimizer generates estimated memory usage that’s not available in explain, but can be used as qualification criteria.
  • SLES11: IWM Tactical exceptions cannot be turned-off and apply to all Tactical WD
  • SLES11: Tactical exceptions CPU, CPU/node (default 2 sec) or IO/node (default 200MB)
  • SLES11: Tactical exception can cause query to run in different WD on different nodes
  • SLES11: Decay: halve the priority after 10 CPU sec or 100MB, then again after 200 CPU or 10GB IO. Decay is at node level and is not synchronized between nodes.
  • System level bypassed users are still subjected to WD throttles

Exception

  • Can be defined at either global (ruleset) or local (workload) level
  • evaluated either sync (end of step) or async (every Exception Interval)
  • skew is not calculated at sync, only available at async at each exception interval
    • TDWM uses MON SESS cmd for skew detection. This also flushes the internal cache and restarts accum. TD Mgr also does this if run concurrently
    • skew: %=(high-avg)/avg. Impact=(high-avg)/avg, diff=(high-avg)
    • IO Skew considers logical IO and not physical IO
  • two types: Unqualified (triggered immed). Qualified (must persist for duration)
  • Actions: None, Abort, Abort on Sel, Change Workload, Alert, Run program. 1 All except "None" log exceptions into dbc.TDWMExceptionLog
  • If multiple exceptions occur for a WD, all will be executed if no conflicts. If conflicts, most sever one will be preferred e.g. Abort over Change WD
  • SLES11: Tactical Exception checks for I/O as Physical bytes transferred
  • SLES11: Decay 1) 100 Sec/100 MB 2) 10,000 Sec or 10,000 MB
  • SLES11: Any per node CPU or I/O exception will cause request to change workload only on that node. Whereas sum-over-all-nodes will affect all nodes (OB: TD14 IWM)
  • "Tactical CPU Usage Threshold (per node)" uses PSF query milestone, if
    • the action must be "Change Workload" which has < 4X priority
    • CPU per node threshold values is between 0.1 and 5 seconds
    • It can change to new workload that is in the same RP
  • query milestone exception drawbacks
    • uncached complex tactical queries => high parsing time => quick demot
    • no exception logged until WD changes. if exception interval is higher than query run-time, exception is never logged
  • ROT: Don’t demote into a WD with AG CPU cap because it may hold resources
  • ROT: For CPU/IO skew exceptions, set other monitoring apps’ (eg TD Mgr) interval > TDWM exception interval. Because TD flushes interval collection cache on each MON SESSION command.
  • ROT: Use qualification time with skew detection
  • ROT: Always adjust sum CPU or I/O over all nodes after reconfig

Misc

  • Delayed queries
    • WD delayed queries are put in a separate delay queue from throttles
    • Query can be manually reclassified using PMON or TDM monitor session view
    • Changing priority, will not inc/decr query count, so throttle limits will be out-of-sync
  • Intervals: Event, Dashboard, Logging, Exception and Block Cycle Detection
    • Event: Interval to collect data and check for condition and time.
    • Dashboard: Accumulation period. Cached data reset interval. must be n*E
    • Logging: Interval to write summary of cache data to log L = n*D. Will also cause DBQLogTbl, ExceptionLog, EventLog tables to flush
    • Exception: Async exception checking interval.
    • Block Cycle Detection: Check for delayed queries (part of multi-req-tran) that might be blockers. Actions Abort,Release,Log
    • L = yD; D= xE; B = z*X
    • Logging interval affects TDWMSummaryLog, TDWMExceptionLog, TDWMEventLog, TDWMEventHistoryLog and DBQLogTbl
    • If query encounters TASM exception, its SQL will be logged into DBQLSQLTbl even if DBQL is not setup to log SQL
  • ROT
    • TPUMP not subject to utility throttle limits. Good idea to make it bypass
    • SQLs within utility jobs are subject to filters, make them bypass
    • Use obj throttle on user or obj unless WD is classified by user
    • obj throttles: good for low priority, long running work based on PG
    • Sys Throttles: use Step Time Threshold to let short work run without delay
    • Use WD query limits based on PG or Account
    • Prefer source and QB classification over other for exactness
    • Exceptions:
      • Use MonSesCPUNormalization to avoid CPU variances (affects PM/API)
      • Use CPU ms per IO (or PJI) and skew with qualification time period
      • Typical CPU ms / IO is 1-2. 3-10 are considered abnormal. Use 5 as ROT
      • If on a coexistence and TD2.6-, rely on IO Skew rather than CPU Skew
      • Keep Change WD consistent across all OpEnv. change AG wt/lim instead. -ß Health event: Node failures > 24%
  • Alerts:
    • Alert cause ActionSet which allow multiple actions to be taken
    • Actions include: email, SNMP, BTEQ, program
    • Alert can cause Group allow multiple ActionSets
    • Inserting into Alert Request Table, allows other processes to raise alerts