Feed aggregator

Port Forwarding Using SSH Config File

Michael Dinh - Sun, 2021-04-11 15:03

Here is a good reference SSH config file for OpenSSH client

From a secured server, I am able to connect to 2 different environments which seems counter intuitive but I digress.

Since there are 2 different environments, the same ~/.ssh/config cannot be used as there may be IP overlap.

One environment will use ~/.ssh/config and ssh emhost

Other environment will use ~/.ssh/cbconfig and ssh -F ~/.ssh/cbconfig emhost

The default EM port for both hosts is 7803.

Using firefox https://localhost:7803/em to access EM does not work well when saving username and password as they will be overwritten.

One solution to save sysman’s password is to use URL with different port.

Hence, config will have EM port 7803 forward to 7803 while cbconfig will have EM port 7803 forward to 7804.

========================================
This is on cloud and looks complicated. 
========================================
I did not create the configuration and don't know how many hours it took.
~/.ssh/config

Host emhost
     HostName 10.157.38.66
     LocalForward 7001 10.157.38.66:7001
     LocalForward 7102 10.157.38.66:7102
     LocalForward 7803 10.157.38.66:7803
     LocalForward 9803 10.157.38.66:9803
     LocalForward 9851 10.157.38.66:9851

# DEFAULTS:
Host *
User dinh


========================================
This is on premise and looks simpler. 
========================================
ssh -F ~/.ssh/cbconfig emhost

Host emhost
     HostName 10.10.72.254
     # Forward port need to use IP address.
     # Equivalent to ssh -L 7804:10.10.72.254:7803 mdinh@10.10.72.254
     LocalForward 7804 10.131.28.227:7803

# DEFAULTS:
Host *
User mdinh

Python with Oracle using sqlalchemy and cx_oracle

Hemant K Chitale - Sun, 2021-04-11 09:46

 Here is a simple demo of using Python and the sqlalchemy and cx_oracle libraries


This is the code :

#import required libraries
import pandas as pd
import cx_Oracle
import sqlalchemy
from sqlalchemy.exc import SQLAlchemyError

#setup connection
try:
oracle_pdb = sqlalchemy.create_engine("oracle+cx_oracle://hemant:hemant@localhost/?service_name=orclpdb1", arraysize=100)
except SQLAlchemyError as e:
print(e)

#setup query and pandas dataframe for results
try:
employees_query = """SELECT * FROM hr.employees order by employee_id""";
df_employees = pd.read_sql(employees_query, oracle_pdb)
except SQLAlchemyError as e:
print(e)

#Info on the dataframe
print(df_employees.info())
#the first five rows
print(df_employees.head())

#create a new dataframe with a subset of columns
df_emp_selected_cols=df_employees
df_emp_selected_cols.drop(['email','phone_number','salary','commission_pct','manager_id','department_id'],axis=1, inplace=True)
print(df_emp_selected_cols.head())


And here is the output from my database :

Info on the dataframe

RangeIndex: 108 entries, 0 to 107
Data columns (total 11 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 employee_id 108 non-null int64
1 first_name 108 non-null object
2 last_name 108 non-null object
3 email 108 non-null object
4 phone_number 107 non-null object
5 hire_date 108 non-null datetime64[ns]
6 job_id 108 non-null object
7 salary 107 non-null float64
8 commission_pct 35 non-null float64
9 manager_id 106 non-null float64
10 department_id 107 non-null float64
dtypes: datetime64[ns](1), float64(4), int64(1), object(5)
memory usage: 9.4+ KB
None
The first 5 rows
employee_id first_name last_name ... commission_pct manager_id department_id
0 100 Steven King ... NaN NaN 90.0
1 101 Neena Kochhar ... NaN 100.0 90.0
2 102 Lex De Haan ... NaN 100.0 90.0
3 103 Alexander Hunold ... NaN 102.0 60.0
4 104 Bruce Ernst ... NaN 103.0 60.0

[5 rows x 11 columns]
With selected columns only
employee_id first_name last_name hire_date job_id
0 100 Steven King 2003-06-17 AD_PRES
1 101 Neena Kochhar 2005-09-21 AD_VP
2 102 Lex De Haan 2001-01-13 AD_VP
3 103 Alexander Hunold 2006-01-03 IT_PROG
4 104 Bruce Ernst 2007-05-21 IT_PROG


Once you are familiar with this method, you can use numpy, matplotlib and a host of other python libraries with the dataset.

This article by Yuli Vasiliev is a good starter.


Categories: DBA Blogs

Murder in the Age of Enlightenment

Greg Pavlik - Fri, 2021-04-09 17:51

I had a few days of downtime to deal with some medical issues and turned to some short story collections to fill the time. My companions for a bit were Ryūnosuke Akutagawa and Anton Chekhov. I was quite delighted with a new translation of Akutagawa from Pushkin Press, Murder in the Age of Enlightenment. What sparse but sharp imagery - taken from Japanese history, European literature, Mahayana Buddhism, Christianity, Chinese writings - it was a bit of a smorgasbord. Akutagawa can be dark: his preoccupation with suicide in his writing no doubt reflected in his own suicide at age 35; I found his piece Madonna in Black on a peculiarly evil Maria-Kannon to be troubling, not least because I have a kind of devotional fascination with Maria-Kannon as our Lady of Mercy. But still Akutagawa is deeply humanistic and wide-ranging. The Karetnyk translation can be digested in an afternoon, no doubt time well spent.

My Chekhov choice was the recent translation of fifty-two stories by the unsurpassable translator pair Richard Pevear and Larissa Volokhonsky. These two are artists in their own right... I can't say enough good things about their portfolio of translations. They are so good I've been forced to re-read a number of novels just to digest their interpretative readings over the years.

But back to Akutagawa. Here I post a translation done under Creative Commons license* of the story The Spider's Thread. I don't know if this is a re-telling of Dostoevsky's "Tale of the Onion" in Karamazov for sure, though the story line is so close that I find it impossible to believe otherwise: Lord Buddha Shakyamuni simply replacing the Guardian Angel. Get the Pushkin Press book to read it in a slightly more refined form, but I found this a wonderful read as well:


ONE


One day, the Buddha was strolling alone along the edge of a lotus pond in Paradise. The blooming lotus flowers in the pond were each pure white like jewels, and the place was filled with the indescribably wondrous fragrance continually emitted from each flower’s golden center. It was just morning in Paradise.

After a time, the Buddha paused at the edge of the pond and from between the lotus leaves that covered it saw a glimpse of the state of things below. Now this celestial pond just happened to lie directly over Hell, and peering through that crystal-clear water was like looking through a magnifying glass at the River of Death and the Mountain of Needles and such.

The Buddha saw there, in the depths of Hell, a single man writhing along with the other sinners. This man was named Kandata, and he had been a notorious thief who had performed murder and arson and other acts of evil. In his past, however, he had performed just one good deed: one day, when walking through the deep forest, he saw a spider crawling along the road. At first he raised his foot to crush it, but suddenly he changed his mind and stopped, saying, “No, small though it may be, a spider, too, has life. It would be a pity to meaninglessly end it,” and so did not kill it.

Looking down upon the captives in Hell the Buddha recalled this kind act that Kandata had performed, and thought to use his good deed as a way to save him from his fate. Looking aside, there on a jade-colored lotus leaf he saw a single spider, spinning out a web of silver thread. The Buddha carefully took the spider’s thread into his hand, and lowered it straight down between the jewel-like white lotuses into the depths of Hell.


TWO


Kandata was floating and sinking along with the other sinners in the Lake of Blood at the bottom of Hell. It was pitch black no matter which way he looked, and the occasional glimpse of light that he would see in the darkness would turn out to be just the glint of the terrible Mountain of Needles. How lonely he must have felt! All about him was the silence of the grave, the only occasional sound being a faint sigh from one of the damned. Those who were so evil as to be sent to this place were tired by its various torments, and left without even the strength to cry out. Even the great thief Kandata could only squirm like a dying frog as he choked in the Lake of Blood.

But one day, raising up his head and glancing at the sky above the lake, in the empty darkness Kandata saw a silver spider’s thread being lowered from the ceiling so far, far away. The thread seemed almost afraid to be seen, emitting a frail, constant light as it came down to just above Kandata’s head. Seeing this, Kandata couldn’t help but clap his hands in joy. If he were to cling to this thread and climb up it, he may be able to climb out of Hell! Perhaps he could even climb all the way to Paradise! Then he would never be chased up the Mountain of Needles, nor drowned in the Lake of Blood again.

Thinking so, he firmly grasped the spider’s thread with both hands and began to climb the thread, higher and higher. Having once been a great thief, he was used to tasks such as this. But the distance between Hell and Paradise is tens of thousands of miles, and so it would seem that no amount of effort would make this an easy journey. After climbing for some time Kandata tired, and couldn’t climb a bit higher. Having no other recourse, he hung there from the thread, resting, and while doing so looked down below.

He saw that he had made a good deal of progress. The Lake of Blood that he had been trapped in was now hidden in the dark below, and he had even climbed higher than the dimly glowing Mountain of Needles. If he could keep up this pace, perhaps he could escape from Hell after all. Kandata grasped the thread with both hands, and laughingly spoke in a voice that he hadn’t used in the many years since he had come here, “I’ve done it! I’ve done it!”

Looking down, however, what did he see but an endless queue of sinners, intently following him up the thread like a line of ants! Seeing this, surprise and fear kept Kandata hanging there for a time with mouth open and eyes blinking like a fool. How could this slender spider’s web, which should break even under just his weight, support the weight of all these other people? If the thread were to snap, all of his effort would be wasted and he would fall back into Hell with the others! That just would not do. But even as he thought these thoughts, hundreds more, thousands more of the damned came crawling up from the Lake of Blood, forming a line and scurrying up the thread. If he didn’t do something fast, surely the thread would snap in the middle and he would fall back down.

Kandata shouted out, “Hey! You sinners! This thread is mine! Who said you could climb up it? Get off! Get off!”

Though the thread had been fine until just then, with these words it snapped with a twang right where Kandata held it. Poor Kandata fell headfirst through the air, spinning like a top, right down through the darkness. The severed end of the silver thread hung there, suspended from heaven, shining with its pale light in that moonless, starless sky.


THREE


The Buddha stood in Paradise at the edge of the lotus pond, silently watching these events. After Kandata sank like a stone to the bottom of the Lake of Blood, he continued his stroll with a sad face. He must have been surprised that even after such severe punishment Kandata’s lack of compassion would lead him right back into Hell.

Yet the lotus blossoms in the lotus ponds of Paradise care nothing about such matters. Their jewel-like white flowers waved about the feet of the Buddha, and each flower’s golden center continuously filled the place with their indescribably wondrous fragrance. It was almost noon in Paradise.


(16 April 1918)

* Translation http://tonygonz.blogspot.com/2006/05/spiders-thread-akutagawa-ryunosuke.html

SymPy Tutorial Repository

Bobby Durrett's DBA Blog - Fri, 2021-04-09 11:02

I have been playing with the Python SymPy package and created a repository with my test scripts and notes:

https://github.com/bobbydurrett/sympytutorial

Might be helpful to someone. I just got started.

I had used Maxima before. SymPy and Maxima are both what Wikipedia calls “Computer Algebra Systems.” They have a nice list here:

https://en.wikipedia.org/wiki/List_of_computer_algebra_systems

I got a lot of use out of Maxima but I think it makes sense to switch the SymPy because it is written in Python and works well with other mainstream Python packages that I use like Matplotlib. They both fall under the SciPy umbrella of related tools so for me if I need some computer algebra I probably should stick with SymPy.

Maxima and SymPy are both free.

Bobby

Categories: DBA Blogs

Kubernetes Deployment and Step-by-Step Guide to Deployment: Update, Rollback, Scale & Delete

Online Apps DBA - Fri, 2021-04-09 01:42

Kubernetes is one of the hottest buzz-words in today’s IT industry. In spite of Kubernetes being a container orchestration platform, we don’t manage containers directly. Well, deploying an application onto Kubernetes using any container service is no rocket science. The process of manually updating containerized applications can be time-consuming and monotonous. Upgrading a service to […]

The post Kubernetes Deployment and Step-by-Step Guide to Deployment: Update, Rollback, Scale & Delete appeared first on Oracle Trainings for Apps & Fusion DBA.

Categories: APPS Blogs

Who Can Access

Michael Dinh - Wed, 2021-04-07 19:22

I had a request to list the users who have read access (or greater) to the APP schema.

Base on the results below:

User DINH has SELECT on APP.INTERVAL table (view)

Role APP_ROLE has SELECT/UPDATE on APP.INTERVAL table (view)

User DINH/APP/SYS has APP_ROLE

SQL> show con_name

CON_NAME
------------------------------
ORCLPDB1
SQL> show user
USER is "SYS"
SQL> @priv.sql

SQL> select username from dba_users where created  > (select created from v$database) order by 1;

USERNAME
------------------------------
APP
AUDIT_TEST
DINH
DINH099PD
PDBADMIN
WMS099PD

6 rows selected.

SQL> select * from DBA_TAB_PRIVS where owner='APP';

GRANTEE              OWNER                TABLE_NAME                GRANTOR              PRIVILEGE            GRANTABLE HIERARCHY COM TYPE                     INH
-------------------- -------------------- ------------------------- -------------------- -------------------- --------- --------- --- ------------------------ ---
DINH                 APP                  INTERVAL                  APP                  SELECT               NO        NO        NO  TABLE                    NO
APP_ROLE             APP                  INTERVAL                  APP                  SELECT               NO        NO        NO  TABLE                    NO
APP_ROLE             APP                  INTERVAL                  APP                  UPDATE               NO        NO        NO  TABLE                    NO

SQL> --- ROLE_TAB_PRIVS describes table privileges granted to roles.
SQL> --- Information is provided only about roles to which the user has access.
SQL> select * from ROLE_TAB_PRIVS where OWNER='APP';

ROLE                           OWNER                TABLE_NAME                COLUMN_NAME               PRIVILEGE            GRANTABLE COM INH
------------------------------ -------------------- ------------------------- ------------------------- -------------------- --------- --- ---
APP_ROLE                       APP                  INTERVAL                                            UPDATE               NO        NO  NO
APP_ROLE                       APP                  INTERVAL                                            SELECT               NO        NO  NO

SQL> select * from DBA_ROLE_PRIVS where GRANTED_ROLE='APP_ROLE' order by 1;

GRANTEE              GRANTED_ ADMIN DEL DEFAULT COM INH
-------------------- -------- ----- --- ------- --- ---
APP                  APP_ROLE YES   NO  YES     NO  NO
DINH                 APP_ROLE NO    NO  YES     NO  NO
SYS                  APP_ROLE NO    NO  YES     NO  NO

SQL>

I also used Pete Finnigan’s who_can_access.sql for comparison.

Note who_can_access.sql is per object vs per schema.

If there were hundreds / thousands of table, then not sure how this will scale.

who_can_access: Release 1.0.3.0.0 - Production on Wed Apr 07 19:00:04 2021
Copyright (c) 2004 PeteFinnigan.com Limited. All rights reserved.

NAME OF OBJECT TO CHECK       [USER_OBJECTS]: INTERVAL
OWNER OF THE OBJECT TO CHECK          [USER]: APP
OUTPUT METHOD Screen/File                [S]:
FILE NAME FOR OUTPUT              [priv.lst]:
OUTPUT DIRECTORY [DIRECTORY  or file (/tmp)]:
EXCLUDE CERTAIN USERS                    [N]:
USER TO SKIP                         [TEST%]:

Checking object => APP.INTERVAL
====================================================================


Object type is => TABLE (TAB)
        Privilege => SELECT is granted to =>
        Role => APP_ROLE (ADM = NO) which is granted to =>
                User => DINH (ADM = NO)
                User => SYS (ADM = NO)
                User => APP (ADM = YES)
        User => DINH (ADM = NO)
        Privilege => UPDATE is granted to =>
        Role => APP_ROLE (ADM = NO) which is granted to =>
                User => DINH (ADM = NO)
                User => SYS (ADM = NO)
                User => APP (ADM = YES)

PL/SQL procedure successfully completed.


For updates please visit http://www.petefinnigan.com/tools.htm

SQL>

Did I do this right?

How to do point in time recovery

Tom Kyte - Wed, 2021-04-07 19:06
Hello Tom, Supposed my data file got crashed at 9PM IST & we to do point in time recovery. We have a full db backup which was started at 8AM IST & got finished at 7PM IST in that do we need to apply redo log archive backup? Can you tell me how will come to know which archive backup needed to perform point in time recovery? Regards, Nishant
Categories: DBA Blogs

Extract multiple hostname from a long string by using regexp_substr

Tom Kyte - Wed, 2021-04-07 19:06
Hi Tom, I am trying to extract multiple hostname out from a long strings (In fact, I will use this method for DBLink - host column later). However, my query is only able to extract the first hostname. I did try to put {1,0} after the match pattern '((HOST|HOSTNAME)[[:space:]]*(\=)[[:space:]]*(\w+|\-|\_)*(\.\w+)*|(\w+|\-|\_)*(\.\w+)+|((\w+|\-|\_)+$)){1,}', but still not able to extract the rest of occurrence match at all. Can you please help me out? Thanks. == The query output === HOSTNAME -------------------- LXC01-VIP.TEST.COM Expected Output -------------------- LXC01-VIP.TEST.COM LXC02-VIP.TEST.COM -------------------------------- My current query ---------------------------------- <code>SELECT REGEXP_REPLACE ( REGEXP_REPLACE ( REGEXP_REPLACE ( REGEXP_SUBSTR ( UPPER ( '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = lxc01-vip.test.com)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = lxc02-vip.test.com)(PORT = 1521)) (LOAD_BALANCE = ON) (FAILOVER = ON) ) (CONNECT_DATA = (SERVICE_NAME = Test) ) )'), '((HOST|HOSTNAME)[[:space:]]*(\=)[[:space:]]*(\w+|\-|\_)*(\.\w+)*|(\w+|\-|\_)*(\.\w+)+|((\w+|\-|\_)+$))'), ' ', ''), 'HOST=', ''), 'HOSTNAME=', '') HOSTNAME FROM DUAL;</code> Regards, Anders
Categories: DBA Blogs

ORA-14767 when day of month > 28 with interval partitioning month interval

Bobby Durrett's DBA Blog - Wed, 2021-04-07 18:07
SQL> CREATE TABLE test(
  2  RUN_DATE DATE,
  3  MY_NBR NUMBER(4)
  4  )
  5  PARTITION BY RANGE (RUN_DATE)
  6  INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  7  (
  8    PARTITION data_p1 VALUES LESS THAN (TO_DATE('01/29/2017', 'MM/DD/YYYY'))
  9  );
CREATE TABLE test(
*
ERROR at line 1:
ORA-14767: Cannot specify this interval with existing high bounds


SQL> 
SQL> CREATE TABLE test(
  2  RUN_DATE DATE,
  3  MY_NBR NUMBER(4)
  4  )
  5  PARTITION BY RANGE (RUN_DATE)
  6  INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
  7  (
  8    PARTITION data_p1 VALUES LESS THAN (TO_DATE('01/28/2017', 'MM/DD/YYYY'))
  9  );

Table created.

Creating a range partitioned table with a date type partitioning column and a month interval must have a starting partition that has a day < 29 or it gets an ORA-14767 error.

The error message “Cannot specify this interval with existing high bounds” is not helpful. How about something like “Need a day of the month that exists in every month”? February only has 28 days most years so 28 is the max.

Bobby

Categories: DBA Blogs

Allow user to export all reports to Excel

Tom Kyte - Wed, 2021-04-07 00:46
I have a page which includes 4 regions. All are Classic Reports. I set "Yes" for "CSV Export Enabled" under Attributes/Download of each Classic Report. Is there any way to build in functionality that allow user to <b>export</b> all of classic reports to Excel <b>at once</b> as opposed to having to do so one at a time?
Categories: DBA Blogs

Want to understand use of Checkpoint & scn

Tom Kyte - Wed, 2021-04-07 00:46
Hello Tom, I just wanted to know use of Checkpoint & scn. I know the basic definition but want to know in what are all the situation it occurs & how it will be use in case of Oracle recovery. Can you please explain me with an example. Thanks, Nitin
Categories: DBA Blogs

Recompilation of Trigger Fails with "ORA-01405: fetched column value is NULL"

Tom Kyte - Wed, 2021-04-07 00:46
The error below shown when I recompile a trigger with a new version in a productive database. However, I can recompile the same trigger successfully in a test database (Both the productive and test database are in version 12.2.0.1.0). <i>Error report - ORA-00604: error occurred at recursive SQL level 1 ORA-01405: fetched column value is NULL 00604. 00000 - "error occurred at recursive SQL level %s" *Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables). *Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support. </i> I saw a statement on the Oracle support page but I couldn't verify if drop and compile again the trigger works as I couldn't replicate this error in the test database (I have a concern about dropping the existing trigger and still not able to compile the new trigger in the productive database): <b>The following "CREATE OR REPLACE TRIGGER" statement on an existing trigger fails with "ORA-01405" after application of Database Oct 2018 Release Update 12.2.0.1.181016(<patch 28662603>). The trigger compilation goes successful if the trigger is dropped first and then compiled again with the same "CREATE OR REPLACE TRIGGER" statement.</b> The starting of my trigger code is as below: <code> CREATE OR REPLACE TRIGGER INSERT_TABLE_TR BEFORE INSERT ON table1 REFERENCING OLD AS old NEW AS new FOR EACH ROW ... </code> Why is the trigger recompilation in the productive database raise this error but success in the test database? How can I replicate this error in the test database so that I could test out the suggestion?
Categories: DBA Blogs

Appending to a CLOB with a 4-byte Chinese character in an AL32UTF8 database raises ORA-22921

Tom Kyte - Wed, 2021-04-07 00:46
Hi there, Is there a setting I can tweak in the database to adjust this behaviour and thus avoid the error? There's a few workarounds, but I need* to avoid an application code change. Through Oracle Support this has been raised as Bug 32703286 - DBMS_LOB.WRITEAPPEND FAILS WITH ORA-22921 WHILE PROCESSING SPECIAL CHARACTERS. Ideally this bug would be fixed by July and we can patch our database(s) but we're not holding our breath and are asking the customer to be patient. Thanks, Tim * With the planned development team workload, I can possibly implement the workaround this calendar year but my management is reluctant to authorise work to implement a workaround for a bug when we have much more important things to assign my time to.
Categories: DBA Blogs

Detect Linux Host Restart

Michael Dinh - Mon, 2021-04-05 22:15

Sometime ago I had blogged about Monitor Linux Host Restart

The simple solution: How to email admins automatically after a Linux server starts?

Here is the example from root’s cron:

# crontab -l
@reboot su oracle -c '/home/oracle/scripts/host_restart_alert.sh' > /tmp/host_restart_alert.out 2>&1

Shell script is used because mail cannot be sent from local host and will need to be sent from remote host.

#!/bin/bash -x
MAILFROM=
MAILTO=
SUBJECT="Node reboot detected for $(hostname)"
EMAILMESSAGE="$(hostname) was restarted `uptime -p| awk -F'up' '{print $2}'` ago at `uptime -s`"

# uptime reports minutely and need to sleep for at least 60s after host restart
sleep 63

ssh oracle@remotehost /bin/bash <<EOF
/home/oracle/scripts/send_email.sh "$EMAILMESSAGE" "$SUBJECT" "$MAILFROM" "$MAILTO"
EOF

exit

Why is there a need to detect host restart and isn’t there monitoring for the host?

This is Oracle Exadata Cloud@Customer (ExaCC) environment.

When Oracle support performs patching, they do not provide any sort of communication or status and monitoring is disable for all hosts beforehand.

OPatchAuto to Patch a GI/RAC Environment.

After the patching is complete and your servers are restarted, you should check your product software to verify that the issue has been resolved.

This is why there is a need to detect and be notified for server restart.

Using the INTERVAL data type ( and some pictures) to tell the difference between two Timestamps in Oracle

The Anti-Kyte - Mon, 2021-04-05 14:30

Looking after an overnight batch process can be a big fraught at times.
If it’s a good day, you might begin the morning with a nice cup of coffee and a leisurely scroll through the logs to confirm that all is well.
In contrast, if the batch has overrun you may well find yourself scrambling through those same logs whilst gulping down large quantities of caffeine in a desperate attempt to hot-wire your brain into working out the elapsed time between each entry. Not great. Especially when you consider that, as Terry Pratchett put it,
“Coffee is a way of stealing time that should by rights belong to your older self”.
A better approach might be to get Oracle to do it for you.

What we’re going to look at here is :

  • the INTERVAL data type that holds the difference between two TIMESTAMP values
  • using the LAG SQL function to report the interval time between timestamps across rows of a query
  • creating some graphs using SQLDeveloper User Defined Reports for when it’s just too early for words and numbers.
The Batch Log table

Running on Oracle 18cXE, our overnight batch logs to the BATCH_LOG table :

create table batch_log (
    id number generated always as identity,
    log_ts timestamp not null,
    job_name varchar2(50),
    status varchar2(25))
/ 

I’ve used the following script to populate the table with some test data :

set serverout on size unlimited
clear screen
declare

    tbl_jobs dbms_utility.name_array;
    tbl_statuses dbms_utility.name_array;
    
    start_ts timestamp := to_timestamp('16-MAR-2021 01:00', 'DD-MON-YYYY HH24:MI');
    v_ts timestamp;
    v_duration pls_integer;
begin

    tbl_jobs(1) := 'EXTRACT';
    tbl_jobs(2) := 'TRANSFORM';
    tbl_jobs(3) := 'LOAD';
    tbl_jobs(4) := 'REPORT';

    tbl_statuses(1) := 'STARTED';
    tbl_statuses(2) := 'COMPLETED';
        
    for i in 1..7 loop
        v_ts := start_ts;
        for j in 1..tbl_jobs.count loop
            v_duration := 
                case mod(j, 4) 
                    when 3 then trunc(dbms_random.value(8,11))
                    when 2 then trunc(dbms_random.value(30,41)) 
                    when 1 then trunc(dbms_random.value(25,31)) 
                    else trunc(dbms_random.value(15,21)) 
                end;
            for k in 1..tbl_statuses.count loop
                insert into batch_log( log_ts, job_name, status)
                values( 
                    case when tbl_statuses(k) = 'STARTED' then v_ts else v_ts + (v_duration/1440) end, 
                    tbl_jobs(j), 
                    tbl_statuses(k));
            end loop;
            v_ts := v_ts + (v_duration/1440);
        end loop;
        start_ts := start_ts + interval '1' day;
    end loop;    
end;
/

-- Now simulate a long running Load job
update batch_log 
set log_ts = log_ts + (60/1440)
where id >= (select max(id) from batch_log where job_name = 'LOAD' and status = 'COMPLETED');


commit;


As a result, the table now has a week’s worth of log entries.

The INTERVAL data type

You can find lots of information about Oracle’s DATETIME and INTERVAL data types in the documentation. Here’s the 18c version , for example ( all of the code here is written and tested on 18cXE).

You will see from this that we have two distinct INTERVAL data types – Year to Month and Day to Second.
It’s the latter of these which is relevant in the current context, unless you’re having extreme performance issues on your overnight batch.

If you have any scheduler jobs running, you can see an example of the INTERVAL data type in action in the _scheduler_jobs views :

select owner, job_name, last_run_duration
from dba_scheduler_jobs t
where run_count > 0;
OWNER           JOB_NAME                                 LAST_RUN_DURATION  
--------------- ---------------------------------------- -------------------
SYS             PURGE_LOG                                +00 00:00:00.924991
SYS             PMO_DEFERRED_GIDX_MAINT_JOB              +00 00:00:00.117572
SYS             CLEANUP_NON_EXIST_OBJ                    +00 00:00:00.221356
SYS             CLEANUP_ONLINE_IND_BUILD                 +00 00:00:00.382708
SYS             CLEANUP_TAB_IOT_PMO                      +00 00:00:00.129863
SYS             CLEANUP_TRANSIENT_TYPE                   +00 00:00:00.071285
SYS             CLEANUP_TRANSIENT_PKG                    +00 00:00:00.094254
SYS             CLEANUP_ONLINE_PMO                       +00 00:00:00.024660
SYS             FILE_SIZE_UPD                            +00 00:00:00.166189
SYS             ORA$AUTOTASK_CLEAN                       +00 00:00:00.151000
SYS             DRA_REEVALUATE_OPEN_FAILURES             +00 00:00:00.387121
SYS             BSLN_MAINTAIN_STATS_JOB                  +00 00:00:00.443146
SYS             RSE$CLEAN_RECOVERABLE_SCRIPT             +00 00:00:00.324392
SYS             SM$CLEAN_AUTO_SPLIT_MERGE                +00 00:00:00.023483
APEX_180200     ORACLE_APEX_PURGE_SESSIONS               +00 00:00:00.868204
APEX_180200     ORACLE_APEX_MAIL_QUEUE                   +00 00:00:00.026859
APEX_180200     ORACLE_APEX_WS_NOTIFICATIONS             +00 00:00:01.685787
APEX_180200     ORACLE_APEX_DAILY_MAINTENANCE            +00 00:00:00.943306
ORDS_METADATA   CLEAN_OLD_ORDS_SESSIONS                  +00 00:00:00.640826

19 rows selected. 

According to the documentation, INTERVALs are not subject to a format model in the same way that dates and timestamps are. However, you can re-format their contents using the EXTRACT function.
For example, if we want to convert the LAST_RUN_DURATION from our previous query into seconds, then we can run :

select owner, job_name,
    (extract ( minute from last_run_duration) * 60) +
    extract( second from last_run_duration) as last_run_secs
from dba_scheduler_jobs
where run_count > 0
order by 3 desc
/

…which returns…

OWNER           JOB_NAME                                 LAST_RUN_SECS
--------------- ---------------------------------------- -------------
APEX_180200     ORACLE_APEX_WS_NOTIFICATIONS                  1.685787
APEX_180200     ORACLE_APEX_DAILY_MAINTENANCE                  .943306
SYS             PURGE_LOG                                      .924991
APEX_180200     ORACLE_APEX_PURGE_SESSIONS                     .868204
ORDS_METADATA   CLEAN_OLD_ORDS_SESSIONS                        .640826
SYS             BSLN_MAINTAIN_STATS_JOB                        .443146
SYS             DRA_REEVALUATE_OPEN_FAILURES                   .387121
SYS             CLEANUP_ONLINE_IND_BUILD                       .382708
SYS             RSE$CLEAN_RECOVERABLE_SCRIPT                   .324392
SYS             CLEANUP_NON_EXIST_OBJ                          .221356
SYS             FILE_SIZE_UPD                                  .166189
SYS             ORA$AUTOTASK_CLEAN                                .151
SYS             CLEANUP_TAB_IOT_PMO                            .129863
SYS             PMO_DEFERRED_GIDX_MAINT_JOB                    .117572
SYS             CLEANUP_TRANSIENT_PKG                          .094254
SYS             CLEANUP_TRANSIENT_TYPE                         .071285
APEX_180200     ORACLE_APEX_MAIL_QUEUE                         .026859
SYS             CLEANUP_ONLINE_PMO                              .02466
SYS             SM$CLEAN_AUTO_SPLIT_MERGE                      .023483

19 rows selected.

What we need now is a way to calculate the durations between rows in a single query…

The LAG function

As ever, OracleBase has a pretty good explanation of the LEAD and LAG functions.

All we need to do is to use the LAG function to find the elapsed time between the timestamp of the current record and that of it’s predecessor in the result set :

select trunc(log_ts) as run_date,
    job_name,
    status,
    log_ts,
    case when status = 'COMPLETED' then
        log_ts - lag( log_ts) over( order by log_ts)
    end as duration,
    case when status = 'COMPLETED' then
        extract( hour from log_ts - lag(log_ts) over (order by log_ts)) * 60 +
        extract( minute from log_ts - lag(log_ts) over (order by log_ts))
    end as duration_mins
from batch_log
order by log_ts
/

We could use what we have so far as the basis for an ad-hoc script to give us information about batch runtimes. Then again, if it’s one of those mornings where you’re struggling a bit, looking at a picture might make things a bit easier. That last listing was from SQLDeveloper, which is handy considering where we’re going next …

SQLDeveloper User Defined Chart Reports – The Line Chart

We can see the total runtime for the batch for each day using the query :

with batch_runs as (
    select job_name, status, log_ts,
        case status when 'COMPLETED' then
            (extract( hour from log_ts - lag( log_ts, 1) over( order by log_ts)) * 60) +
            extract(minute from log_ts - lag(log_ts, 1) over( order by log_ts))
        end as duration_mins
    from batch_log
    where (
        (job_name = 'EXTRACT' and status = 'STARTED')
        or (job_name = 'REPORT' and status = 'COMPLETED'))    
    order by log_ts)
select trunc(log_ts) as run_date,
    duration_mins
from batch_runs    
where duration_mins is not null
order by 1
/

…which returns…

RUN_DATE    DURATION_MINS
----------- -------------
16-MAR-2021            88
17-MAR-2021            83
18-MAR-2021            90
19-MAR-2021            84
20-MAR-2021            94
21-MAR-2021            91
22-MAR-2021           155

7 rows selected. 

Jeff Smith has a nifty way of turning a SQLDeveloper query grid into a chart report, which I’ve largely plagiarised below.

First, we run the query in a SQLDeveloper worksheet. Then, in the result grid, right-click and select Save Grid as Report :

This will take you to the Create Report Window :

As you can see, SQLDeveloper has reformatted the query a bit.

Now we can add a Name for the report.
Before we go any further, it’s probably a good idea to specify a connection at this point. This will help us when we’re configuring the report as we’ll be able to use live data to see how things look.
The other thing we need to do is to specify a series name so that we can produce a line graph.

This means that the report query now looks like this :

SELECT "RUN_DATE", "DURATION_MINS", 
    'Overnight Runs' as series_name 
FROM(
with batch_runs as (
    select job_name, status, log_ts,
        case status when 'COMPLETED' then
            (extract( hour from log_ts - lag( log_ts, 1) over( order by log_ts)) * 60) +
            extract(minute from log_ts - lag(log_ts, 1) over( order by log_ts))
        end as duration_mins
    from batch_log
    where (
        (job_name = 'EXTRACT' and status = 'STARTED')
        or (job_name = 'REPORT' and status = 'COMPLETED'))    
    order by log_ts)
select trunc(log_ts) as run_date,
    duration_mins
from batch_runs    
where duration_mins is not null
order by 1
)

…and with a connection specified, I can check everything is still working as expected by clicking the Test Report button…

…which provides the expected output.

Now I know that my query still works, I can change the report Style from Table to Chart :

Now we need to click on the Property node of the navigation tree in the left-hand pane of the Create Report Window.
Here, we change the Chart Type to Line :

Next we need to specify the Data for the report so we need to click on the Data Tree Node.

As we’re connected to a database, we can Fetch Column Names so we can select the Group, Series and Value columns from a drop-down.

Now we can check the Use Live Data checkbox and click Refresh Report to see what the output will look like :

Finally, click the Apply button in the bottom right of the Create Report window and the new report will appear in the User Defined Reports node of the main Reports pane :

Now we can run the report in the usual way…

A picture paints a thousand words…but in this case just says “Hmmm…”

If we want a more granular view of what’s happening during the batch run, but we’re not quite ready to deal with actual text, we can try using…

A Stacked Bar Chart

This time, we want to know the elapsed time for each process in the batch. Therefore, we’ll use this query as the basis for our report :

select trunc(log_ts) as run_date,
    job_name,
    status,
    log_ts,
    case when status = 'COMPLETED' then
        log_ts - lag( log_ts) over( order by log_ts)
    end as duration,
    case when status = 'COMPLETED' then
        extract( hour from log_ts - lag(log_ts) over (order by log_ts)) * 60 +
        extract( minute from log_ts - lag(log_ts) over (order by log_ts))
    end as duration_mins
from batch_log
order by log_ts

The Report creation is as before, except that this time we choose a Chart Type of Bar – Vertical Stack.
Additionally, in order to view the total time for each day, we go to the Plot Area Property and check the Cumulative box in the Data Tips section :

Running this new report, we can see that the increase in duration is due to the Load process taking rather longer than normal :

Should it really be that colour ?

Of course, you’ll need to investigate further to find out just exactly what’s going on but, thanks to some handy SQL features and a couple of SQLDeveloper Chart Reports, at least you know where to start looking.

__ATA.cmd.push(function() { __ATA.initDynamicSlot({ id: 'atatags-26942-606b76329ee58', location: 120, formFactor: '001', label: { text: 'Advertisements', }, creative: { reportAd: { text: 'Report this ad', }, privacySettings: { text: 'Privacy', } } }); });

Come see us at Reconnect Envision!

PeopleSoft Technology Blog - Mon, 2021-04-05 10:27

Quest's Reconnect Envision is offering exciting PeopleSoft education content, with opportunities to chat online with the PeopleSoft experts, arrange to connect with other users, and meet partners with complementary solutions and services.

We’ve planned exciting content for the 2021 Reconnect Envision Conference sponsored by Quest. This year we’ve scheduled more variety in the sessions with panel discussions on various topics and the opportunity for attendees in various worldwide time zones to hear the session presentations.

Below are the informative PeopleSoft PeopleSoft Technology sessions you may be interested in attending presented by Oracle Team members, Partners, and Customers.

(Note that session dates and times could change, so consult the Reconnect Envision agenda online for precise times.)

 

Global Replay Schedule

If you reside outside North America, Quest has made it easier for PeopleSoft users around the globe to join the Reconnect Envision Conference with the following features:

  • Extended session hours – Whether you live in London, Bangalore, Sydney, or Hong Kong, the flexible schedule is designed for attendees to join the sessions live

  • Instant session replays – If you  can’t join a session real-time, simply press ’play’ and you can watch past sessions at the time most convenient for you

  • Flexible simulcast programming – Tune into scheduled replays of popular keynote, roadmap, and other core education sessions

Register now and build your own session agenda for Reconnect Envision 2021.

Note that when you choose sessions for your agenda it will automatically show your local time for the live session.  Of course, you will have the option of watching the recording at any time that suits you.

Case Study

Jonathan Lewis - Mon, 2021-04-05 09:36

A recent question on the Oracle Developer forum posed an interesting question on “finding the closest match” to a numeric value. The OP supplied SQL to create two tables, first a set of “valid” values each with an Id, then a set of measures. The requirement was to find, for each measure, the closest valid value and report its id.

In this note I’m going to make a few comments on three topics:

  • how the question was posed,
  • general thoughts on modelling,
  • some ideas on what to look for when testing possible solutions

We’ll start with the data (almost) as supplied:

rem
rem     Script:         closest_match.sql
rem     Author:         Jonathan Lewis / user626688
rem     Dated:          Apr 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem             11.2.0.4         (with event 22829)
rem
 
create table nom_val_lkp(
        lkp_id  number       not null,
        nom_val number(3,2)  primary key
)
-- organization index
/

insert into nom_val_lkp values(1, 0.1);
insert into nom_val_lkp values(2, 0.2);
insert into nom_val_lkp values(3, 0.3);
insert into nom_val_lkp values(4, 0.4);
insert into nom_val_lkp values(5, 0.5);
insert into nom_val_lkp values(6, 0.6);
insert into nom_val_lkp values(7, 0.7);
insert into nom_val_lkp values(8, 0.8);
insert into nom_val_lkp values(9, 0.9);
commit;

create table measure_tbl(
        id              number      not null, 
        measure_val     number(3,2) not null
)
/

insert into measure_tbl values(1, 0.24);
insert into measure_tbl values(2, 0.5);
insert into measure_tbl values(3, 0.14);
insert into measure_tbl values(4, 0.68);
commit;

insert into measure_tbl values(5, 1.38);
insert into measure_tbl values(6, 0.05);
commit;


execute dbms_stats.gather_table_stats(null,'measure_tbl')
execute dbms_stats.gather_table_stats(null,'nom_val_lkp')

There are a couple of differences between the original and the SQL I’ve listed above. Given the nature of the requirement I’ve added not null constraints to both the lkp_id and nom_val columns of the “valid values” table. I think it’s also reasonable to assume that both columns outght to be (individually) unique and could both be candidate keys for the table although I’ve not bothered to add a uniqueness constraint to the lkp_id. I have made the nom_val (the interesting bit) the primary key because that’s potentially an important feature of a good solution. Obviously this is guesswork on my part, but I think they’re reasonable guesses of what the “real application” will look like and they’re details that ought to be been included in the original specification.

You’ll see that I’ve also included the option for making the table an index organized table – but that’s a generic implementation choice for small look-up tables not something that you could call an omission in the specification of requirements.

One thing to note about the nom_val_lkp table is that the nom_val is strictly constrained to be 3 digits with 2 decimal places, which means values between -9.99 to +9.99. It’s going to be a pretty small table – no more than 1,999 rows. (In “real life” it’s possible that the measure all have to be postive – and if so that’s another detail that could have gone into the specification – so the column could also have a check constraint to that effect.)

Looking at the measure_tbl (which is the “big data” table) I’ve added not null constraints to both columns; I’ve also added a couple of extra rows to the table to make sure that we can test boundary conditions when we write the final SQL statement. We’re looking for “the closest match” so we’ll be looking in the nom_val_lkp table for values above and below the measure value – so we ought to have a measure row where there is no “below” value and one with no “above” value. A common oversight in modelling is to forget about checking special cases, and simple boundary conditions are often overlooked (or inadequately covered).

Thinking about the “above / below / closest” requirement, an immediate follow-up questions springs to mind. What if there is no exact match and the valid values either side are the same distance from the measure? If there’s a tie should the query return the lower value or the higher value, or does it not matter? The specification is not complete, and the most efficient solution may depend on this detail.

Interestingly the measure_val column is constrained in exactly the same way as the nom_val column -3 digits with 2 d.p. Apparently the requirement isn’t something like “take a measurement to 6 decimal places then give me a value to 2 d.p.”; no matter how large the measure_val table gets the number of distinct values it records is pretty small – which means caching considerations could become important. With this thought in mind I added a few more lines (before gathering stats) to make multiple copies of the supplied measures data to model (approximately, and with a very large bias) a large table with a small number of distinct measures.

insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
commit;

update measure_tbl set id = rownum;
commit;

execute dbms_stats.gather_table_stats(null,'measure_tbl')

This doubling-up code resulted in a total of 6 * 1,024 = 6,144 rows in the table. I only increased the data volume after I’d checked that I had a query that produced the correct results, of course.

A possible solution

By the time I saw the thread on the Oracle forum there were already three solutions on offer, but they all took the route of using analytic functions, including one that used keep(dense_rank …), and these all involved sorting the entire measures dataset; so I thought I’d try an approach that demonstrated a completely different method that was visibly following the strategy: “for each row do two high-precision lookups”. I implemented this by joining two lateral views of the lookup table to the measures table. Since I was sitting in front of a copy of 11.2.0.4 at the time I had to set the event 22829 to enable the feature – here’s the basic code with the plan produced by 11g:

select  /*+ qb_name(main) */
        mt.id,
        mt.measure_val,
        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,
        nt_low.nom_val  low_val,
        nt_low.lkp_id   low_lkp,
        nt_high.nom_val high_val,
        nt_high.lkp_id  high_lkp 
from
        measure_tbl     mt,
        lateral(
                select
                        /*+ qb_name(low) index_rs_desc(nt (nom_val)) */
                        nt.lkp_id, nt.nom_val
                from    nom_val_lkp nt
                where   nt.nom_val <= mt.measure_val
                and     rownum = 1
        )(+) nt_low,
        lateral(
                select
                        /*+ qb_name(high) index_rs_asc(nt (nom_val)) */
                        nt.lkp_id, nt.nom_val
                from    nom_val_lkp nt
                where   nt.nom_val >= mt.measure_val
                and     rownum = 1
        ) (+) nt_high
/

        ID MEASURE_VAL     LKP_ID    LOW_VAL    LOW_LKP   HIGH_VAL   HIGH_LKP
---------- ----------- ---------- ---------- ---------- ---------- ----------
         1         .24          2         .2          2         .3          3
         2          .5          5         .5          5         .5          5
         3         .14          1         .1          1         .2          2
         4         .68          7         .6          6         .7          7
         5        1.38          9         .9          9
         6         .05          1                               .1          1


6 rows selected.

You’ll notice that (for debugging purposes) I’ve included columns in my output for the lkp_id and nom_val just lower than (or matching) and just higher than (or matching) the measure_val. The blanks this produces in two of the rows conveniently highlights the cases where the measure is “out of bounds”.

With my tiny data set I had to include the index_rs_desc() hint. Of course I should really have included an “order by” clause in the two subqueries and used an extra layer of inline views to introduce the rownum = 1 predicate, viz:

        lateral(
                select  * 
                from    (
                        select  /*+ qb_name(low) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        order by
                                nom_val desc
                )
                where   rownum = 1
        )(+) nt_low,

There were two reasons I didn’t do this: first I wanted to keep the code short, secondly it wouldn’t have worked with 11g because it was only in 12c that a correlated subquery could correlate more than one level up – the predicate referencing mt.measure_val would have raised error “ORA-00904: invalid identifier”.

If you’re not familiar with lateral views, the idea is quite simple: as with any inline view in the from clause it’s just a query that returns a result set that looks like a table, but it has the special condition that the predicafes in the query can reference columns from tables (or views) that have appeared further to the left in (or, equivalently, further up) the from clause. In this case both of my inline views query nom_val_lkp and both of them reference a column in measure_tbl which was the first table in the from clause.

There are two distinguishing details that are a consequence of the lateral view. First, the view effectively has a join to the driving table built into it so my main query doesn’t have any where clause predicates joining the views to the rest of the query. Se,condly I want to do outer joins (to deal with the cases where there isn’t a nom_val higher/ lower than the measure_val) so in the absence of a join predicate in the main query the necessary syntax simply adds Oracle’s traditional “(+)” to the lateral() operator itself. (If you want to go “full-ANSI” you would use outer apply() instead of lateral()(+) at this point – but 11g doesn’t support outer apply().

Here’s the execution plan from 11g for this query – I’ve enabled rowsource execution stats and pulled the plan from memory using the ‘allstats last’ format option:

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |      6 |00:00:00.01 |      29 |
|   1 |  NESTED LOOPS OUTER              |              |      1 |      6 |      6 |00:00:00.01 |      29 |
|   2 |   NESTED LOOPS OUTER             |              |      1 |      6 |      6 |00:00:00.01 |      18 |
|   3 |    TABLE ACCESS FULL             | MEASURE_TBL  |      1 |      6 |      6 |00:00:00.01 |       7 |
|   4 |    VIEW                          |              |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  5 |     COUNT STOPKEY                |              |      6 |        |      5 |00:00:00.01 |      11 |
|   6 |      TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP  |      6 |      2 |      5 |00:00:00.01 |      11 |
|*  7 |       INDEX RANGE SCAN DESCENDING| SYS_C0072287 |      6 |      6 |      5 |00:00:00.01 |       6 |
|   8 |   VIEW                           |              |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  9 |    COUNT STOPKEY                 |              |      6 |        |      5 |00:00:00.01 |      11 |
|  10 |     TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|* 11 |      INDEX RANGE SCAN            | SYS_C0072287 |      6 |      4 |      5 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(ROWNUM=1)
   7 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
       filter("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
   9 - filter(ROWNUM=1)
  11 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")


As you can see we’ve done a full tablescan of measure_tbl, then performed an outer join to each of two (unnamed) views for each row, and each time we’ve accessed a view we’ve done an index range scan (descending in one case) into nom_val_lkp. passing in (according to the Predicate Information) the measure_val from measure_tbl.

It’s a little oddity I hadn’t noticed before that the ascending and descending range scans behave slightly differently – the descending range scan says we’ve used the predicate as both an access and a filter predicate. I’ll have to check whether this is always the case or whether it’s version-dependent or whether it’s only true under some conditions.

The only other detail to mention is the expression I’ve used to report the closest match – which is a little messy to allow for “out of range” measures::

        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,

This case expression says that if the higher nom_val is closer to (or, to be precise, not further from) the meause_val than the lower nom_val then report the higher lkp_id. otherwise report the lower lkp_id. The ordering of the comparison means that when the differences are the same the higher value will always be reported; and the “cross-over” use of the nvl() function ensures that when the measure_val is out of range (which means one of the nom_val subqueries will have returned null) we see the nom_val that’s at the end of the range rather than a null.

Some bad news

At first sight the lateral() view looks as if it might be a candidate for scalar subquery caching – so when I create multiple copies of the 6 rows in the measure_tbl and run my query against the expanded data set I might hope to get excellent performance because Oracle might only have to call each lateral view once and and cache the subquery inputs and results from that point onwards. But here are the stats I get from the 11g plan after exanding the data to 6,144 rows:

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |   6144 |00:00:00.82 |   22953 |
|   1 |  NESTED LOOPS OUTER              |              |      1 |   6144 |   6144 |00:00:00.82 |   22953 |
|   2 |   NESTED LOOPS OUTER             |              |      1 |   6144 |   6144 |00:00:00.47 |   11689 |
|   3 |    TABLE ACCESS FULL             | MEASURE_TBL  |      1 |   6144 |   6144 |00:00:00.03 |     425 |
|   4 |    VIEW                          |              |   6144 |      1 |   5120 |00:00:00.28 |   11264 |
|*  5 |     COUNT STOPKEY                |              |   6144 |        |   5120 |00:00:00.20 |   11264 |
|   6 |      TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP  |   6144 |      2 |   5120 |00:00:00.12 |   11264 |
|*  7 |       INDEX RANGE SCAN DESCENDING| SYS_C0072291 |   6144 |      5 |   5120 |00:00:00.04 |    6144 |
|   8 |   VIEW                           |              |   6144 |      1 |   5120 |00:00:00.32 |   11264 |
|*  9 |    COUNT STOPKEY                 |              |   6144 |        |   5120 |00:00:00.19 |   11264 |
|  10 |     TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP  |   6144 |      2 |   5120 |00:00:00.11 |   11264 |
|* 11 |      INDEX RANGE SCAN            | SYS_C0072291 |   6144 |      3 |   5120 |00:00:00.04 |    6144 |
-----------------------------------------------------------------------------------------------------------


Look at the Starts column: the two views were called once each for every single row in the expanded measure_tbl, there’s no scalar subquery caching going on.

Bug time (1)

Of course, this is 11g and I’ve enabled lateral views by setting an event; it’s not an officially supported feature so maybe if I upgrade to 12c (or 19c), where the feature is official, Oracle will do better.

Here are the results of the original query against the original data set in 12c and 19c:

        ID MEASURE_VAL     LKP_ID    LOW_VAL    LOW_LKP   HIGH_VAL   HIGH_LKP
---------- ----------- ---------- ---------- ---------- ---------- ----------
         6         .05          1                               .1          1
         3         .14          1         .1          1
         1         .24          1         .1          1
         2          .5          1         .1          1
         4         .68          1         .1          1
         5        1.38          1         .1          1

On the upgrade I’ve got the wrong results! So what does the execution plan look like:

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |      1 |        |      6 |00:00:00.01 |      17 |       |       |          |
|   1 |  MERGE JOIN OUTER       |                 |      1 |      6 |      6 |00:00:00.01 |      17 |       |       |          |
|   2 |   SORT JOIN             |                 |      1 |      6 |      6 |00:00:00.01 |      12 |  2048 |  2048 | 2048  (0)|
|   3 |    MERGE JOIN OUTER     |                 |      1 |      6 |      6 |00:00:00.01 |      12 |       |       |          |
|   4 |     SORT JOIN           |                 |      1 |      6 |      6 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS FULL  | MEASURE_TBL     |      1 |      6 |      6 |00:00:00.01 |       7 |       |       |          |
|*  6 |     SORT JOIN           |                 |      6 |      1 |      5 |00:00:00.01 |       5 |  2048 |  2048 | 2048  (0)|
|   7 |      VIEW               | VW_DCL_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|*  8 |       COUNT STOPKEY     |                 |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|   9 |        TABLE ACCESS FULL| NOM_VAL_LKP     |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|* 10 |   SORT JOIN             |                 |      6 |      1 |      1 |00:00:00.01 |       5 |  2048 |  2048 | 2048  (0)|
|  11 |    VIEW                 | VW_DCL_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|* 12 |     COUNT STOPKEY       |                 |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|  13 |      TABLE ACCESS FULL  | NOM_VAL_LKP     |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(INTERNAL_FUNCTION("NOM_VAL")<=INTERNAL_FUNCTION("MT"."MEASURE_VAL"))
       filter(INTERNAL_FUNCTION("NOM_VAL")<=INTERNAL_FUNCTION("MT"."MEASURE_VAL"))
   8 - filter(ROWNUM=1)
  10 - access("NOM_VAL">="MT"."MEASURE_VAL")
       filter("NOM_VAL">="MT"."MEASURE_VAL")
  12 - filter(ROWNUM=1)


Check what’s appeared in the Name for the view operations 7 and 11: VW_DCL_ A18161FF (DCL = “decorrelate”), I was expecting to see names starting with VW_LAT (LAT = “lateral”). And then I remembered reading this article by Sayan Malakshinov – Oracle (12c+) can decorrelate lateral views but gets the wrong results with rownum. So let’s add in a few hints to avoid decorrelation and check the results and execution plan.

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |        |      6 |00:00:00.01 |      30 |       |       |          |
|   1 |  MERGE JOIN OUTER                        |                 |      1 |      6 |      6 |00:00:00.01 |      30 |       |       |          |
|   2 |   MERGE JOIN OUTER                       |                 |      1 |      6 |      6 |00:00:00.01 |      19 |       |       |          |
|   3 |    TABLE ACCESS FULL                     | MEASURE_TBL     |      1 |      6 |      6 |00:00:00.01 |       8 |       |       |          |
|   4 |    BUFFER SORT                           |                 |      6 |      1 |      5 |00:00:00.01 |      11 |  2048 |  2048 | 2048  (0)|
|   5 |     VIEW                                 | VW_LAT_D77DA787 |      6 |      1 |      5 |00:00:00.01 |      11 |       |       |          |
|*  6 |      COUNT STOPKEY                       |                 |      6 |        |      5 |00:00:00.01 |      11 |       |       |          |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| NOM_VAL_LKP     |      6 |      2 |      5 |00:00:00.01 |      11 |       |       |          |
|*  8 |        INDEX RANGE SCAN                  | SYS_C0055681    |      6 |      3 |      5 |00:00:00.01 |       6 |       |       |          |
|   9 |   BUFFER SORT                            |                 |      6 |      1 |      5 |00:00:00.01 |      11 |  2048 |  2048 | 2048  (0)|
|  10 |    VIEW                                  | VW_LAT_D77DA787 |      6 |      1 |      5 |00:00:00.01 |      11 |       |       |          |
|* 11 |     COUNT STOPKEY                        |                 |      6 |        |      5 |00:00:00.01 |      11 |       |       |          |
|  12 |      TABLE ACCESS BY INDEX ROWID BATCHED | NOM_VAL_LKP     |      6 |      2 |      5 |00:00:00.01 |      11 |       |       |          |
|* 13 |       INDEX RANGE SCAN DESCENDING        | SYS_C0055681    |      6 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(ROWNUM=1)
   8 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")
  11 - filter(ROWNUM=1)
  13 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
       filter("NT"."NOM_VAL"<="MT"."MEASURE_VAL")

Blocking decorrelation was sufficient to get the correct result but there’s still a funny little glitch in the execution plan: why do we have merge join (outer) for operations 1 and 2?

It’s not quite the threat you might think; we’re not multiplying up rows catastrophically. For each row in measures_tbl Oracle does a Cartesian merge join to (at most) one row in each view – so there’s no accidental explosion in data volume, and there’s no real sorting. Nevertheless there may be unnecessary CPU usage so let’s add a few more hints to try and get a nested loop by adding the following hints to the start of the query:

        /*+
                qb_name(main)
                leading(@main mt@main nt_high@main nt_low@main)
                use_nl(@main nt_high@main)
                use_nl(@main nt_low@main)
        */

I was a little surprised at the benefit – roughly a 30% saving on CPU for the same data set.

But there’s more to investigate – I didn’t like the index hints that I’d had to use in 11g, but 12c allows for the more complex “two layer” lateral subquery with its deeply correlated predicate – so what happens if I use the following corrected query (with minimal hinting) in 12c or 19c:

select
        /*+
                qb_name(main)
--              leading(@main mt@main nt_high@main nt_low@main)
--              use_nl(@main nt_high@main)
--              use_nl(@main nt_low@main)
        */
        mt.id,
        mt.measure_val,
        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,
        nt_low.nom_val  low_val,
        nt_low.lkp_id   low_lkp,
        nt_high.nom_val high_val,
        nt_high.lkp_id  high_lkp 
from
        measure_tbl     mt,
        lateral(
                select  *
                from    (
                        select  /*+ qb_name(low) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        order by
                                nom_val desc
                        )
                where   rownum = 1
        )(+) nt_low,
        lateral(
                select  *
                from    (
                        select  /*+ qb_name(high) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        order by
                                nom_val
                )
                where   rownum = 1
        )(+) nt_high
/

First – Oracle doesn’t use decorrelation so I get the right results; secondly Oracle uses the correct index descending without hinting, which is an important part of getting the right results. Unfortunately I still see merge joins unless I include the use_nl() hints (with the leading() hint as an extra safety barrier) to get that 30% reduction in CPU usage.

The sad news is that I still don’t see scalar subquery caching. If I have 6,144 rows in measure_tbl I still see 6,144 executions of both the lateral subqueries.

Since 12c onwards supports “outer apply” it’s worth testing to see what happens if I replace my lateral()(+) operator with the outer apply() mechanism. It works, but behaves very much like the lateral approach (including the unexpected merge joins unless hinted), except it introduces another layer of lateral joins. Here’s the plan (12c and 19c) with 6,144 rows:

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |   6144 |00:00:00.14 |   22954 |       |       |          |
|   1 |  MERGE JOIN OUTER                   |                 |      1 |   6144 |   6144 |00:00:00.14 |   22954 |       |       |          |
|   2 |   MERGE JOIN OUTER                  |                 |      1 |   6144 |   6144 |00:00:00.08 |   11690 |       |       |          |
|   3 |    TABLE ACCESS FULL                | MEASURE_TBL     |      1 |   6144 |   6144 |00:00:00.01 |     426 |       |       |          |
|   4 |    BUFFER SORT                      |                 |   6144 |      1 |   5120 |00:00:00.06 |   11264 |  2048 |  2048 | 2048  (0)|
|   5 |     VIEW                            | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|   6 |      VIEW                           | VW_LAT_A18161FF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|*  7 |       COUNT STOPKEY                 |                 |   6144 |        |   5120 |00:00:00.03 |   11264 |       |       |          |
|   8 |        VIEW                         |                 |   6144 |      2 |   5120 |00:00:00.03 |   11264 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP     |   6144 |      6 |   5120 |00:00:00.02 |   11264 |       |       |          |
|* 10 |          INDEX RANGE SCAN DESCENDING| SYS_C0023500    |   6144 |      2 |   5120 |00:00:00.01 |    6144 |       |       |          |
|  11 |   BUFFER SORT                       |                 |   6144 |      1 |   5120 |00:00:00.06 |   11264 |  2048 |  2048 | 2048  (0)|
|  12 |    VIEW                             | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|  13 |     VIEW                            | VW_LAT_E88661A9 |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|* 14 |      COUNT STOPKEY                  |                 |   6144 |        |   5120 |00:00:00.03 |   11264 |       |       |          |
|  15 |       VIEW                          |                 |   6144 |      1 |   5120 |00:00:00.02 |   11264 |       |       |          |
|  16 |        TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP     |   6144 |      1 |   5120 |00:00:00.02 |   11264 |       |       |          |
|* 17 |         INDEX RANGE SCAN            | SYS_C0023500    |   6144 |      4 |   5120 |00:00:00.01 |    6144 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter(ROWNUM=1)
  10 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
  14 - filter(ROWNUM=1)
  17 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")

Note operations 5 and 6, then 12 and 13: the “ANSI” syntax outer apply seems to be another case of Oracle doing more work because it has to transform the query before optimising.

A Traditional Solution

Having worked through a few of the newer mechanisms in Oracle, why not think back to how the same pattern of implementation could have been achieved in older versions of Oracle. What’s wrong, for example, with using scalar subqueries in the select list? If we can expect plenty of scalar subquery caching this might be a very effective way of writing the query.

The immediate problem, though, is that scalar subqueries in the select list only allow one column to be returned (unless you want to fake things through by playing nasty games with user-defined types). So our two lateral views will have to change to four scalar subqueres to get all the data we need.

Here’s a possible solution (I’ve stuck with the hinted shorter, but bad practice, “first row” mechanism for compactness) – with execution stats:

select
        id,
        measure_val,
        case
                when
                        nt_high_nom_val - measure_val <=
                        measure_val - nt_low_nom_val
                then    nvl(nt_high_lkp_id,nt_low_lkp_id)
                else    nvl(nt_low_lkp_id,nt_high_lkp_id)
        end     lkp_id,
        nt_low_nom_val,
        nt_low_lkp_id,
        nt_high_nom_val,
        nt_high_lkp_id
from    (
        select
                mt.id,
                mt.measure_val,
                (
                        select
                                /*+ index_rs_asc(nt (nom_val)) */
                                nt.lkp_id
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        and     rownum = 1
                ) nt_high_lkp_id,
                (
                        select
                                /*+ index_rs_asc(nt (nom_val)) */
                                nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        and     rownum = 1
                ) nt_high_nom_val,
                (
                        select
                                /*+ index_rs_desc(nt (nom_val)) */
                                nt.lkp_id
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        and     rownum = 1
                ) nt_low_lkp_id,
                (
                        select
                                /*+ index_rs_desc(nt (nom_val)) */
                                nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        and     rownum = 1
                ) nt_low_nom_val
        from
                measure_tbl     mt
        )
/

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |   6144 |00:00:00.01 |     426 |
|*  1 |  COUNT STOPKEY                          |              |      6 |        |      5 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN                      | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  3 |   COUNT STOPKEY                         |              |      6 |        |      5 |00:00:00.01 |       6 |
|*  4 |    INDEX RANGE SCAN DESCENDING          | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  5 |    COUNT STOPKEY                        |              |      6 |        |      5 |00:00:00.01 |      11 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED | NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  7 |      INDEX RANGE SCAN                   | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  8 |     COUNT STOPKEY                       |              |      6 |        |      5 |00:00:00.01 |      11 |
|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED| NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|* 10 |       INDEX RANGE SCAN DESCENDING       | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|  11 |  TABLE ACCESS FULL                      | MEASURE_TBL  |      1 |   6144 |   6144 |00:00:00.01 |     426 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - access("NT"."NOM_VAL">=:B1)
   3 - filter(ROWNUM=1)
   4 - access("NT"."NOM_VAL"<=:B1)
       filter("NT"."NOM_VAL"<=:B1)
   5 - filter(ROWNUM=1)
   7 - access("NT"."NOM_VAL">=:B1)
   8 - filter(ROWNUM=1)
  10 - access("NT"."NOM_VAL"<=:B1)
       filter("NT"."NOM_VAL"<=:B1)

I’ve left the index hints in place in this example so that the code can run on 11g and earlier (without setting any special events, of course); but in 12c and 19c if you replace the subqueries with the double-layer subqueries (inline order by, then rownum = 1) as shown further up the page the hints (specifically the descending hints) are no longer necessary.

The key performance benefit of this approach is visible in the Starts column – although I now have 4 subqueries to run (which should mean doing more work) each one runs only once thanks to an extremely “lucky” level of scalar subquery caching.

This, really, is where this note takes us back to the beginning. Will this be a fantastic solution for the end-user, or does the pattern of the data mean that it’s going to be a total disaster. It’s nice to see the SQL that defines the tables and supplies a bit of test data – but there’s not point in trying to provide a solution without a better idea of what the data really looks like and what the critical usage is in production.

Bug time (2)

Nothing’s perfect, of course – and even though this last SQL statement is pretty simple and its execution plan is (for the right data pattern) very efficient, the shape of the plan is wrong – and in more complex plans you could be fooled into thinking that Oracle isn’t doing what you want it do.

Operations 1,3,5,8 and 11 should all be at the same depth (you’ll find that they all have parent_id = 0 if you look at the underlying data in v$sql_plan): there’s a defect in Oracle’s calculation of the depth column of v$sql_plan (et. al.) that introduces a pattern of indentation that shouldn’t be there.

Summary

This has been a fairly informal ramble through the playing around that I did after I read the original post. It holds some comments about the way the question was asked, the test data as supplied and corrected, and the observations and tweaks as the testing progressed.

On the plus size, the OP has supplied code to create and populate a model, and described what they wanted to see as a result. However the requirement didn’t mention (and the model therefore didn’t cater for) a couple of special cases. There were also a few cases where unique and mandatory columns were likely to be appropriate but were not mentioned, even though they could affect the correctness or performance of any suggested solutions.

More importantly, although the model implied some fairly narrow restrictions on what the production data might look like this information wasn’t presented explcitily, and there were no comments about the ultimate scale and distribution patterns of the data that might give some clues about the most appropriate features of SQL to use.

Adding Tracing to Your Distributed Cloud Native Microservices

OTN TechBlog - Mon, 2021-04-05 08:00

When adopting cloud-native technologies and certain architectures such as the microservice pattern, observability and monitoring become a huge need and a high priority for many development teams. On the “monitoring” side, I recently blogged about using Micronaut’s built-in support for Micrometer and the OCI SDK integrations to collect and analyze your server and application-related performance metrics with OCI Monitoring. But what about “observability”? It’s just as important to be able to trace and analyze requests across your distributed services so you can obtain a complete picture and be able to pinpoint bottlenecks and issues before they become a real headache. To that end, I want to talk to you about adding tracing to your Micronaut applications. Just as you’d expect, there is plenty of support for adding tracing to your applications in the Micronaut ecosystem. Is it easy to integrate this support into your OCI environment? Let’s take a look.

Tracing Requests with Micronaut

Micronaut features support for integrating with the two most popular solutions for tracing: Zipkin and Jaeger. To get comfortable with tracing, let’s launch Zipkin locally and create two simple microservices that communicate to see how distributed tracing works.

Launch Zipkin

The quickest and easiest way is to launch a Docker container.

Hit localhost:9411 in your browser to make sure it’s up and running.

Generate & Configure Microservices

Using the Micronaut CLI, generate two services. Include the management and tracing-zipkin features.

Edit src/main/resources/application.yml in demo1 to configure a few variables and point the application at the local Zipkin install.

Configure demo2 to run on port 8081 (to avoid conflict with demo1) and point at the local Zipkin install as well.

Create Controllers

Starting with demo2, create a controller that returns a “favorite number” for a user based on their ID. We use the special annotation @ContinueSpan to indicate that we want to group this endpoint along with whatever request called it in our traces. The @SpanTag annotation on the method parameter lets us pull out specific variables to include in our tracing spans so that we can filter or use them for troubleshooting later on.

Next, in the demo1 service, create a declarative HTTP client that can be used to make calls to demo2 from demo1.

Now we’ll create a controller in demo1 that has a few endpoints for testing. Note that we’re injecting the Demo2Client and making a call to demo2 from demo1 in the /user/{id} endpoint.

We can run each service at this point and make some calls to the various endpoints. Take a look at Zipkin and see how it handles tracing for the microservices. 

Now drill in to one of the /user/{id} calls (by clicking on ‘Show’) to see the spans from demo2 included in the trace.

Click on the ‘Demo2’ span to highlight the row and then click ’Show Annotations’ on the right-hand side to view span details and the user.id that we tagged with the @SpanTag annotation.

We can also use the user.id to query spans.

As you can see, tracing distributed microservices with Micronaut and Zipkin is not difficult. However, it does require that you install, configure, maintain, and secure your own Zipkin install. For larger teams with a strong DevOps presence, this isn’t a problem. But for smaller teams or organizations who don’t have the resources to dedicate to infrastructure management, is there a managed service option? The answer to that question is almost always “yes”, but that answer invariably leads to the next obvious question: “how difficult is it to migrate to the managed option and what will it take to migrate off of it if we ever have to”? Those are fair questions - and as usual with Oracle Cloud Infrastructure, you have an option that is fully compatible with the popular industry standard that can be dropped in with just minor config changes. Let’s look at using Application Performance Monitoring for our tracing endpoint instead of Zipkin.

Using OCI Application Performance Monitoring as a Drop-In Tracing Replacement

OCI Application Performance Monitoring (APM) is a suite of services that give you insight into your applications and servers running in OCI via a small agent that runs on the machine and aggregates and reports metric data. It’s a nice service to monitor and diagnose performance issues. It also includes a Trace Explorer that is Zipkin (and Jaeger) compatible and we can use that Trace Explorer from our Micronaut applications (even without taking full advantage of APM via the Java Agent). Let’s swap out Zipkin for APM Trace Explorer in our microservices.

Create Cloud Configuration

In the demo1 project, create a new file in src/main/resources/ called application-oraclecloud.yml. This file will automatically be used when your application runs in the Oracle Cloud thanks to Micronaut’s environment detection features.

Do the same for demo2.

Create an APM Domain

Now, in the OCI console, create an APM domain. We’ll share a single domain that will be used to group and trace all of our services. I know that may seem a bit confusing given the name ‘domain’, but think of it more like a “project group” or an “environment” (you may want to create separate domains for QA, Test, Prod, etc). Search for ‘Application Performance Monitoring’ and click on ‘Administration’.

In the left sidebar, click on ‘APM Domains’.

Click on ‘Create APM Domain’.

Name it, choose a compartment and enter a description.

Once the domain is created, view the domain details. Here you’ll need to grab a few values, so copy the data upload endpoint (#1), private key (#2), and public key (#3).

Now we have what we need to construct a URL to plug in to our application config files. The ‘Collector URL’ format requires us to construct a URL by using the data upload endpoint as our base URL and generate the path based on some choices including values from our private or public key. The format is documented here. Once we’ve constructed the URL path, we can plug it in to our application-oraclecloud.yml config. Since we use the same domain for both services, the URL and path would be the same for both config files.

If you wanted to keep these values out of the config file, you could alternatively set them as environment variables on the server like so:

And that’s it! Just by creating an APM domain and plugging in our new URL and path our application will start producing tracing data to APM. We can run a few requests and then head to the Trace Explorer in the OCI console to view, search and filter our traces just like we did in Zipkin. 

Choose your APM domain in the top right and the time period that you’d like to view/search.

Choose one of the available pre-configured queries across the top.

View traces and spans:

Click on a trace to view detailed info.

Click on a span inside a trace to view detailed info and tagged values.

Read more about the Trace Explorer in the documentation.

Summary

In this post, we looked at how to use tracing to gain insight into our Micronaut microservices. We first looked at using Zipkin, then we switched to the fully managed OCI Trace Explorer with nothing but a few changes to our configuration.

If you’d like to see the code used in this demo, check out the following GitHub repos.
 

I hope you enjoyed this look at tracing in the cloud. If there is another topic you’d like to see covered here on the developer blog, please drop a comment below!

Image by Free-Photos from Pixabay 

.cke_editable p { border: 1px dashed; min-height: 20px; border-radius: 5px; padding: 5px;} .backgroundOverlay, .delayedPopupWindow { display: none !important } .gist { border-left: none !important;} @media only screen and (max-width:640px){.gist .blob-num{padding: 1px 10px!important;} .gist table{display:table}.gist tr{display:table-row}.gist td{display:table-cell;border:1px solid #ddd}.gist table td:before{position: inherit;top:0;left:0;padding-right:0}} .code-inline { display: inline; margin: 0; padding: 1px 2px; white-space: pre !important; word-wrap: normal; font-size: inherit;} .cke_editable .gist-ph:before { content: 'Gist Content...'; display: inline-block; color: blue; } .intro, .info, .success, .warning, .error { -webkit-box-shadow: 4px 4px 9px -1px rgba(102,102,102,1); -moz-box-shadow: 4px 4px 9px -1px rgba(102,102,102,1); box-shadow: 4px 4px 9px -1px rgba(102,102,102,1); border: 1px solid; border-radius: 2px; margin: 10px 0px; padding:15px 10px;} .info { color: #00529b; background-color: #bde5f8;} .success { color: #4f8a10; background-color: #dff2bf;} .warning { color: #9f6000; background-color: #FEEFB3;} .error { color: #D8000C; background-color: #FFBABA;} .intro { color: #484848; background-color: #f5f5f5; border: 1px solid; } .shadow { -webkit-box-shadow: 4px 4px 9px -1px rgba(102,102,102,1); -moz-box-shadow: 4px 4px 9px -1px rgba(102,102,102,1); box-shadow: 4px 4px 9px -1px rgba(102,102,102,1); }
let postContainer = document.querySelector('.cb11v2-posturltracking'); let minuteCount = 0; if( postContainer ) { minuteCount = Math.round(postContainer.textContent.split(" ").filter((t) => t != "" && t != "\n").length / 265); } if( postContainer && minuteCount && minuteCount > 0 ) document.querySelector('.u03-date').append(` • ${minuteCount} minute read`);

Microsoft Ignite: Book of News - March 2021 (Azure et al.)

Dietrich Schroff - Mon, 2021-04-05 02:46

If you are interested about the new features of Azure, Office 365 and other Microsoft topics, read the Book of New:

https://news.microsoft.com/ignite-march-2021-book-of-news/

 


The table of contents shows the following chapters:


In my opinion chapter 5.4 is one of the most important ones:

https://news.microsoft.com/ignite-march-2021-book-of-news/#a-541-new-security-compliance-and-identity-certifications-and-content-aim-to-close-security-skills-gap

To help address the security skills gap, Microsoft has added four new Security, Compliance and Identity certifications with supporting training and has made several updates to the Microsoft Security Technical Content Library. These certifications and content are intended to help cybersecurity professionals increase their skilling knowledge and keep up with complex cybersecurity threats.

These new certifications with supporting training are tailored to specific roles and needs, regardless of where customers are in their skilling journey:

  • The Microsoft Certified: Security, Compliance, and Identity Fundamentals certification will help individuals get familiar with the fundamentals of security, compliance and identity across cloud-based and related Microsoft services.
  • The Microsoft Certified: Information Protection Administrator Associate certification focuses on planning and implementing controls that meet organizational compliance needs.
  • The Microsoft Certified: Security Operations Analyst Associate certification helps security operational professionals design threat protection and response systems.
  • The Microsoft Certified: Identity and Access Administrator Associate certification helps individuals design, implement and operate an organization’s identity and access management systems by using Azure Active Directory (Azure AD).

In addition, the Microsoft Security Technical Content Library contains new technical content and resources.

 

Pages

Subscribe to Oracle FAQ aggregator