Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Nostalgia and choosing your in-flight movie

First thing to note on this post. No tech content in this one. Just some nostalgia.

Couple of days ago, I was flying from Perth to Dubai on my way to APEX Connect in Bonn. Because this is an 11hour hell in a death tube flight I settled in to my standard sleepless task of watching movies to pass the time. I try to steer clear of going exclusively with new releases because I know I’ll always be travelling again soon, so I try not to exhaust all my options too soon Smile

I was browsing through the “Movie Classics” section and found a movie from my childhood: The Right Stuff.

If you haven’t seen it, it’s a somewhat romanticized and slightly cheesy recount of the space race from Chuck Yeager breaking the sound barrier, through to the first American in space and subsequent launches, but stopping short of the moon program. In terms of run time, this is not a movie for faint-hearted. If you think Avengers Endgame is ridiculous at 3hrs, then The Right Stuff trumps it with an additional 15 minutes on top of that.

My father loved this movie, and the first time I watched it was with him on a Sony Betamax video recorder in my early teens. We sat and watched it together, and he constantly hit pause to explain to me how significant certain milestones were. Clichéd as it might sound, it was one of those great father-son bonding moments.

To bring at least a little bit of technology into this post, it blows me away that the central character of the film, Chuck Yeager is now on Twitter and has even been gracious enough to answer a couple of my questions over the years. It amazes me – the transition from the portrayal of a figure on screen to someone I’ve managed to communicate with directly on social media.

This year marks the 20th year since my father passed away from early onset Alzheimer’s, and I’ve realised that a re-watching of The Right Stuff on the plane was perhaps a mistake. Because I’ve discovered that it tends to freak out the passenger next to you, when a grown man sits there blubbering away at a movie that doesn’t really have any sad content. So nostalgia is a good thing, but perhaps best saved for somewhere less public Smile

Love you Dad.

#VDC19 Voxxed Days CERN 2019

This was the first time I attended to Voxxed Days. I was also speaker there for a short live-demo talk during lunch. And that was an awesome experience and the occasion to meet people I don’t see in Oracle User Groups conferences. Organized at CERN, the speakers were able to visit the CMS experiment (A 14,000 tonnes 100 meters underground detector, observing the result of the collision of protons accelerated in the LHC), and that probably helps to motivate the best speakers to come. And kudos to the colleagues at the organization of this event.

The event was sponsored by Oracle Groundbreakers. Oracle Switzerland offered some Cloud trials where you don’t have to put your credit card number and that’s a really good initiative, finally.

And look at the end of this post the video made by Romy Lienhard during the CMS visit and the conference.

Keynote: Robert C. Martin, aka Uncle Bob

I really enjoyed hearing Uncle Bob message. I put quotes in bold here. Today, many organizations push to faster releases, more and more feature, and unachievable deadlines. But that, unfortunately, lowers the quality of software delivered.

If we value only the lines of code, the frequency of releases, and the number of features, this apparent productivity is:
unstable productivity
and will fail one day. But who will be responsible for this failure? As a software engineer, you must not ship shit. You may think that you were forced by your manager’s unreasonable deadlines. But remember that you were hired because you know and they don’t. You must be professional and say no. Frequent releases are good only when they ensure that you ship only what works and will be stable. IT professionalism must ban the ship of code that will fail and be unmaintainable.

With my DBA eyes, I see many applications that fail to scale with more users, more data, or that break when a database upgrade reveals a bug that was hiding there. This can be solved only at the root: the quality of the design and code. Do not rely on the UAT to find those design weaknesses. Who tests concurrent updates during UAT? Who tests with the future volume of data? Who tests with the future database versions?

The Error of our Ways

A nice continuation was Kevlin Henney’s session. He is famous showing when the magic shell of software applications is failing, displaying the naked error stack, blue screen, like:

That’s part of our software maker professionalism: not only ship new features specified by the business, but also be sure to handle all the unexpected exceptions. When errors break the magic of the GUI, you will give a very bad impression of your company, and you open many security weaknesses.

In the database area, this means that if you are subject to SQL injection (because not using queries parameters with bind variables), and in addition to that you expose the database and the name of tables, then you are giving two axes to break into your system.

Have fun!

Yes, productivity can be increased by having fun. Holly Cummins from IBM explains the importance of having fun in the workplace and how to get it.

In the database area, this is probably the main reason to go to DevOps: stop that fight between Developer and DBA and be happy to work together.

Follow the link for the blog post and slides, my best part is about DevOps and Automation: Repetition is boring. Automating stuff is fun.

Deep learning in computer vision

The best demo I’ve seen so far was from Krzysztof Kudryński (Nvidia) and Błażej Kubiak (TomTom). First, showing the coding iterations to solve an image recognition pattern, like a human face wearing glasses or not.

And then face recognition, in live demo, with a mobile phone and a laptop: record one face and then be able to recognize the same person within a group of people. Beyond the computer thing, it was also a nice exposure on the demo effect, and the human factor about it. Demos fail in a talk because the conditions are different. I love this fail because of the scientific approach of the speakers to fix it. It didn’t work to record the face of an attendee in the audience. It worked when the speaker did it on himself. Then empirically find what’s different: the glasses, the light,… and finally, the fact that there were many faces in the background. Those things that you know perfectly and forget once you are face to the audience, thinking about your talk, the time, and everything…

The speakers never gave up and finally had a great working demo.

Compact Muon Solenoid, Council Chamber and Main Auditorium

Want to have a look at the venue — between Council Chamber and Main Auditorium? Here’s the video made by Romy Lienhard:


see you next year?

Hi Piotr, Bug 29534218 : WITH THE NEW MULTIHOST EZCONNECT METHOD GET WRONG OUTPUTS is in status 16…

Hi Piotr, Bug 29534218 : WITH THE NEW MULTIHOST EZCONNECT METHOD GET WRONG OUTPUTS is in status 16 — Bug Screening/Triage

Occurence

Before you comment – I do know that the title has a spelling mistake in it. That’s because the Oracle code uses exactly this spelling in one of the little-used features of tracing.

I write a note a few years ago about enabling sql_trace (and other tracing events) system-wide for a single SQL statement. In the note I suggested that you could enable tracing for a few minutes then disable it to minimise the impact on the system while still capturing a reasonable number of statement traces. A recent ODC thread, however, described a problem where a particular statement executed in the order of 1,000,000 times per hour – which is getting on for about 300 executions per second, and you probably don’t want to leave a system-wide trace running for any length of time when things are operating at that rate. Fortunately we can refine the method with the occurence filter to capture a small and limited number of executions, spread over as many sessions as are running. Here’s an example of the syntax:

rem
rem     Script: trace_occur.sql
rem     Author: Jonathan Lewis
rem     Dated:  April 2019
rem

define m_sql_id = 'gu1s28n6y73dg'
define m_sql_id = 'fu0ftxk6jcyya'

alter system set events 
        '
        sql_trace[SQL:&m_sql_id] 
        {occurence: start_after 101, end_after 496}
        bind=true,
        wait=true
        '
;

pause   Run the test script here and press return when it ends

alter system set events 
        '
        sql_trace[SQL:&m_sql_id]
        off
        '
;

All I’ve done, compared to the earlier note, is include in curly brackets, just after identifying the SQL ID, the text: “{occurence: start_after 101 , end_after 496}”. Roughly speaking this means that every session will start counting calls to the given statement and on the hundred and first it will start dumping the trace file, and for a total of 496 calls it will continue dumping the trace file. So it’s possible to make sure that a session does trace but doesn’t dump a huge volume of trace data. Of course I do still execute a call to switch tracing off for the statement otherwise every session that subsequently logs on will still start tracing and dump a few executions into their trace file.

There is, unfortunately, a catch. I don’t know how Oracle is counting for the start_after/end_after values – but it’s not executions of the statement, and it varies with working environment, and it changes as the trace is enabled, and it changes with version, and is probably dependent on the session_cached_cursors parameter, and it behaves differently when interacting with the PL/SQL cursor cache. It is perhaps easiest to show an example.

I have table called test_lobs (id, bytes …) with a unique index on (id) for this test.


create table test_lobs (
        id        number(8,0) primary key,
        bytes     number(8,0)
);

insert into test_lobs values(-1,999):
commit;

execute dbms_stats.gather_table_stats(user,'test_lobs')

And one of my test scripts is as follows:

rem
rem     This generates a statement with SQL_ID = gu1s28n6y73dg
rem

declare
        m_result number;
begin
        for i in 1..1000 loop
                begin
                        select bytes into m_result from test_lobs where id = i;
                exception
                        when others then null;
        end;
        end loop;
end;
/

Running 18.3 I start the trace script from one session, then start the test script from another session. As it stands the SQL statement embedded in the PL/SQL loop will have the SQL_ID I am tracing, so the second session will start dumping a trace file. The big question is: which executions of the statement will it dump? Since I’ve enabled bind variable dumping and the bound value is a simple loop counter it will be easy (!) to find the answer to this question.

To stabilise the results I did the following:

  • Session 1: Create the table.
  • Session 1: Start the trace event
  • Session 2: Connect to the database and run the test
  • Session 1: End the trace event
  • Session 1: Start the trace event again
  • Session 2: Connect to the database again and run the test a second time
  • Session 1: End the trace event

I’ll explain the need for looking at the results of the second cycle in a moment.

The trace file I produced started with the first three lines below, and then repeated the 10 line highlighted fragment a number of times:


PARSING IN CURSOR #140126713239784 len=43 dep=1 uid=104 oct=3 lid=104 tim=168304257545 hv=233016751 ad='63b8f0c0' sqlid='gu1s28n6y73dg'
SELECT BYTES FROM TEST_LOBS WHERE ID = :B1
END OF STMT

====================================================================================================
BINDS #140126713239784:

 Bind#0
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1206001 frm=00 csi=00 siz=24 off=0
  kxsbbbfp=7f71cb0c67c0  bln=22  avl=02  flg=05
  value=50
EXEC #140126713239784:c=0,e=57,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2054437130,tim=168304262695
FETCH #140126713239784:c=0,e=3,p=0,cr=1,cu=0,mis=0,r=0,dep=1,og=1,plh=2054437130,tim=168304262729
CLOSE #140126713239784:c=0,e=1,dep=1,type=3,tim=168304262772
====================================================================================================

Notice the bind value report. A key feature that we are interested in is the first “value=” reported and the last “value=”. In my case the low/high were 26 and 87, for a total of 62 executions. A little arithmetic (and a few corroborating checks) highlight the significance of the following:

  • I started tracing after the 25th execution, and 25 * 4 + 1 = 101, my start_after value.
  • I traced 62 executions and 62 * 8 = 496, my end_after value.

Oracle is counting something whenever it hits the SQL_ID we’ve specified but (for SQL inside a PL/SQL loop) it’s counting something which happens 4 times for each execution; then when it hits the start_after and starts tracing whatever it counts happens twice as often each time around the loop.

My general response to this type of detail is: “Argh!!!” – by the way. Maybe a call to Frits or Stefan asking them to trace C functions is in order.

By this time you may have guessed why I examined the trace file from the second run of the test. The counting seems to include counts of database calls that take place in the recursive SQL needed to optimise / hard parse the query – anything that relates to the SQL_ID we specify may be included in the counts. So on the first test I got a load of garbage in the trace file then saw a partial dump of the trace data for value=2 and the trace file ended partway through the trace data for value=17.

As a further test, I had a go with pure SQL calls in a test script:


set serveroutput off
variable b1 number;

exec :b1 := 100
select bytes from test_lobs where id = :b1;

exec :b1 := 101
select bytes from test_lobs where id = :b1;

...

exec :b1 := 129
select bytes from test_lobs where id = :b1;

After getting a stable result, versions 12.1.0.2 and 18.3.0.0 behaved differently;

  • 18.3.0.0 – counted 5 for every execution, so start_after = 16 skipped the first 3 executions and started tracing for value = 103
  • 12.1.0.2 – counted 7 for the first execution and 5 thereafter, so start_after=8 skipped one execution, start_after=13 skipped two and so on.
  • Both versions counted 10 for every execution while tracing was enabled, so end_after = 30 traced 3 executions in both cases.

It’s possible, of course, that some differences in the way session_cached_cursors works would for the small difference – but I suspect I could have spent a couple of days trying to sort out minor variations due to slight parameter and implementation changes between versions. It’s also possible that some of my guesses are wrong and there is more method to the madness than I have spotted.

Conclusion

It is possible to enable tracing system-wide for a limited number of executions per session of a given statement; however the number of executions that might take place before tracing starts and the number of executions actually traced depends on a variety of details of which some may be outside your control.

As a baseline, it looks as if the number of executions before tracing starts is going to be about one-fifth of the value you set for start_after, and the number of executions trace will be about one-tenth of the end_after; however recursive SQL (perhaps even including dynamic sampling) can get caught up in the counts, potentially reducing the number of executions of the target statement that you see.

LOB length

This note is a reminder combined with a warning about unexpected changes as you move from version to version. Since it involves LOBs (large objects) it may not be relevant for most people but since there’s a significant change in the default character set for the database as you move up to 18.3 (or maybe even as you move to 12.2) anyone using character LOBs may get a surprise.

Here’s a simple script that I’m first going to run on an instance of 11.2.0.4:

rem
rem     Script:         lob_length_4.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2019
rem

create table t1(
        n1      number,
        version varchar2(8),
        v1      varchar2(4000),
        c1      clob,
        nc1     nclob
)
lob (c1) store as securefile c1_lob(
        enable storage in row
        chunk 8K
        cache logging
)
lob (nc1) store as securefile nc1_lob(
        enable storage in row
        chunk 8K
        cache logging
)
;

insert into t1 values( 0,'General',  null,               empty_clob(),       empty_clob()      ) ;

insert into t1 values( 1,'11.2.0.4', rpad('x',1951,'x'), rpad('x',1951,'x'), rpad('x',1951,'x')) ;
insert into t1 values( 2,'11.2.0.4', rpad('x',1952,'x'), rpad('x',1952,'x'), rpad('x',1952,'x')) ;

insert into t1 values( 3,'12.1.0.2', rpad('x',1951,'x'), rpad('x',1951,'x'), rpad('x',1951,'x')) ;
insert into t1 values( 4,'12.1.0.2', rpad('x',1952,'x'), rpad('x',1952,'x'), rpad('x',1952,'x')) ;

insert into t1 values( 5,'12.2.0.1', rpad('x',1937,'x'), rpad('x',1937,'x'), rpad('x',1937,'x')) ;
insert into t1 values( 6,'12.2.0.1', rpad('x',1938,'x'), rpad('x',1938,'x'), rpad('x',1938,'x')) ;

insert into t1 values( 7,'18.3.0.0', rpad('x',1984,'x'), rpad('x',1984,'x'), rpad('x',1984,'x')) ;
insert into t1 values( 8,'18.3.0.0', rpad('x',1985,'x'), rpad('x',1985,'x'), rpad('x',1985,'x')) ;

begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 'T1',
                method_opt  => 'for all columns size 1'
        );
end;
/

break on version skip 1 on report

compute avg of sys_op_opnsize(c1)  on report
compute avg of sys_op_opnsize(nc1) on report

select
        version, n1,
        length(v1), length(c1), length(nc1),
        sys_op_opnsize(v1), sys_op_opnsize(c1), sys_op_opnsize(nc1)
from
        t1
order by
        n1
;

select  avg_row_len
from    user_tables
where   table_name = 'T1'
;

select  column_name, avg_col_len
from    user_tab_cols
where   table_name = 'T1'
order by
        column_id
;

I’ve created a table holding a varchar2() a CLOB, and an NCLOB, then I’ve inserted some rows into that table, labelling the rows in pairs with what appear to be Oracle version numbers, with one extra row labelled “General” that holds the special “empty LOB value (note a NULL and an empty clob behave very differently). Then I’ve reported the lengths of the LOB columns in two different ways, once using the standard length() function (I could have used the dbms_lob.getlength() function) and once using the internal sys_op_opnsize() function that Oracle uses in its queries to gather table stats.

Here’s the output from the script. Since this first run is on 11.2.0.4 I want you to note, particularly, the numbers in the rows labelled 11.2.0.4

VERSION          N1 LENGTH(V1) LENGTH(C1) LENGTH(NC1) SYS_OP_OPNSIZE(V1) SYS_OP_OPNSIZE(C1) SYS_OP_OPNSIZE(NC1)
-------- ---------- ---------- ---------- ----------- ------------------ ------------------ -------------------
General           0                     0           0                                    96                  96

11.2.0.4          1       1951       1951        1951               1951               2048                3999
                  2       1952       1952        1952               1952               2049                  86

12.1.0.2          3       1951       1951        1951               1951               2048                3999
                  4       1952       1952        1952               1952               2049                  86

12.2.0.1          5       1937       1937        1937               1937               2034                3971
                  6       1938       1938        1938               1938               2035                3973

18.3.0.0          7       1984       1984        1984               1984               2081                  86
                  8       1985       1985        1985               1985               2082                  86

********                                                                 ------------------ -------------------
avg                                                                              1835.77778          1820.22222


AVG_ROW_LEN
-----------
       5410

COLUMN_NAME          AVG_COL_LEN
-------------------- -----------
N1                             3
VERSION                        9
V1                          1740
C1                          1837
NC1                         1822

The reminders:

  • A LOB that uses a multi-byte character set (and possibly, but I have not checked, an NCLOB that is using a single-byte character set) stores its content using a two-byte fixed width character set. So when I insert a character string of 1,951 (multibyte) characters the internal representation uses double the number of bytes as there are characters – so the data content will be 3,902 bytes.
  • The length() and dbms_lob.getlength() functions report the number of characters not the number of bytes. (There is a lengthb() function to report the number of bytes, but it doesn’t work with multibyte character set LOBs, raising error: “ORA-22998: CLOB or NCLOB in multibyte character set not supported”). Even though our NCLOB needs 3,902 bytes for its content it is reported – just like the CLOB – with a length of 1,951.
  • The sys_op_opnsize() internal function tells you about the number of bytes a column takes up in the row – though not always totally accurately, as we shall soon see – so it will tell you about the 3,902 bytes stored for the data plus the “structural” data that helps to describe the LOB. So for the row holding strings of length 1,951 we can see that the CLOB seems to take 1,951 + 97 = 2,048 bytes while the NCLOB seems to take 2 * 1951 + 97 = 3,999 bytes.
  • When the total storage for a LOB column exceeds 4,000 bytes the LOB is stored “out of line” even when it has been declared as mine have with “enable storage in row”. So when we insert strings of length 1,952 the CLOB column stays in line and reports a size of 1,952 + 97 = 2.049 bytes while the NCLOB exceeds the limit (2 + 1,952 + 97 = 4001) and goes out of row, reporting an “in-row” size of 86 bytes which is (we assume) the LOB metadata.

You might note, by the way, that the avg_col_len is the average (which I’ve reported for the CLOB and NCLOB columns) of the sys_op_opnsize() values rounded up plus 1 (for the “column count” byte); and – because the number of columns in the table is small – the avg_row_len is very similar to the sum of the avg_col_len.

You should also note that the storage size of an “empty” LOB is (or seems to be) 96 bytes in this version of Oracle. That’s quite a lot of space to say that there’s no data  – but using an empty_[n]clob() to represent “nothing” does mean that you don’t have to code for “is null or is empty” and it may help you to avoid incorrect results as a consequence. In fact a block dump shows that the actual space usage for the empty_[n]clob() is only 30 bytes – so it’s not quite as bad as it seems. The error is probably based around the older code using the maximum possible length of a lob locator as an assumption rather than checking the actual size in the table.

The warning

It’s a minor variation of the standard warning: “odd little things change when you upgrade and some of them may have a big side effect”. Here are the results from 18.3 (which, amongst other details, defaults to a multi-byte character set – which I’ll check before I show you the LOB results).

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET%';

PARAMETER                        VALUE
-------------------------------- --------------------
NLS_CHARACTERSET                 AL32UTF8
NLS_NCHAR_CHARACTERSET           AL16UTF16

This affects the storage of CLOBs if you’ve previously been using a default single-byte character set – you’re suddenly going to find your LOB segments are twice as big as they used to be – even though any report of “length()” will be unchanged. But there are other changes. Here’s the output from the same script running on 18.3.0.0:



VERSION          N1 LENGTH(V1) LENGTH(C1) LENGTH(NC1) SYS_OP_OPNSIZE(V1) SYS_OP_OPNSIZE(C1) SYS_OP_OPNSIZE(NC1)
-------- ---------- ---------- ---------- ----------- ------------------ ------------------ -------------------
General           0                     0           0                                    30                  30

11.2.0.4          1       1951       1951        1951               1951               3933                3933
                  2       1952       1952        1952               1952               3935                3935

12.1.0.2          3       1951       1951        1951               1951               3933                3933
                  4       1952       1952        1952               1952               3935                3935

12.2.0.1          5       1937       1937        1937               1937               3905                3905
                  6       1938       1938        1938               1938               3907                3907

18.3.0.0          7       1984       1984        1984               1984               3999                3999
                  8       1985       1985        1985               1985                132                 132
********                                                                 ------------------ -------------------
avg                                                                              3078.77778          3078.77778

AVG_ROW_LEN
-----------
       7912

COLUMN_NAME          AVG_COL_LEN
-------------------- -----------
N1                             3
VERSION                        9
V1                          1740
C1                          3080
NC1                         3080

Looking at the details for “General” and “18.3.0.0” what can we see that’s changed?

First, the length of an empty_[n]clob is now reported correctly at 30 bytes. This might (if the value is also used internally) explain why a LOB can now be 33 characters (66 bytes) longer before it’s moved out of line – it’s a small difference, but you might be at a boundary condition where it makes a big difference (for good, or for bad – who can tell) or your system.

Secondly, the LOB metadata for an out of line LOB seems to have jumped from 86 bytes to 132 bytes. Like the empty_[n]clob() issue, this is an error. The actual space usage in row was 38 bytes – consisting of the basic 30 bytes “empty” metadata, 4 extra bytes overhead, and a 4-byte block address linking to the stored LOB value. (For larger LOBs you will probably see that the “tail” of the in-row data grows to accomodate a short list of block addresses and chunk information).

Finally, you’re allowed to guess that the rows labelled 12.2.0.1 are there to give you a hint that in that version the NCLOB moves out of line at 1,938 characters – but you’ll probably want to run the test on 12.2 to confirm that claim. This does mean, though, that an upgrade to 12.2 from a lower version might be a bigger threat than any upgrade to 18.3. You’ll also find that in 12.2 the empty_[n]clob() is reported with a length of 124. (Again, this may explain the change in the break-point for going out of line: 124 – 96 = 28, and (1952 – 28/2) = 1,938. The arithmetic is consistent with the incorrect reporting of the size of the empty LOB metadata.

So the bottom line is this – if you do a lot of work with LOBs, do some careful checking of how much space you are using, how much space you will use on the next install, how changes in character sets can affect numbers, how the statistics might change – even when collected in exactly the same way – and what this might do to execution plans.

Footnote

This testing was all done using securefile LOBs – which has been the default for some time if you don’t specify the type of LOB you want to use. The numbers, particularly the break from in-row to out-of-row, are a little different if you switch to basicfile LOBs.

A quick test on LiveSQL (19.2) suggests that nothing has changed from 18.3 to 19.3

For further reading on quirky details of LOBs – here’s the article that has a footnote reporting another interesting change in LOB handling in 18.3; and a very old note about analysing the contents of a LOB segment with a view to assessing the impact of deleting old LOBs and potentially moving the LOB segment to a tablespace of a different size.

 

 

Partition loading in direct mode

Direct mode insert using the APPEND hint is a cool piece of technology that lets you load bulk data into a table very quickly and efficiently. Obviously there are a number of implications of doing so which you can read about here, but the one that catches most people out is the that you are locking the table during the load and once the load is completed, the table is “disabled” until the transaction ends. Here’s a quick example of that in action:


SQL> create table t
  2  as select * from dba_objects d
  3  where 1=0;

Table created.

SQL> insert /*+ APPEND */ into t
  2  select * from dba_objects d;

82417 rows created.

--
-- No further INSERTs are possible
--
SQL> insert /*+ APPEND */ into t
  2  select * from dba_objects d;
insert /*+ APPEND */ into t
                          *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


--
-- No further DML at all is possible
--
SQL> delete from t;
delete from t
            *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


--
-- Not even a SELECT statement is allowed
--
SQL> select count(*) from t;
select count(*) from t
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


--
-- I must end the transaction first with commit or rollback before normal service is resumed
--
SQL> commit;

Commit complete.

SQL> select count(*) from t;

  COUNT(*)
----------
     82417

This makes sense given that a direct mode insert is manipulating the high water mark (HWM) for a table, so it pretty much has to be an all or nothing process for the session issuing the load, because the HWM is in a state of flux until we commit or rollback.

However, what about a partitioned table? Can I do a direct mode insert into a single partition, whilst leaving the other partitions available in the same session? Let’s try it out.


SQL> create table t
  2  partition by list ( x )
  3  ( partition p1 values (1),
  4    partition p2 values (2)
  5  )
  6  as select 1 x, d.* from dba_objects d
  7  where 1=0;

Table created.

--
-- Only load into partition P1
--
SQL> insert /*+ APPEND */ into t partition (p1)
  2  select 1 x, d.* from dba_objects d;

82419 rows created.

--
-- And now see if partition P2 can be loaded as part of the same transaction
--
SQL> insert /*+ APPEND */ into t partition (p2)
  2  select 2 x, d.* from dba_objects d;
insert /*+ APPEND */ into t partition (p2)
                          *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

Unfortunately not. Even though we never touched partition P2 with the first INSERT, and partition P2 is a different physical segment, we still cannot do additional direct loads on it. One easy workaround to this is to place that second load in a separate transaction, for example:


SQL> declare
  2    pragma autonomous_transaction;
  3  begin
  4    insert /*+ APPEND */ into t partition (p2)
  5    select 2 x, d.* from dba_objects d;
  6    commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

You’re probably thinking “That’s a bit silly. If we’re going to commit for each partition, then why wouldn’t we just commit after the load of the first partition P1 anyway?”. That’s a valid point, but what the above example shows is that you can do direct path loads into separate partitions concurrently. This opens up opportunities for (dramatically) increasing the throughput of direct path loads if you can segment the source data into its designated target partitions at load time. I’ll extend the table above to have 10 partitions, and use a little DBMS_JOB code to now load 10 partitions all concurrently.


SQL> create table t
  2  partition by list ( x )
  3  ( partition p1 values (1),
  4    partition p2 values (2),
  5    partition p3 values (3),
  6    partition p4 values (4),
  7    partition p5 values (5),
  8    partition p6 values (6),
  9    partition p7 values (7),
 10    partition p8 values (8),
 11    partition p9 values (9),
 12    partition p10 values (10)
 13  )
 14  as select 1 x, d.* from dba_objects d
 15  where 1=0;

Table created.

SQL>
SQL> declare
  2    j int;
  3    l_sql varchar2(200) :=
  4      'begin
  5         insert into t partition (p@)
  6         select @ x, d.* from dba_objects d;
  7         commit;
  8       end;';
  9  begin
 10    for i in 1 .. 10 loop
 11      dbms_job.submit(j,replace(l_sql,'@',i));
 12    end loop;
 13    
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL> select job, what from user_jobs
  2  where what like '%dba_objects%';

       JOB WHAT
---------- --------------------------------------------------
       161 begin
           insert into t partition (p1)
           select 1 x, d.* from dba_objects d;
           commit;
           end;

       162 begin
           insert into t partition (p2)
           select 2 x, d.* from dba_objects d;
           commit;
           end;

       163 begin
           insert into t partition (p3)
           select 3 x, d.* from dba_objects d;
           commit;
           end;

       164 begin
           insert into t partition (p4)
           select 4 x, d.* from dba_objects d;
           commit;
           end;

       165 begin
           insert into t partition (p5)
           select 5 x, d.* from dba_objects d;
           commit;
           end;

       166 begin
           insert into t partition (p6)
           select 6 x, d.* from dba_objects d;
           commit;
           end;

       167 begin
           insert into t partition (p7)
           select 7 x, d.* from dba_objects d;
           commit;
           end;

       168 begin
           insert into t partition (p8)
           select 8 x, d.* from dba_objects d;
           commit;
           end;

       169 begin
           insert into t partition (p9)
           select 9 x, d.* from dba_objects d;
           commit;
           end;

       170 begin
           insert into t partition (p10)
           select 10 x, d.* from dba_objects d;
           commit;
           end;


10 rows selected.

SQL>
SQL> commit;

Commit complete.

SQL> select subobject_name, cnt
  2  from (
  3    select dbms_rowid.rowid_object(rowid) obj, count(*) cnt
  4    from   t
  5    group by dbms_rowid.rowid_object(rowid)
  6    ), user_objects
  7  where obj = data_object_id
  8  order by to_number(substr(subobject_name,2));

SUBOBJECT_        CNT
---------- ----------
P1              82427
P2              82427
P3              82427
P4              82427
P5              82427
P6              82427
P7              82427
P8              82427
P9              82427
P10             82427

And voila! 10 partitions all loaded in direct mode concurrently! There is also a standard means of loading data concurrently using parallel DML, but if I know the segmentation of the data in advance, the “manual” method above can sometimes be a quicker and easier to debug option.

I/O Benchmark Minor Update

I've recently published a new version 1.03 of the I/O benchmark scripts on #333333;">my #336699;">github repository#333333;"> (ideally pick the #336699;">IO_BENCHMARK.ZIP containing all the scripts#333333; font-family: "verdana" , "arial" , sans-serif;">). The original post including some more instructions can be found here, and there is also a video on my Youtube channel explaining how to use the benchmark scripts.
#333333; font-family: "verdana" , "arial" , sans-serif;">
#333333; font-family: "verdana" , "arial" , sans-serif;">The main change is a new version of the "Write IOPS" benchmark that should scale much better than the older version.
#333333; font-family: "verdana" , "arial" , sans-serif;">
#333333; font-family: "verdana" , "arial" , sans-serif;">There are now #333333;">actually#333333; font-family: "verdana" , "arial" , sans-serif;"> two variants of the "#333333;">max_write_iops_benchmark_slave.sql" script. The currently used one is based on a batch SQL update whereas the "max_write_iops_benchmark_slave_forall.sql" script uses a PL/SQL FORALL update approach to achieve the same. In my tests the two performed quite similarly, but I've decided to include both so you can test which one works better for you - just rename the scripts accordingly.

#333333;">
#333333;">In order to max out "Write IOPS" I suggest you create objects that are smaller than the corresponding buffer cache so can be cached entirely and set FAST_START_MTTR_TARGET to 1 to maximize the write pressure on the DBWR process(es). The Online Redo Logs should be sized adequately in order to avoid bottlenecks in that area. The script is designed to minimize redo generation and maximize the number of blocks modified that have then to be written by DBWR.

#333333;">
#333333;">You could still run the script in a mixed read/write IOPS mode if you create objects larger than the buffer cache - in which case there can be additional pressure on the DBWR if there are no free buffers to read in new blocks ("free buffer waits"). I've also already used successfully both the "Read IOPS" and "Write IOPS" benchmark scripts simultaneously to maximize both, read and write IOPS.

#333333;">
#333333;">There is still the problem at least in 12.2 (and I think it's still there in 18c but not entirely sure off the top of my head) that the PDB level AWR reports don't cover properly the DBWR "Write I/O" related figures, so although the script specifically detects that it runs on 12.2+ and on PDB level and creates AWR PDB reports accordingly you won't get any useful "Write IOPS" results and would have to either run the benchmark on CDB level or create CDB level AWR snapshots accordingly.

#333333;">
#333333;">The interactive scripts now also echo the command finally called to execute the actual benchmark script, which can be helpful if you don't want to go through the interactive script again and again for repeated executions. Of course you still would need to take care of dropping / creating / keeping a suitable schema where to execute the benchmark, and maybe also modify the scripts that they don't keep creating and dropping the objects if you want have multiple runs with same object size / concurrency settings.
#333333;">
#333333;">I'm thinking about a version 2.0 of the I/O benchmark scripts that should be improved in various aspects - the "interactive" script should become much more user friendly with improved defaults and abbreviations that can be entered, and more options like keeping the schema / objects. Also the measurement of the IOPS / throughput should be improved by monitoring the figures continuously which should provide a much better picture of the performance over time (varying IOPS rates for example). The core I/O benchmark scripts seem to be working pretty well (now that the write IOPS is improved) so I don't see much need for improvement there. Maybe an option to execute the benchmark in a kind of loop with increasing object sizes / concurrency level might also be useful.

Writing Linux Scripts- Part I

Many see scripting as a science, but if you want to write not just functional scripts, but efficient and easy to work with scripts, it is also an art.

Most SQL DBAs are feeling the pressure to learn BASH as they enter Azure and I strongly recommend it. I’m learning PowerShell as part of my education coming from a Linux background to Azure. It’s all about “the more you know”….you know?

So let’s start with learning it right.

Scripts = Stories

A good script has the following parts to it:

  • An Introduction
  • A Body
  • A Conclusion

We’re going to focus on this as part of our education on Linux scripting before we get into a load of terminology or scripting language.

The Introduction

Just as we would have an introduction to a story, we need to set up our reader for what we plan on performing as part of our script:

  • What scripting language do we plan on using
    • If shell, we’ll set our shell, (#/bin/bash)
    • If we’re working with Python, well then we’ll import our pre-built functions
  • We’ll set up our arguments, our variables and other values that are pertinent to the script processing what is needed dynamically or statically
  • Designate any error handling and requirements for the script to execute.

The Body

This section will contain 90% of the “functional” code. This is the meat of the script- processing, manipulating and building out what must be performed or performing it. The reason that I say perform or what will be performed is that functions are a very powerful and useful part of Linux scripting, allow you to more efficiently build and test your scripts, (as anyone who works with them would verify…) and is a big part of advanced Linux scripting.

The Conclusion

In the conclusion, you will wrap up, clean up, log out or if using functions, execute all the code in your script that you’ve spent so much valuable time on building.

Now that we know what is expected in our scripting quality, we’ll start to break down each piece in subsequent posts.

Keep your chin up!



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBAKevlar [Writing Linux Scripts- Part I], All Right Reserved. 2019.

Raw partitions?

Here’s a quirky one for you. It can happen when you are dealing with a partitioned table where the partition key is defined as RAW. To be honest, I really can’t think of a reason why you ever want a table with a raw partition key. (If you have one, please let me know in the comments). Anyway, here’s the demo. I’ll start with a table using the cool automatic list partition facility and load a single row into it.


C:\>sql18

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 29 16:38:19 2019
Version 18.6.0.0.0

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

Last Successful login time: Mon Apr 29 2019 16:37:30 +08:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0


SQL> create table test
  2  (
  3     id         raw(16),
  4     groupid    raw(16),
  5     chardata   varchar2(20),
  6     numbdata   number,
  7     datedata   date
  8  )
  9    partition by list (groupid) automatic (partition P_ values ('FF'))
 10  ;

Table created.

SQL> alter table test
  2  add constraint test_pk
  3  primary key (id)
  4  using index;

Table altered.

SQL> insert into test
  2  values (sys_guid(), sys_guid(), 'some char data', 123, sysdate - 3);

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0

No dramas there. Now I’ll take a DataPump export of that table using the defaults.


C:\>expdp mcdonac/*****@db18_pdb1 directory=TEMP dumpfile=test_exp.dmp tables=test

Export: Release 18.0.0.0.0 - Production on Mon Apr 29 16:38:19 2019
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "MCDONAC"."SYS_EXPORT_TABLE_01":  mcdonac/********@db18_pdb1 directory=TEMP dumpfile=test_exp.dmp tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "MCDONAC"."TEST":"P_"                           0 KB       0 rows
. . exported "MCDONAC"."TEST":"SYS_P7389"                6.804 KB       1 rows
Master table "MCDONAC"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MCDONAC.SYS_EXPORT_TABLE_01 is:
  C:\TEMP\TEST_EXP.DMP
Job "MCDONAC"."SYS_EXPORT_TABLE_01" successfully completed at Mon Apr 29 16:38:40 2019 elapsed 0 00:00:16

Everything is still going OK. If I took a DataPump export, it’s a reasonable assumption that I will be wanting to load that into a new database somewhere. To mimic that, I’ll simply drop the table I just created so that I can import the table back into the same schema anew.


C:\>sql18

SQL*Plus: Release 18.0.0.0.0 - Production on Mon Apr 29 16:38:42 2019
Version 18.6.0.0.0

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

Last Successful login time: Mon Apr 29 2019 16:38:19 +08:00

Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0



SQL> drop table test purge;

Table dropped.

SQL> exit
Disconnected from Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.6.0.0.0

And now we’ll run a simple DataPump import to put my table back in place.



C:\>impdp mcdonac/******@db18_pdb1 directory=TEMP dumpfile=test_exp.dmp tables=test

Import: Release 18.0.0.0.0 - Production on Mon Apr 29 16:38:43 2019
Version 18.6.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Master table "MCDONAC"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "MCDONAC"."SYS_IMPORT_TABLE_01":  mcdonac/********@db18_pdb1 directory=TEMP dumpfile=test_exp.dmp tables=test
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"MCDONAC"."TEST" failed to create with error:
ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL

Failing sql is:
CREATE TABLE "MCDONAC"."TEST" ("ID" RAW(16), "GROUPID" RAW(16), "CHARDATA" VARCHAR2(20 BYTE) COLLATE "USING_NLS_COMP", "NUMBDATA" NUMB
ER, "DATEDATA" DATE)  DEFAULT COLLATION "USING_NLS_COMP" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  STORAGE( BUFFER_POOL DEFAULT F
LASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  PARTITION BY LIST ("GROUPID") AUTOMATIC  (PARTITION "P_"  VALUES ('FF
') SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING  STORAGE( BUFFER_POOL DEFAULT FLASH_CAC
HE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"  READ WRITE , PARTITION "SYS_P7389"  VALUES (HEXTORAW('38E50ADF7A7840149B16767
9433196C5')) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOCOMPRESS LOGGING  STORAGE(INITIAL 8388608 NEX
T 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_
FLASH_CACHE DEFAULT) TABLESPACE "USERS"  READ WRITE )

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"MCDONAC"."TEST_PK" skipped, base object type TABLE:"MCDONAC"."TEST" creation failed

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "MCDONAC"."SYS_IMPORT_TABLE_01" completed with 2 error(s) at Mon Apr 29 16:38:46 2019 elapsed 0 00:00:03

Uh oh… You can see from the highlighted part of the import log, that DataPump sees the RAW datatype definition and hence tries to map the partition keys to the the same datatype using HEXTORAW. It’s an interesting contradiction that you are allowed to partition a table by raw, but if you try to nominate a raw datatype in the actual partition definition values, then the database gets upset with you. Here’s the same DDL in simpler form showing the same error.



SQL> create table test
  2  (
  3     id         raw(16),
  4     groupid    raw(16),
  5     chardata   varchar2(20),
  6     numbdata   number,
  7     datedata   date
  8  )  partition by list (groupid) (partition P_ values (hextoraw('FF')));
)  partition by list (groupid) (partition P_ values (hextoraw('FF')))
                                                                   *
ERROR at line 8:
ORA-14308: partition bound element must be one of: string, datetime or interval literal, number, or NULL

To be honest, I’m not losing any sleep over this, because I can’t think of a reason to partition by raw. But if you do have a valid case for it, then you’ll need to pre-create the table (without the HEXTORAW expressions) and then use DataPump to load the data back into the table.

PostgreSQL and Jupyter notebook

Here is a little test of Jupyter Notebook to access a PostgreSQL database with very simple installation, thanks to Anaconda. I did it on Windows 10 but the same simplicity is on Linux and Mac.

Anaconda

I’ll use Anaconda to install the required components

Conda is an open source package management system and environment management system that runs on Windows, macOS and Linux. Conda quickly installs, runs and updates packages and their dependencies.

https://www.anaconda.com/distribution/#download-section

Installation

The install is very easy — just go to https://www.anaconda.com/ and download for your environment. Then, manage everything from the Anaconda Navigator. I choose the Python3 64-bit version for Windows.

I used all defaults, the installation directory being in the %USERPROFILE% home directory (like C:\Users\Franck\Anaconda3).

This installs a few shortcuts in the Start Menu, such as the Anaconda Prompt (the command line where you can run conda, with all environment set) in CMD and PowerShell version, or the Jupyter notebook.

You can run everything from the Anaconda Navigator, for example, get the command line with all the environment set:

Or you can simply run the Anaconda Prompt in the Start Menu which is a shortcut for:

%windir%\System32\cmd.exe "/K" C:\Users\Franck\Anaconda3\Scripts\activate.bat C:\Users\Franck\Anaconda3

Now, from this prompt, I’ll install a few additional packages

IPython SQL

ipython-sql introduces a %sql (or %%sql) magic to your notebook allowing you to connect to a database, using SQLAlchemy connect strings, then issue SQL commands within IPython or IPython Notebook.

catherinedevlin/ipython-sql

Here is how to install it from the Anaconda Prompt:

conda install -y -c conda-forge ipython-sql

PostgreSQL

I’ll run the postgres server directly in this environment:

conda install -y -c conda-forge postgresql

The link between ipython-sql and the postgresql API is done by psycopg2:

Psycopg is the most popular PostgreSQL adapter for the Python programming language. Its main features are the complete implementation of the Python DB API 2.0 specification and the thread safety.

conda install -y -c anaconda psycopg2

I also install Pgspecial to run the ‘backslash’ commands like in psql

conda install -y -c conda-forge pgspecial

Create a database

From the Anaconda Prompt, I create my PostgreSQL database in C:\Anaconda\pgdata:

set PGDATA=C:\Anaconda\pgdata
mkdir %PGDATA%
pg_ctl initdb
pg_ctl start
psql -c "create database DEMO;" postgres

I’m now ready to run some SQL from the Jupyter notebook and do not need the Anaconda Prompt anymore (I can run shell commands from a notebook).

Jupyter Notebook

I can start Jupyter from the Anaconda Navigator, but it defaults to the %USERPROFILE% directory.

I prefer to change the Jupyter Notebook shortcut (right-click-more-open file location-properties) to replace USERPROFILE with my C:\Anaconda directory which will be where I’ll create notebooks:

C:\Users\Franck\Anaconda3\python.exe C:\Users\Franck\Anaconda3\cwp.py C:\Users\Franck\Anaconda3 C:\Users\Franck\Anaconda3\python.exe C:\Users\Franck\Anaconda3\Scripts\jupyter-notebook-script.py "C:\Anaconda/"

Or simply run it from the Anaconda Prompt:

jupyter.exe notebook --notebook-dir=C:\Anaconda

This runs Jupyter and opens it in my browser. I create a new notebook with New-Python3:

I load the iPython SQL extension:

%load_ext sql

connect to the DEMO database

%sql postgresql://localhost/demo

and I can run some SQL statements, like:

%sql select version()

But I’ll not put more commands in this blog post, because that’s the main advantage of a Jupyter Notebook: show the commands, the output, and some comments.

GitHub Gist and Medium

I’ve uploaded my notebook on GitHub Gist for easy sharing: Blog20190428-postgresql-and-jupyter-notebook-on-windows.ipynb

GitHub display it correctly, and you can download it to test on your environment. And Medium seems to embed it in a very way: