Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Earn Money on Your WordPress.com Website with Premium Content and Paid Newsletters

Make money while you build an engaged following for your website: use the WordPress.com Premium Content block to create monthly and yearly paid memberships that give followers access to the premium content of your choice – text, photos, videos, and more. You can now automatically deliver your new premium posts right to subscribers’ inboxes as a paid newsletter!

Anything that you can publish on a WordPress.com site with a block can become part of your premium content offering. Summer recipes, podcasts, fitness instruction videos, photography portfolios, music samples, access to digital downloads, poetry, political remarks —  people on WordPress.com include all of that and more in Premium Content blocks, and they make money for sharing their expertise.

Premium Content block examples

Premium Content memberships also offer you a new way to engage your most engaged fans. Create membership tiers with different costs and content access levels. Craft targeted messaging for each one. Want to send special emails and offers or ask for suggestions about what kind of content you might create next? You control what content and messaging goes to your paying members via Premium Content blocks.

You focus on creating amazing content. We’ll handle the credit and debit card payment processing, reporting, and providing the right access for paying members to view your premium content or get your newsletters.

Launch your first membership

  • To use Premium Content blocks, you’ll need a WordPress.com website with any paid plan — Personal, Premium, Business, or eCommerce.
  • Create a new page or post, and add a Premium Content block.
https://en-blog.files.wordpress.com/2020/06/screen-shot-2020-04-29-at-2.... 648w, https://en-blog.files.wordpress.com/2020/06/screen-shot-2020-04-29-at-2.... 150w, https://en-blog.files.wordpress.com/2020/06/screen-shot-2020-04-29-at-2.... 300w" sizes="(max-width: 325px) 100vw, 325px" />
  • To set up your first paid membership or subscription, create a Stripe account (if you don’t have one already). Stripe is the company we’ve partnered with to process credit and debit card payments in a safe, secure, and speedy way.
  • Set the cost of the membership and decide whether people will pay monthly or yearly. Want to offer multiple kinds of memberships with access to different kinds of content? Add separate Premium Content blocks for each one to create multiple membership options.
https://en-blog.files.wordpress.com/2020/06/block-settings.png?w=847 847w, https://en-blog.files.wordpress.com/2020/06/block-settings.png?w=150 150w, https://en-blog.files.wordpress.com/2020/06/block-settings.png?w=300 300w, https://en-blog.files.wordpress.com/2020/06/block-settings.png?w=768 768w" sizes="(max-width: 425px) 100vw, 425px" />
  • Add content that’s included with this particular membership to the Premium Content block. You’ll add content using blocks, and can add as many blocks within the Premium Content block as you like.
  • To let followers opt into receiving new premium content via email, turn on the “Posts via email” option in your paid membership plan settings. Your membership payments are processed by a WordPress.com feature called Recurring Payments, which powers seamless credit and debit card processing for the Premium Content block.
https://en-blog.files.wordpress.com/2020/06/premium-content-edit-email-o... 728w, https://en-blog.files.wordpress.com/2020/06/premium-content-edit-email-o... 148w, https://en-blog.files.wordpress.com/2020/06/premium-content-edit-email-o... 297w" sizes="(max-width: 364px) 100vw, 364px" />

And just like that, you’re a membership organization! Share your new membership offerings with your network — social media, email, and word of mouth are all great places to start — and start building your following along with your stable, recurring revenue.

Oracle 12c – pre-built join index

By Franck Pachot

.
This post is part of a series of small examples of recent features. I’m running this in the Oracle 20c preview in the Oracle Cloud. I have created a few tables in the previous post with a mini-snowflake scheme: a fact table CASES with the covid-19 cases per country and day. And a dimension hierarchy for the country with COUNTRIES and CONTINENTS tables.

This title may look strange for people used to Oracle. I am showing the REFRESH FAST ON STATEMENT Materialized View clause here, also known as “Synchronous Refresh for Materialized Views”. This name makes sense only when you already know materialized views, complete and fast refreshes, on commit and on-demand refreshes… But that’s not what people will look for. Indexes are also refreshed by the statements, synchronously. Imagine that they were called “Synchronous Refresh for B*Trees”, do you think they would have been so popular?

A materialized view, like an index, is a redundant structure where data is stored in a different physical layout in order to be optimal for alternative queries. For example, you ingest data per date (which is the case in my covid-19 table – each day a new row with the covid-19 cases per country). But if I want to query all points for a specific country, those are scattered though the physical segment that is behind the table (or the partition). With an index on the country_code, I can identify easily one country, because the index is sorted on the country. I may need to go to the table to get the rows, and that is expensive, but I can avoid it by adding all the attributes in the index. With Oracle, as with many databases, we can build covering indexes, for real index-only access, even if they don’t mention those names.

But with my snowflake schema, I’ll not have the country_code in the fact table and I have to join to a dimension. This is more expensive because the index on the country_name will get the country_id and then I have to go to an index on the fact table to get the rows for this country_id. When it comes to joins, I cannot index the result of the join (I’m skipping bitmap join indexes here because I’m talking about covering indexes). What I would like is an index with values from multiple tables.

A materialized view can achieve much more than an index. We can build the result of the join in one table. And no need for event sourcing or streaming here to keep it up to date. No need to denormalize and risk inconsistency. When NoSQL pioneers tell you that storage is cheap and redundancy is the way to scale, just keep your relational database for integrity and build materialized views on top. When they tell you that joins are expensive, just materialize them upfront. Before 12c, keeping those materialized views consistent with the source required either:

  1. materialized view logs which is similar to event sourcing except that ON COMMIT refresh is strongly consistent
  2. partition change tracking which is ok for bulk changes, when scaling big data

This is different from indexes which are maintained immediately: when you update the row, the index is synchronized because your session has the values and the rowid and can go directly to update the index entry.

refresh fast on statement

In 12c you have the benefit from both: index-like fast maintenance with rowid access, and the MView possibility of querying pre-build joins. Here is an example on the tables created in the previous post.


SQL> 
 create materialized view flatview refresh fast on statement as
  select daterep,continent_name,country_name,cases from cases join countries using(country_id) join continents 
  using(continent_id) where cases>0;

select daterep,continent_name,country_name,cases from cases join countries using(country_id) join continents using(continent_id) where cases>0
                                                                                                                                             *
ERROR at line 2:
ORA-12015: cannot create a fast refresh materialized view from a complex query

There are some limitations when we want fast refresh and we have a utility to help us understand what we have to change or add in our select clause.

explain_mview

I need to create the table where the messages will be written to by this utility:


@ ?/rdbms/admin/utlxmv

SQL> 
set sqlformat ansiconsole
SQL> 
set pagesize 10000

This has created mv_capabilities_table and I can run dbms_mview.explain_mview() now.

Here is the call, with the select part of the materialized view:


SQL> 
exec dbms_mview.explain_mview('-
  select daterep,continent_name,country_name,cases from cases join countries using(country_id) join continents using(continent_id) where cases>0-
  ');

PL/SQL procedure successfully completed.

SQL> 
select possible "?",capability_name,related_text,msgtxt from mv_capabilities_table where capability_name like 'REFRESH_FAST%' order by seq;

   ?                  CAPABILITY_NAME    RELATED_TEXT                                                                 MSGTXT
____ ________________________________ _______________ ______________________________________________________________________
N    REFRESH_FAST
N    REFRESH_FAST_AFTER_INSERT                        inline view or subquery in FROM list not supported for this type MV
N    REFRESH_FAST_AFTER_INSERT                        inline view or subquery in FROM list not supported for this type MV
N    REFRESH_FAST_AFTER_INSERT                        view or subquery in from list
N    REFRESH_FAST_AFTER_ONETAB_DML                    see the reason why REFRESH_FAST_AFTER_INSERT is disabled
N    REFRESH_FAST_AFTER_ANY_DML                       see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
N    REFRESH_FAST_PCT                                 PCT FAST REFRESH is not possible if query contains an inline view

SQL> 
rollback;

Rollback complete.

“inline view or subquery in FROM list not supported for this type MV” is actually very misleading. I use ANSI joins and they are translated to query blocks and this is not supported.

No ANSI joins

I rewrite it with the old join syntax:


SQL> 
exec dbms_mview.explain_mview('-
  select daterep,continent_name,country_name,cases from cases , countries , continents where cases.country_id=countries.country_id and countries.continent_id=continents.continent_id and cases>0-
  ');

PL/SQL procedure successfully completed.

SQL> 
select possible "?",capability_name,related_text,msgtxt from mv_capabilities_table where capability_name like 'REFRESH_FAST%' order by seq;

   ?                  CAPABILITY_NAME       RELATED_TEXT                                                                      MSGTXT
____ ________________________________ __________________ ___________________________________________________________________________
N    REFRESH_FAST
N    REFRESH_FAST_AFTER_INSERT        CONTINENTS         the SELECT list does not have the rowids of all the detail tables
N    REFRESH_FAST_AFTER_INSERT        DEMO.CASES         the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.COUNTRIES     the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.CONTINENTS    the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_ONETAB_DML                       see the reason why REFRESH_FAST_AFTER_INSERT is disabled
N    REFRESH_FAST_AFTER_ANY_DML                          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
N    REFRESH_FAST_PCT                                    PCT is not possible on any of the detail tables in the materialized view

SQL> 
rollback;

Rollback complete.

Now I need to add the ROWID of the table CONTINENTS in the materialized view.

ROWID for all tables

Yes, as I mentioned, the gap between indexes and materialized views is shorter. The REFRESH FAST ON STATEMENT requires access by rowid to update the materialized view, like when a statement updates an index.


SQL> 
exec dbms_mview.explain_mview('-
  select continents.rowid continent_rowid,daterep,continent_name,country_name,cases from cases , countries , continents where cases.country_id=countries.country_id and countries.continent_id=continents.continent_id and cases>0-
  ');

PL/SQL procedure successfully completed.

SQL> 
select possible "?",capability_name,related_text,msgtxt from mv_capabilities_table where capability_name like 'REFRESH_FAST%' order by seq;

   ?                  CAPABILITY_NAME       RELATED_TEXT                                                                      MSGTXT
____ ________________________________ __________________ ___________________________________________________________________________
N    REFRESH_FAST
N    REFRESH_FAST_AFTER_INSERT        COUNTRIES          the SELECT list does not have the rowids of all the detail tables
N    REFRESH_FAST_AFTER_INSERT        DEMO.CASES         the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.COUNTRIES     the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.CONTINENTS    the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_ONETAB_DML                       see the reason why REFRESH_FAST_AFTER_INSERT is disabled
N    REFRESH_FAST_AFTER_ANY_DML                          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
N    REFRESH_FAST_PCT                                    PCT is not possible on any of the detail tables in the materialized view
SQL> 
rollback;

Rollback complete.

Now, the ROWID for COUNTRIES.

I continue the and finally I’ve added ROWID for all tables involved:


SQL> 
exec dbms_mview.explain_mview('-
  select continents.rowid continent_rowid,daterep,continent_name,country_name,cases from cases , countries , continents where cases.country_id=countries.country_id and countries.continent_id=continents.continent_id and cases>0-
  ');

PL/SQL procedure successfully completed.

SQL> 
select possible "?",capability_name,related_text,msgtxt from mv_capabilities_table where capability_name like 'REFRESH_FAST%' order by seq;

   ?                  CAPABILITY_NAME       RELATED_TEXT                                                                      MSGTXT
____ ________________________________ __________________ ___________________________________________________________________________
N    REFRESH_FAST
N    REFRESH_FAST_AFTER_INSERT        COUNTRIES          the SELECT list does not have the rowids of all the detail tables
N    REFRESH_FAST_AFTER_INSERT        DEMO.CASES         the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.COUNTRIES     the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.CONTINENTS    the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_ONETAB_DML                       see the reason why REFRESH_FAST_AFTER_INSERT is disabled
N    REFRESH_FAST_AFTER_ANY_DML                          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
N    REFRESH_FAST_PCT                                    PCT is not possible on any of the detail tables in the materialized view

SQL> 
rollback;

Rollback complete.

SQL> 
exec dbms_mview.explain_mview('-
  select cases.rowid case_rowid,countries.rowid country_rowid,continents.rowid continent_rowid,daterep,continent_name,country_name,cases from cases , countries , continents where cases.country_id=countries.country_id and countries.continent_id=continents.continent_id and cases>0-
  ');

PL/SQL procedure successfully completed.

SQL> select possible "?",capability_name,related_text,msgtxt from mv_capabilities_table where capability_name like 'REFRESH_FAST%' order by seq;
   ?                  CAPABILITY_NAME       RELATED_TEXT                                                                      MSGTXT
____ ________________________________ __________________ ___________________________________________________________________________
N    REFRESH_FAST
N    REFRESH_FAST_AFTER_INSERT        DEMO.CASES         the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.COUNTRIES     the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_INSERT        DEMO.CONTINENTS    the detail table does not have a materialized view log
N    REFRESH_FAST_AFTER_ONETAB_DML                       see the reason why REFRESH_FAST_AFTER_INSERT is disabled
N    REFRESH_FAST_AFTER_ANY_DML                          see the reason why REFRESH_FAST_AFTER_ONETAB_DML is disabled
N    REFRESH_FAST_PCT                                    PCT is not possible on any of the detail tables in the materialized view

SQL> 
rollback;

Rollback complete.

Ok, now another message: “the detail table does not have a materialized view log”. But that’s exactly the purpose of statement-level refresh: being able to fast refresh without creating and maintaining materialized view logs, and without full-refreshing a table or a partition.

This’t the limit of DBMS_MVIEW.EXPLAIN_MVIEW. Let’s try to create the materialized view now:


SQL> 
create materialized view flatview refresh fast on statement as
  select cases.rowid case_rowid,countries.rowid country_rowid,continents.rowid continent_rowid,daterep,continent_name,country_name,cases from cases , countries , continents where cases.country_id=countries.country_id and countries.continent_id=continents.continent_id and cases>0;

select cases.rowid case_rowid,countries.rowid country_rowid,continents.rowid continent_rowid,daterep,continent_name,country_name,cases from cases , countries , continents where cases.country_id=countries.country_id and countries.continent_id=continents.continent_id and cases>0
                                                                                                                                                                                                                                                                                    *
ERROR at line 2:
ORA-32428: on-statement materialized join view error: Shape of MV is not
supported(composite PK)

SQL>

That’s clear. I had created the fact primary key on the compound foreign keys.

Surrogate key on fact table

This is not allowed by statement-level refresh, so let’s change that:


SQL> 
alter table cases add (case_id number);

Table altered.

SQL> 
update cases set case_id=rownum;

21274 rows updated.

SQL> 
alter table cases drop primary key;

Table altered.

SQL> 
alter table cases add primary key(case_id);

Table altered.

SQL> 
alter table cases add unique(daterep,country_id);
Table altered.

I have added a surrogate key and defined a unique key for the composite one.

Now the creation is sucessful:


SQL> 
create materialized view flatview refresh fast on statement as
  select cases.rowid case_rowid,countries.rowid country_rowid,continents.rowid continent_rowid,daterep,continent_name,country_name,cases from cases , countries , continents where cases.country_id=countries.country_id and countries.continent_id=continents.continent_id and cases>0;

Materialized view created.

Note that I tested later and I am able to create it with the ROWID from the fact table CASES only. But that’s not a good idea: in order to propagate any change to the underlying tables, the materialized view must have the ROWID, like an index. I consider as a bug the possibility to do it.

Here are the columns stored in my materialized view:


SQL> 
desc flatview

              Name    Null?            Type
__________________ ________ _______________
CASE_ROWID                  ROWID
COUNTRY_ROWID               ROWID
CONTINENT_ROWID             ROWID
DATEREP                     VARCHAR2(10)
CONTINENT_NAME              VARCHAR2(30)
COUNTRY_NAME                VARCHAR2(60)
CASES                       NUMBER

Storing the ROWID is not something we should recommend as some maintenance operations may change the physical location of rows. You will need to complete refresh the materialized view after an online move for example.

No-join query

I’ll show query rewrite in another blog post. For the moment, I’ll query this materialized view directly.

Here is a query similar to the one in the previous post:


SQL> 
select continent_name,country_name,top_date,top_cases from (
 select continent_name,country_name,daterep,cases
  ,first_value(daterep)over(partition by continent_name order by cases desc) top_date
  ,first_value(cases)over(partition by continent_name order by cases desc)top_cases
  ,row_number()over(partition by continent_name order by cases desc) r
  from flatview
 )
 where r=1 order by top_cases
;

   CONTINENT_NAME                COUNTRY_NAME      TOP_DATE    TOP_CASES
_________________ ___________________________ _____________ ____________
Oceania           Australia                   23/03/2020             611
Africa            South_Africa                05/06/2020            3267
Europe            Russia                      12/05/2020           11656
Asia              China                       13/02/2020           15141
America           United_States_of_America    26/04/2020           48529

I have replaced the country_id and continent_id by their name as I didn’t put them in my materialized view. And I repeated the window function everywhere if you want to run the same in versions lower than 20c.

This materialized view is a table. I can partition it by hash to scatter the data. I can cluster on another column. I can add indexes. I have the full power of a SQL databases on it, without the need to join if you think that joins are slow. If you come from NoSQL you can see it like a DynamoDB global index. You can query it without joining, fetching all attributes with one call, and filtering on another key than the primary key. But here we have always strong consistency: the changes are replicated immediately, fully ACID. They will be committed or rolled back by the same transaction that did the change. They will be replicated synchronously or asynchronously with read-only replicas.

DML on base tables

Let’s do some changes here, lowering the covid-19 cases of CHN to 42%:


SQL> 
alter session set sql_trace=true;

Session altered.

SQL> 
update cases set cases=cases*0.42 where country_id=(select country_id from countries where country_code='CHN');

157 rows updated.

SQL> 
alter session set sql_trace=false;

Session altered.

I have set sql_trace because I want to have a look at the magic behind it.

Now running my query on the materialized view:



SQL> 
select continent_name,country_name,top_date,top_cases from (
 select continent_name,country_name,daterep,cases
  ,first_value(daterep)over(partition by continent_name order by cases desc) top_date
  ,first_value(cases)over(partition by continent_name order by cases desc)top_cases
  ,row_number()over(partition by continent_name order by cases desc) r
  from flatview
 )
 where r=1 order by top_cases
;

   CONTINENT_NAME                COUNTRY_NAME      TOP_DATE    TOP_CASES
_________________ ___________________________ _____________ ____________
Oceania           Australia                   23/03/2020             611
Africa            South_Africa                05/06/2020            3267
Asia              India                       05/06/2020            9851
Europe            Russia                      12/05/2020           11656
America           United_States_of_America    26/04/2020           48529

CHN is not the top one in Asia anymore with the 42% correction.

The changes were immediately propagated to the materialized view like when indexes are updated, and we can see that in the trace:


SQL> 
column value new_value tracefile
SQL> 
select value from v$diag_info where name='Default Trace File';
                                                                     VALUE
__________________________________________________________________________
/u01/app/oracle/diag/rdbms/cdb1a_iad154/CDB1A/trace/CDB1A_ora_49139.trc


SQL> 
column value clear
SQL> 
host tkprof &tracefile trace.txt

TKPROF: Release 20.0.0.0.0 - Development on Thu Jun 4 15:43:13 2020

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

SQL> 
host awk '/"FLATVIEW/,/^[*]/' trace.txt

sql_trace instruments all executions with time and number of rows. tkprof aggregates those for analysis.

The trace shows two statements on my materialized view: DELETE and INSERT.

The first one is about removing the modified rows.


DELETE FROM "DEMO"."FLATVIEW"
WHERE
 "CASE_ROWID" = :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      158      0.00       0.00          0          0          0           0
Execute    158      0.02       0.10         38        316        438         142
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      316      0.02       0.10         38        316        438         142

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 634     (recursive depth: 1)
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  DELETE  FLATVIEW (cr=2 pr=0 pw=0 time=2080 us starts=1)
         1          0          1   INDEX UNIQUE SCAN I_OS$_FLATVIEW (cr=2 pr=0 pw=0 time=2055 us starts=1 cost=1 size=10 card=1)(object id 78728)

This has been done row-by-row but is optimized with an index on ROWID that has been created autonomously with my materialized view.

The second one is inserting the modified rows:


INSERT INTO  "DEMO"."FLATVIEW" SELECT "CASES".ROWID "CASE_ROWID",
  "COUNTRIES".ROWID "COUNTRY_ROWID","CONTINENTS".ROWID "CONTINENT_ROWID",
  "CASES"."DATEREP" "DATEREP","CONTINENTS"."CONTINENT_NAME" "CONTINENT_NAME",
  "COUNTRIES"."COUNTRY_NAME" "COUNTRY_NAME","CASES"."CASES" "CASES" FROM
  "CONTINENTS" "CONTINENTS","COUNTRIES" "COUNTRIES", (SELECT "CASES".ROWID
  "ROWID","CASES"."DATEREP" "DATEREP","CASES"."CASES" "CASES",
  "CASES"."COUNTRY_ID" "COUNTRY_ID" FROM "DEMO"."CASES" "CASES" WHERE
  "CASES".ROWID=(:Z)) "CASES" WHERE "CASES"."COUNTRY_ID"=
  "COUNTRIES"."COUNTRY_ID" AND "COUNTRIES"."CONTINENT_ID"=
  "CONTINENTS"."CONTINENT_ID" AND "CASES"."CASES">0

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      158      0.00       0.00          0          0          0           0
Execute    158      0.01       0.11          0        734        616         142
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      316      0.02       0.12          0        734        616         142


Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 634     (recursive depth: 1)
Number of plan statistics captured: 3

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         0          0          0  LOAD TABLE CONVENTIONAL  FLATVIEW (cr=5 pr=0 pw=0 time=235 us starts=1)
         1          0          1   NESTED LOOPS  (cr=2 pr=0 pw=0 time=25 us starts=1 cost=3 size=52 card=1)
         1          0          1    NESTED LOOPS  (cr=2 pr=0 pw=0 time=20 us starts=1 cost=2 size=42 card=1)
         1          0          1     TABLE ACCESS BY USER ROWID CASES (cr=1 pr=0 pw=0 time=14 us starts=1 cost=1 size=22 card=1)
         1          0          1     TABLE ACCESS BY INDEX ROWID COUNTRIES (cr=1 pr=0 pw=0 time=5 us starts=1 cost=1 size=20 card=1)
         1          0          1      INDEX UNIQUE SCAN SYS_C009414 (cr=0 pr=0 pw=0 time=3 us starts=1 cost=0 size=0 card=1)(object id 78716)
         1          0          1    TABLE ACCESS BY INDEX ROWID CONTINENTS (cr=1 pr=0 pw=0 time=2 us starts=1 cost=1 size=10 card=1)
         1          0          1     INDEX UNIQUE SCAN SYS_C009412 (cr=0 pr=0 pw=0 time=1 us starts=1 cost=0 size=0 card=1)(object id 78715)

Again, a row-by-row insert apparently as the “execute count” is nearly the same as the “rows count”. 157 is the number of rows I have updated.

You may think that this is a huge overhead, but those operations are optimized for a long time. The materialized view is refreshed and ready for optimal queries: no need to queue, stream, reorg, vacuum,… And I can imagine that if this feature is used, it will be optimized with bulk operations which would allow compression.

Truncate

This looks all good. But… what happens if I truncate the table?


SQL> 
truncate table cases;

Table truncated.

SQL> 
select continent_name,country_name,top_date,top_cases from (
 select continent_name,country_name,daterep,cases
  ,first_value(daterep)over(partition by continent_name order by cases desc) top_date
  ,first_value(cases)over(partition by continent_name order by cases desc)top_cases
  ,row_number()over(partition by continent_name order by cases desc) r
  from flatview
 )
 where r=1 order by top_cases
;

   CONTINENT_NAME                COUNTRY_NAME      TOP_DATE    TOP_CASES
_________________ ___________________________ _____________ ____________
Oceania           Australia                   23/03/2020             611
Africa            South_Africa                05/06/2020            3267
Asia              India                       05/06/2020            9851
Europe            Russia                      12/05/2020           11656
America           United_States_of_America    26/04/2020           48529

Nothing changed. This is dangerous. You need to refresh it yourself. This may be a bug. What will happen if you insert data back? Note that, like with triggers, direct-path inserts will be transparently run as conventional inserts.


SQL>
exec dbms_mview.refresh('DEMO.FLATVIEW');
SQL>
insert into cases select rownum cases_id,daterep, geoid country_id,cases from covid where continentexp!='Other';

21483 rows created.

SQL>
commit;

CONTINENT_NAME COUNTRY_NAME TOP_DATE TOP_CASES
_________________ ___________________________ _____________ ____________
Oceania Australia 23/03/2020 611
Africa South_Africa 05/06/2020 3267
Europe Russia 12/05/2020 11656
Asia China 13/02/2020 15141
America United_States_of_America 26/04/2020 48529

Joins are not expensive

This feature is really good to pre-build the joins in a composition of tables, as a hierarchical key-value, or snowflake dimension fact table. You can partition, compress, order, filter, index,… as with any relational table. There no risk here with the denormalization as it is transparently maintained when you update the underlying tables.

If you develop on a NoSQL database because you have heard that normalization was invented to reduce storage, which is not nexpensive anymore, that’s a myth (you can read this long thread to understand the origin of this myth). Normalization is about database integrity and separation lof logical and physical layers. And that’s what Oracle Database implements with this feature: you update the logical view, tables are normalized for integrity, and the physical layer transparently maintains additional structures like indexes and materialized views to keep queries under single-digit milliseconds. Today you still need to think about indexes and materialized views to build. Some advisors may help. All those are the bricks for the future: an autonomous database where you define only the logical layer for your application and all those optimisations will be done in background.

Cet article Oracle 12c – pre-built join index est apparu en premier sur Blog dbi services.

Oracle 18c – select from a flat file

By Franck Pachot

.
This post is the first one from a series of small examples on recent Oracle features. My goal is to present them to people outside of Oracle and relational databases usage, maybe some NoSQL players. And this is why the title is “select from a flat-file” rather than “Inline External Tables”. In my opinion, the names of the features of Oracle Database are invented by the architects and developers, sometimes renamed by Marketing or CTO, and all that is very far from what the users are looking for. In order to understand “Inline External Table” you need to know all the history behind: there were tables, then external tables, and there were queries, and inlined queries, and… But imagine a junior who just wants to query a file, he will never find this feature. He has a file, it is not a table, it is not external, and it is not inline. What is external to him is this SQL language and what we want to show him is that this language can query his file.

I’m running this in the Oracle 20c preview in the Oracle Cloud.

In this post, my goal is to load a small fact and dimension table for the next posts about some recent features that are interesting in data warehouses. It is the occasion to show that with Oracle we can easily select from a .csv file, without the need to run SQL*Loader or create an external table.
I’m running everything from SQLcl and then I use the host command to call curl:


host curl -L http://opendata.ecdc.europa.eu/covid19/casedistribution/csv/ | dos2unix | (sed -u 1q; sort -t, -n -k4,4 -k3,3 -k2,2 ) > /tmp/covid-19.csv

This gets the latest number of COVID-19 cases per day and per country.

It looks like this:


SQL> host head  /tmp/covid-19.csv

dateRep,day,month,year,cases,deaths,countriesAndTerritories,geoId,countryterritoryCode,popData2018,continentExp
31/12/2019,31,12,2019,0,0,Afghanistan,AF,AFG,37172386,Asia
31/12/2019,31,12,2019,0,0,Algeria,DZ,DZA,42228429,Africa
31/12/2019,31,12,2019,0,0,Armenia,AM,ARM,2951776,Europe
31/12/2019,31,12,2019,0,0,Australia,AU,AUS,24992369,Oceania
31/12/2019,31,12,2019,0,0,Austria,AT,AUT,8847037,Europe
31/12/2019,31,12,2019,0,0,Azerbaijan,AZ,AZE,9942334,Europe
31/12/2019,31,12,2019,0,0,Bahrain,BH,BHR,1569439,Asia
31/12/2019,31,12,2019,0,0,Belarus,BY,BLR,9485386,Europe
31/12/2019,31,12,2019,0,0,Belgium,BE,BEL,11422068,Europe

I sorted them on date on purpose (next post may talk about data clustering) but this is the way the file comes anyway.

I need a directory object to access the file:


SQL> create or replace directory "/tmp" as '/tmp';

Directory created.

You don’t have to use quoted identifiers if you don’t like it. I find it convenient here.

I can directly select from the file, the EXTERNAL clause mentioning what we had to put in an external table before 18c:


SQL> 
select *
 from external (
  (
   dateRep                    date
   ,n_day                     number
   ,n_month                   number
   ,n_year                    number
   ,cases                     number
   ,deaths                    number
   ,countriesAndTerritories   varchar2(50)
   ,geoId                     varchar2(10)
   ,countryterritoryCode      varchar2(3)
   ,popData2018               number
   ,continentExp              varchar2(10)
  )
  default directory "/tmp"
  access parameters (
     records delimited by newline skip 1 -- skip header
     logfile 'covid-19.log'
     badfile 'covid-19.bad'
     fields terminated by "," optionally enclosed by '"'
(dateRep date 'dd/mm/yyyy',n_day,n_month,n_year,cases,deaths,countriesAndTerritories,geoId,countryterritoryCode,popData2018,continentExp))
  location ('covid.csv')
  reject limit 10000 - because I've seen some bad data ;)
 )
.


SQL> /

      DATEREP    N_DAY    N_MONTH    N_YEAR    CASES    DEATHS                       COUNTRIESANDTERRITORIES       GEOID    COUNTRYTERRITORYCODE    POPDATA2018    CONTINENTEXP
_____________ ________ __________ _________ ________ _________ _____________________________________________ ___________ _______________________ ______________ _______________
31/12/2019          31         12      2019        0         0 Afghanistan                                   AF          AFG                           37172386 Asia
31/12/2019          31         12      2019        0         0 Algeria                                       DZ          DZA                           42228429 Africa
31/12/2019          31         12      2019        0         0 Armenia                                       AM          ARM                            2951776 Europe
31/12/2019          31         12      2019        0         0 Australia                                     AU          AUS                           24992369 Oceania
31/12/2019          31         12      2019        0         0 Austria                                       AT          AUT                            8847037 Europe
31/12/2019          31         12      2019        0         0 Azerbaijan                                    AZ          AZE                            9942334 Europe
31/12/2019          31         12      2019        0         0 Bahrain                                       BH          BHR                            1569439 Asia

ORA-01013: user requested cancel of current operation

SQL>

I cancelled it as that’s too long to display here.

As the query is still in the buffer, I just add a CREATE TABLE in front of it:


SQL> 1
  1* select *
SQL> c/select/create table covid as select/
   create table covid as select *
  2   from external (
  3    (
  4     dateRep                    varchar2(10)
  5     ,day                       number
...

SQL> /

Table created.

SQL>

While I’m there I’ll quickly create a fact table and a dimension hierarchy:


SQL> 
create table continents as select rownum continent_id, continentexp continent_name from (select distinct continentexp from covid where continentexp!='Other');

Table created.

SQL> create table countries as select country_id,country_code,country_name,continent_id,popdata2018 from (select distinct geoid country_id,countryterritorycode country_code,countriesandterritories country_name,continentexp continent_name,popdata2018 from covid where continentexp!='Other') left join continents using(continent_name);

Table created.

SQL> 
create table cases as select daterep, geoid country_id,cases from covid where continentexp!='Other';

Table created.

SQL> 
alter table continents add primary key (continent_id);

Table altered.

SQL> 
alter table countries add foreign key (continent_id) references continents;

Table altered.

SQL> 
alter table countries add primary key (country_id);

Table altered.

SQL> 
alter table cases add foreign key (country_id) references countries;

Table altered.

SQL> 
alter table cases add primary key (country_id,daterep);

Table altered.

SQL>

This creates a CASES fact table with only one measure (covid-19 cases) and two dimensions. To get it simple, the date dimension here is just a date column (you usually have a foreign key to a calendar dimension). The geographical dimension is a foreign key to the COUNTRIES table which itself has a foreign key referencing the CONTINENTS table.

12c Top-N queries

In 12c we have a nice syntax for Top-N queries with the FETCH FIRST clause of the ORDER BY:


SQL> 
select continent_name,country_code,max(cases) from cases join countries using(country_id) join continents using(continent_id) group by continent_name,country_code order by max(cases) desc fetch first 10 rows only;

   CONTINENT_NAME    COUNTRY_CODE    MAX(CASES)
_________________ _______________ _____________
America           USA                     48529
America           BRA                     33274
Asia              CHN                     15141
Europe            RUS                     11656
America           ECU                     11536
Asia              IND                      9851
Europe            ESP                      9181
America           PER                      8875
Europe            GBR                      8719
Europe            FRA                      7578

10 rows selected.

This returns the 10 countries which had the maximum covid-19 cases per day.

20c WINDOW clauses

If I want to show the date with the maximum value, I can use analytic functions and in 20c I don’t have to repeat the window several times:


SQL> 
select continent_name,country_code,top_date,top_cases from (
   select continent_name,country_code,daterep,cases
    ,first_value(daterep)over(w) top_date
    ,first_value(cases)over(w) top_cases
    ,row_number()over(w) r
    from cases join countries using(country_id) join continents using(continent_id)
    window w as (partition by continent_id order by cases desc)
   )
   where r=1 -- this to get the rows with the highest value only
   order by top_cases desc fetch first 10 rows only;

   CONTINENT_NAME    COUNTRY_CODE      TOP_DATE    TOP_CASES
_________________ _______________ _____________ ____________
America           USA             26/04/2020           48529
Asia              CHN             13/02/2020           15141
Europe            RUS             12/05/2020           11656
Africa            ZAF             05/06/2020            3267
Oceania           AUS             23/03/2020             611

The same can be done before 20c but you have to write the (partition by continent_id order by cases desc) for each projection.

In the next post I’ll show a very nice feature. Keeping the 3 tables normalized data model but, because storage is cheap, materializing some pre-computed joins. If you are a fan of NoSQL because “storage is cheap” and “joins are expensive”, then you will see what we can do with SQL in this area…

Cet article Oracle 18c – select from a flat file est apparu en premier sur Blog dbi services.

Do you want to be a star?

https://oracle-base.com/blog/wp-content/uploads/2020/06/clapper-board-15... 274w" sizes="(max-width: 203px) 85vw, 203px" />

If you’ve followed my YouTube channel, you’ll know I include a clip of people from the community saying “.com” at the start of each video. It’s just a silly tradition, and it allows me to give a shout out to other people.

When I started the channel I used to record loads of these at conferences, but I acquired so many I stopped doing it, until I could get through what I already had. I have now used all of them…

If you would like to star in a video, please do the following:

  • Drop me an email with your video clip saying “.com”. You know my name is “tim” and my website is “oracle-base.com”, so you can guess my email address.
  • Please say “.com” 2 or 3 times with a 1 second gap between them.
  • In the email give me your name and social media links, so I can include them in the video description and associated blog post. I am terrible with names, even people I’ve met a lot, so don’t count on me remembering your name. <br />
</li></ul></div>

    	  	<div class=

Fetch First Update

A question about mixing the (relatively new) “fetch first” syntax with “select for update” appeared a few days ago on the Oracle Developer Forum. The requirement was for a query something like:


select
        *
from
        t1
order by
        n1
fetch
        first 10 rows only
for     update
;

The problem with this query is that it results in Oracle raising error ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc. The error doesn’t seem to be particularly relevant, of course, until you remember that “fetch first” creates an inline view using the analytic row_number() under the covers.

One suggested solution was to use PL/SQL to open a cursor with a pure select then use a loop to lock each row in turn. This would need a little defensive programming, of course, since each individual “select for update” would be running at a different SCN from the driving loop, and there would be some risk of concurrency problems (locking, or competing data change) occuring.

There is a pure – thought contorted – SQL solution though where we take the driving SQL and put it into a subquery that generates the rowids of the rows we want to lock, as follows:


select
        /*+
                qb_name(main)
        */
        *
from
        t1
where
        t1.rowid in (
                select
                        /*+ qb_name(inline) unnest no_merge */
                        t1a.rowid
                from
                        t1 t1a
                order by
                        t1a.n1
                fetch 
                        first 10 rows only
        )
for update
;

The execution plan for this query is critical – so once you can get it working it would be a good idea to create a baseline (or SQL Patch) and attach it to the query. It is most important that the execution plan should be the equivalent of the following:


select  /*+   qb_name(main)  */  * from  t1 where  t1.rowid in (
select    /*+ qb_name(inline) unnest no_merge */    t1a.rowid   from
t1 t1a   order by    t1a.n1   fetch    first 10 rows only  ) for update

Plan hash value: 1286935441

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |      |      1 |        |     10 |00:00:00.01 |     190 |       |       |          |
|   1 |  FOR UPDATE                   |      |      1 |        |     10 |00:00:00.01 |     190 |       |       |          |
|   2 |   BUFFER SORT                 |      |      2 |        |     20 |00:00:00.01 |     178 |  2048 |  2048 | 2048  (0)|
|   3 |    NESTED LOOPS               |      |      1 |     10 |     10 |00:00:00.01 |     178 |       |       |          |
|*  4 |     VIEW                      |      |      1 |     10 |     10 |00:00:00.01 |     177 |       |       |          |
|*  5 |      WINDOW SORT PUSHED RANK  |      |      1 |  10000 |     10 |00:00:00.01 |     177 |  2048 |  2048 | 2048  (0)|
|   6 |       TABLE ACCESS FULL       | T1   |      1 |  10000 |  10000 |00:00:00.01 |     177 |       |       |          |
|   7 |     TABLE ACCESS BY USER ROWID| T1   |     10 |      1 |     10 |00:00:00.01 |       1 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=10)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY "T1A"."N1")<=10)

Critically you need the VIEW operation to be the driving query of a nested loop join that does the “table access by user rowid” joinback. In my case the query has used a full tablescan to identify the small number of rowids needed – in a production system that would be the part of the statement that should first be optimised.

It’s an unfortunate feature of this query structure (made messier by the internal rewrite for the analytic function) that it’s not easy to generate a correct set of hints to force the plan until after you’ve already managed to get the plan. Here’s the outline information that shows the messiness of the hints I would have needed:


Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"INLINE")
      OUTLINE_LEAF(@"SEL$A3F38ADC")
      UNNEST(@"SEL$1")
      OUTLINE(@"INLINE")
      OUTLINE(@"MAIN")
      OUTLINE(@"SEL$1")
      NO_ACCESS(@"SEL$A3F38ADC" "from$_subquery$_003"@"SEL$1")
      ROWID(@"SEL$A3F38ADC" "T1"@"MAIN")
      LEADING(@"SEL$A3F38ADC" "from$_subquery$_003"@"SEL$1" "T1"@"MAIN")
      USE_NL(@"SEL$A3F38ADC" "T1"@"MAIN")
      FULL(@"INLINE" "T1A"@"INLINE")
      END_OUTLINE_DATA
  */

You’ll notice that my /*+ unnest */ hint is now modified – for inclusion at the start of the query – to /*+ unnest(@sel1) */ rather than the /*+ unnest(@inline) */ that you might have expected. That’s the side effect of the optimizer doing the “fetch first” rewrite before applying “missing” query block names. If I wanted to write a full hint set into the query itself (leaving the qb_name() hints in place but removing the unnest and merge I had originally) I would need the following:


/*+
        unnest(@sel$1)
        leading(@sel$a3f38adc from$_subquery$_003@sel$1 t1@main)
        use_nl( @sel$a3f38adc t1@main)
        rowid(  @sel$a3f38adc t1@main)
*/

I did make a bit of a fuss about the execution plan. I think it’s probably very important that everyone who runs this query gets exactly the same plan and the plan should be this nested loop. Although there’s a BUFFER SORT at operation 2 that is probably ensuring that every would get the same data in the same order regardless of the execution plan before locking any of it, I would be a little worried that different plans might somehow be allowed to lock the data in a different order, thus allowing for deadlocks.

Loading a Flat File from OCI Object Storage into an Autonomous Database. Part 3. Copying data from Object Storage to a Regular Table

This blog is the third in a series of three that looks at transferring a file to Oracle Cloud Infrastructure (OCI) Object Storage, and then reading it into the database with an external table or copying it into a regular table.

Copy Data into Table 

Alternatively, we can copy the data into a normal table. The table needs to be created in advance. This time, I am going to run the copy as user SOE rather than ADMIN.  I need to:

  • Grant connect and resource privilege and quota on the data tablespace.
  • Grant execute on DBMS_CLOUD to SOE, so it can execute the command.
  • Grant READ and WRITE access on the DATA_PUMP_DIR directory – the log and bad files created by this process are written to this database directory.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">connect admin/Password2020!@gofaster1b_tp 
CREATE USER soe IDENTIFIED BY Password2020;
GRANT CONNECT, RESOURCE TO soe;
GRANT EXECUTE ON DBMS_CLOUD TO soe;
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO soe;
ALTER USER soe QUOTA UNLIMITED ON data;

I am now going to switch to user SOE and create my table.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">connect soe/Password2020@gofaster1b_tp
Drop table soe.ash_hist purge;
CREATE TABLE soe.ASH_HIST
( SNAP_ID NUMBER,
DBID NUMBER,
INSTANCE_NUMBER NUMBER,
SAMPLE_ID NUMBER,
SAMPLE_TIME TIMESTAMP (3),
-- SAMPLE_TIME_UTC TIMESTAMP (3),
-- USECS_PER_ROW NUMBER,
SESSION_ID NUMBER,
SESSION_SERIAL# NUMBER,
SESSION_TYPE VARCHAR2(10),
FLAGS NUMBER,
USER_ID NUMBER,
-----------------------------------------
SQL_ID VARCHAR2(13),
IS_SQLID_CURRENT VARCHAR2(1),
SQL_CHILD_NUMBER NUMBER,
SQL_OPCODE NUMBER,
SQL_OPNAME VARCHAR2(64),
FORCE_MATCHING_SIGNATURE NUMBER,
TOP_LEVEL_SQL_ID VARCHAR2(13),
TOP_LEVEL_SQL_OPCODE NUMBER,
SQL_PLAN_HASH_VALUE NUMBER,
SQL_FULL_PLAN_HASH_VALUE NUMBER,
-----------------------------------------
SQL_ADAPTIVE_PLAN_RESOLVED NUMBER,
SQL_PLAN_LINE_ID NUMBER,
SQL_PLAN_OPERATION VARCHAR2(64),
SQL_PLAN_OPTIONS VARCHAR2(64),
SQL_EXEC_ID NUMBER,
SQL_EXEC_START DATE,
PLSQL_ENTRY_OBJECT_ID NUMBER,
PLSQL_ENTRY_SUBPROGRAM_ID NUMBER,
PLSQL_OBJECT_ID NUMBER,
PLSQL_SUBPROGRAM_ID NUMBER,
-----------------------------------------
QC_INSTANCE_ID NUMBER,
QC_SESSION_ID NUMBER,
QC_SESSION_SERIAL# NUMBER,
PX_FLAGS NUMBER,
EVENT VARCHAR2(64),
EVENT_ID NUMBER,
SEQ# NUMBER,
P1TEXT VARCHAR2(64),
P1 NUMBER,
P2TEXT VARCHAR2(64),
-----------------------------------------
P2 NUMBER,
P3TEXT VARCHAR2(64),
P3 NUMBER,
WAIT_CLASS VARCHAR2(64),
WAIT_CLASS_ID NUMBER,
WAIT_TIME NUMBER,
SESSION_STATE VARCHAR2(7),
TIME_WAITED NUMBER,
BLOCKING_SESSION_STATUS VARCHAR2(11),
BLOCKING_SESSION NUMBER,
-----------------------------------------
BLOCKING_SESSION_SERIAL# NUMBER,
BLOCKING_INST_ID NUMBER,
BLOCKING_HANGCHAIN_INFO VARCHAR2(1),
CURRENT_OBJ# NUMBER,
CURRENT_FILE# NUMBER,
CURRENT_BLOCK# NUMBER,
CURRENT_ROW# NUMBER,
TOP_LEVEL_CALL# NUMBER,
TOP_LEVEL_CALL_NAME VARCHAR2(64),
CONSUMER_GROUP_ID NUMBER,
-----------------------------------------
XID RAW(8),
REMOTE_INSTANCE# NUMBER,
TIME_MODEL NUMBER,
IN_CONNECTION_MGMT VARCHAR2(1),
IN_PARSE VARCHAR2(1),
IN_HARD_PARSE VARCHAR2(1),
IN_SQL_EXECUTION VARCHAR2(1),
IN_PLSQL_EXECUTION VARCHAR2(1),
IN_PLSQL_RPC VARCHAR2(1),
IN_PLSQL_COMPILATION VARCHAR2(1),
-----------------------------------------
IN_JAVA_EXECUTION VARCHAR2(1),
IN_BIND VARCHAR2(1),
IN_CURSOR_CLOSE VARCHAR2(1),
IN_SEQUENCE_LOAD VARCHAR2(1),
IN_INMEMORY_QUERY VARCHAR2(1),
IN_INMEMORY_POPULATE VARCHAR2(1),
IN_INMEMORY_PREPOPULATE VARCHAR2(1),
IN_INMEMORY_REPOPULATE VARCHAR2(1),
IN_INMEMORY_TREPOPULATE VARCHAR2(1),
-- IN_TABLESPACE_ENCRYPTION VARCHAR2(1),
CAPTURE_OVERHEAD VARCHAR2(1),
-----------------------------------------
REPLAY_OVERHEAD VARCHAR2(1),
IS_CAPTURED VARCHAR2(1),
IS_REPLAYED VARCHAR2(1),
-- IS_REPLAY_SYNC_TOKEN_HOLDER VARCHAR2(1),
SERVICE_HASH NUMBER,
PROGRAM VARCHAR2(64),
MODULE VARCHAR2(64),
ACTION VARCHAR2(64),
CLIENT_ID VARCHAR2(64),
MACHINE VARCHAR2(64),
PORT NUMBER,
-----------------------------------------
ECID VARCHAR2(64),
DBREPLAY_FILE_ID NUMBER,
DBREPLAY_CALL_COUNTER NUMBER,
TM_DELTA_TIME NUMBER,
TM_DELTA_CPU_TIME NUMBER,
TM_DELTA_DB_TIME NUMBER,
DELTA_TIME NUMBER,
DELTA_READ_IO_REQUESTS NUMBER,
DELTA_WRITE_IO_REQUESTS NUMBER,
DELTA_READ_IO_BYTES NUMBER,
-----------------------------------------
DELTA_WRITE_IO_BYTES NUMBER,
DELTA_INTERCONNECT_IO_BYTES NUMBER,
PGA_ALLOCATED NUMBER,
TEMP_SPACE_ALLOCATED NUMBER,
DBOP_NAME VARCHAR2(64),
DBOP_EXEC_ID NUMBER,
CON_DBID NUMBER,
CON_ID NUMBER,
-----------------------------------------
CONSTRAINT ash_hist_pk PRIMARY KEY (dbid, instance_number, snap_id, sample_id, session_id)
)
COMPRESS FOR QUERY LOW
/

As Autonomous Databases run on Exadata, I have also specified Hybrid Columnar Compression (HCC) for this table.
Credentials are specific to the database user.  I have to create an additional credential, for the same cloud user, but owned by SOE.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">ALTER SESSION SET nls_date_Format='hh24:mi:ss dd.mm.yyyy';
set serveroutput on timi on
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'SOE_BUCKET',
username=> 'oraclecloud1@go-faster.co.uk',
password=> 'K7xfi-mG<1Z:dq#88;1m'
);
END;
/
column owner format a10
column credential_name format a20
column comments format a80
column username format a40
SELECT * FROM dba_credentials;

OWNER CREDENTIAL_NAME USERNAME WINDOWS_DOMAIN
---------- -------------------- ---------------------------------------- ------------------------------
COMMENTS ENABL
-------------------------------------------------------------------------------- -----
ADMIN MY_BUCKET oraclecloud1@go-faster.co.uk
{"comments":"Created via DBMS_CLOUD.create_credential"} TRUE

SOE SOE_BUCKET oraclecloud1@go-faster.co.uk
{"comments":"Created via DBMS_CLOUD.create_credential"} TRUE

The COPY_DATA procedure is similar to CREATE_EXTERNAL_TABLE described in the previous post, but it doesn't have a column list. The field names much match the column names. It is sensitive to field names with a trailing #.  These must be enclosed in double-quotes.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">TRUNCATE TABLE soe.ash_hist;
DECLARE
l_operation_id NUMBER;
BEGIN
DBMS_CLOUD.COPY_DATA(
table_name =>'ASH_HIST',
credential_name =>'SOE_BUCKET',
file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz',
schema_name => 'SOE',
format => json_object('blankasnull' value 'true'
,'compression' value 'gzip'
,'dateformat' value 'YYYY-MM-DD/HH24:mi:ss'
,'timestampformat' value 'YYYY-MM-DD/HH24:mi:ss.ff'
,'delimiter' value '<,>'
,'ignoreblanklines' value 'true'
,'rejectlimit' value '10'
,'removequotes' value 'true'
,'trimspaces' value 'lrtrim'
),
field_list=>'SNAP_ID,DBID,INSTANCE_NUMBER,SAMPLE_ID,SAMPLE_TIME ,SESSION_ID,"SESSION_SERIAL#",SESSION_TYPE,FLAGS,USER_ID
,SQL_ID,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,SQL_OPCODE,SQL_OPNAME,FORCE_MATCHING_SIGNATURE,TOP_LEVEL_SQL_ID,TOP_LEVEL_SQL_OPCODE,SQL_PLAN_HASH_VALUE,SQL_FULL_PLAN_HASH_VALUE
,SQL_ADAPTIVE_PLAN_RESOLVED,SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS,SQL_EXEC_ID,SQL_EXEC_START,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID
,QC_INSTANCE_ID,QC_SESSION_ID,"QC_SESSION_SERIAL#",PX_FLAGS,EVENT,EVENT_ID,"SEQ#",P1TEXT,P1,P2TEXT
,P2,P3TEXT,P3,WAIT_CLASS,WAIT_CLASS_ID,WAIT_TIME,SESSION_STATE,TIME_WAITED,BLOCKING_SESSION_STATUS,BLOCKING_SESSION
,"BLOCKING_SESSION_SERIAL#",BLOCKING_INST_ID,BLOCKING_HANGCHAIN_INFO,"CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#",TOP_LEVEL_CALL_NAME,CONSUMER_GROUP_ID
,XID,"REMOTE_INSTANCE#",TIME_MODEL,IN_CONNECTION_MGMT,IN_PARSE,IN_HARD_PARSE,IN_SQL_EXECUTION,IN_PLSQL_EXECUTION,IN_PLSQL_RPC,IN_PLSQL_COMPILATION
,IN_JAVA_EXECUTION,IN_BIND,IN_CURSOR_CLOSE,IN_SEQUENCE_LOAD,IN_INMEMORY_QUERY,IN_INMEMORY_POPULATE,IN_INMEMORY_PREPOPULATE,IN_INMEMORY_REPOPULATE,IN_INMEMORY_TREPOPULATE,CAPTURE_OVERHEAD
,REPLAY_OVERHEAD,IS_CAPTURED,IS_REPLAYED,SERVICE_HASH,PROGRAM,MODULE,ACTION,CLIENT_ID,MACHINE,PORT
,ECID,DBREPLAY_FILE_ID,DBREPLAY_CALL_COUNTER,TM_DELTA_TIME,TM_DELTA_CPU_TIME,TM_DELTA_DB_TIME,DELTA_TIME,DELTA_READ_IO_REQUESTS,DELTA_WRITE_IO_REQUESTS,DELTA_READ_IO_BYTES
,DELTA_WRITE_IO_BYTES,DELTA_INTERCONNECT_IO_BYTES,PGA_ALLOCATED,TEMP_SPACE_ALLOCATED,DBOP_NAME,DBOP_EXEC_ID,CON_DBID,CON_ID',
operation_id=>l_operation_id
);
dbms_output.put_line('Operation ID:'||l_operation_id||' finished successfully');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Operation ID:'||l_operation_id||' raised an error');
RAISE;
END;
/

The copy data takes slightly longer than the query on the external table.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Operation ID:31 finished successfully

PL/SQL procedure successfully completed.

Elapsed: 00:02:01.11
The status of the copy operation is reported in USER_LOAD_OPERATIONS.  This includes the number of rows loaded and the names of external tables that are created for the log and bad files.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">set lines 120
column type format a10
column file_uri_list format a64
column start_time format a32
column update_time format a32
column owner_name format a10
column table_name format a10
column partition_name format a10
column subpartition_name format a10
column logfile_table format a15
column badfile_table format a15
column tempext_table format a30
select * from user_load_operations where id = &operation_id;

ID TYPE SID SERIAL# START_TIME UPDATE_TIME STATUS
---------- ---------- ---------- ---------- -------------------------------- -------------------------------- ---------
OWNER_NAME TABLE_NAME PARTITION_ SUBPARTITI FILE_URI_LIST ROWS_LOADED
---------- ---------- ---------- ---------- ---------------------------------------------------------------- -----------
LOGFILE_TABLE BADFILE_TABLE TEMPEXT_TABLE
--------------- --------------- ------------------------------
31 COPY 19965 44088 07-MAY-20 17.03.20.328263 +01:00 07-MAY-20 17.05.36.157680 +01:00 COMPLETED
SOE ASH_HIST https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu 1409305
/b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz
COPY$31_LOG COPY$31_BAD COPY$Y2R021UKPJ5F75JCMSKL

An external table is temporarily created by the COPY_DATA procedure but is then dropped before the procedure completes.  The bad file is empty because the copy operation succeeded without error, but we can query the copy log.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">select * from COPY$31_LOG;

RECORD
------------------------------------------------------------------------------------------------------------------------
LOG file opened at 05/07/20 16:03:21

Total Number of Files=1

Data File: https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucke...

Log File: COPY$31_105537.log

LOG file opened at 05/07/20 16:03:21

Total Number of Files=1

Data File: https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucke...

Log File: COPY$31_105537.log

LOG file opened at 05/07/20 16:03:21

KUP-05014: Warning: Intra source concurrency disabled because the URLs specified for the Cloud Service map to compressed data.

Bad File: COPY$31_105537.bad

Field Definitions for table COPY$Y2R021UKPJ5F75JCMSKL
Record format DELIMITED BY
Data in file has same endianness as the platform
Rows with all null fields are accepted
Table level NULLIF (Field = BLANKS)
Fields in Data Source:

SNAP_ID CHAR (255)
Terminated by "<,>"
Trim whitespace from left and right
DBID CHAR (255)
Terminated by "<,>"
Trim whitespace from left and right
INSTANCE_NUMBER CHAR (255)
Terminated by "<,>"
Trim whitespace from left and right
SAMPLE_ID CHAR (255)
Terminated by "<,>"
Trim whitespace from left and right
SAMPLE_TIME CHAR (255)
Date datatype TIMESTAMP, date mask YYYY-MM-DD/HH24:mi:ss.ff
Terminated by "<,>"
Trim whitespace from left and right

CON_ID CHAR (255)
Terminated by "<,>"
Trim whitespace from left and right

Date Cache Statistics for table COPY$Y2R021UKPJ5F75JCMSKL
Date conversion cache disabled due to overflow (default size: 1000)

365 rows selected.
These files are written to the DATA_DUMP_DIR database directory.  We don't have access to the database file system in Autonomous, so Oracle has provided the LIST_FILES procedure in DBMS_CLOUD so that we can see what files are in a directory.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Set pages 99 lines 150
Column object_name format a32
Column created format a32
Column last_modified format a32
Column checksum format a20
SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

OBJECT_NAME BYTES CHECKSUM CREATED LAST_MODIFIED
-------------------------------- ---------- -------------------- -------------------------------- --------------------------------

COPY$31_dflt.log 0 07-MAY-20 16.03.20.000000 +00:00 07-MAY-20 16.03.20.000000 +00:00
COPY$31_dflt.bad 0 07-MAY-20 16.03.20.000000 +00:00 07-MAY-20 16.03.20.000000 +00:00
COPY$31_105537.log 13591 07-MAY-20 16.03.21.000000 +00:00 07-MAY-20 16.05.35.000000 +00:00

Statistics are automatically collected on the table by the copy process because it was done in direct-path mode.  We can see the number of rows retrieved corresponds with the number of rows imported by the COPY_DATA procedure.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Set pages 99 lines 140
Column owner format a10
Column IM_STAT_UPDATE_TIME format a30
Select *
from all_tab_statistics
Where table_name = 'ASH_HIST';

OWNER TABLE_NAME PARTITION_ PARTITION_POSITION SUBPARTITI SUBPARTITION_POSITION OBJECT_TYPE NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- ---------- ---------- ------------------ ---------- --------------------- ------------ ---------- ---------- ------------
AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO IM_IMCU_COUNT
---------- ---------- ----------- ------------------------- ------------------- ----------------- ------------------- -------------
IM_BLOCK_COUNT IM_STAT_UPDATE_TIME SCAN_RATE SAMPLE_SIZE LAST_ANALYZED GLO USE STATT STALE_S SCOPE
-------------- ------------------------------ ---------- ----------- ------------------- --- --- ----- ------- -------
SOE ASH_HIST TABLE 1409305 19426 0
0 0 486 0 0
1409305 15:16:14 07.05.2020 YES NO NO SHARED

I can confirm that the data is compressed because the compression type of every row is type 8 (HCC QUERY LOW).  See also DBMS_COMPRESSION Compression Types
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">WITH x AS (
select dbms_compression.get_compression_type('SOE', 'ASH_HIST', rowid) ctype
from soe.ash_hist sample (.1))
Select ctype, count(*) From x group by ctype;

CTYPE COUNT(*)
---------- ----------
8 14097
I can find this SQL Statement in the Performance Hub. 
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">INSERT /*+ append enable_parallel_dml */ INTO "SOE"."ASH_HIST" SELECT * FROM COPY$Y2R021UKPJ5F75JCMSKL

Therefore, the data was queried from the temporary external table into the permanent table, in direct path mode and in parallel.
I can also look at the OCI Performance Hub and see that mode of the time was spent on CPU.  I can see the SQL_ID of the insert statement and the call to the DBMS_CLOUD procedure.

I can drill in further to the exact SQL statement.

When I query the table I get exactly the same data as previously with the external table.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">set autotrace on timi on lines 180 trimspool on
break on report
compute sum of ash_secs on report
column min(sample_time) format a22
column max(sample_time) format a22
select event, sum(10) ash_Secs, min(sample_time), max(sample_time)
from soe.ash_hist
group by event
order by ash_Secs desc
;

EVENT ASH_SECS MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)
---------------------------------------------------------------- ---------- ---------------------- ----------------------
10304530 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
direct path read 3258500 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
SQL*Net more data to client 269220 22-MAR-20 10.00.31.205 07-APR-20 22.59.30.275
direct path write temp 32400 22-MAR-20 11.39.53.996 07-APR-20 21.43.47.329
gc cr block busy 24930 22-MAR-20 10.51.33.189 07-APR-20 22.56.56.804

latch free 10 28-MAR-20 20.26.11.307 28-MAR-20 20.26.11.307
----------
sum 14093050

86 rows selected.

Elapsed: 00:00:00.62

I can see that the execution plan is now a single serial full scan of the table.
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Execution Plan
----------------------------------------------------------
Plan hash value: 1336681691

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 84 | 1428 | 1848 (9)| 00:00:01 |
| 1 | SORT ORDER BY | | 84 | 1428 | 1848 (9)| 00:00:01 |
| 2 | HASH GROUP BY | | 84 | 1428 | 1848 (9)| 00:00:01 |
| 3 | TABLE ACCESS STORAGE FULL| ASH_HIST | 1409K| 22M| 1753 (4)| 00:00:01 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
11 recursive calls
13 db block gets
19255 consistent gets
19247 physical reads
2436 redo size
5428 bytes sent via SQL*Net to client
602 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
86 rows processed

Support the Fight Against Inequality: Resources and Ways to Act

The past few months have been tiring for everyone. As the coronavirus spread across the globe, most of us thought that we were going to live with the uncomfortableness of shelter-in-place for a few months before things could return to normal. We thought that what would consume most of our free time was TikTok videos, Animal Crossing, Netflix, and maybe a reignition of hobbies. Unfortunately, this has not been the case.

Fast-forward to today. Society has not returned to normal and instead, we have had more time to engage on the topic of race on a global scale — specifically, how unfairly Black Americans are treated in American society.

We are not only bearing witness to how disproportionately the COVID-19 pandemic has hit Black and Brown Americans, we are seeing the injustices and violence Black Americans face daily in an amplified manner. Whether it’s having the cops called on you following a simple and reasonable request in the park, going jogging in your neighborhood, or being asleep in your own home, the world is watching and finally responding to these injustices. From Eric Garner to George Floyd, the list of people we grieve over is far too long.

We are hurt, confused, frustrated, angry, and just tired.

We are tired but never done.

How can you support your Black colleagues and friends?

  • Give them a bit more time, space, and compassion.
  • Understand that some of them are whiplashed and at a loss for what to do.
  • Let them come to you with causes you can support.
  • Collectively agree on a way of showing wordless support, like an emoji for example:

How can you support this movement?

Understand that this movement is not history, nor will it soon be over. We need to fight for equality until life, liberty, and the pursuit of happiness are available for all.

Here is a list of places you can amplify, donate to, or sign petitions for change:

Donate

Donate to any of these organizations and petitions to show support and help advance the agenda for equal representation and justice.

Sign

Sign any of these petitions to show support for change and accountability in our judicial system.

Do

Educational resources

Dedicate time to learn more deeply about institutionalized racism in America, and how to safely take action against it.

Mental health resources

  • Ethel’s Club – A Black-owned and -operated social club that offers access to Black therapists and a multitude of creative events for People of Color. 
  • Crisis Text Line – A different approach to crisis intervention, Crisis Text Line offers you help when you text 741-741. You’ll be able to chat with someone who is willing to listen and provide you with additional resources.
  • Shine Text –  A Black-owned self-care app through which you can sign up to receive cheerful texts and tips every day. 
  • Therapy for Black Girls – A Black-owned directory to help you find Black therapists in your area. 
  • BEAM Community – A Black emotional and mental health collective committed to the health and healing of Black communities.
  • Self-Care Tips for Black People Who Are Struggling With This Very Painful Week – A resource on VICE with tips that may provide a bit of relief.

Tips for protesting

Lastly, let’s celebrate solidarity and beauty when we see it:

Stay safe out there!

Not Sure How to Get Your Blog Off the Ground? Join Our New Workshop.

Starting a blog is easy and free on WordPress.com. But what if you’re new to blogging? If you need guidance on best practices, actionable tips on how to grow your audience and find inspiration to write, and constructive feedback from experts and fellow bloggers, you should join us at Blogging: From Concept to Content. It’s a three-day, hands-on, intensive workshop that will take you from “I’m not entirely sure what I’m doing?” to “I’m a blogger!”

Date: June 16–18, 2020
Time: 7:00 a.m. – 11:00 a.m. PDT | 8:00 a.m. –12:00 p.m. MDT | 9:00 am-1:00 pm CDT | 10:00 a.m. – 2:00 p.m. EDT| 14:00 – 18:00 UTC
Location: Online via Zoom and private blog
Cost: Early Bird Price — US$99 until 23:59 UTC on June 8, 2020. Regular price — US$179 from June 9 – June 15, 2020.
Register now: https://wordpress.com/blogging-basics-workshop/

Featuring guest speakers and WordPress.com experts in areas like content and writing, SEO, design, and digital marketing, the workshop will include daily assignments and interactive discussions. You’ll also have plenty of opportunities to interact directly with the instructors as well as with Happiness Engineers. At the end of the workshop, you’ll walk away with:

  • A ready-to-launch blog.
  • An editorial calendar for the next 8–12 weeks.
  • A well-stocked toolkit of tips and techniques to continue to develop your blog and grow its reach.
  • Finally, and at least as important: a community of new blogging friends to learn from and grow with long after the workshop has ended.

We created this workshop for new bloggers who crave a structured, step-by-step approach to creating a blog that reflects their vision and voice, and who don’t want to waste time looking for answers all over the web. Be prepared to dive in and do the work! You won’t regret this investment, and you’ll be in great company.

Seats are limited to facilitate interaction between participants and instructors, so register now to save your slot. By registering this week, you’ll take advantage of our Early Bird Price of US$99 through June 8, after which the regular registration price of $179 will take effect.

See you then!

Loading a Flat File from OCI Object Storage into an Autonomous Database. Part 2. Reading from Object Storage with an External Table

This blog is the second in a series of three that looks at transferring a file to Oracle Cloud Infrastructure (OCI) Object Storage, and then reading it into the database with an external table or copying it into a regular table.

Create A Credential 

First, I need to create a credential that the database will use to connect to the OCI Object Storage. This is not the same as the credential that the OCI CLI used to connect.
In the OCI interface navigate to Identity ➧ Users ➧ User Details, and create an Authentication Token.

It is important to copy the token at this point because you will not see it again.

Now you can put the token into a database credential.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">connect admin/Password2020@gofaster1b_tp 
BEGIN
DBMS_CLOUD.CREATE_CREDENTIAL (
credential_name => 'MY_BUCKET',
username=> 'oraclecloud1@go-faster.co.uk',
password=> 'K7xfi-mG<1z:dq code="" end="" m="">

Note: The visibility of the bucket that I created earlier is private by default. Therefore, I can only access it with an authenticated user. If I were to create a credential for an unauthenticated user, it could only be accessed as public bucket. Otherwise, I would obtain an error.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-20404: Object not found -
https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucke...
05-1552/o/dba_hist_active_sess_history.txt.gz
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 964
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_INTERNAL", line 3891
ORA-06512: at line 1

Create an External Table

In my blog Reading the Active Session History Compressed Export File in eDB360/SQLd360 as an External Table, I showed how to create an external table to read a compressed file.  Now I am going to do the same thing as, except that now I am going to read it from OCI Object Storage into an external table created with DBMS_CLOUD.

  • I have to provide a list of columns in the external table and a list of fields in the flat file.
  • N.B. Some column names end in a # symbol.  These must be put in double-quotes in the field list though this is not needed in the column list.
  • The Access Parameters section of the ORACLE_LOADER access driver that I used to create the external table becomes contents the format parameter.  I have created a JSON object to hold the various parameters.  The parameters are not exactly the same, in fact, I have added some.  See also DBMS_CLOUD Package Format Options
#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">DROP TABLE ash_hist PURGE;
BEGIN
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name =>'ASH_HIST',
credential_name =>'MY_BUCKET',
file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz',
format => json_object('blankasnull' value 'true'
,'compression' value 'gzip'
,'dateformat' value 'YYYY-MM-DD/HH24:mi:ss'
,'timestampformat' value 'YYYY-MM-DD/HH24:mi:ss.ff'
,'delimiter' value '<,>'
,'ignoreblanklines' value 'true'
,'rejectlimit' value '10'
,'removequotes' value 'true'
,'trimspaces' value 'lrtrim'
),
column_list => 'SNAP_ID NUMBER
,DBID NUMBER
,INSTANCE_NUMBER NUMBER
,SAMPLE_ID NUMBER
,SAMPLE_TIME TIMESTAMP(3)
,SESSION_ID NUMBER
,SESSION_SERIAL# NUMBER
,SESSION_TYPE VARCHAR2(10)
,FLAGS NUMBER
,USER_ID NUMBER
-----------------------------------------
,SQL_ID VARCHAR2(13)
,IS_SQLID_CURRENT VARCHAR2(1)
,SQL_CHILD_NUMBER NUMBER
,SQL_OPCODE NUMBER
,SQL_OPNAME VARCHAR2(64)
,FORCE_MATCHING_SIGNATURE NUMBER
,TOP_LEVEL_SQL_ID VARCHAR2(13)
,TOP_LEVEL_SQL_OPCODE NUMBER
,SQL_PLAN_HASH_VALUE NUMBER
,SQL_FULL_PLAN_HASH_VALUE NUMBER
-----------------------------------------
,SQL_ADAPTIVE_PLAN_RESOLVED NUMBER
,SQL_PLAN_LINE_ID NUMBER
,SQL_PLAN_OPERATION VARCHAR2(64)
,SQL_PLAN_OPTIONS VARCHAR2(64)
,SQL_EXEC_ID NUMBER
,SQL_EXEC_START DATE
,PLSQL_ENTRY_OBJECT_ID NUMBER
,PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
,PLSQL_OBJECT_ID NUMBER
,PLSQL_SUBPROGRAM_ID NUMBER
-----------------------------------------
,QC_INSTANCE_ID NUMBER
,QC_SESSION_ID NUMBER
,QC_SESSION_SERIAL# NUMBER
,PX_FLAGS NUMBER
,EVENT VARCHAR2(64)
,EVENT_ID NUMBER
,SEQ# NUMBER
,P1TEXT VARCHAR2(64)
,P1 NUMBER
,P2TEXT VARCHAR2(64)
-----------------------------------------
,P2 NUMBER
,P3TEXT VARCHAR2(64)
,P3 NUMBER
,WAIT_CLASS VARCHAR2(64)
,WAIT_CLASS_ID NUMBER
,WAIT_TIME NUMBER
,SESSION_STATE VARCHAR2(7)
,TIME_WAITED NUMBER
,BLOCKING_SESSION_STATUS VARCHAR2(11)
,BLOCKING_SESSION NUMBER
-----------------------------------------
,BLOCKING_SESSION_SERIAL# NUMBER
,BLOCKING_INST_ID NUMBER
,BLOCKING_HANGCHAIN_INFO VARCHAR2(1)
,CURRENT_OBJ# NUMBER
,CURRENT_FILE# NUMBER
,CURRENT_BLOCK# NUMBER
,CURRENT_ROW# NUMBER
,TOP_LEVEL_CALL# NUMBER
,TOP_LEVEL_CALL_NAME VARCHAR2(64)
,CONSUMER_GROUP_ID NUMBER
-----------------------------------------
,XID RAW(8)
,REMOTE_INSTANCE# NUMBER
,TIME_MODEL NUMBER
,IN_CONNECTION_MGMT VARCHAR2(1)
,IN_PARSE VARCHAR2(1)
,IN_HARD_PARSE VARCHAR2(1)
,IN_SQL_EXECUTION VARCHAR2(1)
,IN_PLSQL_EXECUTION VARCHAR2(1)
,IN_PLSQL_RPC VARCHAR2(1)
,IN_PLSQL_COMPILATION VARCHAR2(1)
-----------------------------------------
,IN_JAVA_EXECUTION VARCHAR2(1)
,IN_BIND VARCHAR2(1)
,IN_CURSOR_CLOSE VARCHAR2(1)
,IN_SEQUENCE_LOAD VARCHAR2(1)
,IN_INMEMORY_QUERY VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_POPULATE VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_PREPOPULATE VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_REPOPULATE VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_TREPOPULATE VARCHAR2(1) /*added 12.1*/
,CAPTURE_OVERHEAD VARCHAR2(1)
-----------------------------------------
,REPLAY_OVERHEAD VARCHAR2(1)
,IS_CAPTURED VARCHAR2(1)
,IS_REPLAYED VARCHAR2(1)
,SERVICE_HASH NUMBER
,PROGRAM VARCHAR2(64)
,MODULE VARCHAR2(64)
,ACTION VARCHAR2(64)
,CLIENT_ID VARCHAR2(64)
,MACHINE VARCHAR2(64)
,PORT NUMBER
-----------------------------------------
,ECID VARCHAR2(64)
,DBREPLAY_FILE_ID NUMBER /*added 12.1*/
,DBREPLAY_CALL_COUNTER NUMBER /*added 12.1*/
,TM_DELTA_TIME NUMBER
,TM_DELTA_CPU_TIME NUMBER
,TM_DELTA_DB_TIME NUMBER
,DELTA_TIME NUMBER
,DELTA_READ_IO_REQUESTS NUMBER
,DELTA_WRITE_IO_REQUESTS NUMBER
,DELTA_READ_IO_BYTES NUMBER
-----------------------------------------
,DELTA_WRITE_IO_BYTES NUMBER
,DELTA_INTERCONNECT_IO_BYTES NUMBER
,PGA_ALLOCATED NUMBER
,TEMP_SPACE_ALLOCATED NUMBER
,DBOP_NAME VARCHAR2(64) /*added 12.1*/
,DBOP_EXEC_ID NUMBER /*added 12.1*/
,CON_DBID NUMBER /*added 12.1*/
,CON_ID NUMBER /*added 12.1*/'
-----------------------------------------
,field_list=>'SNAP_ID,DBID,INSTANCE_NUMBER,SAMPLE_ID,SAMPLE_TIME ,SESSION_ID,"SESSION_SERIAL#",SESSION_TYPE,FLAGS,USER_ID
,SQL_ID,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,SQL_OPCODE,SQL_OPNAME,FORCE_MATCHING_SIGNATURE,TOP_LEVEL_SQL_ID,TOP_LEVEL_SQL_OPCODE,SQL_PLAN_HASH_VALUE,SQL_FULL_PLAN_HASH_VALUE
,SQL_ADAPTIVE_PLAN_RESOLVED,SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS,SQL_EXEC_ID,SQL_EXEC_START ,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID
,QC_INSTANCE_ID,QC_SESSION_ID,"QC_SESSION_SERIAL#",PX_FLAGS,EVENT,EVENT_ID,"SEQ#",P1TEXT,P1,P2TEXT
,P2,P3TEXT,P3,WAIT_CLASS,WAIT_CLASS_ID,WAIT_TIME,SESSION_STATE,TIME_WAITED,BLOCKING_SESSION_STATUS,BLOCKING_SESSION
,"BLOCKING_SESSION_SERIAL#",BLOCKING_INST_ID,BLOCKING_HANGCHAIN_INFO,"CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#",TOP_LEVEL_CALL_NAME,CONSUMER_GROUP_ID
,XID,"REMOTE_INSTANCE#",TIME_MODEL,IN_CONNECTION_MGMT,IN_PARSE,IN_HARD_PARSE,IN_SQL_EXECUTION,IN_PLSQL_EXECUTION,IN_PLSQL_RPC,IN_PLSQL_COMPILATION
,IN_JAVA_EXECUTION,IN_BIND,IN_CURSOR_CLOSE,IN_SEQUENCE_LOAD,IN_INMEMORY_QUERY,IN_INMEMORY_POPULATE,IN_INMEMORY_PREPOPULATE,IN_INMEMORY_REPOPULATE,IN_INMEMORY_TREPOPULATE,CAPTURE_OVERHEAD
,REPLAY_OVERHEAD,IS_CAPTURED,IS_REPLAYED,SERVICE_HASH,PROGRAM,MODULE,ACTION,CLIENT_ID,MACHINE,PORT
,ECID,DBREPLAY_FILE_ID,DBREPLAY_CALL_COUNTER,TM_DELTA_TIME,TM_DELTA_CPU_TIME,TM_DELTA_DB_TIME,DELTA_TIME,DELTA_READ_IO_REQUESTS,DELTA_WRITE_IO_REQUESTS,DELTA_READ_IO_BYTES
,DELTA_WRITE_IO_BYTES,DELTA_INTERCONNECT_IO_BYTES,PGA_ALLOCATED,TEMP_SPACE_ALLOCATED,DBOP_NAME,DBOP_EXEC_ID,CON_DBID,CON_ID'
);
END;
/

This file contains 1.4M rows in a 200Mb compressed file. If uncompressed it would be 4.6Gb. It takes about 81 seconds to perform a full scan on it.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">set autotrace on timi on pages 99 lines 160
break on report
compute sum of ash_secs on report
column event format a40
column min(sample_time) format a22
column max(sample_time) format a22
select event, sum(10) ash_Secs, min(sample_time), max(sample_time)
from ash_hist
--where rownum <= 1000
group by event
order by ash_Secs desc
;
EVENT ASH_SECS MIN(SAMPLE_TIME) MAX(SAMPLE_TIME)
---------------------------------------- ---------- ---------------------- ----------------------
10304530 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
direct path read 3258500 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
SQL*Net more data to client 269220 22-MAR-20 10.00.31.205 07-APR-20 22.59.30.275
direct path write temp 32400 22-MAR-20 11.39.53.996 07-APR-20 21.43.47.329
gc cr block busy 24930 22-MAR-20 10.51.33.189 07-APR-20 22.56.56.804

latch: gc element 10 30-MAR-20 18.42.51.748 30-MAR-20 18.42.51.748
----------
sum 14093050

86 rows selected.

Elapsed: 00:01:21.17

We can see from the plan that it full scanned the external table in parallel.

#eeeeee; font-family: "courier new"overflow: auto; line-height: 95%; width: 95%;">Execution Plan
----------------------------------------------------------
Plan hash value: 4220750095

------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8344K| 374M| 1417 (33)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10002 | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,02 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,02 | PCWP | |
| 4 | PX RECEIVE | | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,02 | PCWP | |
| 5 | PX SEND RANGE | :TQ10001 | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,01 | P->P | RANGE |
| 6 | HASH GROUP BY | | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,01 | PCWP | |
| 7 | PX RECEIVE | | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,01 | PCWP | |
| 8 | PX SEND HASH | :TQ10000 | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,00 | P->P | HASH |
| 9 | HASH GROUP BY | | 8344K| 374M| 1417 (33)| 00:00:01 | Q1,00 | PCWP | |
| 10 | PX BLOCK ITERATOR | | 8344K| 374M| 1089 (13)| 00:00:01 | Q1,00 | PCWC | |
| 11 | EXTERNAL TABLE ACCESS FULL| ASH_HIST | 8344K| 374M| 1089 (13)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------

Note
-----
- automatic DOP: Computed Degree of Parallelism is 2 because of degree limit


Statistics
----------------------------------------------------------
2617 recursive calls
3 db block gets
2751 consistent gets
0 physical reads
728 redo size
5428 bytes sent via SQL*Net to client
602 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
346 sorts (memory)
0 sorts (disk)
86 rows processed

In the next post, I will explain how to copy the data directly from Object Storage into a regular table.

Celebrating Pride Month with Out in Tech

Happy Pride Month! Last year, I shared resources and highlighted organizations doing awesome work in the LGBTQ+ community. This year, I’m excited to tell you more about Out in Tech, an organization that Automattic has partnered with for the past four years. I’m proud to say that this year, the Queeromattic Employee Resource Group — an employee-led collective for LGBTQ+ initiatives at Automattic — is co-sponsoring this partnership for the first time. 

“We’re a global nonprofit community of 40,000 members working toward a world in which LGBTQ+ people are empowered, well-represented in the tech industry, and have full agency, from intern to CEO,” says Gary Goldman, the Senior Program Director of Out in Tech. As the Queeromattic Lead, I’ve been fortunate to benefit from the wonderful and empowering community Out in Tech has created through their Qorporate Roundtables, vibrant Slack community, and virtual hangouts in light of COVID-19. It brings me great joy to share more about Out In Tech with you all in this recent interview with Gary. 

https://en-blog.files.wordpress.com/2020/06/out-in-tech.jpg?w=150 150w, https://en-blog.files.wordpress.com/2020/06/out-in-tech.jpg?w=300 300w, https://en-blog.files.wordpress.com/2020/06/out-in-tech.jpg?w=768 768w, https://en-blog.files.wordpress.com/2020/06/out-in-tech.jpg 1616w" sizes="(max-width: 1024px) 100vw, 1024px" />

Q. Tell us a bit about yourself! How do you identify? How did you get started with Out in Tech?

I identify as a cisgender gay man. Before Out in Tech, I worked as a United Nations consultant for five years in data management. During that time, I was a volunteer for Out in Tech as head of the New York chapter. It has been a dream come true to transition to being a staff member and work for my actual favorite organization out there. 

Q. Can you share any exciting things Out in Tech has planned for Pride?

The unsung heroes of the LGBTQ+ community are the activists working on the ground in the 70+ countries where being queer is illegal (and sometimes even punishable by death). 

On June 20, we’ll be building WordPress.com websites for 10 incredible organizations in these countries; they’re planning on using these sites to advocate for policy change, grow their community, and fundraise.

We’re also hosting a virtual Pride series the second week of June for those working in customer experience (June 10) as well as a day of workshops for folks currently navigating the job market (June 13).  To learn more, visit outintech.com.

Q. Is there one person you’ve helped over the years (or a project you’ve worked on) that stands out in your memory?

I’ve noticed that a lot of people in the LGBTQ+ tech community have been eager to leverage their skills to make the world a better place. 

Derrick Reyes was an early recipient of the Out in Tech Coding Scholarship. Since graduating, they’ve been leveraging their new skills to create an incredible company called Queerly Health, which helps you find and book LGBTQ+ friendly health and wellness practitioners. It was a real full-circle moment to welcome them as a panelist at an Out in Tech event back in January. 

Q. Has partnering with Automattic helped your work?

This partnership has made all the difference in Out in Tech’s work, and that’s not an understatement. When I was a United Nations consultant, I traveled to dozens of countries where being LGBTQ+ is outlawed, and where activists needed a digital platform to amplify their voices. 

WordPress turned that vision into a reality. 

Since 2017, the Out in Tech Digital Corps has built over 100 WordPress.com websites for activists in 50+ countries. 

Automattic provides these activists with hosting, themes, and domains free of charge. We also have Automatticians support us technically during the Digital Corps build days — a special shout-out to Mindy Postoff, who has been to over 10 build days!

Simply put, Out in Tech is powered by Automattic, and we’re incredibly grateful to Marlene Ho, Megan Marcel, and Matt Mullenweg for making it all happen. 

Q. In this time when organizations have pivoted to digital events, can you tell us about your virtual events and other ways to participate in your community?  

Out in Tech’s mission is to create opportunities for our members to advance their careers, grow their networks, and leverage tech for social change. During COVID-19, we’re still doing just that — but digitally. 

Every week, members have an opportunity to hear from dozens of companies that are actively hiring and to network with each other during Queer and Trans People of Color (QTPOC) socials and even RuPaul’s Drag Race viewing parties. We also have virtual events featuring prominent LGBTQ+ tech leaders, such as Arlan Hamilton, the founder of Backstage Capital, and Jeff Titterton, the chief marketing officer of Zendesk. 

When it comes to leveraging tech for social change, 100 volunteers built websites for organizations in Senegal, Uganda, Nigeria, and Zimbabwe (among others), and we’re doing it again in June. This spring, our mentorship program connected 83 LGBTQ+ youth to tech mentors for eight weeks. They’re graduating at the end of this month, and we hope some of you reading this will hire them as interns!

Q. What do you look for when partnering with organizations and LGBTQ+ activists around the world?

Out in Tech accepts applications from LGBTQ+ groups on every continent on a rolling basis. When our Digital Corps leadership team reviews applications, they assess four main criteria: 

  • Does the LGBTQ+ organization have a good reason for needing a website? This can range from needing to crowdsource input from the community to applying for grants. 
  • Do they already have a website and just need a revamp? We only select organizations who either do not have an existing web presence, or whose website is very challenging to navigate. 
  • Has the organization been around for more than one year? We want to ensure that the groups we support are established and are going to stick around for the long haul after we build their shiny new website. 
  • Does the organization have at least a few volunteers to keep the website active and up to date once we deliver a user guide to them? We regularly track and monitor which sites are active and how they’re being used.  This helps us to continuously improve our efforts to unite the global LGBTQ+ community.

Community is so important, especially in these times, and I’m doubly thankful for people like Gary who have helped the LGBTQ+ community remain strong. What organizations are you celebrating this month? How are you creating community from afar? Share in the comments below!

At WordPress.com, we strive to be a platform that democratizes publishing so that anyone can share their stories regardless of income, gender, politics, language, or where they live in the world. This month is a great reminder for why we work hard to expand the open web.