Feed aggregator

Notes on DataStax and Cassandra

DBMS2 - Sun, 2016-08-07 20:19

I visited DataStax on my recent trip. That was a tipping point leading to my recent discussions of NoSQL DBAs and misplaced fear of vendor lock-in. But of course I also learned some things about DataStax and Cassandra themselves.

On the customer side:

  • DataStax customers still overwhelmingly use Cassandra for internet back-ends — web, mobile or otherwise as the case might be.
  • This includes — and “includes” might be understating the point — traditional enterprises worried about competition from internet-only ventures.

Customers in large numbers want cloud capabilities, as a potential future if not a current need.

One customer example was a large retailer, who in the past was awful at providing accurate inventory information online, but now uses Cassandra for that. DataStax brags that its queries come back in 20 milliseconds, but that strikes me as a bit beside the point; what really matters is that data accuracy has gone from “batch” to some version of real-time. Also, Microsoft is a DataStax customer, using Cassandra (and Spark) for the Office 365 backend, or at least for the associated analytics.

Per Patrick McFadin, the four biggest things in DataStax Enterprise 5 are:

  • Graph capabilities.
  • Cassandra 3.0, which includes a complete storage engine rewrite.
  • Tiered storage/ILM (Information Lifecycle Management).
  • Policy-based replication.

Some of that terminology is mine, but perhaps my clients at DataStax will adopt it too. :)

We didn’t go into as much technical detail as I ordinarily might, but a few notes on that tiered storage/ILM bit are:

  • It’s a way to have some storage that’s more expensive (e.g. flash) and some that’s cheaper (e.g. spinning disk). Duh.
  • Since Cassandra has a strong time-series orientation, it’s easy to imagine how those policies might be specified.
  • Technologically, this is tightly integrated with Cassandra’s compaction strategy.

DataStax Enterprise 5 also introduced policy-based replication features, not all of which are in open source Cassandra. Data sovereignty/geo-compliance is improved, which is of particular importance in financial services. There’s also hub/spoke replication now, which seems to be of particular value in intermittently-connected use cases. DataStax said the motivating use case in that area was oilfield operations, where presumably there are Cassandra-capable servers at all ends of the wide-area network.

Related links

  • I wrote in detail about Cassandra architecture in December, 2013.
  • I wrote about intermittently-connected data management via the relational gold standard SQL Anywhere in July, 2010.
Categories: Other

Lost control files

Tom Kyte - Sun, 2016-08-07 15:26
I have couple of questions regarding controlfiles 1.I am connected through catalog and lost all the control files of database how can i recover the database and get all control files back please suggest 2.If in lost of all control files will I be a...
Categories: DBA Blogs

Updating / Resetting columns

Tom Kyte - Sun, 2016-08-07 15:26
Hey Guys, I have a process where I need to update one column with the value of another if the 1st column is null, when I transfer the value I need to set the value of the second column to null, I am doing this in a single update statement, see exa...
Categories: DBA Blogs

MVIEW Dependencies

Tom Kyte - Sun, 2016-08-07 15:26
Where are the MVIEW dependencies stored? SQL> create view y1 as select 1 as y1 from dual; View created. <code>SQL> create materialized view y2 as select y1 as y2 from y1; Materialized view created. SQL> create view y3 as select y2 as y...
Categories: DBA Blogs

Selecting rank wise records

Tom Kyte - Sun, 2016-08-07 15:26
Hi Tom, Its been such a long time to visit here and resolve the doubts but this is the first time I am asking. There is an Employee table with Name, Department, Salary and Date of Joining in it. I need to write a query which will give the outpu...
Categories: DBA Blogs

Oracle Text CONTAINS with NULL input string

Tom Kyte - Sun, 2016-08-07 15:26
Hi Tom, I am on Oracle 11gR2. I have a table where I have created a text index. I have written a query as below: SELECT * FROM my_transactions t WHERE 1=1 AND CONTAINS (t.search_transactions_flag, '%'||:str||'%') > 0; It prompt...
Categories: DBA Blogs

out-of-place refresh of a materialized view

Tom Kyte - Sun, 2016-08-07 15:26
I am researching how to improve the availability of data in an MV. The doc says that while doing a complete refresh of MV using the out-of-place option that the data in the mv is still available, which I find is true. But it also says the mv is av...
Categories: DBA Blogs

Oracle

Tom Kyte - Sun, 2016-08-07 15:26
TABLEA ****** C1 C2 C3 (COLS) * - - - * - * - * - * * I have 6(*) in the above table? But when i select my table i get the count 6 result how? sir? From my side i have done like select count(c1) from tb...
Categories: DBA Blogs

plsql trigger

Tom Kyte - Sun, 2016-08-07 15:26
create or replace trigger foremp before insert on emp declare day char(10); begin if (to_char(sysdate,day) in ('sunday')) then raise_application_error(-20300,'not allowed on sunday'); end if; end; / I created this trigger t...
Categories: DBA Blogs

Are we really create table aumatically as per our requirement

Tom Kyte - Sun, 2016-08-07 15:26
Hi, I want to create a table automatically as per based of various column data types and various number of column. Please ! dont cconsider the constraint. for example: table name :table1 required output is : DDl structure of crea...
Categories: DBA Blogs

PLSQL syntax

Tom Kyte - Sun, 2016-08-07 15:26
VARIABLE NAME CANNOT START WITH A) NUMBER B) SPECIAL CHARACTER C) CHARACTER D) ALL OF THE ABOVE I KNOW THAT WE CANNOT START A VARIABLE USING NUMBER ALSO SPECIAL CHARACTERS EXCLUDING(&,#,_). THIS IS A QUESTION I GOT FOR AN EXAM AND I SELECTED...
Categories: DBA Blogs

Multitenant internals: object links on fixed tables

Yann Neuhaus - Sun, 2016-08-07 11:13

The previous post partly answered to the original question (why an object link to V$SESSION is refreshed only every 30 seconds): recursive queries on shared=object views. Now let’s see what is different with fixed tables.

Disclaimer: this is research only. Don’t do that anywhere else than a lab. This is implicit when the title is ‘internals’.

Result cache

When query on a shared=object view is executed from a PDB, the session switches to the CDB to run a recursive query to get the rows. This query uses result cache by adding the following hint:
/*+ RESULT_CACHE (SYSOBJ=TRUE) */

This enables result cache for the rows fetched by this query, and even for system object. The ‘SYSOBJ=TRUE’ is there because the “_rc_sys_obj_enabled” defaults to true.

Here is the result cache from the previous post (I flushed the result cache just before the second run because in 12c hard parsing is also using a lot the result cache):


16:34:00 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,3551671056) order by last_active_time;
 
CON_ID SQL_ID ROWS_PROCESSED PLAN_HASH_VALUE EXECUTIONS PARSE_CALLS FETCHES BUFFER_GETS SQL_TEXT
---------- ------------- -------------- --------------- ---------- ----------- ---------- ----------- --------------------------------------------------------------------------------
1 350gg6247sfa6 200 3598352655 2 2 2 26 SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV
3 bynmh7xm4bf54 0 3598352655 0 5 0 51 SELECT * FROM NO_OBJECT_LINK("SYS"."DEMOV")
3 duy45bn72jr35 200 3551671056 2 2 16 269 select id from DEMOV where num column name format a120 trunc
16:34:00 SQL> select type,status,name,row_count from v$result_cache_objects order by row_count desc fetch first 10 rows only;
 
TYPE STATUS NAME ROW_COUNT
---------- --------- ------------------------------------------------------------------------------------------------------------------------ ----------
Result Published SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV") "DEMOV" WHERE "DEMOV"."NUM"<=100 100
Dependency Published SYS.DEMOT 0
Dependency Published SYS.DEMOV 0

As with regular result cache, there is dependency tracking: as soon as the underlying table has some modification, the cache will be invalidated. So this query is always guaranteed to get fresh results.

Invalidation

I did the same when deleting half of the rows before the second execution in order to invalidate the result cache:


16:43:46 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,3551671056) order by last_active_time;
 
CON_ID SQL_ID ROWS_PROCESSED PLAN_HASH_VALUE EXECUTIONS PARSE_CALLS FETCHES BUFFER_GETS SQL_TEXT
---------- ------------- -------------- --------------- ---------- ----------- ---------- ----------- --------------------------------------------------------------------------------
1 350gg6247sfa6 150 3598352655 2 2 2 26 SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV
3 bynmh7xm4bf54 0 3598352655 0 5 0 51 SELECT * FROM NO_OBJECT_LINK("SYS"."DEMOV")
3 duy45bn72jr35 150 3551671056 2 2 13 269 select id from DEMOV where num column name format a120 trunc
16:43:46 SQL> select type,status,name,row_count from v$result_cache_objects order by row_count desc fetch first 10 rows only;
 
TYPE STATUS NAME ROW_COUNT
---------- --------- ------------------------------------------------------------------------------------------------------------------------ ----------
Result Invalid SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV") "DEMOV" WHERE "DEMOV"."NUM"<=100 100
Result Published SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE) */ ID,NUM FROM NO_OBJECT_LINK("SYS"."DEMOV") "DEMOV" WHERE "DEMOV"."NUM"<=100 50
Dependency Published SYS.DEMOT 0
Dependency Published SYS.DEMOV 0

I’ve 100 rows from the first run, invalidated, and them 50 rows from the second one.

Note that I’ve the same result when I set “_disable_cdb_view_rc_invalidation”=true. Sometimes undocumented parameters behavior cannot be guessed only from their name.

Fixed tables

I’ve run the same testcase but with the following definition of DEMOV:

create view DEMOV sharing=object as select saddr id, rownum num from V$SESSION;

Here is the trace of the recursive query run in CDB$ROOT, at first execution:

PARSING IN CURSOR #140436732146672 len=112 dep=1 uid=0 oct=3 lid=0 tim=769208810641 hv=3298783355 ad='108ee92f0' sqlid=' 10qf9kb29yw3v'
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100
END OF STMT
PARSE #140436732146672:c=4000,e=10614,p=0,cr=6,cu=0,mis=1,r=0,dep=1,og=4,plh=350654732,tim=769208810640
EXEC #140436732146672:c=0,e=17,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=350654732,tim=769208810701
FETCH #140436732146672:c=1000,e=961,p=0,cr=0,cu=0,mis=0,r=53,dep=1,og=4,plh=350654732,tim=769208811687
STAT #140436732146672 id=1 cnt=53 pid=0 pos=1 obj=0 op='RESULT CACHE byq3fbkawmkm34gtfk1csvwv52 (cr=0 pr=0 pw=0 time=877 us)'
STAT #140436732146672 id=2 cnt=53 pid=1 pos=1 obj=98258 op='VIEW DEMOV (cr=0 pr=0 pw=0 time=655 us cost=0 size=171 card=9)'
STAT #140436732146672 id=3 cnt=53 pid=2 pos=1 obj=0 op='COUNT (cr=0 pr=0 pw=0 time=652 us)'
STAT #140436732146672 id=4 cnt=53 pid=3 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=597 us cost=0 size=306 card=9)'
STAT #140436732146672 id=5 cnt=53 pid=4 pos=1 obj=0 op='NESTED LOOPS (cr=0 pr=0 pw=0 time=436 us cost=0 size=270 card=9)'
STAT #140436732146672 id=6 cnt=53 pid=5 pos=1 obj=0 op='FIXED TABLE FULL X$KSLWT (cr=0 pr=0 pw=0 time=219 us cost=0 size=352 card=44)'
STAT #140436732146672 id=7 cnt=53 pid=5 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSUSE (ind:1) (cr=0 pr=0 pw=0 time=101 us cost=0 size=22 card=1)'
STAT #140436732146672 id=8 cnt=53 pid=4 pos=2 obj=0 op='FIXED TABLE FIXED INDEX X$KSLED (ind:2) (cr=0 pr=0 pw=0 time=70 us cost=0 size=4 card=1)'

The difference here is that SHELFLIFE=30 has been added to the generated result cache hint.

The second run has very short parse time (c=0) because it’s a soft parse but you also see very short fetch time (c=0) because it’s a cache hit:

PARSING IN CURSOR #140436733602136 len=112 dep=1 uid=0 oct=3 lid=0 tim=769208821904 hv=3298783355 ad='108ee92f0' sqlid=' 10qf9kb29yw3v'
SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100
END OF STMT
PARSE #140436733602136:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=350654732,tim=769208821904
EXEC #140436733602136:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=350654732,tim=769208821955
FETCH #140436733602136:c=0,e=18,p=0,cr=0,cu=0,mis=0,r=53,dep=1,og=4,plh=350654732,tim=769208821990

When I look at the result cache, there were no invalidations:
TYPE STATUS NAME ROW_COUNT
---------- --------- ------------------------------------------------------------------------------------------------------------------------ ----------
Result Published SELECT /*+ RESULT_CACHE (SYSOBJ=TRUE SHELFLIFE=30) */ ID,NUM FROM "SYS"."DEMOV" "DEMOV" WHERE "DEMOV"."NUM"<=100 55
Dependency Published SYS.DEMOV 0

When SHELFLIFE is set in a result cache hint, there is no dependency tracking. I’ve described RESULT_CACHE hint expiration options in a previous post.

The V$ views are on fixed tables, structures in memory, where there is no dependency tracking possibility. This is probably why the recursive query for sharing=object views use a SHELFLIFE instead.

This means that if you create an object link view on a fixed table the query will show same result for the next executions for 30 seconds.

“_cdb_view_rc_shelflife”

I’ve tested a shared=object view on V$SESSION as an answer to a previous blog comment. My query selects MAX(LAST_ET_CALL) which is supposed to increase every second for the inactive sessions. And we see that the result changes only every 30 seconds.

Those 30 seconds are parametered by “_cdb_view_rc_shelflife”. Here is the same test where I set “_cdb_view_rc_shelflife” to 5 seconds:


15:31:48 SQL> alter session set "_cdb_view_rc_shelflife"=5;
Session altered.
 
15:31:48 SQL> set serveroutput on
15:31:48 SQL> declare
15:31:48 2 x varchar2(100);
15:31:48 3 begin
15:31:48 4 for i in 1..60 loop
15:31:48 5 dbms_lock.sleep(1);
15:31:48 6 select to_char(current_timestamp)||' --> '||max(last_call_et) into x from DEMOV;
15:31:48 7 dbms_output.put_line(x);
15:31:48 8 end loop;
15:31:48 9 end;
15:31:48 10 /
 
07-AUG-16 03.31.49.852081 PM +00:00 --> 775144
07-AUG-16 03.31.50.863742 PM +00:00 --> 775144
07-AUG-16 03.31.51.863753 PM +00:00 --> 775144
07-AUG-16 03.31.52.864697 PM +00:00 --> 775144
07-AUG-16 03.31.53.864706 PM +00:00 --> 775144
07-AUG-16 03.31.54.864726 PM +00:00 --> 775144
07-AUG-16 03.31.55.864669 PM +00:00 --> 775150
07-AUG-16 03.31.56.864711 PM +00:00 --> 775150
07-AUG-16 03.31.57.864754 PM +00:00 --> 775150
07-AUG-16 03.31.58.864702 PM +00:00 --> 775150
07-AUG-16 03.31.59.864711 PM +00:00 --> 775150
07-AUG-16 03.32.00.864779 PM +00:00 --> 775150
07-AUG-16 03.32.01.865710 PM +00:00 --> 775156
07-AUG-16 03.32.02.866738 PM +00:00 --> 775156
07-AUG-16 03.32.03.866719 PM +00:00 --> 775156
07-AUG-16 03.32.04.866787 PM +00:00 --> 775156
07-AUG-16 03.32.05.866758 PM +00:00 --> 775156
07-AUG-16 03.32.06.866805 PM +00:00 --> 775156
07-AUG-16 03.32.07.867738 PM +00:00 --> 775162
07-AUG-16 03.32.08.868743 PM +00:00 --> 775162
07-AUG-16 03.32.09.868727 PM +00:00 --> 775162
07-AUG-16 03.32.10.868724 PM +00:00 --> 775162
07-AUG-16 03.32.11.868758 PM +00:00 --> 775162
07-AUG-16 03.32.12.869763 PM +00:00 --> 775167
07-AUG-16 03.32.13.870741 PM +00:00 --> 775167
07-AUG-16 03.32.14.870742 PM +00:00 --> 775167
07-AUG-16 03.32.15.870721 PM +00:00 --> 775167
07-AUG-16 03.32.16.870734 PM +00:00 --> 775167
07-AUG-16 03.32.17.870883 PM +00:00 --> 775167
07-AUG-16 03.32.18.872741 PM +00:00 --> 775173
07-AUG-16 03.32.19.873837 PM +00:00 --> 775173

And here is the same test after setting:

SQL> exec dbms_result_cache.bypass(true);

I’ve not tested, but I expect the same in Standard Edition where result cache is disabled


07-AUG-16 03.43.32.158741 PM +00:00 --> 775846
07-AUG-16 03.43.33.185793 PM +00:00 --> 775847
07-AUG-16 03.43.34.186633 PM +00:00 --> 775848
07-AUG-16 03.43.35.186738 PM +00:00 --> 775849
07-AUG-16 03.43.36.187696 PM +00:00 --> 775850
07-AUG-16 03.43.37.188684 PM +00:00 --> 775851
07-AUG-16 03.43.38.188692 PM +00:00 --> 775852
07-AUG-16 03.43.39.189755 PM +00:00 --> 775853
07-AUG-16 03.43.40.190697 PM +00:00 --> 775854
07-AUG-16 03.43.41.191763 PM +00:00 --> 775855
07-AUG-16 03.43.42.192706 PM +00:00 --> 775856
07-AUG-16 03.43.43.193736 PM +00:00 --> 775857

Conclusion

Don’t be afraid. There are very few sharing=object views in the dictionary, and only few of them have dependencies on fixed tables:

SQL> select owner,name,referenced_name from dba_dependencies
where (referenced_owner,referenced_name) in (select 'SYS',view_name from v$fixed_view_definition union select 'SYS',name from v$fixed_table)
and (owner,name,type) in (select owner,object_name,object_type from dba_objects where sharing='OBJECT LINK')
;
 
OWNER NAME REFERENCED_NAME
------------------------------ ------------------------------ ------------------------------
SYS INT$DBA_HIST_SQLSTAT X$MODACT_LENGTH
SYS INT$DBA_HIST_ACT_SESS_HISTORY X$MODACT_LENGTH
SYS INT$DBA_OUTSTANDING_ALERTS X$KELTGSD
SYS INT$DBA_OUTSTANDING_ALERTS X$KELTSD
SYS INT$DBA_OUTSTANDING_ALERTS X$KELTOSD
SYS INT$DBA_ALERT_HISTORY X$KELTGSD
SYS INT$DBA_ALERT_HISTORY X$KELTSD
SYS INT$DBA_ALERT_HISTORY X$KELTOSD
SYS INT$DBA_ALERT_HISTORY_DETAIL X$KELTGSD
SYS INT$DBA_ALERT_HISTORY_DETAIL X$KELTSD
SYS INT$DBA_ALERT_HISTORY_DETAIL X$KELTOSD
SYS DEMOV GV$SESSION
 
6 rows selected.

I’ve described how AWR views are stacked onto each other in a previous post.

And don’t worry, you don’t need to have a fresh view of those X$ tables. As an example, behind DBA_HIST_ACTIVE_SES_HISTORY the fixed table X$MODACT_LENGTH holds only the length of module and action strings:
SQL> select * from X$MODACT_LENGTH;
 
ADDR INDX INST_ID CON_ID KSUMODLEN KSUACTLEN
---------------- ---------- ---------- ---------- ---------- ----------
00007FF2EF280920 0 1 0 48 32

And the others (X$KELTSD, X$KELTGSD, X$KELTOSD) are the structures behind V$ALERT_TYPES that are not supposed to change.

So don’t panic. The multitenant architecture has some strange implementation stuff, but mostly harmless…

 

Cet article Multitenant internals: object links on fixed tables est apparu en premier sur Blog dbi services.

ODTUG Leadership Program / ODTUG Board Elections

Tim Tow - Sun, 2016-08-07 09:36
As many of you know, I have spent many years helping the Hyperion community in many ways.  I have wrote this blog for quite some time, have taken on the OlapUnderground Utilities and provided free support through my company,  In other words, I have worked hard to give back to the community and, in return, have had many kind words from people we have helped.

As a result of this spirit of giving back, I was lucky enough to be elected to the ODTUG Board of Directors which brings me to the real point of this blog post.  There is an upcoming ODTUG Board Election coming up and, due to term limits, my time on the board is coming to a close.  The same is true of my good friend Cameron Lackpour.  With two board seats open, could *you* be the next person to step up?

Here are my thoughts on what it takes to be a board member.  Joining the ODTUG Board isn't just something you decide to do and it isn't an achievement that is 'a feather in someone's hat'.  It is a commitment to helping others in the community for two years.  But it is more than that.  To be a successful ODTUG Board member, the commitment you have should be a continuation of the long-term commitment you have made to helping others.  So, if you are interested in serving on the ODTUG Board at some time in the future, the thing you should do is #GetInvolved.  If you are not already involved, the easiest way to get involved is to volunteer on the ODTUG Volunteer page.

If you are ready to step up from that point, for a number of years, ODTUG has maintained a Leadership Program to help train the next generation of users.  The application process for this year's Leadership Program is open for another week.  For more information, or to submit an application for the Leadership Program, you can get more information on the ODTUG Leadership Program page.

Categories: BI & Warehousing

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

Pages

Subscribe to Oracle FAQ aggregator