Sokrates on Oracle

Archive for the ‘11g’ Category

DDL_LOCK_TIMEOUT does not prevent ORA-14450

Posted by Matthias Rogel on 29. August 2011

According to doc, “DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue.”

Actually, I don’t know exactly what is meant by that ( I have some idea what a “DML lock queue” could be, but where is the exact definition ? ), but it doesn’t help in synchronizing DDL and DML operations on a temp object:

SQL> create global temporary table t(v varchar2(1)) on commit preserve rows;

Table created.

SQL> declare
2 pragma autonomous_transaction;
3 begin
4 insert into t values('a');
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> alter session set ddl_lock_timeout = 100;

Session altered.

SQL> set timi on
SQL> alter /* expect exception after 100 seconds */ table t modify(v varchar2(2));
alter /* expect exception after 100 seconds */ table t modify(v varchar2(2))
*
ERROR at line 1:
ORA-14450: attempt to access a transactional temp table already in use

Elapsed: 00:00:00.05
SQL> set timi off
SQL> select type from v$lock where id1 = ( select object_id from user_objects where object_name = 'T' );

TYPE
------
TO

SQL> select description from v$lock_type where type = 'TO';

DESCRIPTION
--------------------------------------------------------------------------------
Synchronizes DDL and DML operations on a temp object

Looks to me that DDL_LOCK_TIMEOUT should behave other than it does.

Advertisements

Posted in 11g, Allgemein | Leave a Comment »

utl_file.fopen raises exception when file to be opened has more than one name

Posted by Matthias Rogel on 15. June 2011

On Unix, creating a hard link is just the same as giving a file another name.
I observed an interesting issue when using utl_file with hard links:

11.2.0.2.0 > create directory tmp as '/tmp';
Directory created.
11.2.0.2.0 > !echo hi > /tmp/t.txt
11.2.0.2.0 > declare
2 f utl_file.file_type;
3 l varchar2(100);
4 begin
5 f:= utl_file.fopen('TMP', 't.txt', 'r');
6 utl_file.get_line(f, l, 100);
7 utl_file.fclose(f);
8 dbms_output.put_line('line = "' || l || '"');
9 end;
10 /
line = "hi"
PL/SQL procedure successfully completed.

so far, so good

now:

11.2.0.2.0 > !ln /tmp/t.txt /tmp/another_name_for_t.txt
11.2.0.2.0 > /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation
ORA-06512: at line 5

utl_file.fopen throws exception when file to be opened has more than one name.
I wonder why

Posted in 11g, PL/SQL | Leave a Comment »

A Hint without +

Posted by Matthias Rogel on 4. June 2011

I always though, every hint would start with /*+ or –+, SQL reference   http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements006.htm#SQLRF00219 also says that.

But I learnt something new on

http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm

about the

adaptive cursor sharing hint (acs_1)

:

The same on

http://www.dba-oracle.com/t_cursor_sharing_similar.htm :

an “11g adaptive cursor sharing tip

Interesting, isn’t it ?

Couldn’t find something about this hint without a + in the docs nor on support.

But didn’t optimizermagic himself write about it on http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html :

Plenty of

/*ACS_1*/

‘s there ! ( as on http://optimizermagic.blogspot.com/2009/04/update-on-adaptive-cursor-sharing.html )

Well, they only use /*ACS_1 */ as a marker comment to find the sql later on in v$sql, so they can demonstrate the new 11g adaptive cursor sharing feature.

I arrived to a conclusion how this “tip”  originated and had a great laugh !

Should everyone who read this come to his own conclusions

By the way, on http://optimizermagic.blogspot.com/2007/12/why-are-there-more-cursors-in-11g-for.html , they indeed announce a hint to mark a statement as bind aware:

Q: Are you planning a hint to mark statements as bind-aware ?
A: Yes, we plan to add this in the future. This will allow users to bypass the startup cost of automatically determining that a query is a good candidate for bind-aware cursor sharing.

Seems to me that it is the BIND_AWARE hint mentioned in Bug 9532657 :

For cursors where one knows it wants to be bind aware then add a 
/*+ BIND_AWARE */ hint. 
This makes the cursor aware of bind values rather than automatically 
trying to work out if it should change from non-aware to aware 
based on execution statistics.
...
"

Can’t check it now because I have no access to an 11g at the moment, will check it later on and update this not.

Update:

seems to be correct, BIND_AWARE ( which does occur in V$SQL_HINT ) suffices to mark an sql bind aware, as long as statistics do exist:

sokrates@11.2 > create table t ( i int );

Table created.

sokrates@11.2 > exec dbms_stats.gather_table_stats(user, 'T', cascade => true)

PL/SQL procedure successfully completed.

sokrates@11.2 > variable n number
sokrates@11.2 > exec :n := 1

PL/SQL procedure successfully completed.

sokrates@11.2 > select /*+BIND_AWARE */ * from t where i=:n;

no rows selected

sokrates@11.2 > select /*ACS_1 */ * from t where i=:n;

no rows selected

sokrates@11.2 > select /*placebo */ * from t where i=:n;

no rows selected

sokrates@11.2 > select is_bind_sensitive, is_bind_aware, sql_text from v$sql
where sql_text like 'select /*ACS_1%' or sql_text like 'select /*+BIND%' or sql_text like 'select /*placebo%';

IS_ IS_
--- ---
SQL_TEXT
---------------------------------
Y N
select /*placebo */ * from t where i=:n

Y N
select /*ACS_1 */ * from t where i=:n

Y Y
select /*+BIND_AWARE */ * from t where i=:n

sokrates@11.2 > REM stats on t suffice to become bind aware with hint "BIND_AWARE", ACS_1 behaves like placebo

Posted in 11g, fun | Tagged: | 2 Comments »