Sokrates on Oracle

Archive for June, 2011

group by ()

Posted by Matthias Rogel on 21. June 2011

I never saw neither used

group by ()

However, there are cases one may need it:

Support asked me in one of the issues I am working with them

what’s the size of your database ?

They didn’t define “size of database” – I don’t know what is meant by that.
All used space together ?
All segments together ?
All datafiles together ?
All datafiles + online-redo-log-files + controlfiles ?
All datafiles + online-redo-log-files + controlfiles + … (standby log-files, archived log files), rman backup’s ?

So, in order to gave them an answer, my first thought was querying SYS.FILE$, I thought this one should contain all necessary informations and if they aren’t content with me using SYS.FILE$ they can define for me what they want to know. I knew DBA_DATA_FILES is built using SYS.FILE$, and I thought SYS.FILE$ would probably contain also information about other files than datafiles …

SQL> desc sys.file$

 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE#					   NOT NULL NUMBER
 STATUS$				   NOT NULL NUMBER
 BLOCKS 				   NOT NULL NUMBER
 TS#						    NUMBER
....

shows, there are no BYTES in SYS.FILE$, so we have to sum up the Blocks and multiply by

select value from v$parameter where name = 'db_block_size';

, so my thoughts

( In fact, it is a bad idea to query SYS.FILE$ , it is not documented and my thoughts about it were completely wrong )

So I thought, I give them the output of

select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$;

I was very surprised when I saw:

SQL> select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$;

select sum(blocks) * (select value from v$parameter where name = 'db_block_size') from sys.file$                             *
ERROR at line 1:
ORA-00937: not a single-group group function

I didn’t expect that.

(select value from v$parameter where name = 'db_block_size')

is a scalar subquery, hence, for the duration of the SQL, it is a constant and should be treated like a constant.

Let’s go away from SYS-objects and use the following:
take an aggregate function

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) from n;

    MAX(N)
----------
	10

we can multiply that by a constant without trouble

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * 5 from n;

  MAX(N)*5
----------
	50

but we are getting into trouble when using a scalar subquery instead of a constant:

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * (select 5 from dual) from n;

select max(n) * (select 5 from dual) from n                        *

ERROR at line 2:
ORA-00937: not a single-group group function

Don’t know why and if this is expected / intended.
Scalar Subquery Expressions
shows some restrictions of scalar subquery expressions, but it doesn’t seem to cover this issue here ( I don’t use a scalar subquery expression in a group by clause )

Googling around, I found Laurent Schneider blogging on similar issues on
Why cannot I use subquery there?
and an interesting construct on Comment No.1 by Chen Shapira

group by ()

I never saw this construct, but tried it:

SQL> with n as (select level as n from dual connect by level <= 10)
  2  select max(n) * (select 5 from dual) from n
  3  group by ();

MAX(N)*(SELECT5FROMDUAL)
------------------------
		      50

Eh voilà – it works.

group by ()

doesn’t seem to be documented (due to Laurent), so I don’t know if it is a good idea to use it in production, though.

Advertisements

Posted in sql | 1 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 »

“ORA-32035: unreferenced query name defined in WITH” clause no longer existent in 11.2 ?

Posted by Matthias Rogel on 14. June 2011

when I issued a select in 10.2 with a query defined in the WITH-clause but not referenced later on, I received an ORA-32035 exception:


10.2 > with a as (select 1 a from dual)
2 select * from dual;
with a as (select 1 a from dual)
*
ERROR at line 1:
ORA-32035: unreferenced query name defined in WITH clause

I was a bit surprised today, when I saw the following running against 11.2:


11.2 > with a as (select 1 a from dual)
2 select * from dual;
D
-
X

I don’t manage to get a ORA-32035 on 11.2 ….
It’s documented though on
ORA-32035
that I should get one.

So, is it a bug or a feature ?

Posted in 11.2 | 5 Comments »

My Constraints are checked again, now – how to validate them ?

Posted by Matthias Rogel on 12. June 2011

I upgraded to 11.2 in production some days after it was released in September 2010.

Since them I did hit a number of bugs, got some of them fixed with Oracle Support and are still working with them on a number of others.

The most serious for me ( because my data was not validated anymore in a number of SQLs – and I care about my data ) I wrote about on

https://marogel.wordpress.com/2011/03/27/prevent-validation-of-check-constraints-in-11-2-0-2/

Thanks t

Timo Raitalaakso I was informed about an available patch 9285259 for “Bug 9285259: MERGE IGNORES CHECK CONSTRAINT”

It did install without issues on test-db and I verified successfully that the software was really patched, I could not cheat again with merge.

So today (Sunday) I patched production in 5 minutes. Good news: my constraints are checked again !

Bad news: I now have to validate each of them manually – did my data run into inconsistencies ?
How to achive that ? No word on Oracle Support about that. Seems to me, they don’t really care much about the consistency of the data of their clients.

My first idea was something like this (pseudo-code)


for c in (
select owner, table_name, search_condition, constraint_name
from dba_constraints
where constraint_type='C'
) loop
execute immediate
'select count(*) from ' ||
c.owner || '."' || c.table_name ||
'" where not (' || c.search_condition || ') '
into n;
if n > 0 then
dbms_output.put_line(
c.owner || ', ' || c.table_name || ', ' || c.constraint_name || ': ' ||
n || 'exceptionally rows - you may want to have a deeper look onto these');
end if;
end loop;

However, the optimizer of course takes a shortcut – using the constraint:


11.2.0.2 > create table t ( i int check( i != 3 ));
Table created.
11.2.0.2 > insert into t values(2);
1 row created.
11.2.0.2 > merge into t
2 using ( select rowid r from t) s
3 on (t.rowid = s.r)
4 when matched then
5 update set i = 3;
1 row merged.
11.2.0.2 > set autotr on explain
11.2.0.2 > select i from t where not (i != 3);
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1322348184
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 0 (0)| |
|* 1 | FILTER | | | | | |
|* 2 | TABLE ACCESS FULL| T | /1 | 13 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
2 - filter("I"=3)
Note
-----
- dynamic sampling used for this statement (level=2)

See the
”  1 – filter(NULL IS NOT NULL)”
using the check constraint.

So my second idea was to use lnnvl :

11.2.0.2 > select i from t where lnnvl(i != 3);
I
----------
3
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 13 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LNNVL("I"<>3))
Note
-----
- dynamic sampling used for this statement (level=2)

well, this one seems to work – for this case

But it doesn’t work for all cases – see:

11.2.0.2 > create table s( i int check ( i in (3, 4) ));
Table created.
11.2.0.2 > insert into s values(3);
1 row created.
11.2.0.2 > merge into s
2 using (select rowid r from s) p
3 on (p.r = s.rowid)
4 when matched then update
5 set i = 2;
1 row merged.
11.2.0.2 > commit;
Commit complete.
11.2.0.2 > select i from s;
I
----------
2
11.2.0.2 > select search_condition, validated, status from user_constraints where table_name='S';
SEARCH_CONDITION VALIDATED
STATUS
-------------------------------------------------------------------------------- -------------
--------
i in (3, 4) VALIDATED
ENABLED
11.2.0.2 > select * from s where lnnvl(i in (3, 4));
select * from s where lnnvl(i in (3, 4))
*
ERROR at line 1:
ORA-13207: incorrect use of the [LNNVL] operator

so now, my question is still holds:

how can I validate all my check constraints manually without using clever optimizer ?

Seems to me, I have to disable / enable each of them.

Enabling with validate of course.

And not in production of course, but on a clone DB.

Update on Monday:
ok, tests show there is no need to disable them before call enable validate.
So, the following script to validate all in-doubt check-constraints of a list of owners suffices:

create table exceptions(row_id urowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));
declare
check_cons_violated exception;
pragma exception_init(
check_cons_violated,
-2293 -- "cannot validate (%s.%s) - check constraint violated"
);
vers_obj_cant_be_altered exception;
pragma exception_init(
vers_obj_cant_be_altered,
-20171 -- "WM error: Versioned objects cannot be altered."
);
begin
for c in (
select *
from dba_constraints
where constraint_type = 'C'
and owner in (....)
) loop
begin
execute immediate
'alter table "' || c.owner ||
'"."' || c.table_name ||
'" enable validate constraint "' ||
c.constraint_name ||
'" exceptions into exceptions';
exception when check_cons_violated or vers_obj_cant_be_altered then null;
end;
end loop;
end;
/
select * from exceptions;

Posted in 11.2, Allgemein, bugfighting | Leave a Comment »

A Bug which survived from 7.3.3

Posted by Matthias Rogel on 9. June 2011

One of the most annoying bugs I hit almost daily is over 14 years of age and was filed against 7.3.3

It is an sqlplus issue:
serverout on stops working when you receive
ORA-04068: existing state of packages has been discarded

See (using SQL*Plus: Release 11.2.0.1.0 Production ):
Session 1

sokrates1 > set serverout on
sokrates1 > create package abc is x number; end abc;
2 /
Package created.
sokrates1 > exec abc.x := 17 ; dbms_output.put_line(abc.x)
17
PL/SQL procedure successfully completed.

Now, in Session 2 (same db, same user), we do

sokrates2 > create or replace package abc is x number; y number; end abc;
2 /
Package created.

and then, back in Session 1:

sokrates1 > exec abc.x := 16; dbms_output.put_line(abc.x)
BEGIN abc.x := 16; dbms_output.put_line(abc.x); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "SOKRATES.ABC" has been invalidated
ORA-04065: not executed, altered or dropped package "SOKRATES.ABC"
ORA-06508: PL/SQL: could not find program unit being called: "SOKRATES.ABC"
ORA-06512: at line 1

we receive ORA-04068, that’s ok and expected.

But now:

sokrates1 > show serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
sokrates1 > exec abc.x := 16; dbms_output.put_line(abc.x)
PL/SQL procedure successfully completed.
sokrates1 > REM oops - serverout is ON, but dbms_output.put_line does not display something on my screen
sokrates1 > exec dbms_output.put_line('serverout is on, why do you don''t want to talk to me ?')
PL/SQL procedure successfully completed.
sokrates1 > show serverout
serveroutput ON SIZE UNLIMITED FORMAT WORD_WRAPPED
sokrates1 > exec dbms_output.put_line('are we friends again ?')
PL/SQL procedure successfully completed.
sokrates1 > set serverout on
sokrates1 > exec dbms_output.put_line('now we are friends again')
now we are friends again
PL/SQL procedure successfully completed.

After receiving ORA-04068, serverout ON is ignored. You have to manually set it again in order to work.

This bug was filed more than 14 years ago ( ID 560143 ) against sqlplus 7.3.3 ( and still reproduces under 11.2 )

Bug 11720497 was filed by me.

Posted in 11.2, 7.3.3, sqlplus | 1 Comment »

Average of DATEs

Posted by Matthias Rogel on 7. June 2011

avg does not support DATEs as arguments:

sokrates@11.2 > select avg(created) from dba_users;
select avg(created) from dba_users
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got DATE

Why ? I don’t know

sokrates@11.2 > select
2 min( created ),
3 max( created ),
4 median( created )
5 from dba_users;
MIN(CREATED) MAX(CREATED) MEDIAN(CREATED)
-------------------- -------------------- --------------------
17-sep-2010 16:08:04 04-apr-2011 11:13:54 09-oct-2010 15:57:09

work without problems.

So, what’s the easiest workaround when you need avg of DATEs ?

sokrates@11.2 > select
2 sysdate - avg ( sysdate - created ) "avg(created)"
3 from dba_users;
avg(created)
--------------------
15-oct-2010 21:19:00

is neat, isn’t it ?

And: it’s deterministic (doesn’t depend on SYSDATE) !
(proof of that is left to the reader)

Posted in 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 »