Oakies Blog Aggregator

Oracle Open World 2017 presentation

Hi,
I will be presenting about ASM internals in Oracle Open World 2017 conference on Sunday October 1st. Following are the details:

Session ID: SUN5682

Session Title: Oracle Automatic Storage Management and Internals

Room: Moscone South – Room 155 Date: 10/01/17
Start Time: 12:45:00 PM
End Time: 01:30:00 PM

See you there!

Activating and Deactivating Performance Feedback

Performance feedback is one of the adaptive query optimizer features introduced in Oracle Database 12c. The aim of this short post isn’t to explain how it works, but to simply warn you about a buggy behavior in its configuration.

The parameters that control performance feedback are the following:

  • OPTIMIZER_ADAPTIVE_FEATURES (12.1 only): if set to TRUE (default), performance feedback is enabled. Otherwise, it is disabled.
  • OPTIMIZER_ADAPTIVE_STATISTICS (12.1 with patch 22652097, and 12.2): if set to TRUE, performance feedback is enabled. Otherwise, which is the default, it is disabled.
  • PARALLEL_DEGREE_POLICY (12.1 and 12.2): it has to be set to ADAPTIVE (default is MANUAL) to enable performance feedback.

So far, so good.

The problem is that all those parameters control the undocumented parameter _OPTIMIZER_PERFORMANCE_FEEDBACK without checking how the others are set. As a result, knowing the value of the documented parameters isn’t enough to know whether statistics feedback is enabled. What you have to know is the order in which they were set! Alternatively you can check the value of the undocumented parameter.

To demonstrate that behavior I wrote this script. It’s output, when executed against 12.1 and 12.2, is the following. As you can see, when PARALLEL_DEGREE_POLICY is set after OPTIMIZER_ADAPTIVE_FEATURES/ OPTIMIZER_ADAPTIVE_STATISTICS, performance feedback is incorrectly enabled.

  • 12.1
VERSION
------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_features *before* parallel_degree_policy

optimizer_adaptive_features parallel_degree_policy _optimizer_performance_feedback
--------------------------- ---------------------- -------------------------------
FALSE                       MANUAL                 OFF
FALSE                       LIMITED                OFF
FALSE                       AUTO                   OFF
FALSE                       ADAPTIVE               ALL
TRUE                        MANUAL                 OFF
TRUE                        LIMITED                OFF
TRUE                        AUTO                   OFF
TRUE                        ADAPTIVE               ALL

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_features *after* parallel_degree_policy

parallel_degree_policy optimizer_adaptive_features _optimizer_performance_feedback
---------------------- --------------------------- -------------------------------
MANUAL                 FALSE                       OFF
LIMITED                FALSE                       OFF
AUTO                   FALSE                       OFF
ADAPTIVE               FALSE                       OFF
MANUAL                 TRUE                        OFF
LIMITED                TRUE                        OFF
AUTO                   TRUE                        OFF
ADAPTIVE               TRUE                        ALL
  • 12.2
VERSION
------------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *before* parallel_degree_policy

optimizer_adaptive_statistics parallel_degree_policy _optimizer_performance_feedback
----------------------------- ---------------------- -------------------------------
FALSE                         MANUAL                 OFF
FALSE                         LIMITED                OFF
FALSE                         AUTO                   OFF
FALSE                         ADAPTIVE               ALL
TRUE                          MANUAL                 OFF
TRUE                          LIMITED                OFF
TRUE                          AUTO                   OFF
TRUE                          ADAPTIVE               ALL

WHAT
------------------------------------------------------------------------------------
Set optimizer_adaptive_statistics *after* parallel_degree_policy

parallel_degree_policy optimizer_adaptive_statistics _optimizer_performance_feedback
---------------------- ----------------------------- -------------------------------
MANUAL                 FALSE                         OFF
LIMITED                FALSE                         OFF
AUTO                   FALSE                         OFF
ADAPTIVE               FALSE                         OFF
MANUAL                 TRUE                          OFF
LIMITED                TRUE                          OFF
AUTO                   TRUE                          OFF
ADAPTIVE               TRUE                          ALL

How #Exadata benefits your Data Warehouse

https://uhesse.files.wordpress.com/2017/09/exadata.png?w=49&h=150 49w, https://uhesse.files.wordpress.com/2017/09/exadata.png 168w" sizes="(max-width: 98px) 100vw, 98px" />

A migration of your Data Warehouse to Exadata will deliver most likely the combined benefits of Hybrid Columnar Compression, Smart Scan and Storage Indexes. That means better performance with less storage space consumption. Let’s see an example:

 

SQL> desc sales_noexa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PRODUCT                                            CHAR(25)
 CHANNEL_ID                                         NUMBER
 CUST_ID                                            NUMBER
 AMOUNT_SOLD                                        NUMBER
 ORDER_DATE                                         DATE
 SHIP_DATE                                          DATE

SQL> select count(*) from sales_noexa;

  COUNT(*)
----------
  80000000

SQL> select sum(bytes)/1024/1024 as mb from user_segments where segment_name='SALES_NOEXA';

        MB
----------
      5520

This table is partitioned by ORDER_DATE with one partition per year. Without Exadata, performance is as follows:

SQL> select count(*),avg(amount_sold) from sales_noexa where channel_id=2;

  COUNT(*) AVG(AMOUNT_SOLD)
---------- ----------------
  16000000             5000

Elapsed: 00:00:11.06
SQL> select sum(amount_sold) from sales_noexa where ship_date = to_date('01.01.2011','dd.mm.yyyy');

SUM(AMOUNT_SOLD)
----------------
        40000000

Elapsed: 00:00:06.07
SQL> select sum(amount_sold) from sales_noexa where order_date = to_date('01.01.2011','dd.mm.yyyy');

SUM(AMOUNT_SOLD)
----------------
        40000000

Elapsed: 00:00:00.15

There is no index, so each statement above was a Full Table Scan. The last was much faster because of Partition Pruning. Now the same on Exadata:

SQL> desc sales_exa
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PRODUCT                                            CHAR(25)
 CHANNEL_ID                                         NUMBER
 CUST_ID                                            NUMBER
 AMOUNT_SOLD                                        NUMBER
 ORDER_DATE                                         DATE
 SHIP_DATE                                          DATE

SQL> select count(*) from sales_exa;

  COUNT(*)
----------
  80000000

SQL> select sum(bytes)/1024/1024 as mb from user_segments where segment_name='SALES_EXA';

        MB
----------
      1574

The table is way smaller although it contains the very same rows. That’s because I used Hybrid Columnar Compression to create this table:

create table sales_exa (id number, product char(25), channel_id number, cust_id number, amount_sold number, order_date date, ship_date date)
partition by range (order_date)
(
partition p1990 values less than (to_date('01.01.1991','dd.mm.yyyy')) compress for archive high,
partition p1991 values less than (to_date('01.01.1992','dd.mm.yyyy')) compress for archive high,
partition p1992 values less than (to_date('01.01.1993','dd.mm.yyyy')) compress for archive high,
partition p1993 values less than (to_date('01.01.1994','dd.mm.yyyy')) compress for archive high,
partition p1994 values less than (to_date('01.01.1995','dd.mm.yyyy')) compress for archive high,
partition p1995 values less than (to_date('01.01.1996','dd.mm.yyyy')) compress for archive high,
partition p1996 values less than (to_date('01.01.1997','dd.mm.yyyy')) compress for archive low,
partition p1997 values less than (to_date('01.01.1998','dd.mm.yyyy')) compress for archive low,
partition p1998 values less than (to_date('01.01.1999','dd.mm.yyyy')) compress for archive low,
partition p1999 values less than (to_date('01.01.2000','dd.mm.yyyy')) compress for archive low,
partition p2000 values less than (to_date('01.01.2001','dd.mm.yyyy')) compress for archive low,
partition p2001 values less than (to_date('01.01.2002','dd.mm.yyyy')) compress for query high,
partition p2002 values less than (to_date('01.01.2003','dd.mm.yyyy')) compress for query high,
partition p2003 values less than (to_date('01.01.2004','dd.mm.yyyy')) compress for query high,
partition p2004 values less than (to_date('01.01.2005','dd.mm.yyyy')) compress for query high,
partition p2005 values less than (to_date('01.01.2006','dd.mm.yyyy')) compress for query high,
partition p2006 values less than (to_date('01.01.2007','dd.mm.yyyy')) compress for query low,
partition p2007 values less than (to_date('01.01.2008','dd.mm.yyyy')) compress for query low,
partition p2008 values less than (to_date('01.01.2009','dd.mm.yyyy')) compress for query low,
partition p2009 values less than (to_date('01.01.2010','dd.mm.yyyy')) compress for query low,
partition p2010 values less than (to_date('01.01.2011','dd.mm.yyyy')) compress for query low,
partition p2011 values less than (to_date('01.01.2012','dd.mm.yyyy')),
partition p2012 values less than (to_date('01.01.2013','dd.mm.yyyy')),
partition p2013 values less than (to_date('01.01.2014','dd.mm.yyyy')),
partition p2014 values less than (to_date('01.01.2015','dd.mm.yyyy')),
partition p2015 values less than (to_date('01.01.2016','dd.mm.yyyy')),
partition p2016 values less than (to_date('01.01.2017','dd.mm.yyyy')),
partition p2017 values less than (to_date('01.01.2018','dd.mm.yyyy'))
);

The older the partitions, the stronger the compression – that saved about 4 gig of storage space in this case. How about the performance?

SQL> set timing on
SQL> select count(*),avg(amount_sold) from sales_exa where channel_id=2;

  COUNT(*) AVG(AMOUNT_SOLD)
---------- ----------------
  16000000             5000

Elapsed: 00:00:05.83
SQL> select sum(amount_sold) from sales_exa where order_date = to_date('01.01.2011','dd.mm.yyyy');

SUM(AMOUNT_SOLD)
----------------
        40000000

Elapsed: 00:00:00.14

The first Full Table Scan is twice as fast as before now because of Smart Scan, while the Partition Pruning shows about the same effect as before. A real whopper is that one:

SQL> select sum(amount_sold) from sales_exa where ship_date = to_date('01.01.2011','dd.mm.yyyy');

SUM(AMOUNT_SOLD)
----------------
        40000000

Elapsed: 00:00:00.22

That was more than 6 seconds without Exadata before! Why is that so much faster now, almost as good as the Partition Pruning performance?

SQL> connect adam/adam
Connected.

SQL> select sum(amount_sold) from sales_exa where ship_date = to_date('01.01.2011','dd.mm.yyyy');

SUM(AMOUNT_SOLD)
----------------
        40000000

SQL> select name,value/1024/1024 as mb from v$statname
     natural join v$mystat where name='cell physical IO bytes saved by storage index';

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                    1385.64844

I connected newly to initialize v$mystat. The above shows that not only do we need to do less I/O because of the decreased size of the table due to compression and not only did we have a Smart Scan but additionally a Storage Index could be used to further significantly limit the amount of data the cells had to scan through. That is possible because the partitioning on ORDER_DATE leads to a physical sort on disk not only on the ORDER_DATE column but also on the SHIP_DATE column. SHIP_DATE is usually very close to ORDER_DATE. And this ordered way the data is stored is the foundation of the successful usage of Storage Indexes.

Overall, space consumption went down while performance went up </p />
</p></div>

    	  	<div class=

Oracle Indexing Myths (Telling Lies)

I’m currently hard at work fully revising and updating my “Indexing Internals and Best Practices” seminar in time for a series of events I’ll be running in Australia in October/November. This has been a highly popular and acclaimed 2 day seminar that is a must for any DBA, Developer, Solutions Architect or anyone else interested […]

Am I a DBA 3.0 or just an SQL*DBA?

There are currently a lot of new buzz words and re-namings which suggest that our DBA role is changing, most of them escorted with a #cloud hashtag. Oracle Technology Network is now called Oracle Developer Community. Larry Ellison announced the database that does not need to be operated by humans. And people talking about the death of DBA, about the future of DBA, about DBA 3.0,…

Those are all generalizations and universal statements, and I don’t like generalizations. There is not only one type of DBA role. The DBA role in big US companies (where most of those claims come from) is very different than the DBA role in European medium companies (where I’m doing most of my job). The only thing I like with generalization is that a simple counterexample is sufficient to prove that the universal statement is wrong. And I’ll take the example I know the best: mine.

CaptureEM
What do you call DBA role? Is it only server management, or database management? Are you a Dev DBA or an Ops DBA? And when you will go multitenant, will you become a CDB DBA or PDB DBA? And on Engineered Systems, are you still a Database administrator or a Database Machine administrator?

So here is my experience. Let’s flashback to sysdate-8000.
6580a20eb9faaba67ff143dcd7bfcbdc
My first job with an Oracle Database was in 1994. I was working at Alcatel in Paris. The IT was typical of big companies at that time: all IBM, databases were DB2 on mainframe and applications were developed through a several years waterfall cycle from specifications to production. But I was not working there. I was in the accounting department which had his own little IT with some Borland Paradox small applications. This IT department was one senior person, from whom I’ve learned everything, and me, junior developer doing some Borland Paradox things. When came the need for a new application, the idea was to build a prototype in this ‘rebel’ IT service rather than waiting for the whole cycle of development managed by the official IT department.

mainpictWe asked SUN to lend us a workstation. We asked Oracle to lend us Oracle 7.1 database. That was not difficult. Both of them were happy to try to come into this all-IBM company by another way. And Borland had a new product: Delphi so this is where I started to build the prototype. I had everything to learn there: I had never installed a Unix system, I had never installed a database, I even never configured a TCP/IP network. But with the books (no internet then) and the help of my manager (did I say I owe him everything?) we got the environment ready within one month.

aba590f96f7b8138deb71fe28526fb93Today we are talking about Cloud PaaS as the only way to get quickly an environment to start a new development project. The marketing explains that you can get the environment with a few clicks and operational one hour later. But in real life, I know several projects where the environment is not ready after one month, for different reasons (time to choose which service, evaluate the cost, set-it up). Remember that in my case, 23 years ago, it took one or two months but I had a full server, enough storage, available 24/7, with the workstation on my desk. And all that for free.

CaptureDelphiSo I started to develop the application. The business users were there in next room. A short meeting, a small doc, and the conception of the first prototype was ready. Development with Delphi was really quick (remember RAD – Rapid Application Development?) and as soon as I had a usable prototype, one user had access to it, giving me their feedback for future evolutions. We have built something very clever: easy to evolve, fit to business needs, with good performance, and easy to deploy. It has been replaced years later by an application provided by the IT department, but our application was used a the specification.

So, what was my role here? I was clearly a developer and not a DBA. But I was already designing a system, installing a server, creating a database, modeling the data and analyzing performance. When interacting with the database, I was just a DBA doing some SQL. If I want to invent new words myself, I would call that an SQL*DBA, like the name of the tool that was replaced at that time by svrmgrl for the DBA stuff related to the server management. All that has been consolidated into the same tools later: sqlplus does the DBA and Developer stuff, Enterprise manager does both, SQL Developer does both…

During the 20 years later, I’ve evolved to a DBA. I’ve learned new technologies each time, but I don’t think that my DBA role has changed. I’ve done BI (called ‘infocenter’ at that time and ‘datawarehouse’ later) with Business Objects and DB2 and Data Propagator (yes, logical replication and that was in 1996). All this was designed in cooperation with the business end-users. In 2001 I’ve managed terabyte databases full of numbers, doing what we call Big Data today. All maintenance tasks (capacity planning, upgrade, recovery) were done very closely to the business utilization of this data. I administered telecom databases at a time where mobile phone providers came with a new idea every month to be implemented. We would call that ‘agile’ today. I’ve setup databases for quick cloning with transportable tablespaces. I’ve setup continuous integration tests of databases based on flashback technologies and workspace manager. I’ve configured parallel query and XMLDB to do analytics on unstructured data, with better results than MapReduce PoC. Technology evolves, names are modernized, but my DBA role is the same and I still use SQL.

The latest I’ve read about this changing role is Penny Avril interview and it is a very good explanation of all those changes announced. I totally agree with all of that. Except that I see no change in my role there. Let’s take this: DBAs are being asked to understand what businesses do with data rather than just the mechanics of keeping the database healthy and running.
I have always known which business users and which business cases are interacting with the database. My resume always mentioned the business area of each project. I’ve always interacted with end-users and developers for any database I administered, whether there are DEV, TEST or PROD databases. You cannot setup a backup/recovery plan without knowing the data and the business requirements. You cannot upgrade or migrate without interacting with users to test and validate the migration. You cannot address performance without interacting with users and developers. You cannot size the SGA without knowing how the data is used, at the different times of the day or the week.You cannot keep database healthy without knowing how it is used. You cannot build an infrastructure architecture without the support of the business for the software costs.

My DBA job is not a mechanics to keep processes running on a server. That would be a Database System administrator. But we are talking about DataBase Administrator. The major part of my job, and the main reason why I like it, is the human interaction that is around the database. You talk to server/storage/network administrators, you talk to all kind of business users, you talk to developers, you talk to managers, you talk to vendors,… You have to understand OS schedulers, network security, and mutexes, and also have to understand banking, retail, hospital data workflow. Then I don’t worry about the self-driven/no-human-labor part of the DBA role that may be moved to be managed by the cloud provider. Those are boring things that we already automated long time ago. For example, at dbi-services we have included all this automation into the DMK. And this goes further for open source databases with the OpenDB Appliance. Do you think a consulting company would provide this for free to their customers if this automation takes all the DBA job out? The boring and recurring things are automated to avoid errors, and all the intelligent stuff is provided by experienced human DBAs talking SQL with the system. As always.

 

Cet article Am I a DBA 3.0 or just an SQL*DBA? est apparu en premier sur Blog dbi services.

Wrong result with multitenant, dba_contraints and current_schema

Multitenant architecture is not such a big change and this is why I recommend it when you start a project in 12c or if you upgrade to 12.2 – of course after thoroughly testing your application. However, there is a point where you may encounter problems on dictionary queries, because it is really a big change internally. The dictionary separation has several side effects. You should test carefully the queries you do on the dictionary views to get metadata. Here is an example of a bug I recently encountered.

This happened with a combination of things you should not do very often, and not in a critical use case: query dictionary for constraints owned by your current schema, when different than the user you connect with.

I create two users: USER1 and USER2
SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL> grant dba to USER1 identified by USER1 container=current;
Grant succeeded.
SQL> grant dba to USER2 identified by USER2 container=current;
Grant succeeded.

USER1 owns a table which has a constraint:

SQL> connect USER1/USER1@//localhost/PDB1
Connected.
SQL> create table DEMO(dummy constraint pk primary key) as select * from dual;
Table DEMO created.

USER2 can access to the table either by prefixing it with USER1 or by setting the current_schema to USER1

SQL> connect USER2/USER2@//localhost/PDB1
Connected.
SQL> alter session set current_schema=USER1;
Session altered.

Bug

Ok, now imagine you want to read constraint metadata for the current schema you have set:

SQL> select sys_context('USERENV','CURRENT_SCHEMA'), a.*
2 from sys.dba_constraints a
3 where owner = sys_context('USERENV','CURRENT_SCHEMA')
4 /
 
no rows selected

No rows selected is a wrong result here because my current_schema is USER1 and USER1 has constraints:

SQL> select owner,constraint_name
2 from sys.dba_constraints a
3 where owner = 'USER1'
4 /
OWNER CONSTRAINT_NAME
----- ---------------
USER1 PK

So, where’s the problem? Let’s have a look at the execution plan:

SQL_ID 2fghqwz1cktyf, child number 0
-------------------------------------
select sys_context('USERENV','CURRENT_SCHEMA'), a.* from
sys.dba_constraints a where owner =
sys_context('USERENV','CURRENT_SCHEMA')
 
Plan hash value: 1258862619
 
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 0 |00:00:00.32 | 2656 |
| 1 | PARTITION LIST ALL | | 1 | 2 | 0 |00:00:00.32 | 2656 |
|* 2 | EXTENDED DATA LINK FULL| INT$INT$DBA_CONSTRAINTS | 2 | 2 | 0 |00:00:00.32 | 2656 |
--------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
2 - filter((("INT$INT$DBA_CONSTRAINTS"."OBJECT_TYPE#"=4 OR
("INT$INT$DBA_CONSTRAINTS"."OBJECT_TYPE#"=2 AND "INT$INT$DBA_CONSTRAINTS"."ORIGIN_CON_ID"=TO_NUMBER(SY
S_CONTEXT('USERENV','CON_ID')))) AND "OWNER"=SYS_CONTEXT('USERENV','CURRENT_SCHEMA')))

I am in 12.2 and DBA_CONSTRAINTS reads from INT$DBA_CONSTRAINTS which reads from INT$INT$DBA_CONSTRAINTS and in multitenant this view being an extended data view will read from CDB$ROOT and from the current container. This is why we see EXTENDED DATA LINK FULL in the execution plan and up to this point the predicates are correct: “OWNER”=SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’)

The execution through data link is run on each container with parallel processes: they switch to the container and run the underlying query on the view. But when I look at the sql trace of the parallel process running the query on my PDB I can see that the predicate on OWNER has replaced the SYS_CONTEXT(‘USERENV’,’CURRENT_SCHEMA’) with the hardcoded value:

SELECT /*+ NO_STATEMENT_QUEUING RESULT_CACHE (SYSOBJ=TRUE) OPT_PARAM('_ENABLE_VIEW_PDB', 'FALSE') */ OWNER,CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,OBJECT_TYPE#,SEARCH_CONDITION,SEARCH_CONDITION_VC,R_OWNER,R_CONSTRAINT_NAME,DELETE_RULE,STATUS,DEFERRABLE,DEFERRED,VALIDATED,GENERATED,BAD,RELY,LAST_CHANGE,INDEX_OWNER,INDEX_NAME,INVALID,VIEW_RELATED,ORIGIN_CON_ID FROM NO_COMMON_DATA(SYS."INT$INT$DBA_CONSTRAINTS") "INT$INT$DBA_CONSTRAINTS" WHERE ("INT$INT$DBA_CONSTRAINTS"."OBJECT_TYPE#"=4 OR "INT$INT$DBA_CONSTRAINTS"."OBJECT_TYPE#"=2 AND "INT$INT$DBA_CONSTRAINTS"."ORIGIN_CON_ID"=TO_NUMBER('3')) AND "INT$INT$DBA_CONSTRAINTS"."OWNER"=q'"USER2"'

And unfortunately, this value is not the right one: USER2 is my connected user, but not the CURRENT_SCHEMA that I have set. In the same trace, I can see where this value comes from:

select 'q''"' || SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') || '"''' from sys.dual

but it seems that the current_schema was lost through the call to the parallel process and the PDB switch to my container.

Workaround

The problem is easy to workaround. This works:

SQL> select owner,constraint_name
2 from sys.dba_constraints a
3 where owner = ( select sys_context('USERENV','CURRENT_SCHEMA') from dual )
4 /
 
OWNER CONSTRAINT_NAME
----- ---------------
USER1 PK

And anyway, better to get the current schema before and pass it as a bind variable. The bind variables are passed correctly through data link queries:


SQL> variable v varchar2(30)
SQL> exec select sys_context('USERENV','CURRENT_SCHEMA') into :v from dual;
 
PL/SQL procedure successfully completed.
 
SQL> select sys_context('USERENV','CURRENT_SCHEMA'), a.*
2 from sys.dba_constraints a
3 --where owner = sys_context('USERENV','CURRENT_SCHEMA')
4 where owner = :v
5 /

So what?

The multitenant architecture is a real challenge for dictionary views. The dictionary is separated: system metadata in CDB$ROOT and user metadata in PDB. But, because of compatibility with non-CDB architecture, the dictionary views must show both of them, and this is where it becomes complex: what was separated on purpose has now to be merged. And complexity is subject to bugs. If you want to get an idea, have a look at dcore.sql in ORACLE_HOME/rdbms/admin and compare 11g version with 12c ones, with all the evolution in 12.1.0.1, 12.1.0.2 and 12.2.0.1

 

Cet article Wrong result with multitenant, dba_contraints and current_schema est apparu en premier sur Blog dbi services.

AskTOM TV–episode 11

Just a quick note to give the supporting collateral to the latest episode of AskTOM TV.

The question I tackled is this one:

https://asktom.oracle.com/pls/apex/asktom.search?tag=want-to-retrive-numbers-in-words

which was a fun one to answer because it showcases several useful SQL techniques:

  • Using CONNECT to synthesize rows,
  • Using regular expressions to parse text ,
  • Using MULTICAST in Oracle 10g to emulate the native LISTAGG functions from 11g onwards ,
  • Using the hooks into the OCI aggregation facilities to build custom aggregations ,
  • The JSP format mask as a mean to generate numeric words

And here is the entire script from the episode if you want to run it yourself.



drop type string_agg_type;
col column_value format a60
col digit format a60
col concat_str format a60
drop table  t purge;


select to_char(to_date('7','J'),'JSP') from dual;

select to_char(to_date('0','J'),'JSP') from dual;

select
  case x
    when '0' then 'zero'
    when '1' then 'one'
    when '2' then 'two'
    when '3' then 'three'
    when '4' then 'four'
    when '5' then 'five'
    when '6' then 'six'
    when '7' then 'seven'
    when '8' then 'eight'
    when '9' then 'nine'
  end
from ( select '3' x from dual ) 

/

select
  case x
    when '0' then 'zero'
    when '1' then 'one'
    when '2' then 'two'
    when '3' then 'three'
    when '4' then 'four'
    when '5' then 'five'
    when '6' then 'six'
    when '7' then 'seven'
    when '8' then 'eight'
    when '9' then 'nine'
  end
from (
  select substr('123',rownum,1) x
  from dual
  connect by level <= 3
  ) 

/  


create or replace type string_list is table of varchar2(1000);
/

create table t ( x int );
insert into t values (101);
insert into t values (456);
insert into t values (789);

select *
from t,
     table(cast(multiset(
        select substr(to_char(t.x),rownum,1)
        from dual
connect by level <= length(to_char(t.x))) as string_list)
)

/


select
  x,
  digit
from (
  select x, column_value digit
  from t,
       table(cast(multiset(
          select 
            case substr(to_char(t.x),rownum,1)
              when '0' then 'zero'
              when '1' then 'one'
              when '2' then 'two'
              when '3' then 'three'
              when '4' then 'four'
              when '5' then 'five'
              when '6' then 'six'
              when '7' then 'seven'
              when '8' then 'eight'
              when '9' then 'nine'
            end str
          from dual
          connect by level <= length(to_char(t.x))) as string_list)
  )
)

/

create or replace type string_agg_type as object
(
   data  string_list,

   static function
        ODCIAggregateInitialize(sctx IN OUT string_agg_type )
        return number,

   member function
        ODCIAggregateIterate(self IN OUT string_agg_type ,
                             value IN varchar2 )
        return number,

   member function
        ODCIAggregateTerminate(self IN string_agg_type,
                               returnValue OUT  varchar2,
                               flags IN number)
        return number,

   member function
        ODCIAggregateMerge(self IN OUT string_agg_type,
                           ctx2 IN string_agg_type)
        return number
);
/
 
create or replace type body string_agg_type
is

static function ODCIAggregateInitialize(sctx IN OUT string_agg_type)
return number
is
begin
    sctx := string_agg_type( string_list() );
    return ODCIConst.Success;
end;

member function ODCIAggregateIterate(self IN OUT string_agg_type,
                                     value IN varchar2 )
return number
is
begin
    data.extend;
    data(data.count) := value;
    return ODCIConst.Success;
end;

member function ODCIAggregateTerminate(self IN string_agg_type,
                                       returnValue OUT varchar2,
                                       flags IN number)
return number
is
    l_data varchar2(4000);
begin
    for x in ( select column_value from TABLE(data) order by 1 )
    loop
            l_data := l_data || ',' || x.column_value;
    end loop;
    returnValue := ltrim(l_data,',');
    return ODCIConst.Success;
end;

member function ODCIAggregateMerge(self IN OUT string_agg_type,
                                   ctx2 IN string_agg_type)
return number
is
begin
    for i in 1 .. ctx2.data.count
    loop
            data.extend;
            data(data.count) := ctx2.data(i);
    end loop;
    return ODCIConst.Success;
end;

end;
/
 
CREATE or replace
FUNCTION stragg(input varchar2 )
RETURN varchar2
PARALLEL_ENABLE AGGREGATE USING string_agg_type;
/


with source_data as
(
    select
      x,
      digit
    from (
      select x, column_value digit
      from t,
           table(cast(multiset(
              select 
                case substr(to_char(t.x),rownum,1)
                  when '0' then 'zero'
                  when '1' then 'one'
                  when '2' then 'two'
                  when '3' then 'three'
                  when '4' then 'four'
                  when '5' then 'five'
                  when '6' then 'six'
                  when '7' then 'seven'
                  when '8' then 'eight'
                  when '9' then 'nine'
                end str
              from dual
              connect by level <= length(to_char(t.x))) as string_list)
      )
    )
)
select x, stragg(digit) concat_str
from   source_data
group by x
order by 1

/
 
with source_data as
(
    select
      x,
      digit
    from (
      select x, column_value digit
      from t,
           table(cast(multiset(
              select '@'||lpad(level,10,'0')||'~'||
                case substr(to_char(t.x),rownum,1)
                  when '0' then 'zero'
                  when '1' then 'one'
                  when '2' then 'two'
                  when '3' then 'three'
                  when '4' then 'four'
                  when '5' then 'five'
                  when '6' then 'six'
                  when '7' then 'seven'
                  when '8' then 'eight'
                  when '9' then 'nine'
                end str
              from dual
              connect by level <= length(to_char(t.x))) as string_list)
      )
    )
)
select x, stragg(digit)concat_str
from   source_data
group by x
order by 1

/


with source_data as
(
    select
      x,
      digit
    from (
      select x, column_value digit
      from t,
           table(cast(multiset(
              select '@'||lpad(level,10,'0')||'~'||
                case substr(to_char(t.x),rownum,1)
                  when '0' then 'zero'
                  when '1' then 'one'
                  when '2' then 'two'
                  when '3' then 'three'
                  when '4' then 'four'
                  when '5' then 'five'
                  when '6' then 'six'
                  when '7' then 'seven'
                  when '8' then 'eight'
                  when '9' then 'nine'
                end str
              from dual
              connect by level <= length(to_char(t.x))) as string_list)
      )
    )
)
select x, regexp_replace(stragg(digit),'\@[0-9]*\~') concat_str
from   source_data
group by x
order by 1


/




Woo hoo … more OpenWorld 17 content

You bewdy! I managed to score myself a couple more mini-sessions at OpenWorld.

These will be in The Exchange, the re-designed venue for vendors and demonstrations at OpenWorld.

Come along as say Hi. It will be a whirlwind 20 minutes as I try cram as much information into the short time frame as I can.

See you there!

 

image

 

Interview with PeopleSoft Administrator Podcast: Oracle Resource Manager

This week's  PeopleSoft Administrator Podcast includes a few minutes of me talking to Dan and Kyle about Oracle Resource Manager.

(22 September 2017) #99 - Oracle Resource Manager

You can listen to the podcast on psadmin.io, or subscribe with your favourite podcast player, or in iTunes.

OpenWorld 2017–where is the tech content ?

I can’t actually remember how many OpenWorld conferences I have attended. At a guess it would be around 7 or 8. Every year there is a criticism that there is “not enough technical content”.

Well I’m calling “fake news” on that. I can’t remember a time that I’ve struggled to find sessions that I’m interested in. Don’t get me wrong – I would love it if there was double the technical content especially in the core database area, but there’s a pretty simple reason for that. I’m a technically-oriented database-oriented kinda guy Smile. I’m sure that as I type this post, there is probably an Oracle Apps Functional Analyst preparing a blog post about how database content has gone overboard and there should be more Apps content. It’s a fact of life that you can’t please everyone.

“But I looked at the agenda, and a lot of it says ‘in the cloud'” I hear you exclaim. “It will all just be advertising!”.

Yeah, I’m calling “fake news” on that claim as well. Think about that for a second. If I’m submitting a talk for a conference that has a heavy emphasis on cloud architectures, then “Duh!”, of course I’m going to have “cloud” in the title or the synopsis. Because one of the prerequisites for giving a talk at the conference is getting the damn thing accepted ! I suspect some of the cynics that claim the agenda has not enough technical content perhaps have not taken the effort to actually study it.

But you need not worry… Some kind person has done that for you Smile. Having enough technical content is the least of your problems if you’re attending OpenWorld 2017. I’ve been through the session catalog and scraped out those sessions that I have an interest in, and even from my narrow database-centric perspective, I still have a massively tough task of deciding between multiple clashing sessions for almost every single timeslot. And as an Oracle employee, it will be even harder, because I have to wait until all of the conference attendees have entered the session, and only then do I get in if there are a few seats still vacant!

So here’s my list of sessions I’d love to attend at OpenWorld. Click on the code for each to get more details, or (if you are an attendee) to add them to your session builder … you lucky devils! Smile

Sunday 9:45

  • Franck Pachot Multitenant: Trust It by Understanding Beyond What Is Documented SUN1051 Marriott Marquis (Golden Gate Level) – Golden Gate A 01-OCT-2017 09:45 01-OCT-2017 09:45
  • Gary Gordhamer Oracle and NLS SUN5637 Moscone South – Room 155 01-OCT-2017 09:45 01-OCT-2017 09:45
  • Jason Arneil Cloud Networking for Beginners SUN1230 Marriott Marquis (Yerba Buena Level) – Nob Hill A/B 01-OCT-2017 09:45 01-OCT-2017 09:45

Sunday 10:45

  • Adeesh Fulay Docker 101 for Oracle DBAs SUN5617 Moscone South – Room 151 01-OCT-2017 10:45 01-OCT-2017 10:45
  • Francis Mignault Cloudy with a Chance of Oracle Application Express SUN6246 Marriott Marquis (Yerba Buena Level) – Salon 4-6 01-OCT-2017 10:45 01-OCT-2017 10:45
  • Debra Lilley,Ralf Koelling,Jan-Peter Timmermann EOUC Database ACES Share Their Favorite Database Things SUN2413 Marriott Marquis (Golden Gate Level) – Golden Gate A 01-OCT-2017 10:45 01-OCT-2017 10:45
  • Peter Koletzke Leveraging Oracle Database SQL and PL/SQL to Simplify User Interface App Dev SUN6252 Marriott Marquis (Yerba Buena Level) – Salon 10/11 01-OCT-2017 10:45 01-OCT-2017 10:45
  • Sai Janakiram Penumuru A 360-Degree View of Oracle Database Cloud Service SUN5296 Moscone South – Room 160 01-OCT-2017 10:45 01-OCT-2017 10:45
  • Stephen Kost The Thrifty DBA Does Database Security SUN5690 Moscone South – Room 159 01-OCT-2017 10:45 01-OCT-2017 10:45
  • Wayne Martin,Arun Sar Ingesting Data from Oracle Database 12cR2 in a Hadoop System SUN5615 Moscone South – Room 157 01-OCT-2017 10:45 01-OCT-2017 10:45

Sunday 11:45

  • Alex Zaballa Let’s Get Started with Oracle Database Cloud SUN1871 Moscone South – Room 314 01-OCT-2017 11:45 01-OCT-2017 11:45
  • Debra Lilley,Ralf Koelling,Jan-Peter Timmermann EOUC Developer Champions Show the Cool Tech They Use SUN2415 Marriott Marquis (Golden Gate Level) – Golden Gate A 01-OCT-2017 11:45 01-OCT-2017 11:45
  • Harris Baskaran SSL Securing Oracle Database: Goodbye Passwords SUN5642 Moscone South – Room 159 01-OCT-2017 11:45 01-OCT-2017 11:45
  • James Czuprynski Oracle Database In-Memory: Adventures with SwingBench TPC-DS SUN5644 Moscone South – Room 153 01-OCT-2017 11:45 01-OCT-2017 11:45
  • Yalim Gerger Version Control for PL/SQL SUN5700 Moscone South – Room 157 01-OCT-2017 11:45 01-OCT-2017 11:45

Sunday 12:45

  • Ludovico Caldara Get the Most Out of Oracle Data Guard SUN1399 Marriott Marquis (Yerba Buena Level) – Nob Hill A/B 01-OCT-2017 12:45 01-OCT-2017 12:45
  • Janis Griffin Need for Speed: Top Five Oracle Performance Tuning Tips SUN5647 Moscone South – Room 153 01-OCT-2017 12:45 01-OCT-2017 12:45
  • Oren Nakdimon Write Less (Code) with More: Oracle Database 12c New Features SUN2990 Marriott Marquis (Golden Gate Level) – Golden Gate A 01-OCT-2017 12:45 01-OCT-2017 12:45
  • Riyaj Shamsudeen Oracle Automatic Storage Management and Internals SUN5682 Moscone South – Room 155 01-OCT-2017 12:45 01-OCT-2017 12:45

Sunday 13:45

  • Brendan Tierney SQL: One Language to Rule All Your Data SUN1238 Marriott Marquis (Golden Gate Level) – Golden Gate A 01-OCT-2017 13:45 01-OCT-2017 13:45
  • Jonathan Dixon Alexa, How Do You Work with Oracle REST Data Services? SUN6251 Marriott Marquis (Yerba Buena Level) – Salon 4-6 01-OCT-2017 13:45 01-OCT-2017 13:45
  • Julian Dontcheff DBA Types SUN1111 Marriott Marquis (Yerba Buena Level) – Nob Hill A/B 01-OCT-2017 13:45 01-OCT-2017 13:45
  • Pini Dibask Database Consolidation Using Oracle’s Multitenant Architecture SUN5677 Moscone South – Room 153 01-OCT-2017 13:45 01-OCT-2017 13:45
  • Simon Pane,Ivica Arsov Securing Your DBA Monitoring and Backup Scripts SUN5687 Moscone South – Room 159 01-OCT-2017 13:45 01-OCT-2017 13:45
  • Stewart Bryson Kafka, Data Streaming, and Analytics Microservices SUN5693 Moscone South – Room 157 01-OCT-2017 13:45 01-OCT-2017 13:45
  • Tariq Farooq,francisco munoz alvarez Under a Cloudy Sky: The Different Types of Skies SUN4396 Moscone South – Room 313 01-OCT-2017 13:45 01-OCT-2017 13:45
  • Tim Gorman Linux/UNIX Tools for Oracle DBAs SUN5694 Moscone South – Room 151 01-OCT-2017 13:45 01-OCT-2017 13:45

Sunday 14:45

  • Alex Nuijten Life After Oracle Forms: The Move to Oracle Application Express SUN6274 Marriott Marquis (Yerba Buena Level) – Salon 4-6 01-OCT-2017 14:45 01-OCT-2017 14:45
  • Ami Aharonovich Exploring Oracle Database Partitioning New Features and Best Practices SUN5065 Marriott Marquis (Golden Gate Level) – Golden Gate A 01-OCT-2017 14:45 01-OCT-2017 14:45
  • Markus Flechtner Application Containers: Multitenancy for Database Applications SUN3110 Marriott Marquis (Yerba Buena Level) – Nob Hill A/B 01-OCT-2017 14:45 01-OCT-2017 14:45
  • Nelson Calero Redefining Tables Online Without Surprises SUN3541 Moscone South – Room 314 01-OCT-2017 14:45 01-OCT-2017 14:45

Sunday 15:45

  • Heli Helskyaho,Elise Valin-Raki,Tuomas Pystynen Oracle Private Cloud Built with Engineered Systems in Fennia Insurance Company SUN4487 Marriott Marquis (Golden Gate Level) – Golden Gate A 01-OCT-2017 15:45 01-OCT-2017 15:45
  • John King Oracle Database 12c New Features for Developers and DBAs SUN5654 Moscone South – Room 157 01-OCT-2017 15:45 01-OCT-2017 15:45
  • Martin D’Souza Explore Oracle Application Express APIs SUN6244 Marriott Marquis (Yerba Buena Level) – Salon 4-6 01-OCT-2017 15:45 01-OCT-2017 15:45
  • Mauro Pagano Adaptive Plans on Oracle Database 12c SUN5664 Moscone South – Room 155 01-OCT-2017 15:45 01-OCT-2017 15:45
  • Rita Nunez Managing Oracle Database in Oracle Database Exadata Express Cloud Service SUN3575 Moscone South – Room 314 01-OCT-2017 15:45 01-OCT-2017 15:45

Monday 11:00

  • Burt Clouse,Michael Sukhenko,Sreedhar Bobbadi Database Management with Gold Images: Easily Provision, Patch, and Upgrade CON6706 Moscone West – Room 3004 02-OCT-2017 11:00 02-OCT-2017 11:00
  • Geertjan Wielenga,Lucas Jellema,John Brock,Andrejus Baranovskis,Filip Huysmans,Luc Bors Getting Started with Oracle JavaScript Extension Toolkit CON3935 Moscone West – Room 3001 02-OCT-2017 11:00 02-OCT-2017 11:00
  • Mohamed Taman Effective Design of RESTful APIs CON1206 Moscone West – Room 2005 02-OCT-2017 11:00 02-OCT-2017 11:00
  • Perry Harrington,Mirko Ortensi MySQL DBA Primer HOL7298 Hilton San Francisco Union Square (Ballroom Level) – Continental Ballroom 7 02-OCT-2017 11:00 02-OCT-2017 11:00
  • Sergiusz Wolicki New Paradigm for Case-Sensitivity and Collation on Oracle Database 12c Release 2 CON6551 Moscone West – Room 3008 02-OCT-2017 11:00 02-OCT-2017 11:00

Monday 11:30

  • I’ll be in the theatre area in the Demo Grounds doing a lightning talk on Multi-tenant and In-Memory in 12c Release 2

Monday 12:15

  • Geertjan Wielenga,Lucas Jellema,John Brock,Andrejus Baranovskis,Filip Huysmans,Luc Bors Java Versus JavaScript for the Enterprise? CON6063 Moscone West – Room 2007 02-OCT-2017 12:15 02-OCT-2017 12:15
  • John Heimann How Safe Am I? Choosing the Right Security Tool for the Job CON6303 Moscone South – Room 313 02-OCT-2017 12:15 02-OCT-2017 12:15
  • Shay Shmeltzer,Patrik Frankovic Introduction to Oracle’s Visual Low-Code Development Platform CON6510 Moscone West – Room 3001 02-OCT-2017 12:15 02-OCT-2017 12:15

Monday 13:15

  • Avi Miller,Marshall Weymouth,Brian Grad Tips for Securing Your Docker Containers CON6462 Marriott Marquis (Yerba Buena Level) – Salon 13 02-OCT-2017 13:15 02-OCT-2017 13:15
  • Bruce Lowenthal,Juan Perez-Etchegoyen To Patch or Not to Patch: Answering the CPU Question CON6302 Moscone South – Room 313 02-OCT-2017 13:15 02-OCT-2017 13:15
  • Eleanor Meritt New Release Model for Oracle Database CON6550 Moscone West – Room 3014 02-OCT-2017 13:15 02-OCT-2017 13:15
  • Jeff Smith What’s New in Oracle REST Data Services and Oracle SQL Developer CON6669 Moscone West – Room 3012 02-OCT-2017 13:15 02-OCT-2017 13:15

Monday 14:15

  • George Lumpkin What’s New in Oracle Database 12c for Data Warehousing CON6643 Moscone West – Room 3010 02-OCT-2017 14:15 02-OCT-2017 14:15
  • Heli Helskyaho,Elise Valin-Raki,Tuomas Pystynen Design Your Databases Straight to the Cloud CON1846 Moscone West – Room 3007 02-OCT-2017 14:15 02-OCT-2017 14:15
  • Juan Loaiza Oracle Exadata: Disruptive New Memory and Cloud Technologies CON6661 Moscone West – Room 3014 02-OCT-2017 14:15 02-OCT-2017 14:15
  • Roel Hartman Docker for Dummies CON1049 Moscone West – Room 3005 02-OCT-2017 14:15 02-OCT-2017 14:15

Monday 15:15

  • Alan Williams,Keith Wilcox NEW FEATURE! Centralized Database User Management Using Active Directory CON6574 Moscone West – Room 3011 02-OCT-2017 15:15 02-OCT-2017 15:15
  • Jeff Smith Oracle SQL Developer: GUI, CLI, or Browser? CON6670 Moscone West – Room 3012 02-OCT-2017 15:15 02-OCT-2017 15:15


Monday 15:30

  • I’ll be in the theatre area in the Demo Grounds doing a lightning talk on hierarchy SQL and the WITH clause

Monday 16:45

  • Guido Schmutz Apache Kafka: Scalable Message Processing and More CON6156 Moscone West – Room 2004 02-OCT-2017 16:30 02-OCT-2017 16:30
  • Chaitanya Koratamaddi Oracle Application Express 5.2 New Features CON6739 Moscone West – Room 3002 02-OCT-2017 16:45 02-OCT-2017 16:45
  • Chaitanya Pydimukkala,Sai Devabhaktuni,Bobby Curtis Oracle Data Integration Platform Empowers Enterprise-Grade Big Data Solutions CON6893 Marriott Marquis (Golden Gate Level) – Golden Gate B 02-OCT-2017 16:45 02-OCT-2017 16:45
  • George Lumpkin Data Warehousing for Everybody: Oracle Data Warehouse Cloud Service CON6647 Moscone West – Room 3004 02-OCT-2017 16:45 02-OCT-2017 16:45
  • Julian Dontcheff Oracle RAC 12c Release 2: Operational Best Practices CON6684 Moscone West – Room 3014 02-OCT-2017 16:45 02-OCT-2017 16:45
  • Lisa Considine Five Inexpensive Tricks to Accelerate Oracle Database Using x86 CON7580 Marriott Marquis (Yerba Buena Level) – Nob Hill A/B 02-OCT-2017 16:45 02-OCT-2017 16:45
  • Nancy Ikeda,Christopher Jones,Anthony Tuininga Oracle Net Services 12c: Best Practices for Database Performance and Scalability CON6718 Moscone West – Room 3011 02-OCT-2017 16:45 02-OCT-2017 16:45
  • Santanu Datta,Christian Shay Oracle and .NET: Intro and What’s New CON6722 Moscone West – Room 3012 02-OCT-2017 16:45 02-OCT-2017 16:45

Monday 17:45

  • Lawrence Mcintosh,Ken Kutzer Best Practices for implementing Database Security CON6434 Marriott Marquis (Yerba Buena Level) – Nob Hill A/B 02-OCT-2017 17:45 02-OCT-2017 17:45
  • Michael Hichwa Low-Code Rapid Application Development CON6685 Moscone West – Room 3002 02-OCT-2017 17:45 02-OCT-2017 17:45
  • Pedro Lopes NEW! Database Security Assessment Tool Discovers Top Security Risks CON6575 Moscone West – Room 3011 02-OCT-2017 17:45 02-OCT-2017 17:45

Tuesday 11:00

  • Chris Richardson ACID Is So Yesterday: Maintaining Data Consistency with Sagas CON2832 Moscone West – Room 2005 03-OCT-2017 11:00 03-OCT-2017 11:00
  • Hermann Baer,Yasin Baskan What Everybody Should Know About Oracle Partitioning CON6638 Moscone West – Room 3008 03-OCT-2017 11 03-OCT-2017 11:00
  • Julian Dontcheff,Anil Nair,Markus Michalewicz Oracle RAC 12c Release 2 and Cluster Architecture Internals CON6690 Moscone West – Room 3004 03-OCT-2017 11 03-OCT-2017 11:00

Tuesday 11:30

  • Dominic Giles, Maria Colgan Oracle Database and the Internet of Things CON7121 Moscone West – Room 3011 03-OCT-2017 11:30 03-OCT-2017 11:30
  • Colm Divilly Oracle REST Data Services/Oracle Database REST API CON6662 Moscone West – Room 3012 03-OCT-2017 11:30 03-OCT-2017 11:30
  • Gp Gongloor,Sriram Vrinda,Konstantin Kerekovski SQL Tuning for Expert DBAs CON7091 Moscone West – Room 3018 03-OCT-2017 11:30 03-OCT-2017 11:30
  • Tirthankar Lahiri,Ananth Raghavan,Doug Hood Oracle Database In-Memory Deep Dive: Past, Present, and Future CON6584 Moscone West – Room 3014 03-OCT-2017 11:30 03-OCT-2017 11:30

Tuesday 12:45

  • Bryn Llewellyn Ten Rules for Doing a PL/SQL Performance Experiment CON7639 Moscone West – Room 3011 03-OCT-2017 12:45 03-OCT-2017 12:45
  • Colm Divilly Securing Your RESTful Services CON6667 Moscone West – Room 3012 03-OCT-2017 12:45 03-OCT-2017 12:45

Tuesday 13:15

  • Connor Mcdonald Leaner, Faster Code with Advanced SQL Techniques CON3557 Moscone West – Room 2002 03-OCT-2017 13:15 03-OCT-2017 13:15 RubberStamp-Must-See

Tuesday 15:00

  • Dan Mcghan Creating RESTful Web Services the Easy Way with Node.js CON1242 Moscone West – Room 2002 03-OCT-2017 15:00 03-OCT-2017 15:00

Tuesday 15:45

  • Dan Mcghan,Chris Saxon 12 Things Developers Will Love About Oracle Database 12c Release 2 CON6734 Moscone West – Room 3014 03-OCT-2017 15:45 03-OCT-2017 15:45
  • Martin Gubar Oracle Big Data SQL: Roadmap to High-Performance Data Virtualization CON6644 Moscone West – Room 3010 03-OCT-2017 15:45 03-OCT-2017 15:45

Tuesday 16:45

  • Stephan Haisley,Sai Krishnamurthy Maximizing Availability for Oracle GoldenGate Microservices CON6570 Moscone West – Room 3014 03-OCT-2017 16:45 03-OCT-2017 16:45

Tuesday 17:45

  • Connor Mcdonald Using Advanced SQL Techniques for Faster Applications CON6735 Moscone West – Room 3014 03-OCT-2017 17:45 03-OCT-2017 17:45 RubberStamp-Must-See

Wednesday 09:45

  • Nancy Ikeda,Christopher Jones,Anthony Tuininga Python and Oracle Database 12c: Scripting for the Future HOL7605 Hilton San Francisco Union Square (Ballroom Level) – Continental Ballroom 6 04-OCT-2017 09:45 04-OCT-2017 09:45

Wednesday 11:00

  • Greg Drobish Oracle Database 12c Storage Features that Cut Database Tuning Time by 67 Percent CON4671 Marriott Marquis (Yerba Buena Level) – Nob Hill C/D 04-OCT-2017 11:00 04-OCT-2017 11:00
  • Juergen Mueller,Andrew Holdsworth Why the Fastest IO Is Still the One You Don’t Do CON6560 Moscone West – Room 3012 04-OCT-2017 11:00 04-OCT-2017 11:00
  • Mark Drake NoSQL Development and JSON Support in the Next Generation of Oracle Database CON6636 Moscone West – Room 3011 04-OCT-2017 11:00 04-OCT-2017 11:00
  • Mark Fallon Inside the Head of a Database Hacker CON6572 Moscone West – Room 3014 04-OCT-2017 11:00 04-OCT-2017 11:00
  • Thomas Baby Oracle Multitenant: Ask the Architects CON6731 Moscone West – Room 3010 04-OCT-2017 11:00 04-OCT-2017 11:00
  • Tim Goh,Michael Schulman NoSQL Data Modeling for RDBMS Users CON6545 Moscone West – Room 3008 04-OCT-2017 11:00 04-OCT-2017 11:00

Wednesday 12:00

  • Gregg Christman Get the Best Out of Oracle Compression CON6577 Moscone West – Room 3006 04-OCT-2017 12:00 04-OCT-2017 12:00
  • Pravin Jha Oracle Database 12.2: Lockdown Profiles CON6424 Moscone West – Room 3016 04-OCT-2017 12:00 04-OCT-2017 12:00
  • Gerald Venzl, Maria Colgan Oracle Database Features Every Developer Should Know About CON6558 Moscone West – Room 3020 04-OCT-2017 12:00 04-OCT-2017 12:00

Wednesday 13:00

  • Connor Mcdonald War of the Worlds: DBAs Versus Developers CON6737 Moscone West – Room 3014 04-OCT-2017 13:00 04-OCT-2017 13:00 RubberStamp-Must-See

Wednesday 14:00

  • Mike Dietrich,Jay Barnhart Migrate a 100 TB Database Cross-Platform in Less Than a Day CON6467 Moscone West – Room 3004 04-OCT-2017 14:00 04-OCT-2017 14:00
  • Dominic Giles, Maria Colgan Five Things You Might Not Know About Oracle Database CON6631 Moscone West – Room 3020 04-OCT-2017 14:00 04-OCT-2017 14:00

Wednesday 14:45

  • Dan Mcghan Building Real-time Data in Web Applications with Node.js CON1240 Moscone West – Room 2003 04-OCT-2017 14:45 04-OCT-2017 14:45

Wednesday 15:30

  • Bryn Llewellyn Guarding Your Data Behind a Hard Shell PL/SQL API CON6633 Moscone West – Room 3014 04-OCT-2017 15:30 04-OCT-2017 15:30

Wednesday 16:30

  • Scott Rotondo Top Five Tips for Building Secure Database Applications CON6578 Moscone West – Room 3011 04-OCT-2017 16:30 04-OCT-2017 16:30
  • Ashish Agrawal SQL Analytics: Using Automatic Workload Repository and Active Session History CON7065 Moscone West – Room 3018 04-OCT-2017 17 04-OCT-2017 17:00
  • Tim Hall Make the RDBMS Relevant Again with RESTful Web Services and JSON CON7358 Moscone West – Room 2002 04-OCT-2017 16:30 04-OCT-2017 16:30

Wednesday 17:30

  • John King Blockchain? What Is Blockchain? Why Do I Care? CON2276 Moscone West – Room 2010 04-OCT-2017 17:30 04-OCT-2017 17:30
  • Lucas Jellema,Jurgen Leijer JavaScript to SQL: Diagnose Application Issues Across a Hybrid Infrastructure CON6914 Moscone West – Room 2024 04-OCT-2017 17:30 04-OCT-2017 17:30
  • Stewart Bryson Practical Advice from a Developer Using a Cloud-Based DevOps Environment CON7377 Moscone West – Room 2002 04-OCT-2017 17:30 04-OCT-2017 17:30

Thursday 10:45

  • Beda Hammerschmidt,Vikas Arora,Maxim Orgiyan The Top 10 Dos and Don’ts of JSON Processing in a Database CON4647 Marriott Marquis (Golden Gate Level) – Golden Gate C1/C2 05-OCT-2017 10:45 05-OCT-2017 10:45

Thursday 13:45

  • Brendan Tierney Is SQL the Best Language for Statistics and Machine Learning? CON7350 Marriott Marquis (Golden Gate Level) – Golden Gate C3 05-OCT-2017 13:45 05-OCT-2017 13:45


Thursday 14:45

  • Heli Helskyaho,Elise Valin-Raki,Tuomas Pystynen The Basics of Machine Learning CON7354 Marriott Marquis (Golden Gate Level) – Golden Gate C1/C2 05-OCT-2017 14:45 05-OCT-2017 14:45
  • Lucas Jellema,Jurgen Leijer How End User Monitoring and Selenium Can Help Troubleshoot Customer issues CON6912 Marriott Marquis (Golden Gate Level) – Golden Gate C3 05-OCT-2017 14:45 05-OCT-2017 14:45

 

And as I mentioned before…the whole AskTOM team will be at OpenWorld for the week