After cleaning up all the low hanging fruit from our application queries DPA is now bubbling up Oracle system queries from internal maintenance jobs and statistics gathering. Most of the queries coming back from the extremely useful table analysis are now full table and index scans for the nightly / weekend stats jobs. There is nothing we can do about those queries and they are technically fine as is. It would be nice to have some easy buttons to filter away those queries from both the UI and potentially from the analysis churn by DPA. Via support we are using a clause in the quickpoll_where_clause support option but this feels like a more concrete way could be implemented. DEV please see Case # - 00170867 for more information.
Perhaps a global toggle to show/hide SYS queries is a better implementation than a forced ignore but with the stats related queries in the analysis suggestions the power of the tool is dramatically reduced. The screen shot belows shows the suggestions for our largest system tables. All 10 queries to review are from stats jobs on large indexes such as below:
Example of the 'bad' query.
SELECT
/*+ parallel_index(t, "T_STATEMENT_IPS_PRT_ORG",8) dbms_stats cursor_sharing_exact
use_weak_name_resl dynamic_sampling(0) no_monitoring xmlindex_sel_idx_tbl opt_param('
optimizer_inmemory_aware' 'false') no_substrb_pad no_expand use_invisible_indexes index_ffs(t,"
T_STATEMENT_IPS_PRT_ORG") */
count(*) as nrw,
count(DISTINCT sys_op_lbid(10606914,'L',t.rowid)) as nlb,
count(DISTINCT hextoraw(sys_op_descend("PRT_BA_ID")||sys_op_descend("PRT_ORG_ID")||
sys_op_descend("ACCOUNTING_MONTH")||sys_op_descend("CC_ID")||sys_op_descend("AFE_ID")||
sys_op_descend("DATASOURCE")||sys_op_descend("INVOICE_ID")||sys_op_descend("OP_BA_ID")||
sys_op_descend("OP_ORG_ID")||sys_op_descend("DISPUTE_REASON_CODE")||sys_op_descend(
"LAST_PL_VERSION_STMT")||sys_op_descend("ORIGINAL_PRT_AMT")||sys_op_descend("ORIGINAL_GST_AMT")
||sys_op_descend("ORIGINAL_CSH_AMT")||sys_op_descend("ACCEPTED_PRT_AMT")||sys_op_descend(
"ACCEPTED_GST_AMT")||sys_op_descend("ACCEPTED_CSH_AMT")||sys_op_descend(
"CLOSED_DISPUTED_PRT_AMT")||sys_op_descend("CLOSED_DISPUTED_GST_AMT")||sys_op_descend(
"CLOSED_DISPUTED_CSH_AMT")||sys_op_descend("OPEN_DISPUTED_PRT_AMT")||sys_op_descend(
"OPEN_DISPUTED_GST_AMT")||sys_op_descend("OPEN_DISPUTED_CSH_AMT")||sys_op_descend(
"ACCEPTED_CHANGE_PRT_AMT")||sys_op_descend("ACCEPTED_CHANGE_GST_AMT")||sys_op_descend(
"ACCEPTED_CHANGE_CSH_AMT")||sys_op_descend("REV_TAX_AMT")||sys_op_descend("REV_DEDUCT_AMT")||
sys_op_descend("REV_NETTED_AMT"))) as ndk,
sys_op_countchg(substrb(t.rowid,1,15),16) as clf
FROM "RDA_US"."T_STATEMENT" t
