Oracle

November 23, 2009

_newsort_enabled, sql environment and CBO

Filed under: Uncategorized — srivenu @ 7:34 am

After reading some articles on Jonathan Lewis’s blog, I was running some sort tests in my test instance and observed that changing the parameter “_newsort_enabled” is not having an effect for a sql which is already loaded in the shared pool.

I used the following test case.

create 	table x (
	obn 	char(2000), 
	obn1 	char(2000),
	obn2 	char(2000)
	) 
	tablespace users
/

Insert 	into x
select  object_name, object_name, object_name
from	dba_objects
where	rownum <= 10000
/

commit;

********
session 1
********

alter session set "_newsort_enabled" = false
/

set autot trace stat

select 	*
from 	(
	select 	rownum, x.* 
	from 	x
	) 
order 	by obn
/

The sql_id for the sql is '0r2zx0v1aggvs'

********
session 2
********

Check the sql workarea

select 	sql_id, operation_type, TOTAL_EXECUTIONS
from 	v$sql_workarea
where 	sql_id = '0r2zx0v1aggvs'
/


SQL_ID           OPERATION_TYPE        TOTAL_EXECUTIONS
--------------   --------------------  ----------------
0r2zx0v1aggvs    SORT (v1)             1

********
session 3
********

alter session set "_newsort_enabled" = true
/

set autot trace stat

select 	* 
from 	(
	select 	rownum, x.* 
	from 	x
	) 
order 	by obn
/

********
session 2
********

Check the sql workarea 

SQL_ID           OPERATION_TYPE        TOTAL_EXECUTIONS
--------------   --------------------  ----------------
0r2zx0v1aggvs    SORT (v1)             2

I even confirmed the sort version from trace files after setting sort trace. It runs in v1 sort even after explicitly setting “_newsort_enabled” to true. So here was a parameter which is not considered by the optimizer but taken into effect by the run time engine. I used 10053 & 10132 (level 12) and I could not find “_newsort_enabled” listed in there.

Can i deduce the following from this observation ?

1) There is more environment information captured for a sql than what is listed in V$SYS_OPTIMIZER_ENV
2) The Optimizer ignores all environment information for a sql except that listed in V$SYS_OPTIMIZER_ENV
or
The Optimizer considers some of this additional info while creating child cursors. A change in this environment value might result in a new child cursor creation but since this parameter is not part of V$SYS_OPTIMIZER_ENV, the reason for this child cursor creation is not listed as “optimizer_mismatch” in V$SQL_SHARED_CURSOR. This is the reason that we do not see any reason listed in V$SQL_SHARED_CURSOR for some of the child cursors (I know that some cases like cursor_sharing similar can also cause this). (My question is it did not take into consideration the “_newsort_enabled” parameter, but there might be some it does isn’t it ?)
3) Some of this additional environment information which is ignored by the Optimizer is used by the run time engine and effects some of its decisions like the v1 vs v2 sort etc.

Advertisements

November 21, 2009

A little patience pays in digging deeper down your google search pages

Filed under: Miscellaneous — srivenu @ 11:29 am

Sometimes you get good docs or articles hidden deep down the search page results. I was searching for “hash join internals” and on page 8 of the search result i found this good doc – Hash Joins

Blog at WordPress.com.