Debug PL/SQL program units Debug PL/SQL procedures and functions through anonymous blocks In the Recompile dialog, select With "debug" option. PL/SQL program units organize the code into blocks. A block without a name is an anonymous block. The anonymous block is not stored in the Oracle database. In the debugging process, you use the anonymous block to pass values for parameters. To debug procedures, packages, and functions, write an anonymous block that calls the necessary routine. Voilá! Query Store holds both the statistics and the plan even if the stored procedure uses the RECOMPILE option in its definition.In the Database Explorer ( View | Tool Windows | Database Explorer), double-click the PL/SQL object that you created and compiled for debugging.Ĭlick the Run Procedure button. WHERE OBJECT_NAME(qsq.object_id) = 'uspRecompiled' INNER JOIN sys.query_store_runtime_stats_interval qsrsi ON ntime_stats_interval_id = ntime_stats_interval_id INNER JOIN sys.query_store_runtime_stats qsrs ON an_id = qsp.plan_id INNER JOIN sys.query_store_plan qsp ON qsq.query_id = qsp.query_id INNER JOIN sys.query_context_settings qcs ON qsq.context_settings_id = qcs.context_settings_id INNER JOIN sys.query_store_query_text qsqt ON qsq.query_text_id = qsqt.query_text_id (select cast(query_plan as xml) from sys.query_store_plan WHERE query_id = qsp.query_id AND plan_id = qsp.plan_id) Now Ladies and Gentleman, let us see what happens if we look in Query Store Views instead: SELECT OBJECT_NAME(qsq.object_id) as ObjectName, WHERE OBJECT_NAME(qt.objectid, qt.dbid) = ' uspRecompiled' Of course we can guess what’s going to happen if I run the same query I used in the beginning over the DMOs: SELECT DB_NAME(qt.dbid) AS DataBaseName, Ups, no compiled plan for any stored procedure, just for the latest Ad-hoc query executed (inside the red rectangle). So far so good… Now I’ll use the sys.dm_exec_cached_plans DMV that “ Returns a row for each query plan that is cached by SQL Server for faster query execution” to look for the SP plan_handle: Ok, this it it! Let’s free the proc cache again and re-execute the SP: DBCC FREEPROCCACHE Now let me alter the SP by adding the RECOMPILE option: ALTER PROCEDURE uspRecompiled WHERE OBJECT_NAME(qt.objectid, qt.dbid) = 'uspRecompiled'Īs we can see in the picture above, there is an entrance corresponding to the execution I just did. OBJECT_NAME(qt.objectid, qt.dbid) AS ObjectName,įROM sys.dm_exec_query_stats qs WITH (NOLOCK)ĬROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qtĬROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qpĬROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) tqp Using the DMOs, I’ll look for the exec info regarding this SP: SELECT DB_NAME(qt.dbid) AS DataBaseName, Now we’re ready to go! Let’s exectute the SP: EXEC uspRecompiled freeing the proc cache: DBCC FREEPROCCACHE In order to facilitate results reading I’ll start by doing something that shouldn’t be done in a production environment, i.e. In this post I’ll demonstrate that we CAN’T capture exec info for Stored Procedures using DMOs when they’re created with the RECOMPILE option but using Query Store we CAN!įirst I’ll create the stored procedure without using the RECOMPILE option: use LETsLOOKatQUERY_STORE In today’s post I’ll answer this question comparing DMOs and Query Store □ If you create a Stored Procedure with the RECOMPILE option, will you have access to its runtime statistics and execution plan? You can find Part VII here where I showed that using Query Store it’s possible to get the execution plans even for encrypted stored procedures. This is part VIII of several posts about a new feature in SQL Server 2016 called: Query Store!
0 Comments
Leave a Reply. |