tag:blogger.com,1999:blog-12865988399249959972024-03-14T05:32:48.812+02:00Rafu on dbRafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.comBlogger169125tag:blogger.com,1999:blog-1286598839924995997.post-88997749020892429392023-09-14T08:30:00.006+03:002023-09-25T09:28:29.657+03:00Sqlstat History
Oracle Diagnosics pack purchased? AWR reports reveal many aspects of database usage and behavior. In addition to Enterprise Manager Cloud control pages the dba_hist_* views are available.
Here is a script that visualizes most elapsed_time and shows 18 figures of sql clauses used and captured in dba_hist_sqlstat view.
<PRE>
select trim(rpad(' ',ceil(t.elapsed_time/t.mxe*len),'*'))||trim(rpad(' ',ceil((t.mxe-t.elapsed_time)/t.mxe*len),'-')) vis
, t.*
from (
select s.*
, max(s.elapsed_time)over() mxe
, 80 len
from (
select min(trunc(sn.begin_interval_time,'hh24')) bg
, max(trunc(sn.end_interval_time,'hh24')) en
, max(sn.snap_id) snap_id
, ss.parsing_schema_name
, ss.module
-- , ss.action
-- , ss.sql_id, (select sql_text from dba_hist_sqltext t where ss.sql_id = t.sql_id and rownum = 1) sql_text
, sum(ss.elapsed_time_delta) elapsed_time
, sum(ss.sharable_mem) sharable_mem
, sum(ss.version_count) version
, sum(ss.fetches_delta) fetches
, sum(ss.end_of_fetch_count_delta) end_of_fetch_count
, sum(ss.sorts_delta) sorts
, sum(ss.executions_delta) executions
, sum(ss.loads_delta) loads
, sum(ss.parse_calls_delta) parse_calls
, sum(ss.buffer_gets_delta) buffer_gets
, sum(ss.rows_processed_delta) rows_processed
, sum(ss.cpu_time_delta) cpu_time
, sum(ss.iowait_delta) iowait
, sum(ss.plsexec_time_delta) plsexec_time
, sum(ss.physical_read_requests_delta) physical_read_requests
, sum(ss.physical_read_bytes_delta) physical_read_bytes
, sum(ss.physical_write_requests_delta) physical_write_requests
, sum(ss.physical_write_bytes_delta) physical_write_bytes
from dba_hist_sqlstat ss
, dba_hist_snapshot sn
where ss.snap_id = sn.snap_id
and ss.instance_number = sn.instance_number
and ss.dbid = ss.dbid
and sn.end_interval_time > trunc(sysdate)
group by ss.parsing_schema_name
, ss.module
-- , ss.action
-- , ss.sql_id
, sn.snap_id
) s ) t
order by module,vis
;
</PRE>Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-70826635311380496632023-02-03T14:39:00.006+02:002023-02-03T15:21:43.610+02:00Access Path Suggestor and Generator -Mariadb
Earlier I have published accesspath suggestors for <a href="http://rafudb.blogspot.com/2019/01/access-path-suggestor.html">Oracle</a> and <a href="http://rafudb.blogspot.com/2019/10/access-path-suggestor-postgresql.html">Postgresql</a>. Those are interpreting accesspath bidirectionally. Here is one for Mariadb. I quess it suits also for Mysql. I have not tried. This traverses the foreign key tree only in one direction. So the dependencies from a <tt>:roottable</tt> to <tt>:goaltablename</tt> in a <tt>:tableschema</tt> are reported. Also a query for the whole accesspath is generated.
<PRE>
with recursive fks as (
SELECT constraint_name,
table_name,
column_name,
referenced_table_name,
referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = :tableschema
AND referenced_column_name IS NOT NULL
), pths as (
select 1 lvl,
cast(concat('/',table_name) as varchar(4000)) pth,
constraint_name,
table_name,
column_name,
referenced_table_name,
referenced_column_name,
cast(concat(referenced_table_name,', ',table_name,' ',constraint_name) as varchar(4000)) fro,
cast(concat(referenced_table_name,'.',referenced_column_name,' = ',constraint_name,'.',column_name) as varchar(4000)) joi
from fks
where referenced_table_name = :roottablename
union all
select r.lvl+1,
concat(r.pth,'/',c.table_name),
c.constraint_name,
c.table_name,
c.column_name,
c.referenced_table_name,
c.referenced_column_name,
concat(r.fro,', ',c.table_name,' ',c.constraint_name),
concat(r.joi,' and ',r.constraint_name,'.',c.referenced_column_name,' = ',c.constraint_name,'.',c.column_name)
from pths r, fks c
where r.table_name = c.referenced_table_name
) cycle table_name, referenced_table_name restrict
select concat('select * from ',fro,' where ',joi,';') sq
, pths.pth
from pths
where table_name = :goaltablename
;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-85300704877141455252020-09-08T15:04:00.002+03:002020-09-21T13:22:08.221+03:00Aggregating Timestamp Ranges with Postgresql
Having timestamp ranges and need to combine those from several lines. Here is a rehearsal to create an user defined aggregate function range_agg with postgresql. Range_agg handles tsrange datatyped input parameter and returns an array of those. At the end there is an usage example with unnest(range_agg(aa)) to receive aggregated and generated ranges. Array is needed because aggregation of ranges can produce several ranges when input ranges do not overlap or meet each other. Ranges and ranges_final functions are used and implements the behavior of the range_agg(tsrange) aggregate.
<PRE>
create or replace function ranges(tsrange[],tsrange)
returns tsrange[] as
$$
select array_append($1,$2);
$$ language 'sql' strict;
create or replace function ranges_final(tsrange[])
returns tsrange[] as
$$
with times as (
select st, en, max(newst) over(order by st,en) ledge
from (
select st, en, case when st <= max(le) over(order by st,en) then null else st end as newst
from (
select st, en, lag(en) over(order by st, en) le
from (
select distinct lower(ra) st, upper(ra) en
from (
select unnest($1) ra
) s0
) s1
) s2
) s3
), ranges as (
select ledge st, max(en) en
from times
group by ledge
order by ledge
)
select array_agg(tsrange(st,en))
from ranges;
$$ language 'sql' strict;
create or replace aggregate range_agg(tsrange)
(
sfunc = ranges,
stype = tsrange[],
finalfunc = ranges_final,
initcond = '{}'
);
select unnest(range_agg(ra)) ra from (
-- 06 - 07
select tsrange(current_date+time'06:00', current_date+time'07:00') ra
union all
-- 08 - 10
select tsrange(current_date+time'08:00', current_date+time'09:00') ra
union all
select tsrange(current_date+time'09:00', current_date+time'10:00') ra
union all
-- 11 - 14
select tsrange(current_date+time'11:00', current_date+time'13:00') ra
union all
select tsrange(current_date+time'12:00', current_date+time'14:00') ra
) aaa
;
["2020-09-08 06:00:00","2020-09-08 07:00:00")
["2020-09-08 08:00:00","2020-09-08 10:00:00")
["2020-09-08 11:00:00","2020-09-08 14:00:00")
</PRE>
Documentation links:
<a href="https://www.postgresql.org/docs/12/rangetypes.html">rangetypes</a>,
<a href="https://www.postgresql.org/docs/12/functions-array.html">functions-array</a>,
<a href="https://www.postgresql.org/docs/12/functions-aggregate.html">functions-aggregate</a>,
<a href="https://www.postgresql.org/docs/12/sql-createaggregate.html">sql-createaggregate</a> and
<a href="https://www.postgresql.org/docs/12/xaggr.html">xaggr</a>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com1tag:blogger.com,1999:blog-1286598839924995997.post-15653676626399742882019-10-07T14:34:00.001+03:002023-11-24T11:12:29.218+02:00Unindex PostgresqlThe issue of <a href="http://rafudb.blogspot.com/2009/02/unindex.html">foreign keys without indexing</a> was the starting point of my blog. Just browsing and I found <a href="https://www.cybertec-postgresql.com/en/index-your-foreign-key/">a postgresql version of unindex query</a>. Copied the query here. Visit the original authors <a href="https://www.cybertec-postgresql.com/en/index-your-foreign-key/">page</a> for more explanation and an example. Expecially liked the commented code /*the first index columns must be the same as the key columns, but order doesn't matter*/.
<PRE>
SELECT c.conrelid::regclass AS "table",
/* list of key column names in order */
string_agg(a.attname, ',' ORDER BY x.n) AS columns,
pg_catalog.pg_size_pretty(
pg_catalog.pg_relation_size(c.conrelid)
) AS size,
c.conname AS constraint,
c.confrelid::regclass AS referenced_table,
'create index '||c.conname||'_idx on '||c.conrelid::regclass||'('||string_agg(a.attname, ',' ORDER BY x.n)||');' idx
FROM pg_catalog.pg_constraint c
/* enumerated key column numbers per foreign key */
CROSS JOIN LATERAL
unnest(c.conkey) WITH ORDINALITY AS x(attnum, n)
/* name for each key column */
JOIN pg_catalog.pg_attribute a
ON a.attnum = x.attnum
AND a.attrelid = c.conrelid
WHERE NOT EXISTS
/* is there a matching index for the constraint? */
(SELECT 1 FROM pg_catalog.pg_index i
WHERE i.indrelid = c.conrelid
/* the first index columns must be the same as the
key columns, but order doesn't matter */
AND (i.indkey::smallint[])[0:cardinality(c.conkey)-1]
@> c.conkey)
AND c.contype = 'f'
GROUP BY c.conrelid, c.conname, c.confrelid
ORDER BY pg_catalog.pg_relation_size(c.conrelid) DESC;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-68670154558639026572019-10-03T15:36:00.001+03:002019-10-03T16:07:41.719+03:00Access Path Suggestor - Postgresql Here is an improved access path suggestor from postgresql metadata. Based from my <a href="http://rafudb.blogspot.com/2019/01/access-path-suggestor.html">Oracle version</a>. In addition to visualized path alternative this generates a query based on the found access path. Could be used in getting familiar with a new data model. Should work also with foreign keys with multiple columns. Cycle detection missing.
Just give names of the tables whose path alternatives you are interested in as :foo and :bar binds.
<PRE>
WITH RECURSIVE fks as (
SELECT cc.fk_schematable,
cc.fk_table,
string_agg(a.attname, ',' ORDER BY x.n) AS fk_columns,
cc.conname AS constraint_name,
cc.uk_schematable,
cc.uk_table,
string_agg(b.attname, ',' ORDER BY y.n) AS pk_columns,
string_agg(cc.uk_table||'.'||b.attname||' = '||cc.fk_table||'.'||a.attname, ' and ' ORDER BY y.n) AS joi
FROM (
SELECT c.conrelid::regclass as uk_schematable,
pk.relname uk_table,
c.conname,
c.confrelid::regclass as fk_schematable,
fk.relname fk_table,
c.conkey,
c.confkey,
c.conrelid,
c.confrelid
FROM pg_catalog.pg_constraint c
INNER JOIN pg_catalog.pg_class pk ON c.conrelid!=0 AND c.conrelid=pk.oid
INNER JOIN pg_catalog.pg_class fk ON c.confrelid!=0 AND c.confrelid=fk.oid
WHERE c.contype = 'f'
) cc
/* enumerated key column numbers per foreign key */
CROSS JOIN LATERAL
unnest(cc.conkey) WITH ORDINALITY AS x(attnum, n)
/* name for each foreign key column */
JOIN pg_catalog.pg_attribute a
ON a.attnum = x.attnum
AND a.attrelid = cc.conrelid
CROSS JOIN LATERAL
unnest(cc.confkey) WITH ORDINALITY AS y(attnum, n)
/* name for each unique key column */
JOIN pg_catalog.pg_attribute b
ON b.attnum = y.attnum
AND b.attrelid = cc.confrelid
WHERE x.n=y.n
GROUP BY cc.fk_schematable, cc.fk_table, cc.conname, cc.uk_schematable, cc.uk_table
), pths as (
SELECT uk_schematable as o1,uk_table as t1,constraint_name,fk_schematable as o2,fk_table as t2,'-<' dir, joi
FROM fks
UNION ALL
SELECT fk_schematable as o1,fk_table as t1,constraint_name,uk_schematable as o2,uk_table as t2,'>-' dir, joi
FROM fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth,fro,joi) as (
SELECT o1,t1,constraint_name,o2,t2,dir,1 lvl,o1||dir||o2 pth, o1||' '||t1||', '||o2||' '||t2, joi
FROM pths
WHERE t1 = :foo
UNION ALL
SELECT s.o1,s.t1,s.constraint_name,s.o2,s.t2
, s.dir,lvl+1
, prio.pth||s.dir||s.o2
, prio.fro||', '||s.o2||' '||s.t2
, prio.joi||' and '||s.joi
FROM rcte prio INNER JOIN pths s ON prio.o2=s.o1 AND prio.constraint_name != s.constraint_name
WHERE prio.lvl < 30
)
SELECT pth,'select * from '||fro||' where '||joi||';' sq
FROM rcte
WHERE t2 = :baz
ORDER BY lvl,pth
;
</PRE>
Testing
<PRE>
create table foo(i int primary key);
create table bar(j int primary key, i int references foo);
create table baz(k int primary key, j int references bar);
</PRE>
<PRE>
pth sq
foo>-bar>-baz select * from foo foo, bar bar, baz baz where bar.i = foo.i and baz.j = bar.j;
</PRE>Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-76976713710711520272019-10-02T09:36:00.001+03:002019-10-02T14:37:49.406+03:00Pascal Matrix - Wishes Come True
Years ago I made a wish in my blog that an analytic function should work in a recursive query. My approach to generate <a href="https://rafudb.blogspot.com/2012/06/pascal-matrix.html">Pascal Matrix</a> seems to work with Oracle 19c database.
<PRE>
with n (u) as (
select 1 from dual
union all
select n.u+1
from n
where n.u < 8
), q as (
select n.u v, m.u w
from n, n m
), r (v,w,s,d,e) as (
select v,w, v,w,sum(w)over(order by w)
from q
where v = 1
union all
select q.v,q.w
,r.d
,r.e
,sum(r.e)over(order by r.w)
from r
inner join q
on r.w=q.w and r.v+1=q.v
)
select v,w,s
from r
;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-62719353396699652132019-09-09T16:13:00.000+03:002019-09-09T16:13:43.970+03:00Alter view and dba_dependencies
Adding constraints to a view. Maybe better to consider recreating. Dependencies are populated more complete that way.
<PRE>
create table huba(a int primary key);
create table hubb(b int primary key);
create table link(a int references huba, b int references hubb);
create or replace view fact as (select a,b from link);
alter view fact add constraint f_a_fk foreign key (a) references huba disable;
alter view fact add constraint f_b_fk foreign key (b) references hubb disable;
select referenced_name from dba_dependencies where name = 'FACT';
--HUBB
--LINK
create or replace view fact (
a
, b
, constraint f_a_fk foreign key (a) references huba disable
, constraint f_b_fk foreign key (b) references hubb disable
)as (select a,b from link);
select referenced_name from dba_dependencies where name = 'FACT';
--HUBA
--HUBB
--LINK
drop table huba cascade constraints purge;
drop table hubb cascade constraints purge;
drop view fact;
</PRE>Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com2tag:blogger.com,1999:blog-1286598839924995997.post-69267163032817328922019-02-01T15:32:00.001+02:002019-02-01T15:34:00.915+02:00Errors With Depth First Hierarchical Query and Table Of Types
Struggling with a hierarchical query and table of types. Getting ORA-00600: internal error code, arguments: [koxsi2sz1] and [rworupo.1] errors with 12.1.0.2 version.
<PRE>
WITH Factorial (operand,total_so_far,foo) AS (
SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1), foo
FROM Factorial
WHERE operand > 1)
SEARCH breadth FIRST BY operand SET order1
SELECT * FROM Factorial
;
</PRE>
Breadth first and everything is fine. But changing to depth first the problems occur.
<PRE>
WITH Factorial (operand,total_so_far,foo) AS (
SELECT 5 operand, 5 total_so_far, sys.odcinumberlist(1,2) foo FROM dual -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1), foo
FROM Factorial
WHERE operand > 1)
SEARCH <B>depth</B> FIRST BY operand SET order1
SELECT * FROM Factorial
;
</PRE>
With 18.4 version the error message is more tolerable ORA-00932: inconsistent datatypes: expected UDT got SYS.ODCINUMBERLIST. But no success with the results.
<P/>
Getting around the problem is to create a NumberListWrapper wrapper type for the array type.
The workaround seems to work at least with 12.2 and 18.4 versions.
<PRE>
CREATE OR REPLACE TYPE NumberListWrapper AS OBJECT (
numbertable sys.odciNumberlist,
MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC
);
/
CREATE OR REPLACE TYPE BODY NumberListWrapper AS
MAP MEMBER FUNCTION comparable RETURN NUMBER DETERMINISTIC IS
BEGIN
RETURN 1;
END;
END;
/
WITH Factorial (operand,total_so_far,foo) AS (
SELECT 5 operand, 5 total_so_far, <B>NumberListWrapper</B>(sys.odcinumberlist(1,2)) foo FROM dual -- Using anchor member to pass in "5"
UNION ALL
SELECT operand-1, total_so_far * (operand-1), foo
FROM Factorial
WHERE operand > 1)
SEARCH DEPTH FIRST BY operand SET order1
SELECT * FROM Factorial
;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-25827311687064468092019-01-30T11:03:00.000+02:002019-01-30T14:13:20.450+02:00Select For Update Locks Joined Rows
Select for update locks the joined rows even thou columns from the table are not in the select list. The rows used in a predicate sub query are not locked.
Here is an example. First preparing tables:
<PRE>
drop table bar;
drop table foo;
create table foo as select column_value i from table(sys.odcinumberlist(1,2,3,4));
alter table foo add constraint foo_pk primary key (i);
create table bar as select i,i j from foo;
alter table bar add constraint bar_pk primary key(j);
alter table bar add constraint bar_foo_fk foreign key(i) references foo;
</PRE>
Selecting for update. The foo table used in exists predicate is not locked.
<PRE>
select j from bar where j = 1 and exists (select 0 from foo where foo.i=bar.i) for update;
--only the row in BAR table is locked
select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');
--Row-X (SX) BAR
rollback;
</PRE>
When joining is used, also the joined row in FOO table is locked
<PRE>
select bar.j from bar inner join foo on foo.i=bar.i where j = 1 for update;
select l.mode_held,(select object_name from dba_objects o where object_id = l.lock_id1) obj from dba_locks l where mode_held like 'Row%' and session_id = SYS_CONTEXT('USERENV', 'SID');
--Row-X (SX) FOO
--Row-X (SX) BAR
rollback;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-16336259336138436082019-01-09T10:30:00.001+02:002019-01-09T10:30:27.020+02:00Access Path Suggestor
Reverse engineering an existing schema or creating a new sql query. Join access paths are often following foreign keys. Here is a query that searches foreign key dependency paths between two tables.
<PRE>
with fks as(
select pk.owner o1, pk.table_name t1, fk.constraint_name, fk.owner o2, fk.table_name t2
from all_constraints fk, all_constraints pk
where fk.r_owner= pk.owner
and fk.r_constraint_name = pk.constraint_name
and fk.constraint_type = 'R'
), pths as (
select o1,t1,constraint_name,o2,t2,'-<' dir
from fks
union all
select o2,t2,constraint_name,o1,t1,'>-' dir
from fks
), rcte(o1,t1,constraint_name,o2,t2,dir,lvl,pth) as (
select o1,t1,constraint_name,o2,t2,dir,1,o1||'.'||t1||dir||constraint_name||'-'||o2||'.'||t2
from pths
where o1 = :owner1
and t1 = :table1
union all
select s.o1,s.t1,s.constraint_name,s.o2,s.t2
, s.dir,lvl+1
, prio.pth||s.dir||case when s.constraint_name not like 'SYS\_%' escape '\' then s.constraint_name||'-' end||s.o2||'.'||s.t2
from rcte prio inner join pths s on prio.o2=s.o1 and prio.t2=s.t1 and prio.constraint_name != s.constraint_name
where prio.lvl < 7
) cycle o1,t1 set cycle to 1 default 0
select lvl,cycle,pth
from rcte
where o2 = :owner2
and t2 = :table2
order by lvl,pth
;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-35235634988727812842018-10-23T15:24:00.002+03:002018-10-23T15:24:32.604+03:00Finding shortest paths in lenght from a hierarchyA slight modification to my previous <a href="https://rafudb.blogspot.com/2018/03/finding-shortest-path-in-lenght-from.html">post</a>. This way it is possible to find shortest paths from all start nodes in a graph to a specified end point.
<PRE>
with tre as (
select 0 parent, 1 child, 1 len from dual union all
select 1 parent, 2 child, 2 len from dual union all
select 2 parent, 3 child, 3 len from dual union all
select 3 parent, 1 child, 4 len from dual union all
select 3 parent, 4 child, 5 len from dual union all
select 4 parent, 5 child, 6 len from dual union all
select 5 parent, 6 child, 7 len from dual union all
select 2 parent, 5 child, 20 len from dual
), rcte( root, parent, child, len, lvl, foundpth, pthlen, minpthlen, pth) as (
select t.parent
, t.parent
, t.child
, t.len
, 1 lvl
, case when t.child = :endnode then 1 else 0 end foundpth
, t.len pthlen
, case when t.child = :endnode then t.len else 9999999 end minpthlen
, t.parent||'-'||t.child pth
from tre t
where t.parent != :endnode
union all
select r.root
, t.parent
, t.child
, t.len
, r.lvl+1 lvl
, case when t.child = :endnode then 1 else 0 end
, r.pthlen+t.len pthlen
, min(case when t.child = :endnode then r.pthlen+t.len else 9999999 end)over(partition by r.root) minpthlen
, r.pth||'-'||t.child pth
from tre t, rcte r
where t.parent = r.child
and r.pthlen + t.len < r.minpthlen
) search breadth first by parent,child set ordr
cycle parent,child set cycle to 1 default 0
, pths as (
select root,child,lvl,pthlen,pth,cycle,foundpth,row_number()over(partition by root order by pthlen, lvl, pth) rn
from rcte
where foundpth = 1
)
select *
from pths
where rn=1
order by root,pthlen, lvl, pth
;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-30869340637275946852018-03-08T14:16:00.001+02:002018-10-23T15:20:06.267+03:00Finding shortest path in lenght from a hierarchy
Using an analytical function to find out when to stop browsing a hierarchy. Here is an example without further explanation what is happening.
<PRE>
with tre as (
select 0 parent, 1 child, 1 len from dual union all
select 1 parent, 2 child, 2 len from dual union all
select 2 parent, 3 child, 3 len from dual union all
select 3 parent, 1 child, 4 len from dual union all
select 3 parent, 4 child, 5 len from dual union all
select 4 parent, 5 child, 6 len from dual union all
select 5 parent, 6 child, 7 len from dual union all
select 2 parent, 5 child, 20 len from dual
), rcte( root, parent, child, len, lvl, foundpth, pthlen, minpthlen, pth) as (
select t.parent
, t.parent
, t.child
, t.len
, 1 lvl
, case when t.child = :endnode then 1 else 0 end foundpth
, t.len pthlen
, case when t.child = :endnode then t.len else 9999999 end minpthlen
, t.parent||'-'||t.child pth
from tre t
where t.parent = :startnode
and t.parent != :endnode
union all
select r.root
, t.parent
, t.child
, t.len
, r.lvl+1 lvl
, case when t.child = :endnode then 1 else 0 end
, r.pthlen+t.len pthlen
, min(case when t.child = :endnode then r.pthlen+t.len else 9999999 end)over() minpthlen
, r.pth||'-'||t.child pth
from tre t, rcte r
where t.parent = r.child
and r.pthlen + t.len < r.minpthlen
) search breadth first by parent,child set ordr
cycle parent,child set cycle to 1 default 0
select *
from rcte
where foundpth = 1
order by minpthlen, lvl, pth
fetch first row only
;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-34715058595849299122017-10-18T11:14:00.001+03:002017-10-18T11:14:04.411+03:00A slow SQL using TEMP, when and how much.A really slow SQL clause using a lot of temp space. How much and when? Here is a query that reports hourly maximum usage of temp from ASH.
<PRE>
select dy
, hr
, coalesce(tmp,0) tmp
, coalesce(tm,' ') tm
from (
select trunc(sample_time,'hh24') samplehr
, max(tempgb) tmp
, rpad(' ',max(tempgb)/mx*80,'*') tm
from (
select sample_time
, tempgb
, max(tempgb)over() mx
from (
select sample_time
, sample_id
, trunc(sum(temp_space_allocated)/1024/1024/104) tempgb
from dba_hist_active_sess_history
where sql_id like coalesce(:sql_id,'%')
group by sample_time,sample_id
)
)
group by trunc(sample_time,'hh24')
, mx
) tmps
right outer join (
select hr - level/24 hr
, to_char(hr - level/24,'dy','NLS_DATE_LANGUAGE = AMERICAN') dy
from (
select trunc(min(sample_time),'hh24') mi
, trunc(sysdate,'hh24') hr
from dba_hist_active_sess_history
) connect by level < (hr - mi)*24
) hours
on hours.hr=tmps.samplehr
order by hr desc
;
</PRE>
If you need more detailed information just browse the views as <a href="http://rafudb.blogspot.fi/2009/12/sql-tuning-reading-plans-and-temp-usage.html">I have done earlier</a>.Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-64055680035692968222017-08-22T14:20:00.003+03:002017-09-01T09:44:15.054+03:00Tables that a query is touching
Here is a query that tells tables that a query is using. Give a sql_id as a parameter.
<PRE>
select distinct t.owner, t.table_name, t.degree, t.num_rows, t.last_analyzed, t.partitioned
from v$sql_plan p, dba_tables t
where p.sql_id = :sql_id
and (p.object_owner,p.object_name) in (
select t.owner,t.table_name
from dual
union all
select owner,index_name
from dba_indexes i
where i.table_owner = t.owner
and i.table_name = t.table_name )
order by t.owner, t.table_name
;
</PRE>
<P>
Earlier I have posted SQL queries to find
<BR>
-Tables that a view or a procedure is using: <a href="http://rafudb.blogspot.fi/2013/04/tables-used.html">http://rafudb.blogspot.fi/2013/04/tables-used.html</a>
<BR>
-Information about queries that have been touching a table. Diagnostics pack needed <a href="http://rafudb.blogspot.fi/2014/11/ash-mining-slow-queries.html">http://rafudb.blogspot.fi/2014/11/ash-mining-slow-queries.html</a>
<P>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-48895362367926754332017-06-27T16:53:00.000+03:002017-06-27T16:59:36.696+03:00Passing a PL/SQL Boolean Parameter in a SQL clause
Oracle SQL does not have a boolean data type. Here is an example how to pass a PL/SQL boolean parameter to a function in a SQL statement. This is using 12c version ability to declare a function in with part of a query.
<PRE>
rollback;
with function begin_transaction return varchar2 is
begin
dbms_lock.sleep(2);
return dbms_transaction.LOCAL_TRANSACTION_ID(TRUE);
end;
select systimestamp beforetime
, dbms_transaction.LOCAL_TRANSACTION_ID not_in_a_transaction
, begin_transaction
, dbms_transaction.LOCAL_TRANSACTION_ID inside_a_transaction
, systimestamp aftertime
from dual
;
beforetime not_in_a_transaction begin_transaction inside_a_transaction aftertime
27.06.2017 16:44:23,134 (null) 5.24.3524 5.24.3524 27.06.2017 16:44:23,134
</PRE>
SQL function systimestamp is returning consistent results inside a cursor. Columns beforetime and aftertime returns the same time allthou pl/sql function call to declared begin_transaction is coded to take two seconds in between.
PL/SQL function calls inside a SQL clause have some order in which they are executed as one can see form this example results.
The first call of dbms_transaction.LOCAL_TRANSACTION_ID for not_in_a_transaction returns null.
Second call for dbms_transaction.LOCAL_TRANSACTION_ID function starts an transaction as it gets TRUE parameter.
The third call returns the transaction id in inside_a_transaction.Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-71448743263361488952017-06-22T11:03:00.002+03:002017-06-22T11:03:59.025+03:00Truncate table partition cascade
Trying to truncate partitions on a parent table that has a reference partitioned child table. Both have global indexes supporting primary keys. So during truncate also update indexes clause needs to be used. Using the documented syntax we hit ORA-14126 error. Here is an example and correction to the situation.
<PRE>
drop table c purge;
drop table p purge;
create table p(a int constraint a_pk primary key, b int) partition by list(b)(partition p1 values(1),partition p2 values (2));
create table c(b int constraint b_pk primary key, a not null constraint c_p_fk references p on delete cascade) partition by reference(c_p_fk);
insert into p values(1,1);
insert into p values(2,2);
insert into c values(1,1);
insert into c values(2,2);
commit;
truncate table p drop storage cascade;
alter table p truncate partition p1 drop storage update global indexes cascade;
ORA-14126: only a <parallel clause> may follow description(s) of resulting partitions
14126. 00000 - "only a <parallel clause> may follow description(s) of resulting partitions"
*Cause: Descriptions of partition(s) resulting from splitting of a
table or index partition may be followed by an optional
<parallel clause> which applies to the entire statement and
which, in turn, may not be followed by any other clause.
*Action: Ensure that all partition attributes appear within the
parenthesized list of descriptions of resulting partitions in
ALTER TABLE/INDEX SPLIT PARTITION statement.
</PRE>
So the documented syntax is not working
<a href="http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#i2131210">http://docs.oracle.com/database/121/SQLRF/statements_3001.htm#i2131210</a>
and <a href="https://docs.oracle.com/database/121/VLDBG/GUID-92748418-FB88-4A41-9CEF-E44D2D9A6464.htm">https://docs.oracle.com/database/121/VLDBG/GUID-92748418-FB88-4A41-9CEF-E44D2D9A6464.htm</a>
<P>
The working place for cascade word is before update indexes clause.
<PRE>
alter table p truncate partition p1 drop storage cascade update global indexes;
</PRE>
Submitted a documentation bug today.
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-64197946972389550732017-06-20T13:30:00.002+03:002017-06-22T11:09:23.067+03:00Visualizing slow sql executionA slow sql execution vanished from sql monitor. Take a look from awr. It was almost six hours of execution time. Which sql plan lines was the execution spending its time?
<PRE>
select sql_plan_line_id
, cnt
, mi
, mx
, trim(rpad(' ',((aa-sta)/nullif(aa,0))*len,'-'))
|| trim(rpad(' ',((sta-sto)/nullif(aa,0))*len,'*'))
|| trim(rpad(' ',(sto/nullif(aa,0))*len,'-')) t
from (
select 80 len
, sql_exec_start
, sql_plan_line_id
, cnt
, to_char(mis,'hh24:mi:ss') mi
, to_char(mxs,'hh24:mi:ss') mx
, max(mxs)over(partition by sql_exec_start)-min(mis)over(partition by sql_exec_start) dur
, max(cast(mxs as date))over(partition by sql_exec_start)-min(cast(mis as date))over(partition by sql_exec_start) aa
, max(cast(mxs as date))over(partition by sql_exec_start)-cast(mis as date) sta
, max(cast(mxs as date))over(partition by sql_exec_start)-cast(mxs as date) sto
from (
select sql_exec_start
, sql_plan_line_id
, count(*) cnt
, min(sample_time) mis
, max(sample_time) mxs
from dba_hist_active_sess_history
where sql_plan_hash_value = 917708421
and sql_id = 'cnf5jz56h4swp'
and trunc(sample_time) = date'2017-01-20'
group by sql_exec_start,sql_plan_line_id
)
)
order by sql_exec_start,sql_plan_line_id
;
</PRE>
<PRE>
PLANLINEID CNT MI MX T
1 2 16:21:42 16:23:02 ----------------------------------------------------------------------------
3 155 09:18:25 16:22:52 --**************************************************************************
4 8 11:37:38 16:11:21 ---------------------------************************************************-
5 1 13:03:45 13:03:45 ----------------------------------------------------------------------------
8 16 09:00:34 14:38:03 ***********************************************************-----------------
11 2 09:00:24 09:00:54 ----------------------------------------------------------------------------
12 4 09:01:04 09:01:34 ----------------------------------------------------------------------------
13 1 10:03:09 10:03:09 ----------------------------------------------------------------------------
14 36 09:11:15 14:47:54 ***********************************************************-----------------
15 697 09:02:54 14:48:24 *************************************************************---------------
16 469 09:01:44 14:48:44 *************************************************************---------------
17 2 12:43:53 14:40:23 ---------------------------------------********************-----------------
18 27 09:50:58 14:37:53 --------**************************************************------------------
19 557 09:04:24 14:48:34 *************************************************************---------------
20 235 09:02:14 14:45:54 *************************************************************---------------
21 29 09:45:38 14:42:23 -------****************************************************-----------------
22 345 14:50:24 16:20:32 --------------------------------------------------------------**************
24 10 14:48:54 14:50:44 ----------------------------------------------------------------------------
25 1 14:49:44 14:49:44 ----------------------------------------------------------------------------
26 15 14:57:55 15:05:25 ----------------------------------------------------------------------------
27 2 14:50:54 14:51:04 ----------------------------------------------------------------------------
28 1 14:57:35 14:57:35 ----------------------------------------------------------------------------
29 1 14:54:04 14:54:04 ----------------------------------------------------------------------------
30 12 14:51:54 14:53:54 ----------------------------------------------------------------------------
31 5 14:51:14 14:52:14 ----------------------------------------------------------------------------
32 11 14:55:35 14:57:25 ----------------------------------------------------------------------------
34 7 14:54:35 14:55:45 ----------------------------------------------------------------------------
35 1 14:54:14 14:54:14 ----------------------------------------------------------------------------
36 1 14:54:25 14:54:25 ----------------------------------------------------------------------------
</PRE>Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-60400171213278227092016-12-01T15:49:00.000+02:002016-12-01T15:49:22.473+02:00Plan Shaping and Cardinality Miss Estimate on row_number over partition by
Recalling talks with <a href="https://oracle-base.com/">Tim Hall</a> I wrote in my <a href="http://rafudb.blogspot.fi/2016/11/trip-to-bgoug-conference-pravets-10.html">last post</a>. He mentionend <a href="https://jonathanlewis.wordpress.com/">Jonathan Lewis</a> telling about plan shaping.
Every now and then there comes a need to tell the optimizer where the query execution should be starting.
Write your query from part in order and use ordered hint or most often leading hint will be enough.
Hitting the need for such plan shaping comes when the optimizer sees inline views in a query returning only one row and actually there are more in execution time.
If there are several missleading one liners and a cartesian join between those a quite simple query can consume significant query time.
12.1.0.2 database has a anoying bug involving top-n queries with analytic partition by part. Luckily a patch for the 21971099 bug is available for some environments.
Here is a simplified test getting cardinality one. Imagine having a couple of such in your bigger query.
<PRE>
create table s as (
select round(level/2) n
, level n2
from dual connect by level < 10e4
)
;
select /*+gather_plan_statistics*/ n
, n2
from (
select n
, n2
, row_number()over(partition by n order by n2 desc) rn
from s)
where rn <= 1
;
select *
from table(dbms_xplan.display_cursor(format=>'iostats last'))
;
Plan hash value: 2407482549
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50000 |00:00:00.12 | 208 |
|* 1 | VIEW | | 1 | <B>1</B> | 50000 |00:00:00.12 | 208 |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 99999 | 50000 |00:00:00.11 | 208 |
| 3 | TABLE ACCESS FULL | S | 1 | 99999 | 99999 |00:00:00.02 | 208 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=1)
2 - filter(ROW_NUMBER() OVER ( PARTITION BY "N" ORDER BY
INTERNAL_FUNCTION("N2") DESC )<=1)
</PRE>
While waiting for the patching to happen, an alternative to bypass the problem is not to use row_number analytic function. By using rank and order by part that won't return competing winners, we get another cardinality estimate. It is as much wrong as the earlier one, but most likely will help with the cartesian join problem.
<PRE>
select /*+gather_plan_statistics*/ n
, n2
from (
select n
, n2
, rank()over(partition by n order by n2 desc, rowid) rn
from s)
where rn <= 1
;
select *
from table(dbms_xplan.display_cursor(format=>'iostats last'))
;
Plan hash value: 2407482549
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 50000 |00:00:00.13 | 208 |
|* 1 | VIEW | | 1 | <B>99999</B> | 50000 |00:00:00.13 | 208 |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 99999 | 50000 |00:00:00.11 | 208 |
| 3 | TABLE ACCESS FULL | S | 1 | 99999 | 99999 |00:00:00.02 | 208 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=1)
2 - filter(RANK() OVER ( PARTITION BY "N" ORDER BY INTERNAL_FUNCTION("N2") DESC
,ROWID)<=1)
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com1tag:blogger.com,1999:blog-1286598839924995997.post-65341052669369399822016-11-15T10:52:00.000+02:002016-11-15T13:55:38.311+02:00Trip to BGOUG Conference Pravets 10.-13.11.2016It was a day in this autumn. I had put a marker to my calendar. <A href="http://www.bgoug.org/en/events/details/98.html">Bulgarian Oracle User Group conference 2016</A> call for papers deadline was that day. <A href="https://twitter.com/toonkoppelaars">Toon Koppelaars</A> will be there. I could talk about ideas behind SQL Assertion implementation. Vote <A href="https://community.oracle.com/ideas/13028"> here</A> if you have not yet voted yet. 5000 votes already. I did not know how could I fit the trip to my calendar. I did not know it even then but decided to submit. My presentation "Hub Insert ORA-00001" got accepted. And after a while I started booking flights to Sofia. There was a possibility to choose such flight that I did not have to start my journey too early in the morning and I would get in time to reach the speakers dinner.
<P>
While preparing my slides I noticed that my abstract I promised to talk about parallelism. Concurrency issues was the ones I was prepared for. Well just need to add content to the presentation. Parallelism on top of SQL calls cause concurrency issues and parallelism under a SQL clause should speed up the execution. Had to figure out a way to fit a parallel SQL execution example to talk about in some minutes. This is the time when you learn and find out new stuff. While gathering information you learn new stuff. This time one thing to unlearn from 11G days was how 12c parallel dml is able to populate same segments from different processes. Further reading <A href="https://blogs.oracle.com/optimizer/entry/space_management_and_oracle_direct">in a oracle optimizer blog post</A>.
<P>
The morning of leaving from home came. My first flight to München will be delayed. The latter flight had to be moved to evening flight. I will miss the speakers dinner. Arriving to Sofia 10pm. If I had arrived with my planned flight, conference organizer transportation would have been available. Had to take a taxi. I choose the yellow official cab company next to the station. Not a pleasant journey to Pravets. Taxi driver stopping three times. Once on a dark motorway side about should I pay 100EUR to the driver that he would continue the trip. I gave some money and the rest of the 60km journey continued. Meter was not running anymore. Arrived to the hotel. Somehow I was not sleepy even thou travelling 15 hours already. Went for an one hour walk around the hotel.
<P>
Morning walk around a lake wearing short pants. While I left home there was five centimeters snow and -8 celsius cold. Seminar registration, breakfast and on with the show.
I got to see good talks. <a href="https://twitter.com/juliandontcheff">Julian Dontcheff</a> tweaking his laptop virtual machine database super_fast "with a parameter". Toon explaining thickdb approach. Flame graphs explained. Network speed and layers influence performance. Somethings about Oracle in the Cloud. Pluggable databases used in development cycle and also what will they brake. Surprisingly not so much fuss about in-memory option. Full rooms for some sessions. My session had plenty of space. Thanks for those who attended. Hopefully you got something out of it.
<P>
In addition to the talks I heard one major reason to attend these kind of seminars is to meet people and get to talk about issues. This conference had a well planned schedule for that. At least 15 minutes pause in between each session. Here are some.
<P>
Even thou I did not get to see OBIEE presentations by <a href="https://twitter.com/G_Ceresa">Gianni Ceresa</a> or <a href="https://twitter.com/Nephentur">Christian Berg</a> it was nice meeting you.
<P>
I gave some minor feedback to <a href="https://twitter.com/MDWidlake">Martin Widlake</a> about hist talks how Oracle works animations.
<P>
<a href="https://twitter.com/MichalSimonik">Michal Å imonÃk</a> "join talk" got a slide about qube join.
<P>
Straight from Nigel Bayliss, Optimizer Product Manager I got encouragement to my suspicion that my struggling with OLTP system parse times will have some other issues also than the known 12c adaptivity problems. Seems like we found something just yesterday a day after the seminar. Maybe a place to another blog post.
<P>
I did not get to talk with Toon about SQL assertions. But at the Sunday breakfast changed some words with <A href="https://twitter.com/BrynLite">Bryn Llewellyn</A>. The vote page is not promising assertion word to be implemented. Will there be some more weight to parsing time? And as those men are influencing the implementation will it be supported at first phases behind the scenes of thickdb approach. We will see. Hopefully sooner than later. So vote.
<P>
There was talks also with <a href="https://twitter.com/ChandlerDBA">Neil Chandler</a> and many others.
<P>
After the presentations and before dinner time I had also time to go to a Finnish Sauna and watch ice hockey. Tappara winning two matches. The flight home was more enjoyable than going there as I had got business class tickets at the same price than economy tickets would have cost. I was quite relaxed. I read almost a full book by Miika Nousiainen that I purchased for the trip.
<P>
On a way home nice talks with <a href="https://twitter.com/oraclebase">Tim Hall</a> and Gianni while waiting at airport.
<P>
Thank you <a href="https://twitter.com/milenagerova">Milena Gerova</a> and BGOUG staff for letting me to be a speaker in your event. This was the first time I did a presentation in foreign user group other than <a href="http://www.ougf.fi/">Ougf</a>.Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-88404176423738158652015-10-12T10:17:00.000+03:002017-04-05T10:37:57.609+03:00Installing Oracle 12.1.0.2 on Red Hat 7
This is not a complete guide how to do the installation. Just a note how to get a RPM-GPG-KEY-oracle file to Red Hat 7. Go to <a href="https://oracle-base.com/articles/12c/oracle-db-12cr1-installation-on-oracle-linux-7">oracle-base</a> for installation notes on oel7.
<P>
<a href="http://docs.oracle.com/database/121/LADBI/pre_install.htm#LADBI80757">Installation documentation</a> is mentioning:
"Starting with Oracle Database 12c Release 1 (12.1.0.2), Oracle Linux 7 and Red Hat Enterprise Linux 7 are supported on Linux x86-64 systems." So do not try to install 12.1.0.1 on Red Hat 7. The case when you have SE one licence and not yet buying SE2 license.
Just to mention 11.2 is supported to be installed on Red Hat Enterprise Linux 7.
<P>
Download the information about Oracle public yum repo <a href="https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-downloading-yum-repo.html">https://docs.oracle.com/cd/E52668_01/E54669/html/ol7-downloading-yum-repo.html</a>
<PRE>
cd /etc/yum.repos.d
wget http://public-yum.oracle.com/public-yum-ol7.repo
</PRE>
Try to install preinstall package and get an error.
<PRE>
yum install oracle-rdbms-server-12cR1-preinstall
</PRE>
...
<PRE>
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
GPG key retrieval failed: [Errno 14] curl#37 - "Couldn't open file /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle"
</PRE>
You need the file mentioned here <a href="https://docs.oracle.com/cd/E37670_01/E39381/html/ol_import_gpg.html">https://docs.oracle.com/cd/E37670_01/E39381/html/ol_import_gpg.html</a>
<PRE>
wget http://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol7 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle
</PRE>
And you will succeed installing:
<PRE>
yum install oracle-rdbms-server-12cR1-preinstall
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com4tag:blogger.com,1999:blog-1286598839924995997.post-50357519850128510392015-07-24T11:47:00.003+03:002015-07-24T11:47:40.466+03:00SUM over time -ElementsA while ago wrote how to calculate <a href="http://rafudb.blogspot.fi/2009/04/sum-over-time.html">sum over time</a>. During overlapping time intervals the resulting sum is produced there. But how about if one needs the elements that the sum is generated from?
<PRE>
2015-07-25 2015-07-26 1
2015-07-26 2015-07-28 2
2015-07-27 2015-07-30 3
</PRE>
The testdata is generated in td view.
<PRE>
with td as (
select trunc(sysdate) + mod(level, 4) validFrom
, trunc(sysdate) + mod(level, 8) * 2 validTill
, level a
from dual
connect by level < 4
), b as (
select row_number()over(order by case r when 1 then validFrom else validTill end,r desc) rn
, a
, r
, case r when 1 then validFrom else validTill end validFrom
, sum(case r when 1 then a else -a end) over (order by case r when 1 then validFrom else validTill end,r desc) sumover
, sum(case r when 1 then 1 else -1 end) over (order by case r when 1 then validFrom else validTill end,r desc) cntover
from td, (
select rownum r from dual connect by level < 3
)
),c (rn,a,r,validfrom,sumover,cntover,vals) as (
select rn,a,r,validfrom,sumover,cntover,mdsys.SDO_NUMTAB(a) from b where rn = 1
union all
select b.rn,b.a,b.r,b.validFrom,b.sumover,b.cntover,case b.r when 1 then c.vals multiset union mdsys.SDO_NUMTAB(b.a) else c.vals multiset except mdsys.SDO_NUMTAB(b.a) end
from b,c where b.rn = c.rn+1
),d as (select max(cntover)over(partition by validFrom) mx,a,r,validFrom,sumover,cntover,(select count(*) from table(vals)) n,vals, count(*)over(partition by validFrom) cn
from c
where cntover=(select count(*) from table(vals))
)
select validFrom
, nvl(lead(validFrom) over (order by validFrom)
, to_date('22000101','yyyymmdd')) validTill
, sumover
, (select listagg(column_value,'+')within group(order by column_value) from table(vals)) vals
from d
where cntover=mx
order by validFrom;
2015-07-25 2015-07-26 1 1
2015-07-26 2015-07-27 2 2
2015-07-27 2015-07-28 5 2+3
2015-07-28 2015-07-30 3 3
2015-07-30 2200-01-01 0
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-90641974718024245922014-11-11T16:34:00.000+02:002014-11-11T16:34:36.830+02:00ASH Mining Slow Queries
"Database is slow." It was fast three days ago. We know the problem table but no queries are informed to the dba. Luckily we have diagnostics pack purchased. Time to start finding the slowest queries touching the table.
<PRE>
with sqid as (
select /*+materialize*/
distinct sql_id
from dba_hist_sql_plan p, dba_tables t
where t.owner = :table_owner
and t.table_name = :table_name
and (p.object_owner,p.object_name) in (
select t.owner,t.table_name
from dual
union all
select owner,index_name
from dba_indexes i
where i.table_owner = t.owner
and i.table_name = t.table_name
)
)
select /*+leading(s)*/
trunc(h.sample_time) dt
, h.sql_id
, max(h.sample_time-h.sql_exec_start) dur
, min(h.sample_time) mins
, max(h.sample_time) maxs
, count(distinct h.sql_plan_hash_value) cntpln
, collect(distinct h.sql_plan_hash_value) plns
, count(distinct h.sql_exec_id) cntexec
, count(distinct h.session_id) cntsess
, collect(distinct h.event) events
, (select dbms_lob.substr(t.sql_text,2000)
from dba_hist_sqltext t
where t.sql_id = h.sql_id) txt
from dba_hist_active_sess_history h, sqid s
where sample_time > trunc(sysdate)-7
and h.sql_id = s.sql_id
group by trunc(h.sample_time),h.sql_id
order by max(dur)over(partition by h.sql_id) desc, dt desc
;
</PRE>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-49949196002817605382014-08-29T16:03:00.001+03:002017-06-07T13:51:40.519+03:00DBMS_STATS.GET_PREFS for a Table
<pre>
select t.owner
, t.table_name
, p.column_value pname
, dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) pvalue
, case when dbms_stats.get_prefs(p.column_value,t.owner,t.table_name) != dbms_stats.get_prefs(p.column_value) then dbms_stats.get_prefs(p.column_value) end pvaluedefault
from dba_tables t,
table(sys.odcivarchar2list('AUTOSTATS_TARGET','CASCADE','DEGREE','ESTIMATE_PERCENT','METHOD_OPT','NO_INVALIDATE','GRANULARITY','PUBLISH','INCREMENTAL','INCREMENTAL_STALENESS','INCREMENTAL_LEVEL','STALE_PERCENT','TABLE_CACHED_BLOCKS','OPTIONS')) p
where owner = :owner
and table_name = :table_name
;
RAFU P AUTOSTATS_TARGET AUTO
RAFU P CASCADE DBMS_STATS.AUTO_CASCADE
RAFU P DEGREE NULL
RAFU P ESTIMATE_PERCENT DBMS_STATS.AUTO_SAMPLE_SIZE
RAFU P METHOD_OPT FOR ALL COLUMNS SIZE AUTO
RAFU P NO_INVALIDATE DBMS_STATS.AUTO_INVALIDATE
RAFU P GRANULARITY AUTO
RAFU P PUBLISH TRUE
RAFU P INCREMENTAL TRUE FALSE
RAFU P STALE_PERCENT 10
</pre>Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-43844063984889786492014-04-11T10:35:00.001+03:002019-05-22T14:31:47.523+03:00Gouping consecutive datesI just read about Tabibitosan method on <a href="https://community.oracle.com/message/3991177">Oracle PL/SQL forum</a>. <br />
<a href="http://rwijk.blogspot.fi/2014/01/tabibitosan.html">Rob Van Wijk</a> has written also about the method recently.<br />
<br />
Here is just another example of it.<br />
Something happening on random times. Need to know sequences of dates. Need to get a grouping information on rows, if there exist rows on yesterday or tomorrow.<br />
<br />
<span style="font-family: Courier New, Courier, monospace;">with dates as (</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-15 01:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-15 02:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-15 03:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-16 01:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-17 02:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-17 03:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-19 01:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-19 02:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-21 02:00:00' dt from dual union all</span><br />
<span style="font-family: Courier New, Courier, monospace;">select TIMESTAMP'2014-04-21 03:00:00' dt from dual</span><br />
<span style="font-family: Courier New, Courier, monospace;">)</span><br />
<span style="font-family: Courier New, Courier, monospace;">select dt,TRUNC(dt)-DENSE_RANK()over(order by trunc(dt)) grp</span><br />
<span style="font-family: Courier New, Courier, monospace;">from dates</span><br />
<span style="font-family: Courier New, Courier, monospace;">;</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">2014-04-15 01:00:00 2014-04-14</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-15 02:00:00 2014-04-14</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-15 03:00:00 2014-04-14</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-16 01:00:00 2014-04-14</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-17 02:00:00 2014-04-14</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-17 03:00:00 2014-04-14</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-19 01:00:00 2014-04-15</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-19 02:00:00 2014-04-15</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-21 02:00:00 2014-04-16</span><br />
<span style="font-family: Courier New, Courier, monospace;">2014-04-21 03:00:00 2014-04-16</span><br />
<div>
<br /></div>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0tag:blogger.com,1999:blog-1286598839924995997.post-80616475345643975542014-03-07T17:27:00.002+02:002014-03-07T17:31:12.000+02:00Variable in-list cardinality<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Collect the statistics in <a href="http://rafudb.blogspot.fi/2011/10/variable-inlist.html">my earlier varying in-list</a> example inlist_test table and you might hit the following problem.</span><br />
<br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">select * from table(SYS.odcivarchar2list('a','b'));</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">select * from table(SYS.odcinumberlist(1,2,3));</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">----------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">----------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 1 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 8168 | 16336 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">----------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: inherit;">The default 8168 cardinality is the root cause for some performance problems. <a href="http://www.oracle-developer.net/display.php?id=427">Adrian Billington to the rescue</a>. </span><br />
<span style="font-family: Courier New, Courier, monospace;">"</span><span style="background-color: white; font-family: Arial, Helvetica, sans-serif; font-size: 11px; line-height: 18px; text-align: justify;">The Extensible Optimiser feature is the most flexible method to use at this stage and is usable in all versions of 10g. Using this, we have devised a good alternative to the CARDINALITY hint for pipelined functions and also created a generic wrapper for small collections that are typically used in variable in-list queries."</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
Putting the different lists to a collection wrapper package.<br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">CREATE OR REPLACE package coll</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> as </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> FUNCTION wrapper(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcinumberlist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN SYS.odcinumberlist;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> FUNCTION wrapper(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcivarchar2list</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN SYS.odcivarchar2list;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> FUNCTION wrapper(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcidatelist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN SYS.odcidatelist;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">END coll;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">/</span><br />
<span style="font-size: xx-small;"><span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">CREATE OR REPLACE package body coll</span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> as </span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> FUNCTION wrapper(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcinumberlist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN SYS.odcinumberlist as</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> BEGIN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> RETURN p_collection;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END wrapper;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> FUNCTION wrapper(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcivarchar2list</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN SYS.odcivarchar2list as</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> BEGIN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> RETURN p_collection;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END wrapper;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> FUNCTION wrapper(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcidatelist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN SYS.odcidatelist as</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> BEGIN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> RETURN p_collection;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END wrapper;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">END;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">/</span><br />
<span style="font-size: xx-small;"><span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">CREATE OR REPLACE TYPE coll_wrapper_ot AS OBJECT (</span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> dummy_attribute NUMBER,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIGetInterfaces (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_interfaces OUT SYS.ODCIObjectList</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIStatsTableFunction (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_function IN SYS.ODCIFuncInfo,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats OUT SYS.ODCITabFuncStats,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_args IN SYS.ODCIArgDescList,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcinumberlist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIStatsTableFunction (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_function IN SYS.ODCIFuncInfo,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats OUT SYS.ODCITabFuncStats,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_args IN SYS.ODCIArgDescList,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcivarchar2list</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIStatsTableFunction (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_function IN SYS.ODCIFuncInfo,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats OUT SYS.ODCITabFuncStats,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_args IN SYS.ODCIArgDescList,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcidatelist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> );</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">/</span><br />
<span style="font-size: xx-small;"><span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">CREATE OR REPLACE TYPE BODY coll_wrapper_ot AS</span></span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIGetInterfaces (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_interfaces OUT SYS.ODCIObjectList</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER IS</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> BEGIN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_interfaces := SYS.ODCIObjectList(</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> SYS.ODCIObject ('SYS', 'ODCISTATS2')</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> );</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> RETURN ODCIConst.success;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END ODCIGetInterfaces;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIStatsTableFunction (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_function IN SYS.ODCIFuncInfo,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats OUT SYS.ODCITabFuncStats,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_args IN SYS.ODCIArgDescList,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcinumberlist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER IS</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> BEGIN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> RETURN ODCIConst.success;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END ODCIStatsTableFunction;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIStatsTableFunction (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_function IN SYS.ODCIFuncInfo,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats OUT SYS.ODCITabFuncStats,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_args IN SYS.ODCIArgDescList,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcivarchar2list</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER IS</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> BEGIN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> RETURN ODCIConst.success;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END ODCIStatsTableFunction;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> STATIC FUNCTION ODCIStatsTableFunction (</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_function IN SYS.ODCIFuncInfo,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats OUT SYS.ODCITabFuncStats,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_args IN SYS.ODCIArgDescList,</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_collection IN SYS.odcidatelist</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> ) RETURN NUMBER IS</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> BEGIN</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> p_stats := SYS.ODCITabFuncStats(p_collection.COUNT);</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> RETURN ODCIConst.success;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END ODCIStatsTableFunction;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;"> END;</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">/</span><br />
<span style="font-size: xx-small;"><span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">--DISASSOCIATE STATISTICS FROM PACKAGES coll;</span></span><br />
<span style="font-size: xx-small;"><span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">ASSOCIATE STATISTICS WITH PACKAGES coll USING coll_wrapper_ot;</span></span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">select * from table(coll.wrapper(SYS.odcivarchar2list('a','b')));</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 0 | SELECT STATEMENT | | 2 | 4 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 1 | COLLECTION ITERATOR PICKLER FETCH| WRAPPER | 2 | 4 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">select * from table(coll.wrapper(SYS.odcinumberlist(1,2,3)));</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 0 | SELECT STATEMENT | | 3 | 6 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 1 | COLLECTION ITERATOR PICKLER FETCH| WRAPPER | 3 | 6 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">select * from table(coll.wrapper(SYS.odcidatelist(date'2014-03-07',date'2014-03-08',date'2014-03-09')));</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 0 | SELECT STATEMENT | | 3 | 6 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 1 | COLLECTION ITERATOR PICKLER FETCH| WRAPPER | 3 | 6 | 29 (0)| 00:00:01 |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">---------------------------------------------------------------------------------------------</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">Seems like just the thing desired, but how about binds?</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">declare</span><br />
<span style="font-family: Courier New, Courier, monospace;"> b sys.odcinumberlist := sys.odcinumberlist(1,2,3,4,5,6,7,8,9,1,2,4,5,7,8,9,0);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> ret number;</span><br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> select /*+monitor*/ count(*) into ret from table(coll.wrapper(b));</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_output.put_line(ret);</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<span style="font-family: Courier New, Courier, monospace;">/</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">=============================================================================================================================================</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">=============================================================================================================================================</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 2 | COLLECTION ITERATOR PICKLER FETCH | WRAPPER | 1 | 29 | 1 | +0 | 1 | 17 | | |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">=============================================================================================================================================</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: inherit;"><br /></span>
<span style="font-family: inherit;">Got unlucky. Esitmated is not 8168 but one. The second documented alternative was to use DYNAMIC_SAMPLING hint. </span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace;">declare</span><br />
<span style="font-family: Courier New, Courier, monospace;"> b sys.odcinumberlist := sys.odcinumberlist(1,2,3,4,5,6,7,8,9,1,2,4,5,7,8,9,0);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> ret number;</span><br />
<span style="font-family: Courier New, Courier, monospace;">begin</span><br />
<span style="font-family: Courier New, Courier, monospace;"> select /*+ monitor DYNAMIC_SAMPLING(2)*/ count(*) into ret from table(b);</span><br />
<span style="font-family: Courier New, Courier, monospace;"> dbms_output.put_line(ret);</span><br />
<span style="font-family: Courier New, Courier, monospace;">end;</span><br />
<span style="font-family: Courier New, Courier, monospace;">/</span><br />
<span style="font-family: Courier New, Courier, monospace;"><br /></span>
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">SQL Plan Monitoring Details (Plan Hash Value=3309076612)</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">==========================================================================================================================================</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">==========================================================================================================================================</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 1 | | |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 1 | SORT AGGREGATE | | 1 | | 1 | +0 | 1 | 1 | | |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">| 2 | COLLECTION ITERATOR PICKLER FETCH | | 17 | 11 | 1 | +0 | 1 | 17 | | |</span><br />
<span style="font-family: Courier New, Courier, monospace; font-size: xx-small;">==========================================================================================================================================</span><br />
<div>
<br /></div>
<div>
That seems to work also with the binds. My newly created inlist_test table did not have statistics gathered and so the DYNAMIC_SAMPLING is used to the statement in <a href="http://rafudb.blogspot.fi/2011/10/variable-inlist.html">varying in list</a>. And so the different plans are generated for different collection sizes.</div>
Rafuhttp://www.blogger.com/profile/09149025430133739471noreply@blogger.com0