XTended Oracle SQL
Easy(lazy) way to check which programs have properly configured FetchSize
select
s.module
,ceil(max(s.rows_processed/s.fetches)) rows_per_fetch
from v$sql s
where
s.rows_processed>100
and s.executions >1
and s.fetches >1
and s.module is not null
and s.command_type = 3 -- SELECTs only
and s.program_id = 0 -- do not account recursive queries from stored procs
and s.parsing_schema_id!=0 -- <> SYS
group by s.module
order by rows_per_fetch desc nulls last
/
Categories: Development
PL/SQL functions: Iterate and keys for associative arrays
Unfortunately associative arrays still require more “coding”:
we still can’t use “indices of” or “values of” in simple FOR(though they are available for FORALL for a long time), don’t have convinient iterators and even function to get all keys…
That’s why I want to show my templates for such things like iterator and keys function. You can adopt these functions and create them on schema level.
declare
type numbers is table of number;
type anumbers is table of number index by pls_integer;
a anumbers;
i pls_integer;
function iterate( idx in out nocopy pls_integer, arr in out nocopy anumbers)
return boolean
as pragma inline;
begin
if idx is null
then idx:=arr.first;
else idx:=arr.next(idx);
end if;
return idx is not null;
end;
function keys(a in out nocopy anumbers) return numbers as
res numbers:=numbers();
idx number;
pragma inline;
begin
while iterate(idx,a) loop
res.extend;
res(res.count):=idx;
end loop;
return res;
end;
begin
a(1):=10;
a(3):=30;
a(5):=50;
a(8):=80;
-- iterate:
while iterate(i,a) loop
dbms_output.put_line(a(i));
end loop;
-- keys:
for i in 1..keys(a).count loop
dbms_output.put_line(a(keys(a)(i)));
end loop;
end;
Categories: Development
How to group connected elements (or pairs)
I see quite often when developers ask questions about connected components:
Table “MESSAGES” contains fields “SENDER” and “RECIPIENT”, which store clients id.How to quickly get all groups of clients who are connected even through other clients if the table has X million rows?
So for this table, there should be 4 groups:
- (1, 2, 4, 8, 16)
- (3, 6, 12)
- (5, 10, 20)
- (7, 14)
- (9, 18)
Of course, we can solve this problem using SQL only (model, recursive subquery factoring or connect by with nocycle), but such solutions will be too slow for huge tables.
Example of SQL solution
with
t(sender,recipient) as (select level,level*2 from dual connect by level<=10)
, v1 as (select rownum id,t.* from t)
, v2 as (select id, account
from v1
unpivot (
account for x in (sender,recipient)
))
, v3 as (
select
id
,account
,dense_rank()over(order by account) account_n
,count(*)over() cnt
from v2)
, v4 as (
select distinct grp,account
from v3
model
dimension by (id,account_n)
measures(id grp,account,cnt)
rules
iterate(1e6)until(iteration_number>cnt[1,1])(
grp[any,any] = min(grp)[any,cv()]
,grp[any,any] = min(grp)[cv(),any]
)
)
select
listagg(account,',')within group(order by account) s
from v4
group by grp
[collapse]
In such situations it’s much better to adopt standard algorithms like Quick-find or Weighted quick-union for PL/SQL.
The first time I wrote such solution about 5 years ago and I even posted here one of the latest solutions, but all of them were not universal, so I’ve created the package today with a couple of functions for most common problems: XT_CONNECTED_COMPONENTS
It contains 2 functions based on Weighted quick-find quick-union algorithm:
- function get_strings(cur in sys_refcursor, delim varchar2:=’,’) return strings_array pipelined;
It accepts a cursor and returns found connected components as table of varchar2(v_size). You can change v_size in the package definition.
Input cursor should contain one Varchar2 column with linked strings, for example: ‘a,b,c’.
You can also specify list delimiter, by default it is comma.
Examples:select * from table(xt_connected_components.get_strings( cursor(select ELEM1||','||ELEM2 from TEST)); select * from table( xt_connected_components.get_strings( cursor(select 'a,b,c' from dual union all select 'd,e,f' from dual union all select 'e,c' from dual union all select 'z' from dual union all select 'X,Y' from dual union all select 'Y,Z' from dual))); COLUMN_VALUE ----------------------------------------- STRINGS('X', 'Y', 'Z') STRINGS('a', 'b', 'c', 'd', 'e', 'f') STRINGS('z') - function get_numbers(cur in sys_refcursor) return numbers_array pipelined;
This function also returns connected components, but for numbers.
Input cursor should contain two columns with linked numbers.
Examples:select * from table( xt_connected_components.get_numbers( cursor( select sender_id, recipient_id from messages ))); select * from table( xt_connected_components.get_numbers( cursor( select level account1 , level*2 account2 from dual connect by level<=10 ))); SQL> select * 2 from 3 table( 4 xt_connected_components.get_numbers( 5 cursor( 6 select level account1 7 , level*2 account2 8 from dual 9 connect by level<=10 10* ))) SQL> / COLUMN_VALUE ------------------------ NUMBERS(1, 2, 4, 8, 16) NUMBERS(3, 6, 12) NUMBERS(5, 10, 20) NUMBERS(7, 14) NUMBERS(9, 18)
How to install:
Download all files from Github and execute “@install” in SQL*Plus or execute them in another tool in the following order:
xt_connected_components_types.sql
xt_connected_components_pkg.sql
xt_connected_components_bdy.sql
Download URL: https://github.com/xtender/xt_scripts/tree/master/extra/xt_connected_components
Categories: Development
Ampersand instead of colon for bind variables
I’ve troubleshooted one query today and I was very surprised that bind variables in this query were specified with &ersand instead of :colon! I have never seen this before and I couldn’t find anything about this in documentation…
Unfortunately SQL*Plus doesn’t support ampersand yet, even if you disable define (“set define off”),
so I’ve tested such behaviour with this code:
set def off serverout on exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
And it really works! //at least on 11.2.0.2 and 12.2.0.1
SQL> set def off serverout on
SQL> exec declare s varchar2(1); begin execute immediate 'select 1 from dual where dummy=&var' into s using 'X'; dbms_output.put_line(s); end;
1
PL/SQL procedure successfully completed.
SQL> select substr(sql_text,1,40) stext,sql_id,executions,rows_processed from v$sqlarea a where sql_text like '%dual%&var';
STEXT SQL_ID EXECUTIONS ROWS_PROCESSED
------------------------------------- ------------- ---------- --------------
select 1 from dual where dummy=&var ckkw4u3atxz02 3 3
SQL> select * from table(dbms_xplan.display_cursor('ckkw4u3atxz02'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID ckkw4u3atxz02, child number 0
-------------------------------------
select 1 from dual where dummy=&var
Plan hash value: 272002086
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("DUMMY"=:VAR)
18 rows selected.
Update: Btw, it works for SQL only, not for PL/SQL:
SQL> var v varchar2(1);
SQL> begin &v = 'Z'; end;
2 /
begin &v = 'Z'; end;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:
SQL> exec &v := 'X';
BEGIN &v := 'X'; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00103: Encountered the symbol "&" when expecting one of the following:
The symbol "&" was ignored.
SQL> exec :v := 'X'; PL/SQL procedure successfully completed. SQL> select * from dual where dummy=&v 2 ; D - X
And we can can use mixed placeholders:
SQL> select * from dual where dummy=&v and &v=:v; D - X
Categories: Development
Simple regexp to check that string contains word1 and word2 and doesn’t contain word3
with tst as ( select 'qwe word1 asd ...............' s from dual union all select 'qwe word1 asd word2 .........' s from dual union all select 'qwe word1 asd word2 zxc word3' s from dual union all select 'qwe word2 asd word1 zxc word4' s from dual ) select s ,regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') subst ,case when regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$') then 'matched' end tst2 from tst where 1=1 --and regexp_like(regexp_replace(s, '(word1)|(word2)|(word3)|(.)','`\3') , '^`+$')
Categories: Development
Book advice: ORACLE SQL & PL/SQL Golden Diary by Asim Chowdhury
I’ve reviewed this book recently, and I highly recommend it as it has almost all that needed to become strong Oracle developer. You can check at least the table of contents:
ORACLE SQL & PL/SQL Golden Diary: by Asim Chowdhury
New Book Demystifies Complex Cross-Version Oracle Problem Solving
Compiled by veteran computer scientist and data modeler, Asim Chowdhury, ‘ORACLE SQL & PL/SQL Golden Diary: Refactoring, Interoperability of Versions & Integration of related concepts for High Performance’ is the first book on the market that comprehensively allows data architects to unravel any concepts in SQL and PL/sql till oracle 12c. It’s poised to remove much confusion from the many versions of Oracle SQL now on the market; a Godsend for the computer science industry.
Categories: Development
SQL validation during PL/SQL compilation
A recent posting on SQL.RU asked why Oracle doesn’t raise such errors like “ORA-00979 not a group by expression” during PL/SQL compilation. Since I couldn’t find a link to the answer (though I read about it many years ago, but I don’t remember where…), I’ve decided to post short answer:
During PL/SQL compilation Oracle checks static SQL using only:
- Syntactic analysis – Oracle verifies that keywords, object names, operators, delimiters, and so on are placed correctly in your SQL statement. So such queries like “select * foRm dual” will fail during this validation. For example, we can get here such errors like:
ORA-00900: invalid SQL statement
ORA-00923: FROM keyword not found where expected
ORA-00924: missing BY keyword
ORA-00933: SQL command not properly ended
… - Semantic analysis – it verifies that references to host variables and database objects are valid(including their grants) and that host-variable datatypes are correct. For example, “select * from nonexisting_table” will fail this validation.
And since Oracle doesn’t validate all other types of errors during Syntactic and Semantic analysis, we can detect them only during optimization* or execution*.
For example, Oracle detects “ORA-00979 not a group by expression” during optimization phase.
* Read “Chapter Chapter 7: Parsing and Optimizing” in Oracle Core by Jonathan Lewis
NB. It doesn’t not apply to CREATE or ALTER VIEW, because Oracle executes optimization step for them. You can check it using trace 10053.
Simple example:
-- fORm instead of "from" - syntactic validation fails:
SQL> create table t(a int, b int, c int);
SQL> create or replace procedure p_syntactic is
2 cursor c is select a,b,sum(c) sum_c fORm t group by a;
3 begin
4 null;
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> sho error;
Errors for PROCEDURE P_SYNTACTIC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16 PL/SQL: SQL Statement ignored
2/40 PL/SQL: ORA-00923: FROM keyword not found where expected
-- semantic validation fails:
SQL> create or replace procedure p_semantic is
2 cursor c is select a,b,sum(blabla) sum_c from t group by a;
3 begin
4 null;
5 end;
6 /
Warning: Procedure created with compilation errors.
SQL> sho error;
Errors for PROCEDURE P_SEMANTIC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
2/16 PL/SQL: SQL Statement ignored
2/31 PL/SQL: ORA-00904: "BLABLA": invalid identifier
-- As you can see this procedure passes successfully both syntactic and semantic analysis,
-- though query is not valid: it should raise "ORA-00979: not a GROUP BY expression"
SQL> create or replace procedure p_valid is
2 cursor c is select a,b,sum(c) sum_c from t group by a;
3 begin
4 null;
5 end;
6 /
SQL> sho error;
No errors.
-- Oracle checks such errors for "CREATE VIEW", because it runs optimization for the query text:
SQL> create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a;
create view v_cursor as select a,b,sum(c) sum_c from t group by a order by a
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
Categories: Development
How to speed up slow unicode migration of a table with xmltype columns
Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:
update /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')
“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:
Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB
Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.
Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.
For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.
Btw, Oracle didn’t support parallel dml for xmltype on previous releases:
No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)
So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:
create table tmp_rids as select rowid rid, ntile(16)over(order by rowid) grp from t_xmldata;
3. Execute
ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER';
to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:
update t_xmldata A set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') where rowid in (select rid from tmp_rids where grp=&grp); commit;
5. Disable event 22838:
ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF';
6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.
This simple method allowed to make unicode migration about 16 times faster.
Categories: Development
row pieces, 255 columns, intra-block row chaining in details
You may know about Intra-block row chaining which may occur when the number of columns in a table are more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!
Documentation says:
When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.
A bit more details:
1. One row piece can store up to 255 columns
2. Oracle splits fields by row pieces in reverse order
3. Oracle doesn’t store trailing null fields in a row (not in row piece)
4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.
I’ll show in examples with dumps:
Example 1:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_300) values(2)
3. dump data blocks
test code
drop table test purge;
set serverout on;
alter session set tracefile_identifier='test1';
declare
c varchar2(32000);
v varchar2(32000);
cols varchar2(32000):='c_1 number(1,0)';
vals varchar2(32000):='null';
ndf int;
nbl int;
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' number(1,0)';
vals:=vals||','||case
when i = 300 then '2'
else 'null'
end;
end loop;
c:='create table test('||cols||')';
v:='insert into test values('||vals||')';
dbms_output.put_line(c);
dbms_output.put_line(v);
execute immediate (c);
execute immediate (v);
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
bdba: 0x018019f3
block_row_dump:
bdba: 0x018019f4
block_row_dump:
bdba: 0x018019f5
block_row_dump:
bdba: 0x018019f6
block_row_dump:
bdba: 0x018019f7
block_row_dump:
tl: 260 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
...
col 252: *NULL*
col 253: *NULL*
col 254: [ 2] c1 03
tl: 54 fb: --H-F--- lb: 0x1 cc: 45
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
...
col 42: *NULL*
col 43: *NULL*
col 44: *NULL*
The resulted dump file shows us:
1. Both row pieces are in the same block 0x018019f4
2. They contain only first 300 columns, (trailing 55 columns are NULLs)
3. First row piece contains columns c_46 – c_300,
4. Second row piece contains columns c_1 – c_45 (they all are NULLs)
Example 2.
But let’s test an update with the same table:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(null)
3. update test set c_300=2
4. dump data blocks
Test code 2
drop table test purge;
set serverout on;
alter session set tracefile_identifier='test2';
declare
c varchar2(32000);
cols varchar2(32000):='c_1 number(1,0)';
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' number(1,0)';
end loop;
c:='create table test('||cols||')';
execute immediate (c);
execute immediate ('insert into test(c_1) values(null)');
execute immediate 'update test set c_300=3';
commit;
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
bdba: 0x018019f3
block_row_dump:
tl: 260 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
...
col 251: *NULL*
col 252: *NULL*
col 253: *NULL*
col 254: [ 2] c1 04
bdba: 0x018019f4
block_row_dump:
bdba: 0x018019f5
block_row_dump:
bdba: 0x018019f6
block_row_dump:
bdba: 0x018019f7
block_row_dump:
tl: 54 fb: --H-F--- lb: 0x1 cc: 45
col 0: *NULL*
col 1: *NULL*
col 2: *NULL*
...
col 42: *NULL*
col 43: *NULL*
col 44: *NULL*
As you can see, there is no intra-block chaining – second row piece was created in another block.
Example 3.
Now I want to show how oracle splits already chained rows:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(1)
3. update test set c_300=2
4. update test set c_301=3
5. update test set c_302=4
6. dump data blocks
Test code 3
drop table test purge;
set serverout on
alter session set tracefile_identifier='test3';
declare
cols varchar2(32000):='c_1 number(1,0)';
procedure print_and_exec(c varchar2) as
begin
dbms_output.put_line(c);
execute immediate c;
end;
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' number(1,0)';
end loop;
print_and_exec ('create table test('||cols||')');
print_and_exec ('insert into test(c_1) values(1)');
print_and_exec ('update test set c_300=2');
print_and_exec ('update test set c_301=3');
print_and_exec ('update test set c_302=4');
commit;
execute immediate 'alter system flush buffer_cache';
execute immediate 'select count(*) from test';
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
bdba: 0x018019f3
block_row_dump:
tl: 10 fb: -------- lb: 0x1 cc: 1
col 0: *NULL*
bdba: 0x018019f4
block_row_dump:
tl: 264 fb: -----L-- lb: 0x1 cc: 255
col 0: *NULL*
col 1: *NULL*
...
col 249: *NULL*
col 250: *NULL*
col 251: *NULL*
col 252: [ 2] c1 03
col 253: [ 2] c1 04
col 254: [ 2] c1 05
bdba: 0x018019f5
block_row_dump:
bdba: 0x018019f6
block_row_dump:
tl: 10 fb: -------- lb: 0x1 cc: 1
col 0: *NULL*
bdba: 0x018019f7
block_row_dump:
tl: 56 fb: --H-F--- lb: 0x1 cc: 45
col 0: [ 2] c1 02
col 1: *NULL*
col 2: *NULL*
col 3: *NULL*
...
col 42: *NULL*
col 43: *NULL*
col 44: *NULL*
This dump shows us 4 row pieces: First row piece contains 255 columns, second – 45, and 2 row pieces – just by one row.
So we can analyze it step-by-step:
2. insert into test(c_1) values(1)
After insert we have just one row piece with 1 field.
3. update test set c_300=2
After this update, we have 2 row pieces:
1) c_1-c_45
2) c_46-c_300
4. update test set c_301=3
This update split row piece c_46-c_300 into 2 row pieces:
1) c_46
2) c_47-c_301
So we have 3 row pieces now: c_1-c_45, c_46, c_47-c_301
5. update test set c_302=4
This update split row piece c_47-c_301 into 2 row pieces:
1) c_47
2) c_48-c_302
And we’ve got 4 row pieces: c_1-c_45, c_46, c_47, c_48-c_302
You can try Example 4 and see how many blocks you can get, and all of them (except last one) will have only 1 column each:
Test code 4
drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';
declare
cols varchar2(32000):='c_1 char(3)';
procedure print_and_exec(c varchar2) as
begin
dbms_output.put_line(c);
execute immediate c;
end;
begin
for i in 2..355 loop
cols:=cols||',c_'||i||' char(3)';
end loop;
print_and_exec ('create table test('||cols||')');
print_and_exec ('insert into test(c_1) values(null)');
commit;
for i in 256..355 loop
execute immediate 'update test set c_'||i||'='||i;
end loop;
commit;
execute immediate 'alter system flush buffer_cache';
for r in (select 'alter system dump datafile '||file#||' block '||block# cmd
from (
select distinct file#,block#
from v$bh
where class#=1
and objd in (select o.data_object_id from user_objects o where object_name='TEST')
order by 1,2
)
)
loop
execute immediate r.cmd;
end loop;
end;
/
disc;
[collapse]
grep
bdba: 0x01801281 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801282 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801283 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801284 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801285 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801286 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801287 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801288 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801289 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128a block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128b block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128c block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128d block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128e block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180128f block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801291 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801292 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801293 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801294 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801295 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801296 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801297 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801298 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x01801299 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129a block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129b block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129c block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129d block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129e block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x0180129f block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012a9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012aa block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ab block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ac block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ad block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ae block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012af block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012b9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ba block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bb block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012be block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012bf block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012c9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ca block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cb block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012ce block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012cf block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d1 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d2 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d7 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012d9 block_row_dump: bdba: 0x018012da block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012db block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012dc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012dd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018012de block_row_dump: tl: 558 fb: -----L-- lb: 0x1 cc: 255 col 0: *NULL* col 1: *NULL* col 2: *NULL* ... col 152: *NULL* col 153: *NULL* col 154: *NULL* col 155: [ 3] 32 35 36 col 156: [ 3] 32 35 37 col 157: [ 3] 32 35 38 col 158: [ 3] 32 35 39 ... col 251: [ 3] 33 35 32 col 252: [ 3] 33 35 33 col 253: [ 3] 33 35 34 col 254: [ 3] 33 35 35 bdba: 0x018012df block_row_dump: bdba: 0x018019f3 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f4 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f5 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f6 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f7 block_row_dump: tl: 10 fb: --H-F--- lb: 0x2 cc: 1 col 0: *NULL* bdba: 0x018019f8 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019f9 block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fa block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fb block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fc block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fd block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019fe block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL* bdba: 0x018019ff block_row_dump: tl: 10 fb: -------- lb: 0x1 cc: 1 col 0: *NULL*
[collapse]
Categories: Development
How even empty trigger increases redo generation
Very simple example:
Test case
set feed on; -- simple table: create table xt_curr1 as select level a,level b from dual connect by level<=1e4; -- same table but with empty trigger: create table xt_curr2 as select level a,level b from dual connect by level<=1e4; create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row begin null; end; / set autot trace stat; update xt_curr1 set b=a; set autot off; set autot trace stat; update xt_curr2 set b=a; set autot off; set feed off drop table xt_curr1 purge; drop table xt_curr2 purge;
[collapse]
SQL> -- simple table:
SQL> create table xt_curr1 as select level a,level b from dual connect by level<=1e4;
Table created.
SQL> -- same table but with empty trigger:
SQL> create table xt_curr2 as select level a,level b from dual connect by level<=1e4;
Table created.
SQL> create or replace trigger tr_xt_curr2 before update on xt_curr2 for each row
2 begin
3 null;
4 end;
5 /
Trigger created.
SQL> update xt_curr1 set b=a;
10000 rows updated.
Statistics
----------------------------------------------------------
25 recursive calls
10553 db block gets
91 consistent gets
18 physical reads
3101992 redo size
560 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
SQL> update xt_curr2 set b=a;
10000 rows updated.
Statistics
----------------------------------------------------------
11 recursive calls
20384 db block gets
59 consistent gets
18 physical reads
4411724 redo size
560 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10000 rows processed
Categories: Development
8 queens chess problem: solution in Oracle SQL
This is just another solution of this problem for a chessboard, but you can choose any size of the checkerboard:
with
t as (select level i, cast(level as varchar2(1)) c from dual connect by level<=&d)
,x(l,s,n) as (
select 1 l, c s, chr(97)||c||' ' from t
union all
select l+1, x.s||t.c, n||chr(98+l)||i||' '
from x
join t
on instr(s,c)=0
and not exists(select 0 from dual
where L+1 - t.i = level - substr(s,level,1)
or L+1 + t.i = level + substr(s,level,1)
connect by level<=length(s))
where L<&d
)
select n
from x
where l=&d
8x8:
SQL> @tests/f Size[8]: 8 N -------------------------------------------------------------------------------- a1 c5 d8 e6 f3 g7 h2 i4 a1 c6 d8 e3 f7 g4 h2 i5 a1 c7 d4 e6 f8 g2 h5 i3 a1 c7 d5 e8 f2 g4 h6 i3 a2 c4 d6 e8 f3 g1 h7 i5 a2 c5 d7 e1 f3 g8 h6 i4 a2 c5 d7 e4 f1 g8 h6 i3 a2 c6 d1 e7 f4 g8 h3 i5 a2 c6 d8 e3 f1 g4 h7 i5 a2 c7 d3 e6 f8 g5 h1 i4 a2 c7 d5 e8 f1 g4 h6 i3 a2 c8 d6 e1 f3 g5 h7 i4 a3 c1 d7 e5 f8 g2 h4 i6 a3 c5 d2 e8 f1 g7 h4 i6 a3 c5 d2 e8 f6 g4 h7 i1 a3 c5 d7 e1 f4 g2 h8 i6 a3 c5 d8 e4 f1 g7 h2 i6 a3 c6 d2 e5 f8 g1 h7 i4 a3 c6 d2 e7 f1 g4 h8 i5 a3 c6 d2 e7 f5 g1 h8 i4 a3 c6 d4 e1 f8 g5 h7 i2 a3 c6 d4 e2 f8 g5 h7 i1 a3 c6 d8 e1 f4 g7 h5 i2 a3 c6 d8 e1 f5 g7 h2 i4 a3 c6 d8 e2 f4 g1 h7 i5 a3 c7 d2 e8 f5 g1 h4 i6 a3 c7 d2 e8 f6 g4 h1 i5 a3 c8 d4 e7 f1 g6 h2 i5 a4 c1 d5 e8 f2 g7 h3 i6 a4 c1 d5 e8 f6 g3 h7 i2 a4 c2 d5 e8 f6 g1 h3 i7 a4 c2 d7 e3 f6 g8 h1 i5 a4 c2 d7 e3 f6 g8 h5 i1 a4 c2 d7 e5 f1 g8 h6 i3 a4 c2 d8 e5 f7 g1 h3 i6 a4 c2 d8 e6 f1 g3 h5 i7 a4 c6 d1 e5 f2 g8 h3 i7 a4 c6 d8 e2 f7 g1 h3 i5 a4 c6 d8 e3 f1 g7 h5 i2 a4 c7 d1 e8 f5 g2 h6 i3 a4 c7 d3 e8 f2 g5 h1 i6 a4 c7 d5 e2 f6 g1 h3 i8 a4 c7 d5 e3 f1 g6 h8 i2 a4 c8 d1 e3 f6 g2 h7 i5 a4 c8 d1 e5 f7 g2 h6 i3 a4 c8 d5 e3 f1 g7 h2 i6 a5 c1 d4 e6 f8 g2 h7 i3 a5 c1 d8 e4 f2 g7 h3 i6 a5 c1 d8 e6 f3 g7 h2 i4 a5 c2 d4 e6 f8 g3 h1 i7 a5 c2 d4 e7 f3 g8 h6 i1 a5 c2 d6 e1 f7 g4 h8 i3 a5 c2 d8 e1 f4 g7 h3 i6 a5 c3 d1 e6 f8 g2 h4 i7 a5 c3 d1 e7 f2 g8 h6 i4 a5 c3 d8 e4 f7 g1 h6 i2 a5 c7 d1 e3 f8 g6 h4 i2 a5 c7 d1 e4 f2 g8 h6 i3 a5 c7 d2 e4 f8 g1 h3 i6 a5 c7 d2 e6 f3 g1 h4 i8 a5 c7 d2 e6 f3 g1 h8 i4 a5 c7 d4 e1 f3 g8 h6 i2 a5 c8 d4 e1 f3 g6 h2 i7 a5 c8 d4 e1 f7 g2 h6 i3 a6 c1 d5 e2 f8 g3 h7 i4 a6 c2 d7 e1 f3 g5 h8 i4 a6 c2 d7 e1 f4 g8 h5 i3 a6 c3 d1 e7 f5 g8 h2 i4 a6 c3 d1 e8 f4 g2 h7 i5 a6 c3 d1 e8 f5 g2 h4 i7 a6 c3 d5 e7 f1 g4 h2 i8 a6 c3 d5 e8 f1 g4 h2 i7 a6 c3 d7 e2 f4 g8 h1 i5 a6 c3 d7 e2 f8 g5 h1 i4 a6 c3 d7 e4 f1 g8 h2 i5 a6 c4 d1 e5 f8 g2 h7 i3 a6 c4 d2 e8 f5 g7 h1 i3 a6 c4 d7 e1 f3 g5 h2 i8 a6 c4 d7 e1 f8 g2 h5 i3 a6 c8 d2 e4 f1 g7 h5 i3 a7 c1 d3 e8 f6 g4 h2 i5 a7 c2 d4 e1 f8 g5 h3 i6 a7 c2 d6 e3 f1 g4 h8 i5 a7 c3 d1 e6 f8 g5 h2 i4 a7 c3 d8 e2 f5 g1 h6 i4 a7 c4 d2 e5 f8 g1 h3 i6 a7 c4 d2 e8 f6 g1 h3 i5 a7 c5 d3 e1 f6 g8 h2 i4 a8 c2 d4 e1 f7 g5 h3 i6 a8 c2 d5 e3 f1 g7 h4 i6 a8 c3 d1 e6 f2 g5 h7 i4 a8 c4 d1 e3 f6 g2 h7 i5 92 rows selected.
[collapse]
It works quite fast:
8*8 ~ 0.1s
9*9 ~ 0.6s
10*10 ~4s
script for sqlplus
set arrays 1000;
col n for a80;
accept d prompt "Size[8]: " default 8;
with
t as (select/*+inline*/ level i, cast(level as varchar2(2)) c from dual connect by level<=&d)
,x(l,s,n) as (
select 1 l, c s, chr(97)||c||' ' from t
union all
select l+1, x.s||t.c, n||chr(98+l)||i||' '
from x
join t
on instr(s,c)=0
and not exists(select 0 from dual
where L+1 - t.i = level - substr(s,level,1)
or L+1 + t.i = level + substr(s,level,1)
connect by level<=length(s))
where L<&d
)
select n
from x
where l=&d
/
col n clear;
[collapse]
Categories: Development
Maven: how to copy files after a build into several distribution directories
Sometimes it is convenient to copy jar-files automatically after a build into several different directories, for example if you have different config files for local tests and for office test server, then you may want to copy these files into local test directory, internal office test server and public distribution directory.
This short part of pom.xml contains 2 different methods to build UBER-JAR and copying them into 3 different directories: localtest, officetest and public
<build>
<plugins>
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<version>2.6</version>
<configuration>
<archive>
<manifest>
<mainClass>tv.tmd.YourMainClass</mainClass>
</manifest>
<manifestEntries>
<Class-Path>.</Class-Path>
</manifestEntries>
</archive>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<phase>package</phase>
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-shade-plugin</artifactId>
<version>2.4.3</version>
<executions>
<execution>
<phase>package</phase>
<goals>
<goal>shade</goal>
</goals>
<configuration>
<transformers>
<transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
<manifestEntries>
<Main-Class>tv.tmd.YourMainClass</Main-Class>
<Build-Number>2</Build-Number>
</manifestEntries>
</transformer>
</transformers>
</configuration>
</execution>
</executions>
</plugin>
<plugin>
<artifactId>maven-antrun-plugin</artifactId>
<version>1.8</version>
<executions>
<execution>
<id>copy</id>
<phase>package</phase>
<configuration>
<target>
<echo>ANT TASK - copying files....</echo>
<copy todir="${basedir}/distribution/localtest" overwrite="true" flatten="true">
<fileset dir="${basedir}" includes="*.bat" >
<include name="*.bat" />
<include name="ReadME.*" />
<include name="target/*.jar" />
</fileset>
</copy>
<copy todir="${basedir}/distribution/officetest" overwrite="true" flatten="true">
<fileset dir="${basedir}" includes="*.bat" >
<include name="*.bat" />
<include name="ReadME.*" />
<include name="target/*.jar" />
</fileset>
</copy>
<copy todir="${basedir}/distribution/public" overwrite="true" flatten="true">
<fileset dir="${basedir}" includes="*.bat" >
<include name="*.bat" />
<include name="ReadME.*" />
<include name="target/*.jar" />
</fileset>
</copy>
</target>
</configuration>
<goals>
<goal>run</goal>
</goals>
</execution>
</executions>
</plugin>
</plugins>
</build>
Categories: Development
WINDOW NOSORT STOPKEY + RANK()
Recently I found that WINDOW NOSORT STOPKEY with RANK()OVER() works very inefficiently: http://www.freelists.org/post/oracle-l/RANKWINDOW-NOSORT-STOPKEY-stopkey-doesnt-work
The root cause of this behaviour is that Oracle optimizes WINDOW NOSORT STOPKEY with RANK the same way as with DENSE_RANK:
rnk1
create table test(n not null) as
with gen as (select level n from dual connect by level<=100)
select g2.n as n
from gen g1, gen g2
where g1.n<=10
/
create index ix_test on test(n)
/
exec dbms_stats.gather_table_stats('','TEST');
select/*+ gather_plan_statistics */ n
from (select rank()over(order by n) rnk
,n
from test)
where rnk<=3
/
select * from table(dbms_xplan.display_cursor('','','allstats last'));
drop table test purge;
[collapse]
Output
N
----------
1
1
1
1
1
1
1
1
1
1
10 rows selected.
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------
SQL_ID 8tbq95dpw0gw7, child number 0
-------------------------------------
select/*+ gather_plan_statistics */ n from (select rank()over(order by
n) rnk ,n from test) where rnk<=3
Plan hash value: 1892911073
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.01 | 3 | | | |
|* 1 | VIEW | | 1 | 1000 | 10 |00:00:00.01 | 3 | | | |
|* 2 | WINDOW NOSORT STOPKEY| | 1 | 1000 | 30 |00:00:00.01 | 3 | 73728 | 73728 | |
| 3 | INDEX FULL SCAN | IX_TEST | 1 | 1000 | 31 |00:00:00.01 | 3 | | | |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNK"<=3)
2 - filter(RANK() OVER ( ORDER BY "N")<=3)
[collapse]
As you can see, A-Rows in plan step 2 = 30 – ie, that is the number of rows where
DENSE_RANK<=3
but not
RANK<=3
The more effective way will be to stop after first 10 rows, because 11th row already has RANK more than 3!
But we can create own STOPKEY version with PL/SQL:
PLSQL STOPKEY version
create or replace type rowids_table is table of varchar2(18);
/
create or replace function get_rowids_by_rank(
n int
,max_rank int
)
return rowids_table pipelined
as
begin
for r in (
select/*+ index_rs_asc(t (n)) */ rowidtochar(rowid) chr_rowid, rank()over(order by n) rnk
from test t
where t.n > get_rowids_by_rank.n
order by n
)
loop
if r.rnk <= max_rank then
pipe row (r.chr_rowid);
else
exit;
end if;
end loop;
return;
end;
/
select/*+ leading(r t) use_nl(t) */
t.*
from table(get_rowids_by_rank(1, 3)) r
,test t
where t.rowid = chartorowid(r.column_value)
/
[collapse]
Categories: Development
Thanks to all #odevchoice voters!
And special thanks to all the great people who voted for me! 
I want to list all voters for all nomenees in one page: http://orasql.org/odevchoice/all-the-voters.html
The query (using xt_http of course :):
Spoiler
with
finalists(category, userid, name) as (
-- SQL~ Voting:
------ ------------------ -------------------------------------------------------------------------------------
select 'SQL' , 6899, 'Stew Ashton ' from dual union all
select 'SQL' , 6900, 'Sean Stuber ' from dual union all
select 'SQL' , 6901, 'Sayan Malakshinov' from dual union all
select 'SQL' , 6902, 'Matthias Rogel ' from dual union all
select 'SQL' , 6903, 'Kim Berg Hansen ' from dual union all
select 'SQL' , 6904, 'Justin Cave ' from dual union all
select 'SQL' , 6905, 'Erik Van Roon ' from dual union all
select 'SQL' , 6906, 'Emrah Mete ' from dual union all
-- PL/SQL~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'PL/SQL' , 6907, 'Sean Stuber ' from dual union all
select 'PL/SQL' , 6908, 'Roger Troller ' from dual union all
select 'PL/SQL' , 6909, 'Patrick Barel ' from dual union all
select 'PL/SQL' , 6910, 'Morten Braten ' from dual union all
select 'PL/SQL' , 6911, 'Kim Berg Hansen ' from dual union all
select 'PL/SQL' , 6912, 'Bill Coulam ' from dual union all
select 'PL/SQL' , 6913, 'Adrian Billington' from dual union all
-- ORDS ~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'ORDS' , 6881, 'Tim St. Hilaire ' from dual union all
select 'ORDS' , 6882, 'Morten Braten ' from dual union all
select 'ORDS' , 6883, 'Kiran Pawar ' from dual union all
select 'ORDS' , 6884, 'Dimitri Gielis ' from dual union all
select 'ORDS' , 6885, 'Dietmar Aust ' from dual union all
select 'ORDS' , 6886, 'Anton Nielsen ' from dual union all
-- APEX ~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'ORDS' , 6887, 'Trent Schafer ' from dual union all
select 'ORDS' , 6888, 'Paul MacMillan ' from dual union all
select 'ORDS' , 6889, 'Morten Braten ' from dual union all
select 'ORDS' , 6890, 'Kiran Pawar ' from dual union all
select 'ORDS' , 6891, 'Karen Cannell ' from dual union all
select 'ORDS' , 6893, 'Juergen Schuster ' from dual union all
select 'ORDS' , 6894, 'Jari Laine ' from dual union all
-- DatabaseDesign ~ Voting
------ ----------------- -------------------------------------------------------------------------------------
select 'DbDesign', 6896, 'Michelle Kolbe ' from dual union all
select 'DbDesign', 6897, 'Mark Hoxey ' from dual union all
select 'DbDesign', 6898, 'Heli Helskyaho ' from dual union all
select 'DbDesign', 6919, 'Rob Lockard ' from dual
)
select
f.category
,f.name as Nominee
,row_number()over(partition by f.category,f.name
order by
case
when regexp_like(t.column_value,'^user\d+$') then 2
when regexp_like(t.column_value,'^\d+$') then 3
when regexp_like(t.column_value,'\w{6}-\w{4}-\w{4}-\w{4}-\w{12}') then 4
else 1
end
) n
,t.column_value as VoterName
from finalists f,
table(
xt_http.get_matches(
pUrl => 'https://community.oracle.com/voting-history.jspa?ideaID='||to_char(f.userid,'fm0000')||'&start=0&numResults=1000'
,pPattern => 'alt="([^"]+)"'
,pGroup => 1
)
) t
order by 1,2,3,4
[collapse]
Categories: Development
Oracle package for HTTPS/HTTP[version 0.2]
A couple days ago i created simple package for HTTPS/HTTP, but I’ve decided now to improve it:
- Timeout parameter – it would be better to control connection time;
- Simple page parsing with PCRE regular expressions – to speed up and simplify page parsing, because if you want to get big number matched expressions from CLOB with regexp_xxx oracle functions, you have to call these functions many times with different [occurance] parameters, passing/accessing to the clob many times. But within java procedure it will be just one pass.
- Support of plsqldoc – the tool for automatically generating documentation in HTML format.(javadoc analogue)
Upd 2015-10-11:
- added HttpMethod parameter – so you can choose POST or GET method
- added function get_last_response – returns last HTTP response code.
You can download new version from github: https://github.com/xtender/xt_http
Also it may be interesting if you want to see how to get collection of CLOBs/varchar2 from JAVA stored procedure.
So with new functions I can even more easy get UpVoters list from prevous post:
select *
from table(
xt_http.get_matches(
pUrl => '&url'
,pPattern => 'alt="([^"]+)"'
,pGroup => 1
)
) t
Results
select *
from table(
xt_http.get_matches(
pUrl => '&url'
,pPattern => 'alt="([^"]+)"'
,pGroup => 1
)
) t
/
Enter value for url: https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000
NAME
--------------------------------------------------
Denes Kubicek
Pavel Luzanov
Martin Preiss
AlexAA
scherbak
TimHall
Toon Koppelaars
Dom Brooks
mweedman
BluShadow
Dmitry-Oracle
Mahir M. Quluzade
SA2
Dmitry A. Bogomolov
SQL*Plus
Alexander.Ryndin-Oracle
Mohamed Houri
Randolf Geist
ctrieb
UltraBlast
Kot Dmitriy
user9506228
Timur Akhmadeev
Franck Pachot
pudge
user12068799
user11933056
user11994768
iRAV
user12228999
nicher100
vva
Alexander Semenov
Dmitry_Nikiforov
Bud Light
user7111641
dbms_photoshop
AcidMan
achervov
GokhanAtil
user2616810
Harun Kucuksabanoglu
_Nikotin
Maki
user9066618
user10487079
IgorUsoltsev
edw_otn
Vigneswar Battu
user11198823
be here now
869219
user7543311
VladimirSitnikov
kamineff
Asmodeus
djeday84
Oleh Tyshchenko
87Rb-87Sr
911978
KoTTT
Konstantin
945154
953255
user12217223
Alexander_Anokhin-Oracle
oragraf.sql
Jack10154746
user9502569
Yury Pudovchenko
Sergey Navrotskiy
985277
xifos
MatthiasRogel
Mikhail Velikikh
user12134743
1025067
Oren Nakdimon
1051964
1094595
1209426
user11211533
user6115180
user3990689
d.nemolchev
user8925862
user11222376
user882251
user12279047
1284785
1323138
1336159
Grigory-OC
1373320
1373354
1411786
1421824
user13287062
1442254
1443436
1447180
KSDaemon
user12097700
Sergei Perminov
user3539222
Victor Osolovskiy
1560322
user13609377
user6672754
Alfredo Abate
user11315510
user12245839
1636030
user5399907
user3983717
user12276855
1744386
user9171605
ksAsmodeus
user12058508
1856219
user3214869
Trihin Pavel
1879578
1886567
user8869337
1890583
1913344
user11978061
user1438531
Menno Hoogendijk
2616420
2646629
rpc1
user4770257
user5217858
user11949728
al.netrusov
2693742
AKalugin
2721788
RZGiampaoli
2728073
2769955
2786798
2789541
BobDJ
2816036
user10123230
2831055
DBA_Hans_007
2880604
2896072
2902504
ApInvent
Pinto Das
natalka
2913706
2924025
2957186
2960288
-KE-
user5337688
3001391
3004351
oleksii.kondratenko
zeynep.samsa
Mehmet Kaplan
adbee6ae-dcb4-4c59-915b-487b33773a3f
6648e909-359d-4a32-b7bd-6cea3fd9fec3
mehmetyalcin
60e3f71b-bd5d-422c-b479-7a087d5b3827
3032511
c09042fd-15da-494b-b7b0-243ff4ceeb4b
5e087e18-4789-4923-92cb-cce149ba3072
5291adcf-242b-4f01-bbe6-b7ce44db1aac
9c0321bf-a358-49f9-88e4-462bd4fd5674
03b9266b-53f4-4503-ae04-b339c039bffd
user12044736
4a89f5a6-cc35-424e-8e3c-59964ad8d56b
54113fd4-6592-4a32-9920-663813bdd4c5
590d4b9f-32b6-43ad-b0c4-3b767407c055
ffb03087-d390-4068-98e5-4cfd73e66d00
3fa47a10-7b28-4857-9274-d175f3b7fd48
user5814569
d0b20163-7a58-4d4d-9a7d-01ec973bc3e1
8aaa1ed8-e0f6-4712-bef7-6b1e6579798f
1aee554c-832b-4fa5-bead-0680a53d1cc5
5daa756f-80aa-4260-b91d-10d2c51b78d6
e7897e6a-993d-46db-ad93-215b61b715ad
9f982eda-2b58-4d61-aad9-4c6a50d2dab7
user2503867
3032876
f3012cfb-62b5-4c86-a102-2172c3640d5b
68fe6d1e-d41c-4528-b076-ac3bc5289cc5
41232c56-5a29-442c-a1c6-d5b94477be1d
0682b6e1-8662-498c-8455-629032a25cea
user6592033
59961cb1-b4a6-470c-9802-44432911a7ff
user7345691
ab7980ce-71ba-4ec1-a578-6b716f2ae1ae
user5844404
723b639c-f6c6-4780-8ad6-0315564ef937
28e651f6-c9c3-4d2a-af03-001837eb99ba
3032942
841b96b4-ab84-461e-aed9-58f9df710406
user9961876
d7e48e5c-868f-4b2d-88b5-8614e9d35c80
3033022
f095cbb6-707a-4f40-8f18-a6a9dc37894d
3033091
9e9d3c99-9b5e-4fa2-89ac-4e6216209566
a9e702d3-f8e5-43ee-8e6c-0fe722d9ab50
bdd07d60-c467-4115-8149-8ef2af880d9a
d5571104-4726-4f06-b529-293dc667ae6e
user10865764
petrelevich
87a6503f-5717-4887-ac77-cd916002f53a
user7355088
user6083916
user2300447
user12299863
81ddc21a-7cb3-4298-a96b-ea7c9774b2c4
3405d13c-9d19-4903-8eb0-14a2544cb32b
user2427414
4670adeb-1c9d-4ce4-98eb-962bc4c68f5d
oldhook
1e4428aa-3a63-4a1a-90b3-f2b74292f502
user6367919
7c75e315-487d-4797-8e5b-f3dee58bbc79
user8828289
1a8ad4e7-759f-48ee-8054-c449540d0573
6f3b1262-c9d6-42d6-b703-fc4e6a40b7a1
037f209b-f643-4642-a059-79988d19d77b
PL99
3034106
3034166
user5489918
user2340616
naeel maqsudov
3036157
user2626322
52e8d732-4289-4d0d-b8c5-80e701f3c07d
4b2deedd-84c4-4b8b-8724-837c54dd764e
user12569643
07292d40-2bab-4e94-b68e-cfaae6c093a8
8ec394ac-fd54-4896-9810-0381bb75260e
caec3a42-0f98-440b-ad71-9522cb1e0a0e
9c52d45b-e2e4-489c-9a32-548c77f159b3
f6e966be-8576-4da9-a0df-8fec374b6cd3
5214be2e-d761-4a4e-aeba-23ff7bb4cf4e
f070b484-017d-4d4c-a740-f6ad9db37286
d4f322d3-0265-458d-948d-83bd66d5c7e3
[collapse]
Categories: Development
Oracle Database Developer Choice Awards: Up-Voters list
It’s very easy to get and analyze voters list using my new XT_HTTP package
We can get up-voters list by the URL:
https://community.oracle.com/voting-history.jspa?ideaID=NNNN&start=0&numResults=1000
where NNNN is Idea ID from nomenee’s page.
For example my page – https://community.oracle.com/ideas/6901 so my voters page will be https://community.oracle.com/voting-history.jspa?ideaID=6901&start=0&numResults=1000
BTW, though this page is called “VotingHistory”, but it shows up-voters only
-- create table upvoters as
with
finalists(category, userid, name) as (
-- SQL~ Voting:
------ ------------------ -------------------------------------------------------------------------------------
select 'SQL' , 6899, 'Stew Ashton ' from dual union all
select 'SQL' , 6900, 'Sean Stuber ' from dual union all
select 'SQL' , 6901, 'Sayan Malakshinov' from dual union all
select 'SQL' , 6902, 'Matthias Rogel ' from dual union all
select 'SQL' , 6903, 'Kim Berg Hansen ' from dual union all
select 'SQL' , 6904, 'Justin Cave ' from dual union all
select 'SQL' , 6905, 'Erik Van Roon ' from dual union all
select 'SQL' , 6906, 'Emrah Mete ' from dual union all
-- PL/SQL~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'PL/SQL' , 6907, 'Sean Stuber ' from dual union all
select 'PL/SQL' , 6908, 'Roger Troller ' from dual union all
select 'PL/SQL' , 6909, 'Patrick Barel ' from dual union all
select 'PL/SQL' , 6910, 'Morten Braten ' from dual union all
select 'PL/SQL' , 6911, 'Kim Berg Hansen ' from dual union all
select 'PL/SQL' , 6912, 'Bill Coulam ' from dual union all
select 'PL/SQL' , 6913, 'Adrian Billington' from dual union all
-- ORDS ~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'ORDS' , 6881, 'Tim St. Hilaire ' from dual union all
select 'ORDS' , 6882, 'Morten Braten ' from dual union all
select 'ORDS' , 6883, 'Kiran Pawar ' from dual union all
select 'ORDS' , 6884, 'Dimitri Gielis ' from dual union all
select 'ORDS' , 6885, 'Dietmar Aust ' from dual union all
select 'ORDS' , 6886, 'Anton Nielsen ' from dual union all
-- APEX ~ Voting
------ ------------------ -------------------------------------------------------------------------------------
select 'ORDS' , 6887, 'Trent Schafer ' from dual union all
select 'ORDS' , 6888, 'Paul MacMillan ' from dual union all
select 'ORDS' , 6889, 'Morten Braten ' from dual union all
select 'ORDS' , 6890, 'Kiran Pawar ' from dual union all
select 'ORDS' , 6891, 'Karen Cannell ' from dual union all
select 'ORDS' , 6893, 'Juergen Schuster ' from dual union all
select 'ORDS' , 6894, 'Jari Laine ' from dual union all
-- DatabaseDesign ~ Voting
------ ----------------- -------------------------------------------------------------------------------------
select 'DbDesign', 6896, 'Michelle Kolbe ' from dual union all
select 'DbDesign', 6897, 'Mark Hoxey ' from dual union all
select 'DbDesign', 6898, 'Heli Helskyaho ' from dual union all
select 'DbDesign', 6919, 'Rob Lockard ' from dual
)
,finalists_with_voters_pages as (
select finalists.*
,xt_http.get_page('https://community.oracle.com/voting-history.jspa?ideaID='||to_char(userid,'fm0000')||'&start=0&numResults=1000') page
from finalists
where rownum>0
)
,finalists_with_voters as (
select fp.category
,fp.userid
,fp.name
,voters.column_value as voter
from finalists_with_voters_pages fp
,table(cast(multiset(
select regexp_substr(page,'alt="([^"]+)"',1,level,'c',1)
from dual
connect by level<=regexp_count(page,'<li>')
) as ku$_vcnt)) voters
)
select *
from finalists_with_voters
Categories: Development
Very simple oracle package for HTTPS and HTTP
I don’t like to import certificates, so i cannot use httpuritype for HTTPS pages and I decided to create package which will work with https as http.
It was pretty easy with java stored procedures
github/XT_HTTP
java source: xt_http.jsp
create or replace and compile java source named xt_http as
package org.orasql.xt_http;
import javax.net.ssl.HttpsURLConnection;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.HttpURLConnection;
import java.sql.Connection;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;
public class XT_HTTP {
/**
* Function getPage
* @param String Page URL
* @return String
*/
public static CLOB getPage(java.lang.String sURL)
throws java.sql.SQLException
{
OracleDriver driver = new OracleDriver();
Connection conn = driver.defaultConnection();
CLOB result = CLOB.createTemporary(conn, false, CLOB.DURATION_CALL);
result.setString(1," ");
try {
URL url = new URL(sURL);
HttpURLConnection con = (HttpURLConnection)url.openConnection();
//HttpsURLConnection con = (HttpsURLConnection)url.openConnection();
if(con!=null){
BufferedReader br =
new BufferedReader(
new InputStreamReader(con.getInputStream()));
StringBuilder sb = new StringBuilder();
String line;
while ((line = br.readLine()) != null){
sb.append(line);
}
br.close();
result.setString(1,sb.toString());
}
} catch (MalformedURLException e) {
result.setString(1, e.getMessage());
} catch (IOException e) {
result.setString(1, e.getMessage());
}
return result;
}
public static java.lang.String getString(java.lang.String sURL) {
String result="";
try {
URL url = new URL(sURL);
HttpURLConnection con = (HttpURLConnection)url.openConnection();
if(con!=null){
BufferedReader br =
new BufferedReader(
new InputStreamReader(con.getInputStream()));
StringBuilder sb = new StringBuilder();
String line;
while ((line = br.readLine()) != null){
sb.append(line);
}
br.close();
result = sb.toString().substring(0,3999);
}
} catch (MalformedURLException e) {
return e.getMessage();
} catch (IOException e) {
return e.getMessage();
}
return result;
}
}
/
[collapse]
package xt_http
create or replace package XT_HTTP is
/**
* Get page as CLOB
*/
function get_page(pURL varchar2)
return clob
IS LANGUAGE JAVA
name 'org.orasql.xt_http.XT_HTTP.getPage(java.lang.String) return oracle.sql.CLOB';
/**
* Get page as varchar2(max=4000 chars)
*/
function get_string(pURL varchar2)
return varchar2
IS LANGUAGE JAVA
name 'org.orasql.xt_http.XT_HTTP.getString(java.lang.String) return java.lang.String';
end XT_HTTP;
/
[collapse]
We have to grant connection permissions:
dbms_java.grant_permission( grantee => 'XTENDER' -- username , permission_type => 'SYS:java.net.SocketPermission' -- connection permission , permission_name => 'ya.ru:443' -- connection address and port , permission_action => 'connect,resolve' -- types );
And now we can easily get any page:
USAGE example:
declare
c clob;
s varchar2(8000);
begin
--- Through HTTPS as CLOB:
c:=xt_http.get_page('https://google.com');
--- Through HTTP as CLOB
c:=xt_http.get_page('http://ya.ru');
--- Through HTTPS as varchar2:
s:=xt_http.get_string('https://google.com');
--- Through HTTP as varchar2
s:=xt_http.get_string('http://ya.ru');
end;
/
select length( xt_http.get_page('https://google.com') ) page_size from dual
Categories: Development
12c: New SQL PLAN OPERATIONS and HINTS
This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.
OPERATION_NAME
Description
Links
JSONTABLE EVALUATION
JSON_TABLE execution
XMLTABLE EVALUATION
This is new name for “COLLECTION ITERATOR PICKLER FETCH [XQSEQUENCEFROMXMLTYPE]”. XPATH EVALUATION still exists.
MATCH RECOGNIZE
New feature “PATTERN MATCHING”
STATISTICS COLLECTOR
Optimizer statistics collector
PX SELECTOR
Execution of the serial plan parts in the one of the parallel slaves
Hint PQ_DISTRIBUTE_WINDOW(@Query_block N), where N=1 for hash, N=2 for range, N=3 for list VECTOR
KEY VECTOR Inmemory aggregation
DETECT END
Unknown
DM EXP MAX AGGR
Unknown
DM EXP MAX PAR
Unknown
FAULT-TOLERANCE BUFFER
The fault-tolerance for parallel statement.
Patent #US8572051: Making parallel execution of structured query language statements fault-tolerant
- How Adaptive Plans Work
- ADAPTIVE_PLAN hint
- NO_ADAPTIVE_PLAN hint
- Christian Antognini “STATISTICS COLLECTOR Row Source Operation and LOBs”
- CREATE TABLE … AS SELECT
- INSERT INTO … SELECT into an empty table using a direct-path insert
- USE_CUBE Hint
- NO_USE_CUBE Hint
- Joining Cubes to Tables and Views
- CUBE_AJ/CUBE_SJ hints
Example
SQL> explain plan for
2 select--+ parallel
3 owner,object_name
4 from xt_test l
5 where exists(select/*+ no_unnest */ 0 from dual where dummy=object_name);
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2189761709
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 177K (1)| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 91060 | 2756K| 113 (0)| Q1,00 | P->S | QC (RAND) |
|* 3 | FILTER | | | | | Q1,00 | PCWC | |
| 4 | PX BLOCK ITERATOR | | 91060 | 2756K| 113 (0)| Q1,00 | PCWC | |
| 5 | INDEX FAST FULL SCAN| IX_TEST_1 | 91060 | 2756K| 113 (0)| Q1,00 | PCWP | |
|* 6 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| | | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SYS"."DUAL" "DUAL" WHERE "DUMMY"=:B1))
6 - filter("DUMMY"=:B1)
[collapse]
- Randolf Geist “12c Parallel Execution New Features: PX SELECTOR”
- Concurrent Execution of Union All
- PQ_CONCURRENT_UNION Hint
- NO_PQ_CONCURRENT_UNION Hint
- Adaptive Plans: Parallel Distribution Methods
- Randolf Geist “Parallel Execution Skew – 12c Hybrid Hash Distribution With Skew Detection”
- Randolf Geist “12c Hybrid Hash Distribution with Skew Detection / Handling”
- PQ_SKEW / NO_PQ_SKEW hints
Example
-- TESTPART - list-partitiioned table:
-------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 74384 | 102| | | | | |
| PX COORDINATOR | | | | | | | | |
| PX SEND QC (RANDOM) | :TQ10001 | 74384 | 102| | | Q1,01 | P->S | QC (RAND) |
| WINDOW SORT | | 74384 | 102| | | Q1,01 | PCWP | |
| PX RECEIVE | | 74384 | 100| | | Q1,01 | PCWP | |
| PX SEND RANGE | :TQ10000 | 74384 | 100| | | Q1,00 | P->P | RANGE |
| PX BLOCK ITERATOR | | 74384 | 100| 1 | 3 | Q1,00 | PCWC | |
| TABLE ACCESS FULL| TESTPART | 74384 | 100| 1 | 3 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE_WINDOW(@"SEL$1" 3)
FULL(@"SEL$1" “TESTPART"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
[collapse]
Hint PQ_DISTRIBUTE_WINDOW(@Query_block N), where N=1 for hash, N=2 for range, N=3 for list VECTOR
KEY VECTOR Inmemory aggregation
- In-Memory Aggregation
- V$KEY_VECTOR
- USE_VECTOR_AGGREGATION / NO_USE_VECTOR_AGGREGATION hints
- VECTOR_TRANSFORM
/ NO_VECTOR_TRANSFORM hints - VECTOR_TRANSFORM_DIMS
/ NO_VECTOR_TRANSFORM_DIMS hints - VECTOR_TRANSFORM_FACT
/ NO_VECTOR_TRANSFORM_FACT hints
Example
SQL> explain plan for select/*+ parallel(t 4) PQ_DISTRIBUTE_WINDOW(2) */ count(*) over(partition by owner) cnt,owner from xt_test t;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3410952625
---------------------------------------------------------------------------------------------------
| Id | Operation |Name |Rows |Cost |Pstart|Pstop| TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |91060| 124| | | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) |:TQ10001|91060| 124| | | Q1,01 | P->S | QC (RAND) |
| 3 | WINDOW CONSOLIDATOR BUFFER| |91060| 124| | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | |91060| 124| | | Q1,01 | PCWP | |
| 5 | PX SEND HASH |:TQ10000|91060| 124| | | Q1,00 | P->P | HASH |
| 6 | WINDOW SORT | |91060| 124| | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | |91060| 122| 1 | 4| Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL |XT_TEST |91060| 122| 1 | 4| Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of table property
[collapse]
Patent #US8572051: Making parallel execution of structured query language statements fault-tolerant
- PX_FAULT_TOLERANCE / NO_PX_FAULT_TOLERANCE hints
See also:
- Randolf Geist “12c New Optimizer Features”
- Randolf Geist “Parallel Execution 12c New Features Overview”
HINTS:
PATH
HINT_CLASS
HINT_NAME
VERSION
VERSION_OUTLINE
ALL
WITH_PLSQL
WITH_PLSQL
12.1.0.1
ALL -> ANSI_REARCH
ANSI_REARCH 1
ANSI_REARCH
NO_ANSI_REARCH 12.1.0.2 12.1.0.2 ALL -> EXECUTION BATCH_TABLE_ACCESS_BY_ROWID 2 BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID 12.1.0.1 12.1.0.1 INMEMORY INMEMORY
NO_INMEMORY 12.1.0.2 12.1.0.2 INMEMORY_PRUNING INMEMORY_PRUNING
NO_INMEMORY_PRUNING 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> ZONEMAP ZONEMAP ZONEMAP
NO_ZONEMAP 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT
NO_DATA_SECURITY_REWRITE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO CLUSTER_BY_ROWID CLUSTER_BY_ROWID
CLUSTER_BY_ROWID 12.1.0.1(11.2.0.4) 12.1.0.1 ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE BITMAP_AND BITMAP_AND 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> ADAPTIVE_PLAN ADAPTIVE_PLAN ADAPTIVE_PLAN
NO_ADAPTIVE_PLAN 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> CBO -> AUTO_REOPT AUTO_REOPTIMIZE 2 AUTO_REOPTIMIZE
NO_AUTO_REOPTIMIZE 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD ANTIJOIN CUBE_AJ 12.1.0.1 12.1.0.1 SEMIJOIN CUBE_SJ 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_CUBE JOIN USE_CUBE
NO_USE_CUBE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTIAL_JOIN PARTIAL_JOIN PARTIAL_JOIN
NO_PARTIAL_JOIN 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTITION USE_HIDDEN_PARTITIONS USE_HIDDEN_PARTITIONS 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN
NO_PARTIAL_ROLLUP_PUSHDOWN 12.1.0.1 12.1.0.1 PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION
NO_PQ_CONCURRENT_UNION 12.1.0.1 12.1.0.1 PQ_DISTRIBUTE_WINDOW PQ_DISTRIBUTE_WINDOW 12.1.0.1 12.1.0.1 PQ_FILTER PQ_FILTER 12.1.0.1 12.1.0.1 PQ_SKEW PQ_SKEW
NO_PQ_SKEW 12.1.0.1 12.1.0.1 PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE
NO_PX_FAULT_TOLERANCE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ -> PQ_REPLICATE PQ_REPLICATE PQ_REPLICATE
NO_PQ_REPLICATE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS
NO_GATHER_OPTIMIZER_STATISTICS 12.1.0.1 ALL -> COMPILATION -> TRANSFORMATION ELIM_GROUPBY ? ELIM_GROUPBY
NO_ELIM_GROUPBY ALL -> COMPILATION -> CBO -> CBQT -> VECTOR_AGG
and
ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> VECTOR_AGG USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION
NO_USE_VECTOR_AGGREGATION 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM VECTOR_TRANSFORM
NO_VECTOR_TRANSFORM 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS
NO_VECTOR_TRANSFORM_DIMS 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT
NO_VECTOR_TRANSFORM_FACT 12.1.0.2 12.1.0.2
NO_DECORRELATE 12.1.0.1 12.1.0.1
NO_ANSI_REARCH 12.1.0.2 12.1.0.2 ALL -> EXECUTION BATCH_TABLE_ACCESS_BY_ROWID 2 BATCH_TABLE_ACCESS_BY_ROWID
NO_BATCH_TABLE_ACCESS_BY_ROWID 12.1.0.1 12.1.0.1 INMEMORY INMEMORY
NO_INMEMORY 12.1.0.2 12.1.0.2 INMEMORY_PRUNING INMEMORY_PRUNING
NO_INMEMORY_PRUNING 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> ZONEMAP ZONEMAP ZONEMAP
NO_ZONEMAP 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT
NO_DATA_SECURITY_REWRITE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO CLUSTER_BY_ROWID CLUSTER_BY_ROWID
CLUSTER_BY_ROWID 12.1.0.1(11.2.0.4) 12.1.0.1 ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE BITMAP_AND BITMAP_AND 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> ADAPTIVE_PLAN ADAPTIVE_PLAN ADAPTIVE_PLAN
NO_ADAPTIVE_PLAN 12.1.0.2 12.1.0.2 ALL -> COMPILATION -> CBO -> AUTO_REOPT AUTO_REOPTIMIZE 2 AUTO_REOPTIMIZE
NO_AUTO_REOPTIMIZE 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD ANTIJOIN CUBE_AJ 12.1.0.1 12.1.0.1 SEMIJOIN CUBE_SJ 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_CUBE JOIN USE_CUBE
NO_USE_CUBE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTIAL_JOIN PARTIAL_JOIN PARTIAL_JOIN
NO_PARTIAL_JOIN 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PARTITION USE_HIDDEN_PARTITIONS USE_HIDDEN_PARTITIONS 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN
NO_PARTIAL_ROLLUP_PUSHDOWN 12.1.0.1 12.1.0.1 PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION
NO_PQ_CONCURRENT_UNION 12.1.0.1 12.1.0.1 PQ_DISTRIBUTE_WINDOW PQ_DISTRIBUTE_WINDOW 12.1.0.1 12.1.0.1 PQ_FILTER PQ_FILTER 12.1.0.1 12.1.0.1 PQ_SKEW PQ_SKEW
NO_PQ_SKEW 12.1.0.1 12.1.0.1 PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE
NO_PX_FAULT_TOLERANCE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> PQ -> PQ_REPLICATE PQ_REPLICATE PQ_REPLICATE
NO_PQ_REPLICATE 12.1.0.1 12.1.0.1 ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS
NO_GATHER_OPTIMIZER_STATISTICS 12.1.0.1 ALL -> COMPILATION -> TRANSFORMATION ELIM_GROUPBY ? ELIM_GROUPBY
NO_ELIM_GROUPBY ALL -> COMPILATION -> CBO -> CBQT -> VECTOR_AGG
and
ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> VECTOR_AGG USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION
NO_USE_VECTOR_AGGREGATION 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM VECTOR_TRANSFORM
NO_VECTOR_TRANSFORM 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS
NO_VECTOR_TRANSFORM_DIMS 12.1.0.2 12.1.0.2 VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT
NO_VECTOR_TRANSFORM_FACT 12.1.0.2 12.1.0.2
ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> DECORRELATE DECORRELATE DECORRELATE
NO_DECORRELATE 12.1.0.1 12.1.0.1
Categories: Development
RESULT_CACHE: run-time dependency tracking
As you know, since 11.2 “relies_on” clause was deprecated and oracle tracks dependencies at runtime now.
Test function and tables
create or replace function f_without_deps(p_tab varchar2) return varchar2 as res varchar2(30); begin execute immediate 'select '''||p_tab||''' from '||p_tab||' where rownum=1' into res; return res; end; / create table a as select 'a' a from dual; create table b as select 'b' b from dual; create view v_ab as select a,b from a,b;
[collapse]
And it works fine with normal tables:
v_ab
SQL> exec :p_tab:='v_ab'; PL/SQL procedure successfully completed. SQL> call DBMS_RESULT_CACHE.flush(); Call completed. SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual; RESULT ---------- v_ab 1 row selected. SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id; NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID ----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- ------- XTENDER.F_WITHOUT_DEPS 1579 0 Dependency Published 0 0 0 XTENDER.V_AB 3127 2 Dependency Published 0 0 0 XTENDER.B 778 3 Dependency Published 0 0 0 XTENDER.A 464 4 Dependency Published 0 0 0 select/*+ result_cache */ f_without_deps(:p_tab) result from dual 1749 1 Result Published 0 0 0
[collapse]
But don’t forget that the result_cache also caches such functions with the objects, that usually should not be cached, and such objects will not be listed in the result_cache dependencies list:
v$database
SQL> exec :p_tab:='v$database'; PL/SQL procedure successfully completed. SQL> call DBMS_RESULT_CACHE.flush(); Call completed. SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual; RESULT ---------- v$database 1 row selected. SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id; NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID ----------------------------------------------------------------- ---------- --- ---------- --------- ---------- ---------- ------- XTENDER.F_WITHOUT_DEPS 772 0 Dependency Published 0 0 0 PUBLIC.V$DATABASE 1363 2 Dependency Published 0 0 0 select/*+ result_cache */ f_without_deps(:p_tab) result from dual 2283 1 Result Published 0 0 0 3 rows selected.
[collapse]
SYS.OBJ$
SQL> exec :p_tab:='sys.obj$'; PL/SQL procedure successfully completed. SQL> call DBMS_RESULT_CACHE.flush(); Call completed. SQL> select/*+ result_cache */ f_without_deps(:p_tab) result from dual; RESULT ---------- sys.obj$ 1 row selected. SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id; NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALID ----------------------------------------------------------------- ---------- -- ---------- --------- ---------- ---------- ------- XTENDER.F_WITHOUT_DEPS 3922 0 Dependency Published 0 0 0 select/*+ result_cache */ f_without_deps(:p_tab) result from dual 3753 1 Result Published 0 0 0 2 rows selected.
[collapse]
We easily check that the queries with any table in SYS schema or with sysdate,systimestamp,current_date,current_timestamp,dbms_random will not be cached:
SYS tables
SQL> select/*+ result_cache */ current_scn result from v$database;
RESULT
----------
##########
1 row selected.
SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o order by type,id;
no rows selected
SQL> explain plan for select/*+ result_cache */ * from sys.obj$;
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
Plan hash value: 2311451600
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87256 | 7328K| 296 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| OBJ$ | 87256 | 7328K| 296 (1)| 00:00:04 |
--------------------------------------------------------------------------
[collapse]
And even if create own tables in SYS schema(don’t do it
), they will not be cached :
SYS.V_AB
SYS> create table a as select 'a' a from dual; SYS> create table b as select 'b' b from dual; SYS> create view v_ab as select a,b from a,b; SYS> grant select on v_ab to xtender; XTENDER> explain plan for select/*+ result_cache */ * from sys.v_ab; PLAN_TABLE_OUTPUT ----------------------------------------------------------------------------- Plan hash value: 215283502 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 6 | 4 (0)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 1 | 6 | 4 (0)| 00:00:01 | | 2 | TABLE ACCESS FULL | A | 1 | 3 | 2 (0)| 00:00:01 | | 3 | BUFFER SORT | | 1 | 3 | 2 (0)| 00:00:01 | | 4 | TABLE ACCESS FULL | B | 1 | 3 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2)
[collapse]
But sys_context and userenv will be cached successbully:
sys_context
SQL> explain plan for select/*+ result_cache */ sys_context('userenv','os_user') from dual;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | 267m2hcwj08nq5kwxcb0nb2ka8 | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ sys_context('userenv','os_user') from dual"
14 rows selected.
[collapse]
userenv
SQL> explain plan for select/*+ result_cache */ userenv('instance') from dual;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 1388734953
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | RESULT CACHE | dxzj3fks1sqfy35shbbst4332h | | | |
| 2 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Result Cache Information (identified by operation id):
------------------------------------------------------
1 - column-count=1; attributes=(single-row); parameters=(sys_context);
name="select/*+ result_cache */ userenv('instance') from dual"
[collapse]
Categories: Development
A function gets called twice if the result_cache is used
Recently I showed simple example how result_cache works with non-deterministic functions and observed strange behaviour: a function gets fired once in the normal query, but twice with the result_cache hint.
Moreover, only third and subsequent query executions return same cached results as second query execution.
I didn’t want to investigate such behavior, just because 1) we should not cache the results of non-deterministic functions and 2) it doesn’t matter if we use deterministic functions.
But later I was asked to explain this, so this post is just a short description with test case.
Look at the simple function that just returns random values:
create or replace function f_nondeterministic(p int:=100) return int as res number; begin res:=round(dbms_random.value(0,p)); return res; end;
SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
481
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
with result_cache_statistics
SQL> exec dbms_result_cache.flush;
PL/SQL procedure successfully completed.
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
481
SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');
NAME VALUE
-------------------------------------------------- ----------
Create Count Success 1
Find Count 0
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');
NAME VALUE
-------------------------------------------------- ----------
Create Count Success 1
Find Count 1
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
689
SQL> select name,value from v$result_cache_statistics where name in ( 'Create Count Success', 'Find Count');
NAME VALUE
-------------------------------------------------- ----------
Create Count Success 1
Find Count 2
SQL> select name,bucket_no, id,type,status,pin_count,scan_count,invalidations from v$result_cache_objects o;
NAME BUCKET_NO ID TYPE STATUS PIN_COUNT SCAN_COUNT INVALIDATIONS
-------------------------------------------------- ---------- ---------- ---------- --------- ---------- ---------- -------------
XTENDER.F_NONDETERMINISTIC 552 0 Dependency Published 0 0 0
select/*+ result_cache */ f_nondeterministic(1000) 2102 1 Result Published 0 2 0
nondeter from dual
[collapse]
As you can see, second execution returns different result than first one.
If we change this function:
create or replace function f_nondeterministic(p int:=100) return int
as
res number;
begin
res:=round(dbms_random.value(0,p));
dbms_output.put_line('fired! ('||res||')');
return res;
end;
and repeat this test-case:
SQL> select/*+ result_cache */ f_nondeterministic(1000) nondeter from dual;
NONDETER
----------
943 -- << (2)
1 row selected.
fired! (607) -- << (1)
fired! (943) -- << (2)
SQL> /
NONDETER
----------
607 -- << (1)
1 row selected.
SQL> /
NONDETER
----------
607 -- << (1)
1 row selected.
SQL> /
NONDETER
----------
607 -- << (1)
1 row selected.
we will see that there were 2 function executions: first result was cached, and the second was fetched!
Categories: Development