I recently moved a set of my Oracle SQL queries to a package with table functions where I am returning data pipelined.
However, I started observing something unusual. V$SQL stats like buffer_gets, fetches, cpu_time, execution_time etc, have started showing a cumulative number, and they keep getting higher with every execution of the queries.
Is this usual behavior?
That's expected behavior.
There's a lots of shared resources involved. If you have an application with a dozen database connections in a pool, all doing the same sort of work, then you can have SQLs that have multiple users fetching from them at the same time (eg Connection 1 getting the Invoices for ABC while Connection 2 is getting invoices for XYZ). V$SQL (or GV$SQL in a RAC environment) will show the cumulative totals for most details (and things like USERS_EXECUTING will be current values across all sessions).
The only time they'll get reset is when the SQL ages out of the shared area, typically when it hasn't being used for some time and the resources are needed for another SQL, or rare events like a database shutdown.
