hello,
I'm trying to figure out a certain query.
after running this query with gather_plan_statistics hint
and looking the collected results by running:
select * from table (DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALL ALLSTATS LAST'));
I Get the following Columns:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Inst |IN-OUT| A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
one of them is "Buffers", my guess is that Buffers means blocks being copied to buffer cache.
the thing is, that the part that has the Biggest A-Time seems to be something that I would expect to run fine:
| 43 | NESTED LOOPS | | 146 | 3 | 243 | 2364 (2)| 00:00:01 | | | 392 |00:00:14.97 | 1544K| 468 | | | |
|* 44 | INDEX FAST FULL SCAN | CASE_CRD | 146 | 3 | 57 | 2360 (2)| 00:00:01 | | | 484 |00:00:15.61 | 1543K| 0 | | | |
|* 45 | TABLE ACCESS BY INDEX ROWID | CASE | 484 | 1 | 62 | 2 (0)| 00:00:01 | | | 392 |00:00:00.58 | 1458 | 468 | | | |
|* 46 | INDEX UNIQUE SCAN | CASE | 484 | 1 | | 1 (0)| 00:00:01 | | | 484 |00:00:00.34 | 970 | 227 | | | |
line 44, results in 484 rows, it gets all the columns from the Index alone, (and those columns have pretty much basic data types with small values)
the only "big" alerting number I see is the 1543K under buffers which I can't find anyone talking about.
actually, the whole gather_plan_statistics doesn't seem to be very popular with the oracle community, which is strange as it's very useful.