What is the Cost of an Exception
This Post Should Have been made in March. It’s old…
In 2 weeks we have Dynatrace on-site to do some product training. I’ve been fortunate enough to have been introduced over the past year to the training approaches that the Dynatrace consulting team takes with their customers. So I kind of know what’s going to happen when Asad comes back. He’s definitely going to grasp at low-hanging fruit. The biggest one that I know he’s going to push hard are unnecessary Java Exceptions.
Below is the PurePath details of a teacher Grade Center request. The path is “/webapps/gradebook/do/instructor/getJSONData” which was called 2x by the same thread for the same request. I have a suspicion that it has to do with the Page Processing framework. Either way if you dig into the request, you see littered throughout the calls are a series of Exceptions.
The first chart shows the details of (3) Constraint Violations. Looking into the exception, you see the chart below which is calling out that certain columns from the COURSE_USERS (intro, note and pinfo) are NULL. That could be a result of our data model. It could also be something all customers receive because these are table values that might seem irrelevant. I know for the purpose of this query, this data is completely unnecessary. What’s even more frustrating is that we are calling a NULL value exception, but the table does not restrict NULLs on those 3 columns.
So it’s a little more complicated than quantifying the cost. It comes back to poor programming decisions or poor DDL design. I would argue it’s the first. We really only want the PK1 and possibly a couple other foreign keys from COURSE_USERS for what we are doing. We certainly do not need to unmarshall a NCLOB, let alone 3 of them for no purpose. Imagine if we actually were populating those fields and their impact on memory.
SELECT pk1, dtmodified, users_pk1, crsmain_pk1, data_src_pk1, role, link_name_1, link_url_1, link_desc_1, link_name_2, link_url_2, link_desc_2, link_name_3, link_url_3, link_desc_3, photo_link, intro, note, pinfo, cartridge_ind, available_ind, receive_email_ind, roster_ind, row_status, enrollment_date, last_access_date, child_crsmain_pk1 FROM course_users WHERE ( crsmain_pk1 = ? ) AND ( users_pk1 = ? ) AND ( row_status = ? )
\\ Below is the Table
SQL> desc course_users;
Name Null? Type
----------------------------------------- -------- ----------------------------
PK1 NOT NULL NUMBER(38)
LINK_NAME_3 NVARCHAR2(100)
CRSMAIN_PK1 NOT NULL NUMBER(38)
DATA_SRC_PK1 NUMBER(38)
ROLE NVARCHAR2(50)
LINK_NAME_1 NVARCHAR2(100)
LINK_URL_1 VARCHAR2(100)
LINK_DESC_1 NVARCHAR2(255)
LINK_NAME_2 NVARCHAR2(100)
LINK_URL_2 VARCHAR2(100)
LINK_DESC_2 NVARCHAR2(255)
USERS_PK1 NOT NULL NUMBER(38)
LINK_URL_3 VARCHAR2(100)
LINK_DESC_3 NVARCHAR2(255)
PHOTO_LINK NVARCHAR2(500)
INTRO NCLOB
NOTE NCLOB
PINFO NCLOB
CARTRIDGE_IND NOT NULL CHAR(1)
AVAILABLE_IND NOT NULL CHAR(1)
RECEIVE_EMAIL_IND NOT NULL CHAR(1)
ROSTER_IND NOT NULL CHAR(1)
SOS_ID_PK2 NOT NULL NUMBER(38)
ROW_STATUS NUMBER(1)
ENROLLMENT_DATE DATE
LAST_ACCESS_DATE DATE
CRSMAIN_SOS_ID_PK2 NUMBER(38)
USERS_SOS_ID_PK2 NUMBER(38)
DTMODIFIED DATE
CHILD_CRSMAIN_PK1 NUMBER(38)
SQL>
What’s My Point About Dynatrace
So you are probably asking what’s my point about Dynatrace…Well I know for certain they are going to focus on exceptions. They are right to do so. Dirty code is bad code, nobody can argue that. Yes, we should really fix these problems. This could have been a filtered load. Second, we could not have issued an exception for a NULL value on columns that do not restrict NULLs. Third, we could make the columns NOT NULL, but I wouldn’t do it.
The key point is that at somepoint, we are going to need to measure the true cost…
[...] two exceptions were responsible for 550k exceptions as well. We don’t quite know what the cost of an exception is to date. Dynatrace is still working that problem. I did some informal correlations. I’m [...]
[...] start by talking about Exceptions and Log Messages. I’ve written two blogs, one about the cost of an exception and the second about using Dynatrace to evaluate exceptions and logs. Both of which discuss the [...]