Feed aggregator

Partner Webcast – Oracle REST Data Services Communication for Cloud and Mobility

Representational State Transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web architecture delivering web services with a significant impact...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Oracle Database Cloud (DBaaS) Performance Consistency - Part 2

Randolf Geist - Sun, 2016-08-07 06:30
This is the second part of this installment, comparing the performance consistency of the DBaaS cloud offering with a dedicated physical host. This time instead of burning CPU using a trivial PL/SQL loop (see part 1) the test harness executes a SQL statement that performs logical I/O only, so no physical I/O involved.

In order to achieve that a variation of Jonathan Lewis' good old "kill_cpu" script got executed. In principle each thread performed the following:

define tabname = &1

define thread_id = &1;

alter session set "_old_connect_by_enabled" = true;

declare
  n number;
begin
  loop
    select
   count(*) X
    into    n
    from
   kill_cpu&tabname
    connect by
   n > prior n
    start with
   n = 1;
    insert into timings(testtype, thread_id, ts) values ('SQLLIO', &thread_id, systimestamp);
    commit;
  end loop;
end;
/

Each thread got its own, exclusive "kill_cpu<n>" table, so this setup attempted to avoid competition for buffer cache latches. The tables (in fact IOTs) were created like the following:

create table kill_cpu<n>(n primary key)
organization index
as
select
rownum n
from
all_objects
where
rownum <= 25
;

Again there were as many threads started as CPUs available - which meant 8 threads for the DBaaS environment and 4 threads for the physical host and again this was left running for several days.
The overall results look like this:
DBaaS:
Physical host:
Again the graph is supposed to show how many of the runs deviated how much from the overall median runtime. In contrast to the previous, simple PL/SQL test this time the DBaaS service shows a significantly different profile with a larger spread of deviation up to 3 percent from the median runtime, whereas the physical host only shows significant deviation up to 1.5 percent.
It's interesting to note however, that the physical host this time shows more extreme outliers than the DBaaS service, which was the other way around in the previous PL/SQL test.

The same graph on a per day basis doesn't show too significant differences between the days for either environment (here DBaaS):
Physical host:
Looking at the individual performance of each thread again the DBaaS shows a similar behaviour than last time - the different threads show a slightly different performance, and they also get slightly slower towards the end of the measurement:
And again the physical host shows a more consistent performance between threads:
The next test round will be a physical I/O bound setup.

Multitenant internals: how object links are parsed/executed

Yann Neuhaus - Sun, 2016-08-07 05:33

I had a comment on object links internals when creating a sharing=object view on GV$SESSION. Before posting about this specific case, I realized that I’ve never explained how a query on an object link is run on the CDB$ROOT container.

Data link testcase

Here is how I create the DEMOT table and DEMOV view as object link.

14:48:58 SQL> connect / as sysdba
Connected.
14:48:59 SQL> alter session set container=CDB$ROOT;
Session altered.
14:48:59 SQL> alter session set "_oracle_script"=true;
Session altered.
14:48:59 SQL> create table DEMOT as select rownum id, rownum num from xmltable('10 to 1000000');
Table created.
SQL> exec dbms_stats.gather_table_stats('','DEMOT');
14:49:00 SQL> create view DEMOV sharing=object as select * from DEMOT;
View created.
14:49:00 SQL> alter session set container=PDB;
Session altered.
14:49:00 SQL> create table DEMOT as select rownum id, rownum num from xmltable('1 to 1');
Table created.
SQL> exec dbms_stats.gather_table_stats('','DEMOT');
14:49:01 SQL> create view DEMOV sharing=object as select * from DEMOT;
View created.
14:49:01 SQL> alter session set "_oracle_script"=false;
Session altered.

And I run the following query two times (easier to look at trace without hard parsing)

14:49:02 SQL> select id from DEMOV where num<=100;
...
100 rows selected.

SQL_TRACE

I’ll detail the sql_trace of the last run.

First, the query is parsed in our PDB:

PARSING IN CURSOR #140360238365672 len=35 dep=0 uid=0 oct=3 lid=0 tim=687080512770 hv=237558885 ad='10cf55ae8' sqlid=' duy45bn72jr35'
select id from DEMOV where num<=100
END OF STMT
PARSE #140360238365672:c=0,e=86,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3055399777,tim=687080512769
EXEC #140360238365672:c=0,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3055399777,tim=687080512872

There was no hard parse for this second parse call (mis=0 means no ‘Misses in library cache during parse’). Execution occurred but no fetch yet.

At that point, the session switches to CDB$ROOT container (you have to trust me as there is no information about it in the trace file in 12.1)

PARSING IN CURSOR #140360238643592 len=99 dep=1 uid=0 oct=3 lid=0 tim=687080513015 hv=2967959178 ad='107be5590' sqlid=' 3b9x1rasffxna'
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100
END OF STMT
PARSE #140360238643592:c=0,e=33,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3598352655,tim=687080513014
EXEC #140360238643592:c=0,e=16,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3598352655,tim=687080513084
FETCH #140360238643592:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=100,dep=1,og=4,plh=3598352655,tim=687080513137
STAT #140360238643592 id=1 cnt=100 pid=0 pos=1 obj=0 op='RESULT CACHE cgn1rxw6ycznac8fyzfursq2z6 (cr=0 pr=0 pw=0 time=12 us)'
STAT #140360238643592 id=2 cnt=0 pid=1 pos=1 obj=98422 op='TABLE ACCESS FULL DEMOT (cr=0 pr=0 pw=0 time=0 us)'

We have here a recursive query (dep=1) that is run on the view in CDB$ROOT. It’s not the same query as ours: FROM clause is our sharing=object view, WHERE clause is the predicates that applies on it, and SELECT clause the columns that we need (ID was in my SELECT clause and NUM was in my WHERE clause). The query is parsed, executed, the 100 rows are fetched and interestingly the result goes to result cache. Yes, if you query V$RESULT_CACHE_OBJECTS in a CDB you will see lot of objects:

#multitenant object links uses result cache internally. AWR views are object links. pic.twitter.com/V5IMW2qQx2

— Franck Pachot (@FranckPachot) August 7, 2016

If you look at the FETCH line above, you see that the second execution was a result cache hit (cr=0)

So, the rows we require from the object link are fetched, then the execution of our query continues in our PDB:


FETCH #140360238365672:c=0,e=235,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=3055399777,tim=687080513194
FETCH #140360238365672:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3055399777,tim=687080513375
FETCH #140360238365672:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3055399777,tim=687080513586
FETCH #140360238365672:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3055399777,tim=687080513776
FETCH #140360238365672:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3055399777,tim=687080513983
FETCH #140360238365672:c=0,e=9,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3055399777,tim=687080514188
FETCH #140360238365672:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=15,dep=0,og=1,plh=3055399777,tim=687080514375

Up there 91 rows were fetched. We can see in the trace that the recursive cursor is closed there (session switches to CDB$ROOT for that):

CLOSE #140360238643592:c=0,e=3,dep=1,type=0,tim=687080514584

And our session is back on PDB container where the remaining rows are fetched and our cursor closed:

FETCH #140360238365672:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=9,dep=0,og=1,plh=3055399777,tim=687080514610
STAT #140360238365672 id=1 cnt=100 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$OBLNK$ (cr=0 pr=0 pw=0 time=263 us cost=0 size=13000 card=500)'
CLOSE #140360238365672:c=0,e=3,dep=0,type=0,tim=687080516173

As I’ve explained at DOAGDB16 and SOUG Day (next session is planned for UKOUG TECH16), following metadata and object links is done by the session switching from PDB to CDB$ROOT.

TKPROF

Here is the tkprof of the full trace with two executions

Our query on PDB


SQL ID: duy45bn72jr35 Plan Hash: 3055399777
 
select id
from
DEMOV where num<=100
 
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 16 0.00 0.00 0 0 0 200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 20 0.00 0.00 0 0 0 200
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 2
 
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100 100 100 FIXED TABLE FULL X$OBLNK$ (cr=1040 pr=1034 pw=0 time=42636 us cost=0 size=13000 card=500)

The number of executions and row count is correct, but there’s no logical reads here because all block reads occurred through the recursive query. The execution plan shows are full table scan on X$OBLNK$ which is how object link access path are displayed in 12.1

Query on CDB$ROOT


SQL ID: 3b9x1rasffxna Plan Hash: 3598352655
 
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM
FROM
"SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100
 
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.08 2068 2074 0 200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.03 0.08 2068 2074 0 200
 
Misses in library cache during parse: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 2
 
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
100 100 100 RESULT CACHE cgn1rxw6ycznac8fyzfursq2z6 (cr=1037 pr=1034 pw=0 time=662 us)
100 50 100 TABLE ACCESS FULL DEMOT (cr=1037 pr=1034 pw=0 time=522 us)

Here is where the work to get rows from the view is, in CDB$ROOT. Only two fetches there (one per execution). As we have seen in the row trace, all rows from the object link were fetched before we issue any fetch call from our query. I did same test with more rows selected and it seems that the fetch size is 200 rows: when 200 rows are fetched from CDB$ROOT, session switches back to PDB to fetch those rows (15 by 15 with the default sqlplus arraysize) and comes again to CDB$ROOT for next 200 rows. This means that they are probably buffered.

Actually there’s a hidden parameter to define that: “_cdb_view_prefetch_batch_size” is set to 200 by default.

Note that the 2000 logical reads are from the first execution only because the second one found the result in result cache.

V$SQL

From SQL_TRACE, the work done in the other container is not included in statement statistics. This makes tuning more difficult as we are used to see recursive work cumulated in the top level statement.

From CDB$ROOT here is what we can see from the shared pool (V$SQL) about the queries I’ve seen in the SQL_TRACE. I query V$SQL with the PLAN_HASH_VALUE (‘phv’ in the SQL_TRACE dump).


14:49:02 SQL> select con_id,sql_id,rows_processed,plan_hash_value,executions,parse_calls,fetches,buffer_gets,sql_text from v$sql where plan_hash_value in (3598352655,3055399777) order by last_active_time;
 
CON_ID SQL_ID ROWS_PROCESSED PLAN_HASH_VALUE EXECUTIONS PARSE_CALLS FETCHES BUFFER_GETS SQL_TEXT
---------- ------------- -------------- --------------- ---------- ----------- ---------- ----------- --------------------------------------------------------------------------------
4 duy45bn72jr35 200 3055399777 2 2 16 2721 select id from DEMOV where num<=100
4 bynmh7xm4bf54 0 3598352655 0 1 0 61 SELECT * FROM NO_OBJECT_LINK("SYS"."DEMOV")
1 3b9x1rasffxna 200 3598352655 2 2 2 2080 SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE

For the two queries we have seen above, the number of executions and fetches matches what we have seen in the trace. However, buffer_gets from the user query includes the logical reads from the recursive query.

But there’s more here. A statement is there with the same PLAN_HASH_VALUE than the internal query. It’s the query on the shared=object view, with the undocumented NO_OBJECT_LINK() function. It is parsed but not executed. This parse occurred in PDB just before switching to CDB$ROOT. This parse occurend only one time when our query was hard parsed. It has the same plan hash value than the internal query because the plan is the same: full table scan on the table.

My understanding of that is that when hard parsing our query and executing the recursive query on CDB$ROOT, the optimizer checks the view definition in the current container (the PDB) by parsing it without following object links (reason for the NO_OBJECT_LINK).

NO_OBJECT_LINK

Here is the parsing of that query with NO_OBJECT_LINK that occurs in the PDB:


PARSING IN CURSOR #140360238422472 len=43 dep=1 uid=0 oct=3 lid=0 tim=687080413554 hv=1715845284 ad='108fc0230' sqlid=' bynmh7xm4bf54'
SELECT * FROM NO_OBJECT_LINK("SYS"."DEMOV")
END OF STMT
PARSE #140360238422472:c=3000,e=2948,p=0,cr=61,cu=0,mis=1,r=0,dep=1,og=4,plh=3598352655,tim=687080413553
CLOSE #140360238422472:c=0,e=3,dep=1,type=0,tim=687080413614

There is no where clause here. I guess that the goal is just to validate the view in the PDB before executing the full query on CDB$ROOT.

Note that query in the CDB$ROOT do not use the NO_OBJECT_LINK here in 12.1 but could have use it to ensure that there are no further links.

Execution plans

With all those recursive queries, how the cardinalities are estimated? DEMOT has no rows in PDB and 1000000 rows in CDB$ROOT. Statistics gathered and I query only 100 rows (they are evenly distributed between low and high value);

The query that is only parsed in PDB:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID bynmh7xm4bf54, child number 0
-------------------------------------
SELECT * FROM NO_OBJECT_LINK("SYS"."DEMOV")
Plan hash value: 3598352655
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DEMOT | 1 | 6 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------

The query that is run in CDB$ROOT:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 3b9x1rasffxna, child number 0
-------------------------------------
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM "SYS"."DEMOV"
"DEMOV" WHERE "DEMOV"."NUM"<=100
Plan hash value: 3598352655
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 568 (100)| |
| 1 | RESULT CACHE | 9cv1sbwyz16651fgh17234v67g | | | | |
|* 2 | TABLE ACCESS FULL| DEMOT | 100 | 1000 | 568 (2)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("NUM"<=100)

Estimation is ok here.

And my user query:

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID duy45bn72jr35, child number 0
-------------------------------------
select id from DEMOV where num<=100
Plan hash value: 3055399777
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
|* 1 | FIXED TABLE FULL| X$OBLNK$ | 500 | 13000 | 0 (0)|
------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NUM"<=100)

Here, this estimation is not accurate at all, seems to have an hardcoded value of 500.

Conclusion

Lot of interesting things here. Object link (that you can call data links as well) are processed in a very special way. But don’t worry. Remember that you are not allowed to create them yourself. And there are only very few oracle maintained object links: some PDB information that must be available when PDB are not opened, AWR information that is consolidated in root, some audit reference tables,…

It interesting to see (and think about all consequences) that result cache is used here for internal optimization. Even when you don’t use result cache for your application, you should have a look at it and maybe size it differently than default. In a future post I’ll create a sharing=object view on V$ fixed views and result cache will be even more fun.

 

Cet article Multitenant internals: how object links are parsed/executed est apparu en premier sur Blog dbi services.

Is there a way to report on PROCESSES, SESSIONS AND CONNECTIONS that were killed using the kill command?

Tom Kyte - Sat, 2016-08-06 21:06
Is there a way to report on PROCESSES/SESSIONS/CONNECTIONS that were killed using the kill command?
Categories: DBA Blogs

Timestamp comparison in Oracle

Tom Kyte - Sat, 2016-08-06 21:06
Hi Guys, I need to fetch almost 2 years old records for 1 day i.e if today is 5 Aug I want to fetch records of 25 July. For this I wrote below query select * from Employee where employee_start_ts between add_months(sysdate-12, -24) and ad...
Categories: DBA Blogs

IOPS in oracle database

Tom Kyte - Sat, 2016-08-06 21:06
Hi Team, We need to check the read write iops in oracle database how can we check. Thanks
Categories: DBA Blogs

Do tons of sequences create any kind of problems?

Tom Kyte - Sat, 2016-08-06 21:06
Do sequences take up much memory or cause any other trouble if you end up with 10s of thousands of them? ========================= Unnecessary details: We have to design a service to generate unique IDs. We can do to a couple ways - One i...
Categories: DBA Blogs

Deleting large number of records

Tom Kyte - Sat, 2016-08-06 21:06
I have a table with 1 Million records. Because of undo log size, i can not delete this 1M records in one shot. So, need to create a PLSQL block which will delete the record in batches (let's say undo log can sustain .1 M records at one shot). Could y...
Categories: DBA Blogs

Sql trace and identify problem

Tom Kyte - Sat, 2016-08-06 21:06
My main question is how to identify cause of sql? alter session set timed_statistics=true alter session set max_dump_file_size=unlimited alter session set tracefile_identifier='MYSESSION_TODAY' alter session set events '10046 trace ...
Categories: DBA Blogs

SQL IN trimmed trailing blanks!

Tom Kyte - Sat, 2016-08-06 21:06
<Fred Feuerstein writes> I recently received an email from a puzzled developer. So...what's wrong with the following scenario? SQL> SELECT 'SQL IN trimmed trailing blanks!' 2 FROM DUAL 3 WHERE 'CA ' IN ( 'CA', 'US' ) 4 / 'TRIMMEDTRAILI...
Categories: DBA Blogs

Note to self for blocking locks

Michael Dinh - Sat, 2016-08-06 05:20
Session 1 starts UPDATE and nothing else.
++++++++++
Session 1:
02:53:39 ARROW:(MDINH@leo):PRIMARY> update t set object_id=100;

1 row updated.

02:53:45 ARROW:(MDINH@leo):PRIMARY>
++++++++++
Session 2:
02:53:50 ARROW:(SYSTEM@leo):PRIMARY> update mdinh.t set object_id=2;
++++++++++
Session 3:
02:53:58 ARROW:(DEMO@leo):PRIMARY> update mdinh.t set object_id=200;
++++++++++
Monitor blocking locks
$ sysdba @b.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 02:55:03 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947                        57 SQL*Net message from INACTIVE    1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      1 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           6827jhnufmcfx update t set object_id=100
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
              6827jhnufmcfx

ARROW:(SYS@leo):PRIMARY>
Blocking session is INACTIVE and the UPDATE SQL is available.

.

Session 1 execute SELECT following UPDATE.
++++++++++
Session 1:
02:56:16 ARROW:(MDINH@leo):PRIMARY> select sysdate from dual;

SYSDATE
-------------------
2016-08-06 02:56:23

02:56:23 ARROW:(MDINH@leo):PRIMARY>
++++++++++
Monitor blocking locks
ARROW:(SYS@leo):PRIMARY> @b

STATE      USERNAME        SID_SERIAL   SQL_ID          SEQ# EVENT                STATUS    MIN MACHINE              OSUSER     PROGRAM
---------- --------------- ------------ ------------- ------ -------------------- -------- ---- -------------------- ---------- ---------------
BLOCKING   MDINH           22,1947      7h35uxf5uhmm1     61 SQL*Net message from INACTIVE    0 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *DEMO           27,487       0sst1nnb8vw49     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l
WAITING    *SYSTEM         35,795       dh603ks5ggumy     45 enq: TX - row lock c ACTIVE      3 arrow.localdomain    oracle     sqlplus@arrow.l


STATE      USERNAME        SQL_ID        SQL_TEXT
---------- --------------- ------------- --------------------------------------------------------------------------------
BLOCKING   MDINH           7h35uxf5uhmm1 select sysdate from dual
WAITING    *DEMO           0sst1nnb8vw49 update mdinh.t set object_id=200
WAITING    *SYSTEM         dh603ks5ggumy update mdinh.t set object_id=2

ARROW:(SYS@leo):PRIMARY> select sql_id, prev_sql_id from v$session where sid=22;

SQL_ID        PREV_SQL_ID
------------- -------------
7h35uxf5uhmm1 7h35uxf5uhmm1

ARROW:(SYS@leo):PRIMARY>
Note: SQL_ID=PREV_SQL_ID and SQL is not the blocking SQL.
ARROW:(SYS@leo):PRIMARY> !cat b.sql
col username for a15 trunc
col state for a10 trunc
col osuser for a10 trunc
col program for a15 trunc
col sid_serial for a12 trunc
col event for a20 trunc
col machine for a20 trunc
col sid for 999999
col wait_min for 999
col sql_text for a100 trunc
col seq# for 99999
col min for 999
col sql_text for a80 trunc
set lines 200 pages 10000 tab off trimspool off
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
s.sid||','||s.serial# sid_serial,
sql_id,
s.seq#,
s.event,
s.status,
round(s.last_call_et/60) min,
s.machine,
s.osuser,
s.program
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
order by 1,2
;
---
with s as (
SELECT
decode(level,1,'BLOCKING','WAITING') state,
LPAD('*',(level-1)*1,' ') || NVL(s.username,'(oracle)') AS username,
decode(status,'INACTIVE',prev_sql_id,sql_id) sql_id
FROM   v$session s
WHERE  level > 1
OR     EXISTS (SELECT null FROM v$session WHERE blocking_session = s.sid)
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL
)
SELECT s.state, s.username, s.sql_id, sql_text
FROM v$sqlarea a, s
WHERE a.sql_id=s.sql_id
order by 1,2
;

ARROW:(SYS@leo):PRIMARY>
Nice Script from Jeffrey M. Hunter

http://www.idevelopment.info/data/Oracle/DBA_scripts/Locks/locks_blocking.sql

$ sysdba @locks_blocking.sql

SQL*Plus: Release 11.2.0.4.0 Production on Sat Aug 6 03:12:55 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Real Application Testing options


+------------------------------------------------------------------------+
| Report   : Blocking Locks                                              |
| Instance : leo                                                         |
+------------------------------------------------------------------------+


+------------------------------------------------------------------------+
| BLOCKING LOCKS (Summary)                                               |
+------------------------------------------------------------------------+

Number of blocking lock incidents: 2

Incident 1
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 27                                       22
Serial#               : 487                                      1947
Oracle User           : DEMO                                     MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20525                                    20521
Terminal              : pts/3                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=200

Incident 2
---------------------------------------------------------------------------------------------------------
                        WAITING                                  BLOCKING
                        ---------------------------------------- ----------------------------------------
Instance Name         : leo                                      leo
Oracle SID            : 35                                       22
Serial#               : 795                                      1947
Oracle User           : SYSTEM                                   MDINH
O/S User              : oracle                                   oracle
Machine               : arrow.localdomain                        arrow.localdomain
O/S PID               : 20706                                    20521
Terminal              : pts/7                                    pts/2
Lock Time             : 19 minutes                               19 minutes
Status                : ACTIVE                                   INACTIVE
Program               : sqlplus@arrow.localdomain (TNS V1-V3)    sqlplus@arrow.localdomain (TNS V1-V3)
Waiter Lock Type      : Transaction
Waiter Mode Request   : Exclusive
Waiting SQL           : update mdinh.t set object_id=2


+------------------------------------------------------------------------+
| LOCKED OBJECTS                                                         |
+------------------------------------------------------------------------+

Instance  SID / Serial#   Status    Locking Oracle User  Object Owner    Object Name               Object Type     Locked Mode
--------- --------------- --------- -------------------- --------------- ------------------------- --------------- -------------------------
leo       22 / 1947       INACTIVE  MDINH                MDINH           T                         TABLE           Row-Exclusive (SX)
leo       27 / 487        ACTIVE    DEMO                 MDINH           T                         TABLE           Row-Exclusive (SX)
leo       35 / 795        ACTIVE    SYSTEM               MDINH           T                         TABLE           Row-Exclusive (SX)

ARROW:(SYS@leo):PRIMARY>

Trying VirtualBox

Bobby Durrett's DBA Blog - Fri, 2016-08-05 23:49

I have been using  VMware Player to build test virtual machines on my laptop with an external drive for some time now. I used to use the free VMware Server. My test VMs weren’t fast because of the slow disk drive but they were good enough to run small Linux VMs to evaluate software. I also had one VM to do some C hacking of the game Nethack for fun. I got a lot of good use out of these free VMware products and VMware is a great company so I’m not knocking them. But, this week I accidentally wiped out all the VMs that I had on my external drive so I tried to rebuild one so I at least have one to boot up if I need a test Linux VM. I spend several hours trying to get the Oracle Linux 6.8 VM that I created to work with a screen resolution that matched my monitor. I have a laptop with a smaller 14 inch 1366 x 768 resolution built-in monitor and a nice new 27 inch 1920 x 1080 resolution external monitor. VMware player wouldn’t let me set the resolution to more than 1366 x 768 no matter what I did.

Finally after a lot of googling and trying all kinds of X Windows and VMware settings I finally gave up and decided to try VirtualBox. I was able to quickly install it and get my OEL 6.8 VM up with a larger resolution with no problem. It still didn’t give me 1920 x 1080 for some reason but had a variety of large resolutions to choose from.

After getting my Linux 6.8 machine to work acceptably I remembered that I was not able to get Linux 7 to run on VMware either. I had wanted to build a VM with the latest Linux but couldn’t get it to install. So, I downloaded the 7.2 iso and voilà it installed like a charm in VirtualBox. Plus I was able to set the resolution to exactly 1920 x 1080 and run in full screen mode taking up my entire 27 inch monitor.  Very nice!

I have not yet tried it, but VirtualBox seems to come with the ability to take a snapshot of a VM and to clone a VM. To get these features on VMware I’m pretty sure you need to buy the $249 VMware Workstation. I have a feeling that Workstation is a good product but I think it makes sense to try VirtualBox and see if the features that it comes with meet all my needs.

I installed VirtualBox at the end of the work day today so I haven’t had a lot of time to find its weaknesses and limitations. But so far it seems to have addressed several weaknesses that I found in VMware Player so it may have a lot of value to me. I think it is definitely worth trying out before moving on to the commercial version of VMware.

Bobby

P.S. Just tried the snapshot and clone features. Very neat. Also I forgot another nuisance with VMware Player. It always took a long time to shut down a machine. I think it was saving the current state. I didn’t really care about saving the state or whatever it was doing. Usually I just wanted to bring something up real quick and shut it down fast. This works like a charm on VirtualBox. It shuts down a VM in seconds. So far so good with VirtualBox.

P.P.S This morning I easily got both my Linux 6.8 and 7.2 VM’s to run with a nice screen size that takes up my entire 27 inch monitor but leaves room so I can see the menu at the top of the VM window and my Windows 7 status bar below the VM’s console window. Very nice. I was up late last night tossing and turning in bed thinking about all that I could do with the snapshot and linked clone features. &#x1f642;

Categories: DBA Blogs

New OA Framework 12.2.5 Update 5 Now Available

Steven Chan - Fri, 2016-08-05 14:10

Web-based content in Oracle E-Business Suite 12 runs on the Oracle Application Framework (OAF or "OA Framework") user interface libraries and infrastructure.   Since the release of Oracle E-Business Suite 12.2 in 2013, we have released several cumulative updates to Oracle Application Framework to fix performance, security, and stability issues. 

These updates are provided in cumulative Release Update Packs, and cumulative Bundle Patches that can be applied on top of the Release Update Pack. "Cumulative" means that the latest RUP or Bundle Patch contains everything released earlier.

The latest OAF update for EBS 12.2.5 is now available:

EBS 12.2.5 Bundle Patch 5 download patch

Where is the documentation for this update?

Instructions for installing this OAF Release Update Pack are here:

Who should apply this patch?

All EBS 12.2.5 users should apply this patch.  Future OAF patches for EBS 12.2.5 will require this patch as a prerequisite. 

What's new in this update?

This bundle patch is cumulative: it includes all fixes released in previous EBS 12.2.5 bundle patches.

This latest bundle patch includes new fixes for the following critical issues:

    • The getIndexedChild API throws an exception when all the subtabs are hidden in the subtab layout.
    • Adding new rows to the scroll mode table with more than 30 records leads to UI distortion and table becomes empty.    
    • The value in Table cells with style OraTableCellNumber and OraTableTotalNumber is left aligned.
    • The inline error message at the table cell is not wrapped in 12.2 code lines and consequently, the table columns are expanded to the length of error messages if the error messages are long.
    • Mechanism to render table header tag from Row wise rendering(Row Bean) is not available in OAF
    This Bundle Patch requires the R12.ATG_PF.C.Delta.5 Release Update Pack as a mandatory prerequisite.

    Related Articles


    Categories: APPS Blogs

    Journalismus, den die Welt braucht. Teil 1 | Pearls of journalism. Part 1

    Dietrich Schroff - Fri, 2016-08-05 14:07
    Today i browsed the magazine IT-BUSINESS  and read some really annoying articles. It was just words without building meaningful setences. It was so stunned, that i decided: I have to do something...


    The  following idea came into my mind:
    What about translating the part of the article, which i did not understand via Google Translate? Perhaps in english it sounds more meaningful (and more than 20% of the words are just technical terms in english...)






    And after Google Translate:

    Matching promotional concepts

    In replacement markets optimization of promotional activities is one of the easiest ways to improve the profit. A price reduction associated with the direct delivery into the channel leading to a significant decline in sales in the distribution. A channel conflict could follow: The inventory turnover in the distribution decreases the Profitabliltät in the width of the channels decreases. It is a further price action required to stimulate demand again. It is more settled, for all concerned but worsens the profit situation.
    Wow. It is really easier to understand this...


    Steve Miranda’s Big Bet: One Hundred Percent SaaS

    Linda Fishman Hoyle - Fri, 2016-08-05 12:41

    EVP Steve Miranda (pictured left) is certain of one thing: one hundred percent of businesses will move their applications to the cloud. It’s just a matter of time, Miranda says, because as Oracle makes better software faster, "this is the model that all our customers will want."

    Technology writer Donovan Jackson published "One hundred percent SaaS?" after Steve's recent visit to Australia.

    Basicfile LOBs

    Jonathan Lewis - Fri, 2016-08-05 10:12

    I got a call to a look at a performance problem involving LOBs a little while ago. The problem was with an overnight batch that had about 40 sessions inserting small LOBs (12KB to 22KB) concurrently, for a total of anything between 100,000 and 1,000,000 LOBs per night. You can appreciate that this would eventually become a very large LOB segment – so before the batch started all LOBs older than one month were deleted.

    The LOB column had the following (camouflaged) declaration:

     LOB (little_lob) STORE AS BASICFILE (
            TABLESPACE lob_ts 
            ENABLE STORAGE IN ROW 
            RETENTION
            NOCACHE 
            LOGGING
    )
    

    The database was 11gR2, the tablespace was defined with ASSM with uniform 1MB extents and a blocksize of 8KB (so the LOBs were all 2 or 3 chunks) and the undo retention time was 900 seconds. The effect of the “enable storage in row” is that the LOBINDEX didn’t have to hold any details of current LOB chunks (for in-row, the first 12 chunks are listed in the LOB Locator in the base table).

    So, examining an AWR report covering the critical interval, reviewing the captured ASH data, and checking the database, a few questions came to mind:

    • With 200 GB of current LOB data in the segment, why was the segment roughly 800GB ?
    • With no need for current LOBs to be indexed, how had the LOB Index reached 500,000 blocks in size ?
    • There had been 500,000 inserts that night – so why had Oracle done 6 Billion (cached) buffer gets on the (nocache) LOB segment ?
    • Given that the LOB Segment had not changed size during the night, why had there been millions of HW enqueue wait on the inserts ?

    Knowing the stuff that I did know about basicfile LOBs it seemed likely that the most significant problem was that the segment hadn’t been created with multiple freepools which, according to the very sparse (and not entirely self-consistent) documentation, exist to allow improved concurrency. So I thought I’d search the Internet for any useful information about freepools, how they worked, what impact they might have on this problem, why their absence might produce the symptoms I’d seen, and what the best course of action would be to address the problem.

    Of course the “correct” solution according to MoS would be to convert from basicfile to securefile – with a strange insistence on using online redefinition, but no explanation of why a simple CTAS or alter table move is undesirable or dangerous. Unfortunately there are a couple of notes on MoS describing performance issues with “high” levels of concurrent inserts that need to be addressed by setting hidden parameters so I’m not (yet) keen on rebuilding 700GB of a production system to produce a change that might still not work quickly enough; especially since I couldn’t find anything on MoS that could quantify the time needed to do the conversion.

    To my surprise I couldn’t find a single useful piece of information about the problem. The only articles I could find seemed to be little bits of cut-n-paste from the Oracle manual pages about using multiple freepools, and the best of those actually demonstrated rebuilding or changing the freepools settings on a LOB of a few megabytes. The most significant MoS note did say that the process “could be slow” and would lock the table. But surely someone, somewhere, must have tried it on a big system and had some idea of “how slow”.

    In the end I had to start building some simple models and doing a few experiments to find out what happens and where the time goes and what causes the strange results and – most importantly – how freepools might help. Fortunately, following a call to the Oak Table for any ideas or links to useful documents, Kurt van Meerbeeck directed me to the original Oracle patents which were enormously helpful in showing why freepools could help and why, in the wrong circumstances, you could still end up with a (slightly smaller) disaster on your hands.

     

    To be continued …

     

    Until I find time to write up the mechanisms I’ve discovered I’m happen to respond to any quick questions about this problem and I’m more than happy to hear about experiences others have had either rebuilding freepools, changing the number of freepools, or migrating from basicfile to securefile LOBs.

     


    List usernames instead of uids with the ps command for long usernames

    Yann Neuhaus - Fri, 2016-08-05 08:27

    Have your ever faced such a situation. You have usernames in your /etc/passwd file with more than 8 characters. This is no problem for Linux at all, usernames may be up to 32 characters long, only your ps output might look a little scrambled.

    It shows you the uid instead of the username like in the following example:

    $ id
    
    uid=20001(longuser01) gid=10002(oinstall) groups=10002(oinstall)
    
    $ sleep 1000000 &
    
    $ ps -ef | grep sleep | grep -v grep
    
    20001    14069 11739  0 14:11 pts/0    00:00:00 sleep 1000000

     

    But you want to see the username instead of the uid. The workaround is

    • Don’t use more than eight characters for your usernames  :-)
    • Or …. format your ps output the right way

    You could use the following alias to get the job done.

    $ alias psx='export PS_FORMAT="user:12,pid,%cpu,%mem,vsz,rss,tty,stat,start,time,command"; ps ax'
    
    $ psx | grep sleep | grep -v grep
    
    longuser01 14069  0.0  58940 520 pts/0 S 14:11:50 sleep 1000000

     

    Now it looks better.

    Cheers, William

     

    Cet article List usernames instead of uids with the ps command for long usernames est apparu en premier sur Blog dbi services.

    The new Identity clause doesn't increment the id

    Tom Kyte - Fri, 2016-08-05 08:26
    To reproduce the problem : <code>CREATE TABLE MY_TABLE ( ID NUMBER(19, 0) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name NVARCHAR2(255) NOT NULL ); INSERT INTO MY_TABLE (ID, name) VALUES (1, 'test1'); INSERT INTO MY_TABLE (name) VAL...
    Categories: DBA Blogs

    Use select query to concatinate, and remove spaces from two columns

    Tom Kyte - Fri, 2016-08-05 08:26
    Compare table with spaces in between data, with table without spaces Example:- Table A has three fields: ID, Cd_1, and Cd_2 Fields: Cd_1 or Cd_2 can have spaces between the codes. Below example has space between X and Y in Cd_1 ID Cd...
    Categories: DBA Blogs

    I've done the test and got the wrong answer but I don't know why

    Tom Kyte - Fri, 2016-08-05 08:26
    It's about the code, I don't know why it displays "before 5 after 5". CREATE OR REPLACE PROCEDURE plch_test AS BEGIN NULL; -- 5 lines of code END; / CREATE OR REPLACE TRIGGER plch_bca BEFORE CREATE OR ALTER ON SCHEMA DECLARE l_li...
    Categories: DBA Blogs

    Pages

    Subscribe to Oracle FAQ aggregator