March 2010

calculating the SQL_ID hash from the SQL_TEXT

This really cool:

he explains a how to calculate the sql_id from the SQL_TEXT. The SQL_ID is just a hash of the text.

Analyze this

Here’s a little script I wrote a few years ago to make a point about using the dbms_stats package. I’ve just re-run it on 10.2.0.3 to see if it still behaves the way it used to – and it does. If you want to be just a little bit baffled, set up a database with [...]

Oracle Plan Stability (Stored Outlines) in PeopleSoft Global Payroll

A longer version of this posting, with experimental results, is available on my website.

In PeopleSoft for the Oracle DBA, I wrote a page (p. 291) explaining why stored outlines were not suitable for use in PeopleSoft.  Five years later, my view has not significantly changed.  Essentially, stored outlines work best with shared SQL, and there isn't much shared SQL in PeopleSoft, because a lot of it is dynamically generated.

Calculate SQL_ID and SQL_HASH_VALUE from SQL text

Some time ago I wrote an article about the 10g+ SQL_ID being just a hash value of the SQL statement text. It’s just like the “old” SQL_HASH_VALUE, only twice longer (8 last bytes instead of 4 last bytes of the MD5 hash value of SQL text).

Slavik Markovich has written a nice python script for calculating SQL_IDs and SQL hash values from SQL text using that approach.

Slavik’s article is available here:

http://www.slaviks-blog.com/2010/03/30/oracle-sql_id-and-hash-value/

Share/Bookmark

You Buy a NUMA System, Oracle Says Disable NUMA! What Gives? Part III.

By The Way, How Many NUMA Nodes Is Your AMD Opteron 6100-Based Server?
In my on-going series about Oracle Database 11g configuration for NUMA systems I’ve spoken of the enabling parameter and how it changed from _enable_NUMA_optimization (11.1) to _enable_NUMA_support (11.2). For convenience sake I’ll point to the other two posts in the series for folks [...]

PeopleSoft for the Oracle DBA

A PeopleSoft Survival Guide for DBAs and other technicians

A part of the OakTable Press series, PeopleSoft for the Oracle DBA, will teach you a range of techniques for maintaining a PeopleSoft system. You will then become able to implement techniques like indexing, implementing DDL, managing tablespaces, and fixing low-performing SQL queries. It provides answers to common questions that arise when using PeopleSoft on an Oracle database.

The book begins with an architecture overview, then proceeds to BEA Tuxedo, PeopleSoft's application server. Subsequent chapters explain database structures, connectivity, keys and indexing, the PeopleSoft DDL generation, tablespaces organisation, performance metrics, performance monitoring utilities, and SQL optimization techniques.

WLS + Oracle benchmark – 2

Another press release from Oracle on the same topic: benchmark of WLS 10.3.3 (not released yet) + Oracle 11.2.0.1 @ Dell PowerEdge R910 Server with 4×8-core beasts X7560 (announced today by Intel). Benchmark was done by Oracle itself (previous were conducted by Cisco & HP). Results and FDA are here. DB & WLS configuration are [...]