Tables used

Having a view stack? Trying to find out tables a view is using?

 select d.referenced_name table_name, count(*) times, sum(count(*))over() tables
   from user_dependencies d 
  where d.referenced_type = 'TABLE'
  start with d.name = :name 
connect by   d.name = prior d.referenced_name 
        and  d.type = prior d.referenced_type
 group by d.referenced_name
 order by d.referenced_name
;

Not only restricted to views also eg. procedures have dependencies. Just give the root object name as a parameter to the query and you get the dependent tables out.

Counting Character Occurrences in SQL

Which characters and how many times they are used in a rows of a column. Just change following src to a desired query.

with src as (
   SELECT ename col FROM emp
)
SELECT letter
     , COUNT (*) cnt
  FROM (    
 SELECT SUBSTR (col, LEVEL, 1) letter
   FROM (
  SELECT col
       , ROWNUM rn
       , LENGTH (col) len
    FROM src
        )
 CONNECT BY LEVEL <= len
        AND rn = PRIOR rn
        AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
        )
GROUP BY letter
ORDER BY letter
;

This approach is not the most efficient way to do this. But it does the job. For a 10 million rows varchar2(30) column it took about half an hour to calculate the result. Maybe in database map reduce approach could be used to speedup the execution. Something like my euler17 implementation.

UKOUG 2012

UKOUG 2012

I am alive all thou I did not obey the first timer’s advice Health warning!
"You will not be able to fill all three conference days with presentations and come out of the other side in one piece."
I stuffed my days full of presentations.  I had time to:
* Talk to quite many people
* Walk outside at  the Christmas market - it started raining
* Visit the exhibition - many times and had good talks with many exhibitors
* There actually is someone using Oracle Workspace Manager out there somewhere, no presentations here

Attended sessions:

Oaktable Sunday 

12:15 – 13:00
Keeping it Simple in Database Application Development:  A Case Study
Melanie Caffrey, Oracle

Research to create manitainable system.
Get out of users way, do not throw errorstacks to users face.
Design should be centered around users needs.
Call your baby ugly -sometimes. The problem may be easier and more efficiently implemented using another approach than your hammer.
Simple is not allways easy.


13:05 – 13:50 Horrid Collateral Compression
Jonathan Lewis, JL Computer Consultancy

Deduplication of index compress and basic/oltp table compression.
Hybrid Column compression available in Exadata, ZFS and Pillar.
Describing the uncompress cpu usage problem while rowid access path


14:35 – 15:20 Guiding Practices for Gathering Optimiser Statistics (or Not)
Martin Widlake, ORA600

Collected during maintenance window. May be changed.
Sometimes beneficial to cather first primary key stats and other afterwards.
System, fixed object, dictionary and object stats.
Don't replace auto stats job.
Incremential stats on partitioned objects brake if even one (sub)partition stats missing or alter table add column.


15:25 – 16:10 Tuning Untouchable SQL using Profiles
Dan Fink, TZOD Consultancy

v$sql exact_matching_signature and force_matching_signature
category may be used to use profiles
alter session set sqltune_category='DEMO';


Evening ACE Dinner - Thank you Debra Lilley for arranging also this.

Monday

09:30 - 10:20
Opening Oracle Keynote
Dermot O'Kelly & Dr. Andrew Sutherland - Oracle

Whole stack. Hardware and software together. Did I hear correctly "We can put database to chipset" or was my mind wondering somewhere?
Comparing Social media to inhouse applications, structured data and processes. Social media is not an application.
It is a common service. How to handle integration in between?
Mobile access. Fusion TAP.
Big data value varies ease of tools ease of use. Oil drilling comparison.

10:30 - 11:15
KEYNOTE: Oracle's Latest Generation of Database Technology
Tom Kyte, OakTable - Oracle

12 new features next year


11:50 - 12:35
Creating Test Cases
Jonathan Lewis, Oracle ACE Director, OakTable - JL Computer Consultancy

Should have been titled Test Data Generation
How to generate rows
Distribution, physical location and index size varies depending how you generate rows
sql vs pl/sql
How to generate and run many statements
Use dbms_lock package to handle concurrency instead of a table row lock.

12:45 -

Benchmarking Oracle I/O Performance with ORION
Mr Alex Gorbachev, Oracle ACE Director, OakTable

First couple of slides. Orion comes with database binaries.



13:00 - 13:45
PL/SQL: Stop Making the Same Performance Mistakes
Tim Hall, Oracle ACE Director, OakTable - ORACLE-BASE.com

Use SQL instead
Bulk operations
Nocopy
Avoid using wrong datatypes


14:30 - 15:30
Is RAT Worth Catching?
Julian Dyke, Oracle ACE, OakTable - JulianDyke.com

RAT experiences traps
system time before replay


15:40 - 17:30
B-Tree Indexes
Jonathan Lewis, Oracle ACE Director, OakTable - JL Computer Consultancy

I attended only first ten minutes and was deilghted to see him using a book index example as I have been using allready when expaining indexing.


15:50 - 16:35
How to Create in 5 Minutes a SQL Tuning Test Case Using SQLTXPLAIN
Carlos Sierra - Oracle

What for
Investigate unexpected results
11g test case builder tcb
tc
tcx system generated column names missing use tc


16:45 - 17:30
Shareable Cursors
Christian Antognini, OakTable - Trivadis

Reason_xml information v$sql_shared_cursor sql_id and child# are not allways unique in v$sql when _cursor_obsolete_treshold reached v$sql_bind_metadata


UKOUG Exhibition drinks: 18:30 – 19:30 and SIG Focus Pubs: 19:30 – 22:30

Tuesday

Unlucky me my notes file got corrupted for tuesday


09:00 - 09:45
Are You Sure You Need Exadata?
Jonathan Lewis, Oracle ACE Director, OakTable - JL Computer Consultancy


09:55 - 10:55
Oracle Optimizer: Harnessing The Power of Optimizer Hints
Maria Colgan, OakTable - Oracle

Hints may appear not to be used


11:15 - 12:00
A Deep Dive into the SQL Monitor Report
Graham Wood - Oracle

Yes it is the best place to see ongoing problems. Only if it was available without tuning pack license.
The buffer might be a bit bigger that also long plans would stick in.
The plan page is using graphical presentation of the plan by default. Who uses such. Even Garham changed to tabular.


12:10 - 13:10
Controlling Execution Plans (Without Touching the Code)
Kerry Osborne, Oracle ACE Director, OakTable - Enkitec

About Outlines - SQL Patches - SQL Profiles - SQL Baselines
Profiles may work also with binds


13:25 - 14:10
Big Data Meets Big Process
Tristan Atkins - Microgen


14:30 - Unscheduled Oracle Optimizer roudtable with
Maria Colgan, Jonathan Lewis, Tony Hasler, Coscan Gundogar, Andy Colvin, Kerry Osborne, Cristian Antognini, Tuomas Pystynen, Jože Senegačnik and many others

Missed many other planned talks, but it was worth the sacrifice.


16:05 - 17:05
Cardinality Feedback
Peter Brink - Credit Suisse

Preferred to have good statistics


17:20 - 18:05
Oracle Exalytics - One Size Fits All?
Robin Moffatt - Rittman Mead

Timesten is a datasoure to obiee inside.
Sources may be other than oracle db
Obiee repository must be solid
Special relase of timesten for exalytics only available with exalytics
Summary advisor tool suggest most useful aggregates to timesten based on queries run
No incremental refeshes available yet
Just another batch stage in your etl
Loading through oracle staging db
or times ten may be used as a target of your etl
idea of ditching your dw
timesten may be used in cache mode not supporting materialized view sources

Wednesday

09:00 - 09:45
Capacity Planning, Simple and Without Tooling
Piet De Visser, Oracle ACE - Logica

Add a zero


9:20-9:50
Unconference
Tony Hasler

Talking about how a block corruption recovery became a sql tuning task. Looking forward for the promised blog post.
How to find high water mark


09:55 - 10:55
Oracle Optimizer: An Insider’s View of How the Oracle Optimizer Works
Maria Colgan, OakTable - Oracle

Future improvements
Adaptivity while runtime
Nested loops statistics collector has a tresholder. Reaching it changes to hash only on first execution. Format +adaptive +report will have swiching plan_hash_values during the first execution nl to hash hash to nl no additional parsing needed. All needed paths are allredy in the cursor.
Small buffering needed. Nothing is restarted.
Adapt parallel data distribution
Hybrid-hash
Change tresholds possible. That is the beauty how cost based optimizer works.
Dynamic statistics replaces dynamic sampling re-optimization new cardinality feedback used to age out when cursor ages out 12c stores that info as sql plan directives dbms_spd will create extended stats next time stats are gathered due to directives exists adaptive plans and statistics


11:15 - 12:00
The MODEL Clause Explained
Tony Hasler - Anvil Computer Services

May run parallel by model partitions


12:10 - 13:10
Really Using Analytic Functions
Kim Berg Hansen - T. Hansen Gruppen A/S

Aggregate funcions may be used mixed in analytic functions
Nice picking route and efficiency
Examples regr_slope to forecast


13:55 - 14:55
Exadata and the Oracle Optimizer: The Untold Story
Maria Colgan, OakTable - Oracle

Addressed the same issue Jonathan Lewis talked sunday and mentioned the soon to be available patch numbers to bypass the problem.
Do not gather system stats. Tested with defaults. Recomended for extremely slow hardware.
They will change the cost model too much.


No more sessions. Had a plane to catch. Originally half an hour change time in Amsterdam was a bit scary. The first plane left 15 minutes late. We made it as the other flight was one hour late.

See you hopefully next year in Manchester. Before that OUG Harmony.

Call for Papers for the OUGF 25th anniversary conference

Just arrived from UKOUG 2012. While waiting for next year in Manchester consider attending a boat trip. Submit an abstract before end of this year. The Call for Papers for the OUGF 25th anniversary conference.

More details
OUGF Spring Conference
“OUG Harmony” and 25th anniversary of OUGF
on a cruise from Helsinki (Finland) to Stockholm (Sweden)
Location: TallinkSilja cruise boat
Event starts on April 15, 2013 at 11.00 in Helsinki
Event ends on April 17th, 2013 at 10.00 in Helsinki
The boat leaves Helsinki on April 15th, 2013 at 17.00
The boat arrives to Stockholm on April 16th at 9.55
The boat leaves Stockholm on April 16th at 17.00
The boat arrives to Helsinki on April 17th at 10.00
Languages: Finnish, English
Organizer Oracle User Group Finland

Insert All and Column Naming

SQL> create table ins(i int, j int);

Table created.

SQL> insert into ins(i, j) select 1,0 from dual;

1 row created.

SQL> insert into ins(i, j) select 1,1 from dual;

1 row created.

All fine. How about insert all?

SQL> insert all into ins(i, j) select 2,0 from dual;

1 row created.

SQL> insert all into ins(i, j) select 2,1 from dual;

1 row created.

SQL> insert all into ins(i, j) select 2,2 from dual;
insert all into ins(i, j) select 2,2 from dual
                *
ERROR at line 1:
ORA-00918: column ambiguously defined

SQL> select * from ins;

         I          J
---------- ----------
         1          0
         1          1
         2          0
         2          1

Got an error about column naming. The simple insert into does not seem to require column naming. As the 1,1 value pair is inserted fine. Insert all requires different column names in the select list. 2,2 without column naming is not working without naming the columns.

SQL> select 2, 2 from dual;

         2          2
---------- ----------
         2          2

SQL> insert all into ins(i, j) select 2 foo, 2 bar from dual;

1 row created.

The column naming does not have an influence here. The placing does.

SQL> insert all into ins(i, j) select 3 j, 4 i from dual;

1 rows created.

SQL> select * from ins where j=3;

no rows selected

SQL> select * from ins where i=3;

         I          J
---------- ----------
         3          4

How about actual multitable insert?

SQL> insert all into ins(i, j) into ins(i, j) select 5 j, 6 i from dual;

2 rows created.

SQL> select * from ins where j=5;

no rows selected

SQL> select * from ins where i=5;

         I          J
---------- ----------
         5          6
         5          6

Similar behavior. Select list column naming does not have effect which column is populated. I would prefer using values with multitable insert to map the select list columns to the placeholders of inserted column names.

SQL> insert all into ins(i, j) into ins(i, j) values (i, j) select 7 j, 8 i from dual;

SQL> select * from ins where j in (7,8);

         I          J
---------- ----------
         7          8
         8          7

SQL Developer 3.2 and something about 12c

SQL Developer 3.2 available for download. Seems to include improvements to database diff and future 12c Database Support.

Could it have something to do with Cloud Connections.

Index Organized Materialized View

Having a materialized view joining two tables. I want to create the materialized view as a index organized table. Materialized view should be possible to be an IOT as documented. Examples of such may be found from support pages "How to create an Index-Organized Materialized View (Snapshot) [ID 114272.1]". But the examples are only for query on top of single table. No join in the materialized view query. There is the ORAGANIZATION INDEX opportunity in create materialized view, but the statement does not have the possibility to state the primary key constraint for the view.

It is possible to create a materialized view ON PREBUILT TABLE. The table may be index organized and attach the materialized view query on top to the existing table. Here is an example of such.

drop materialized view ab;

drop table ab;

drop table b;

drop table a;


create table a(aid int primary key, aname varchar2(20));

create table b(bid int primary key, aid references a, bname varchar2(20));


create table ab(aid int, bid int constraint ab_pk primary key, aname varchar2(20), bname varchar2(20)) organization index;

create materialized view ab (aid, bid, aname, bname) 
on prebuilt table
as
select a.aid, b.bid, a.aname, b.bname
  from a, b
 where a.aid=b.aid
;

insert into a values (1,'A');

insert into b values (1,1,'B');


insert into a values (2,'C');

insert into b values (2,2,'D');


commit;

exec dbms_mview.refresh('AB','C');

select * from ab;

1 1 A B
2 2 C D

select segment_name,segment_type from user_segments where segment_name like 'AB_PK';

AB_PK    INDEX

select table_name,iot_type from user_tables where table_name = 'AB';

AB    IOT