Hello!
I have been working on automating a very long script that until now has been manually with highlighting text and running snippets at a time, then manually recording the number of rows inserted/total duration of the script run. This disgusts me and I have already done a good amount of work to allow the entire script to be run at once (standardizing variable names, trimming out fat, using sub-queries to insert information needed automatically, etc), but I am stumbling when trying to figure out the best way to capture performance statistics about the queries.
Current set up: Each step in the script has a unique ID (e.g. 'SCR1-SUB2-101022', which I would like to associate with results of the script. SQL is currently built around dropping and recreating a bunch of times, primarily, or inserting additional data into newly recreated tables (considering dropping this in favor of truncation and inserts only).
Goal: ALL of the script should run at once - Hit F8 and let the script run to completion without further manual input. During this script run, a table that includes information about the run time, number of records inserted (if applicable - usually is), and whether the script step completed successfully should be generated with each substep. So, SCR1-SUB2-101022, Complete, 1300000ms, 123155667 records inserted, Start time, (end time?) would be inserted into the table as it moves through the run.
Approaches considered:
- Stored Procedure:I have considered creating a stored procedure so that each step is effectively wrapped in code that would set the name of the substep, and then trigger once at the start with a time stamp and then again after completion of the script. A count(*) after the insert is run would provide the total number of rows in the table after each step. Each row would contain the information. The SP would have a switch in the inputs to "S"tart by inserting the base information and "E"nd by updating the same record with the ending information.
- V$SQLAREA + V$SQL something something:I know that these tables contain information about the current SQL code that is running that includes the elapsed time and start time when the code was run. The information that I want is in there, but it seems like when I run a block of code with multiple substeps all that gets rolled up into one row. In addition, it seems like stats for DML and DDL queries get put in different views. Is there a good way to get this data?
Thanks for the advice.