Posted in Oracle

Cost-Based Oracle Fundamentals (Expert's Voice in Oracle) by Jonathan Lewis

By Jonathan Lewis

Jonathan Lewis is without doubt one of the world's best gurus during this box (he is usually quoted and reference through different best specialists, comparable to Tom Kyte – see for instance

Book can be strongly co-promoted with Tom Kyte's professional Oracle Database structure (1-59059-530-0)

Highlights traps for these migrating from Oracle 8i to 9i to 10g, in all probability fending off frequently disastrous functionality matters and downtime (=lost profit)

The first entire ebook written to enquire, describe, and reveal the equipment utilized by the price dependent Optimizer

Jonathan is one among only a few Oracle authors to keep up on-line improvements, errata and addenda pages, so the reader can be supported lengthy after the ebook is published

Show description

Read Online or Download Cost-Based Oracle Fundamentals (Expert's Voice in Oracle) PDF

Best oracle books

Beginning Oracle Programming

Oracle is the world's best relational database. It offers a wealthy atmosphere to shop, deal with, and extract information. while used adequately, Oracle offers an excellent origin upon which to construct firm scale functions. getting to know the Oracle database isn't an in a single day activity. It takes time, perform, and many actual international event.

By Thomas Kyte - Expert Oracle Database Architecture (3rd Edition) (2014-11-20) [Paperback]

Now in its 3rd version, this best-selling booklet keeps to convey you the superior pondering on easy methods to observe Oracle Database to supply scalable purposes that practice good and bring right effects. Tom Kyte and Darl Kuhn proportion an easy philosophy: "you can deal with Oracle as a black field and simply stick facts into it, otherwise you can know how it really works and make the most it as a robust computing setting.

Expert Oracle Exadata

Professional Oracle Exadata, 2d variation opens up the internals of Oracle's Exadata platform that you can absolutely enjoy the such a lot performant and scalable database equipment in a position to operating Oracle Database. This variation is fully-updated to hide Exadata 5-2 and Oracle Database 12c. in case you are new to Exadata, you will soon research that it embodies a metamorphosis in the way you take into consideration and deal with relational databases.

Extra info for Cost-Based Oracle Fundamentals (Expert's Voice in Oracle)

Sample text

Answer: Oracle still tries to use the actual value for db_file_multiblock_read_count—scaled up or down if we are reading from a tablespace with a nondefault block size. I actually had my db_file_multiblock_read_count set to 8, so it was silly to set the MBRC to 12, but for the purposes of the arithmetic the optimizer believed me, and then the run-time engine read the table 8 blocks at a time. Where did the extra 1 come from in the I/O cost when db_file_multiblock_read_count was set to 32? I don’t know.

In 10g, the packaged procedure was enhanced in several ways to allow you to report an execution plan for a single statement still in the view v$sql, or even to report a set of plans for all the statements returned by a query made against either v$sql or the Automatic Workload Repository (AWR) tables. sql and the dbms_xplan package if you want to keep abreast of the latest developments. It’s also critical to work on a stable platform when investigating the optimizer. In my case, the most important features of my starting test environment are • Block size 8KB • db_file_multiblock_read_count = 8 • Locally managed tablespaces • Uniform extents of size 1MB • Freelist space management—not automatic segment space management • optimizer_mode = all_rows • System statistics (cpu_costing) initially disabled There are various parameters that I might then adjust for different test cases, but I always start with this baseline.

Fm Page 26 Tuesday, September 27, 2005 7:02 AM 26 CHAPTER 2 ■ TABLESCANS view holds. Here, for example, is a query to discover the activity against a particular data segment (the t1 table used in earlier examples in this chapter) in a 10g database: select from where ; * v$segstat obj# = 52799 TS# OBJ# DATAOBJ# STATISTIC_NAME STATISTIC# VALUE ----- ---------- ---------- -------------------------- ---------- ---------4 52799 52799 logical reads 0 21600 4 52799 52799 buffer busy waits 1 0 4 52799 52799 gc buffer busy 2 0 4 52799 52799 db block changes 3 240 4 52799 52799 physical reads 4 23393 4 52799 52799 physical writes 5 10001 4 52799 52799 physical reads direct 6 0 4 52799 52799 physical writes direct 7 10000 4 52799 52799 gc cr blocks received 9 0 4 52799 52799 gc current blocks received 10 0 4 52799 52799 ITL waits 11 0 4 52799 52799 row lock waits 12 0 4 52799 52799 space used 14 0 4 52799 52799 space allocated 15 82837504 4 52799 52799 segment scans 16 4 This wealth of information is invaluable for troubleshooting—note particularly the items identifying contention points (buffer busy waits, gc buffer busy, ITL waits, row lock waits) and the explicit finger-pointing at tablescans and index fast full scans (segment scans).

Download PDF sample

Rated 4.67 of 5 – based on 42 votes