SmartIT 1. 0 sp1 patch 001 Oracle 11.2.0.3.0.
Problem Summary # On SmartIT Console, it takes lot of time to load the ticket data.
Most filters available on the Smart IT Console, there are no indexes in the DB. Filtering by those causes table scans. We have issues with Assigned Group, Assignee, Status, and Site. Here are the details from this particular query, which is only filtering by Open Tickets. "All Rows Fetched: 76 in 64.984 seconds" Plan hash value: 1247797987
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 76 | 42560 | 2550 (1)| 00:00:31 | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 311 | 170K| 2550 (1)| 00:00:31 | |* 3 | SORT ORDER BY STOPKEY| | 311 | 174K| 2550 (1)| 00:00:31 | | 4 | VIEW | GU__OVERVIEW_CONSOLE | 311 | 174K| 2549 (1)| 00:00:31 | | 5 | UNION-ALL | | | | | | |* 6 | TABLE ACCESS FULL | T2318 | 76 | 17556 | 120 (0)| 00:00:02 | |* 7 | TABLE ACCESS FULL | T1339 | 79 | 12482 | 62 (0)| 00:00:01 | |* 8 | TABLE ACCESS FULL | T2941 | 77 | 13783 | 183 (1)| 00:00:03 | |* 9 | TABLE ACCESS FULL | T1758 | 79 | 15326 | 2183 (1)| 00:00:27 | ------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter(ROWNUM<=76) 3 - filter(ROWNUM<=76) 6 - filter(CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =8000 OR CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =18000 OR CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =19000 OR CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =70000 OR CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =71000 OR CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =35000 OR CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =6000 OR CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =34000) 7 - filter(CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =6000 OR CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =8000 OR CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =18000 OR CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =19000 OR CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =34000 OR CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =35000 OR CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =70000 OR CASE "C7" WHEN 1000 THEN 34000 WHEN 2000 THEN 8000 WHEN 3000 THEN 19000 WHEN 4000 THEN 35000 WHEN 5000 THEN 21000 WHEN 6000 THEN 32000 WHEN 7000 THEN 36000 END =71000) 8 - filter(CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =8000 OR CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =18000 OR CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =19000 OR CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =70000 OR CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =71000 OR CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =35000 OR CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =6000 OR CASE "C7" WHEN 0 THEN 8000 WHEN 1 THEN 19000 WHEN 2 THEN 70000 WHEN 3 THEN 71000 WHEN 4 THEN 18000 WHEN 5 THEN 27000 WHEN 6 THEN 28000 WHEN 7 THEN 33000 WHEN 8 THEN 32000 END =34000) 9 - filter(CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =6000 OR CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =8000 OR CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =18000 OR CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =19000 OR CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =34000 OR CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =35000 OR CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =70000 OR CASE "C7" WHEN 1000 THEN 3000 WHEN 2000 THEN 19000 WHEN 3000 THEN 70000 WHEN 4000 THEN 71000 WHEN 5000 THEN 18000 WHEN 6000 THEN 27000 WHEN 7000 THEN 28000 WHEN 8000 THEN 33000 WHEN 9000 THEN 32000 WHEN 1200 THEN 3000 WHEN 1500 THEN 4000 WHEN 1800 THEN 5000 END =71000)
T4205.C301569500 is "ConsolidatedStatus" on "SHR:UnionOverview_MasterConsole". There wasn't a single filter on the Console that would load in less than a minute.. Our expectation is that the console should never have users waiting for more than a minute to load.
|
This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to Article Number 000385088 for more information about troubleshooting BMC products in containers. Legacy ID:KA425514 Defect # SW00488452 has been raised to address this in 1.2 release. Workaround # 01. Add custom indexes to TMS:Task, WOI:WorkOrder, HPD:Help Desk, and SRM:Request to get the Console to filter by Assignee quickly 02. So here is one way to speed up sql query using function based index. Please note that I have tested it only for one view (HPD). Please make sure the solution works on the union of all 4 views. Please also make sure you test it on a customer like database where there is enough data volume. Here are the steps:
create index T2132_C7Case on T2132 (CASE WHEN C7 = 0 THEN 6000 WHEN C7 = 1 THEN 8000 WHEN C7 = 2 THEN 18000 WHEN C7 = 3 THEN 19000 WHEN C7 = 4 THEN 25000 WHEN C7 = 5 THEN 32000 WHEN C7 = 6 THEN 33000 end ) 2. For safety gather table statistics 3. Created the view MU__HPD_HELP_DESK (I had to follow this step as I did not know the view name) 4. Ran the sql to see if it picks up function based index on the view. It did.
SQL> l 1* select REQUESTID, ASSIGNEE_GROUPS, PRODUCT_NAME from MU__HPD_HELP_DESK where CONSOLIDATEDSTATUS= 33000 SQL> /
INC_CAL_1000018 ;1000000001;
Execution Plan ---------------------------------------------------------- Plan hash value: 452843822
-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 3 | 468 | 2 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID| T2132 | 3 | 468 | 2 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | T2132_C7CASE | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - access(CASE "C7" WHEN 0 THEN 6000 WHEN 1 THEN 8000 WHEN 2 THEN 18000 WHEN 3 THEN 19000 WHEN 4 THEN 25000 WHEN 5 THEN 32000 WHEN 6 THEN 33000 END =33000)
Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 705 bytes sent via SQL*Net to client 524 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
Related Products:
|