Montag, 12. September 2022

indirect partition pruning

The nice and important thing to do when going to a conference is - beside consuming all the great presentations - talk to other attendees and exchange experiences, current issues and probable solutions. 
I am very happy I attended POUG2022! This conference is one of the best in my opinion! 


During one of the discussions I claimed Oracle can - under specific circumstances - do a partition pruning, even when the partition key isn't somewhere defined as a filter. 

It's probably easiest to show it by a simple example.

First some data to play with
CREATE TABLE RAW_DATA
   (	"ID"      NUMBER NOT NULL ENABLE, 
       	"SNAP_ID" NUMBER NOT NULL ENABLE, 
	"EVENT_TIME"  DATE   NOT NULL ENABLE, 
	"PAD1"        VARCHAR2(4000 BYTE)
   );
   
insert into RAW_DATA
select rownum
     , 1+trunc(rownum/99)
     , sysdate - (rownum/99)
     , rpad( to_char(rownum), 500, '*')
from dual
connect by level <= 10000;
commit;

CREATE TABLE SNAP 
   (	"SNAP_ID"       NUMBER NOT NULL ENABLE, 
	"BEGIN_INTERVAL_TIME" DATE NOT NULL ENABLE, 
	"END_INTERVAL_TIME"   DATE NOT NULL ENABLE, 
	 CONSTRAINT "SNAP__PK" PRIMARY KEY ("SNAP_ID") ENABLE, 
	 CONSTRAINT "SNAP__BEGIN_U" UNIQUE ("BEGIN_INTERVAL_TIME") ENABLE, 
	 CONSTRAINT "SNAP__END_U" UNIQUE ("END_INTERVAL_TIME") ENABLE, 
	 CONSTRAINT "SNAP__TIME_ASC" CHECK (begin_interval_time < end_interval_time) ENABLE --> -- for HTML parser
   )
; 

INSERT INTO SNAP
SELECT SNAP_ID, min(event_time), max(event_time) 
from RAW_DATA
group by SNAP_ID;

commit; -- 102 rows inserted

CREATE TABLE PART
   (	"ID"     NUMBER NOT NULL ENABLE, 
	"SNAP_ID"    NUMBER NOT NULL ENABLE, 
	"EVENT_TIME" DATE   NOT NULL ENABLE, 
	"PAD1" VARCHAR2(4000 BYTE) NOT NULL ENABLE, 
	 CONSTRAINT "PART_PK" PRIMARY KEY ("ID")  ENABLE
   ) 
  PARTITION BY LIST ("SNAP_ID") AUTOMATIC 
 (PARTITION "P1"  VALUES (1) SEGMENT CREATION DEFERRED )
 ;
 
select Partition_Name from user_tab_partitions where table_name='PART';

PARTITION_NAME                                                                                                                  
-------------- 
P1
SYS_P529
SYS_P530
...
SYS_P627
SYS_P628
SYS_P629

102 rows selected. 

The partitions are not that big, the data quite boring, but it's sufficient data to play around. 

Now I want to query a row from PART for a specific time-range. as the field EVENT_TIME is not the partition key, the simple query goes like this:


-- SQL1 
SELECT /*+ gather_plan_statistics */ p.* 
from PART p 
WHERE EVENT_TIME between sysdate-(1+1/98) and sysdate-1
;


Plan hash value: 2974113857

------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Pstart| Pstop | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |       |       |      1 |00:00:00.01 |    7007 |
|*  1 |  FILTER             |      |      1 |        |       |       |      1 |00:00:00.01 |    7007 |
|   2 |   PARTITION LIST ALL|      |      1 |      1 |     1 |   102 |      1 |00:00:00.01 |    7007 |
|*  3 |    TABLE ACCESS FULL| PART |    102 |      1 |     1 |   102 |      1 |00:00:00.01 |    7007 |
------------------------------------------------------------------------------------------------------


In this simple query all partitions are scanned (Pstart => 1 to Pstop => 102). 
Let's see if we can use SNAP to make the query more efficient: 

The simplest query makes it even more expensive: 


-- SQL2
select p.* 
from snap sn, part p
where sn.snap_id = p.snap_id
  and p.event_time between sysdate-(1+1/98) and sysdate-1 ;

Plan hash value: 2574539144

------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name     | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |          |       |       | 27817 (100)|          |       |       |
|*  1 |  FILTER                   |          |       |       |            |          |       |       |
|   2 |   NESTED LOOPS            |          |     1 |  2050 | 27817   (1)| 00:00:02 |       |       |
|   3 |    INDEX FAST FULL SCAN   | SNAP__PK |   102 |  1326 |     2   (0)| 00:00:01 |       |       |
|   4 |    PARTITION LIST ITERATOR|          |     1 |  2037 |   273   (1)| 00:00:01 |   KEY |   KEY |
|*  5 |     TABLE ACCESS FULL     | PART     |     1 |  2037 |   273   (1)| 00:00:01 |   KEY |   KEY |
------------------------------------------------------------------------------------------------------


But we are missing something:

alter table PART
  add constraint PART__SNAP_FK foreign key("SNAP_ID") references "SNAP"("SNAP_ID")
;

-- rerun SQL2 

Plan hash value: 2974113857

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        | 27818 (100)|       |       |      1 |00:00:00.01 |    7007 |
|*  1 |  FILTER             |      |      1 |        |            |       |       |      1 |00:00:00.01 |    7007 |
|   2 |   PARTITION LIST ALL|      |      1 |      1 | 27818   (1)|     1 |   102 |      1 |00:00:00.01 |    7007 |
|*  3 |    TABLE ACCESS FULL| PART |    102 |      1 | 27818   (1)|     1 |   102 |      1 |00:00:00.01 |    7007 |
-------------------------------------------------------------------------------------------------------------------


Now we are back at the original plan - but no progress is made.
Next try: provide more information about the correlation between SNAP_IDs and dates in PART and SNAP:

select /*+ gather_plan_statistics */ p.* 
from snap sn, part p
where sn.snap_id = p.snap_id
  and p.event_time between sysdate-(1+1/98) and sysdate-1 
  and p.event_time between sn.begin_interval_time and sn.end_interval_time
;


Plan hash value: 3865632887

---------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name     | Starts | E-Rows | Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |          |      1 |        | 27819 (100)|       |       |      1 |00:00:00.01 |      74 |
|*  1 |  FILTER                       |          |      1 |        |            |       |       |      1 |00:00:00.01 |      74 |
|   2 |   NESTED LOOPS                |          |      1 |      1 | 27819   (1)|       |       |      1 |00:00:00.01 |      74 |
|   3 |    NESTED LOOPS               |          |      1 |      1 | 27819   (1)|       |       |      1 |00:00:00.01 |      73 |
|   4 |     PARTITION LIST SUBQUERY   |          |      1 |      1 | 27818   (1)|KEY(SQ)|KEY(SQ)|      1 |00:00:00.01 |      72 |
|*  5 |      TABLE ACCESS FULL        | PART     |      1 |      1 | 27818   (1)|KEY(SQ)|KEY(SQ)|      1 |00:00:00.01 |      70 |
|*  6 |     INDEX UNIQUE SCAN         | SNAP__PK |      1 |      1 |     0   (0)|       |       |      1 |00:00:00.01 |       1 |
|*  7 |    TABLE ACCESS BY INDEX ROWID| SNAP     |      1 |      1 |     1   (0)|       |       |      1 |00:00:00.01 |       1 |
---------------------------------------------------------------------------------------------------------------------------------


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

   1 - filter(SYSDATE@!-1>=SYSDATE@!-1.01020408163265306122448979591836734694)
   5 - filter(("P"."EVENT_TIME">=SYSDATE@!-1.01020408163265306122448979591836734694 AND
              "P"."EVENT_TIME"<=SYSDATE@!-1))
   6 - access("SN"."SNAP_ID"="P"."SNAP_ID")
   7 - filter(("P"."EVENT_TIME">="SN"."BEGIN_INTERVAL_TIME" AND "P"."EVENT_TIME"<="SN"."END_INTERVAL_TIME"
              AND "SN"."END_INTERVAL_TIME">=SYSDATE@!-1.01020408163265306122448979591836734694 AND
              "SN"."BEGIN_INTERVAL_TIME"<=SYSDATE@!-1))
-- >

D'OH! Now the costs increased - by the additional effort shows less Buffers! And also Pstart and Pstop show thePartitions begin and end at KEY(SQ) (no 1 .. 102 anymore). 

That's what I tried to achieve: indirect partition pruning based on a key stored in another table. 

Unfortunately the nasty 
and p.event_time between sn.begin_interval_time and sn.end_interval_time
is required as it can't be defined as an ASSERTION - even it is defined in SQL-92 already which is 30 years old. 
If you would like to see ASSERTIONs in Oracle, please upvote this Idea!

Freitag, 15. April 2022

qgis with Oracle Cloud Database

qgis is A Free and Open Source Geographic Information System. It can use several spatial backends, and Oracle is just one of them. From what I see it is quite often used with other databases like PostgreSQL or even SpatiaLite. And even Oracle is quite well supported, there sem to be some loose ends, especially for complex situations.

One of these complex situations is the connection to an Always Free Autonomous Database. Oracle tries to secure the database access, so by default it does not allow a simple connection with host, port, service_name and username/password. Instead a wallet is provided to secure the network access before providing the credentials. 

The zip contains several files which can be used for OCI or JDBC connections. In it's tnsnames.ora the aliases are slightly more complex than normally expected:


db1234_medium = 
  (description= 
    (retry_count=20)(retry_delay=3)
    (address=
      (protocol=tcps)
      (port=1522)
      (host=adb.eu-frankfurt-1.oraclecloud.com)
    )
    (connect_data=
      (service_name=pxyz_db1234_medium.adb.oraclecloud.com)
    )
    (security=
      (ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US")
    )
  )
On the other hand, qgis (3.24.1) provides these input fields:
Name, Database, Host and Port define the connection endpoint. But there is no field available for additional details like protocol=tcps or ssl_server_cert_dn=.... Also the specific wallets location can not be provided. 

But it is possible to add all these details. To understand the method, it's required to understand how qgis simply concatenates the given strings to one connection URL which is then handed over to the Oracle client libraries. This client also accepts an Easy Connect String.  
With this information, I managed to generate these values: 
Database pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin
Hosttcps://adb.eu-frankfurt-1.oraclecloud.com
Port1522

Here are 3 important details: in the Database field, you can add information about the certificates DN and the wallet location. In the Host field, the prefix tcps:// defines the specific protocol used. 
This alltogether generates an URL
tcps://adb.eu-frankfurt-1.oraclecloud.com:1522/pxyz_db1234_medium.adb.oraclecloud.com?ssl_server_cert_dn="CN=adwc.eucom-central-1.oraclecloud.com, OU=Oracle BMCS FRANKFURT, O=Oracle Corporation, L=Redwood City, ST=California, C=US"&wallet_location=C:\tns_admin
which is sufficient to connect to my ATP Database 

Dienstag, 22. Februar 2022

sqltrace - stitch together one EXPERIENCE ID from different tracefiles

 

Today I stumbled across an interesting question:
(slightly edited)

How can I cut one specific EXPERIENCE ID from several SQL-tracefiles and merge it together in one (artificial) tracefile for further processing.

For those who are familiar with SQL-Trace files, the term EXPERIENCE ID might be dubious. That's for a good reason: The EXPERIENCE ID is developed by Cary Millsap and Jeff Holt to identify one specific experience. It can be implemented by several ways - here an example from The Method R Guide to Mastering Oracle Trace Data:


begin 
    dbms_session.session_trace_enable(true, false, 'first_execution');
	
    dbms_log.ksdwrt(1, '*** EXPERIENCE ID:('||sys_guid()||')'); 
    dbms_session.set_identifier(sys_context('userenv','session_user')); 
    dbms_application_info.set_module('OE', 'BOOK');
end;
/

select 'your application goes here' from dual
/
begin
    dbms_application_info.set_module('', '');
    dbms_session.set_identifier('');
    dbms_log.ksdwrt(1, '*** EXPERIENCE ID:()'); dbms_session.session_trace_disable();
end;
/

You can imagine, all the tools in the Method R Workbench can deal with this artificial trace entry. 

Now the initial request is still somehow special: Normally one EXPERIENCE ID will be visible only in one tracefile. But in this special implementation, one experience can be spread over several tracefiles due to the applications special handling of its connection pool.

There is no simple tool to grab all the different pieces and glue them together: 

  • oracles trcsess does not know about the EXPERIENCE ID, so it is of no help here to identify ine experience. 
  • Method-Rs mrcrop can create single files for each EXPERIENCE ID, but it doesn't glue them together as required. 
But both can be combined in a quite nice way: 
mrcrop can filter for a specific experience with the parameters experience ‑‑id=$experience
and trcsess can stitch together several tracefiles, when they have one of its filter-criteria in common. 
This leads to a simple pseudo-code which does the trick :

for each experience ID:
  {purge previous mrcrop directories}
  for each XE_ora_*.trc
    mrcrop experience --id=$experience XE_ora_file.trc
  trcsess output=$experience.trc service=xepdb1 XE_ora_*/XE_ora*.trc 

By this we have one tracefile for each experience - goal achieved.

Samstag, 15. Januar 2022

eBPF on ExaCC

 

Recently I had to answer for a customer a quite simple question: 
Which processes suffer from having their memory in swap?
Basically having some memory pages swapped out and the physical memory used for soemething else is not a problem at all. Only if and when these pages are required by the program, it has to wait. This translates the question into a question easier to answer: 

Which processes need to wait for memory pages read from swap (every second).

The processes does not know if they are waiting for a memory page to be read in. It's not a syscall they do. The process only waits. So tools like strace does not provide any information here. 

Luckily there is a quite clever engine for instrumentation (and other useful extension like networking, security, ...) vailable in the modern linux kernel, called eBPF. Brendan Gregg wrote a suite of linux tracing tools. There you can find a tool called swapin (manpage, examples). 

Unfortunately, Oracle doesn't seem to think, performance tools are of any use in Exadata Cloud at Customers (ExaCC) and so bcc/eBPF isn't part of their installation image. Luckily at the end it's a simple Oracle Linux, and in the public repositories the rpms are available. 
I just had to install 
the packages
llvm-private 
python-netaddr 
bcc  python-bcc  bcc-tools 

the packages 
libdtrace-ctf 
kernel-uek-devel matching  uname -r

Unfortunately I didn't find swapin script in /usr/share/bcc/tools, so I had to create it there. 

But now, after these steps, we can answer if having memory in swap on a system is bad at all and which processes are affected. 
(of course, after all these preparations, the symptoms were gone and there is nothing to observe. But now everything is prepared!) 

Samstag, 25. Dezember 2021

under DMUs cover

 Oracle Data Migration Assistant for Unicode  (DMU) is a nice tool to convert databases (but no CDB$ROOT, sorry) from any character set to unicode. 

It is a nice java tool which is described already, So I will focus more on the steps what's going on. 

DMU uses an internal package DBMS_DUMA_INTERNAL which just creates an interface to DUMA_LIB library. 

Beside that, in my simple testcase DMU used the undocumented function SYS_OP_CSCONV(<column>, <target characterset> [, <assumend source characterset>] ). Even it is not part of the SQL documentation, it can be found in the DMU documentation

To enambe DMU doing all it's magic, some events are set. In my case they were: 

ALTER SESSION SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1, FOREVER'
ALTER SESSION SET EVENTS '14533 TRACE NAME CONTEXT LEVEL 1, FOREVER'
ALTER SESSION SET EVENTS '14535 TRACE NAME CONTEXT LEVEL 1, FOREVER'
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT LEVEL 1, FOREVER'

and at the end, another undocumented command is executed: 

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8

The lastest (21) docu tells: 
CHARACTER SET, NATIONAL CHARACTER SET

You can no longer change the database character set or the national character set using the ALTER DATABASE statement. Refer to Oracle Database Globalization Support Guide for information on database character set migration.

Of course using any of these statements is totally unsupported (even others already used some parts to fix existing problems) it's woth to know SYS_OP_CSCONV() can be used in any case where "wrong" characters were inserted and need to be fixed. 

One last bit I stumbled across: it seems even DMU can not manipulate AUD$INIFIED:




There is one even less documented function: SYS_OP_CSCONVTEST. It also has 3 parameters, I just assume these are equal to those of SYS_OP_CSCONV
It returns a number, in my tests there were 3 possible results:
1needs conversion
0no conversion needed
-1no conversion possible

Let's hope this is of any help.

Freitag, 20. August 2021

ASM lsof with process ID

 Oracle ASM can list files which belong to a (current active) instance. 


The command is lsof. Unfortunately it does not provide the process-ID which holds the file open. lsof gets the data from internal view X$KFFOF, which contains additional information.

Based on all I found (which is close to nothing) I can provide this SQL - might it help, to find the holder of an open AMS file form now on.

SELECT lsof.GROUP_KFFOF        as GROUP_NUMBER
     , lsof.NUMBER_KFFOF       as FILE_NUMBER
     , lsof.INSTANCENAME_KFFOF as INSTANCE_NAME
     , lsof.DBNAME_KFFOF       as DB_NAME
     , lsof.CLUSTERNAME_KFFOF  as CLUSTER_NAME
     , lsof.OSID_KFFOF         as OS_PID
     , lsof.PATH_KFFOF         as PATH
     , lsof.FLAGS_KFFOF        as FLAGS 
     , lsof.TIMESTAMP_KFFOF    as TIMESTAMP
FROM X$KFFOF lsof
WHERE 1=1
  AND bitand(flags_kffof, to_number('00004000', 'XXXXXXXX')) = 0  -- kffilf_invalid = 0x00004000
;   

Sonntag, 2. Mai 2021

tracing network ACLs

If there are problems with ACLs (Access Control Lists) in Oracle, the note SRDC - How to Collect Standard Information for Access Control Lists (ACLs) (Doc ID 1905572.1) is an important starting point.
Basically it suggests to enable these events.


alter session set events '10937 trace name context forever, level 7'; 
alter session set events '24247 trace name errorstack level 3';
But the only suggestion is to open a SR at oracle and send the results in. Sometimes, it already helps to understand parts of the trace file to identify an issue - no need to invest precious time on MOS. First the events itself. Their meaning are:

10937, 00000, "trace name context forever"
// * Cause:
// * Action: When set, this event enables tracing of PL/SQL packages.
// * Comment: Setting this event could result in some performance slowdown.
and

24247, 00000, "network access denied by access control list (ACL)"
// *Cause:    No access control list (ACL) has been assigned to the target
//            host or the privilege necessary to access the target host has not
//            been granted to the user in the access control list.
// *Action:   Ensure that an access control list (ACL) has been assigned to
//            the target host and the privilege necessary to access the target
//            host has been granted to the user.
Unfortunately I did not find a comprehensive documentation of these events levels, maybe this is area for some other posts.

A first try with a newly created user (grants: connect, resource & alter session) fails as expected:

SQL> select utl_http.request(url => 'http://www.google.com') from dual;

Error starting at line : 1 in command -
select utl_http.request(url => 'http://www.google.com') from dual
Error report -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1530
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_HTTP", line 380
ORA-06512: at "SYS.UTL_HTTP", line 1470
ORA-06512: at line 1
With no ACLS at all, it's obvious.
According tho the documentation of UTL_HTTP:

This package is an invoker's rights package. The invoking user will need the connect privilege granted in the access control list assigned to the remote network host to which he wants to connect, as well as the use-client-certificates or the use-passwords privilege to authenticate himself with the remote Web server using the credentials stored in an Oracle wallet.

So connect it is: 

BEGIN
  DBMS_NETWORK_ACL_ADMIN.append_host_ace (
    host       => 'www.google.com', 
    lower_port => 80,
    upper_port => 443,
    ace        => xs$ace_type(privilege_list => xs$name_list('connect'),
                              principal_name => 'BERX',
                              principal_type => xs_acl.ptype_db)); 
END;
/
With this privilege, let's see what the ACL trace file says. (I have to set event 10937 to level 6 (not 7) in my lab env - lvl7 did not generate anything). 

SQL> alter session set events '10937 trace name context forever, level 6';

Session altered.

SQL> alter session set events '24247 trace name errorstack level 3';

Session altered.

SQL> select utl_http.request(url => 'http://www.google.com') from dual;

UTL_HTTP.REQUEST(URL=>'HTTP://WWW.GOOGLE.COM')
____________________________________________________________________________
<html code="" itemscope="" itemtype="http://schema.org/WebPage" lan...
The select is fine now - and what's in the trace?


Trace file /u01/app/oracle/diag/rdbms/cdb1/cdb1/trace/cdb1_ora_5483.trc
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
Build label:    RDBMS_19.3.0.0.0DBRU_LINUX.X64_190417
ORACLE_HOME:    /u01/app/oracle/product/19.0.0/dbhome_1
System name:    Linux
Node name:      localhost.localdomain
Release:        5.4.17-2011.5.3.el8uek.x86_64
Version:        #2 SMP Wed Jul 29 22:09:11 PDT 2020
Machine:        x86_64
Instance name: cdb1
Redo thread mounted by this instance: 1
Oracle process number: 53
Unix process pid: 5483, image: oracle@localhost.localdomain


*** 2021-05-02T07:43:38.309047+00:00 (PDB1(3))
*** SESSION ID:(270.62847) 2021-05-02T07:43:38.309125+00:00
*** CLIENT ID:() 2021-05-02T07:43:38.309135+00:00
*** SERVICE NAME:(pdb1) 2021-05-02T07:43:38.309144+00:00
*** MODULE NAME:(java@localhost.localdomain (TNS V1-V3)) 2021-05-02T07:43:38.309153+00:00
*** ACTION NAME:() 2021-05-02T07:43:38.309162+00:00
*** CLIENT DRIVER:(jdbcoci : 19.3.0.0.0) 2021-05-02T07:43:38.309170+00:00
*** CONTAINER ID:(3) 2021-05-02T07:43:38.309210+00:00

psdnop: BERX 120 3 www.google.com:80
psdnopCheckAcl: priv 2147483693 cnt 1
psdnopCheckAcl: aclid 2147493757 status 3
psdnop: denied
psdnop: BERX 120 2 www.google.com:80
psdnopCheckAcl: priv 2147483690 cnt 1
psdnopCheckAcl: aclid 2147493757 status 1
psdnop: granted
According to the last line, everything is fine - access is granted. But there is more! 
the first line of interest is psdnop: BERX 120 3 www.google.com:80. The username is obvious (well, not so if I think about PLSQL with PL/SQL AUTHID CURRENT_USER|DEFINER ).
120 is the USER_ID of user BERX. 
I have no idea what the 3rd parameter of psdnop is - any suggestion is very welcome! 
The last entry is the host & port I try to connect to. 
For me it's the summary what will be checked against ACLs. 
The last line of this first block is psdnop: denied. Even in the 2nd block it's granted, I still 

The second line in this block is psdnopCheckAcl: priv 2147483693 cnt 1. It checks if an ACL is granted. But which one? 
It's first parameter tells us it's a priviliege the number is 2147483693. Unfortunately it's not written in the tracefile, so we need to identify this privilege.

  SQL> select name, id  from sys.xs$obj where id = 2147483693 ;
   NAME            ID
_______ _____________
HTTP       2147483693
This means Oracle first checks if the HTTP privilege is present - as I did not grant it, it's not there. 
The last 2 parameters of psdnopCheckAcl are unclear: cnt 1 - I have no explanation. 

In case you don't want to query sys.xs$obj for a given privilege, have a peek in $ORACLE_HOME/rdbms/admin/catts.sql. The 3 important parts there (for privilege HTTP) are 

RESERVED_ID              NUMBER := 2147483648;
...
PRIV_HTTP                   NUMBER := RESERVED_ID + 45;
...
-- Create http privilege
insert into xs$obj(name, owner, tenant, id, type, status, flags)
            values('HTTP', DEFAULT_OWNER, DEFAULT_TENANT,
                   PRIV_HTTP,4,1,1);



Next line is psdnopCheckAcl: aclid 2147493757 status 3. Again a check, just for an aclid now.  Also this one can be identified, in our case: 
SQL> select * from nacl$_HOST where acl#=2147493757;

             HOST    LOWER_PORT    UPPER_PORT          ACL#
_________________ _____________ _____________ _____________
www.google.com               80           443    2147493757
Now it's clear why the first block is denied: for this host that privilege is just not granted.

To crosscheck on the second (granted) block: 
SQL> select name, id  from sys.xs$obj where id = 2147483690 ;

      NAME            ID
__________ _____________
CONNECT       2147483690

This is by far not a comprehensive introduction into ACL traces, but at least it might help to identify, WHAT is missing when hitting ORA-24247