We are looking to put in a user performance measurement change from before to after an upgrade of a system.
This would be used to spot problems, and present to management, check the new CBO is working for us, in individual SQL statements looking at the delta before and after an upgrade. This could be new hardware or database software upgrade on the same hardware. I took a try at it, really did not get what I expected, from a UAT system to a production system.
select SQL_ID, PARSING_SCHEMA_NAME, EXECUTIONS, OPTIMIZER_MODE, OPTIMIZER_COST, CPU_TIME, USER_IO_WAIT_TIME
where (exclude non system schemas like sys, system dbsnmp) and EXECUTIONS > 1 and OPTIMIZER_COST > 0 and CPU_TIME > 0 and USER_IO_WAIT_TIME > 0
order by SQL_ID ;
Paste the output into an Excel spreadsheet old new and create a percentage change sheet.
The not many of the SQL queries are in both spreadsheets. The problem is they age out of the GV$SQLAREA, I looked around and did not find a helpful table with all the SQL queries.
Are there any tools already existing, any suggestions?