The first result you should get back is one from a fellow Tampan (Tampon?), Lewis Cunningham, from July 2006. OK, it's a bit old (I think it was still called Raptor back then), but I'll give it a try.
In it, Lewis talks about creating additional "pseudo" columns, SDEV_LINK_NAME, SDEV_LINK_OWNER, SDEV_LINK_OBJECT which appear to map to the corresponding columns in DBA_OBJECTS.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgz3wQvkjMJKfAz0GGpkmHr_9Kot-q78E9wSHEkiYrH9LynvFdUzU82h-zWT7dqhDG9C9_ptDPHXZnnkIEtfpkfqVPt2ZAFV1il6rQ4q4X4F0wbFg1PTtoPeIPfI6Q0z6O2ylifVNsKasUk/s800/cunningham-sqldev-f7.jpg)
I tried that, and got...nothing. I tried changing the alias(es) to match the column I was using, again, to no avail.
Let me back up just a tad, I'm trying to create some reports based on the PLSQL_PROFILER_% tables:
* PLSQL_PROFILER_RUNS
* PLSQL_PROFILER_UNITS
* PLSQL_PROFILER_DATA
It's annoying to have to rewrite the SQL everytime. I did create a @profile script, but I had to pass the RUNID; so first, I had to know the RUNID.
So I took to Twitter as I know Kris Rice hangs out there sometimes.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjyrJBFLg4u-awpkO3n8XxyIKkQeGluDo6wRcpoX9sJXRfl_-PNPOtCJiPCU1LlqyB2Ey18mvb4VhlCT35ZmEEvz4bcpS5NFxJSsCbI7mSGd9-440EodfblQEV1zlR5uzkdPSGNpmW-8HZ/s800/kris_rice_1.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhOriMe4RogYNXk2Xqkz8dTYFtiA7qtVya0ZgbVl13ziSqLQVViWBHDujmr0djZ4NuZUfPc9UdNGLYEjf1RcjMkeCwETWdi1QZ5BEYiv5lkUxWZ3R6_mzbhJSps1I0rgyDAtYhrM0eJGpmm/s800/oraclenerd_answer.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi429Iz7q_2ylMVaa-b7e45U_Pj1VDdx318A2wvJGVbXN8K3qjEmpRJhZMg8rjNfY24NlVknt3_2xUqwSCs8U2OPey6UayHkJUYdlOnzWQMQBCh-NBcqOFNeCrBKj2e40ATPYr5BBcn8Br5/s800/kris_rice_2.png)
That was last week, and I have been unable to get this to work. I could have sworn Kris had a good tutorial on it, but I think I confused it with the extensions you can create.
Anyway, I'm at it again tonight and I end up back at the link Kris originally pointed me to. For some reason (cough) I missed this crucial little nugget this first time
(the bind variable is case-sensitive)Really? Could it be that easy? I UPPERed RUNID and voila! It worked!
To recap, go to the Reports tab, right click on a folder (I have one named "profiler") and select Add Report.
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6YCEXcpZGqYoC7Ia_zWF5GtrGcMh6I4XfVngBX-cwSoxg7sr62iF5enMqQLIIwO9zIoVzKMUHTWzZP3ZhyphenhyphenDd9YpF5Ncbw5G-5YZc7IoA6sYvoOefslGhqtC0AZBMFptg2fZhlV5U989b2/s800/sqldev_step1.png)
I fill out the Name, Description and Tooltip (optional)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6rvLyEVf0qiYw_8nSXrdh-hcB7lwaOGIWPCffDToF7VjQmkQzpXrs_T-SU7Q9XS11Y2EVlhD7AKH2ogetFQ30KlAV7b3RqwhyQepkleeTsGfhrMshj5_5GTHuiLcHDeJjqQEymhvXAVyA/s800/sql_dev_step2.png)
Hit Apply which saves my report. Now I want a report that on PLSQL_PROFILER_UNITS that accepts the RUNID as an IN parameter.
First, create the report:
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEil6gE3Uf-080H4QIVVfUGCVmtbR5roPd5DO9oJVeriMcDY_Yf5sfW1TO41CtzBKH6C7t3pA6VIcbg3FVnBgVZMLyEuJGtuvSCMuox9RRLXpd9oN-e4u4dlzIpYoErmqAQDr-N4HgpJg0E/s800/sqldev_step3.png)
Go to the Binds tab and fill in the fields
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiaz14ST2vV7qhO25v8b6KKJ-TsH49HdoYaPn7efE_Dem6nvFlsNgD2ahOPVKcSYUXlhnvzaWNFbxUqqVRHQSmQp-9WGbpflpCpWiKHoYKQ2XOZigWPl25EgYMgyZd0X3lrCoTEEcd6Dobm/s800/sqldev_step4.png)
Go to the Advanced tab and fill in the name of the report
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhmDrKO9Nal-JSYVe5NqCRMJAJyuYg413-_5-AQja15uo90hYnugfsfbDoaJY7SDyvdgA7CsTMfexZBb0_U6rMCff_2DMKuZ3Sb1YUK-u7UxgYXshBreP0BT8vEuMTDx2SS2p9EQRvO9wI0/s800/sqldev_step5.png)
Now, select your first report, right click, go to Reports and select the report you just created
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgp6h3Tqobc4XHCESEalHRrENGFV7Pn8k6VaCwbAuc2nbved_ZUCe60tzxJKEGJ8NoBcEm9qAEuwKVg6BLUA6siLOgXzi8G-3CHdvwcmeYywIXGG8YI27w9ik6OhiuALftl0sy-V8-dEGfV/s800/sqldev_step6.png)
![](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj77BGk7v9EXEuY2-XwW63vnjtCHF0Xm-BVaZrtiOOKS2AkLHe8y1GfVNBDctZV_EIorhHU-S37M-bxrG_000eHGrF0oyqbIL4Z6T2k34ZbuusiI7xYmc-HeP0NHVHjLbljbW5IuMbotiem/s800/sqldev_step7.png)
Perfect!
Just a small reminder, the bind parameters are CASE SENSITIVE!
5 comments:
Not sure if it qualifies as a "Drill Down Report",... but I created some reports on PLSQL_PROFILER data some years ago
You know SQL Developer has built-in profiler functionality (right click on package/procedure>profile) hooking into DBMS_HPROF and reporting the results?
alex,
thanks for the link, i'll check them out. maybe i can "convert" them into sql dev reports.
chet
dom
no. thanks for telling me though.
that's what i get for not reading the entire manual.
at least i learned 2 new things though:
1. how to create a drill down report
2. i can profile anything more easily with sql dev
Excellent instrunctions. Thanks. In the newer versions of SQL Developer the Advanced Tab is now call Drill Down.
Post a Comment