Is there a way to collect all of SQLs and the Execution Plans (from the SGA or the database) so they could be compared to another Database? Not one SQL at a time, but rather as a group? (For example, to see if the new Execution Plan is the same in a clean SGA as in the old database)
1. Retrieve the SQL statements from the SGA using the SGA Inspector module.
2. At Job level, execute the 'Save to Repository' function to send the SQL to the Impact Analyzer module.
3. When completed, all the SQL in the Impact Analyzer along with their execution plan are created.
4. From the Impact Analyzer, click 'Add Analyzer', in the SQL tab, select the newly added Inspector SQLs. In the Plan Snapshot, create a Baseline Snapshot using the 'Copy existing execution plan from SQL Repository' as you want to keep the existing plan for comparison. Then create another Snapshot and enter the new connection.
5. Click Finish, it will generate a new set of plans from the new connection. Analyze the differences between plans.