In one bigger DB with many user schemas and big tables I have the devs starting regulary the Analyze select dbms_stats queries , that consume a lot of cpu when started for many users at once. These analyze queries have sometimes over 2-3 Minutes duration time and all of them have COUNT inside and the explain plan shows that the queries use every time table access FULL scan.
/* SQL Analyze(0) */ select /*+ full(t) parallel(t,5) parallel_index(t,5) dbms_stats
A lot of those queries need to collect statistics, but during this period they consume all of the server CPUs, and I would like to see if they can be optimized.
The queries are similar like in the Doc ID 2552730.1
They have a lot of repetitve
to_char(count( sub-functions for the table columns.
PX Coordinator is starting them parallel, which is ok, before SORT aggregate and after sort agreggate in the explain plan.
Many tables have over 150 million rows.
At the end of the queries we have also TOPN,NIL,NDV methods repeating many times.
Have you maybe encountered same issues and which approach could be taken to optimize those queries if possible. SQL Advisor gave back no recommendations.