Daily for SQL plan reading and analysis, using below command quite often from SQL Developer Tool.
explain plan for statement_id = '';
select * from gv$sql where sql_text like '%%';
select * from table(dbmx_xplan.display);
select * from table(dbmx_xplan.display_cursor('',null,'ALLSTATS_LAST'));
Wouldn’t it be great, if we can have some template for all above command and executing queries on ease.
One of friend during our usual tea talk mention about code template options for SQL Developer and its quite amazing.
let us walk through same.
In SQL Developer follow below Options.
Tools –> Preference –> Database –> SQL Editor Code Template –> Explore the magic.
We can give a short name(ID) for each helper queries we execute for day to day jobs.
Now once SQL code template are added, now we can quickly run same,
Just type in the short name or ID we had given while adding template within query editor and let Autocomplete help you with save Query.
Thats Pretty cool!.
We might need to restart SQL Developer console after adding any query template.
To increase Autocomplete time for Code template, we can check below options as well.
Tools –> Preferences –> Code Editor –> Completion Insight
Enable Popup Speed to faster option.