Wednesday, March 30, 2005
I have noticed a significant issue with the SimpleProfiler Utility, this issue is also likely to affect users of the Trace Analyzer Utility from Oracle. The issue is this, STAT lines that get emitted to the trace file after a new cursor has been parsed get associated with the new cursor rather than the cursor they refer to. This has the effect that some execution plans may be truncated whilst others may display incorrect information.
I will be working on this, but if you suspect that the execution plan that Simple profiler lists doesn't make sense or is truncated then the workaround is to run the trace file through tkprof (without the explain= option since that can also give incorrect results) and look for the plan recorded there.
0 Comments
I will be working on this, but if you suspect that the execution plan that Simple profiler lists doesn't make sense or is truncated then the workaround is to run the trace file through tkprof (without the explain= option since that can also give incorrect results) and look for the plan recorded there.
Wednesday, March 23, 2005
A little while back I posted a brief demo of the fact that whilst segment monitoring in 9i and 10g gives precise statistics, the statistics may not be accurate. I have been asked to recap this, so here is the question and the demo.
0 Comments
> Niall, monitoring will show table as used, but statistics is updated
> at the time of commit. First, there is no statistics in USER_TABLES that
> would count the number of times that blocks have been referenced by I/O
> and second, statistics for monitored tables is not updated until the
> transaction commits. To do otherwise would seriously break the relational
> rules.
Clearly I wasn't advocating using table monitoring but I would refer
you to the following piece of rule breaking
SQL> connect / as sysdba
Connected.
SQL> drop user u1;
User dropped.
SQL> create user u1 identified by u1
2 default tablespace users
3 temporary tablespace temp
4 quota unlimited on users;
User created.
SQL> grant create session,create table to u1;
Grant succeeded.
SQL> connect u1/u1
Connected.
SQL> select to_char(sysdate,'hh24:mi:ss') from dual;
TO_CHAR(
--------
20:39:18
SQL> create table t1 (col1 number);
Table created.
SQL> insert into t1
2 select rn
3 from (select rownum rn,'x' from all_objects,all_objects where rownum < 1001
);
1000 rows created.
SQL> rollback;
Rollback complete.
SQL> host time 23:55
SQL> select to_char(sysdate,'hh24:mi:ss') from dual;
TO_CHAR(
--------
23:55:06
SQL> select table_name,inserts,updates,deletes from useR_tab_modifications;
TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
T1 1000 0 0
SQL>
precise but wrong and certainly a violation of relational rules. This
is why Oracle refer to it as an estimate.
Tuesday, March 22, 2005
I have also today uploaded the presentation I recently gave to the UK Oracle Users Group DBMS sig. The presentation focuses on some practical applications of a response time based approach to Oracle tuning. There will be a small paper to accompany it in due course.
0 Comments
I was looking at the Known Issues for the 10.1.0.3 patchset on Windows, I mentioned these articles a little earlier this month, and there is an excellent example of the quality of information in them, that you maybe cannot get anywhere else. Issue 2 is probably not of huge importance, but you have to feel for the site that discovered issue 1.
10.1.0.3 README Additions
These are additions to the Patch Set README which are not documented in separate notes.
The installer allows you to install the Grid Control Patch Set in a Server ORACLE_HOME and vice-versa. Take extreme care chosing the ORACLE_HOME that the Patch Set is installed into to avoid needing to perform a complete reinstall.
pdh.dll should be removed after apply Patch Set 10.1.0.3.0 (This is Windows-only issue).
Workaround:
Delete pdh.dll from %ORACLE_HOME%\bin.
0 Comments
10.1.0.3 README Additions
These are additions to the Patch Set README which are not documented in separate notes.
Workaround:
Delete pdh.dll from %ORACLE_HOME%\bin.
Tuesday, March 15, 2005
Jonathan Lewis has a number of new articles up on his site. They are all well worth a read, but I thought that I'd add a little bit to his review of an article about tuning by silver bullets available at dbazine.
I found the multi-step silver bullet of fixing missing cbo statistics particularly unhelpful because of the following unfortunate errors.
alter table xxx delete/drop??? Statistics; isn't a valid command, in fact neither of the options given is a valid command.
Exec dbms_stats(...); isn't a valid command either.
The conclusion that the dba drew from the above was
The system immediately returned to an acceptable performance level and the DBA learned about the importance of providing complete and timely statistics for the CBO using the dbms_stats utility
Well clearly the dba could have learned no such thing from the published commands because they wouldn't actually work. Assuming the author intended that the dba should run analyze table delete statistics and then use dbms_stats to gather stats this tells you nothing about timeliness or completeness of statistics gatherng though it might tell you about a new feature that might make your life easier.
0 Comments
I found the multi-step silver bullet of fixing missing cbo statistics particularly unhelpful because of the following unfortunate errors.
alter table xxx delete/drop??? Statistics; isn't a valid command, in fact neither of the options given is a valid command.
Exec dbms_stats(...); isn't a valid command either.
The conclusion that the dba drew from the above was
The system immediately returned to an acceptable performance level and the DBA learned about the importance of providing complete and timely statistics for the CBO using the dbms_stats utility
Well clearly the dba could have learned no such thing from the published commands because they wouldn't actually work. Assuming the author intended that the dba should run analyze table delete statistics and then use dbms_stats to gather stats this tells you nothing about timeliness or completeness of statistics gatherng though it might tell you about a new feature that might make your life easier.
Friday, March 11, 2005
One of the issues that faces DBAs or others when considering applying patches to software is what effect the change will have on their existing applications. For example windows patches have been known to adversely affect the availability of Oracle databases.
In Oracle's case there is an often overlooked opportunity to discover known issues like this before you run into them rather than afterwards. This is because Oracle keeps a regularly update list of issues known to affect their database patchsets.
For Oracle 9.2 you should start at Note 189908.1
For Oracle 10g you should start at Note 279831.1
A valid support contract is required for these notes.
0 Comments
In Oracle's case there is an often overlooked opportunity to discover known issues like this before you run into them rather than afterwards. This is because Oracle keeps a regularly update list of issues known to affect their database patchsets.
For Oracle 9.2 you should start at Note 189908.1
For Oracle 10g you should start at Note 279831.1
A valid support contract is required for these notes.
Wednesday, March 09, 2005
The following question was asked on Oracle-L just recently.
We are sending the e-mails using the mailx client from the unix cron jobs.
The new requirement is, after the stored procedure is executed using the job
scheduler (dbms_job), we would like to send the e-mail from Oracle. Is there
a way to send the e-mail from oracle?
The answer to this does depend on version and requirements, but yes you can send emails from Oracle in all versions from 8.1.7 up.
If you just want simple mail then the UTL_SMTP package will allow you to send emails quite simply. I have uploaded a sample package that you can use for this purpose here. If you want to send attachments or large messages (larger than the 32k varchar2 limit) then prior to 10g you will need to adopt some other technique. One possible technique is to load a fully capable Java mail client into the database and create a java stored procedure for this code. Tom Kyte covers this in his Expert one-on-one Oracle book.
It is also worth looking at the HTMLDB 1.6 release which uses a wrapper around UTL_SMTP to queue and send mail. This asynchronous queue related technique is suitable for high volume mailings as typically a mail server session will not respond in a suitable timeframe for a transaction processing application.
10g itself introduces the UTL_MAIL package. This allows attachments etc to be sent.
0 Comments
We are sending the e-mails using the mailx client from the unix cron jobs.
The new requirement is, after the stored procedure is executed using the job
scheduler (dbms_job), we would like to send the e-mail from Oracle. Is there
a way to send the e-mail from oracle?
The answer to this does depend on version and requirements, but yes you can send emails from Oracle in all versions from 8.1.7 up.
If you just want simple mail then the UTL_SMTP package will allow you to send emails quite simply. I have uploaded a sample package that you can use for this purpose here. If you want to send attachments or large messages (larger than the 32k varchar2 limit) then prior to 10g you will need to adopt some other technique. One possible technique is to load a fully capable Java mail client into the database and create a java stored procedure for this code. Tom Kyte covers this in his Expert one-on-one Oracle book.
It is also worth looking at the HTMLDB 1.6 release which uses a wrapper around UTL_SMTP to queue and send mail. This asynchronous queue related technique is suitable for high volume mailings as typically a mail server session will not respond in a suitable timeframe for a transaction processing application.
10g itself introduces the UTL_MAIL package. This allows attachments etc to be sent.
Tuesday, March 08, 2005
I was browsing Mark Rittman's Blog and noticed that he has published further details of his project to enable instrumentation of Oracle Warehouse Builder processes. Mark has already made significant progress in terms of proof of concept, and the whole article and the associated blog entries make for some fine real world application of the uses of Extended SQL trace.
0 Comments
I have today updated the SimpleProfiler utility. This is available unsurprisingly on the SimpleProfiler Page. Thanks are due to a number of people who have provided useful feedback. Thanks then to Holger Baer, Simon, and Andy Helm. I have also added a feedback link to the new version as I always welcome constructive criticism.
Version 0.9 contains a number of enhancements and bug fixes. In particular the following should be noted
The chart display is now customizable. I have made this change since discovering that the SVG plugin could in fact crash certain browsers, notably Mozilla Firefox. To control which chart displays note the customize link at the foot of the page.
I have added a preliminary business process repository.
The correct version of the package body is now shipped, this will compile.
Future developments are listed in the readme, I will be looking especially at completing a help system with the 1.0 release and at improvements to the business process repository and trending.
0 Comments
Version 0.9 contains a number of enhancements and bug fixes. In particular the following should be noted
Future developments are listed in the readme, I will be looking especially at completing a help system with the 1.0 release and at improvements to the business process repository and trending.
Tuesday, March 01, 2005
Don Burleson has a couple of new articles out that are somewhat educational reads. The first is a 'Challenge' to build a set of predictive tests to allow you to predict when a table or index should be reorganized. This has led to at least 2 or 3 discussions about the challenge in particular (there is one on the boards that Don owns) and one over here at AskTom. They are certainly worth reading for some insight into the ongoing (maybe never-ending) discussions between those who want to understand why and how something happens, and those who want a set of quick fixes or rules of thumb.
The second article looks at the issue of credibility. Don states that he looks for
Experience at Oracle corporation
Computer Science Background
Computer Science Research (specifically ACM and IEEE)
He also clearly regards having books published as an indicator of credibility. Of the 9 people he chooses to highlight, 7 are published authors of books on Oracle.
He also states that
the Oracle Oak Table group claims to be “a network for the Oracle scientist”, yet I was concerned when I could not locate some of their members scientific research, academic achievements, awards, computer science conference proceedings, nor their membership in professional computer science organizations such as ACM or IEEE.
He probably has me, amongst others in mind, the Google search for a CV or Resume which he says he uses doesn't actually throw up my resume (because it isn't on-line) (though it does throw up VB code with On Error Resume Next in it, the perils of searching).
I'd agree that the ability to distinguish good advice from bad, and sound thinking from unsound is very important. I don't think that the Web helps much here. On the other hand CV information, even if you can get it, may not be that much of a help. For example Don appears to have none of the qualifications he looks for in others, his degree is in Psychology, he has never worked for Oracle, if you use the free scholar service provided by Google, you will find that whilst Don's books are listed and his presentations at IOUG count (the UKOUG doesn't show in these searches) he is cited just the once. That citation is in itself an interesting and accurate description of the checklist approach.
"Traditional approaches ([B01], [G01], [ORCLT99]) rely primarily on graphing and examining a small number of system parameters. Determining which system resources to investigate is at best a lengthy process of educated guesswork, where many problems can run undetected." (B01 is the citation, the paper is here).
There is of course another thing that is important in determining credibility, and that is the content of what the author is proposing. The credibility article is somewhat excellent in this regard.
Don - who, according to his resume, got his degree in Psychology in 1979 and started work in 1983 - claims in the article to have been using email in the 70s, one assumes that this was the University in-house email system rather than public discourse, and 'the web' at the same time period. This is really quite a remarkable claim given that Tim Berners-Lee had not yet even started upon Enquire, which itself would not become the web until 1991 or so. For some reason I am reminded of Al Gore. Now, it is true that people confuse the web and the internet all the time, but how credible is a Adjunct Professor in an IT field who does the same thing?
Now I am sure that Don is not arguing that he himself has little or no credibility in his own eyes (and we do know that he is a prolific publisher and author), but I remain confused as to why a published CV is apparently more important than attempting to understand and demonstrate behaviours.
In the mean-time and for what its worth I do have a degree - in Economics from the University of Bristol, I already have a job and am not actively looking for another, and if you care to read my presentations you will find them here or at the websites of the relevant conferences.
Niall.
0 Comments
The second article looks at the issue of credibility. Don states that he looks for
He also clearly regards having books published as an indicator of credibility. Of the 9 people he chooses to highlight, 7 are published authors of books on Oracle.
He also states that
the Oracle Oak Table group claims to be “a network for the Oracle scientist”, yet I was concerned when I could not locate some of their members scientific research, academic achievements, awards, computer science conference proceedings, nor their membership in professional computer science organizations such as ACM or IEEE.
He probably has me, amongst others in mind, the Google search for a CV or Resume which he says he uses doesn't actually throw up my resume (because it isn't on-line) (though it does throw up VB code with On Error Resume Next in it, the perils of searching).
I'd agree that the ability to distinguish good advice from bad, and sound thinking from unsound is very important. I don't think that the Web helps much here. On the other hand CV information, even if you can get it, may not be that much of a help. For example Don appears to have none of the qualifications he looks for in others, his degree is in Psychology, he has never worked for Oracle, if you use the free scholar service provided by Google, you will find that whilst Don's books are listed and his presentations at IOUG count (the UKOUG doesn't show in these searches) he is cited just the once. That citation is in itself an interesting and accurate description of the checklist approach.
"Traditional approaches ([B01], [G01], [ORCLT99]) rely primarily on graphing and examining a small number of system parameters. Determining which system resources to investigate is at best a lengthy process of educated guesswork, where many problems can run undetected." (B01 is the citation, the paper is here).
There is of course another thing that is important in determining credibility, and that is the content of what the author is proposing. The credibility article is somewhat excellent in this regard.
Don - who, according to his resume, got his degree in Psychology in 1979 and started work in 1983 - claims in the article to have been using email in the 70s, one assumes that this was the University in-house email system rather than public discourse, and 'the web' at the same time period. This is really quite a remarkable claim given that Tim Berners-Lee had not yet even started upon Enquire, which itself would not become the web until 1991 or so. For some reason I am reminded of Al Gore. Now, it is true that people confuse the web and the internet all the time, but how credible is a Adjunct Professor in an IT field who does the same thing?
Now I am sure that Don is not arguing that he himself has little or no credibility in his own eyes (and we do know that he is a prolific publisher and author), but I remain confused as to why a published CV is apparently more important than attempting to understand and demonstrate behaviours.
In the mean-time and for what its worth I do have a degree - in Economics from the University of Bristol, I already have a job and am not actively looking for another, and if you care to read my presentations you will find them here or at the websites of the relevant conferences.
Niall.
