OT: Tips and Tricks

Toad for Oracle
1

I’ve recently updated my trips and tricks guide for Toad, you can find it
on Toad World here:

http://toadworld.com/Portals/0/ToadTips/TipsAndTricksToadOra106.pdf

I re-wrote most of it, hopefully it’s a much easier read now. It’s
also updated for v10.6, but should work for just about any version of Toad since
v9.

I’d be more than happy to offer a 60-90 minute training over LiveMeeting
like we did awhile back if anyone would find any value in that.

Jeff

2

Hey Jeff,

I've recently updated my trips and tricks guide for Toad, you can find it on
Toad World here:
http://toadworld.com/Portals/0/ToadTips/TipsAndTricksToadOra106.pdf

I re-wrote most of it, hopefully it's a much easier read now. It's also
updated for v10.6, but should work for just about any version of Toad since
v9.

Great stuff! That's the kinda info I need to remind me to get out of my
10-year-old Toad shell and use the newer stuff.

Thanks!

Rich -- [TeamT]

Disclaimer: One week does not a season make.

3

I can’t get Execution Profiling to work, I click on the Stopwatch icon and run my PL/SQL (an anonymous block) and nothing happens. It only seems to work if I execute the PL/SQL from the Execution window in the Schema Browser (I can paste anything in there and it works).

I’ve added what you’ve described as an enhancement request in the Idea Pond but it looks like it might be a bug?

4

Open the stored proc in the editor, toggle on Profiler, hit execute.

Afterwards, check out the Profiler Analysis window, refresh. You should see your
profiler run.

I’ve always ran it from the editor, but it should be identical behavior
from running it via the Schema Browser.

5

OK, I think this is the difference:

  1. Type this into the SE

BEGIN
NULL;
END;

  1. Toggle on the Profiler button
  2. Execute the PL/SQL

Nothing happens.

But, if I:

  1. Open the SB
  2. Click on a Package (any one)
  3. Click on the Execute button
  4. Type the following into the Execute window (replacing/ignoring the existing text)

BEGIN
NULL;
END;

  1. Toggle on the Profiler
  2. Execute the SQL

I get a Profiler entry.

I’d like to be able to run any PL/SQL straight from the SE - even anonymous blocks just like you can from the Schema Browser.

6

Interesting.

What value do you get from profiling a NULL; Anon block?

Are you saying you want to execute a script which has many PL/SQL programs in it for profiling purposes? You should be able to do that I think too.

7

I want to be able to enter any anonymous block in the SE and profile it. The NULL example was about as simple as I could write, I just used it so that you could see that you do get a Profile if you run it from the SB but you don’t get one from the SE.

I’d even like to be able to highlight an anonymous block in the SE (assuming I have others and SQL statements etc in the same tab) toggle the Profiler on, Shift+F9, and view the result.

Often I’m executing packages direct from the SE (in fact, I’m mostly doing this, I very rarely execute from the SB) and sometimes I might be executing more than one:

BEGIN
package_a.proc_a;
package_b.proc_b;
END;

I want to be able to highlight this, toggle on and execute it and then be able to view the results.

8

Hi;

I would like to understand the solution proposed by Oracle Tunning Advisor on a
SQL request.

The ADDM rapport indicate the following request :

RECOMMENDATION 1: SQL Tuning, 100% benefit (6487 seconds)
ACTION: Examinez la possibilité d’améliorer les performances de
l’instruction SQL où SQL_ID = “8rv158cfpqfda”.
RELEVANT OBJECT: SQL statement with SQL_ID 8rv158cfpqfda and
PLAN_HASH 1772850126
SELECT
CONCAT(CONCAT(CONCAT(CONCAT(TO_CHAR(( Date_INDIC_TRIER.VA_SEMAINE
),‘00’),’/’),( Date_INDIC_TRIER.VA_ANNEE )),’ - Date début de semaine
: '),TO_CHAR(( Date_INDIC_TRIER.DT_DEBUT_SEMAINE ),‘dd/mm/YYYY’))
FROM
( SELECT DISTINCT QS_INDIC_TOURNEE.VA_ANNEE ,
QS_INDIC_TOURNEE.VA_SEMAINE , QS_INDIC_TOURNEE.DT_DEBUT_SEMAINE
FROM
QS_INDIC_TOURNEE
ORDER BY
QS_INDIC_TOURNEE.VA_ANNEE , QS_INDIC_TOURNEE.VA_SEMAINE )
Date_INDIC_TRIER
WHERE
(
( ( Date_INDIC_TRIER.VA_ANNEE ) > TO_NUMBER(TO_CHAR(EXTRACT(YEAR
FROM SYSDATE) - 2 , ‘0000’))
OR
(( Date_INDIC_TRIER.VA_SEMAINE ) > TO_NUMBER(TO_CHAR(SYSDATE, ‘IW’))
AND
( Date_INDIC_TRIER.VA_ANNEE ) = TO_NUMBER(TO_CHAR(EXTRACT(YEAR FROM
SYSDATE) - 2 , ‘0000’))) )
)

RATIONALE: L’instruction SQL où SQL_ID = “8rv158cfpqfda” a été exécutée
1458 fois, avec un temps écoulé moyen de 0.76 secondes.

Oracle Tunning Advisor propose the following solution :

1- SQL Profile Finding (see explain plans section below)

9

Write a dummy PROC that does nothing.

Then execute that via the Editor, and insert your anon block.

I think the editor would need changed to allow what you’re saying, as I
understand it.

10

Well, I can’t read French, but I think Oracle proposes that you implement this
profile. It will use this to have a better execution plan for your query.

execute dbms_sqltune.accept_sql_profile(task_name => ‘8rv158cfpqfda’, replace =>
TRUE);

11

Hi John;

I have executed the plan propose by Oracle but any Improvement ?

You have a idea on the optimisation of initial query ?

Regards;

12

Sorry, I don’t. You should try on a group that is just about Oracle. We try to
stick to Toad here.