Skip to Content
0
Jun 11, 2018 at 09:48 AM

Query tuning issue. A batch runs for 24 hrs post update stats - SYBASE ASE 15.7 SP138

359 Views Last edit Jun 11, 2018 at 09:50 AM 3 rev

Hello Experts,

Need your advice to fix a PROD batch issue which started post running update index stats on all the tables on the weekend on a PROD database.

Issue details

1> Before the update stats (the batch took 20 mins for total execution). Post running the update stats, it is exceeding 24 hrs duration. No changes have been made in the batch as it is a legacy application.

2> I have extracted few details for the problematic spid.

Please share you inputs as I am new to query tuning.

Let me know if any other details are required. Many thanks in advance.

Regards,

DJV.

Spid Login DB Hostname blocked StartTime Dmins Dtime CpuTime LogicalReads PhysicalReads Command --- ------ ------------ ---------------- ------------ ------- ------------------- ----------- ----- ----------- -------------------- -------------------- ------------------------------

*** 619 CDB_BATCH CDB ozop1069 0 Jun 11 2018 1:02AM 640 10:40 38419876 1822366860 28705 INSERT

1> select * from monProcessWaits where SPID=619

2> go

SPID InstanceID KPID ServerUserID OrigServerUserID WaitEventID Waits WaitTime ----------- ---------- ----------- ------------ ---------------- ----------- ----------- -----------

619 0 526844622 436 0 29 13155 4007 619 0 526844622 436 0 31 1 1 619 0 526844622 436 0 51 2 5 619 0 526844622 436 0 55 4 7 619 0 526844622 436 0 124 24688 6625 619 0 526844622 436 0 214 187681 5954 619 0 526844622 436 0 215 37853 1698 619 0 526844622 436 0 250 3 1 (8 rows affected)

1> select * from master..monProcessActivity where SPID=619

2> go

SPID InstanceID KPID ServerUserID OrigServerUserID CPUTime WaitTime PhysicalReads LogicalReads PagesRead PhysicalWrites PagesWritten MemUsageKB LocksHeld TableAccesses IndexAccesses TempDbObjects WorkTables ULCBytesWritten ULCFlushes ULCFlushFull ULCMaxUsage ULCCurrentUsage Transactions Commits Rollbacks HeapMemoryInUseKB HeapMemoryUsedHWM_KB HeapMemoryReservedKB HeapMemoryAllocs IOSize1Page IOSize2Pages IOSize4Pages IOSize8Pages Application HostName ClientName ClientHostName ClientApplName ----------- ---------- ----------- ------------ ---------------- ----------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- ----------- ------------- ------------- ------------- ----------- --------------- ----------- ------------ ----------- --------------- ------------ ----------- ----------- ----------------- -------------------- -------------------- ---------------- ----------- ------------ ------------ ------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------

619 0 526844622 436 0 37625800 1094 41810 1686691742 53066 46 192 90 11 1162674874 494061308 0 1 5848 9 0 3688 192 15 14 0 1 9 16 37 40221 0 0 1608 isql ozop1069 NULL NULL NULL

QUERY STUCK AT line - 982

977 978 /* Do not raise the INACTSITE trigger for a site if its 979 HQ has no other active site. Delete such records from temp table. 980 Raise the trigger in case the parent is inactive */ 981 982 >>> select distinct CU.CUSTOMER_ID 983 into tempdb..INACTSITE_TRIGGER 984 from 985 CUSTOMER CU 986 where 987 CU.SITE_FLAG = 'Y' 988 and CU.ACTIVITY_STATUS_CODE = 'I' 989 and ( 990 exists

SHOWPLAN

QUERY PLAN FOR STATEMENT 119 (at line 982).

Optimized using Serial Mode

STEP 1 The type of query is CREATE TABLE.

STEP 2 The type of query is INSERT. 29 operator(s) under root |ROOT:EMIT Operator (VA = 29) | | |INSERT Operator (VA = 28) | | The update mode is direct. | | | | |SQFILTER Operator (VA = 27) has 3 children. | | | | | | |SCAN Operator (VA = 0) | | | | FROM TABLE | | | | CUSTOMER | | | | CU | | | | Table Scan. | | | | Forward Scan. | | | | Positioning at start of table. | | | | Using I/O Size 16 Kbytes for data pages. | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | Run subquery 1 (at nesting level 1). | | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 992). | | | | | | Correlated Subquery. | | | Subquery under an EXISTS predicate. | | | | | | |SCALAR AGGREGATE Operator (VA = 11) | | | | Evaluate Ungrouped ANY AGGREGATE. | | | | Scanning only up to the first qualifying row. | | | | | | | | |NESTED LOOP JOIN Operator (VA = 10) (Join Type: Left Semi Join) | | | | | | | | | | |NESTED LOOP JOIN Operator (VA = 7) (Join Type: Left Semi Join) | | | | | | | | | | | | |N-ARY NESTED LOOP JOIN Operator (VA = 4) has 3 children. | | | | | | | | | | | | | | |SCAN Operator (VA = 1) | | | | | | | | FROM TABLE | | | | | | | | INTER_ORG_RELATIONSHIP | | | | | | | | IOR | | | | | | | | Using Clustered Index. | | | | | | | | Index : XPKINTER_ORG_RELATIONSHIP | | | | | | | | Forward Scan. | | | | | | | | Positioning by key. | | | | | | | | Keys are: | | | | | | | | OWNED_CUSTOMER_ID ASC | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | |SCAN Operator (VA = 2) | | | | | | | | FROM TABLE | | | | | | | | INTER_ORG_RELATIONSHIP | | | | | | | | IOR1 | | | | | | | | Index : XIF48INTER_ORG_RELATIONSHIP | | | | | | | | Forward Scan. | | | | | | | | Positioning by key. | | | | | | | | Keys are: | | | | | | | | OWNING_CUSTOMER_ID ASC | | | | | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | |SCAN Operator (VA = 3) | | | | | | | | FROM TABLE | | | | | | | | CUSTOMER_PNI_LINK | | | | | | | | CPL | | | | | | | | Index : XIF189CUSTOMER_PNI_LINK | | | | | | | | Forward Scan. | | | | | | | | Positioning by key. | | | | | | | | Keys are: | | | | | | | | HQ_CUSTOMER_ID ASC | | | | | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | | | Using I/O Size 16 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | |RESTRICT Operator (VA = 6)(0)(0)(0)(13)(0) | | | | | | | | | | | | | | |SCAN Operator (VA = 5) | | | | | | | | FROM TABLE | | | | | | | | BMB_ACCOUNT_SNAPSHOT | | | | | | | | BAS | | | | | | | | Using Clustered Index. | | | | | | | | Index : XPKBMB_ACCOUNT_SNAPSHOT | | | | | | | | Forward Scan. | | | | | | | | Positioning by key. | | | | | | | | Keys are: | | | | | | | | BMB_CUSTOMER_ACCOUNT ASC | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | |RESTRICT Operator (VA = 9)(0)(0)(0)(7)(0) | | | | | | | | | | | | |SCAN Operator (VA = 8) | | | | | | | FROM TABLE | | | | | | | CUSTOMER | | | | | | | CU1 | | | | | | | Using Clustered Index. | | | | | | | Index : XPKCUSTOMER | | | | | | | Forward Scan. | | | | | | | Positioning by key. | | | | | | | Keys are: | | | | | | | CUSTOMER_ID ASC | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | END OF QUERY PLAN FOR SUBQUERY 1. | | | | | | Run subquery 2 (at nesting level 1). | | | | | | QUERY PLAN FOR SUBQUERY 2 (at nesting level 1 and at line 1021). | | | | | | Correlated Subquery. | | | Subquery under an EXISTS predicate. | | | | | | |SCALAR AGGREGATE Operator (VA = 26) | | | | Evaluate Ungrouped ANY AGGREGATE. | | | | Scanning only up to the first qualifying row. | | | | | | | | |SQFILTER Operator (VA = 25) has 2 children. | | | | | | | | | | |NESTED LOOP JOIN Operator (VA = 19) (Join Type: Left Semi Join) | | | | | | | | | | | | |NESTED LOOP JOIN Operator (VA = 16) (Join Type: Inner Join) | | | | | | | | | | | | | | |NESTED LOOP JOIN Operator (VA = 14) (Join Type: Left Semi Join) | | | | | | | | | | | | | | | | |SCAN Operator (VA = 12) | | | | | | | | | FROM TABLE | | | | | | | | | INTER_ORG_RELATIONSHIP | | | | | | | | | IOR | | | | | | | | | Using Clustered Index. | | | | | | | | | Index : XPKINTER_ORG_RELATIONSHIP | | | | | | | | | Forward Scan. | | | | | | | | | Positioning by key. | | | | | | | | | Keys are: | | | | | | | | | OWNED_CUSTOMER_ID ASC | | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | | | |SCAN Operator (VA = 13) | | | | | | | | | FROM TABLE | | | | | | | | | CUSTOMER | | | | | | | | | CU2 | | | | | | | | | Using Clustered Index. | | | | | | | | | Index : XPKCUSTOMER | | | | | | | | | Forward Scan. | | | | | | | | | Positioning by key. | | | | | | | | | Keys are: | | | | | | | | | CUSTOMER_ID ASC | | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | |SCAN Operator (VA = 15) | | | | | | | | FROM TABLE | | | | | | | | CUSTOMER_PNI_LINK | | | | | | | | CPL | | | | | | | | Index : XIF189CUSTOMER_PNI_LINK | | | | | | | | Forward Scan. | | | | | | | | Positioning by key. | | | | | | | | Keys are: | | | | | | | | HQ_CUSTOMER_ID ASC | | | | | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | |RESTRICT Operator (VA = 18)(0)(0)(0)(13)(0) | | | | | | | | | | | | | | |SCAN Operator (VA = 17) | | | | | | | | FROM TABLE | | | | | | | | BMB_ACCOUNT_SNAPSHOT | | | | | | | | BAS | | | | | | | | Using Clustered Index. | | | | | | | | Index : XPKBMB_ACCOUNT_SNAPSHOT | | | | | | | | Forward Scan. | | | | | | | | Positioning by key. | | | | | | | | Keys are: | | | | | | | | BMB_CUSTOMER_ACCOUNT ASC | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | Run subquery 1 (at nesting level 2). | | | | | | | | | | QUERY PLAN FOR SUBQUERY 1 (at nesting level 2 and at line 1038). | | | | | | | | | | Correlated Subquery. | | | | | Subquery under an EXISTS predicate. | | | | | | | | | | |SCALAR AGGREGATE Operator (VA = 24) | | | | | | Evaluate Ungrouped ANY AGGREGATE. | | | | | | Scanning only up to the first qualifying row. | | | | | | | | | | | | |NESTED LOOP JOIN Operator (VA = 23) (Join Type: Left Semi Join) | | | | | | | | | | | | | | |SCAN Operator (VA = 20) | | | | | | | | FROM TABLE | | | | | | | | INTER_ORG_RELATIONSHIP | | | | | | | | IOR1 | | | | | | | | Index : XIF48INTER_ORG_RELATIONSHIP | | | | | | | | Forward Scan. | | | | | | | | Positioning by key. | | | | | | | | Keys are: | | | | | | | | OWNING_CUSTOMER_ID ASC | | | | | | | | Using I/O Size 2 Kbytes for index leaf pages. | | | | | | | | With LRU Buffer Replacement Strategy for index leaf pages. | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | | | | | |RESTRICT Operator (VA = 22)(0)(0)(0)(7)(0) | | | | | | | | | | | | | | | | |SCAN Operator (VA = 21) | | | | | | | | | FROM TABLE | | | | | | | | | CUSTOMER | | | | | | | | | CU1 | | | | | | | | | Using Clustered Index. | | | | | | | | | Index : XPKCUSTOMER | | | | | | | | | Forward Scan. | | | | | | | | | Positioning by key. | | | | | | | | | Keys are: | | | | | | | | | CUSTOMER_ID ASC | | | | | | | | | Using I/O Size 2 Kbytes for data pages. | | | | | | | | | With LRU Buffer Replacement Strategy for data pages. | | | | | | | | | | END OF QUERY PLAN FOR SUBQUERY 1. | | | | | | END OF QUERY PLAN FOR SUBQUERY 2. | | | | TO TABLE | | tempdb..INACTSITE_TRIGGER | | Using I/O Size 16 Kbytes for data pages. Total estimated I/O cost for statement 119 (at line 982): 91193942.