I was asked to have a look at a batch job that started to take increase in time. We are close to spend more time than we can allow. Looking at the awr report from the time of the batch. I noticed some internal sql on the top list.
When I say internal, i do not mean Oracle internal in this case, but internal to our own framework built to support our operation dba:s.
This is the top sql list.
This sql has not yet finished executing and has so far spent 3604 seconds. I found out that it has spent around 65 minutes executing. As i mentioned this comes from an internal framework developed in-house. I talked to the developers and they suggest we run it less frequent. It can be scheduled. They have fixed the query in next release of the framework.
But I remember that I noticed in Oracle Support that accessing dba_extents and other related dba views like dba_data_files, dba_segments can be slow. So I searched for it and found the following:
And my version is 18.104.22.168, in the note we can read:
“Queries on DBA_EXTENTS are much slower due to the presence of a merge join cartesian.”
Looking in my plan I can see several Merge Join Cartesian, so I think this note applies or at least is worth following. Another interesting information comes below in the note.
“An execution plan regression occurs if there are a large number of records in the X$KTFBUE table. Gathering of dictionary or fixed object stats does not improve performance as this table is intentionally omitted from those packages.”
The last sentence is interesting because gathering dictionary and/ fixed object stats are often recommended to fix performance related to dictionary views. But in this case it seems not to apply here.
Finding information about x$ tables are not that easy, I searched internet and found this info for x$ktfbue.
“Used extent bitmap in file header for LMT (equivalent to uet$ in DMT)”
LMT stands for Locally Managed Tablesapce while DMT stands for Dictinary Managed Tablespaces.
I found the following explanation about the bitmap from Oracle.
“A Locally Managed Tablespace (LMT) is a tablespace that manages its own extents maintaining a bitmap in each data file to keep track of the free or used status of blocks in that data file. Each bit in the bitmap corresponds to a block or a group of blocks. When the extents are allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks.”
The information seems to be correct since x$ktfbue references extents which is the dba view we access.
The note recommends a few options. The first option is to collect statistics on the x$ table. EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SYS’, ‘X$KTFBUE’);
Which I did and the result was very positive, my insert now executes in less than 1 minute. And checking the execution plan, the Merge Join Cartesian was gone.
This was one of the issues, I will review the next batch execution and see if I can find more sql to tune.
Query Against DBA_EXTENTS Performs Slowly After Upgrade to 22.214.171.124
(Doc ID 1453425.1)