Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Bacon, Bloggin’ and First Day Keynote at PASS Summit 2019!

After a flurry of release announcements at MSIgnite this week, it was time to talk about the technology at a deeper level at PASS Summit.  I love this event, which has my undying loyalty due to the sheer amount of technical focus on everything in the Microsoft data platform and of course, was not disappointed with the first day keynote on Wednesday!

We started out with a great treat of information before the keynote, just so we were prepared for what was to come in the next couple days…plus bacon!

Grant Says

Grant Fritchey started the keynote by welcoming all the attendees and talking about how impressive the event has been even though its just started!  I have to admit, it’s the first day and I could use a nap.  It’s pretty impressive with the amount of networking and technical socializing that happens at the conference.

Keynote Speaker Rohan Kumar

Rohan Kumar is the Corporate VP over Azure Data and is one of those people that is a symbol of what I see inside Microsoft everyday-  engaged, enthusiastic and fully understanding of the unified vision of the technology.  Unlike previous years, the Microsoft folks weren’t held back by waits on releases at MS Ignite, so instead, they were able to dig in deep to what the announcements meant to us as the professionals using the technology.  I. LOVE. THAT.

We’ve been waiting impatiently for SQL Server 2019, but we’re able to talk about not what is private preview or what might become part of the GA release, but what IS in the GA release!  Discussing the importance of the changes and focuses with how SQL Server 2019 will be the management layer over your datalakes, ability to use T-SQL, Python and everything in-between.

#ffffff; color: #333333; cursor: text; font-family: Georgia,'Times New Roman','Bitstream Charter',Times,serif; font-size: 16px; font-style: normal; font-variant: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-align: left; text-decoration: none; text-indent: 0px; text-transform: none; -webkit-text-stroke-width: 0px; white-space: normal; word-spacing: 0px;">Know that soon you’ll be able to run SQL databases where you may never have imagined, such as ARM, (Raspberry Pi!). Rohan then talked about the future of data with Azure SQL Database Edge, that will be the ARM deployment for Azure.  I was sitting in my chair squealing as Rohan showed us an RPI with Edge on it.  I. WANT.  </p />
</p></div>

    	  	<div class=

Cloning a schema with one line

In the world of DevOps, continuous integration and repeatable test cases, the demand for being able to

  • quickly build a suite of database objects,
  • utilise it for a series of tests,
  • then throw the objects away

has become far more common. This is one of the many great use cases for pluggable databases with all of the powerful cloning facilities available. In particular, now that you can take advantage of pluggable databases without* incurring additional license fees, there are some great opportunities there…but that is the topic for another post.

What about if the “unit of work” is not an entire pluggable database. What if we just want to clone just a schema within a pluggable database. Gone are the days where a DBA might be asked to clone a schema once per month, or even once per week. Cloning a schema is now something the developers want to do multiple times per day, as part of an automated process, and not have to involve anyone at all!  Welcome to DevOps! Smile

Unfortunately, we do not yet have a command in the Oracle database which lets you run:

create user SCOTT2 from SCOTT;

so I thought I’d throw something together which hopefully is the next best thing. We can use the PLSQL API into the Datapump feature to facilitate this. Taking advantage of a few concepts, namely:

  • Network based import,
  • The implicit database link associated with a global name,
  • Dynamically alterable external table definitions

we can build a PL/SQL procedure that is our one line clone schema resource for developers.


--
-- if you want to allow the drop user option, then the 
-- the owning schema will need the following privilege
--
-- Needless to say, you might want to wrap this within a procedure
-- within its own rights to ensure people don't drop the WRONG user
--
-- For example:
--
-- create or replace
-- procedure customised_drop_user(p_user varchar2) is
-- begin
--   if .... then
--      execute immediate 'drop user '||p_user||' cascade';
--   else
--      raise_application_error(-20000,'What the hell?!?!?');
--   end if;
-- end;
--

grant drop user to MY_USER;

drop table datapump_clone_log;

--
-- the initial file in the definition (dummy.log) must
-- exist, and the directory you are using (TEMP) must match
-- the declaration in the PLSQL proc which follows
--
create table datapump_clone_log (
     msg varchar2(4000)
)
organization external
( type oracle_loader
  default directory TEMP
  access parameters
  ( records delimited by newline
    fields terminated by ','
    missing field values are null
   ( msg )
   )
   location ('dummy.log')
) reject limit unlimited;

--
-- p_old    = existing schema
-- p_new    = target schema
-- p_drop   = whether we drop the target schema first
-- p_asynch = whether we wait or simply launch the import and return
--
-- I'd recommend p_asynch as false, because in that way, you'll get the
-- import log returned right back to your screen
--
create or replace
procedure clone_schema(
              p_old varchar2, 
              p_new varchar2, 
              p_drop_new boolean default true,
              p_asynch boolean default false
              ) is
  l_handle       number;
  l_status       ku$_status; 
  l_state        varchar2(30);
  l_link         varchar2(128);
  l_job_name     varchar2(128) := upper(p_old)||'_SCHEMA_IMP';
  l_log_file     varchar2(128) := lower(p_old)||'_import.log';
  l_default_dir  varchar2(128) := 'TEMP';
  rc             sys_refcursor;
  l_msg          varchar2(4000);
  
  procedure info(m varchar2,p_dbms_out boolean default false) is
  begin
    dbms_application_info.set_client_info(to_char(sysdate,'hh24miss')||':'||m);
    if p_dbms_out then
      dbms_output.put_line(to_char(sysdate,'hh24miss')||':'||m);
    end if;
  end;
BEGIN
  if p_drop_new then
    begin
      info('Dropping '||p_new,p_dbms_out=>true);
      --
      -- See notes about potentially wrapping this for safety
      --
      execute immediate 'drop user '||p_new||' cascade';
    exception
      when others then
        if sqlcode != -1918 then raise; end if;
    end;
  end if;
  select global_name into l_link from global_name;
  
  l_handle := dbms_datapump.open(
    operation   => 'IMPORT',
    job_mode    => 'SCHEMA',
    remote_link => l_link,
    job_name    => l_job_name);

  dbms_datapump.add_file(
    handle    => l_handle,
    filename  => l_log_file,
    directory => l_default_dir,
    filetype  => dbms_datapump.ku$_file_type_log_file,
    reusefile => 1);

  dbms_datapump.metadata_filter(
    handle => l_handle,
    name   => 'SCHEMA_EXPR',
    value  => '= '''||p_old||'''');

  dbms_datapump.metadata_remap(
    handle    => l_handle,
    name      => 'REMAP_SCHEMA',
    old_value => p_old,
    value     => p_new);

  info('Starting job',p_dbms_out=>true);
  dbms_datapump.start_job(l_handle);

  if not p_asynch then
    loop
      begin
        dbms_lock.sleep(3);
        dbms_datapump.get_status(
          handle    => l_handle,
          mask      => dbms_datapump.ku$_status_job_status,
          job_state => l_state,
          status    => l_status);
          info('l_state='||l_state);
      exception
        when others then
          if sqlcode = -31626 then
             l_state := 'COMPLETED';
          else
             raise;
          end if;
      end;
      exit when (l_state = 'COMPLETED') or (l_state = 'STOPPED');
    end loop;
    info('Final state:'||l_state,p_dbms_out=>true);
  end if;
   
  dbms_datapump.detach(l_handle);

  if not p_asynch then
    open rc for 'select msg from datapump_clone_log external modify ( location ( '''||l_log_file||''' ) )';
    loop
      fetch rc into l_msg;
      exit when rc%notfound;
      dbms_output.put_line(l_msg);
    end loop;
    close rc;
  end if;
    
end;
/
sho err

You can also get the source from my repo here.

Now lets have a look at the routine in action. This from my 18c database.


SQL> set serverout on
SQL> exec clone_schema('SCOTT','SCOTT2');
172055:Dropping SCOTT2
172057:Starting job
172232:Final state:COMPLETED
Starting "MCDONAC"."SCOTT_SCHEMA_IMP":
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 184.1 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "SCOTT2"."BIGT"                            1146660 rows
. . imported "SCOTT2"."DEPT"                                  4 rows
. . imported "SCOTT2"."EMP"                                  14 rows
. . imported "SCOTT2"."SALGRADE"                              5 rows
. . imported "SCOTT2"."BONUS"                                 0 rows
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39082: Object type PROCEDURE:"SCOTT2"."BLAH2" created with compilation warnings
ORA-39082: Object type PROCEDURE:"SCOTT2"."BLAH" created with compilation warnings
Job "MCDONAC"."SCOTT_SCHEMA_IMP" completed with 2 error(s) at Wed Nov 6 17:21:29 2019 elapsed 0 00:00:33

PL/SQL procedure successfully completed.

Note: If you want to run this on a version of the database below 18c, you can simply break the dynamic external table alteration into an ALTER statement to change the location, and then just query the external table as per normal. All of the rest of the code should work without alteration.

Enjoy!

* To see the details about the new license free options for pluggable databases, check out my video below:

APEX 19.2 : Vagrant and Docker Builds

I’m sure anyone who cares knows that APEX 19.2 was officially released on Friday. I did an upgrade of one of our development instances straight away and it worked fine. it’s subsequently gone to a bunch of other development instances. I’ll be pushing to get this out to production as quickly as possible.

Over the weekend I worked through a bunch of my GitHub stuff.

Vagrant : I’ve updated all my Vagrant builds to use APEX 19.2 and the latest versions of Tomcat 9 and OpenJDK 11. I was using newer versions of OpenJDK, but it seemed a bit silly, so I reverted back to the long term support release. I tried updating the base box to ‘bento/oracle-7.7’, but it kept giving me timeouts, so I’ve reverted back to ‘bento/oracle-7.6’ for the moment.

Docker : Same as above, I’ve updated all my Docker builds to use APEX 19.2 and the latest versions of Tomcat 9 and OpenJDK 11. I noticed oraclelinux:8-slim was behaving a little strangely. I thought it was a PATH issue, but I need to spend some time to understand what is happening. It seems you can’t run basic commands like dnf during the build phase. It’s probably something stupid I’m doing, but for now I’ve switched from oraclelinux:8-slim to oraclelinux:8. Just making that switch made everything work as expected.

My Docker builds within the company have gone through a similar process, so as I’m rolling out APEX 19.2 to the databases, I’m also switching the ORDS containers over to the new images. You gotta love containers!

I guess I’ll be working through all this again when the next version of ORDS and SQLcl drop. </p />
</p></div>

    	  	<div class=

Midlands Microsoft 365 and Azure User Group – November 2019

https://oracle-base.com/blog/wp-content/uploads/2019/09/m365-and-azure-3... 300w" sizes="(max-width: 709px) 85vw, (max-width: 909px) 67vw, (max-width: 984px) 61vw, (max-width: 1362px) 45vw, 600px" />

Last night I went to the Midlands Microsoft 365 and Azure User Group. It was co-organised by Urfaan Azhar and Lee Thatcher from Pure Technology Group, and Adrian Newton from my company.

This event was focused on Microsoft Teams, which is the MS version of Slack. If you don’t know what Slack is, you probably need to come out from under that rock…

First up was Matt Fooks speaking about “Microsoft Teams, the death of email!” We use Teams at work, but I’m a bit of a noob at it and I don’t get involved in any of the administration side of things. The session started with an overview of what Teams does and how it is organised. We spent some time speaking about culture related stuff, which may well be a bigger factor than the tech side of things with regards to transitioning from email to Teams (or Slack) as the primary communication channel. In addition to security and organisational stuff, there was a discussion of integration and applications available from Teams. The first session ended with Urfaan Azhar discussing some of the recent innovations in Teams, covered here.

After food and drinks it was Kevin McDonnell with “Using Bots in Azure/Teams for Automation”. Following the theme of the event, this was a remote presentation over Teams. </p />
</p></div>

    	  	<div class=

My PASS Summit, Wednesday, Nov. 6th Recommendations from DBAKevlar!

I thought I would share the recommendations for the next day that I’m excited about this year at PASS Summit 2019!  There are some really great sessions and as most attendees, I’ve focused the list on topics that I’m interested in or can help me in my role.

Although Tuesday afternoon and evening may kill me with how many events are all planned, there’s still a lot going on for the rest of the week!

Wednesday starts with my session with Denise McInerney, on Becoming a Technical Leader, so I don’t get to attend first, but will be presenting.  This is part of the Learning Pathway on Leadership in Tech and I’m psyched about getting a chance to share the stage with Denise.  It’s been a lot of fun planning this session out.  We’ll be in room 612 at 10am if you’re interested in joining us!

After the keynote, which there’s no way I’d miss Rohan’s keynote, I’m going to head over at 1:30pm to room 2AB for Microsoft Power BI: Business Intelligence Strategy, Vision and Roadmap with Arun Ulagaratchagan.  For my role in helping customers make the most out of their Power BI investment, I always want to know where the product is going so what I architect and solutions I give them today fit tomorrow.

At 3:15, I really would like to see Aggregations in Power BI in Tahoma 4, but I will undoubtedly go support and cheer on my fellow Learning Pathway team member, Hamish Watson as he presents Linux Fundamentals for SQL Server DBAs in room 608.  This is an incredible chance to build Linux skills from the ground up in a three part session here at Summit, so really don’t miss these sessions!

After this, I’m heading over to Speaker Idol because it’s one of my favorite events at PASS Summit.  I love the energy, the feedback from the judges and the chance to see the next great speaker.  This is the just first round, so if you miss one, look for another round in the schedule!

In the evening, there’s dinner with friends and then off to the official Game Night for Wednesday.  Thanks to Matt Cushings and Kevin Hill for organizing this!

 



Tags:  ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright ©  [My PASS Summit, Wednesday, Nov. 6th Recommendations from DBAKevlar!], All Right Reserved. 2019.

Video : Oracle REST Data Services (ORDS) : REST Enabled SQL

Today’s video is a run through the REST Enabled SQL functionality in Oracle REST Data Services (ORDS).

I wasn’t originally planning on doing this video yet, but the subject of REST Enabled SQL came up a couple of times in the last few days, so I thought I would alter my schedule.

This video is based on the following article, where you can find a lot more examples than are present in the video.

There is a lot more information about ORDS generally in these articles.

The star of today’s video is Emrah Mete, who finished up with an crazy grin. </p />
</p></div>

    	  	<div class=

APEX 19.2 Download Available

Yesterday evening Hildo Haenen tweeted that the APEX 19.2 download was available. You had to use the direct file URL and you had to have agreed to the license agreement on another download, as pointed out by Markus Hohloch, for the URL to work, but you could get the software. Of course, I wouldn’t dream of doing such a thing (I totally did…).

Today it seems the download page has been updated and you can get hold of the software in the normal way.

Happy installing/upgrading folks!

Cheers

Tim…

Update: Some people may still see the download page as a 19.1 download. I guess it’s just a matter of waiting for the CDN in your region to update…

PS. First upgrade done… </p />
</p></div>

    	  	<div class=

PASS Summit 2019 is NEXT WEEK!

I’m getting ready for SQL Saturday Oregon this weekend, (taking the train up from Eugene to Portland…) but its about time I talked about the exhilarating and exhausting event we call PASS Summit.

This year I was lucky enough to be chosen as a speaker and a blogger.  Bloggers at the conference get the opportunity to sit in special zones during keynotes and we write about the event.  We also get special access to Microsoft PMs, (although I may be the only one who works for Microsoft attending vs. running them… :)) I look forward to all of the blogger perks and recommend it to anyone who has a passion for writing about our community and tech.

I’m also a speaker again this year, presenting in two of the learning pathways sessions.  The first is the Leadership Pathway, pressing in the first session with Denise McInerney on Becoming a Technical Leader.  We’ll be presenting on Wednesday, Nov. 6th, at 10:15am in room 612.  Its an introduction session for anyone wanting to know how to be more in their career and what makes a leader over just becoming another “manager”.

My second session is the second of three in the Linux Pathway, “Empowering the SQL Server Professional with Linux Scripting” in room 612 at 1:30 on Thursday. This is going to be a hands on lab in 70 minutes, so come prepared!!

Bring your laptop and download the scripts from my Github repository and you can play along from a Linux console.  I will be teaching best practices and the sample scripts and VI cheat sheet should allow those in the session to get a full 101 on Linux scripting with BASH.  It’s a not to miss, but so are the first and third session in this series-  Hamish Watson will be teaching Linux Fundamentals at 3:15 on Tuesday in room 608 and Randolph West will be presenting Managing and Monitoring SQL Server from the Linux Command Line at 2pm on Friday in room 612.

The three of these sessions should give you a strong base to work from if Linux is in scope for your goals for this year at Summit.  I plan on focusing on a few goals for learning for myself, including some Advanced Analytics, Azure Data Studio, Custom Visuals in Power BI and an Azure DevOps session or two.

As the wayward president of the Denver SQL Server Group, there are community meetings and fellow regional members to meet as part of the PASS Community Zone.  I’ll have a few meetings to attend and discussions around what we’re doing well and what we can do better in the community.

There are also a ton of other events, such as WIT luncheon, WIT happy hour, game night, karaoke night, dinners, donuts and let’s just say I’ll be heading home on Friday evening so I can recover for a couple days… </p />
</p></div>

    	  	<div class=

Oracle internal data dictionary oddity

This blogpost is about an inconsistency I found in the X$ tables X$KQFTA and X$KQFCO. This is very specific. If you don’t care about that, you can skip this post.

The Oracle database’s “dynamic performance views” are views that are prefixed with “GV$” and “V$”. The “G” with “GV$” stands for “global” and gets you the results from its “V$” equivalent for all instances. In most cases, but that’s not the purpose of this blog. In most cases, the “V$” version simply is the “GV$” view with inst_id (instance id, used in RAC to specify the instance id) set to the current instance. I’ll refer to both simply as “V$” for simplicity.

The “V$” views are mostly build on top of “X$” tables. The “X$” tables are Oracle internal tables, and not officially supported, therefore you should use the “V$” views.

One of the things that certain “X$” tables expose is information about the “X$” tables and their columns theirselves. These “X$” tables are visible in “X$KQFTA” (kernel query fixed tables tables) for the “X$” tables, and “X$KQFCO” (kernel query fixed tables columns) for the columns of the “X$” tables. I learned about these tables a long time ago from Kyle Hailey, Anjo Kolk and James Morle regarding direct memory attachment for wait events, the reason being the ability to find the memory address for certain “X$” tables to fetch the data these hold directly. We are talking about the 1990s here. In fact, Kyle later put the history of how he learned about it in the “Oracle insights: tales of the oaktable” book.

I gather information about every PSU of every Oracle database version on linux once it comes out. This is how I am able to produce the posts about the difference between a current and a previous PSU. One of the things that I gather is information about X$ tables and their columns. In order to obtain the columns, I use X$KQFTA and X$KQFCO and join these in the way that is publicly known:

select t.kqftanam, c.kqfconam
from x$kqfta t, x$kqfco c
where t.inst_id = 1
and c.inst_id = 1
and t.indx = c.kqfcotab;

And this worked well…

…until I produced a difference report of versions 12.1.0.2.191015 and 12.1.0.2.190716, where the report reported the following difference:

x$ tables columns unique to 12.1.0.2.190716 versus 12.1.0.2.191015

NAME                                                                                                 COLUMN_NAME
---------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------
X$KQFTVRTTST0                                                                                        ADDR
X$KQFTVRTTST0                                                                                        CHGROWID
X$KQFTVRTTST0                                                                                        CHGROWOP
X$KQFTVRTTST0                                                                                        COL0_0
X$KQFTVRTTST0                                                                                        COL0_1
X$KQFTVRTTST0                                                                                        COL0_2
X$KQFTVRTTST0                                                                                        COL0_3
X$KQFTVRTTST0                                                                                        COL0_4
...and so on...

This could happen, these are columns that seem to have vanished with 12.1.0.2.191015. However, I decided to check it, although I haven’t got access to the 12.1.0.2.191015 PSU, because it’s a PSU version only available for paid extended support.

I looked at the columns of X$KQFTVRTST0 in my current database, which happens to be an ancient 11.2.0.2 database. However, much to my surprise, this gave me radically different columns:

SYS@o112 AS SYSDBA> desc X$KQFTVRTTST0
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 COLA                                                                                                                       VARCHAR2(64)
 COLB                                                                                                                       NUMBER

Okay, something weird is going on here.

First let’s look in which versions X$KQFTVRTTST0 is present:

ORAVER@o112 > @x_table_in_versions.sql

DATABASE_VERSION     NAME
-------------------- --------------------------------------------------------------------------------------------------------------------------------
11.2.0.1.0           X$KQFTVRTTST0
11.2.0.2.0           X$KQFTVRTTST0
11.2.0.2.12          X$KQFTVRTTST0
11.2.0.3.0           X$KQFTVRTTST0
11.2.0.3.15          X$KQFTVRTTST0
11.2.0.4.0           X$KQFTVRTTST0
11.2.0.4.1           X$KQFTVRTTST0
11.2.0.4.160119      X$KQFTVRTTST0
11.2.0.4.160419      X$KQFTVRTTST0
11.2.0.4.160719      X$KQFTVRTTST0
11.2.0.4.161018      X$KQFTVRTTST0
11.2.0.4.170418      X$KQFTVRTTST0
11.2.0.4.170718      X$KQFTVRTTST0
11.2.0.4.170814      X$KQFTVRTTST0
11.2.0.4.180116      X$KQFTVRTTST0
11.2.0.4.180417      X$KQFTVRTTST0
11.2.0.4.180717      X$KQFTVRTTST0
11.2.0.4.190115      X$KQFTVRTTST0
11.2.0.4.191015      X$KQFTVRTTST0
11.2.0.4.2           X$KQFTVRTTST0
11.2.0.4.3           X$KQFTVRTTST0
11.2.0.4.4           X$KQFTVRTTST0
11.2.0.4.5           X$KQFTVRTTST0
11.2.0.4.6           X$KQFTVRTTST0
11.2.0.4.7           X$KQFTVRTTST0
11.2.0.4.8           X$KQFTVRTTST0
12.1.0.2.0           X$KQFTVRTTST0
12.1.0.2.1           X$KQFTVRTTST0
12.1.0.2.160119      X$KQFTVRTTST0
12.1.0.2.160419      X$KQFTVRTTST0
12.1.0.2.160719      X$KQFTVRTTST0
12.1.0.2.161018      X$KQFTVRTTST0
12.1.0.2.170117      X$KQFTVRTTST0
12.1.0.2.170418      X$KQFTVRTTST0
12.1.0.2.170718      X$KQFTVRTTST0
12.1.0.2.170814      X$KQFTVRTTST0
12.1.0.2.171017      X$KQFTVRTTST0
12.1.0.2.180116      X$KQFTVRTTST0
12.1.0.2.180417      X$KQFTVRTTST0
12.1.0.2.181016      X$KQFTVRTTST0
12.1.0.2.190115      X$KQFTVRTTST0
12.1.0.2.190416      X$KQFTVRTTST0
12.1.0.2.190716      X$KQFTVRTTST0
12.1.0.2.191015      X$KQFTVRTTST0
12.1.0.2.3           X$KQFTVRTTST0
12.1.0.2.4           X$KQFTVRTTST0
12.1.0.2.5           X$KQFTVRTTST0
12.2.0.1.0           X$KQFTVRTTST0
12.2.0.1.170620      X$KQFTVRTTST0
12.2.0.1.170718      X$KQFTVRTTST0
12.2.0.1.170814      X$KQFTVRTTST0
12.2.0.1.171017      X$KQFTVRTTST0
12.2.0.1.180116      X$KQFTVRTTST0
12.2.0.1.180417      X$KQFTVRTTST0
12.2.0.1.180717      X$KQFTVRTTST0
12.2.0.1.181016      X$KQFTVRTTST0
12.2.0.1.190115      X$KQFTVRTTST0
12.2.0.1.190416      X$KQFTVRTTST0
12.2.0.1.190716      X$KQFTVRTTST0
12.2.0.1.191015      X$KQFTVRTTST0
18.3                 X$KQFTVRTTST0
18.4                 X$KQFTVRTTST0
18.5                 X$KQFTVRTTST0
18.6                 X$KQFTVRTTST0
18.7                 X$KQFTVRTTST0
18.8                 X$KQFTVRTTST0
19.2                 X$KQFTVRTTST0
19.3                 X$KQFTVRTTST0
19.4                 X$KQFTVRTTST0
19.5                 X$KQFTVRTTST0

So, X$KQFVRTTST0 appeared with 11.2.0.1. Now let’s take a look at how the columns look like in X$KQFVRTTST0 looks in version 11.2.0.1:

ORAVER@o112 > @x_table_cols_in_versions.sql

DATABASE_VERSION	       TABLE_NAME		      COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL3_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL2_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL1_0
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_9
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_8
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_7
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_6
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_5
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_4
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_3
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_2
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_1
11.2.0.1.0		       X$KQFTVRTTST0		      COL0_0
11.2.0.1.0		       X$KQFTVRTTST0		      CHGROWOP
11.2.0.1.0		       X$KQFTVRTTST0		      CHGROWID
11.2.0.1.0		       X$KQFTVRTTST0		      COL_OPERATION
11.2.0.1.0		       X$KQFTVRTTST0		      INST_ID
11.2.0.1.0		       X$KQFTVRTTST0		      INDX
11.2.0.1.0		       X$KQFTVRTTST0		      ADDR

These are the columns which were shown in the difference report that said they were removed. Now let’s look at the columns in version 19.5:

ORAVER@o112 > @x_table_cols_in_versions.sql

DATABASE_VERSION               TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ----------------------------------------
19.5                           X$KQFTVRTTST0                  COL3_9
19.5                           X$KQFTVRTTST0                  COL3_8
19.5                           X$KQFTVRTTST0                  COL3_7
19.5                           X$KQFTVRTTST0                  COL3_6
19.5                           X$KQFTVRTTST0                  COL3_5
19.5                           X$KQFTVRTTST0                  COL3_4
19.5                           X$KQFTVRTTST0                  COL3_3
19.5                           X$KQFTVRTTST0                  COL3_2
19.5                           X$KQFTVRTTST0                  COL3_1
19.5                           X$KQFTVRTTST0                  COL3_0
19.5                           X$KQFTVRTTST0                  COL2_9
19.5                           X$KQFTVRTTST0                  COL2_8
19.5                           X$KQFTVRTTST0                  COL2_7
19.5                           X$KQFTVRTTST0                  COL2_6
19.5                           X$KQFTVRTTST0                  COL2_5
19.5                           X$KQFTVRTTST0                  COL2_4
19.5                           X$KQFTVRTTST0                  COL2_3
19.5                           X$KQFTVRTTST0                  COL2_2
19.5                           X$KQFTVRTTST0                  COL2_1
19.5                           X$KQFTVRTTST0                  COL2_0
19.5                           X$KQFTVRTTST0                  COL1_9
19.5                           X$KQFTVRTTST0                  COL1_8
19.5                           X$KQFTVRTTST0                  COL1_7
19.5                           X$KQFTVRTTST0                  COL1_6
19.5                           X$KQFTVRTTST0                  COL1_5
19.5                           X$KQFTVRTTST0                  COL1_4
19.5                           X$KQFTVRTTST0                  COL1_3
19.5                           X$KQFTVRTTST0                  COL1_2
19.5                           X$KQFTVRTTST0                  COL1_1
19.5                           X$KQFTVRTTST0                  COL1_0
19.5                           X$KQFTVRTTST0                  COL0_9
19.5                           X$KQFTVRTTST0                  COL0_8
19.5                           X$KQFTVRTTST0                  COL0_7
19.5                           X$KQFTVRTTST0                  COL0_6
19.5                           X$KQFTVRTTST0                  COL0_5
19.5                           X$KQFTVRTTST0                  COL0_4
19.5                           X$KQFTVRTTST0                  COL0_3
19.5                           X$KQFTVRTTST0                  COL0_2
19.5                           X$KQFTVRTTST0                  COL0_1
19.5                           X$KQFTVRTTST0                  COL0_0
19.5                           X$KQFTVRTTST0                  CHGROWOP
19.5                           X$KQFTVRTTST0                  CHGROWID
19.5                           X$KQFTVRTTST0                  COL_OPERATION
19.5                           X$KQFTVRTTST0                  CON_ID
19.5                           X$KQFTVRTTST0                  INST_ID
19.5                           X$KQFTVRTTST0                  INDX
19.5                           X$KQFTVRTTST0                  ADDR

These are exactly the same! So the columns for X$KQFTVRTTST0 that I obtained from the database are the same for all versions in my data, but when I look in an actual database using ‘describe’ I see ADDR, INDX, INST_ID and COLA and COLB. So my data seems to be wrong!

My first reaction was to change the columns for X$KQFTVRTST0 for every version to correct this. But then I realised that I had to investigate this deeper to understand the actual problem, there might be more X$ tables involved.

The first thing to do, is to see if I can reproduce the issue in my database. This can be quite easily be done by describing X$KQFTVRTTST0 to see the columns, and then query these using the above query that demonstrates the join between X$KQFTA and X$KQFCO by adding a filter on ‘X$KQFTVRTST0’:

SYS@o112 AS SYSDBA> desc X$KQFTVRTTST0
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 COLA                                                                                                                       VARCHAR2(64)
 COLB                                                                                                                       NUMBER
SYS@o112 AS SYSDBA> l
  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and kqftanam = 'X$KQFTVRTTST0'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KQFTVRTTST0                  ADDR
X$KQFTVRTTST0                  INDX
X$KQFTVRTTST0                  INST_ID
X$KQFTVRTTST0                  COL_OPERATION
X$KQFTVRTTST0                  CHGROWID
X$KQFTVRTTST0                  CHGROWOP
X$KQFTVRTTST0                  COL0_0
X$KQFTVRTTST0                  COL0_1
X$KQFTVRTTST0                  COL0_2
X$KQFTVRTTST0                  COL0_3
X$KQFTVRTTST0                  COL0_4
X$KQFTVRTTST0                  COL0_5
X$KQFTVRTTST0                  COL0_6
X$KQFTVRTTST0                  COL0_7
X$KQFTVRTTST0                  COL0_8
X$KQFTVRTTST0                  COL0_9
X$KQFTVRTTST0                  COL1_0
X$KQFTVRTTST0                  COL1_1
X$KQFTVRTTST0                  COL1_2
X$KQFTVRTTST0                  COL1_3
X$KQFTVRTTST0                  COL1_4
X$KQFTVRTTST0                  COL1_5
X$KQFTVRTTST0                  COL1_6
X$KQFTVRTTST0                  COL1_7
X$KQFTVRTTST0                  COL1_8
X$KQFTVRTTST0                  COL1_9
X$KQFTVRTTST0                  COL2_0
X$KQFTVRTTST0                  COL2_1
X$KQFTVRTTST0                  COL2_2
X$KQFTVRTTST0                  COL2_3
X$KQFTVRTTST0                  COL2_4
X$KQFTVRTTST0                  COL2_5
X$KQFTVRTTST0                  COL2_6
X$KQFTVRTTST0                  COL2_7
X$KQFTVRTTST0                  COL2_8
X$KQFTVRTTST0                  COL2_9
X$KQFTVRTTST0                  COL3_0
X$KQFTVRTTST0                  COL3_1
X$KQFTVRTTST0                  COL3_2
X$KQFTVRTTST0                  COL3_3
X$KQFTVRTTST0                  COL3_4
X$KQFTVRTTST0                  COL3_5
X$KQFTVRTTST0                  COL3_6
X$KQFTVRTTST0                  COL3_7
X$KQFTVRTTST0                  COL3_8
X$KQFTVRTTST0                  COL3_9

Bingo! Describe tells me about COLA and COLB, which is the actual situation, and querying X$KQFTA and X$KQFCO gives me different, wrong columns.

First let’s investigate if we can find the correct columns in X$KQFCO, and see what the difference is between X$KQFTA.INDX and X$KQFCO.KQFCOTAB:

SYS@o112 AS SYSDBA> select indx from x$kqfta where kqftanam = 'X$KQFTVRTTST0';

      INDX
----------
       841

SYS@o112 AS SYSDBA> select kqfcotab from x$kqfco where kqfconam = 'COLA';

  KQFCOTAB
----------
       842

Aha! So the join is off by one! So, that must mean that if I decrease KQFCOTAB by one, I do get the correct output:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and kqftanam = 'X$KQFTVRTTST0'
  7  /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KQFTVRTTST0                  ADDR
X$KQFTVRTTST0                  INDX
X$KQFTVRTTST0                  INST_ID
X$KQFTVRTTST0                  COLA
X$KQFTVRTTST0                  COLB

Bingo! The next thing to do is see if this is only a problem for X$KQFTVRTTST0, or a bigger problem. Let’s take the next table based on X$KQFTA.INDX, and see if this shows the same problem:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 842;

KQFTANAM
------------------------------
X$DIAG_ADR_CONTROL
SYS@o112 AS SYSDBA> desc X$DIAG_ADR_CONTROL
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 ADR_PATH_IDX                                                                                                               VARCHAR2(445)
 ADR_HOME                                                                                                                   VARCHAR2(445)
 ADRID                                                                                                                      NUMBER
 SHORTP_POLICY                                                                                                              NUMBER
 LONGP_POLICY                                                                                                               NUMBER
 LAST_MOD_TIME                                                                                                              TIMESTAMP(9) WITH TIME ZONE
 LAST_AUTOPRG_TIME                                                                                                          TIMESTAMP(9) WITH TIME ZONE
 LAST_MANUPRG_TIME                                                                                                          TIMESTAMP(9) WITH TIME ZONE
 ADRDIR_VERSION                                                                                                             NUMBER
 ADRSCHM_VERSION                                                                                                            NUMBER
 ADRSCHMV_SUMMARY                                                                                                           NUMBER
 ADRALERT_VERSION                                                                                                           NUMBER
 CREATE_TIME                                                                                                                TIMESTAMP(9) WITH TIME ZONE
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1   select t.kqftanam, c.kqfconam
  2   from x$kqfta t, x$kqfco c
  3   where t.inst_id = 1
  4   and c.inst_id = 1
  5   and t.indx = c.kqfcotab
  6*  and kqftanam = 'X$DIAG_ADR_CONTROL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_ADR_CONTROL             ADDR
X$DIAG_ADR_CONTROL             INDX
X$DIAG_ADR_CONTROL             INST_ID
X$DIAG_ADR_CONTROL             COLA
X$DIAG_ADR_CONTROL             COLB

Clearly the columns are wrong, these are the columns of X$KQFTVRTTST0! Let’s subtract X$KQFCO.KQFCOTAB by one:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1   select t.kqftanam, c.kqfconam
  2   from x$kqfta t, x$kqfco c
  3   where t.inst_id = 1
  4   and c.inst_id = 1
  5   and t.indx = c.kqfcotab-1
  6*  and kqftanam = 'X$DIAG_ADR_CONTROL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_ADR_CONTROL             ADDR
X$DIAG_ADR_CONTROL             INDX
X$DIAG_ADR_CONTROL             INST_ID
X$DIAG_ADR_CONTROL             ADR_PATH_IDX
X$DIAG_ADR_CONTROL             ADR_HOME
X$DIAG_ADR_CONTROL             ADRID
X$DIAG_ADR_CONTROL             SHORTP_POLICY
X$DIAG_ADR_CONTROL             LONGP_POLICY
X$DIAG_ADR_CONTROL             LAST_MOD_TIME
X$DIAG_ADR_CONTROL             LAST_AUTOPRG_TIME
X$DIAG_ADR_CONTROL             LAST_MANUPRG_TIME
X$DIAG_ADR_CONTROL             ADRDIR_VERSION
X$DIAG_ADR_CONTROL             ADRSCHM_VERSION
X$DIAG_ADR_CONTROL             ADRSCHMV_SUMMARY
X$DIAG_ADR_CONTROL             ADRALERT_VERSION
X$DIAG_ADR_CONTROL             CREATE_TIME

And this is correct again!

Now let’s verify the highest X$KQFTA.INDX number in the version of this database:

SYS@o112 AS SYSDBA> select max(indx) from x$kqfta;

 MAX(INDX)
----------
       927

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 927;

KQFTANAM
------------------------------
X$DIAG_VTEST_EXISTS

SYS@o112 AS SYSDBA> desc X$DIAG_VTEST_EXISTS
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 ADR_PATH_IDX                                                                                                               VARCHAR2(445)
 ADR_HOME                                                                                                                   VARCHAR2(445)
 PROBLEM_ID                                                                                                                 NUMBER
 PROBLEM_KEY                                                                                                                VARCHAR2(552)
 FIRST_INCIDENT                                                                                                             NUMBER
 FIRSTINC_TIME                                                                                                              TIMESTAMP(9) WITH TIME ZONE
 LAST_INCIDENT                                                                                                              NUMBER
 LASTINC_TIME                                                                                                               TIMESTAMP(9) WITH TIME ZONE
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and kqftanam = 'X$DIAG_VTEST_EXISTS'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_VTEST_EXISTS            ADDR
X$DIAG_VTEST_EXISTS            INDX
X$DIAG_VTEST_EXISTS            INST_ID
X$DIAG_VTEST_EXISTS            ADR_PATH_IDX
X$DIAG_VTEST_EXISTS            ADR_HOME
X$DIAG_VTEST_EXISTS            INCIDENT_ID
X$DIAG_VTEST_EXISTS            PROBLEM_ID
X$DIAG_VTEST_EXISTS            CREATE_TIME
X$DIAG_VTEST_EXISTS            CLOSE_TIME
X$DIAG_VTEST_EXISTS            STATUS
X$DIAG_VTEST_EXISTS            FLAGS
X$DIAG_VTEST_EXISTS            FLOOD_CONTROLLED
X$DIAG_VTEST_EXISTS            ERROR_FACILITY
X$DIAG_VTEST_EXISTS            ERROR_NUMBER
X$DIAG_VTEST_EXISTS            ERROR_ARG1
X$DIAG_VTEST_EXISTS            ERROR_ARG2
X$DIAG_VTEST_EXISTS            ERROR_ARG3
X$DIAG_VTEST_EXISTS            ERROR_ARG4
X$DIAG_VTEST_EXISTS            ERROR_ARG5
X$DIAG_VTEST_EXISTS            ERROR_ARG6
X$DIAG_VTEST_EXISTS            ERROR_ARG7
X$DIAG_VTEST_EXISTS            ERROR_ARG8
X$DIAG_VTEST_EXISTS            SIGNALLING_COMPONENT
X$DIAG_VTEST_EXISTS            SIGNALLING_SUBCOMPONENT
X$DIAG_VTEST_EXISTS            SUSPECT_COMPONENT
X$DIAG_VTEST_EXISTS            SUSPECT_SUBCOMPONENT
X$DIAG_VTEST_EXISTS            ECID
X$DIAG_VTEST_EXISTS            IMPACT
X$DIAG_VTEST_EXISTS            IMPACT_STR
X$DIAG_VTEST_EXISTS            STATUS_STR
X$DIAG_VTEST_EXISTS            ERROR_ARG9
X$DIAG_VTEST_EXISTS            ERROR_ARG10
X$DIAG_VTEST_EXISTS            ERROR_ARG11
X$DIAG_VTEST_EXISTS            ERROR_ARG12

So that’s wrong, let’s try subtracting by one:

SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and kqftanam = 'X$DIAG_VTEST_EXISTS'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$DIAG_VTEST_EXISTS            ADDR
X$DIAG_VTEST_EXISTS            INDX
X$DIAG_VTEST_EXISTS            INST_ID
X$DIAG_VTEST_EXISTS            ADR_PATH_IDX
X$DIAG_VTEST_EXISTS            ADR_HOME
X$DIAG_VTEST_EXISTS            PROBLEM_ID
X$DIAG_VTEST_EXISTS            PROBLEM_KEY
X$DIAG_VTEST_EXISTS            FIRST_INCIDENT
X$DIAG_VTEST_EXISTS            FIRSTINC_TIME
X$DIAG_VTEST_EXISTS            LAST_INCIDENT
X$DIAG_VTEST_EXISTS            LASTINC_TIME

And correct again!

So at least starting from X$KQFTA.INDX = 841 up to the highest INDX number, 927, X$KQFCO.KQFCOTAB is off by one.

Starting from there, I decided to test INDX = 1, which was consistent with KQFCOTAB. Then I decided to go down in INDX number starting from 841 being the number of X$KQFTVRTTST0. In my database number 840 is X$KTCNQROW, which was off by one, then I found something weird:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 839;

KQFTANAM
------------------------------
X$SKGXP_MISC

SYS@o112 AS SYSDBA> desc X$SKGXP_MISC
ERROR:
ORA-00942: table or view does not exist
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1      select t.kqftanam, c.kqfconam
  2      from x$kqfta t, x$kqfco c
  3      where t.inst_id = 1
  4      and c.inst_id = 1
  5      and t.indx = c.kqfcotab
  6*    and kqftanam = 'X$SKGXP_MISC'
SYS@o112 AS SYSDBA> /

no rows selected

So the X$ view X$SKGXP_MISC exists in X$KQFTA, but doesn’t exist in reality, nor in X$KQFCO. Please mind this is a separate, different issue from the X$KQFCO.KQFCOTAB inconsistency this blogpost is about. My current thinking is the issue of existing in X$KQFTA and not queryable and (rightfully because of that), not available in X$KQFCO, might be either just a screwup/bug, or the X$ tables being reserved in the X$ table array, but not actually created, maybe because of setting during compilation. Just a hunch, I don’t know.

The same issue of being visible in X$KQFTA and not existing is true for:
838, X$SKGXP_CONNECTION
837, X$SKGXP_PORT
836, X$KSXP_STATS

INDX number 835 is X$KSXPTESTTBL. This table has the X$KQFCO.KQFCOTAB off by one issue:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 835;

KQFTANAM
------------------------------
X$KSXPTESTTBL
SYS@o112 AS SYSDBA> desc X$KSXPTESTTBL
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 KSXPTESTF1                                                                                                                 NUMBER
 KSXPTESTF2                                                                                                                 VARCHAR2(10)
 KSXPTESTF3                                                                                                                 NUMBER
 KSXPTESTF4                                                                                                                 NUMBER
 KSXPTESTF5                                                                                                                 RAW(8)
 KSXPTESTF6                                                                                                                 DATE
 KSXPTESTF7                                                                                                                 TIMESTAMP(3)
 KSXPTESTF8                                                                                                                 TIMESTAMP(3) WITH TIME ZONE
 KSXPTESTF9                                                                                                                 VARCHAR2(10)
 KSXPTESTF10                                                                                                                RAW(2)
 KSXPTESTF11                                                                                                                RAW(10)
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and t.kqftanam = 'X$KSXPTESTTBL'
SYS@o112 AS SYSDBA> /

no rows selected
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab-1
  6* and t.kqftanam = 'X$KSXPTESTTBL'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$KSXPTESTTBL                  ADDR
X$KSXPTESTTBL                  INDX
X$KSXPTESTTBL                  INST_ID
X$KSXPTESTTBL                  KSXPTESTF1
X$KSXPTESTTBL                  KSXPTESTF2
X$KSXPTESTTBL                  KSXPTESTF3
X$KSXPTESTTBL                  KSXPTESTF4
X$KSXPTESTTBL                  KSXPTESTF5
X$KSXPTESTTBL                  KSXPTESTF6
X$KSXPTESTTBL                  KSXPTESTF7
X$KSXPTESTTBL                  KSXPTESTF8
X$KSXPTESTTBL                  KSXPTESTF9
X$KSXPTESTTBL                  KSXPTESTF10
X$KSXPTESTTBL                  KSXPTESTF11

After X$KSXPTESTTBL, INDX number 834 is X$CELL_NAME:

SYS@o112 AS SYSDBA> select kqftanam from x$kqfta where indx = 834;

KQFTANAM
------------------------------
X$CELL_NAME
SYS@o112 AS SYSDBA> desc X$CELL_NAME
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 ADDR                                                                                                                       RAW(8)
 INDX                                                                                                                       NUMBER
 INST_ID                                                                                                                    NUMBER
 CELLPATH_CELLROW                                                                                                           VARCHAR2(400)
 CELLHASHVAL_CELLROW                                                                                                        NUMBER
SYS@o112 AS SYSDBA> ed
Wrote file afiedt.buf

  1  select t.kqftanam, c.kqfconam
  2  from x$kqfta t, x$kqfco c
  3  where t.inst_id = 1
  4  and c.inst_id = 1
  5  and t.indx = c.kqfcotab
  6* and t.kqftanam = 'X$CELL_NAME'
SYS@o112 AS SYSDBA> /

KQFTANAM                       KQFCONAM
------------------------------ ------------------------------
X$CELL_NAME                    ADDR
X$CELL_NAME                    INDX
X$CELL_NAME                    INST_ID
X$CELL_NAME                    CELLPATH_CELLROW
X$CELL_NAME                    CELLHASHVAL_CELLROW

And with X$CELL_NAME the X$KQFTA.INDX=X$KQFCO.KQFCOTAB link is restored.

So, this means that starting with X$KSXPTESTTBL the X$KQFCO.KQFCOTAB number is off by one. In fact, I tested version 18 (18.4), and found that the value X$KQFTA.INDX is 1217, so much higher, but still with X$KSXPTESTTBL the inconsistency of X$KQFCO.KQFCOTAB being one too high to correctly join with X$KQFTA.INDX.

Conclusion.
This means that based on my current investigations, it seems that starting from Oracle database version 11.2.0.1 up to version 19 and current PSU, starting with the X$KQFTA.INDX value of the table X$KSXPTESTTBL in X$KQFTA, the X$KQFCO.KQFCOTAB value needs to be subtracted by one to get the correct fields from X$KQFCO.KQFCOTAB to join to X$KQFTA.INDX.

A lot of X$ tables are not impacted, the majority of the tables have an X$KQFTA.INDX value lower than X$KSXPTESTTBL, but there are a reasonable amount of tables that are impacted, a lot of them related to ADR (starting with X$DIAG).

The reason for showing an additional oddity with X$SKGXP_MISC, X$SKGXP_CONNECTION, X$SKGXP_PORT and X$KSXP_STATS is that the X$ table where the join problem starts is X$KSXPTESTTBL. All these tables have something to do with cross instance IPC communication (skgxp/ksxp), and a lot of them have the weird property that they don’t exist whilst are visible in X$KQFTA, this might have something to do with X$KQFCO.KQFCOTAB being off by one. Of course this is something I can’t check, because it requires source code access, but I thought it would be good to mention.

It’s interesting to see that the join of X$KQFTA.INDX to X$KQFCO.KQFCOTAB is present in Oracle’s data dictionary with the view V$INDEXED_FIXED_COLUMN:

SQL> select view_definition from v$fixed_view_definition where view_name = 'GV$INDEXED_FIXED_COLUMN';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select c.inst_id,kqftanam, kqfcoidx, kqfconam, kqfcoipo, c.con_id  from x$kqfco
c, x$kqfta t where t.indx = c.kqfcotab and kqfcoidx != 0

Which by now should be clear is not correct for X$ tables with an X$KQFTA.INDX number equal or higher than X$KSXPTESTTBL.

IOT Hash

It’s another of my double-entendre titles. The optimizer can turn a hash join involving an index-organized table into a real performance disaster (though you may have to help it along the way by using a silly definition for your primary key columns). This post was inspired by a question posted on the Oracle Developer Community forum recently so the table and column names I’ve used in my model reflect (almost, with a few corrections) the names used in the post.

We start with a simple requirement expressed through the following SQL:


rem
rem     Script:         iot_hash.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Nov 2019
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem

insert
        /*+
                qb_name(insert)
        */
into t_iot(
        id, inst_id, nr_time,
        o_time, st, null_col, svname
)
select
        /*+
                qb_name(main)
                unnest(@subq)
                leading(@sel$a93afaed apar@main ob@subq)
                use_hash(@sel$a93afaed ob@subq)
                swap_join_inputs(@sel$a93afaed ob@subq)
                index_ss_asc(@sel$a93afaed ob@subq (t_iot.st t_iot.inst_id t_iot.svname))a
        */
        apar.id,
        'UP',
        to_date('2019-10-24','yyyy-mm-dd'),
        to_date('1969-12-31','yyyy-mm-dd'),
        'IDLE',
        null,
        'tkt007.jj.bb.com'
from
        t_base apar
where
        apar.id not in (
                select
                        /*+
                                qb_name(subq)
                        */
                        id
                from
                        t_iot ob
                where
                        inst_id = 'UP'
        )
and     nvl(apar.gp_nm,'UA') = 'UA'
and     rownum <= 5000
/

The requirement is simple – insert into table t_iot a set of values dictated by a subset of the rows in table t_base if they do not already exist in t_iot. To model the issue that appeared I’ve had to hint the SQL to get the following plan (which I pulled from memory after enabling rowsource execution stats):


---------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |             |      1 |        |   296 (100)|      0 |00:00:00.03 |     788 |    148 |       |       |          |
|   1 |  LOAD TABLE CONVENTIONAL | T_IOT       |      1 |        |            |      0 |00:00:00.03 |     788 |    148 |       |       |          |
|*  2 |   COUNT STOPKEY          |             |      1 |        |            |    100 |00:00:00.03 |      99 |     91 |       |       |          |
|*  3 |    HASH JOIN RIGHT ANTI  |             |      1 |    100 |   296   (2)|    100 |00:00:00.03 |      99 |     91 |    14M|  1843K|   15M (0)|
|*  4 |     INDEX SKIP SCAN      | T_IOT_STATE |      1 |  12614 |   102   (0)|  10000 |00:00:00.01 |      92 |     91 |       |       |          |
|*  5 |     TABLE ACCESS FULL    | T_BASE      |      1 |    100 |     2   (0)|    100 |00:00:00.01 |       7 |      0 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=5000)
   3 - access("APAR"."ID"="ID")
   4 - access("INST_ID"='UP')
       filter("INST_ID"='UP')
   5 - filter(NVL("APAR"."GP_NM",'UA')='UA')

The optimizer has unnested (as hinted) the subquery and converted it to an anti-join using a right hash anti-join. Take a look at the Used-mem for the hash join – would it surprise you to learn that the total size of the (not compressed in any way) IOT, and all its indexes, and the t_base table together total less than 4 MB. Something dramatically awful has happened in the hash join to generated a requirement of 14MB. (In the case of the OP this appeared as an unexpected 5GB written to the temporary tablespace.)

Before I address the source of the high memory usage, take a close look at the Predicate Information, particularly operation 3, and ask yourself what the definition of index t_iot_state might be. The predicate joins t_base.id to t_iot.id, and here’s the code to create both tables and all the indexes.

create table t_iot (
        nr_time         timestamp,
        id              varchar2(1024),
        inst_id         varchar2(200),
        o_time          timestamp,
        st              varchar2(200),
        null_col        varchar2(100),
        svname          varchar2(200),
        constraint t_iot_pk primary key(nr_time, id, inst_id)
)
organization index
/

insert into t_iot
select
        sysdate,
        dbms_random.string('l',10),
        'UP',
        sysdate,
        'IDLE',
        null,
        rpad('x',25,'x')
from
        all_objects
where
        rownum <= 1e4 -- > hint to avoid wordpress format issue
/

create index t_iot_state on t_iot(st, inst_id, svname); 
create index idx2        on t_iot(id, inst_id, svname);

create table t_base(
        id              varchar2(400) not null,
        gp_nm           varchar2(200)
)
/

insert into t_base
select
        dbms_random.string('l',10),
        'UA'
from
        all_objects
where
        rownum <= 100 -- > hint to avoid wordpress format issue
;


begin
        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 't_iot',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );

        dbms_stats.gather_table_stats(
                ownname     => null,
                tabname     => 't_base',
                cascade     => true,
                method_opt  => 'for all columns size 1'
        );
end;
/


The index t_iot_state that Oracle has used in the hash join is defined on the columns (st, inst_id, svname) – so the predicate is doing a comparison with a column that’s not in the index! At least, it’s not visibly declared in the index; but this is a secondary index on an IOT, and IOTs don’t have “normal” rowids, the rowid in a secondary index is the value of the primary key (plus a “block guess”). So the columns in the index (even though not declared in the index) are: (st, inst_id, svname, {nr_time, id, inst_id, blockguess}). So this index does supply the required id column.

Side note: you’ll see in the list of columns above that inst_id appears twice. In fact (since Oracle 9, I think) the code to handle secondary indexes has been smart enough to avoid this duplication. If the secondary index contains columns from the primary key then the “rowid” doesn’t store those columns, the code knows how to construct the primaryh key value from the stored PK columns combined with the needed columns from the index entry. This can make IOTs a very nice choice of implementation for “intersection” tables that are used to represent many-to-many joins between two other tables.

Unfortunately this “rowid” is the explanation for the massive memory demand. Take a look at the “Column Projection Information” for my execution plan:


Column Projection Information (identified by operation id):
-----------------------------------------------------------
   2 - "APAR"."ID"[VARCHAR2,400], "APAR"."GP_NM"[VARCHAR2,200], ROWNUM[8]
   3 - (#keys=1) "APAR"."ID"[VARCHAR2,400], "APAR"."GP_NM"[VARCHAR2,200]
   4 - "OB".ROWID[ROWID,1249], "NR_TIME"[TIMESTAMP,11], "ID"[VARCHAR2,1024], "INST_ID"[VARCHAR2,200], "OB".ROWID[ROWID,1249]
   5 - "APAR"."ID"[VARCHAR2,400], "APAR"."GP_NM"[VARCHAR2,200]

The interesting line is operation 4. A hash join takes the rowsource from its first child (the build table) and creates an in-memory hash table (which may spill to disc, of course), so if I see an unreasonable memory allocation (or unexpected spill to disc) a good starting point is to look at what the first child is supplying. In this case the first child seems to be saying that it’s supplying (or allowing for) nearly 3,700 bytes to be passed up to the hash join.

On closer inspection we can see it’s reporting the “rowid” twice, and also reporting the three columns that make up that rowid. I think it’s reasonable to assume that it’s only supplying the rowid once, and maybe it’s not even supplying the other three columns because they are embedded in the rowid. Doing a quick arithmetic check, let’s multiply the size of the rowid by the value of A-rows: 1,249 * 10,000 = 12,490,000. That’s pretty close to the 14MB reported by the hash join in operation 3.

Hypothesis – to get at the id column, Oracle has used this index (actually a very bad choice of those available) to extract the rowid and then passed the rowid up to the parent in a (length padded) fixed format. Oracle has then created a hash table by extracting the id column from the rowid and building the hash table on it but also carrying the length-padded rowid into the hash table.  Possible variants on this theme are that some or all of the other columns in the Column Projection Information are also passed upwards so that the id doesn’t have to be extracted, but if they are they are not padded to their maximum length.

A simple test that this is broadly the right assumption is to re-run the model making the declared length of the rowid much larger to see what happens to the memory allocation. Changing the inst_id declaration from 200 bytes to 1000 bytes (note the stored value is only the 2 bytes needed for the value ‘UP’) the Used-mem jumps to 23 MB (which is an increment very similar to 800 * 10,000).  You’ll note that I chose to experiment with a column that wasn’t the column used in the join. It was a column in the secondary index definition, though, so another test would be to change the nr_time column from a timestamp (11 bytes) to a large varchar2, so I re-ran the test declaring the nr_time as a varchar2(1000) – reverting the inst_id to varchar2(200) – and the Used-mem increased to 25MB.

Preliminary Conclusion

If Oracle uses the contents of the rowid of a secondary index on an IOT in a join then it constructs a fixed format version for the rowid by padding every column in the primary key to its maximum length and concatenating the results. This can have catastrophic side effects on performance if you’ve declared some very long columns “just in case”. Any time you use index organized tables you should remember to check the Column Projection Information in any execution plans that use secondary indexes in case they are passing a large, padded, primary key through the plan to a point where a blocking operation (such as a hash join or merge join) has to accumulate a large number of rows.

Footnote

In my test case I had to hint the query heavily to force Oracle into the path I wanted to demonstrate.

It’s surprising that the optimizer should have chosen this path in the OP’s system, given that there’s another secondary index that contains the necessary columns in its definition. (So one thought is that there’s a statistics problem to address, or possibly the “good” index is subject to updates that make it become very inefficient (large) very quickly.)

Another oddity of the OP’s system was that Oracle should have chosen to do a right hash anti-join when it looked as if joining the tables in the opposite order would produce a much smaller memory demand and lower cost – there was an explict swap_join_inputs() hint in the Outline Information (so copying the outline into the query and changing that to no_swap_join_inputs() might have been abother viable workaround.) In the end the OP hinted the query to use a nested loop (anti-)join from t_base to t_iot – which is another way to avoid the hash table threat with padded rowids.