Re: Any ideas if oracle development is reading and following up on Mr. Lewis's new book

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 29 Nov 2006 14:29:49 -0800
Message-ID: <1164839388.971836.206970@j72g2000cwa.googlegroups.com>

hpuxrac wrote:
> Charles Hooper wrote:
> > I know that Tom Kyte posted a review of the book on Amazon.com a while
> > ago.
> >
> > The "Cost-Based Oracle Fundamentals" book that Jonathan Lewis wrote is
> > a great book.
> >
> > A couple days after applying patch 9 to 10.2.0.2 on my server, we
> > started having problems with "ORA-07445: exception encountered: core
> > dump" errors every time clients executed simple SQL statements
> > containing LIKE, such as "select ID from PART where ID like '123%';
> > Each time this happened, a 4MB to 5MB log file was generated on the
> > server.
>
> Yikes ... just when I get closer to migrating to 10.2 you have to tell
> me this!
>
> >
> > Metalink is still working on a solution. Bug 5680308 was posted on
> > 11/24/06 reporting essentially the same core dump error, with no
> > apparent solution.
> >
> > 10053 traces are interesting. I updated the SR on Metalink that I
> > opened today to tell them what needs to be changed to work around the
> > ORA-07445, and then the ORA-00600 errors that appear when other SQL
> > statements are executed. Jonathan Lewis wrote is a great book.
>
> It took me a couple times to get started on that book. I picked it up
> and put it down a couple of times before now ... just finished chapter
> 9.
>
> I think it's going to be similar to Cary Millsap's Optimizing Oracle
> Performance where I am going to read it 3 or 4 times before most of the
> content sinks in. It's not exactly light reading.

Cary Millsap's book is very good, and does a nice job explaining 10046 traces. Those traces, however, fall sort of explaining why the following SQL statement returns an ORA-600 when optimizer_features_enable is set to 10.1.0.5 or greater on Oracle 10g R2 (even with patch 9 installed). This SQL statement is expected to return 1 row.

SELECT
  CASH_RECEIPT.CUSTOMER_ID,
  CASH_RECEIPT.CHECK_ID,
  CUSTOMER.NAME,
  CURRENCY.NAME,
  CASH_RECEIPT.AMOUNT,
  CASH_RECEIPT.POSTING_DATE
FROM
  CASH_RECEIPT,
  CUSTOMER,
  CURRENCY
WHERE
  CASH_RECEIPT.CUSTOMER_ID = CUSTOMER.ID   AND CUSTOMER.CURRENCY_ID = CURRENCY.ID(+)   AND ( CASH_RECEIPT.CUSTOMER_ID LIKE 'CUSTOMER%' ) ORDER BY
  1;

The cost based optimizer sees the foreign key relationship that is defined for CASH_RECEIPT.CUSTOMER_ID to CUSTOMER.ID and uses transitive closure to generate "CUSTOMER.ID LIKE 'CUSTOMER%'"

With optimizer_features_enable set to 10.2.0.1, the single table access path for CUSTOMER is calculated as 0.50, instead of 1.00 - this causes all kinds of havok later in the trace file.   Table: CUSTOMER Alias: CUSTOMER
    Card: Original: 216 Rounded: 1 Computed: 0.50 Non Adjusted: 0.50   Access Path: TableScan
    Cost: 6.04 Resp: 6.04 Degree: 0

      Cost_io: 6.00  Cost_cpu: 147179
      Resp_io: 6.00  Resp_cpu: 147179

kkofmx: index filter:"CUSTOMER"."ID" LIKE :B1   Access Path: index (RangeScan)
    Index: SYS_C004614
    resc_io: 2.00 resc_cpu: 15193
    ix_sel: 0.0023148 ix_sel_with_filters: 0.0023148     Cost: 1.00 Resp: 1.00 Degree: 1
  Best:: AccessPath: IndexRange Index: SYS_C004614

         Cost: 1.00 Degree: 1 Resp: 1.00 Card: 0.50 Bytes: 0

Later in the trace file, the highest nested loops cost on record:   Best NL cost:

179769313486231570000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000.00
          resc: 3.01 resc_io: 3.00 resc_cpu: 35947
          resp: 3.01 resp_io: 3.00 resp_cpu: 35947

Outer Join Card: 1.#J = max ( outer (1.#J), (outer (1.#J) * inner (2.00) * sel (0.5) )
Join Card - Rounded: 1 Computed: 1.#J

And then the fun at the end:
Number of join permutations tried: 3

---

    SORT resource      Sort statistics
      Sort width:        1198 Area size:     1048576 Max Area size:
209715200
      Degree:               1
      Blocks to Sort:       0 Row size:          101 Total Rows:
      1
      Initial runs:         2 Merge passes:        1 IO Cost / pass:
      2
      Total IO sort cost: 2      Total CPU sort cost: 1
      Total Temp space used: 1

*** 2006-11-28 15:53:27.666
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [15160], [], [], [], [], [], [], []

I don't know how anyone would be able to find this behavior without Jonathan's book. I just wish that it also covered versions 10.1.0.5 and greater.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Wed Nov 29 2006 - 16:29:49 CST