Suppose we want to investigate/debug the initial phases of an oracle process creation such as the authentication step (Oracle getting anyone’s password) , how to processed ? We will have to attach to the process just after it’s creation ! Here is a systemtap script that may help !
It will be great to have a tool that will extract latch holder information directly from state objects stored inside the SGA. This way we will reduce the overhead when troubleshooting latch contention an beside that it’s also cool !!
This may sound difficult ! How to proceed ? and the answer is …. Memory reference tracing !
Since oracle 188.8.131.52 Adaptive Dynamic Sampling result are stored inside SPD as a special directive type “DYNAMIC_SAMPLING_RESULT”. This will allow the result of ADS to be persisted on disk,thus it will survive memory flush and database restart.For more detailed info please check Mauro Pagano blog Post Something new about SQL Plan Directives and 12.2
We may ask at this moment is there some sanity check that will trigger the refresh of this ADS result stored as sql plan directive when the table data is marked stale (STALE_PERCENT) ?
“Improving the PL/SQL Debugger:
In prior releases, it was necessary to change the application to include calls to start and stop debugging. With this improvement, one database session can start debugging, and stop debugging a different session.”
“With these improvements, if a problem occurs in a long running test or in a production environment, it is possible to investigate the problem from another session.” Oracle Database 12c Release 2 (12.2) New Features
Let’s take a closer look :
In one of my previous blog post i demonstrated that the amount of work needed for IN-MEMORY population/[trickle] re-population in oracle 184.108.40.206.6 is more than we may expect : IN-MEMORY population/[trickle] re-population : How much work ? You may be surprised !
Let’s do a very quick check on how this is now handled in oracle 220.127.116.11/OEL 6/UEK4