Jun 24 2010
Troubleshooting Oracle Performance – Downloadable Files
This is just a short note to point out that I just uploaded a new version of the scripts related to TOP. The new ZIP is available through this page.
The change log is the following:
| connect.sql | Added DBM10205, DBA10205, DBM11201 and DBA11201 |
| chapter02\bind_variables.sql | Because of 11g modified/added queries against V$SQL_SHARED_CURSOR |
| chapter02\sharable_cursors.sql | Added SET SERVEROUTPUT OFF in the initialization part |
| chapter03\dbms_hprof.sql | New file |
| chapter03\sql_trace_trigger.sql | New file |
| chapter06\display_awr.sql | Improved query that displays AWR content |
| chapter06\execution_plans.sql | Added example for UNION ALL (RECURSIVE WITH) |
| chapter07\baseline_evolution_delete.sql | New file |
| chapter07\baseline_upgrade_11g.sql | After import added update to set the owner of the SQL tuning set |
| chapter07\opt_estimate.sql | Uncommented 11g query |
| chapter07\outline_with_hj.sql | Script compatible with 10g/11g (set “_hash_join_enabled”) |
| chapter07\tune_last_statement.sql | Added SET SERVEROUTPUT OFF in the initialization part |
| chapter08\client-side_caching.sql | New file |
| chapter09\conditions.sql | Added queries containing NOT IN condition |
| chapter09\hash_cluster.sql | Changed comment related to IN operator because of 11.2 improvement |
| chapter10\hash_join.sql | Fixed typo in description |
| chapter10\join_elimination.sql | Fixed typo in description |
| chapter10\join_elimination2.sql | New file |
| chapter10\pwj.sql | Disabled join-filter pruning |
| chapter10\subquery_unnesting.sql | Cover many more cases |
| chapter11\ArrayInterface.java | Added check for the return value of the executeBatch method |
| chapter11\ArrayInterfacePerf.java | Fixed number of iterations in main method |
| chapter11\atomic_refresh.sql | Changed CTAS to avoid ORA-30009 |
| chapter11\dpi_performance.sql | Changed CTAS to avoid ORA-30009 |
| chapter11\px_auto_dop.sql | New file |
| chapter11\px_ddl.sql | Changed the part displaying the parallel DDL status |
| chapter11\px_dml.sql | Changed the part displaying the parallel DML status |
| chapter11\px_query.sql | Changed the part displaying the parallel query status |
| chapter11\result_cache_plsql.sql | Added comment about invalidation in 11.2 |
| chapter12\data_compression.sql | Changed CTAS to avoid ORA-30009 |
| databases\DBA10205 | New directory containing the files to create the database DBA10205 |
| databases\DBM10205 | New directory containing the files to create the database DBM10205 |
| databases\DBA11201 | New directory containing the files to create the database DBA11201 |
| databases\DBM11201 | New directory containing the files to create the database DBM11201 |
October 7th, 2010 at 8:25 am
Sir,
The zip file doesn’t contain the table creation scripts that you have used in demos. Kindly provide that also.
October 7th, 2010 at 8:38 am
Hi
Mhmm… almost every script creates the tables it needs… With which demo do you have problems?
Cheers,
Chris
October 7th, 2010 at 10:08 am
Sir,
I am talking about t1,t2,… tables.I couldn’t find them.
Many thanks
October 7th, 2010 at 11:07 am
Hi
IIRC the only scripts that do not create t1, t2, … are the ones of chapter 10. In fact, those scripts call the script create_tx.sql for that purpose. If they are not automatically created when you run a script it probably means that your current directory is not the onewhere the scripts are stored.
HTH
Chris
October 8th, 2010 at 7:58 am
Sir,
Thanks for the reply.I found them in the folders.
BTW,when are you planning to write a book on instance tuning? Or it would have been wonderfull if the TOP had a section for instance tuning also.That certainly would have completed the meaning of TOP.
October 11th, 2010 at 8:20 am
Hi
> BTW,when are you planning to write a book on instance tuning?
I do not plan to write another book. The only thing I can think of, is to provide a second edition of TOP….
> Or it would have been wonderfull if the TOP had a section for instance tuning also.
I basically agree with you. But, since time is restricted, I had to make a choice. And, honestly, instance tuning is irrelevant in most situations with the current releases. In fact, most of the time I see customers struggling with either poor hardware performance or applications-related issues.
Cheers,
Chris
October 11th, 2010 at 9:11 am
Sir,
You are absolutely correct. Oracle has automated so many things like ASMM etc that there is no need to worry about sizing memory structures.But at times we do get contentions in shared memory,lock issues,Buffer cache contentions etc.Since you got an amazing talent to write and explain things,it would be a great help to the oracle community if you could enlighten the subject.