search

tanelpoder / tpt-oracle

Tanel Poder's Performance & Troubleshooting Tools for Oracle Databases
https://tanelpoder.com
Other
648 stars 307 forks source link

xbi is not showing all columns #23

Closed ursusca closed 4 years ago

ursusca commented 4 years ago

The scripts is not populating the columns below:

Est. rows Opt. Card. Current Physical Physical total missetimate gets read blks write blks

tanelpoder commented 4 years ago

These columns depend on fields in V$SQL_PLAN_STATISTICS that get populated when "row source level statistics" collection is enabled for the cursor. So, either rerun the query with GATHER_PLAN_STATISTICS hint or ALTER SESSION SET statistics_level = ALL before running the query. Another option is to enable SQL_TRACE for your session and then run the queries (no need to look into SQL Trace files, but enabling the trace will populate the required V$ columns as well).

The xb*.sql scripts use the same fields that the A-Time, A-Rows etc columns use in DBMS_XPLAN.DISPLAY_CURSOR( ... 'ALLSTATS LAST') case.

ursusca commented 4 years ago

Thank you for clarification. I just have STATISTICS_LEVEL=ALL permanently at system level but it doesn't collect all runtime statistics, so session level is still required.

tanelpoder commented 4 years ago

Interesting, what DB version is this on? I normally wouldn't run STATISTICS_LEVEL=ALL system-wide due to the CPU overhead of rowsource level stats capture (for some query types with lots of looping), I wonder whether this has changed in a newer DB version.

You can also use my @create_sql_patch &sql_id GATHER_PLANS_STATISTICS and @drop_sql patch scripts (run @help patch to see the syntax) to enable "gather plan statistics" just for one SQL_ID (regardless which session runs this).

ursusca commented 4 years ago

It's 12.2.0.1 with PSU Oct 2019 installed (AIX)

And thank you so much for the sql_patch idea. I was thinking of enabling SQL Trace for a specific session