"Oradba_Linux" <techiey2k3_at_comcast.net> wrote in message
news:3PWdne6ZHNhKZR7fRVn-2w_at_comcast.com...
> DA Morgan wrote:
>> Jonathan Lewis wrote:
>>
>>>> Am I missing something? Hopefully not something too obvious.
>>>
>>>
>>> Try running a query that requires some temp space.
>>> The column is not reported if none of the rows
>>> have populated it. (The default is null, not zero).
>>>
>>> rem 9.2.0.6
>>> rem optimizer_mode = all_rows
>>> rem system stats have been gathered
>>>
>>> explain plan for
>>> select source from sys.source$ order by source;
>>>
>>> select * from table(dbms_xplan.display);
>>>
>>> PLAN_TABLE_OUTPUT
>>> -----------------
>>>
>>> ---------------------------------------------------------------------------------
>>>
>>> | Id | Operation | Name | Rows | Bytes |TempSpc|
>>> Cost (%CPU)|
>>> ---------------------------------------------------------------------------------
>>>
>>> | 0 | SELECT STATEMENT | | 1324K| 2528M| |
>>> 724K (1)|
>>> | 1 | SORT ORDER BY | | 1324K| 2528M| 5172M|
>>> 724K (1)|
>>> | 2 | TABLE ACCESS FULL | SOURCE$ | 1324K| 2528M| |
>>> 4128 (2)|
>>> ---------------------------------------------------------------------------------
>>>
>>>
>>> 8 rows selected.
>>
>>
>> Well I'm the one that wrote: "Hopefully not something too obvious."
>>
>>
>> SQL*Plus: Release 10.1.0.4.0 - Production on Fri May 6 18:19:12 2005
>>
>> Copyright (c) 1982, 2005, Oracle. All rights reserved.
>>
>>
>> Connected to:
>> Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
>> With the Partitioning, OLAP and Data Mining options
>>
>> SQL> set linesize 121
>> SQL> explain plan for
>> 2 select source from sys.source$ order by source;
>>
>> Explained.
>>
>> SQL> select * from table(dbms_xplan.display);
>>
>> PLAN_TABLE_OUTPUT
>> ---------------------------------------------------------------------------------------
>>
>> Plan hash value: 995087943
>>
>> --------------------------------------------------------------------------------------
>>
>> | Id | Operation | Name | Rows | Bytes |TempSpc| Cost
>> (%CPU)| Time |
>> --------------------------------------------------------------------------------------
>>
>> | 0 | SELECT STATEMENT | | 342K| 42M| | 12285 (1)|
>> 00:02:28 |
>> | 1 | SORT ORDER BY | | 342K| 42M| 92M| 12285 (1)|
>> 00:02:28 |
>> | 2 | TABLE ACCESS FULL| SOURCE$ | 342K| 42M| | 2197 (1)|
>> 00:00:27 |
>> --------------------------------------------------------------------------------------
>>
>>
>> 9 rows selected.
>>
>> SQL>
>>
>>
>> Thanks.
>
> 10G displays the SQL hashvalue in the explain plan.
> But is hash value always unique?
No. The concept of any hashing algorithm is to
reduce the number of comparisons you have to
make between the value you have and the value
you are checking against.
With a large enough number of digits, a hash
value is LIKELY to be nearly a unique identifier.
But in 10g, Oracle has added a new SQL_ID
column with has more bits in it to improve the
probability of uniqueness.
To guarantee uniqueness in v$sql, you need to
use the address and child_number - but for an
efficient access path, you need to use the hash_value
(or sql_id) to get started.
--
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated April 5th 2005
Received on Fri May 13 2005 - 01:35:24 CDT
