Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

APEX 20 patch

I’ve been debugging an issue within an APEX application and wanted to make sure that my local installation was at the same version as apex.oracle.com. So I logged on to my workspace and ran queries on APEX_RELEASE and APEX_PATCHES and discovered that there was a patch available for 20.1.

That patch is 30990551 and you can download it from the usual spot on My Oracle Support. None of the bugs listed in the “Fixed Bugs” helped me with my issue, because it turned out to be my own code Smile. But here is what is fixed in the patch:



31232686 - WEBSHEET SAML AUTH: ERROR WHEN ALLOW PUBLIC ACCESS
31217638 - PAGE DESIGNER 'GROUP BY' RENDERING PREFERENCE REMOVAL FROM CODE
31243923 - REGRESSION: IG CSV EXPORTS DOES NOT ENQUOTE VALUES CONTAINING LF (CHR(10))
31237894 - INTERACTIVE REPORT AND INTERACTIVE GRID HIGHLIGHT DOESN'T SHOW HIGHLIGHT STYLING IN REPORT SETTINGS
31206093 - DARK MODE: SQL WORKSHOP QUERY BUILDER COLOUR CONSTRAST ISSUES
31174941 - UNREADABLE HOME SUB-MENUS WHEN CHANGING ZOOM ON CHROME
31253570 - MEGA MENUS HAVE INCORRECT RESPONSIVE BEHAVIOR
31256032 - INCORRECT COLORS RENDERED IN DARK MODE IN APEX BUILDER AND UT
31224836 - SESSION TIMEOUT WARNING INCORRECTLY DISPLAYS IN THE BUILDER
31286811 - SAML AUTH: ORA-01460 WHEN PARSING THE AUTHN REQUEST XML DOCUMENT
31281836 - INTERACTIVE GRID: DIALOG ERRORS WHEN PDF IS THE ONLY EXPORT FORMAT

Installation was quick and uneventful – just running a script and copying some fresh image directories. As always, read the installation instructions carefully to make sure you run the correct script for your environment.


SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 13 10:45:45 2020
Version 19.6.0.0.0

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

Last Successful login time: Tue May 12 2020 18:17:47 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

SQL> conn /@db19_pdb1 as sysdba
Connected.

SQL> @catpatch

. ORACLE
.
. Application Express (APEX) 20.1.0.00.13
. Patch Set Exception 30990551
........................................

APEX_VERSION
------------------------------
APEX_SCHEMA
--------------------------------------------------------------------------------------------------------------------------------
20.1.0.00.13
APEX_200100



PL/SQL procedure successfully completed.


Session altered.


APEX_SCHEMA
--------------------------------------------------------------------------------------------------------------------------------
APEX_200100


Session altered.

...wwv_flow_escape

Package body created.

No errors.
...wwv_flow_session

Package body created.

No errors.
...wwv_flow_authentication

Package body created.

No errors.
...wwv_flow_authentication_saml.plb

Package body created.

No errors.

PL/SQL procedure successfully completed.

...devpatch.sql

Session altered.


Grant succeeded.


Session altered.

...wwv_flow_authentication_f4900.plb

Package body created.

No errors.
...wwv_flow_property_dev

Package body created.

No errors.
...patch_31232686.sql

PL/SQL procedure successfully completed.

...patch_31217638.sql

PL/SQL procedure successfully completed.


Commit complete.

...Validating APEX
...(10:46:40) Starting validate_apex for APEX_200100
...(10:46:40) Checking missing sys privileges
...(10:46:40) Re-generating APEX_200100.wwv_flow_db_version
... wwv_flow_db_version is up to date
...(10:46:41) Checking invalid public synonyms
...(10:46:41) Key object existence check
...(10:46:41) Setting DBMS Registry for APEX to valid
...(10:46:41) Exiting validate_apex

PL/SQL procedure successfully completed.

...Recompiling invalid public synonyms

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.

timing for: Complete Patch 30990551
Elapsed: 00:00:03.47

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0

So if you’re running APEX 20, be sure to patch your instance when you get a chance.

Coming Soon: Make Your Site Private Until You’re Ready to Launch

When you create a new site, you may want to personalize it before making it public. On WordPress.com, we give you a safe space where you can work on building and editing your site until you’re ready to share it with the world.

https://en-blog.files.wordpress.com/2020/05/coming-soon-logged-out.png?w... 2048w, https://en-blog.files.wordpress.com/2020/05/coming-soon-logged-out.png?w... 150w, https://en-blog.files.wordpress.com/2020/05/coming-soon-logged-out.png?w... 300w, https://en-blog.files.wordpress.com/2020/05/coming-soon-logged-out.png?w... 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

Until recently, this Coming Soon mode was limited to new sites without plugins or custom themes installed. We realize, however, that many users — regardless of how long they’ve had a website on WordPress.com — might want to make updates, change the design, or add new functionality without making these changes visible to the public until they’re complete. Now, all sites have this Coming Soon option, and you can toggle it on or off as you wish.

To set your site to Coming Soon, go to Manage in the sidebar, select Settings, scroll down to Privacy, and select the Coming Soon button. Be sure to click on Save settings for the change to take effect.

https://en-blog.files.wordpress.com/2020/05/settings-coming-soon-1.png?w... 150w, https://en-blog.files.wordpress.com/2020/05/settings-coming-soon-1.png?w... 300w, https://en-blog.files.wordpress.com/2020/05/settings-coming-soon-1.png?w... 768w, https://en-blog.files.wordpress.com/2020/05/settings-coming-soon-1.png 1440w" sizes="(max-width: 1024px) 100vw, 1024px" />

While in this mode, site visitors will see a landing page with your site’s title. You and other logged-in people that you invite will see the full website.

To invite people to view your site while in Coming Soon mode, add them as new users from the ManagePeopleInvite button. Users will need to at least be a Contributor to view the site in this mode.

https://en-blog.files.wordpress.com/2020/05/invite-people.png?w=2048 2048w, https://en-blog.files.wordpress.com/2020/05/invite-people.png?w=150 150w, https://en-blog.files.wordpress.com/2020/05/invite-people.png?w=300 300w, https://en-blog.files.wordpress.com/2020/05/invite-people.png?w=768 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

To make your site public again, go to Manage in the sidebar, select Settings, scroll down to Privacy, and select the Public button. Again, be sure to click on Save settings.

Private sites for all

If instead of sharing your site with everyone, you want to keep it private, and only available to invited members of the site — for instance, a private blog for your family, made up of photos and videos of your children or pets — you can use the Private option under ManageSettingsPrivacy.

https://en-blog.files.wordpress.com/2020/05/settings-private-1.png?w=150 150w, https://en-blog.files.wordpress.com/2020/05/settings-private-1.png?w=300 300w, https://en-blog.files.wordpress.com/2020/05/settings-private-1.png?w=768 768w, https://en-blog.files.wordpress.com/2020/05/settings-private-1.png 1440w" sizes="(max-width: 1024px) 100vw, 1024px" />

In this mode, instead of the Coming Soon landing page, logged-out visitors will see a more discreet prompt to log in.

https://en-blog.files.wordpress.com/2020/05/private-logged-out.png?w=2048 2048w, https://en-blog.files.wordpress.com/2020/05/private-logged-out.png?w=150 150w, https://en-blog.files.wordpress.com/2020/05/private-logged-out.png?w=300 300w, https://en-blog.files.wordpress.com/2020/05/private-logged-out.png?w=768 768w" sizes="(max-width: 1024px) 100vw, 1024px" />

No matter what you want to do on your site — whether making a few tweaks, refreshing your site design, or building a fully fledged online store — use the Coming Soon feature to keep it private until you’re ready to unveil it to the world.

We hope you enjoy this new feature!

Oracle Linux 8 (OL8) : Podman

https://oracle-base.com/blog/wp-content/uploads/2020/05/king-tut-161521_... 272w" sizes="(max-width: 197px) 85vw, 197px" />

When Oracle Linux 8 (OL8) was released, one of the first things I did was check for the Oracle supplied Docker engine. Nothing.

Not to worry I thought. They are probably waiting for UEK6 to ship before they worry about the Docker engine. I pretty much left it at that. I wasn’t really in much of a rush. To be honest, a new version of Oracle Linux doesn’t really hit my radar until the Oracle database is certified on it. </p />
</p></div>

    	  	<div class=

Expert Advice: Business Fundamentals for Creative Professionals

Are you an artist, photographer, or freelance writer? How about a website designer, master metalsmith, or musician? If you’re in any creative profession and would like to learn more about how to market and sell your services and work online, we’ve created a free webinar just for you.

We’ve partnered with our friends at FreshBooks, the leading invoicing and accounting software for creative entrepreneurs, to offer tips on how to build your online store and automate your sales and accounting, leaving you with more time to focus on your craft.

Date: Wednesday, May 20, 2020
Time: 10:00 a.m. PDT | 11:00 a.m. MDT | 12:00 p.m. CDT | 1:00 p.m. EDT | 17:00 UTC
Cost: Free
Registration link
Who’s invited: Artists, writers, musicians, website and graphic designers, photographers, marketers, and anyone else interested in learning how to sell their creative services online.

Your hosts will be Jonathan Wold, Community Manager at WooCommerce, and Irene Elliott, Senior Community Manager at FreshBooks. Dustin Hartzler, a WooCommerce Happiness Engineer, will moderate questions. After the 45-minute presentation, we’ll open up the (virtual) floor for a 15-minute Q&A session.

Attendee slots are limited, so be sure to register early to save your seat! But if you can’t make it, we’ve got your back. A recording of the webinar will be uploaded to our YouTube channel a few days after the event.

See you then!

Always free / always up tmux in the Oracle Cloud with KSplice updates

By Franck Pachot

.
I used to have many VirtualBox VMs on my laptop. But now, most of my labs are in the Cloud. Easy access from everywhere.

GCP

There’s the Google Cloud free VM which is not limited in time (I still have the 11g XE I’ve created 2 years ago running there) being able to use 40% of CPU with 2GB of RAM:


top - 21:53:10 up 16 min,  4 users,  load average: 9.39, 4.93, 2.09
Tasks:  58 total,   2 running,  56 sleeping,   0 stopped,   0 zombie
%Cpu(s): 12.9 us,  8.2 sy,  0.0 ni, 12.6 id,  0.0 wa,  0.0 hi,  0.3 si, 66.0 st
GiB Mem :    1.949 total,    0.072 free,    0.797 used,    1.080 buff/cache
GiB Swap:    0.750 total,    0.660 free,    0.090 used.    0.855 avail Mem

This is cool, always free but I cannot ssh to it: only accessible with Cloud Shell and it may take a few minutes to start.

AWS

I also use an AWS free tier but this one is limited in time 1 year.


top - 19:56:11 up 2 days, 13:09,  2 users,  load average: 1.00, 1.00, 1.00
Tasks: 110 total,   2 running,  72 sleeping,   0 stopped,   0 zombie
%Cpu(s): 12.4 us,  0.2 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.0 si, 87.4 st
GiB Mem :      1.0 total,      0.1 free,      0.7 used,      0.2 buff/cache
GiB Swap:      0.0 total,      0.0 free,      0.0 used.      0.2 avail Mem

A bit more CPU throtteled by the hypervisor and only 1GB of RAM. However, I can create it with a public IP and access it though SSH. This is interesting especially to run other AWS services as the AWS CLI is installed here in this Amazon Linux. But limited in time and in credits.

OCI

Not limited in time, the Oracle Cloud OCI free tier allows 2 VMs with 1GB RAM and 2 vCPUs throttled to 1/8:


top - 20:01:37 up 54 days,  6:47,  1 user,  load average: 0.91, 0.64, 0.43
Tasks: 113 total,   4 running,  58 sleeping,   0 stopped,   0 zombie
%Cpu(s): 24.8 us,  0.2 sy,  0.0 ni,  0.0 id,  0.0 wa,  0.0 hi,  0.2 si, 74.8 st
KiB Mem :   994500 total,   253108 free,   363664 used,   377728 buff/cache
KiB Swap:  8388604 total,  8336892 free,    51712 used.   396424 avail Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
18666 opc       20   0  112124    748    660 R  25.2  0.1   6:20.18 yes
18927 opc       20   0  112124    700    612 R  24.8  0.1   0:06.53 yes

This is what I definitlely choose as a bastion host for my labs.

tmux

I run mainly one thing here: TMUX. I have windows and panes for all my work and it stays there in this always free VM that never stops. I just ssh to it and ‘tmux attach’ and I’m back to all my terminals opened.

Ksplice

This, always up, with ssh opened to the internet, must be secured. And as I have all my TMUX windows opened, I don’t want to reboot. No problem: I can update the kernel with the latest security patches without reboot. This is Oracle Linux and it includes Ksplice.
My VM is up since February:


[opc@b ~]$ uptime
 20:15:09 up 84 days, 15:29,  1 user,  load average: 0.16, 0.10, 0.03

The kernel is from October:


[opc@b ~]$ uname -r
4.14.35-1902.6.6.el7uek.x86_64

But I’m actually running a newer kernel:


[opc@b ~]$ sudo uptrack-show --available
Available updates:
None

Effective kernel version is 4.14.35-1902.301.1.el7uek
[opc@b ~]$

This kernel is from April. Yes, 2 months more recent than the last reboot. I have simply updated it with uptrack, which downloads and installs the latest Ksplice rebootless kernel updates:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1974w" sizes="(max-width: 1024px) 100vw, 1024px" />

Because there’s no time limit, no credit limit, always up, ready to ssh to it and running the latest security patches without reboot or even quiting my tmux sessions, the Oracle Autonomous Linux is the free tier compute instance I use everyday. You need to open a free trial to get it (https://www.oracle.com/cloud/free/) but it is easy to create a compute instance which is flagged ‘always free’.

Cet article Always free / always up tmux in the Oracle Cloud with KSplice updates est apparu en premier sur Blog dbi services.

20c: AWR now stores explain plan predicates

By Franck Pachot

.
In a previous post https://blog.dbi-services.com/awr-dont-store-explain-plan-predicates/ I explained this limitation in gathering filter and access predicates by Statspack and then AWR because of old bugs about reverse parsing of predicates. Oracle listens to its customers through support (enhancement requests), though the community (votes on database ideas), and through the product managers who participate in User Groups and ACE program. And here it is: in 20c the predicates are collected by AWS and visible with DBMS_XPLAN and AWRSQRPT reports.

I’ll test with a very simple query on the 20c preview available in the Oracle Cloud DBaaS:


set feedback on sql_id echo on pagesize 1000

SQL> select * from dual where ascii(dummy)=42;

no rows selected

SQL_ID: g4gx2zqbkjwh1

I used the “FEEDBACK ON SQL” feature to get the SQL_ID.

Because this query is fast, it will not be gathered by AWR except if I ‘color’ it:


SQL> exec dbms_workload_repository.add_colored_sql('g4gx2zqbkjwh1');

PL/SQL procedure successfully completed.

Coloring a statement is the AWR feature to use when you want to get a statement always gathered, for example when you have optimized it and want compare the statistics.

Now running the statement between two snapshots:


SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from dual where ascii(dummy)=42;

no rows selected

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

Here, I’m sure it has been gathered.

Now checking the execution plan:


SQL> select * from dbms_xplan.display_awr('g4gx2zqbkjwh1');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g4gx2zqbkjwh1
--------------------
select * from dual where ascii(dummy)=42

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ASCII("DUMMY")=42)


18 rows selected.

Here I have the predicate. This is a silly example but the predicate information is very important when looking at a large execution plan trying to understand the cardinality estimation or the reason why an index is not used.

Of course, this is also visible from the ?/rdbms/admin/awrsqrpt report:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1957w" sizes="(max-width: 1024px) 100vw, 1024px" />

What if you upgrade?

AWR gathers the SQL Plan only when it is not already there. Then, when we will update to 20c only the new plans will get the predicates. Here is an example where I simulate the pre-20c behaviour with “_cursor_plan_unparse_enabled”=false:


SQL> alter session set "_cursor_plan_unparse_enabled"=false;

Session altered.

SQL> exec dbms_workload_repository.add_colored_sql('g4gx2zqbkjwh1');

PL/SQL procedure successfully completed.

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from dual where ascii(dummy)=42;

no rows selected

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from dbms_xplan.display_awr('g4gx2zqbkjwh1');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g4gx2zqbkjwh1
--------------------
select * from dual where ascii(dummy)=42

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

No predicate here. Even If I re-connect to reset the “_cursor_plan_unparse_enabled”:


SQL> connect / as sysdba
Connected.
SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from dual where ascii(dummy)=42;

no rows selected

SQL> exec dbms_workload_repository.create_snapshot;

PL/SQL procedure successfully completed.

SQL> select * from dbms_xplan.display_awr('g4gx2zqbkjwh1');

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID g4gx2zqbkjwh1
--------------------
select * from dual where ascii(dummy)=42

Plan hash value: 272002086

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|   1 |  TABLE ACCESS FULL| DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

13 rows selected.

This will be the situation after upgrade.

If you want to re-gather all sql_plans, you need to purge the AWR repository:


SQL> execute dbms_workload_repository.drop_snapshot_range(1,1e22);

PL/SQL procedure successfully completed.

SQL> execute dbms_workload_repository.purge_sql_details();

PL/SQL procedure successfully completed.

SQL> commit;

This clears everything, so I do not recommend to do that at the same time as the upgrade as you may like to compare some performance with the past. Anyway, we have time and maybe this fix will be backported in 19c.

There are very small chances that fix is ported to Statspack, but you can do it yourself as I mentioned in http://viewer.zmags.com/publication/dd9ed62b#/dd9ed62b/36 (“on Improving Statspack Experience”) with something like:


sed -i -e 's/ 0 -- should be//' -e 's/[(]2254299[)]/--&/' $ORACLE_HOME/rdbms/admin/spcpkg.sql

Cet article 20c: AWR now stores explain plan predicates est apparu en premier sur Blog dbi services.

Oracle Linux 8 (OL8) : Vagrant and Docker builds for 19c Database

https://oracle-base.com/blog/wp-content/uploads/2020/05/penguin-161356_6... 300w" sizes="(max-width: 230px) 85vw, 230px" />

A couple of days ago I mentioned the certification of Oracle database 19c on Oracle Linux 8 (OL8) with UEK6.

I’ve had a bunch of OL8 articles and builds for a while, but up until now they’ve included warnings to say they weren’t certified. Over the last couple of evenings I’ve made some changes, so I thought I would summarise then here.

Database 19c on OL8 Article

My article on installing 19c on OL8 (from July last year) now includes the new preinstall package. It also mentions that the installation will work without patches, but it will not be supported unless you include the 19.7 patches, and are using UEK6.

Oracle Database 19c Installation On Oracle Linux 8 (OL8)

Vagrant Build

I’ve had a vagrant build of 19c on OL8 since last year. This has been amended to use the new preinstall package, and to optionally include the 19.7 patches if you’ve downloaded them. By default the patch script is commented out, so folks without a support contract can still use the build. This isn’t meant to be a “supported build”, so I’m not personally bothered about the patches for it, but it seemed a little wrong to not include them, even if it is lip-service.

https://github.com/oraclebase/vagrant/tree/master/database/ol8_19

The base box is ‘bento/oracle-8’, which hasn’t been updated to 8.2 and UEK6 yet. Once again, this doesn’t phase me. The ‘bento/oracle-8’ image tracks the latest release (8.0, 8.1, 8.2 etc.), so at some point it will updated to the latest spin and UEK6.

My go-to Vagrant build has typically been the “ol7_19” build. I’m now going to switch to the “ol7_19” build.

I’ve also added 19c Data Guard build on OL8.

https://github.com/oraclebase/vagrant/tree/master/dataguard/ol8_19

Container (Docker/Podman) Build

Similar to the Vagrant build, I’ve updated by Docker build. It also uses the new preinstall package and includes and optional patch script. I’ve also switched back from the “oraclelinux:8” image to the “oraclelinux:8-slim” base image, which means I had to make some changes, like using “microdnf” instead of “dnf”. Similar to the vagrant build, I’ve left the patch script commented out by default, because I only use this build for playing and demos.

https://github.com/oraclebase/dockerfiles/tree/master/database/ol8_19

My go-to container combination was “ol7_19” plus “ol7_ords”. I’m now going to switch to “ol8_19” plus “ol8_ords” for running APEX 20.1 using containers.

What’s Next?

I’ve got a few things in the pipeline.

  • RAC on OL8 Vagrant Build. I’m unsure at the moment if I will include the patching for this, as it makes it more complicated. I might just stick with the base release. It’s definitely not production, so I’m not sure how much I care about making the build slower and more complicated.
  • I’ve got some Podman stuff I want to talk about that relates to both Vagrant and Docker, but that is better served in a separate post.

Cheers

Tim…

PS. I don’t feel I should need to say this, but experience tells me I should. This stuff on GitHub is just a playground for me. There is no error checking. It’s purposely kept simple so people can read it easily. It’s not production ready. I don’t support it. It’s not scripture. If you find value in it, that’s great. If you don’t, don’t use it. </p />
</p></div>

    	  	<div class=

APEX upgrade – don’t forget your CDN

I haven’t dug too deep into this one, so what follows may be just coincidental but I thought I would blog it out just in case it happens to anyone else.

While testing AskTOM as part of our Application Express upgrade to version 20.1, I got a bit of a shock when I fired up AskTOM (in a testing environment) after the upgrade. The screen was … well…. “slim”. Smile

image

 

Everything under the banner tabs was missing! A quick look at the underlying page source revealed that all of the usual AskTOM content was indeed there, but it was just not being displayed on screen. I tried the usual things – clearing cookies and caches, different browsers etc, all to no avail. The moment I signed in to AskTOM, the main part of the screen disappeared.

Whilst looking in the browser console area I noticed that my static CDN references were still pointing to the APEX 19 version location. I didn’t think this was the issue, but figured it was something that I should be updating anyway. You can find the static CDN references under User Interface in your application properties.

image

I updated the reference to https://static.oracle.com/cdn/apex/20.1.0.00.13/ and voila! My AskTOM screens sprang back to life.

image

Was this definitely the root cause of the problem? I’m not sure. There is more investigating for me to do there. But if you encounter a similar issue during your upgrade testing, make sure you double check that you are using the correct version of your static CDN files.

Note that you can also set your CDN at instance level so that you don’t need to update each application.

Enjoy APEX 20 !

from$_subquery$_NNN

This is a reference note for a question that came up as a comment on a lengthy note I wrote about reading execution plans.

How do you interpret something like: from$_subquery$_001@SEL$1 in the Query Block Name / Object Alias section of an execution plan.

The simple answer is that if you’ve got an inline view in the FROM clause of a query and you haven’t given the inline view an alias the optimizer will have to invent one – and this is what they look like.

As a quick demo here’s a script to create a couple of tables and then run a query that joins two inline views (using “ANSI”-style SQL), with variations on which of the inline views are named:


rem
rem     Script:         from_subquery.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2020
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem

create table t1
as
select  * 
from    all_objects
where   rownum <= 100
;

create table t2
as
select  *
from    all_objects
where   rownum <= 100
;

set serveroutput off

prompt  =========================
prompt  Neither inline view named
prompt  =========================

select 
        count(*)
from    (select /*+ no_merge */ * from t1)
join 
        (select /*+ no_merge */ * from t2)
using
        (object_id)
;

select * from table(dbms_xplan.display_cursor(null,null,'alias'));

prompt  ============================
prompt  Only first inline view named
prompt  ============================

select 
        count(*)
from    (select /*+ no_merge */ * from t1) v1
join 
        (select /*+ no_merge */ * from t2)
using
        (object_id)
;

select * from table(dbms_xplan.display_cursor(null,null,'alias'));

prompt  =============================
prompt  Only second inline view named
prompt  =============================

select 
        count(*)
from    (select /*+ no_merge */ * from t1)
join 
        (select /*+ no_merge */ * from t2) v2
using
        (object_id)
;

select * from table(dbms_xplan.display_cursor(null,null,'alias'));

prompt  =======================
prompt  Both inline views named
prompt  =======================

select 
        count(*)
from    (select /*+ no_merge */ * from t1) v1
join 
        (select /*+ no_merge */ * from t2) v2
using
        (object_id)
;

select * from table(dbms_xplan.display_cursor(null,null,'alias'));

In all four examples I’ve added the /*+ no_merge */ hint to the inline views; if I hadn’t done that the optimizer would simply have reduced each query to a single query block joining two tables. As it is here are the resulting execution plans (with a little cosmetic editing) reporting the plan hash value, plan, outline and predicates:


=========================
Neither inline view named
=========================

Plan hash value: 1978226902

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / from$_subquery$_001@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / from$_subquery$_003@SEL$1
   6 - SEL$3        / T2@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("from$_subquery$_001"."OBJECT_ID"="from$_subquery$_003"."O
              BJECT_ID")


============================
Only first inline view named
============================

Plan hash value: 1978226902

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / V1@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / from$_subquery$_003@SEL$1
   6 - SEL$3        / T2@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."OBJECT_ID"="from$_subquery$_003"."OBJECT_ID")


=============================
Only second inline view named
=============================

Plan hash value: 1978226902

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / from$_subquery$_001@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / V2@SEL$1
   6 - SEL$3        / T2@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("from$_subquery$_001"."OBJECT_ID"="V2"."OBJECT_ID")


=======================
Both inline views named
=======================

Plan hash value: 1978226902

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |       |       |     4 (100)|          |
|   1 |  SORT AGGREGATE      |      |     1 |    26 |            |          |
|*  2 |   HASH JOIN          |      |   100 |  2600 |     4   (0)| 00:00:01 |
|   3 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |   100 |   500 |     2   (0)| 00:00:01 |
|   5 |    VIEW              |      |   100 |  1300 |     2   (0)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   |   100 |   500 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$16C51A37
   3 - SEL$2        / V1@SEL$1
   4 - SEL$2        / T1@SEL$2
   5 - SEL$3        / V2@SEL$1
   6 - SEL$3        / T2@SEL$3

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V1"."OBJECT_ID"="V2"."OBJECT_ID")

As you scan down the plans you can see that they are all the same – with only a change in naming where V1 is synonymous with from$_subquery$_001 and v2 is synonymous with from$_subquery$_003.

Don’t ask me to explain how the optimizer chooses the names – I had thought I might see a from$_subquery$_002 somewhere in the optimizer trace file, but apart from the two aliases I’ve shown here the only other alias I got was one occurrence of from$_subquery$_005@sel$4.

 

Arbitrary length addition and subtraction

This one just for fun today. An AskTOM question came in about arbitrary length arithmetic because “NUMBER(38) was not enough”. After some back-and-forth discussions it turned out that the business need under the requirement was managing bit strings. The implementation was currently converting the bits to decimals, hence the need for potentially very large number handling.

The problem was ultimately tackled with using RAW datatypes and holding the bits as raw strings, but I thought it would be interesting to throw together an addition and subtraction facility where the boundaries could exceed NUMBER(38).

So using nested tables, I had some fun with the code below.


SQL> set serverout on
SQL> declare
  2    type integer_array is  table of number;
  3    n1 integer_array :=
  4      integer_array(
  5        4,3,5,6,7,8,2,3,5,3,5,3,2,5,4,6,7,6,2,1,5,2,3,5,7,3,6,3,1,7,8,5,
  6        2,3,4,5,2,3,4,5,2,3,5,6,8,7,3,9,4,8,5,7,3,9,8,4,7,5,9,3,8,4,7,5,
  7        9,3,8,7,4,5,9,8,3,7,4,5
  8      );
  9    n2sign int := -1;
 10    n2 integer_array :=
 11      integer_array(
 12        0,0,0,0,0,0,3,4,5,2,3,4,5,2,4,3,7,6,8,5,6,7,5,6,7,6,7,8,6,7,8,5,
 13        4,5,6,3,4,5,7,4,5,6,7,4,5,6,7,5,8,5,6,7,8,5,6,7,9,8,9,3,8,4,7,5,
 14        3,4,6,4,5,4,6,5,7,7,4,5
 15      );
 16
 17    res integer_array := integer_array();
 18
 19    procedure add(a1 integer_array, a2 integer_array, r in out integer_array) is
 20      carry pls_integer := 0;
 21      tmp pls_integer;
 22    begin
 23      for i in reverse 1 .. a1.count
 24      loop
 25          tmp :=  a1(i)+a2(i)+carry;
 26          if tmp > 9 then
 27             carry := 1;
 28             tmp := tmp-10;
 29          else
 30             carry := 0;
 31          end if;
 32        r(i) := tmp;
 33      end loop;
 34    end;
 35
 36    procedure sub(s1 integer_array, s2 integer_array, r in out integer_array) is
 37      carry pls_integer := 0;
 38      tmp pls_integer;
 39    begin
 40      for i in reverse 1 .. s1.count
 41      loop
 42        tmp :=  S1(i)-S2(i)+carry;
 43        if tmp 



Definitely not complete implementations, but since addition and subtraction are things we learn in school, in the great tradition of school teachers around the world, I’ll close off this blog post with: “The rest of the implementation is left as an exercise” Smile