Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Migrating Oracle Exadata Workloads to Azure

I know, I know-  there’s a number of you out there thinking-

I’m being brought in on more and more of these projects due to a recent change for some Exadata backup components many companies kept onsite, (parts that wear out more often, like PDUs and cell disks) which are no longer an option and that moving to Azure is a viable option for these workloads if you know what to identify and address before the move to the cloud.

Engineered, Not Iron

An Exadata is an engineered system-  database nodes, secondary cell nodes, (also referred to as storage nodes/cell disks), InfiniBand for fast network connectivity between the nodes, specialized cache, along with software features such as Real Application Clusters, (RAC), hybrid columnar compression, (HCC), storage indexes, (indexes in memory) offloading technology that has logic built into it to move object scans and other intensive workloads to cell nodes from the primary database nodes.  There are considerable other features, but understanding that Exadata is an ENGINEERED system, not a hardware solution is important and its both a blessing and a curse for those databases supported by one.  The database engineer must understand both Exadata architecture and software along with database administration.  There is an added tier of performance knowledge, monitoring and patching that is involved, including knowledge of the Cell CLI, the command line interface for the cell nodes.  I could go on for hours on more details, but let’s get down to what is required when I am working on a project to migrate an Exadata to Azure.

Focus on the Right Thing

I discovered that many times, it was natural for many to focus on the engineered system resources and attempt to duplicate this in Azure.  Although there are numerous features that are similar to Exadata in Azure Data Warehouse, it’s still not the correct path to focus on the engineered system.

Focus on the Database Workload.

Oracle offers us this in the way of an Automatic Workload Repository, (AWR) report.  This data is always being collected, so no extra workload will be requested on the production system.  Simply generate the report on the repository and review the information provided on CPU, memory, IO, along with any red flags that should be addressed before migrating to the cloud.

It allows us to break down workloads by databases.  For most Exadata investments, there was one or two databases that were the primary reason for the Exadata and the main consumer of resources.  By isolating by database, it allows for better sizing, pricing and licensing cost.

With the breakdown by database, we’re also evolving a spreadsheet with the information we need to calculate the Azure resources that would be needed in a lift and shift.  This makes it simpler to consume by the customer if they’re interested, too.

https://dbakevlar.com/wp-content/uploads/2020/01/awr_sizing_xls-300x146.png 300w, https://dbakevlar.com/wp-content/uploads/2020/01/awr_sizing_xls-768x374.png 768w, https://dbakevlar.com/wp-content/uploads/2020/01/awr_sizing_xls.png 1507w" sizes="(max-width: 800px) 100vw, 800px" />

Its an Exadata Thing

The next step is to identify what Exadata specific features are in play that won’t come over in a lift/shift-

  1. Storage indexes that will need to be physically created that currently only exist in memory for smart scans.
  2. What offloading to cell nodes happens currently and will require faster IO and added resources to support on the IaaS cloud solution?
  3. What HCC is in place and the added IO pressure on the system once it no longer exists? What compression options are available in Oracle on IaaS for the customer to take advantage of?
  4. What objects, etc. reside in the different caches?  Flash Cache for recently used objects must be listed and tested for latency.
  5. Was the SGA shrunk to force offloading and does this mean the new databases require more memory than what is currently allocated?
  6. If using RAC, (expected 98% of time on Exadata) is there a plan to stay RAC and if so, is it justified and if not, ensure to calculate for the savings from global cache no longer needed, but total for the needs of each node’s current workload.
  7. Flash log–  without this temporary store for low-latency redo log writes, latency impacts must be identified and optimized through other means.

All of this data is captured in some form, within the AWR reports and via queries from the Cell CLI.  I commonly request a one week snapshot report of AWR from each database on the Exadata.  If there are monthly, quarterly or yearly reports, this can be addressed farther into the project, but gaining an idea of the workloads for each of the databases is my first step.

  1. Enter in all pertinent data into the spreadsheet, including OS, db version, RAC/Non-RAC, memory, CPU/Core, etc.
  2. Trap the percentage of CPU, memory and IO being used by the workload vs. what is available.
  3. Review the AWR and perform a database health check, including any recommendations for issues that should be addressed before the database is moved to the cloud.
  4. Identify any areas that need deeper investigation- odd block sizes, non-default parameter settings, running maintenance/admin jobs that need to be addressed, etc.

I do provide a full healthcheck report of the databases.  Its not something they often receive, so to have this review is incredibly beneficial to their long-term satisfaction with their cloud migration.  Who wants to migrate over a problem with their database?  Resolving it before the cloud migration saves everyone time, money and headaches.

Second phase is to run Exadata specific collections:

  1. Query the cell cli for offloading percentages
  2. Pull the flashcache information from the AWR
  3. Query the amount of flash log that is in use and how often
  4. Query the objects that are currently using HCC, along with compression ratios, uncompressed sizes, IO percentages for workload
  5. Identify storage indexes used for smart scans.  This includes columns and object name they reside on.
  6. Inspect the PGA, is there tons of multi-pass or is the PGA sized correctly?  Will this be sufficient once off of Exadata where some hash joins may be run on cell nodes?

With this data collected, the next step is unique to the customer database, but most often requires:

  • Physical indexes created in the production database, but left invisible and only made visible after the migration to Azure.
  • Implement new compression options that are available in the version of Oracle in Azure.
  • Resize the SGA and PGA to address any shrinking that was done on the Exadata.
  • Pin objects in memory and other cache features for better performance.
  • Optimize any SQL that relied on the Exadata offloading to exist previously, where optimization should have been the first route taken.
  • Place redo logs on ultra disk if required
  • If there is a specific workload that was using high degree of parallelism,(DOP) to run, it may be time to discuss an Azure DW solution to process the data using Azure Data Factory to push the ELT.  Using the right tool for the job is always preferable and there are times where the massive parallel processing and ability to offload to a solution that can handle the workload long term without impacting the existing data store is preferential.

Testing Without Features on Exadata

There are numerous ways you can “shut off” Exadata features.  If the space exists on the Exadata or a test database(s) exist, the following parameter changes can be used to do this or it can be done as an optimization process as part of the lift and shift.

Stop offloading to cell nodes, disable bloom filters and disable storage indexes:

alter system set cell_offload_processing=false;
alter system set #555555; text-transform: none; text-indent: 0px; letter-spacing: normal; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: normal; orphans: 2; float: none; -webkit-text-stroke-width: 0px; background-color: #ffffff;"> _BLOOM_FILTER_ENABLED=false;
alter system set #555555; text-transform: none; text-indent: 0px; letter-spacing: normal; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; display: inline !important; white-space: normal; orphans: 2; float: none; -webkit-text-stroke-width: 0px; background-color: #ffffff;">_KCFIS_STORAGEIDX_DISABLED=true;

You can then verify offloading to cell nodes is no longer occurring with the following query:

select a.name, b.value/1024/1024 MB from v$sysstat a, v$sysstat b
where a.statistics#=b.statistics# and (a.name in ('physical read total bytes', 
'physical write total bytes', 'cell IO uncompressed bytes')
or a.name like 'cell phy%');

The results should no longer show cell activity once this is put into place and you can check offloading and smart scans.  SGA can be increased and other features will still be in play, but this can then be a step by step process as we migrate to the cloud.  It also gives us the ability to shut off features and test in a controlled situation.

I won’t say migrating off of Exadata is easy, but I will say it’s easily doable.  The main goal is to ensure that customers are satisfied in the long run and this is where the steps you see in this post are important.  It’s not as much a lift and shift as it is a lift and build to the cloud.  This would be a requirement in going to any non-Exadata solution and this one will save the customer money and grant them the scalability they didn’t have with an on-prem engineered solution.

 

 



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [Migrating Oracle Exadata Workloads to Azure], All Right Reserved. 2020.

UTL_FILE_DIR and 18c revisited

A couple of years back (wow…time flies!) I made a video and a post about the de-support of UTL_FILE_DIR in 18c. This was good thing because the number of people opting for “utl_file_dir=*” in their init.ora file, and hence opening themselves up to all sorts of risks seemed to be large! (The post elaborates on this more with an example of erasing your database with a short UTL_FILE script Smile)

But a friend came to me with a valid rebuttal, namely that while most people tended to use UTL_FILE_DIR to read/write files to a small list of directories (most typically one), other customers had taken advantage of UTL_FILE to utilise complex directory structures with hundreds or even thousands of sub-folders. Converting that infrastructure (which is trivially implemented with “utl_file_dir=*” even with all its risks) is a tougher proposition when using directory objects in the database, because a single directory object points to a single directory.

To assist with moving away from utl_file_dir in such a circumstance, I’ve whipped up a small utility which aims to be a drop-in replacement for your UTL_FILE.FOPEN calls. The intent of the utility is that moving to the new directory objects method should simply be the case of adding a nested call. Thus if you had an existing file open call of:


f utl_file.fopen('/u01/app/oracle','myfile.dat',...);

you simply nest the utility function as follows:


f utl_file.fopen( utl_file_dir('/u01/app/oracle'),'myfile.dat',...);

and you should be good to go.

The way this is achieved is that the utility does its best to come up with a sensible but unique name for a directory object to map to the standard OS path that has been passed. The comments in the code explain the workings, and of course you’re free to modify it to suit your needs.



create or replace
function utl_file_dir(p_file_or_dir varchar2, 
                      p_create_dir boolean default true, 
                      p_add_hash boolean default true) return varchar2 is
  pragma autonomous_transaction;

  l_default_dir varchar2(128) := 'TEMP';
  l_delim       varchar2(1)   := '/';   -- change for Windows

  l_last_delim  int           := instr(p_file_or_dir,l_delim,-1);
  l_file        varchar2(255); 
  l_dir_path    varchar2(255);
  l_dir_object  varchar2(255);
  l_exists      int;
  l_clash       int;
begin
  if l_last_delim = 0 then
     --
     -- If no presence of a directory delimiter, then we assume the entire
     -- string is a file if it contains a '.' (ie, a file extension)
     -- and return a default directory object name (l_default_dir)
     -- otherwise we assume the string is a directory in its own right
     --
     if p_file_or_dir like '%.%' then
       l_dir_object := l_default_dir;
     else
       l_dir_path   := p_file_or_dir;
     end if;
  else
     --
     -- We have a delimiter. The directory is the contents up to
     -- the last delimiter, unless there is no file extension past
     -- that last delimiter. In that latter case, we assume the entire
     -- string is a directory in its own right
     --
     l_file      := substr(p_file_or_dir,l_last_delim+1);
     if l_file like '%.%' then
       l_dir_path     := substr(p_file_or_dir,1,l_last_delim-1);
     else
       l_dir_path     := p_file_or_dir;
     end if;
  end if;

  --
  -- Now we make a clean directory object name from the path. We could
  -- of course use any random string, but this is designed to make things
  -- a little more intuitive. 
  -- 
  -- For example '/u01/app/oracle' will become U01_APP_ORACLE
  --
  -- You have a choice here in terms of the risk element of collisions depending 
  -- on how loose your folder structure is.  For example, the two paths:
  --
  --   /u01/app/oracle/
  --   /u01/app/"oracle-"/
  --
  -- by default will map to the same clean name of U01_APP_ORACLE and we will   
  -- report an error in this instance.
  -- 
  -- Alternatively (and the default) is that we take our directory path and 
  -- grab the last few bytes from a MD5 hash on it, to greatly increase the likelihood
  -- of a non-clashing directory name.  In the above example, the clean directory names become
  --
  --   U01_APP_ORACLE_25B9C47A
  --   U01_APP_ORACLE_7D51D324
  -- 
  -- So what you lose in intuitive readability you gain in reduced chance of collision.
  -- This is controlled with "p_add_hash"
  --
  if l_dir_object is null then
     l_dir_object := regexp_replace(replace(replace(l_dir_path,l_delim,'_'),'-','_'),'[^[:alnum:] _]');
     l_dir_object := regexp_replace(trim('_' from upper(regexp_replace(l_dir_object,'  *','_'))),'__*','_');
     if p_add_hash then
       select substr(l_dir_object,1,119)||'_'||substr(standard_hash(l_dir_path,'MD5'),1,8)
       into   l_dir_object
       from   dual;
     else
       l_dir_object := substr(l_dir_object,1,128);
     end if;
  end if;

  -- Now we go ahead and create that directory on the database.
  -- The user running this function must have CREATE ANY DIRECTORY privilege granted
  -- explicitly, which means of course, you should protect this routine and perhaps add
  -- some sanity checking to make sure that no-one creates a directory to reference (say) 
  -- the objects in V$DATAFILE !
  
  if p_create_dir then
    select count(*),
           count(case when directory_path != l_dir_path then 1 end) 
    into   l_exists,
           l_clash
    from   all_directories
    where  directory_name = l_dir_object;

    if l_exists = 0 then
      execute immediate 'create directory "'||l_dir_object||'" as q''{'||l_dir_path||'}''';
    else
      --
      -- If (hash or not) we enter the nasty situation where the same clean name would
      -- map to 2 path names, we give up and go home.
      --
      if l_clash > 0 then
        raise_application_error(-20000,'Found matching directory object '||l_dir_object||' with different path from >'||l_dir_path||'<');
      end if;
    end if;
  end if;
  
  commit;
  return l_dir_object;
end;
/
sho err

Here are some examples of typical usage. By default we would actually create the directory object, but you can override this for simple testing like I’ve done below



SQL> -- Examples
SQL>
SQL> variable dirname varchar2(128)
SQL>
SQL> -- standard file
SQL> exec :dirname := utl_file_dir('/u01/app/oracle/test.dat',p_create_dir=>false)

PL/SQL procedure successfully completed.

SQL> print dirname

DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_9E2472BB

SQL>
SQL> -- quoted/spaces etc
SQL> exec :dirname :=  utl_file_dir('/u01/"asd app"/oracle/test.dat',p_create_dir=>false)

PL/SQL procedure successfully completed.

SQL> print dirname

DIRNAME
-----------------------------------------------------------------------------------------------
U01_ASD_APP_ORACLE_FFDC5BEA

SQL>
SQL> -- trailing delimiter.
SQL> exec :dirname :=  utl_file_dir('/u01/app/oracle/',p_create_dir=>false)

PL/SQL procedure successfully completed.

SQL> print dirname

DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_25B9C47A

SQL> exec :dirname :=  utl_file_dir('/u01/app/oracle--/',p_create_dir=>false)

PL/SQL procedure successfully completed.

SQL> print dirname

DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_7D51D324

SQL>
SQL> -- no file
SQL> exec :dirname :=  utl_file_dir('/u01/app/oracle',p_create_dir=>false)

PL/SQL procedure successfully completed.

SQL> print dirname

DIRNAME
-----------------------------------------------------------------------------------------------
U01_APP_ORACLE_9E2472BB

SQL>
SQL> -- no delimiter
SQL> exec :dirname :=  utl_file_dir('mydir',p_create_dir=>false)

PL/SQL procedure successfully completed.

SQL> print dirname

DIRNAME
-----------------------------------------------------------------------------------------------
MYDIR_33CD7707

SQL>
SQL> -- no delimiter but probably a file
SQL> exec :dirname :=  utl_file_dir('mydir.txt',p_create_dir=>false)

PL/SQL procedure successfully completed.

SQL> print dirname

DIRNAME
-----------------------------------------------------------------------------------------------
TEMP

TL;DR: This utility lets moving away from UTL_FILE_DIR be less difficult.

Code at https://github.com/connormcd/misc-scripts

 

MySQL – manually updating table and index optimizer statistics

Goal was to create a empty table and poplulate it with optimizer table and index stats from a poplulated table to try and get the optimizer to give the same explain plan and costs on the empty table as the populated table. The purpose of this was to be able to modify the empty table by adding indexes quickly and cheaply and still be able to get correct optimizer costs. This worked in the case of table access but unfortunately didn’t work when bring in an index.

Create procedure to populate test table

DROP PROCEDURE IF EXISTS populate_data;
DELIMITER //
CREATE PROCEDURE populate_data(max_val int)
BEGIN
DECLARE int_val INT DEFAULT 0;
test_loop : LOOP
IF (int_val = max_val) THEN
LEAVE test_loop;
END IF;
INSERT INTO source(id) VALUES (FLOOR(RAND()*(1000000+1)+1));
SET int_val = int_val +1;
END LOOP;
END;//
DELIMITER ;

Create source table and populate it.
Create source_clone that will be empty but we want to manually fill in optimizer statistics to mimic source table

CREATE TABLE source(id integer);
call populate_data(1000000);
CREATE TABLE source_clone (id integer) ;

comparing table and index stats on source and source_clone we can see as expected they are different:

select * from mysql.innodb_table_stats where table_name like 'sourc%';
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name   | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| kyle          | source       | 2020-01-22 18:49:48 |   9980 |                   21 |                        0 |
| kyle          | source_clone | 2020-01-22 18:49:44 |      0 |                    1 |                        0 |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
select * from mysql.innodb_index_stats where table_name like 'sourc%';
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_diff_pfx01 |       9980 |          20 | DB_ROW_ID                         |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:49:44 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:49:44 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:49:44 | size         |          1 |        NULL | Number of pages in the index      |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

running the explain plan we get different rows returned as expected

--
mysql> explain select id from kyle.source where id > 2056;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source | ALL  | NULL          | NULL | NULL    | NULL | #ff0000;">9980 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.05 sec)

mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source_clone | ALL  | NULL          | NULL | NULL    | NULL |    #ff0000;">1 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.04 sec)

Now let’s copy the stats from the populated source table to the empty source_clone data

-- TABLE  STATS
update mysql.innodb_table_stats AS `dest`,
     (SELECT  n_rows , clustered_index_size 
     FROM mysql.innodb_table_stats 
     WHERE 
         table_name = 'source'
    ) AS `src`
SET dest.n_rows  = src.n_rows , 
    dest.clustered_index_size  = src.clustered_index_size
WHERE 
         table_name = 'source_clone';
-- INDEX STATS
UPDATE mysql.innodb_index_stats AS `dest`,
(SELECT stat_value, stat_name
FROM mysql.innodb_index_stats
WHERE
table_name = 'source'
and index_name = 'GEN_CLUST_INDEX'
) AS `src`
SET dest.stat_value = src.stat_value
WHERE dest.table_name = 'source_clone'
and dest.index_name = 'GEN_CLUST_INDEX'
and dest.stat_name = src.stat_name;

The optimizer table and index statistics are now the same

mysql> select * from mysql.innodb_table_stats where table_name like 'sourc%';
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name   | last_update         | n_rows | clustered_index_size | sum_of_other_index_sizes |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
| kyle          | source       | 2020-01-22 18:49:48 |   9980 |                   21 |                        0 |
| kyle          | source_clone | 2020-01-22 18:54:31 |   9980 |                   21 |                        0 |
+---------------+--------------+---------------------+--------+----------------------+--------------------------+
--
mysql> select * from mysql.innodb_index_stats where table_name like 'sourc%';
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_diff_pfx01 |       9980 |          20 | DB_ROW_ID                         |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:49:48 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:54:46 | n_diff_pfx01 |       9980 |           1 | DB_ROW_ID                         |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:54:46 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:54:46 | size         |         21 |        NULL | Number of pages in the index      |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+

Running explain we are still getting different rows returned on the source clone

--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source_clone | ALL  | NULL          | NULL | NULL    | NULL |   #ff0000;"> 1 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+

But by doing a flush table on the source_clone we not get the same rows returned in the explain as the source

 FLUSH TABLE source_clone;
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | source_clone | ALL  | NULL          | NULL | NULL    | NULL | #ff0000;">9980 | Using where |
+----+-------------+--------------+------+---------------+------+---------+------+------+-------------+

Now let’s create an index and try to manually update the optimizer index stats

CREATE INDEX source_id ON source (id);
CREATE INDEX source_clone_id ON source_clone (id);
--
mysql> explain select id from kyle.source where id > 2056;
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
| id | select_type | table  | type  | possible_keys | key       | key_len | ref  | rows | Extra                    |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source | range | source_id     | source_id | 5       | NULL | #ff0000;">4990 | Using where; Using index |
+----+-------------+--------+-------+---------------+-----------+---------+------+------+--------------------------+
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    #ff0000;">1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
--
UPDATE mysql.innodb_index_stats AS `dest`,
(SELECT stat_value, stat_name
FROM mysql.innodb_index_stats
WHERE
table_name = 'source'
and index_name = 'source_id'
) AS `src`
SET dest.stat_value = src.stat_value
WHERE dest.table_name = 'source_clone'
and dest.index_name = 'source_clone_id'
and dest.stat_name = src.stat_name;
--
mysql> select * from mysql.innodb_index_stats where table_name like 'sourc%';
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name   | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_diff_pfx01 |       9980 |          20 | DB_ROW_ID                         |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source       | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | n_diff_pfx01 |       9950 |          11 | id                                |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | n_diff_pfx02 |      10000 |          11 | id,DB_ROW_ID                      |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | n_leaf_pages |         11 |        NULL | Number of leaf pages in the index |
| kyle          | source       | source_id       | 2020-01-22 18:56:46 | size         |         12 |        NULL | Number of pages in the index      |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_diff_pfx01 |       9980 |           1 | DB_ROW_ID                         |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | n_leaf_pages |         20 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | GEN_CLUST_INDEX | 2020-01-22 18:56:46 | size         |         21 |        NULL | Number of pages in the index      |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | n_diff_pfx01 |       9950 |           1 | id                                |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | n_diff_pfx02 |      10000 |           1 | id,DB_ROW_ID                      |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | n_leaf_pages |         11 |        NULL | Number of leaf pages in the index |
| kyle          | source_clone | source_clone_id | 2020-01-22 18:57:38 | size         |         12 |        NULL | Number of pages in the index      |
+---------------+--------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |   #ff0000;"> 1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
--
 FLUSH TABLE source_clone;
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    #ff0000;">1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
--
Flush tables;
--
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |    #ff0000;">1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
-- REBOOT database
mysql> explain select id from kyle.source_clone where id > 2056;
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
| id | select_type | table        | type  | possible_keys   | key             | key_len | ref  | rows | Extra                    |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | source_clone | range | source_clone_id | source_clone_id | 5       | NULL |   #ff0000;"> 1 | Using where; Using index |
+----+-------------+--------------+-------+-----------------+-----------------+---------+------+------+--------------------------+

The optimizer index stats are the same, we have flushed the tables, and even did a reboot of the database, but the explain plan still shows different results on the source_clone.
The optimizer must be basing costs in this case on things other than mysql.innodb_index_stats and mysql.innodb_table_stats
 

 

Philosophy 23

It’s a long time since I wrote a note with the “philosophy” theme, but a recent complaint about Oracle prompted me to suggest the following thought:

“If you write SQL that is technically incorrect it’s not Oracle’s fault if sometimes the SQL completes without an error.”

Consider the following fragment of code:

drop table t1 purge;

create table t1 (n1 number, n2 number);
insert into t1 values (1,1);
commit;

select n1 from t1 where n2 = to_number('x');
select n1 from t1 where n2 = to_number('x') and n1 = 2;

select 
        case 
                when 
                        0 in  (select n1 from t1 where n2 = to_number('x'))
                then
                        1
                else
                        0
        end
from
        dual
;

Clearly the first query must raise an error because ‘x’ can’t be converted to a number (until Oracle supplies a format option to read it as a Roman Numeral).

Clearly the second query must raise an error because it’s just the first query with an extra predicate on the end.

Clearly the third query must raise an error because it’s going to execute a subquery that must raise an error.

Here’s the output from running the code from SQL*Plus (on 12.2.0.1).

Table dropped.


Table created.


1 row created.


Commit complete.

select n1 from t1 where n2 = to_number('x')
                                       *
ERROR at line 1:
ORA-01722: invalid number



no rows selected


CASEWHEN0IN(SELECTN1FROMT1WHEREN2=TO_NUMBER('X'))THEN1ELSE0END
--------------------------------------------------------------
                                                             0

1 row selected.

The first query fails with the expected conversion error: ORA-01722: invalid number. The second and third queries run to completion because SQL is a declarative language, not a procedural language, and the optimizer is allowed to transform your query in any way that it thinks might make it complete more quickly.

Here’s the execution plan – with predicate information – for the second query:

-------------------------------------+-----------------------------------+
| Id  | Operation          | Name    | Rows  | Bytes | Cost  | Time      |
-------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT   |         |       |       |     2 |           |
| 1   |  TABLE ACCESS FULL | T1      |     1 |    26 |     2 |  00:00:01 |
-------------------------------------+-----------------------------------+

Predicate Information:
----------------------
1 - filter(("N1"=2 AND "N2"=TO_NUMBER('x')))

The optimizer has decided that it’s more efficent to test for the constant 2 than it is to call the to_number() function, so it’s evaluated n1 = 2 first for each row and never had to check the second predicate because nothing got past the first.

The explanation for the successful completion of the third query is slightly different, but again it revolves around transforming for efficiency. Oracle will (very often) convert an IN subquery to an EXISTS subquery. In my example the resulting SQL looks like this (taken from the CBO (10053) trace file, with some cosmeticy enhancement):


SELECT 
        CASE
                WHEN 
                        EXISTS (SELECT 0 FROM T1 WHERE N1=0 AND N2=TO_NUMBER('x')) 
                THEN 1 
                ELSE 0 
        END  "CASEWHEN0IN(SELECTN1FROMT1WHEREN2=TO_NUMBER('X'))THEN1ELSE0END" 
FROM
        DUAL

Note how the “guaranteed to fail” subquery has an extra predicate added as the IN subquery is transformed into an EXISTS subquery and, just like the previous example, the extra predicate is applied before the to_number() predicate, and there’s no data to match the first predicate so the to_number() function never gets called and never gets a chance to raise an exception.

You could argue, or course, that the optimizer should spot the attempt to generate a constant and evaluate it (if possible) at parse time and raise the error before the runtime engine even sees the query – and it wouldn’t be too  hard to make a case for that – but it would only take a few seconds of thought to create slightly more complex examples to demonstrate the point I’ve been making.

Bottom line(s):

1) Use the correct datatypes for your data.

2) Don’t assume that a statement that raises an exception in some circumstances will result in an exception if it is subsequently embedded in a more complex statement. The optimizer is non-procedural and may transform your statement in a way that bypasses your bad design.

 

Footnote:

I probably shouldn’t say this – it will only encourage someone to do the wrong thing – but for the very simplest examples you can show that the ordered_predicates hint still works:


SQL> select /*+ ordered_predicates */  n1 from t1 where n2 = to_number('x') and n1 = 2;
select /*+ ordered_predicates */  n1 from t1 where n2 = to_number('x') and n1 = 2
                                                                  *
ERROR at line 1:
ORA-01722: invalid number

When AWR is not enough, a.k.a. Why your app is not cloud-ready. #SMARTDB

In my previous post I described a case of "enq: TX – row lock contention" that was actually a network latency problem.

More and more those kinds of problems (it seems like history happens all over again) caused my friend (Radosław Kut) and me to write a simple tool to analyze SQL performance from a network perspective.

We called this tool STADO (SQL Tracefile Analyzer Designed for Oracle). It parses a tcpdump outout from application server to identify the longest running queries from application and network perspective.

For now it can show you:

  • SQL ID
  • Ratio – the amount of time spent in network layer
  • Ela App (ms) – time spent in application for executing the query
  • Ela Net (ms) – time spent in network layer for executing the query
  • Exec – number of executions
  • Ela Stddev App – Stddev for time spent from an app perspective
  • Ela App/Exec – AVG time spent on execution from an app perspective
  • Ela Stddev Net – Stddev for time spent from a network perspective
  • Ela Net/Exec – AVG time spent on execution from a network perspective
  • P – amount of packets used for executing this cursor
  • S – how many sessions where executing this cursor
  • RC – how many times a cursor was reused, reducing the amount of parse calls

Let me show you how this tool works, based on a simple example.

I have two database servers (Oracle XE 18c):

  • 10.0.0.8 – remote destination with ping around 64ms
  • 192.168.56.118 – local destination with ping below 1ms

I’ll execute the following script on both servers:

set arraysize &&1
set timing on

alter system flush shared_pool;

select /*NET_TEST*/ * from EMPLOYEES;

select sql_id, elapsed_time/1000 as ela_ms, executions
from v$sql
where sql_text like '%/*NET_TEST*/%'
order by elapsed_time;

exit

While executing a script, I’ll be dumping network traffic with tcpdump like this:

inter@piclerick stado % sudo tcpdump -i any -w sqls.pcap

Let’s analyze our query performance!

REMOTE

inter@piclerick instantclient_18_1 % ./sqlplus hr/hr@10.0.0.8:1521/netst @emps 2

And the result is:

107 rows selected.

Elapsed: 00:00:03.28

SQL_ID		  ELA_MS EXECUTIONS
------------- ---------- ----------
956vu0d29ah05	  13.931	  1
3bxuudrscy1jn	  41.594	  1

Let’s compare SQL execution time from a database perspective and from an app perspective. We can see, that we spent almost 14ms executing a query, but the whole process from an app perspective took 3,28s!!!

Let’s check the same execution on local server:

LOCAL

107 rows selected.

Elapsed: 00:00:00.03

SQL_ID		  ELA_MS EXECUTIONS
------------- ---------- ----------
956vu0d29ah05	   7.702	  1
3bxuudrscy1jn	  26.069	  1

Database shows, that a query was around 2 times faster on a local server, comparing to the remote. The absolute difference is 6,229ms – but the actual difference, actual "wallclock" difference is more than 3s! That is huge.

And of course, that’s nothing new – Elaped time in V$SQL is "Elapsed time (in microseconds) used by this cursor for parsing, executing, and fetching", but it won’t show you the actual time spent on processing this SQL.

So, let’s say, that my local server is much slower than my new server in a remote destination.
I reduced the number of CPUs and RAM and made sure to keep a database a bit busy on my local machine.

Now my results are:

LOCAL

107 rows selected.

Elapsed: 00:00:00.10

SQL_ID		  ELA_MS EXECUTIONS
------------- ---------- ----------
956vu0d29ah05	  18.101	  1
3bxuudrscy1jn	  47.954	  1

REMOTE

107 rows selected.

Elapsed: 00:00:03.30

SQL_ID		  ELA_MS EXECUTIONS
------------- ---------- ----------
956vu0d29ah05	   8.627	  1
3bxuudrscy1jn	  52.103	  1

From a database perspective, the second query is much faster! But the application shows something very different.

Great, now let’s see the same performance, from a TCPDUMP perspective, using STADO:

inter@piclerick stado % ./stado
STADO (SQL Tracedump Analyzer Doing Oracle) by Radoslaw Kut and Kamil Stawiarski
Pcap file analyzer for finding TOP SQLs from an APP perspective
  -C string
    	 directory path to write SQL Charts i.e. -C DevApp
  -d int
    	Debug flag
  -f string
    	path to PCAP file for analyzing
  -i string
    	IP address of database server
  -p string
    	Listener port for database server
  -s string
    	Sort by: ela (Elapsed Time), elax (Elapsed time / Executions), pckt (Packets), rtt (Network Elapsed Time), rttx (Elapsed Time Net / Exec), rat (Ela Net / Ela App) (default "ela")

LOCAL – fetchsize 2

inter@piclerick stado % ./stado -f sqls.pcap -i "192.168.56.118 or 10.0.0.8" -p 1521 -s rat
SQL ID		Ratio		Ela App (ms)	Ela Net(ms)	Exec	Ela Stddev App	Ela App/Exec	Ela Stddev Net	Ela Net/Exec	P	S	RC
------------------------------------------------------------------------------------------------------------------------------------------------------------
5jng2h1ttn71y	0.000000	259.280000	0.000000	1	0.000000	259.280000	0.000000	0.000000	2	1	0
3bxuudrscy1jn	0.000000	87.572000	0.000000	1	0.000000	87.572000	0.000000	0.000000	3	1	0
8wmvpvzmgjmyx	0.000000	2.400000	0.000000	1	0.000000	2.400000	0.000000	0.000000	3	1	0
5w5f39jcwb5nv	0.000000	4.343000	0.000000	1	0.000000	4.343000	0.000000	0.000000	2	1	0
638x13474m3x2	0.000000	1.894000	0.000000	1	0.000000	1.894000	0.000000	0.000000	2	1	0
2s9mmb6g8kbqb	0.000000	2.210000	0.000000	1	0.000000	2.210000	0.000000	0.000000	2	1	0
956vu0d29ah05	0.542286	108.854000	59.030000	1	0.000000	108.854000	0.000000	59.030000	109	1	0

Sum App Time(s): 0.46655299999999994
Sum Net Time(s): 0.05903

No. SQLs: 7

192.168.56.118 32 kb


	Time frame:  2020-01-22 12:39:40.448777 +0100 CET  <=>  2020-01-22 12:39:41.19642 +0100 CET
	Time frame duration (s):  0.747643

REMOTE – fetchsize 2

inter@piclerick stado % ./stado -f sqlsr.pcap -i "192.168.56.118 or 10.0.0.8" -p 1521 -s rat
SQL ID		Ratio		Ela App (ms)	Ela Net(ms)	Exec	Ela Stddev App	Ela App/Exec	Ela Stddev Net	Ela Net/Exec	P	S	RC
------------------------------------------------------------------------------------------------------------------------------------------------------------
8wmvpvzmgjmyx	0.000000	59.969000	0.000000	1	0.000000	59.969000	0.000000	0.000000	3	1	0
5w5f39jcwb5nv	0.000000	68.520000	0.000000	1	0.000000	68.520000	0.000000	0.000000	2	1	0
638x13474m3x2	0.000000	63.750000	0.000000	1	0.000000	63.750000	0.000000	0.000000	2	1	0
2s9mmb6g8kbqb	0.000000	59.799000	0.000000	1	0.000000	59.799000	0.000000	0.000000	2	1	0
5jng2h1ttn71y	0.000000	241.471000	0.000000	1	0.000000	241.471000	0.000000	0.000000	2	1	0
3bxuudrscy1jn	0.000000	108.820000	0.000000	1	0.000000	108.820000	0.000000	0.000000	3	1	0
956vu0d29ah05	0.972269	3200.125000	3111.382000	1	0.000000	3200.125000	0.000000	3111.382000	109	1	0

Sum App Time(s): 3.8024539999999996
Sum Net Time(s): 3.111382

No. SQLs: 7

10.0.0.8 32 kb


	Time frame:  2020-01-22 12:40:05.489516 +0100 CET  <=>  2020-01-22 12:40:10.377233 +0100 CET
	Time frame duration (s):  4.887717

Here we can see clearly which SQL caused the biggest problem, and that time spent on the network was actually 97% of the whole execution time! (In a case of the local server the network time was only 54% – 49ms)

In most cases, this problem is due to a chatty application – like in this case: 107 rows were returned by 109 packets. That’s crazy.
In SQL*Plus we can modify fetch size with a parameter arraysize (default value is 14, which means that SQL*Plus fetches 14 rows at a time).

Let’s see how arraysize parameter influences the execution time:

LOCAL – fetchsize 14

inter@piclerick stado % ./stado -f sqls.pcap -i "192.168.56.118 or 10.0.0.8" -p 1521 -s rat
SQL ID		Ratio		Ela App (ms)	Ela Net(ms)	Exec	Ela Stddev App	Ela App/Exec	Ela Stddev Net	Ela Net/Exec	P	S	RC
------------------------------------------------------------------------------------------------------------------------------------------------------------
3bxuudrscy1jn	0.000000	72.221000	0.000000	1	0.000000	72.221000	0.000000	0.000000	3	1	0
8wmvpvzmgjmyx	0.000000	1.298000	0.000000	1	0.000000	1.298000	0.000000	0.000000	3	1	0
5w5f39jcwb5nv	0.000000	10.774000	0.000000	1	0.000000	10.774000	0.000000	0.000000	2	1	0
638x13474m3x2	0.000000	17.649000	0.000000	1	0.000000	17.649000	0.000000	0.000000	2	1	0
2s9mmb6g8kbqb	0.000000	1.427000	0.000000	1	0.000000	1.427000	0.000000	0.000000	2	1	0
5jng2h1ttn71y	0.000000	199.157000	0.000000	1	0.000000	199.157000	0.000000	0.000000	2	1	0
956vu0d29ah05	0.226816	41.937000	9.512000	1	0.000000	41.937000	0.000000	9.512000	17	1	0

Sum App Time(s): 0.344463
Sum Net Time(s): 0.009512

No. SQLs: 7

192.168.56.118 27 kb


	Time frame:  2020-01-22 14:14:23.996268 +0100 CET  <=>  2020-01-22 14:14:24.45883 +0100 CET
	Time frame duration (s):  0.462562

REMOTE – fetchsize 14

inter@piclerick stado % ./stado -f sqlsr.pcap -i "192.168.56.118 or 10.0.0.8" -p 1521 -s rat
SQL ID		Ratio		Ela App (ms)	Ela Net(ms)	Exec	Ela Stddev App	Ela App/Exec	Ela Stddev Net	Ela Net/Exec	P	S	RC
------------------------------------------------------------------------------------------------------------------------------------------------------------
3bxuudrscy1jn	0.000000	116.133000	0.000000	1	0.000000	116.133000	0.000000	0.000000	3	1	0
8wmvpvzmgjmyx	0.000000	59.618000	0.000000	1	0.000000	59.618000	0.000000	0.000000	3	1	0
5w5f39jcwb5nv	0.000000	64.575000	0.000000	1	0.000000	64.575000	0.000000	0.000000	2	1	0
638x13474m3x2	0.000000	59.540000	0.000000	1	0.000000	59.540000	0.000000	0.000000	2	1	0
2s9mmb6g8kbqb	0.000000	59.320000	0.000000	1	0.000000	59.320000	0.000000	0.000000	2	1	0
5jng2h1ttn71y	0.000000	256.340000	0.000000	1	0.000000	256.340000	0.000000	0.000000	2	1	0
956vu0d29ah05	0.836899	484.620000	405.578000	1	0.000000	484.620000	0.000000	405.578000	17	1	0

Sum App Time(s): 1.1001460000000003
Sum Net Time(s): 0.405578

No. SQLs: 7

10.0.0.8 27 kb


	Time frame:  2020-01-22 14:13:49.553632 +0100 CET  <=>  2020-01-22 14:13:51.847139 +0100 CET
	Time frame duration (s):  2.293507

LOCAL – fetchsize 200

inter@piclerick stado % ./stado -f sqls.pcap -i "192.168.56.118 or 10.0.0.8" -p 1521 -s rat
SQL ID		Ratio		Ela App (ms)	Ela Net(ms)	Exec	Ela Stddev App	Ela App/Exec	Ela Stddev Net	Ela Net/Exec	P	S	RC
------------------------------------------------------------------------------------------------------------------------------------------------------------
3bxuudrscy1jn	0.000000	87.994000	0.000000	1	0.000000	87.994000	0.000000	0.000000	3	1	0
8wmvpvzmgjmyx	0.000000	1.121000	0.000000	1	0.000000	1.121000	0.000000	0.000000	3	1	0
5w5f39jcwb5nv	0.000000	2.315000	0.000000	1	0.000000	2.315000	0.000000	0.000000	2	1	0
638x13474m3x2	0.000000	14.832000	0.000000	1	0.000000	14.832000	0.000000	0.000000	2	1	0
2s9mmb6g8kbqb	0.000000	1.259000	0.000000	1	0.000000	1.259000	0.000000	0.000000	2	1	0
5jng2h1ttn71y	0.000000	187.470000	0.000000	1	0.000000	187.470000	0.000000	0.000000	2	1	0
956vu0d29ah05	0.019606	17.546000	0.344000	1	0.000000	17.546000	0.000000	0.344000	9	1	0

Sum App Time(s): 0.31253699999999995
Sum Net Time(s): 0.00034399999999999996

No. SQLs: 7

192.168.56.118 26 kb


	Time frame:  2020-01-22 14:30:09.225337 +0100 CET  <=>  2020-01-22 14:30:09.664531 +0100 CET
	Time frame duration (s):  0.439194

REMOTE – fetchsize 200

inter@piclerick stado % ./stado -f sqlsr.pcap -i "192.168.56.118 or 10.0.0.8" -p 1521 -s rat
SQL ID		Ratio		Ela App (ms)	Ela Net(ms)	Exec	Ela Stddev App	Ela App/Exec	Ela Stddev Net	Ela Net/Exec	P	S	RC
------------------------------------------------------------------------------------------------------------------------------------------------------------
2s9mmb6g8kbqb	0.000000	63.342000	0.000000	1	0.000000	63.342000	0.000000	0.000000	2	1	0
5jng2h1ttn71y	0.000000	237.884000	0.000000	1	0.000000	237.884000	0.000000	0.000000	2	1	0
3bxuudrscy1jn	0.000000	119.722000	0.000000	1	0.000000	119.722000	0.000000	0.000000	3	1	0
8wmvpvzmgjmyx	0.000000	64.242000	0.000000	1	0.000000	64.242000	0.000000	0.000000	3	1	0
5w5f39jcwb5nv	0.000000	67.841000	0.000000	1	0.000000	67.841000	0.000000	0.000000	2	1	0
638x13474m3x2	0.000000	64.604000	0.000000	1	0.000000	64.604000	0.000000	0.000000	2	1	0
956vu0d29ah05	0.488563	127.828000	62.452000	1	0.000000	127.828000	0.000000	62.452000	11	1	0

Sum App Time(s): 0.745463
Sum Net Time(s): 0.062452

No. SQLs: 7

10.0.0.8 26 kb


	Time frame:  2020-01-22 14:33:46.121799 +0100 CET  <=>  2020-01-22 14:33:48.165371 +0100 CET
	Time frame duration (s):  2.043572

As we can see, we reduced the time of SQL statement from more than 3000ms to around 128ms by modifying the size of single fetch – fewer packets equals fewer round trips.

Still, the network plays a significant role in the performance of this query (~ 62%), and the bigger latency – the slower the query will be.
Imagine that you have an application with a lot of this kind of chatty cursors, no PL/SQL logic and everything based on JAVA or Python or something like this… Well, most of you don’t have to imagine – you see those systems every day!

If you take this kind of system and migrate it to some fancy new hardware with a turbo-cool automagic hocus-pocus self-problem-solving platform, will it run faster? If this marvelous platform is not a network switch away from your app server, the answer is simple and brutal – NO.

Cloud won’t solve your problems. If your app is designed in a shitty way – cloud will escalate your problems. There is no magic – only physics.

If you want to check if your systems are network-sensitive and chatty – just make a tcpdump and analyze output with STADO, which can be found here:
https://github.com/ora600pl/stado

Plain Language : My review of the course

Last week I went on a Plain Language course. If you were following me on Twitter, you’ll know I was feeling a bit nervous about it. I find any type of “course” difficult. I don’t like being “trapped” and I prefer to learn things at my own pace. Having said that, it went really well.

What’s the point?

How you speak and write can have a big impact on how your message is received. I work for a university, which has a large number of overseas students and staff, where English is not their first language.

A significant proportion of our user base need accessibility tools, and a similar proportion use them by choice.

Even when English is your first language, it can be difficult to understand some of the rubbish that gets produced.

Isn’t it just about dumbing down?

Some people love flowery bullshit language. I hate it. I’m not the best at reading, so every unnecessary word requires parse time, and makes it easier for me to lose my concentration.

My problem is similar to that faced by someone who doesn’t have English as a first language, or someone using accessibility tools. There is a lot of effort spent dealing with words that add no value to the meaning.

Know your audience!

You always have to consider your audience when writing and speaking. There is a difference between writing a legal document, an academic paper and instructions about how to log into the WIFI.

My statistics tell me that about 45% of people reading this will be from India. About 43% from the USA, and the remaining are made up from the rest of the world. I have no idea about the language skills of the audience in those locations, but I’m guessing they don’t track well with someone born 50 years ago and raised in the Midlands, UK. </p />
</p></div>

    	  	<div class=

Announcing SLOB 2.5.2.3

SLOB 2.5.2.3 is a bug-fix release available via the SLOB Resources Page. The bug fixes in this release have to do with refinements to the undocumented Obfuscated Column feature which first appeared in SLOB 2.5.2.

I’d like to give special thanks for some very skilled SLOB experts at Netapp for their feedback on the Obfuscated Column feature. Your testing helped find bugs and resulted in improved functionality.

Thank you Rodrigo Nascimento, Joe Carter and Scott Lane!

 

 

Analysing Database Time with Active Session History for Statements with Online Optimizer Statistics Gathering Operations

I have been looking into the performance of online statistics collection. When statistics are collected online there is an extra OPTIMIZER STATISTICS GATHERING operation in the execution plan. However, I have noticed that the presence or absence of this operation does not change the hash value of the plan. This has consequences for profiling DB time by execution plan line and then describing that line from a captured plan.

OPTIMIZER STATISTICS GATHERING Operation

From 12c, statistics are collected online during either a create-table-as-select operation or the initial direct-path insert into a new segment.  Below, I have different statements, whose execution plans have the same plan hash value, but actually differ. So, the differences are in areas that do not contribute to the plan hash value.

  • The first statement performs online statistics gathering, and so the plan includes the OPTIMIZER STATISTICS GATHERING operation, the second does not.
  • Note also that the statements insert into different tables, and that does not alter the plan hash value either. However, if the data was queried from different tables that would have produced a different plan hash value.
#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">INSERT /*+APPEND PARALLEL(i)*/ into T2 i SELECT * /*+*/ FROM t1 s

Plan hash value: 90348617
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 178K(100)| | | |
| 1 | LOAD AS SELECT | T2 | | | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 100M| 4005M| 178K (1)| 00:00:07 | | |
| 3 | PARTITION RANGE ALL | | 100M| 4005M| 178K (1)| 00:00:07 | 1 |1048575|
| 4 | TABLE ACCESS STORAGE FULL | T1 | 100M| 4005M| 178K (1)| 00:00:07 | 1 |1048575|
---------------------------------------------------------------------------------------------------------
#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">INSERT /*+APPEND PARALLEL(i) NO_GATHER_OPTIMIZER_STATISTICS*/ into T3 i
SELECT /*+*/ * FROM t1 s

Plan hash value: 90348617
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 178K(100)| | | |
| 1 | LOAD AS SELECT | T3 | | | | | | |
| 2 | PARTITION RANGE ALL | | 100M| 4005M| 178K (1)| 00:00:07 | 1 |1048575|
| 3 | TABLE ACCESS STORAGE FULL| T1 | 100M| 4005M| 178K (1)| 00:00:07 | 1 |1048575|
----------------------------------------------------------------------------------------------------

I find that it is often useful to profile database time from DBA_HIST_ACTIVE_SESS_HISTORY (or v$active_session_history) by line in the execution plan, in order to see how much time was consumed by the different operations. I can then join the profile to DBA_HIST_SQL_PLAN (or v$sql_plan) to see what is the operation for each line. So long as I also join these tables by SQL_ID, the answer I get will be correct, but I may not always get an answer.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">column inst_id heading 'Inst|Id' format 99
column sql_plan_line_id heading 'SQL Plan|Line ID'
column sql_plan_hash_value heading 'SQL Plan|Hash Value'
column ash_secs heading 'ASH|Secs' format 999
break on sql_id skip 1
with h as (
SELECT h.dbid, h.sql_id, h.sql_plan_line_id, h.sql_plan_hash_Value
, SUM(10) ash_secs
FROM dba_hist_Active_Sess_history h
WHERE h.sql_plan_hash_value = 90348617
AND h.sql_id IN('g7awpb71jbup1','c2dy3rmnqp7d7','drrbxctf8t5nz','7140frhyu42t5')
GROUP BY h.dbid, h.sql_id, h.sql_plan_line_id, h.sql_plan_hash_Value
)
SELECT h.*, p.operation
FROM h
LEFT OUTER JOIN dba_hist_sql_plan p
ON p.dbid = h.dbid
and p.sql_id = h.sql_id
AND p.plan_hash_value = h.sql_plan_hash_value
AND p.id = h.sql_plan_line_id
ORDER BY 1,2,3
/

If the plan was not captured into AWR or is no longer in the library cache, I don't get a description of the operations in the plan.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">                SQL Plan   SQL Plan  ASH
SQL_ID Line ID Hash Value Secs OPERATION
------------- ---------- ---------- ---- --------------------------------
0s4ruucw2wvsw 0 90348617 4 INSERT STATEMENT
1 90348617 77 LOAD AS SELECT
2 90348617 25 OPTIMIZER STATISTICS GATHERING
3 90348617 11 PARTITION RANGE
4 90348617 24 TABLE ACCESS

33x8fjppwh095 0 90348617 2 INSERT STATEMENT
1 90348617 89 LOAD AS SELECT
2 90348617 10 PARTITION RANGE
3 90348617 20 TABLE ACCESS

7140frhyu42t5 0 90348617 1
1 90348617 83
2 90348617 8
3 90348617 28

9vky53vhy5740 0 90348617 3
1 90348617 89
2 90348617 23
3 90348617 9
4 90348617 22

Normally, I would look for another SQL_ID that produced the same plan hash value. However, for an execution plan that only sometimes includes online statistics gathering, the operations may not match correctly because the OPTIMIZER STATISTICS GATHERING operation changes the line IDs.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">WITH h as (
SELECT h.dbid, h.sql_id, h.sql_plan_line_id, h.sql_plan_hash_Value
, SUM(10) ash_secs
FROM dba_hist_Active_Sess_history h
WHERE h.sql_plan_hash_value = 90348617
AND h.sql_id IN('g7awpb71jbup1','c2dy3rmnqp7d7','drrbxctf8t5nz','7140frhyu42t5')
GROUP BY h.dbid, h.sql_id, h.sql_plan_line_id, h.sql_plan_hash_Value
), p as (
SELECT DISTINCT dbid, plan_hash_value, id, operation
from dba_hist_sql_plan

)
SELECT h.*, p.operation
FROM h
LEFT OUTER JOIN p
ON p.dbid = h.dbid
AND p.plan_hash_value = h.sql_plan_hash_value
AND p.id = h.sql_plan_line_id
ORDER BY 1,2,3
/

If I just join the ASH profile to a distinct list of ID and operation for the same plan hash value but matching any SQL_ID, I can get duplicate rows returned, starting at the line with the OPTIMIZER STATISTICS GATHERING operation because I have different plans with the same plan hash value.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">                           SQL Plan   SQL Plan  ASH
DBID SQL_ID Line ID Hash Value Secs OPERATION
---------- ------------- ---------- ---------- ---- ------------------------------
1278460406 7140frhyu42t5 1 90348617 80 LOAD AS SELECT
1278460406 2 90348617 10 OPTIMIZER STATISTICS GATHERING
1278460406 2 90348617 10 PARTITION RANGE
1278460406 3 90348617 30 PARTITION RANGE
1278460406 3 90348617 30 TABLE ACCESS
...

To mitigate this problem, in the following SQL Query, I check that the maximum plan line ID for which I have ASH data matches the maximum line ID (i.e. the number of lines) in any alternative plan with the same hash value.

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">WITH h as (
SELECT h.dbid, h.sql_id, h.sql_plan_line_id, h.sql_plan_hash_Value
, SUM(10) ash_secs
FROM dba_hist_Active_Sess_history h
WHERE h.sql_plan_hash_value = 90348617
AND h.sql_id IN('g7awpb71jbup1','c2dy3rmnqp7d7','drrbxctf8t5nz','7140frhyu42t5')
GROUP BY h.dbid, h.sql_id, h.sql_plan_line_id, h.sql_plan_hash_value
), x as (
SELECT h.*
, MAX(sql_plan_line_id) OVER (PARTITION BY h.dbid, h.sql_id) plan_lines
, p1.operation
FROM h
LEFT OUTER JOIN dba_hist_sql_plan p1
ON p1.dbid = h.dbid
AND p1.sql_id = h.sql_id
AND p1.plan_hash_value = h.sql_plan_hash_value
AND p1.id = h.sql_plan_line_id
)
SELECT x.*
, (SELECT p2.operation
FROM dba_hist_sql_plan p2
WHERE p2.dbid = x.dbid
AND p2.plan_hash_value = x.sql_plan_hash_value
AND p2.id = x.sql_plan_line_id
AND p2.sql_id IN(
SELECT p.sql_id
FROM dba_hist_sql_plan p
WHERE p.dbid = x.dbid
AND p.plan_hash_value = x.sql_plan_hash_value
GROUP BY p.dbid, p.sql_id
HAVING MAX(p.id) = x.plan_lines)
AND rownum = 1) operation2
FROM x
ORDER BY 1,2,3
/

Now, I get an operation description for every line ID (if the same plan was gathered for a different SQL_ID).

#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">                           SQL Plan   SQL Plan  ASH
DBID SQL_ID Line ID Hash Value Secs PLAN_LINES OPERATION OPERATION2
---------- ------------- ---------- ---------- ---- ---------- -------------------------------- ------------------------------
1278460406 7140frhyu42t5 1 90348617 80 3 LOAD AS SELECT
1278460406 2 90348617 10 3 PARTITION RANGE
1278460406 3 90348617 30 3 TABLE ACCESS

1278460406 c2dy3rmnqp7d7 1 90348617 520 4 LOAD AS SELECT LOAD AS SELECT
1278460406 2 90348617 100 4 OPTIMIZER STATISTICS GATHERING OPTIMIZER STATISTICS GATHERING
1278460406 3 90348617 80 4 PARTITION RANGE PARTITION RANGE
1278460406 4 90348617 280 4 TABLE ACCESS TABLE ACCESS
1278460406 90348617 30 4

1278460406 drrbxctf8t5nz 1 90348617 100 4 LOAD AS SELECT
1278460406 2 90348617 10 4 OPTIMIZER STATISTICS GATHERING
1278460406 3 90348617 10 4 PARTITION RANGE
1278460406 4 90348617 50 4 TABLE ACCESS

1278460406 g7awpb71jbup1 1 90348617 540 3 LOAD AS SELECT LOAD AS SELECT
1278460406 2 90348617 60 3 PARTITION RANGE PARTITION RANGE
1278460406 3 90348617 90 3 TABLE ACCESS TABLE ACCESS
1278460406 90348617 20 3

However, this approach, while better, is still not perfect. I may not have sufficient DB time for the last line in the execution plan to be sampled, and therefore I may not choose a valid alternative plan.

Autonomous & Cloud Databases

Automatic online statistics gathering is becoming a more common occurrence.

  • In the Autonomous Data Warehouse, Oracle has set _optimizer_gather_stats_on_load_all=TRUE, so statistics are collected on every direct-path insert. 
  • From 19c, on Engineered Systems (both in the cloud and on-premises), Real-Time statistics are collected during conventional DML (on inserts, updates and some deletes), also using the OPTIMIZER STATISTICS GATHERING operation. Again, the presence or absence of this operation does not affect the execution plan hash value.
#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">SQL_ID  f0fsghg088k3q, child number 0
-------------------------------------
INSERT INTO t2 SELECT * FROM t1

Plan hash value: 589593414
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1879 (100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | T2 | | | | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 1000K| 40M| 1879 (1)| 00:00:01 | | |
| 3 | PARTITION RANGE ALL | | 1000K| 40M| 1879 (1)| 00:00:01 | 1 |1048575|
| 4 | TABLE ACCESS STORAGE FULL | T1 | 1000K| 40M| 1879 (1)| 00:00:01 | 1 |1048575|
---------------------------------------------------------------------------------------------------------
#eeeeee; border: 0px solid #000000; font-family: "courier new"; font-size: 98%; line-height: 1.0; overflow: auto; padding-left: 2px; padding-right: 2px; width: 95%;">SQL_ID  360pwsfmdkxf4, child number 0
-------------------------------------
INSERT /*+NO_GATHER_OPTIMIZER_STATISTICS*/ INTO t3 SELECT * FROM t1

Plan hash value: 589593414
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 1879 (100)| | | |
| 1 | LOAD TABLE CONVENTIONAL | T3 | | | | | | |
| 2 | PARTITION RANGE ALL | | 1000K| 40M| 1879 (1)| 00:00:01 | 1 |1048575|
| 3 | TABLE ACCESS STORAGE FULL| T1 | 1000K| 40M| 1879 (1)| 00:00:01 | 1 |1048575|
----------------------------------------------------------------------------------------------------

London March 2020: “Oracle Indexing Internals and Best Practices” and “Oracle Performance Diagnostics and Tuning” Seminars

  Time is fast running out to enroll for one of my acclaimed seminars I’ll be running in London, UK in March 2020. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” Seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and Tuning” Seminar […]

What’s new with Oracle database 12.2.0.1.191015 versus 12.2.0.1.200114

For the difference between Oracle database versions 12.2.0.1.191015 and 12.2.0.1.200114 this too follows the line of a low amount of differences.

There have been two spare parameters that have been changed to named undocumented parameters, and no data dictionary changes.

parameters unique in version 12.2.0.1.191015 versus 12.2.0.1.200114

NAME
--------------------------------------------------
_fifth_spare_parameter
_one-hundred-and-forty-eighth_spare_parameter

parameters unique in version 12.2.0.1.200114 versus 12.2.0.1.191015

NAME
--------------------------------------------------
_bug29825525_bct_public_dba_buffer_dynresize_delay
_enable_ptime_update_for_sys

On the C function side, there have been a group of AWR functions that have been removed and a group of SGA management functions, among other functions. There functions that have been added are random and diverse.

code symbol names unique in version 12.2.0.1.191015 versus 12.2.0.1.200114

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
R_CR_entropy_resource_init                                   R_CR_entropy_resource_init                                   ??
kcbzdra                                                      (kcbz)dra                                                    kernel cache buffers subroutines for kcb ??
kdmsCreateSampleInvBlkList                                   (kdm)sCreateSampleInvBlkList                                 kernel data in-memory data layer ??
kdmsFillSampleList                                           (kdm)sFillSampleList                                         kernel data in-memory data layer ??
kewmfdms_flush_drmsum                                        (kewm)fdms_flush_drmsum                                      kernel event AWR metrics ??
kewmgaeidct                                                  (kewm)gaeidct                                                kernel event AWR metrics ??
kewmusmdb_update_smdbuf                                      (kewm)usmdb_update_smdbuf                                    kernel event AWR metrics ??
kewramcs_app_map_condbid_str                                 (kewr)amcs_app_map_condbid_str                               kernel event AWR repository ??
kewramvn_append_mdb_vvwname                                  (kewr)amvn_append_mdb_vvwname                                kernel event AWR repository ??
kewrccsq_collect_csql                                        (kewr)ccsq_collect_csql                                      kernel event AWR repository ??
kewrfosp2_fos_mdb_part2                                      (kewrf)osp2_fos_mdb_part2                                    kernel event AWR repository flush ??
kewrfosp3_fos_mdb_part3                                      (kewrf)osp3_fos_mdb_part3                                    kernel event AWR repository flush ??
kewrgcfes_get_cacheid_from_enum_str                          (kewr)gcfes_get_cacheid_from_enum_str                        kernel event AWR repository ??
kewrggd_get_group_descriptor                                 (kewr)ggd_get_group_descriptor                               kernel event AWR repository ??
kewrggf_grp_get_flags                                        (kewr)ggf_grp_get_flags                                      kernel event AWR repository ??
kewrggh_grp_get_handle                                       (kewr)ggh_grp_get_handle                                     kernel event AWR repository ??
kewrggmc_grp_get_member_count                                (kewr)ggmc_grp_get_member_count                              kernel event AWR repository ??
kewrgltn_gen_lrgtest_tab_name                                (kewr)gltn_gen_lrgtest_tab_name                              kernel event AWR repository ??
kewrgvm_grp_valid_member                                     (kewr)gvm_grp_valid_member                                   kernel event AWR repository ??
kewrice_is_cache_enabled                                     (kewr)ice_is_cache_enabled                                   kernel event AWR repository ??
kewrmfp_map_flush_phase                                      (kewr)mfp_map_flush_phase                                    kernel event AWR repository ??
kewrmplvl_map_snap_level                                     (kewr)mplvl_map_snap_level                                   kernel event AWR repository ??
kewrpfbue_pdb_from_buffer_entry                              (kewr)pfbue_pdb_from_buffer_entry                            kernel event AWR repository ??
kewrptsq_prep_topsql                                         (kewr)ptsq_prep_topsql                                       kernel event AWR repository ??
kewrrc_release_cache                                         (kewr)rc_release_cache                                       kernel event AWR repository ??
kewrsaobn_set_all_objnames                                   (kewr)saobn_set_all_objnames                                 kernel event AWR repository ??
kewrsonie_set_object_names_in_entry                          (kewr)sonie_set_object_names_in_entry                        kernel event AWR repository ??
kewrsqlc_sql_iscolored_cb                                    (kewr)sqlc_sql_iscolored_cb                                  kernel event AWR repository ??
kgskltyp                                                     (kgsk)ltyp                                                   kernel generic service resource manager ??
kkeutlCopyAllocatorState                                     (kke)utlCopyAllocatorState                                   kernel compile cost engine ??
kkeutlIsAllocStructureSame                                   (kke)utlIsAllocStructureSame                                 kernel compile cost engine ??
kmgs_check_uninited_comp                                     (kmgs)_check_uninited_comp                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_partial_inuse_list_comp                            (kmgs)_dump_partial_inuse_list_comp                          kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_quiesce_list                                       (kmgs)_dump_quiesce_list                                     kernel multi threaded/mman manage (sga) space (?) ??
kmgs_dump_resize_summary                                     (kmgs)_dump_resize_summary                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_fill_start_sizes                                        (kmgs)_fill_start_sizes                                      kernel multi threaded/mman manage (sga) space (?) ??
kmgs_get_min_cache_grans                                     (kmgs)_get_min_cache_grans                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgs_getgran_from_comp_pg                                    (kmgs)_getgran_from_comp_pg                                  kernel multi threaded/mman manage (sga) space (?) ??
kmgs_init_sgapga_comps                                       (kmgs)_init_sgapga_comps                                     kernel multi threaded/mman manage (sga) space (?) ??
kmgs_nvmksmid_2_kcbpoolid                                    (kmgs)_nvmksmid_2_kcbpoolid                                  kernel multi threaded/mman manage (sga) space (?) ??
kmgs_recv_and_donor_are_caches                               (kmgs)_recv_and_donor_are_caches                             kernel multi threaded/mman manage (sga) space (?) ??
kmgs_shrink_gran                                             (kmgs)_shrink_gran                                           kernel multi threaded/mman manage (sga) space (?) ??
kmgs_update_param_manual_helper                              (kmgs)_update_param_manual_helper                            kernel multi threaded/mman manage (sga) space (?) ??
kmgs_update_resize_summary                                   (kmgs)_update_resize_summary                                 kernel multi threaded/mman manage (sga) space (?) ??
kmgsb_in_range                                               (kmgs)b_in_range                                             kernel multi threaded/mman manage (sga) space (?) ??
kmgsdpgl                                                     (kmgs)dpgl                                                   kernel multi threaded/mman manage (sga) space (?) ??
kmgsset_timestamp                                            (kmgs)set_timestamp                                          kernel multi threaded/mman manage (sga) space (?) ??
krvxgtf                                                      (krvx)gtf                                                    kernel redo recovery extract ??
krvxrte                                                      (krvx)rte                                                    kernel redo recovery extract ??
kslsesftcb_int                                               (ksl)sesftcb_int                                             kernel service  latching and post-wait ??
ksmg_estimate_sgamax                                         (ksm)g_estimate_sgamax                                       kernel service  memory ??
ktcxbFlgPrint                                                (ktc)xbFlgPrint                                              kernel transaction control component ??
kzagetcid                                                    (kza)getcid                                                  kernel security audit  ??
kzekmdcw                                                     (kz)ekmdcw                                                   kernel security ??
qeroiFirstPart                                               (qeroi)FirstPart                                             query execute rowsource extensibel indexing query component ??
qksbgUnderOFE                                                (qksbg)UnderOFE                                              query kernel sql bind (variable) management(?) ??
ri_entcb_cmd_func                                            ri_entcb_cmd_func                                            ??
zt_yield_entropy_source_cb                                   (zt)_yield_entropy_source_cb                                 security encryption ??

code symbol names unique in version 12.2.0.1.200114 versus 12.2.0.1.191015

NAME                                                         RESOLVE                                                      ANNOTATION
------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
apagwnrn                                                     (apa)gwnrn                                                   SQL Access Path Analysis ??
apagwnrnprd                                                  (apa)gwnrnprd                                                SQL Access Path Analysis ??
apatwnrn                                                     (apa)twnrn                                                   SQL Access Path Analysis ??
kafcpy_one_row                                               (kaf)cpy_one_row                                             kernel access fetch ??
kcbz_eff_bsz                                                 (kcbz)_eff_bsz                                               kernel cache buffers subroutines for kcb ??
kdilm_row_diskcompress_policy_type                           (kdil)m_row_diskcompress_policy_type                         kernel data index load ??
kdsReadAheadSafe                                             (kds)ReadAheadSafe                                           kernel data seek/scan ??
kfdFreeReqs                                                  (kfd)FreeReqs                                                kernel automatic storage management disk ??
kfdp_getNormalFgCnt                                          (kfdp)_getNormalFgCnt                                        kernel automatic storage management disk PST ??
kghunalo                                                     (kgh)unalo                                                   kernel generic heap manager ??
kjcts_syncseq_incident_dump                                  (kjc)ts_syncseq_incident_dump                                kernel lock management communication ??
kkfdIsXlate                                                  (kkfd)IsXlate                                                kernel compile fast dataflow (PQ DFO) ??
kkoRowNumLimit_Int                                           (kko)RowNumLimit_Int                                         kernel compile optimizer ??
kkoWnRowNumLimit                                             (kko)WnRowNumLimit                                           kernel compile optimizer ??
kkoarFreeStats                                               (kkoar)FreeStats                                             kernel compile optimizer automatic (sql) reoptimisation ??
kkqgbpValidPredCB                                            (kkqgbp)ValidPredCB                                          kernel compile query  group by placement ??
kkqoreApplyFKR                                               (kkqore)ApplyFKR                                             kernel compile query  or-expansion ??
kkqstIsOneToOneFunc                                          (kkq)stIsOneToOneFunc                                        kernel compile query  ??
kkquReplSCInMWithRefCB                                       (kkqu)ReplSCInMWithRefCB                                     kernel compile query  subquery unnesting ??
kkqvtOpnInView                                               (kkqvt)OpnInView                                             kernel compile query  vector transformation ??
kokujJsonSerialize                                           (kok)ujJsonSerialize                                         kernel objects kernel side ??
kpdbCheckCommonprofileCbk                                    (kpdb)CheckCommonprofileCbk                                  kernel programmatic interface pluggable database ??
kpdbSyncCreateProfile                                        (kpdbSync)CreateProfile                                      kernel programmatic interface pluggable database DBMS_PDB.KPDBSYNC SYNC_PDB ??
krvfptai_PutTxAuditInfo                                      (krv)fptai_PutTxAuditInfo                                    kernel redo recovery ??
krvtab                                                       (krvt)ab                                                     kernel redo recovery log miner viewer support ??
krvxdsr                                                      (krvx)dsr                                                    kernel redo recovery extract ??
ksmg_estimate_nonimc_sga_size                                (ksm)g_estimate_nonimc_sga_size                              kernel service  memory ??
ktspFetchMeta1                                               (ktsp)FetchMeta1                                             kernel transaction segment management segment pagetable ??
kzekmckdcw                                                   (kz)ekmckdcw                                                 kernel security ??
kzekmckdcw_cbk                                               (kz)ekmckdcw_cbk                                             kernel security ??
opiBindReorderInfo                                           (opi)BindReorderInfo                                         oracle program interface ??
qcpiJsonSerialize                                            (qcpi)JsonSerialize                                          query compile parse interim ??
qcsSqnLegalCB                                                (qcs)SqnLegalCB                                              query compile semantic analysis (parser) ??
qergiSetFirstPartFlag                                        (qergi)SetFirstPartFlag                                      query execute rowsource granule iterator (partitioning? or PX granules?) ??
qeroiFindGranuleIter                                         (qeroi)FindGranuleIter                                       query execute rowsource extensibel indexing query component ??
qesblZero                                                    (qesbl)Zero                                                  query execute services bloom filter ??
qjsnIsDollarOnly                                             (qjsn)IsDollarOnly                                           query json ??
qjsnJsonCreatDom                                             (qjsn)JsonCreatDom                                           query json ??
qjsn_ferrh                                                   (qjsn)_ferrh                                                 query json ??
qkaGetClusteringFactor                                       (qka)GetClusteringFactor                                     query kernel allocation ??
qkaIsRTRIMRequiredForViewCol                                 (qka)IsRTRIMRequiredForViewCol                               query kernel allocation ??
qksopCheckConstOrOptWithBindInAndChains                      (qksop)CheckConstOrOptWithBindInAndChains                    query kernel sql compilter operand processing ??
qksqbCorrToNonParent                                         (qksqb)CorrToNonParent                                       query kernel sql Query compilation for query blocks ??
qksvcCloneHJPred                                             (qksvc)CloneHJPred                                           query kernel sql Virtual Column ??

(disclaimer: I can’t look at the sourcecode, which means I look at the oracle executable with normal, modern tools. This also means that there’s a of stuff that I don’t see, for example if functionality has been added inside an existing function, then that’s totally invisible to me)