many to many select -view efficiently -the problem

My last post was describing a solution to problem that I did not explain beforehand. I also chose a bad example of two things to be localized. The original problem did not have relationship between the things to be localized. The example has the citycountry table there and it should be used in the citycountrynames view. It was yet again difficult to generate a good example. But the original problematic view was something like:

create or replace view citycountrynames_slow as 
select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
     , col.name countryname
  from city ci 
  cross join country co 
  left outer join citylang cl on cl.city=ci.city
  left outer join countrylang col on col.country=co.country and col.lang=cl.lang
;

Cross join there. And that is not the problem. The plan for the query was

select cn.cityname,cn.countryname,sh.shop_name
  from citycountrynames_slow cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;

-------------------------------------------------------------
| Id  | Operation                     | Name                |
-------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |
|   1 |  NESTED LOOPS OUTER           |                     |
|   2 |   NESTED LOOPS                |                     |
|   3 |    VIEW                       |                     |
|   4 |     NESTED LOOPS              |                     |
|   5 |      NESTED LOOPS             |                     |
|   6 |       INDEX UNIQUE SCAN       | CITYLANG_PK         |
|   7 |       INDEX UNIQUE SCAN       | CITY_PK             |
|   8 |      INDEX FAST FULL SCAN     | COUNTRY_PK          |
|   9 |    TABLE ACCESS BY INDEX ROWID| SHOP                |
|  10 |     INDEX RANGE SCAN          | SHOP_COUNTRY_FK_IDX |
|  11 |   INDEX UNIQUE SCAN           | COUNTRYLANG_PK      |
-------------------------------------------------------------

One problem there is INDEX FAST FULL SCAN COUNTRY_PK. And the country table was a big one. The view query has the problem of outer joining to several tables. That is why the optimizer is not able to merge the view to the main query.

select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
     , col.name countryname
  from city ci, country co, citylang cl, countrylang col 
 where cl.city(+)=ci.city
   and col.country(+)=co.country 
   and col.lang(+)=cl.lang
;

ORA-01417: a table may be outer joined to at most one other table

One can avoid ORA-01417 by rewriting the query

select xx.*, col.name countryname
 from (
select cl.lang
     , ci.city
     , co.country
     , cl.name  cityname
  from city ci, country co, citylang cl
 where cl.city(+)=ci.city) xx, countrylang col 
where  col.country(+)=xx.country 
   and col.lang(+)=xx.lang
;

Actually a bit similar structure may be seen in the plan line 3. A view without a name is appearing to the plan. The tuned previous post view is behaving much more efficiently for the query. No full scans. The tuning was to add the lang table to the view query. Move the many parts to select list sub query.

select cn.cityname,cn.countryname,sh.shop_name
  from citycountrynames cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;

----------------------------------------------------------
| Id  | Operation                     | Name             |
----------------------------------------------------------
|   0 | SELECT STATEMENT              |                  |
|   1 |  INDEX UNIQUE SCAN            | CITYLANG_PK      |
|   2 |  INDEX UNIQUE SCAN            | COUNTRYLANG_PK   |
|   3 |  NESTED LOOPS                 |                  |
|   4 |   NESTED LOOPS                |                  |
|   5 |    NESTED LOOPS               |                  |
|   6 |     INDEX UNIQUE SCAN         | CITY_PK          |
|   7 |     INDEX UNIQUE SCAN         | LANG_PK          |
|   8 |    TABLE ACCESS BY INDEX ROWID| SHOP             |
|   9 |     INDEX RANGE SCAN          | SHOP_CITY_FK_IDX |
|  10 |   INDEX UNIQUE SCAN           | COUNTRY_PK       |
----------------------------------------------------------

many to many select -view efficiently

The problem to be solved here is described in the next post. A while ago I wrote about creating a view on top of many to many relation. In that article there was no need to select any columns from the many to many relation table. A localization view might be something that you want to have columns received from. Here is an example of such. A bit of preparation first. We have localized names for country and city names first. Create a view of those to be used from several usages. And a query example that joins a shop table to the view.

drop table shop;

drop table countrylang;

drop table citycountry;

drop table citylang;

drop table country;

drop table city;

drop table lang;



create table lang (lang varchar2(2) constraint lang_pk primary key);

insert into lang values ('fi');

insert into lang values ('en');

insert into lang values ('sv');



create table country(country varchar2(3) constraint country_pk primary key);

insert into country values ('FI');

insert into country values ('SV');


create table countrylang(country constraint countrylang_country_fk references country
                       , lang constraint countrylang_lang_fk references lang
                       , name varchar2(200) not null
                       , constraint countrylang_pk primary key (country,lang)
                       ) organization index compress 1
;

insert into countrylang values ('FI','fi','Suomi');

insert into countrylang values ('FI','sv','Finland');

insert into countrylang values ('FI','en','Finland');

insert into countrylang values ('SV','fi','Ruotsi');

insert into countrylang values ('SV','sv','Sweden');

insert into countrylang values ('SV','en','Sweden');


create table city(city varchar2(3) constraint city_pk primary key);

insert into city values ('tpe');

insert into city values ('hki');

insert into city values ('sto');

create table citylang(city constraint citylang_city_fk references city
                    , lang constraint citylang_lang_fk references lang
                    , name varchar2(200) not null
                    , constraint citylang_pk primary key (city,lang)
                    ) organization index compress 1
;

insert into citylang values ('tpe','fi','Tampere');

insert into citylang values ('tpe','sv','Tammerfors');

insert into citylang values ('tpe','en','Tampere');

insert into citylang values ('hki','fi','Helsinki');

insert into citylang values ('hki','sv','Helsingfors');

insert into citylang values ('hki','en','Helsinki');


create table citycountry(city references city, country references country, constraint citycountry_pk primary key (city,country));

insert into citycountry values('tpe','FI');

insert into citycountry values('hki','FI');

insert into citycountry values('sto','SV');


create view citycountrynames as 
select la.lang
     , ci.city
     , co.country
     , (select cl.name from citylang cl where cl.city=ci.city and cl.lang=la.lang) cityname
     , (select cl.name from countrylang cl where cl.country=co.country and cl.lang=la.lang) countryname
  from lang la, city ci, country co
;

So that was the view. Be sure to populate the tables underneath correctly to avoid nulls.

create table shop (shop int constraint shop_pk primary key
                 , shop_name varchar2(200) not null
                 , city references city not null
                 , country references country
                 , constraint shop_citycountry_fk foreign key (city,country) references citycountry)
;

create index shop_city_fk_idx on shop(city);

create index shop_country_fk_idx on shop(country);

insert into shop values (1,'Helsinki shop','hki','FI');

insert into shop values (2,'Tampere shop','tpe','FI');

insert into shop values (3,'Stockholm shop','sto','SV');

commit;

select cn.cityname,cn.countryname
  from citycountrynames cn 
 inner join shop sh 
    on sh.city=cn.city and sh.country=cn.country
 where cn.city=:city
   and cn.lang=:lang
;

It is late Saturday evening now. I will not show any execution plans now. Try those out yourself. I will have another taste of Isle of Jura.

SQL> variable lang varchar2(2);
SQL> variable city varchar2(3);
SQL> exec :city := 'tpe'

PL/SQL procedure successfully completed.

SQL> exec :lang := 'fi'

PL/SQL procedure successfully completed.

SQL> select sh.shop_name, cn.cityname,cn.countryname
  2    from citycountrynames cn
  3   inner join shop sh
  4      on sh.city=cn.city and sh.country=cn.country
  5   where cn.city=:city
  6     and cn.lang=:lang
  7  ;

SHOP_NAME
------------------------------------------------------

CITYNAME
------------------------------------------------------

COUNTRYNAME
------------------------------------------------------

Tampere shop
Tampere
Suomi

Store more days of history

Diagnostic pack purchased. The default amount of days stored work load repository is eight days. This is how it is changed to 365 days:

select snap_interval, retention
from dba_hist_wr_control;

SNAP_INTERVAL                                     
--------------------------------------------------
RETENTION                                         
--------------------------------------------------
+00 01:00:00.000000                               
+08 00:00:00.000000                               
                                                                                

execute dbms_workload_repository.modify_snapshot_settings(interval => 60,retention => 525600);

select snap_interval, retention
from dba_hist_wr_control;

SNAP_INTERVAL                                     
--------------------------------------------------
RETENTION                                         
--------------------------------------------------
+00 01:00:00.000000                               
+365 00:00:00.000000 

Treasure hunting and blocking sessions

Bored with being just a Sherlock Holmes dba? Take a look at treasurehunt.solita.fi some tricky puzzles available for a week. The airprize available only to scan-agile participants but the pages seem to be open for all internet.

Back to Sherlock Holmesing. Blocking sessions are tricky ones. At least when the bug report is talking about yesterday. The application is throwing 5 minute transaction timeout to end user face. Lucky us the Diagnostic pack is available.

The bug information is tracked to a action SOS.createSO. And lucky us we have module, action and client_identifier populated to the session while accessing database.

select session_id,blocking_session,blocking_session_serial#,sql_exec_start,min(sample_time),max(sample_time), count(*), action
  from  DBA_HIST_ACTIVE_SESS_HISTORY s
 where sample_time > sysdate -300 
   and event= 'enq: TX - row lock contention' 
   and action = 'SOS.createSO'
 group by session_id,blocking_session,blocking_session_serial#,sql_exec_start,action
 having count(*) > 29
 order by min(sample_time) desc
;

sessio blocki blocking sql_exec_start         min(sample_time)           max(sample_time)           count action
465    699    17041    07.03.2012 12:32:45    07.03.2012 12:32:49,839    07.03.2012 12:37:40,203    30    SOS.createSO

We see that our reported bug session 465 was blocked by session 699. The five minutes timeout is readable from count(*) being 30. There is no other information about the blocking session than the sid and serial#. Should we be lucky to see some samples around the same time from this blocking session.

select s.*
  from  DBA_HIST_ACTIVE_SESS_HISTORY s 
 where session_id = 699 
   and session_serial# = 17041
;

No rows
Unlucky us. How about any other sessions being influenced by the kept row lock?

select session_id,blocking_session,blocking_session_serial#,sql_exec_start,min(sample_time),max(sample_time), count(*), action
  from  DBA_HIST_ACTIVE_SESS_HISTORY s
 where sample_time > sysdate -300 
   and event= 'enq: TX - row lock contention' 
 group by session_id,blocking_session,blocking_session_serial#,sql_exec_start,action
 having count(*) > 29
 order by min(sample_time) desc
;

sessio blocki blocking sql_exec_start         min(sample_time)           max(sample_time)           count action
465    699    17041    07.03.2012 12:32:45    07.03.2012 12:32:49,839    07.03.2012 12:37:40,203    30    SOS.createSO
30     699    17041    07.03.2012 12:31:11    07.03.2012 12:31:19,712    07.03.2012 12:36:10,080    30    SOS.createSOL
461    699    17041    07.03.2012 12:16:32    07.03.2012 12:16:38,563    07.03.2012 12:21:28,946    30    SOS.createSOL
406    699    17041    07.03.2012 12:14:21    07.03.2012 12:14:28,402    07.03.2012 12:19:18,780    30    SOS.createSOL
657    304    2655    02.03.2012 12:38:37    02.03.2012 12:38:41,735    02.03.2012 12:43:32,113    30    SOS.createSOL

The session 699 blocked also other sessions doing action SOS.createSOL. The time frame 699 was holding the lock got wider is has started already at time 12:14:21.

And yet again lucky us the similar thing happened a couple days ago also. Another blocking session is found.

select sample_time,sql_exec_start,s.module,s.action,s.client_id
  from  DBA_HIST_ACTIVE_SESS_HISTORY s 
 where session_id = 304 
   and session_serial# = 2655
;

sample_tim sql_exec module   action       client_id
02.03.2012 12:25:20 services RIS.updateRI integration
02.03.2012 12:47:47 works    SS.getSOT    obfuscateduser

And yet again lucky us. We get a before and after problem suspect end users and actions. The guilty code lines are possible to find with this information and application logs. Yet again Diagnostic pack license purchase got us to the right direction. The problem would have otherwise been unsolved.

Time to learn model clause

I received the latest paper printed Oracle magazine today. Reading what the soon to be OUG Harmony 2012  keynote speaker has to say in the magazine askTom column. Dealing with the problem of Grouping Ranges.
So many ways writing code. The article introduces PL/SQL, recursive common table and model clause approaches. I just added a SQL and analytic function approach solution to the problem discussion at http://asktom.oracle.com. Not so pretty, but possible. Maybe the model clause version is still the most efficient one for this problem. Should also I start rehearsing model clause to my coding tool set...

Speaking at OUG Harmony 2012

I will be speaking about execution plans OUG Harmony 30.-31.5.2012 Hämeenlinna Finland. Ougf.fi draft agenda published. Keynote speakers: Chris Date and Tom Kyte

SQL Developer Data Modeler 3.1

New version SQL Developer Data Modeler 3.1 is available. Also SQL Developer 3.1 published.