Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

Hard Parsing and Soft Parsing

The process of compiling a new cursor is referred to as hard parsing (and is worthy of a book all its own); within the context of this chapter it can be simplified to four steps:

Validation

The SQL syntax of the cursor is validated for correctness, and the objects (tables and columns) to which it refers are verified.

Compilation

The cursor is compiled into executable form and is loaded into the database’s shared pool. Its location in the shared pool is referred to as its address.

Execution plan calculation

Oracle’s cost-based optimizer (CBO) determines the best execution plan for the cursor and attaches it to the cursor.

Hash calculation

The ASCII value of each character in the cursor is totaled up and sent to a hash algorithm. The algorithm calculates a value to make the cursor easy to find for subsequent reuse. This value is referred to as the cursor’s hash value. We’ll look more at ASCII values later in this section.

A good deal of database latch activity takes place during these operations because Oracle cannot allow any underlying objects (tables and columns) to change while the cursor is being validated and compiled. The activity is almost entirely CPU-driven, so a portion of your database server’s CPU will be consumed while compilation occurs. More importantly, the really important work of fetching records will be delayed. Subsequent executions of the same cursor (either by the same program or by another) can avoid incurring the costly hard-parse ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page