Re: Another tough question

I don't think ADDRESS in v$sqlarea is every duplicated. It is the address of the parent object in x$kglcursor (x$kglob).

Although the grouping of x$kglob is by several columns (hash value parent address first load time, text), I think they all ensure that the address is unique within the view.

The non-unique item in 'standard' join is the sql_hash_value, since a completely different pair of texts may easily has to the same value. and it is the address that is used to acquire the corrrect object.

However -
a) It is not a good idea to query v$sqlarea anyway because it is a grouped view, and this can have a heavy impact on a large shared pool and

b) the hash value is the only 'pseudo=indexed' column in the view, so is the only efficient way to get information out of v$sql and v$sqlarea. -- On later versions of Oracle EXPLAIN PLAN will cope with v$ and x$ objects and tell you the internal 'indexed access' path.

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Sybrand Bakker wrote in message
<932298112.10600.0.pluto.d4ee154e_at_news.demon.nl>...
>Oracle determines a hash value of every statement. This hash value
>determines whether a statement already exists in the sqlarea. I would
expect
>address is the address in the sqlarea of the statement. This leaves the
>question why address alone is non-unique.
Received on Mon Jul 19 1999 - 06:27:03 CDT