This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,831 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Measure user experience change delta after an upgrade?

User_CZIMD
User_CZIMD Member Posts: 1 Red Ribbon
edited Nov 4, 2016 6:38PM in Database Ideas - Ideas

Hi ;

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

from GV$SQLAREA

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?

Thanks

Bill

2 votes

Active · Last Updated

Comments