APEX Survey Results: Instrumentation Thoughts

This question in my 2015 survey is related to question 7 regarding instrumentation.

Q8: Do you have any thoughts you'd like to share on instrumentation?

I left this open for people to add a free-text response. Here are some worth highlighting, though it was hard to choose since so many people had something to add.

a) How would it help me?

Have a read of this post to get an idea of how the debug output can help solve problems.

b) It's not optional

I agree, it should be baked into your code.

c) An application is not just building, but also managing after deployment. To reduce the cost of the second fase (sic) instrumentation is a must

Another perspective on what instrumentation is all about.

d) Nobody instruments enough ....

Well, I think you could probably have too many logs.

e) Not enough planning goes into instrumentation in general.

Planning is an interesting point. While we could plaster our code with debug logs, this isn't necessarily going to help debugging your code, particularly when you need to further sort the wheat from the chaff.

Once you've decided on a framework, start thinking about standard patterns such as noting start/end of procedures; incoming/outgoing parameters; the varied level of detail you may need; how you can turn it on/off; how errors are highlighted and treated.

Any other factors you think are worth mentioning in regard to planning?

f) Please make Debugging more smoother and documented. An idea can be to create a packaged application to showcase debugging.

Something for the APEX development team to consider.

g) Instrumentation should point out the exact place where error is occured (sic). It should not be overhead for the APEX application and there should be a switch to turn it on/off.

Back in APEX 3.x, debugging was displayed where relevant, within the rendered page itself. It looked awkward and was hard to decipher. Any code is overhead, but the risk/benefit equation outweighs the cost. APEX debugging can be turned on/off, and some logging libraries provide options for granular logging.

h) ... Also debugging apex_collections is somehow hard today ...

Constructs relating specifically to a particular session (such as collections) can be tricky to detail, but with careful planning you should be OK. It may be useful to output a collection count at key locations. You could have a little library that looped and logged key columns from the collection. And the Session link in the Developer Toolbar allows you to see collection contents naturally.

i) I wish I'd known about or used some of the instrumentation methods above long ago when I built my first Apex apps.

I think that comment right there should be a warning to all those who haven't started yet.

j) The CREATE ANY CONTEXT privilege requirement in Logger is a pain.

Sounds worth airing, but I'm pretty sure the benefits outweigh whatever pain that might be.

k) asdfasdfasfd

It seems even cats have opinions on the topic.

l) do it! use whatever tool fits your needs, but use something!

I'll finish with that one.

Does anyone recognise their comment? I didn't want to name anyone without asking.

Connect2016 - Australian Conference Series

It's less than a month away from the Australian conference series and I'm probably about 2 thirds ready for my sessions.

Australia? Yup, the Perth gig will be on 7-8 Nov and the fun continues in Melbourne on 10-11 Nov 2016.

Check out www.ausoug.org.au/connect2016, then think about that junket. Jokes aside, events like these are the best place to chat to people about Oracle technology, with plenty of sessions to allow you to stay sane while considering what challenges others are facing. I know times are hard but the price is reasonable, and there is more to just the program.

Sessions I've got my eye on include:

"Evolving APEX Development Practices" - Mark Lancaster, title alone grabs me.

"User Experience (UX): Building Usable Applications – Why and How" - Basheer Khan, as an APEX developer I'm finding UX very interesting, when I can indulge.

"APEX, RESTful Services and STRIPE" - Lino Schildenfeld, I have plenty to learn about web services.
Lino is also doing "APEX Plugins - World of possibilities", and I'm keen to hear his perspective on this.

"Transition to Cloud - Should we or shouldn't we?"" - Howard Ong, I have a feeling I should listen to this.

"Next Generation Databases" - Guy Harrison, I know this one will be infotainment goodness.

And Connor McDonald will treat us with some 12.2 goodness, and my director has one on BI Visualisations, lessons direct from a recent project.

I have the following sessions:
"Mastering Dynamic Actions" - ready!
"APEX 5.1 Charts with OracleJET" - researched... mostly.

In Perth I'll be joining other Sage colleagues called "Our Kitchen Rules" and I'll have 10 minutes to cover a topic of my choosing - ready!

I have another couple of ideas ready for next year, too.

I'll be on both legs so it will be particularly awesome to see everyone on the east coast.

OTN Appreciation Day : APEX Dynamic Actions

I'm going to take advantage of the fact I live in a city so remote to many others in this amazing community, and schedule this post for my local 8am time. It's seems my schedule didn't work. Not the first fail from blogger... This might get me as one of the first posts in what's hopefully an interesting day amongst Oracle bloggers.

Tim Hall, a great producer of resources for Oracle technologists, suggested bloggers new and old post about their favourite feature, in appreciation for the professional network that is OTN.
https://oracle-base.com/blog/2016/09/28/otn-appreciation-day

I nominated APEX Dynamic Actions for sake of brevity in the subject line, and while they are pretty awesome as a whole, it does encompasses a range of sub-component features.

So I want to be more specific than that, I nominate the jQuery Selector attribute.

One of my favourite features

I could have selected one of a bunch of SQL functions (listagg, nvl2) or some PL/SQL features (%TYPE), or maybe something rarer like Oracle Text, but this one is a little more important to me.

For me it's a bridge between the Oracle world and the native web page we are generating.

With this attribute specifically, we can nominate, with precision, a component on the page and attach some event handler. We can make the web page communicate with the database.

Understanding how this feature operates I found like a gateway to building more interactive applications.

I looked back and it started with a post like this in 2012:
http://www.grassroots-oracle.com/2012/09/using-jquery-selector-in-apex.html
And took me so far as to write a book combining jQuery with APEX:
http://goo.gl/ZzQ0RP
(apparently chapter 9 has finally been corrected, more detail later)

Learning about this feature and further steering my career in an interesting direction required a community that blogged, participated in forums, tweeted, slacked, attended user group events, presented, networked, authored, and so on.

Giving also helps you learn. I do a little bit every now and then on the forums, and now I'm deemed a "legend". I read, watch interactions from interesting questions, and occasionally contribute. It helps in both directions, it's awesome, and it adds up. Same goes for presenting. A topic choice for next year is helping me learn what I need to keep my skills up to date.

#ThanksOTN

Synchronise Sequence value with 12c Identity Column

My journey into 12c continues with the use of identity columns, this time regarding data that had been imported from another database, but sequences haven't been updated.

Ensuring the next number returned from a sequence matches the current value from the table appears to be a common problem, my thoughts are described here. The biggest trouble is linking up the sequence to the column so we could automate the process.

TL;DR

We can now execute an ALTER statement to reset/align the sequence to a value appropriate to the column
alter table my_table modify (id generated as identity START WITH LIMIT VALUE);

The next insert will be problem free, and we don't need to do anything else.

The Underlying Sequence

Identity columns use a sequence under the hood, as hinted in the statement diagrams for the create table syntax.

I love these diagrams

I thought perhaps I could use my old technique on these sequences, but I forgot where to look for the name of the sequence associated with the identity column.

As usual Tim Hall pointed me in the right direction

select table_name, column_name, generation_type, sequence_name
from all_tab_identity_cols
where table_name = 'MY_TABLE'

TABLE_NAME    COLUMN_NAME GENERATION_TYPE SEQUENCE_NAME
------------- ----------- --------------- -------------
MY_TABLE      ID          BY DEFAULT      ISEQ$$_430382

I was reminded shortly after that you can also see the data default referenced within SQL Developer

SQL Developer Table properties

However it turns out if you try to apply alter sequence to those generated by the system from the table DDL, you get the following error.
ORA-32793: cannot alter a system-generated sequence
Fancy that.

So to increment the sequence beyond the most recent ID, I could make a bunch of requests to the next value of the sequence.

declare
  l_val pls_integer;
begin
  for i in 1..240 -- use the difference between .nextval and max(id)
  loop
     l_val := ISEQ$$_430382.nextval; -- change to sequence returned from all_tab_identity_cols
 end loop;
end;
/

This might be considered un-elegant, a dirty way to fix the problem. We're on 12c, surely there's a better way.

12c solution

The sequence creation is built into the DDL, so why not maintenance? Check out the help for the ALTER TABLE command.

START WITH LIMIT VALUE, which is specific to identity_options, can only be used with ALTER TABLE MODIFY. If you specify START WITH LIMIT VALUE, then Oracle Database locks the table and finds the maximum identity column value in the table (for increasing sequences) or the minimum identity column value (for decreasing sequences) and assigns the value as the sequence generator's high water mark. The next value returned by the sequence generator will be the high water mark + INCREMENT BY integer for increasing sequences, or the high water mark - INCREMENT BY integer for decreasing sequences.

Now illustrate this in action.

drop table seq_reset_test;
create table seq_reset_test -- basic table with my suggested identity column options
  (id number generated by default on null as identity
  ,CONSTRAINT seq_reset_test_pk PRIMARY KEY (id)
  ,label varchar2(20)
);

Table SEQ_RESET_TEST created.

-- Will be given first ID of 1
insert into seq_reset_test (label) values ('Initial');

1 row inserted.

-- Simulate update of db without synchronising sequence
update seq_reset_test set id = 1000 where id = 1;

1 row updated.

-- ID too high for sequence
select * from seq_reset_test;

        ID LABEL              
---------- --------------------
      1000 Initial             

-- Find sequence name from all_tab_identity_cols
select ISEQ$$_404558.currval from dual;

   CURRVAL
----------
         1

-- This will use ID 2, risking PK violation
insert into seq_reset_test (label) values ('Second');

1 row inserted.

-- Magic alter statement
alter table seq_reset_test modify (id generated by default on null as identity start with limit value);

Table SEQ_RESET_TEST altered.

-- This will use the updated sequence, avoiding max(id) value
insert into seq_reset_test (label) values ('Third');

1 row inserted.

-- Proof
select * from seq_reset_test;

        ID LABEL              
---------- --------------------
      1000 Initial             
         2 Second              
      1001 Third               

select ISEQ$$_404558.currval from dual;

   CURRVAL
----------
      1001

Conclusion

So it seems we can throw away that old reset_seq.sql file?
Particularly if the replacement is now an ALTER TABLE command that doesn't need to know about any values.

alter table my_table modify (id generated /*by default on null*/ as identity START WITH LIMIT VALUE);

In comments the optional definition settings I find most useful, which would have been defined within the table DDL.

If Oracle can manage by IDs with sequences and do all the grunt work for me, go right ahead.

Related Posts

12 Column Upgrades
Decommissioning Triggers in 12c

Decommissioning triggers in 12c

I've been operating with a 12c environment this year and I can see some standard patterns of mine changing.

One is the use of triggers, or lack thereof. I live in a city with a certain evangelist who does not like triggers, so I was happy to see Sven Weller's "perfect trigger" post. The answer is: there is no trigger.

Well, it's one thing to say 'create all new tables like this', but what about our existing structure? What process should we follow to decommission these triggers?

If you take the weekly DB Design quiz at the PL/SQL Challenge website, you may recognise this example as a quiz from Sept 2016.

Existing Framework

Consider a table with a structure similar to the one I described in a post of mine from 2010

drop table orbiters;
drop sequence orbiter_seq;

create sequence orbiter_seq;

create table orbiters
 (id  number not null
 ,CONSTRAINT orbiter_pk PRIMARY KEY (id)
 ,position    number not null
 ,planet varchar2(15) not null
 ,name   varchar2(30) not null
 ,distance  number not null
 ,created_date date not null
 ,created_by varchar2(50) not null
 );

create or replace TRIGGER orbiters_biur BEFORE INSERT OR UPDATE ON orbiters FOR EACH ROW DECLARE
BEGIN
  IF INSERTING THEN
    :NEW.id            := COALESCE(:NEW.id           , orbiter_seq.NEXTVAL);
    :NEW.created_date  := COALESCE(:NEW.created_date , SYSDATE);
    :NEW.created_by    := COALESCE(:NEW.created_by   , apex_application.g_user, USER);
  END IF;
END;
/

insert into orbiters (position,planet,name,distance) values (3,'Earth', 'Luna', 384) ;
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'SpaceX', 400/1000);
insert into orbiters (position,planet,name,distance) values (3,'Earth', 'ISS', 400/1000);

select id, name from orbiters;

ID  NAME
1   Luna
2   SpaceX
3   ISS

It's not rocket surgery...

Audit columns

There are plenty of options about in regard to auditing your columns and tracking history, but for the pattern you're likely to see regularly, there is no need for a trigger.
alter table orbiters modify created_date default sysdate ;

alter table orbiters modify  created_by default coalesce(
          sys_context('APEX$SESSION','app_user')
         ,regexp_substr(sys_context('userenv','client_identifier'),'^[^:]*')
         ,sys_context('userenv','session_user')
         );

Sven's coalesce to resolve the username when the environment may or may not be APEX is neat.

Default using sequence

The sequence itself has no performance benefit using an assignment vs selecting from dual. Instead, it needs to either be in the insert statement, or as a default value as 12c (finally) allows.

alter table orbiters modify id default orbiter_seq.NEXTVAL;
drop trigger orbiters_biur;

If you get ORA-02262, check your sequence exists and you've typed it properly.

Identity columns

Identity columns are ideal for new tables, but existing tables would require more work.

Identity columns actually use a sequence under the hood, so while I thought I was on the right track by using this syntax:
alter table orbiters modify id GENERATED BY DEFAULT ON NULL AS IDENTITY start with 10;
It returns the following error.
ORA-30673: column to be modified is not an identity column
A few bloggers mention this fact, someone posted the question on Reddit of all places.

But I think the value equation of creating a new column and shifting data, foreign key references etc is outweighed by the fact that performance is better regardless of using identity vs sequence. Tim demonstrates this here. I discuss resetting sequences in identity columns here.

Definitely remove the trigger

Here is the best part:
drop trigger orbiters_biur;

Conclusion

To decommission triggers and replace them with new 12c features, use these steps:

alter table orbiters modify id default orbiter_seq.NEXTVAL;
alter table orbiters modify created_date default sysdate;
alter table orbiters modify created_by default sys_context('APEX$SESSION','app_user');
drop trigger orbiters_biur;

Happy #db12c!

Related Posts

12 Column Upgrades
Synchronise Identity Column