2022-12-29

AHF Insights

Oracle recently released a new version of its Autonomous Health Framework (AHF). At the time of writing the version is 22.3.1. 
As the development is quite active there, one of the new features is AHF Insights with a nice description: 

AHF Insights provides deeper diagnostic insights into Oracle diagnostic collections collected by AHF diagnostic utilities, Oracle EXAchk, Oracle Trace File Analyzer, Exawatcher, and Cluster Health Monitor.

This sounds promising, so I was tempted to give it a try! 
My first attempt failed: I gave it a chance at my sandbox in my laptop, but right now AHF Insights only work on Engineered Systems. I didn't found this documented somewhere - but ok. The good news: I was told AHF Insights will be available in future versions also for non-Engineered environments. As this is the first release, I can follow the argument here. 

So I had to grab an Engineered System machine, install AHF, let it run for some time and then just run 

ahf analysis create --type insights
I also could be executed for a specific time in the past (with --last n{m|h}) or some range (--from DATETIME --to DATETIME) - But as I was not searching for a specific issue, I took the default 2 Hours
The execution took some minutes, but it was faster than an EXAchk
The zipfile created has a fileformat like <hostname>_insights_YYYY_MM_DD_HH24_MI_SS - it contains one directory web containing an index.html and a lot of subfolders:
.
└── web
    ├── css
    │   ├── alta
    │   │   └── 11.1.1
    │   │       └── web
    │   │           ├── fonts
    │   │           └── images
    │   │               └── sprites
    │   ├── fonts
    │   ├── images
    │   └── redwood
    │       └── 11.1.1
    │           ├── common
    │           └── web
    │               ├── fonts
    │               └── images
    ├── dynamicHtml
    ├── icons
    ├── js
    │   ├── jsons
    │   │   ├── insights
    │   │   ├── meta
    │   │   └── topology
    │   ├── libs
    │   │   ├── oj
    │   │   │   └── v11.1.1
    │   │   │       └── resources
    │   │   │           └── root
    │   │   └── require
    │   ├── viewModels
    │   └── views
    │       ├── compliance
    │       │   └── js
    │       │       └── input
    │       ├── dbParameter
    │       │   └── js
    │       │       └── input
    │       ├── ganttPage
    │       │   └── js
    │       │       └── input
    │       ├── os
    │       │   └── js
    │       │       └── input
    │       └── rpm
    │           └── js
    │               └── input
    └── log


the welcome.html is all you need to open. In my case the first page looks like this: 
It contains some basic information about 
  • the AHF version 
  • the time range it covers
  • System Topology
  • Insights it found



The Cluster information contains basic details about the cluster itself, it's resources and the ASM disks. 
The summary information has anCopy as text button which generates a nice output: 

========================================================================================================================
Area                           | Dimension                      | Value
========================================================================================================================
System                         | Type                           | Exadata
System                         | Total Node Count               | 2
Cluster                        | Node Count                     | 1
Cluster                        | GI Version                     | 19.17.0.0.0
Cluster                        | Timezone                       | Europe/
Cluster                        | Cluster Name                   | 
Cluster                        | CRS Home                       | /u01/app/19.0.0.0/grid
Databases                      | Count                          | 1
Databases                      | Database Home                  | /u02/
Database Servers               | Count                          | 2
Database Servers               | Hardware Model                 | Standard PC (Q35 + ICH9, 2009)
Database Servers               | Image Version                  | 21.2.13.0.0.220602
Database Servers               | Operating System               | Linux x86_64
Database Servers               | Operating System Version       | 4.14.35-2047.511.5.5.1.el7uek.x86_64
========================================================================================================================
Unfortunately this feature isn't present on all the pages. 
The Cluster Resources and ASM disks just contain information about all OCR resources and the ASM diskgroups. 

The Databases Topology information provides some basic information about the CDBs and their PDBs, together with some very basic instance information like cpu_count, sga_target, pga_targetpga_limit and processes

On the Insights section, those with some findings worth to check are highlighted in red. On my system, there are 4 such sections. I will show some of them as examples: 


The Timeline gives a graph of all relevant events, in my case there are only 2 types of events on one hostname.  Below there are filters which can be used if many events appear on the timeline. Also a list of Timestamp, Type, Event, Hostname and Description of each (filtered) event is shown. 



If you move the mouse to the right top of the chart, a list of icons becomes visible for an interactive interaction with the graph:
This can be used in all charts in the Insights.



In the Operating System Issues Page, a lot of detailed information about CPU, Memory, IO, Network, and Processes is shown, both as Charts in Metrics and Grouped Details in the Reports section.  
A nice detail in Process Aggregation is the aggregation on instance basis - so I can check the aggregated memory, processes, etc. for each instance. 


In the Best Practices a list of all the checks, their findings and recommendations is given. 
I always be careful with "Best Practices" - in this particular case the underscore parameters were given by Oracle support in a specific service request. 
But of course, you can see them as recommendations, either follow them or at least document the specific reason you did NOT follow them for a given purpose. 



In the last section, Recommended Software, you can see the early stage of the software. 
For me, 19.17.0.0.0 is close enough to 19.17.0.0.221018.
Maybe in a future release, Oracle will agree ;) 

At all, I can highly recommend to use AHF Insights whenever you need to investigate an issue which might have a wider scope than one single SQL or instance. Even as a first release, it can provide a lot of useful information in a nice and well structured report. 

2022-12-22

OEM 13.5 - add cman target with emcli

 Yesterday I had some fun adding a cman to Oracle Enterprise Manager. 

Using the web frontend is fine for a single target, but it doesn't scale well. But OEM also has a very fine command line utility: emcli

emcli has a high amount of parameters, in my case I'm interested in emcli add_target. This verb has basically 4 parameters: -name, -type -host and -propertiesname and host are kind of obvious, type I already identified as oracle_cman, but the properties are of some mystery. I did not find the list of properties somewhere in the documentation, and also google didn't help. 

So I mixed find and grep together in som random methods until I found the file 

 ./middleware/plugins/oracle.sysman.db.oms.plugin_13.5.1.0.0/metadata/targetType/oracle_cman.xml

Beside some other information (e.g. the name of the perlscripts which test the target) there is also a list of all properties:


  <InstanceProperties>
     <InstanceProperty NAME="Machine" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_Machine_iprop">Machine Name</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="Port" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_Port_iprop">Port Number</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="Protocol" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_Protocol_iprop">Connection Protocol</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="CManOraDir" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_CmanOraDir_iprop">cman.ora Directory</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="CManName" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_CmanName_iprop">Connection Manager Name</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="OracleHome" CREDENTIAL="FALSE" OPTIONAL="FALSE">
       <Display>
         <Label NLSID="oracle_cman_OracleHome_iprop">Oracle Home</Label>
       </Display>
     </InstanceProperty>
     <InstanceProperty NAME="Password" CREDENTIAL="TRUE" OPTIONAL="TRUE">
       <Display>
         <Label NLSID="oracle_cman_OracleHome_iprop">Connection Manager Password</Label>
       </Display>
     </InstanceProperty>


But all this effort would not have been necessary 😁  

At my tests, I hit some issues, and so identified

emcli add_target -name=cman_xxx -type=oracle_cman -host="xxx.yyy.zzz"

threw a proper error:

Error: Required properties are missing: Machine, Port, Protocol, CManOraDir, CManName, OracleHome


This syntax worked for me:


emcli add_target -name=cman_xxx -type=oracle_cman \
  -host="xxx.yyy.zzz" \
  -properties="Machine:xxx.yyy.zzz;Port:1521;Protocol:TCP;CManOraDir:<tns_admin>;CManName:cman_xxx;OracleHome:<oracle_home>;Password:đŸ»đŸ»đŸ»"

My next goal is to test if these values are stored correct.
For this I stumbled about the Note 

Emcli Command To Get Target Properties Information Like ORACLE HOME (Doc ID 2329892.1)


emcli list -resource="TargetProperties" -search="TARGET_NAME ='cman_xxx'" -column="PROPERTY_NAME,PROPERTY_VALUE" -script 


PROPERTY_NAME  PROPERTY_VALUE
CManNamecman_xxx
CManOraDir<TNS_ADMIN>
Machinexxx.yyy.zzz
OracleHome<ORACLE_HOME>
Port1521
ProtocolTCP
orcl_gtp_osLinux
orcl_gtp_platformx86_64

unfortunately, the Password isn't shown here and can not be checked by this method.

2022-12-21

OEM 13.5 - add cman target

Right now I have to add some connection manager instances to Oracle Enterprise manager (13.5.0.10). I did not find any automatic discovery, so I had to enter the required values manually. But not all of these values are quite clear (to me) and all the documentation I found was ... of little use. 

Luckily I stumbled across $ORACLE_HOME/plugins/oracle.sysman.db.agent.plugin_*/scripts/cmanresp.pl
This script seem to check if cman is available at all. To understand the meaning of these properties, the code is quite useful. Here some relevant parts: 

my $oracleHome = $ENV{CMAN_ORACLE_HOME};
my $executable = $ENV{CMAN_ORACLE_HOME} . "/bin/cmctl";
my $name = $ENV{CMAN_NAME};
my $cmanOraDir = $ENV{CMAN_ORA_DIR};
my $machine = $ENV{CMAN_MACHINE};
my $protocol = $ENV{CMAN_PROTOCOL};
my $port = $ENV{CMAN_PORT};
my $password = $ENV{CMAN_PASSWORD};

#set default protocol
if(!defined($protocol) or  $protocol eq "")
{
  $protocol = "TCP";
}

#Set environment variable ORACLE_HOME
$ENV{ORACLE_HOME} = $oracleHome;

my $address = "(ADDRESS=(PROTOCOL=$protocol)(HOST=$machine)(PORT=$port))";

my $responseTime;

#----------------------------------------------
#Execute tnsping and see if cman is reachable.
#-----------------------------------------------
#check tnsping availability
if ( !-x "$oracleHome/bin/tnsping" )
 {
  print "em_result=|0|tnsping not found in $oracleHome/bin\n";
  exit;
 }     
Connection Protocol, Machine Name and Port Number should match exactly the values as in cmans connection string - they are combined to (ADDRESS=(PROTOCOL=$protocol)(HOST=$machine)(PORT=$port)).
The default Protocol is TCP - as it's a required field, this should be entered there. 

Another question I raised (and could not answer before) was if or how the Password was used. In this script it's quite easy visible:

#Command to get the cman status
if($password eq "")
{
  $command = "$executable show status -c $name";
}
else
{
  $command = "$executable show status -c $name -p $password";
}

If no password is given, cmctl is used without the -p parameter. 

The properties Oracle Home (ORACLE_HOME) and cman.ora directory (TNS_ADMIN) are obvious, even for me. 


That's a short post, at least my documentation-to-self.

2022-12-03

from 200 active sessions down to 2 - no silver bullets

 Recently I had some fun with an Oracle Enterprise Manager Cloud control installation (13.5.0.10). The Repository is 19.15 in a MAA Gold configuration: RAC and DataGuard, also the OEM has 2 WLS nodes with a load balancer in front.  

The system was not available for some hours (nothing to blame the architecture, and nothing I can discuss here). 

After everything was working again, the Average Active Sessions on the repository were quite high: around 200. It was clear there was a lot of work to be processed, all the events coming in from the agents to be collected, aggregated and so on. Still that load was something to investigate and improve - if possible. 
The first information I focused on were the high cluster related waits (all the gc ... events) together with buffer busy waits. I picket the top SQL which contributes to these waits: SQL_ID:79rrazqdmaq8h
INSERT INTO EM_METRIC_KEYS (METRIC_KEY_ID, KEY_PART_1, KEY_PART_2, KEY_PART_3, KEY_PART_4, KEY_PART_5, KEY_PART_6, KEY_PART_7, METRIC_KEY_GUID, CREATION_DATE)
  VALUES (EM_METRIC_KEY_ID_SEQ.NEXTVAL, :B1 , :B2 , :B3 , :B4 , :B5 , :B6 , :B7 , :B8 , SYSDATE)
  RETURNING METRIC_KEY_ID INTO :O0
At a first glance, the sequence might be a source for such problems. But as the version is greater than 19.10 already, dynamic sequence cache should mitigate this issue already (and _dynamic_sequence_cache was not set). After a little bit of investigation which objects could be affected at all (e.g. in v$active_session_history.CURRENT_OBJ# for the specific SQL_ID and wait event or in v$session using p1 (file#) and p2 (block#) and then identify the object in DBA_EXTENTS). In this case it's still sequence related, but not SEQ$. Instead the majority of these waits were on the index on EM_METRIC_KEYS(METRIC_KEY_ID).  The solution is still the same: Increase the CACHE for the sequence to a value where each involved instance can use it's only index block based on proper index block split (In my case a CACHE size of 5000 die the trick).
Some other INSERT statements also suffered from the same problems and could be improved by the same way. 
This helped to reduce the AAS down to ~80.

As these type of INSERTs disappeared from the top statements, the next to focus on is an UPDATE: 
SQL_ID: 7hyx2gndnw242
UPDATE EM_EVENT_BUS_QUEUES 
  SET LAST_DEQUEUE_TIME = MGMT_GLOBAL.SYSDATE_UTC 
  WHERE QNAME = :B1
Again a lot of Cluster waits with some Concurrency. The specific wait events looked quite familiar to the previous case. Here several sessions are fighting for the same block to do an update there. As the consistency of the block needs to be communicated clusterwide, and the block itself needs to travel back & forth even cache-fusion and modern Exadata hardware can only help so much. 
A quick check on the table EM_EVENT_BUS_QUEUES_E (the one behind the view EM_EVENT_BUS_QUEUES) showed there are only 89 rows in it (at that time, for this specific system). This leads to the idea to provide each session with direct access to the QUEUE name it is searching for (in the hope the same QUEUE is not processed by different sessions at the same time). 
Doing so is simple: 
ALTER TABLE SYSMAN.EM_EVENT_BUS_QUEUES_E PCTFREE 99; 
ALTER TABLE SYSMAN.EM_EVENT_BUS_QUEUES_E MOVE ONLINE;
This wastes a little bit of space, but contention on the blocks is eliminated. 
Again there were several statements and objects which could be threatened by this method. 
AAS went down to ~35. 

Next top statement is slightly different: 
SQL_ID: 747d8puab2h4u
BEGIN 
  DBMS_APPLICATION_INFO.SET_ACTION(:1 ); 
  EM_LOADERJOB.dequeu_job(:2 ,:3 ,:4 ,:5 ,:6 ,:7 ); 
END;
The wait events are still cluster related. But PL/SQL itself does rarely cause such events, and I was lazy digging into the dequeu_job procedure. But lazyness pays off if it leads me to a solution: in my case a simple google search for 747d8puab2h4u leads to the MOS document 
the solution provided there did not help, but some more search - this time within MOS - led to 
and further to 
and at last 
EM13c : Setting Up RAC Services In Repository Database (Doc ID 2693517.1)
There several tasks are explained, but based on my specific issue, I only focussed on one: Loaderjob service
To implement this specific suggestion, I created a dedicated service on the database with only 1 active instance (don't forget to create this service afterwards on the dataguard, or your next switchover test will become a disaster). 
Then this service was configured to the OMS:
emctl set property -name "oracle.sysman.core.pbs.gcloader.connectDescriptor" -value "\(DESCRIPTION=\(ADDRESS_LIST=\(ADDRESS=\(PROTOCOL=TCP\)\(HOST=<primary racinstance>\)\(PORT=1521\)\)\)\(CONNECT_DATA=\(SERVICE_NAME=...loaderjob\)\)\)"
 Of course, the connection string needs to contain both SCANs, in case of switchover tests ... 
This requires the OMS to restart.
After even one OMS restarted, the AAS ... was still at ~25, but the cluster waits mostly disappeared.

The last statement to improve was a SELECT: 
SQL_ID: dc3p6u6d0uvzn
select 1 
from "SYSMAN"."AQ$_EM_LOADERJOB_Q_TBL_L" 
where msgid = :1 
  and subscriber# = :2 
  and name = :3 
  and address# = :4
This SELECT is only ON CPU - at least no clusterwaits but still something worth to improve, if possible. 
The important observation here was the number of rows in this table. At the time of investigation it was 1. At the same time the segment used significant more blocks than required for 1 row. This ON CPU did just one thing: checking some blocks in buffercache IF there are any rows. And these checks were done at a high pace which created the still significant load. 
The solution for this last task was quite simple: 
alter table "SYSMAN"."AQ$_EM_LOADERJOB_Q_TBL_L" MOVE online;   
(shrink space might also have done the trick)
After that last step, the AAS was down to ~2.



These improvements in performance (or maybe more important, reduction in resource consumption) did take some time - and I did not show all the dead ends in between. 
There is also no silver bullet at all. Just a simple focus on top resource consumers. Of course basic knowledge about the system helps, as well as minimal skills in using a search engine (both online available an don software vendors support page).  
In this specific case, tuning & diagnostic pack was licensed. But all the information used here was available in simple data dictionary and dynamic performance views, if required. 



2022-11-20

dynamic paramater change when CPUs (dis)appear

 In my last 2 blogposts I covered how Oracle instance parameters change based on cpu_count at startup, and also what's the minimum requirement for SGA size

But there is more to discover! Modern systems can change the number of CPUs while running. This feature is quite popular in cloud environments but in fact was already available more than 20 years ago

To see if this has any impact on my (21c) sandbox instance, I used a small "trick" in Linux: 
As root I can set a CPU offline (or back online) by 

echo 0 > /sys/devices/system/cpu/cpu7/online

This CPU becomes invisible to the processes - it also "disappears" in /proc/cpuinfo


At every change, the alert.log shows an entry like (The sampling frequency was 70 sec on my system)

2022-11-20T14:09:12.762038+00:00
Detected change in CPU count to 7
* Load Monitor used for high load check
* New Low - High Load Threshold Range = [0 - 0]

In this case my test was quite small (only 1..8 PCUs) but at least in that range only a small set of parameters changed:

PARAMETER
cpu_count
cpu_min_count
job_queue_processes
parallel_max_servers
parallel_servers_target


cpu_count and cpu_min_count makes sense, the remaining parameters are only related to


parallel and job processes. All of them are following the simple formula cpu_count * 20

This follows the "normal" derived values for these parameters as shown before. 


Again this is nothing tremendous spectacular, but worth to know for performance engineering or analysis in volatile systems: Some parameters change and might lead to unexpected behavior, if the change isn't taken into consideration. 

2022-11-19

minimal SGA requirements - dependent on CPU_COUNT

In my previous post I described how different spfile parameters change dependent on the number of cpus available in the system (or the value of CPU_COUNT in my artificial testcase).
During these tests I saw this error (and so had to re-arrgange my scripts and try again): 

ORA-00821: Specified value of sga_target 16M is too small, needs to be at least 2032M

Even the instance started fine with a smaller cpu_count, it seems the SGA needs to grow when more CPUs are used. So it's worth to re-run the test and see, what's the minimum SGA per CPU. (Again, these tests are made on my sandbox with Version 21.8 - your values might differ). 
My measurement tells me SGA requirements between 260MB for 1 CPU and 11568MB for 255 CPUs (the raw data can be seen here). 
It looks there is a quite linear growth, and this is also confirmed: 

Call:
lm(formula = sga ~ cpu, data = data)

Residuals:
    Min      1Q  Median      3Q     Max 
-91.887 -55.661  -3.829  48.098 116.941 

Coefficients:
             Estimate Std. Error t value Pr(>|t|)    
(Intercept) 112.25259    7.38744    15.2   <2e-16 ***
cpu          44.50121    0.05003   889.5   <2e-16 ***
---
Signif. codes:  0 ‘***’ 0.001 ‘**’ 0.01 ‘*’ 0.05 ‘.’ 0.1 ‘ ’ 1

Residual standard error: 58.81 on 253 degrees of freedom
Multiple R-squared:  0.9997,    Adjusted R-squared:  0.9997 
F-statistic: 7.912e+05 on 1 and 253 DF,  p-value: < 2.2e-16

The core message is: SGA requirements starts with 112.3MB and adds about 44.5MB for each CPU. 

Even the graph looks quite straight, there is a little deviation from the numbers. To make them visible, I generated the delta and get this plot. 
It seems the residuals smile at me. We can also see the calculation up to 32 cpus is slightly different than the remaining, it drops to a minimum (negative residual) at 112 and then increases again. Beside this major pattern there are smaller groups also visible, this indicates different (internal) functions have their own increments and together, they provide this picture. 
Please be aware the units: in the first picture we have units of GB, de residuals are shown in MB. Over all they are still small. 
(In case you want to play with the data, my R commands are available)
You might think this is all irrelevant as with huge number of CPUS there also comes huge amount of memory which can be used for SGA. But at least for XE on nodes with many CPUs, there is a real impact (with a simple solution). 

2022-11-15

spfile parameters derived from cpu_count 11.1 up to 21

In Oracle databases some spfile parameters are derived based on others, if they are not set explicit.
One of the main drivers I discovered is CPU_COUNT.
So I did some tests based in the past for version, now up to 21.

the script I used this time is basically (of course with proper version name).
In comparison to previous versions sga_target is bigger again, now it's 12g (otherwise instances with higher ocunts wouldn't even start)
#!/usr/bin/ksh  -x

integer i=1
while ((i <= 255));
do
  print " $i ";

  echo "*.audit_file_dest='/_some_dir_/BX1/adump'
*.audit_trail='db'
*.compatible='21.0.0.0.0'
*.control_files='/u02/oradata/CDB1/control01.ctl','/u02/oradata/CDB1/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u02/oradata'
*.db_name='cdb1'
*.db_unique_name='cdb1'
*.diagnostic_dest='/u01/appl/oracle'
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=12g
*.enable_pluggable_database=true
*.undo_tablespace='UNDOTBS1'
*._disable_cpu_check='TRUE'
*.cpu_count=$i" > /_some_dir_/cpus/initBX1_$i.ora

  echo "shutdown abort;
startup pfile='/_some_dir_/cpus/initBX1_$i.ora' ;

insert into init_parameters
select '21.08'     ,
       $i         ,
       a.ksppinm  ,
       b.ksppstvl ,
       c.ksppstvl
from x\$ksppi a, x\$ksppcv b, x\$ksppsv c
where a.indx = b.indx and a.indx = c.indx;

commit;

select distinct(\"CPUs\") from init_parameters;

shutdown;
exit;"> cpus/21doit$i.sql

sqlplus "/ as sysdba" @cpus/21doit$i.sql >cpus/21log$i.log

sleep 5

  (( i = i + 1));
done


To get a good picture of the changes, feel free to select parameters, versions or whatever you want below.
Raw data is available at GistHub.
The js app is available here.
(It's based on an idea PerfSheet.js by Luca Canali.

As some of the values are not numbers, I applied this mapping:
TRUE1
FALSE0
OFF0
ADAPTIVE -1
INSTANCE -2


2022-09-12

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!

2022-04-15

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 

2022-02-22

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.

2022-01-15

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!)