Here’s a little test you might want to try. Examine the following script, and decide what sort of symptoms you would see in the AWR report.
create global temporary table gtt1(n1 number); execute dbms_workload_repository.create_snapshot; insert into gtt1 values(1); truncate table gtt1; -- repeat insert/truncate for a total of 100 cycles execute dbms_workload_repository.create_snapshot; -- generate an AWR report across the interval.
I don’t need anyone to tell me their results – but if your predictions and the actual results match then you can give yourself a pat on the head.
You might also like to enable SQL trace for all the inserts/truncate to see if that shows you anything interesting.
This is one of the simpler scripts of the 3,500 I have on my laptop that help me interpret the symptoms I see in client systems.