Geeky PL/SQL tracer/profiler : First step

This blog post is about how to extend our capabilities to trace and profile PL/SQL code.It’s primarily motivated by few tweets from Franck Pachot and of course because it’s FUN !

Capture 02

Capture 01

So in the first part of this series we are going to answer to this questions : Can we map those underling function to the source PL/SQL object and line number ? Can we obtain a full trace ? Of course yes otherwise there will be no blog post :p

Continue reading

POUG : Memory access tracing slides

You know POUG ? Of course you know POUG ! This is one of the best  Oracle conference out there ! Great peoples ! Great Speakers ! Great Place ! Awesome ambiance !!!

Last week i was honored to be there ! It was a pleasure to meet many other Oracle geeks and to participate to this great event !

Poug_day

bad

So as promised here is my presentation (My first public speak by the way)  on slide share : Just click here !

Many thanks for all the POUG Team for this great adventure ! Don’t miss POUG18 !!!!

Getting Latch holder info out of the state objects in SGA memory

Updated 12/05/2017

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 !

Continue reading

Adaptive Dynamic Sampling : DYNAMIC_SAMPLING_RESULT & STALE_PERCENT

Since oracle 12.2.0.1 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) ?

Continue reading

Improved remote session debugging (DBMS_DEBUG_JDWP)

“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 :

Continue reading