Is it possible to add our own notes to the Toad Knowledgebases?

[flyfishpanfish]

Was poking TFO to find way to collect SQL a particular user had run in database
today and along the way found StatPack Browser and earmarked it for a later
look.

TFO Help tells me how to use the StatsPack Browser and the KB gave me a bit more
info (sort of) but still wasn’t sure about StatsPack so I went to my
O’Reilly Safari and found this:

“A Statspack report is generated from two snapshots of the database. These
snapshots are a copy of the contents of the various dynamic performance tables
that are continuously accumulating information about activity in the database.
Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor
(ADDM) were offered as replacements for Statspack beginning with Oracle Database
10g and are no longer documented in the Oracle manuals. However, you can use AWR
and ADDM only with Enterprise Edition and only if you have a license for
Diagnostics Pack—an extra-cost option available only with Enterprise
Edition. In my experiences, very few organizations have the requisite licenses.
I therefore emphasize the use of Statspack and use it a lot in my own work. Note
that Oracle did make several incremental enhancements to Statspack in Oracle
Database 10g and 11g , thus further increasing its usefulness and relevance.
However, you will have to refer to Chapter 21 of Oracle Database 9i Performance
Tuning Guide and Reference for documentation on Statspack because it is not
documented in the Oracle Database 10g and 11g manuals. A useful introduction to
Statspack as well as installation instructions can be found in spdoc.txt in the
ORACLE_HOME\rdbms\admin folder. “ Beginning Oracle Database 11g
Administration: From Novice to Professional by Iggy Fernandez.

I’d really like to have the last couple of sentences of the quote (or
similar info) stored in KB somewhere so I don’t lose it and can know where
to go when wondering about StatPack again.

Deborah M Flad, DBA
Delaware Children’s Department, State of Delaware
1825 Faulkland Rd, Wilmington DE 19805
Office: 302.633.2694 deborah.flad@state.de.us Emergencies: 302.528.3652 Txt:
www.vtext.com
Our website: www.kids.delaware.gov
How am I doing? Email my manager Eugene Mitchell at Eugene.Mitchell@state.de.us
with any feedback.
“Please consider your environmental responsibility before printing this e-mail”

---

Confidentiality Notice – This electronic mail transmission and any
attachment(s) are privileged and confidential and are intended only for the
review of the party to whom it is addressed. If you have received this
transmission in error, please immediately notify the sender and delete the
unintended message received. Unintended transmission shall not constitute waiver
of work product or any other privilege.

[HillbillyToad]

Yes, you can add custom notes to KXpert topics. What version of Knowledge Xpert
are you using?

[NormTeamT]

Morning Deborah,

Was poking TFO to find way to collect SQL a particular user
had run in database today and along the way found Statspack
Browser and earmarked it for a later look.

Just a warning about Statspack:

Statspack is nice, useful when you don't have other tools to help out.
But ....

The numbers you see in the Statspack reports are averaged (the mean)
over the time period between snapshots. It the time period is long, the
numbers tend to flatten out - so a query that reads 8 million blocks
from disc each execution would have it's figures spread over the time
period. You could see 8 million blocks per hour instead of over a 2
minute period.

Any code that starts within the period between the snapshots but
finishes outside the later snapshot will not show any resources used
during the reported period.

Ditto any job that started before the first snapshot and finished during
the monitored period - all it's stats will be dumped into your report.
This can skew things a little if the job that just finished had been
running for 20 hours!

I'm pretty certain that in order to collect the SQL for a user it needs
to meet some preset criteria such as the number of seconds used, blocks
read and so on. You also need to gather stats at level 6 or higher to
get the SQL.

Tom Kyte advises 5 minute snapshots for best results, bearing in mind
the caveats about jobs not starting and ending within the 5 minute
period affecting the results.

Oh and don't ignore the so called idle event "SQL*Net message from
client" it shows the client's "think" time and could show where the
response problem really is - in the application rather than the
database.

Have fun.

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

[Bert_Scalzo]

That’s why originally the TOAD stats pack screen would not give advice for
period longer than 30 minutes – which we thought was already excessive.

BUT – customer feedback was that it needed to be much longer – that
few people did
and Tom K. When doing stats pack (and even ADDM/AWR) snapshots, the finer the
granularity the more reliably and faster you’ll find problems J

[flyfishpanfish]

KB 10.1 for Oracle Admin and PL/SQL (Engine 10.1.0.188)

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
“Please consider your environmental responsibility before printing this e-mail”

[flyfishpanfish]

KB 10.1 for Oracle Admin and PL/SQL (Engine 10.1.0.188)

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
“Please consider your environmental responsibility before printing this e-mail”

[HillbillyToad]

You want to add a comment then, you also have the choice to make that visible to
other KXpert users.

[HillbillyToad]

You want to add a comment then, you also have the choice to make that visible to
other KXpert users.

[HillbillyToad]

You want to add a comment then, you also have the choice to make that visible to
other KXpert users.

[matt.wilkins]

In addition to KB, you can also add notes to Toad’s native ToadTips. Very
handy!

[matt.wilkins]

In addition to KB, you can also add notes to Toad’s native ToadTips. Very
handy!

[matt.wilkins]

In addition to KB, you can also add notes to Toad’s native ToadTips. Very
handy!

[matt.wilkins]

In addition to KB, you can also add notes to Toad’s native ToadTips. Very
handy!

[flyfishpanfish]

Thanks guys – took searching and reading! the Toad and KB Help files but
found how to add my own notes in both places. Cool.

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
"Please consider your environmental responsibility before printing this e-mail"

[flyfishpanfish]

Thanks guys – took searching and reading! the Toad and KB Help files but
found how to add my own notes in both places. Cool.

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
“Please consider your environmental responsibility before printing this e-mail”

[flyfishpanfish]

Thanks guys – took searching and reading! the Toad and KB Help files but
found how to add my own notes in both places. Cool.

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
“Please consider your environmental responsibility before printing this e-mail”

[flyfishpanfish]

Thanks guys – took searching and reading! the Toad and KB Help files but
found how to add my own notes in both places. Cool.

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
“Please consider your environmental responsibility before printing this e-mail”

[flyfishpanfish]

Thanks guys – took searching and reading! the Toad and KB Help files but
found how to add my own notes in both places. Cool.

Deborah M Flad, DBA
Office: 302.633.2694 Emergencies: 302.528.3652
"Please consider your environmental responsibility before printing this e-mail"

[Guest]

Hi, Norman,

Any code that starts within the period between the snapshots but
finishes outside the later snapshot will not show any resources used
during the reported period.

Ditto any job that started before the first snapshot and finished during
the monitored period - all it's stats will be dumped into your report.
This can skew things a little if the job that just finished had been
running for 20 hours!

Oracle has apparently corrected that problem in the 11g version of Statspack and it should be possible to manually fix the code in the 10g version of Statspack.

From the 10gR2 documentation: "Statistics displayed in V$SQL are normally updated at the end of query execution. However, for long running queries, they are updated every 5 seconds. This makes it easy to see the impact of long running SQL statements while they are still in progress." (V$SQL)

From the 11g version of spdoc.txt: "SQL statements which have not completed execution (e.g. have a 0 execution count as the statement was still running when the End snapshot was taken) can now appear in the SQL reports."

You also need to gather stats at level 6 or higher to
get the SQL.

We get the SQL text and execution metrics at level 5 (the default). I believe that level 6 produces the actual execution plans in addition to the SQL text and execution metrics.

Regards,

Iggy

[NormTeamT]

Morning Iggy,

Oracle has apparently corrected that problem in the 11g
version of Statspack and it should be possible to manually
fix the code in the 10g version of Statspack.
Excellent news, thanks for that. The closest I've been to an 11g
database is installing the 11gR1 docs on my desktop. 10g is "news tuff"
here where I am at the moment. I am working through the new features
guide though - slowly!

...

We get the SQL text and execution metrics at level 5 (the
default). I believe that level 6 produces the actual
execution plans in addition to the SQL text and execution metrics.
True, you get "high resource usage SQL statements" at level 5 - but no
execution plans. At level 6 you get plans and plan usage which makes the
output from "sprepsql" more informative.

Thanks for correcting me.

Looking forward to 11g even more now!

Cheers,
Norm. [TeamT]

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk

Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.

We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.

If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk