I had a conversation a few weeks ago with someone who asked me how to find a missing table when you have a wrapped PL/SQL file and cannot see the source code and you install it and it gives an….[Read More]
Posted by Pete On 18/03/24 At 01:00 PM
Blogs I follow…
I had a conversation a few weeks ago with someone who asked me how to find a missing table when you have a wrapped PL/SQL file and cannot see the source code and you install it and it gives an….[Read More] Posted by Pete On 18/03/24 At 01:00 PM
I had a conversation a few weeks ago with someone who asked me how to find a missing table when you have a wrapped PL/SQL file and cannot see the source code and you install it and it gives an….[Read More]
Posted by Pete On 18/03/24 At 01:00 PM
In 12.2 Oracle introduced Index Usage Tracking to replace the previous option for “alter index xxx monitoring usage”. A recent post on the Oracle database discussion forum prompted me to look for articles about this “new” feature and what people had to say about it. There didn’t seem to be much information online – just […]
In 12.2 Oracle introduced Index Usage Tracking to replace the previous option for “alter index xxx monitoring usage”. A recent post on the Oracle database discussion forum prompted me to look for articles about this “new” feature and what people had to say about it. There didn’t seem to be much information online – just a handful of articles starting with Tim Hall a few years ago and ending with Maria Colgan a few months ago – so I thought I’d update my notes a little and publish them.
Unfortunately, by the time I’d written the first 6 pages it was starting to feel like very heavy going, so I decided to rewrite it as a mini-series. In part one I’ll just give you some descriptions and explanations that are missing from the manuals; in part two I’ll do a bit of a fairly shallow dive to talk about what’s happening behind the scenes and how you can do some experiments; in part three I’ll describe some of the experiments and show the results that justify the descriptions I’ve given here in part one.
In the bad old days you could enable “monitoring” on an index to see if it was being used. The command to do this was:
alter index {index name} monitoring usage;
After executing this statement you would wait for a bit then check the view dba_object_usage:
SQL> desc dba_object_usage
Name Null? Type
----------------------------- -------- --------------------
OWNER NOT NULL VARCHAR2(128)
INDEX_NAME NOT NULL VARCHAR2(128)
TABLE_NAME NOT NULL VARCHAR2(128)
MONITORING VARCHAR2(3)
USED VARCHAR2(3)
START_MONITORING VARCHAR2(19)
END_MONITORING VARCHAR2(19)
SQL> select * from dba_object_usage;
OWNER INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
--------------- -------------------- ------------------------- --- --- ------------------- -------------------
TEST_USER T2_I1 T2 YES YES 03/12/2024 15:31:35
1 row selected.
As you can see, this didn’t give you much information – just “yes it has been used” or “no it hasn’t been used” since the moment you started monitoring it; and that’s almost totally useless as an aid to measuring or understanding the effectiveness of the index.
Apart from the almost complete absence of information, there were collateral issues: I think that, initially, gathering stats, index rebuilds and using explain plan would flag an index as used; at the opposite extreme indexes that were actually used to avoid foreign key locking problems were not flagged as used.
The promise of Index Usage Tracking is clearly visible in the description of the view you use to report the details captured:
SQL> desc dba_index_usage
Name Null? Type
----------------------------------- -------- ------------------------
OBJECT_ID NOT NULL NUMBER
NAME NOT NULL VARCHAR2(128)
OWNER NOT NULL VARCHAR2(128)
TOTAL_ACCESS_COUNT NUMBER
TOTAL_EXEC_COUNT NUMBER
TOTAL_ROWS_RETURNED NUMBER
BUCKET_0_ACCESS_COUNT NUMBER
BUCKET_1_ACCESS_COUNT NUMBER
BUCKET_2_10_ACCESS_COUNT NUMBER
BUCKET_2_10_ROWS_RETURNED NUMBER
BUCKET_11_100_ACCESS_COUNT NUMBER
BUCKET_11_100_ROWS_RETURNED NUMBER
BUCKET_101_1000_ACCESS_COUNT NUMBER
BUCKET_101_1000_ROWS_RETURNED NUMBER
BUCKET_1000_PLUS_ACCESS_COUNT NUMBER
BUCKET_1000_PLUS_ROWS_RETURNED NUMBER
LAST_USED DATE
Though the columns are not very well described in the reference manuals you can see very clearly that there’s a lot more detail than just “yes/no” here. The columns clearly carry information about “how many times” and “how much data”, breaking the numbers down across a small range-based histogram. Here’s an example of output (using Tom Kyte’s print_table() routine to turn columns to rows):
SQL> execute print_table('select * from dba_index_usage where name = ''T1_I1''')
OBJECT_ID : 206312
NAME : T1_I1
OWNER : TEST_USER
TOTAL_ACCESS_COUNT : 889
TOTAL_EXEC_COUNT : 45
TOTAL_ROWS_RETURNED : 17850
BUCKET_0_ACCESS_COUNT : 0
BUCKET_1_ACCESS_COUNT : 0
BUCKET_2_10_ACCESS_COUNT : 0
BUCKET_2_10_ROWS_RETURNED : 0
BUCKET_11_100_ACCESS_COUNT : 878
BUCKET_11_100_ROWS_RETURNED : 13200
BUCKET_101_1000_ACCESS_COUNT : 9
BUCKET_101_1000_ROWS_RETURNED : 1650
BUCKET_1000_PLUS_ACCESS_COUNT : 2
BUCKET_1000_PLUS_ROWS_RETURNED : 3000
LAST_USED : 11-mar-2024 20:26:26
The order of the columns is just a little odd (in my opinion) so I’ve switched two of them around in my descriptions below:
The most important omission in the descriptions given in the manuals is the difference between total_exec_count and total_access_count. (It was a comment on Maria Colgan’s blog note asking about the difference that persuaded me that I really had to write this note.) If you don’t know what an “access” is supposed to be you can’t really know how to interpret the rest of the numbers.
Take another look at the sample output above, it shows 45 executions and 889 accesses – I happen to know (because I did the test) that most of the work I’ve done in this interval has been reporting a two-table join that uses a nested loop from t2 into t1 using an index range scan on index t1_i1 to access table t1. I know my data well enough to know that every time I run my query it’s going to find about 20 rows in t2, and that for every row I find in t2 there will be roughly 15 rows that I will access in t1 through the index.
Give or take a little extra activity round the edges that blur the numbers I can see that the numbers make sense:
The numbers are in the right ball-park to meet my expectations. But we do have 11 more accesses reported – 9 of them reported an average of 1,650/9 = 183 rows, 2 of them reported an average of 3,000/2 = 1500 rows. Again, I know what I did, so I can explain why those numbers have appeared, but in real life you may have to do a little work to find a reasonable explanation (Spoilers: be suspicious about gathering index stats)
It’s possible, for example, that there are a few rows in the t2 table that have far more than the 15 row average in t1 and the larger numbers are just some examples from the nested loop query that happened to hit a couple of these outliers in t2. (It’s worth highlighting, as a follow-up to this suggestion, that a single execution could end up reporting accesses and row counts in multiple buckets.)
In fact the 9 “medium sized” access were the result of single table queries using a “between” clause that ranged through 10 to 15 values of t1 (returning 150 to 225 rows each), and the two “large” accesses were the result of two index-only queries where I forced an index full scan and an index fast full scan that discarded half the rows of an index holding 3,000 entries.
As I said, I’ll be presenting a few examples in part 3, but a guideline that may be helpful when considering the executions, accesses, and rowcounts is this: if you’re familiar with the SQL Monitor report then you’ll know that each call to dbms_sql_monitor.report_sql_monitor() reports one execution – then the Starts column for any index operation will (probably) be the total access count, and the Rows (Actual) column will (probably) be the total rows returned. As noted above, though, any one execution may end up splitting the total Starts and Rows (Actual) across multiple buckets.
I hope this has given you enough information to get you interested in Index Usage Tracking, and some idea of what you’re looking at when you start using the view. There are, however, some technical details you will need to know if you want to do some testing before taking any major steps in production. There are also some questions that ought to be addressed before jumping to conclusions about what the numbers mean, so I thought I’d list several questions that came to mind when I first read about the feature:
If you can think of any other questions where “something different” might happen, feel free to add them as comments.
At the start of this note I said it had been prompted by a question on one of the Oracle forums. The thread was about identifying indexes that could be dropped and the question was basically: “Is the old index monitoring obsolete?” The answer is “Yes, definitely, and it has been for years.”
Despite their continued presence in the Oracle data dictionary, LONG columns are not an option that anyone should choose; you can’t do much with them and they introduce a number of strange space management problems. Nevertheless a recent thread on the Oracle database forum started with the following question: “How do you use LONG columns […]
Despite their continued presence in the Oracle data dictionary, LONG columns are not an option that anyone should choose; you can’t do much with them and they introduce a number of strange space management problems. Nevertheless a recent thread on the Oracle database forum started with the following question: “How do you use LONG columns in a WHERE clause?”. The basic answer is: “You don’t”.
This user wanted to query all_tab_partitions for a given table_name and high_value, and the high_value is (still) a LONG, so attempts to use it resulted in Oracle error “ORA-00997: illegal use of LONG datatype”. A possible, and fairly obvious but undesirable, solution to the requirement is to write a PL/SQL function to read the current row from all_tab_partitions and returns the first N characters of the high_value as a varchar2(). Here’s a version (not quite the one I posted) of such a function, with a sample of use:
rem
rem Script: get_high_value.sql
rem Author: Jonathan Lewis
rem Dated: Mar 2024
rem
create or replace function get_high_value (
i_tab_owner varchar2,
i_tab_name varchar2,
i_part_name varchar2,
i_part_posn number
)
return varchar2
is
v1 varchar2(4000);
begin
select atp.high_value
into v1
from all_tab_partitions atp
where atp.table_owner = upper(i_tab_owner)
and atp.table_name = upper(i_tab_name)
and atp.partition_name = upper(i_part_name)
and atp.partition_position = upper(i_part_posn)
;
return v1;
end;
/
select
apt.table_owner, apt.table_name,
apt.tablespace_name,
apt.partition_name, apt.partition_position,
apt.high_value
from
all_tab_partitions apt
where
apt.table_owner = 'TEST_USER'
and apt.table_name = 'PT_RANGE'
and get_high_value(
apt.table_owner,
apt.table_name,
apt.partition_name,
apt.partition_position
) = '200'
/
This seemed to work quite well and sufficiently rapidly – but I only had two partitioned tables in my schema and a total of 12 partitions, so it’s not sensible to look at the clock to see how efficient the query is.
Another possible solution introduced me to a function that has been around for years (and many versions) which I had never come across: sys_dburigen(). PaulZip supplied the following code (which I’ve altered cosmetically and edited to pick up a table in my schema):
select *
from (
select
dbms_lob.substr(
sys_dburigen (
atp.table_owner,
atp.table_name,
atp.partition_name,
atp.partition_position,
atp.high_value,
'text()'
).getclob(), 4000, 1) high_value_str,
atp.table_owner, atp.table_name, atp.partition_name,
atp.tablespace_name, atp.high_value
from all_tab_partitions atp
where atp.table_owner = 'TEST_USER'
and atp.table_name = 'PT_RANGE'
)
where high_value_str = '200'
/
This was so cute, and looked like a much nicer (i.e. “legal”) solution than my PL/SQL hacking that I had to take a closer look at sys_dburigen() – first to understand what it was supposed achieve (yes, I do RTFM) then to see how it actually worked.
Something I did first was simply to strip back the layers of the expression used to supplied the high_value_str which took me through the following four combinations (with and without ‘text’ , with and without ‘get_clob’). Each expression is followed by the result for the row selected above:
sys_dburigen (atp.table_owner, atp.table_name, atp.partition_name, atp.partition_position, atp.high_value, 'text()').getclob()
200
---
sys_dburigen (atp.table_owner, atp.table_name, atp.partition_name, atp.partition_position, atp.high_value).getclob()
<?xml version="1.0"?><HIGH_VALUE>200</HIGH_VALUE>
--
sys_dburigen (atp.table_owner, atp.table_name, atp.partition_name, atp.partition_position, atp.high_value, 'text()')
DBURITYPE('/PUBLIC/ALL_TAB_PARTITIONS/ROW[TABLE_OWNER=''TEST_USER'' and TABLE_NAME=''PT_RANGE'' and PARTITION_NAME=''P200'' and PARTITION_POSITION=''1'']/HIGH_VALUE/text()', NULL)
--
sys_dburigen (atp.table_owner, atp.table_name, atp.partition_name, atp.partition_position, atp.high_value)
DBURITYPE('/PUBLIC/ALL_TAB_PARTITIONS/ROW[TABLE_OWNER=''TEST_USER'' and TABLE_NAME=''PT_RANGE'' and PARTITION_NAME=''P200'' and PARTITION_POSITION=''1'']/HIGH_VALUE', NULL)
Working from the bottom pair up we see that we start by generating a dburitype which defines the type of thing we want to query and the restriction we want to use while querying. The ‘text()’ option simply adds an extra detail to the dburitype.
The top pair shows us that the get_clob() will then return the value we have requested, either as an XML value, or as the text value described by the XML value if we’ve supplied the ‘text()’ option.
Our call to sys_dburigen() has specified an object we want to access, and 4 columns in that object that will identify a unique row in that object, and a fifth column that we want returned either as an XML value or as a text value.
I actually worked through the analysis in the opposite direction to the one I’ve been showing. When the call to sys_dburigen() I suspected that it might be doing the same thing as my PL/SQL function call, so I ran the two queries with SQL tracing enabled to see what activity took place at the database.
Ignoring driving query against all_tab_partitions the content of the PL/SQL trace was basically 3 executions (I had 3 partitions in the pt_range table) of:
SELECT ATP.HIGH_VALUE
FROM
ALL_TAB_PARTITIONS ATP WHERE ATP.TABLE_OWNER = UPPER(:B4 ) AND
ATP.TABLE_NAME = UPPER(:B3 ) AND ATP.PARTITION_NAME = UPPER(:B2 ) AND
ATP.PARTITION_POSITION = UPPER(:B1 )
The content of the sys_dburigen() trace was 3 executions of a query like:
SELECT alias000$."HIGH_VALUE" AS HIGH_VALUE
FROM
"ALL_TAB_PARTITIONS" alias000$ WHERE 1 = 1 AND ((((alias000$."TABLE_OWNER"=
'TEST_USER') AND (alias000$."TABLE_NAME"='PT_RANGE')) AND
(alias000$."PARTITION_NAME"='P200')) AND (alias000$."PARTITION_POSITION"=
'1'))
Note particularly the literal values in the predicates in lines 4, 5 and 6. This version of the code has to generate and optimise (hard-parse) a new SQL statement for every partition in the table referenced in the driving query. For a table with a large number of partitions, and a system with a large number of partitioned tables, the disruption of shared pool that this might cause could be severe if (as the user said at one point) “we will be frequently selecting from all_tab_partitions”.
Using LONG columns in SQL predicates is not nice – and not likely to be efficient – but there are ways of working around the limitations of LONGs. It’s undesirable to use PL/SQL that calls SQL inside a SQL statement, but we can use a PL/SQL function to return a string from a LONG in the current row – and since that’s pretty much what Oracle seems to be doing with its call to sys_dburigen() it’s hard to insist that the PL/SQL strategy is inappropriate. (But maybe the call to sys_dburigen() in this context would be considered an abuse of a feature anyway – even though it seems much more elegant and flexible once you’ve learned a little about how it works.)
As another detail on analysing the cost/benefit of different approaches – it would be possible to avoid creating the pl/sql function by embedding it in the SQL as a “with function” clause:
with function get_high_value (
i_tab_owner varchar2,
i_tab_name varchar2,
i_part_name varchar2,
i_part_posn number
)
return varchar2
is
v1 varchar2(4000);
begin
select atp.high_value
into v1
from all_tab_partitions atp
where atp.table_owner = upper(i_tab_owner)
and atp.table_name = upper(i_tab_name)
and atp.partition_name = upper(i_part_name)
and atp.partition_position = upper(i_part_posn)
;
return v1;
end;
select
apt.table_owner, apt.table_name,
apt.tablespace_name,
apt.partition_name, apt.partition_position,
apt.high_value
from
all_tab_partitions apt
where
apt.table_owner = 'TEST_USER'
and apt.table_name = 'PT_RANGE'
and get_high_value(
apt.table_owner,
apt.table_name,
apt.partition_name,
apt.partition_position
) = '200'
/
Here’s a silly little detail about execution plans on (interval) partitioned tables that I hadn’t noticed until it showed up on this thread on a public Oracle forum: it’s an execution plan that claims that Oracle will be visiting a partition that clearly won’t be holding the data requested. Here’s the starting section of a […]
Here’s a silly little detail about execution plans on (interval) partitioned tables that I hadn’t noticed until it showed up on this thread on a public Oracle forum: it’s an execution plan that claims that Oracle will be visiting a partition that clearly won’t be holding the data requested.
Here’s the starting section of a demonstration script – mostly by Solomon Yakobson with minor tweaks and additions from me:
rem
rem Script: non_existent_partition.sql
rem Author: Solomon Yakobson / Jonathan Lewis
rem Dated: Mar 2024
rem
rem Last tested
rem 19.11.0.0
rem
create table invoices_partitioned(
invoice_no number not null,
invoice_date date not null,
comments varchar2(500)
)
partition by range (invoice_date)
interval (interval '3' month)
(
partition invoices_past values less than (date '2023-01-01')
);
insert into invoices_partitioned
select level,
date '2023-01-01' + numtoyminterval(3 * (level - 1),'month'),
null
from dual
connect by level <= 6
/
insert into invoices_partitioned select * from invoices_partitioned;
/
/
/
/
/
/
/
/
/
/
/
/
/
/
/
/
commit
/
execute dbms_stats.gather_table_stats(user,'invoices_partitioned')
set linesize 156
column high_value format a80
select partition_position, num_rows,
partition_name,
high_value
from user_tab_partitions
where table_name = 'INVOICES_PARTITIONED'
order by partition_position
/
alter table invoices_partitioned drop partition for (date'2023-09-01');
purge recyclebin;
select partition_position, num_rows,
partition_name,
high_value
from user_tab_partitions
where table_name = 'INVOICES_PARTITIONED'
order by partition_position
/
The script creates an interval partitioned table, with an interval of 3 months, then inserts 131,072 rows per partition (the strange re-execution of “insert into x select from x” was my lazy way of increasing the volume of data from the original one row per partition without having to think too carefully.
After creating the data we report the partition names and high values in order, then drop (and purge) the partition that should hold the value 1st Sept 2023 (which will be the partition with the high_value of 1st Oct 2023) and report the partition names and high values again so that you can see the “gap” in the high values and the adjustment to the partition_position values. Here are the “before” and “after” outputs:
PARTITION_POSITION NUM_ROWS PARTITION_NAME HIGH_VALUE
------------------ ---------- ---------------------- --------------------------------------------------------------------------------
1 0 INVOICES_PAST TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
2 131072 SYS_P39375 TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
3 131072 SYS_P39376 TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 131072 SYS_P39377 TO_DATE(' 2023-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
5 131072 SYS_P39378 TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
6 131072 SYS_P39379 TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
7 131072 SYS_P39380 TO_DATE(' 2024-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
7 rows selected.
PARTITION_POSITION NUM_ROWS PARTITION_NAME HIGH_VALUE
------------------ ---------- ---------------------- --------------------------------------------------------------------------------
1 0 INVOICES_PAST TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
2 131072 SYS_P39375 TO_DATE(' 2023-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
3 131072 SYS_P39376 TO_DATE(' 2023-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 131072 SYS_P39378 TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
5 131072 SYS_P39379 TO_DATE(' 2024-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
6 131072 SYS_P39380 TO_DATE(' 2024-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
6 rows selected.
Now we check the execution plan for a query that would have accessed the partition we’ve just dropped:
explain plan for
select *
from invoices_partitioned
where invoice_date = date '2023-09-01';
select *
from dbms_xplan.display();
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
Plan hash value: 1148008570
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 109K| 1173K| 104 (1)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 109K| 1173K| 104 (1)| 00:00:01 | 4 | 4 |
|* 2 | TABLE ACCESS FULL | INVOICES_PARTITIONED | 109K| 1173K| 104 (1)| 00:00:01 | 4 | 4 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("INVOICE_DATE"=TO_DATE(' 2023-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
The execution plans says it’s going to visit partition number 4 (pstart/pstop) – which we know will definitely cannot be holding any relevant data. If this were an ordinary range-partitioned table – as opposed to interval partitioned – it would be the correct partition for 1st Sept 2024, of course, but it isn’t, so it feels like the pstart/pstop ought to say something like “non-existent” and all the numeric estimates should be zero.
A quick trick for making an interval partition appear without inserting data into it is to issue a “lock table … partition for () …” statement (See footnote to this blog note). I did wonder if the attempt to explain a plan that needed a non-existent partition had actually had the same effect of making Oracle create the partition, so I ran the query against user_tab_partitions again just to check that this hadn’t happend.
So what’s going to happen at run-time: is this an example of “explain plan” telling us a story that’s not going to match what shows up in v$sql_plan (dbms_xplan.display_cursor). Let’s run the query (with rowsource execution stats enabled) and find out:
set serveroutput off
alter session set statistics_level = all;
alter session set "_rowsource_execution_statistics"=true;
select *
from invoices_partitioned
where invoice_date = date '2023-09-01';
select *
from table(dbms_xplan.display_cursor(format=>'allstats last partition'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
SQL_ID d42kw12htubhn, child number 0
-------------------------------------
select * from invoices_partitioned where invoice_date = date
'2023-09-01'
Plan hash value: 1148008570
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Pstart| Pstop | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 0 |00:00:00.01 |
| 1 | PARTITION RANGE SINGLE| | 1 | 109K| 4 | 4 | 0 |00:00:00.01 |
|* 2 | TABLE ACCESS FULL | INVOICES_PARTITIONED | 0 | 109K| 4 | 4 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("INVOICE_DATE"=TO_DATE(' 2023-09-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
It’s the same plan with the same “wrong” partition identified, and the same estimate for rows returned – but the access never actually happened: Starts = 0 on the table access full.
My hypothesis about this misleading reporting is that Oracle knows from the table definition everything about every partition that might eventually exist – the high_value for the “anchor” partition is known and the interval is known so the appropriate partition number for any partition key value can be derived. Then, at some point, a disconnect appears between the theoretical partition position and the set of physically instantiated partitions, so the optimizer gets the message “theoretically it’s in the 4th partition” and collects the stats from “partition_position = 4” to do the arithmetic and produce the plan.
Fortunately there’s some metadata somewhere that means the run-time engine doesn’t try to access the wrong partition, so this little glitch doesn’t really matter for this simple query – beyond its ability to cause a little confusion.
It’s possible, though, that this behaviour leaves the optimizer with another reason for getting the arithmetic wrong and picking the wrong path if you have a number of “missing” partitions in an interval partitioned table that you’re querying with a range-based predicate that crosses several (non-existent) partitions. So treat this as a warning/clue if you recognise that pattern in any of your partitioned table.
Here’s a little detail that I discovered recently when I wanted to do something a little exotic in 23c on the official Virtualbox VM. There’s been a hidden parameter to disable oradebug since (at least) 12.2.0.1. The clue is in the name: The valid values for this parameter are none, restricted, and all. The default […]
Here’s a little detail that I discovered recently when I wanted to do something a little exotic in 23c on the official Virtualbox VM. There’s been a hidden parameter to disable oradebug since (at least) 12.2.0.1. The clue is in the name:
_disable_oradebug_commands
The valid values for this parameter are none, restricted, and all. The default value in 12c, 18c, and 19c is none; the default value in 21.3 and 23.3 is restricted. This means some of the investigative code you may have used in the earlier versions may produce an error in the newer versions. To change the value you have to restart the database. Here are a couple of the error messages you might see:
_disable_oradebug_commands=all
SQL> oradebug setmypid
ORA-32519: insufficient privileges to execute ORADEBUG command: execution of ORADEBUG commands is disabled for this instance
SQL>
_disable_oradebug_commands=restricted
SQL> oradebug setmypid
Statement processed.
SQL> oradebug peek 0x000000008E65E1D8 16
[08E65E1D8, 08E65E1E8) = 99FC0501 00000004 8E519690 00000000
SQL> oradebug call keiutFlush
ORA-32519: insufficient privileges to execute ORADEBUG command: execution of ORADEBUG commands is disabled for this instance
SQL>
The “restricted” example above is the one that led me to the parameter when I was testing 23c. However, setting the value to “none” in the spfile and restarting the instance didn’t help. This is what I saw when I tried running my code from a PDB:
SQL> oradebug setmypid
Statement processed.
SQL> oradebug call keiutFlush
ORA-32519: insufficient privileges to execute ORADEBUG command: OS debugger privileges required for client
SQL>
I have no idea how to give myself OS debugger privileges. It’s a nuisance, but since I.m running the whole thing from the server and have to have at least two sessions active for the testing I’m doing, it’s not a big issue to have one more running from the oracle (OS account) connected to the cdb$root to call the flush command.
To check for the list of valid values for string parameters, you can query v$parameter_valid_values, but that view won’t show you the “underscore” parameters (the commented where clause above is the text in v$fixed_view_definition that allows Oracle to hide the hidden parameter). To see the valid values for the hidden parameters you need to access the x$ structure underneath the v$, and I wrote a little script (that has to be run by sys) to do that a long time ago.
The other day I came across the term AI Prompt Engineer. It was in the context of being the next big thing in the job market. I did a bit of Googling and sure enough there are courses about it, and a number of jobs being offered. These seem to break do…
The other day I came across the term AI Prompt Engineer. It was in the context of being the next big thing in the job market. I did a bit of Googling and sure enough there are courses about it, and a number of jobs being offered. These seem to break down into two main … Continue reading “AI Prompt Engineer (AI-fu). The new Google-fu?”
The post AI Prompt Engineer (AI-fu). The new Google-fu? first appeared on The ORACLE-BASE Blog.
Over time, Oracle has been adding more contextual information into each allocated heap memory chunk, to make it easier to see what for your heap memory is used. This instrumentation is used for private (PGA,UGA,etc) heaps too, but this article focuses …
Over time, Oracle has been adding more contextual information into each allocated heap memory chunk, to make it easier to see what for your heap memory is used. This instrumentation is used for private (PGA,UGA,etc) heaps too, but this article focuses only on shared pool heaps.
A few examples from past are:
Library cache object hash values added to allocated shared pool chunk names (2010) The above technique is used for some other allocation types too, like KKSSP^NNN allocations that are used for “session pages” that hold various tiny memory structures like library cache lock (and pin in older DB versions) that a session must allocate when accessing library cache objects.
A recent (Mar 2024) question on the MOSC DB Admin forum (needs an account) asks why a query which runs every few minutes and executes 25 times in 2 hours according to an AWR report never shows up in v$session when you query for the SQL_ID. There are a few reasons why you might be […]
A recent (Mar 2024) question on the MOSC DB Admin forum (needs an account) asks why a query which runs every few minutes and executes 25 times in 2 hours according to an AWR report never shows up in v$session when you query for the SQL_ID.
SQL> select * from V$session where sql_id = '0qsvkwru0agwg';
no rows selected
There are a few reasons why you might be unlucky with the timing but it seems likely that this query, if repeated very frequently for a couple of minutes, ought to be able to capture details of the session executing it, especially since the (edited) line supplied from the “SQL ordered by Elapsed Time” section of the AWR shows the average execution time of the query to be 1.93 seconds. There is, however, an obvious clue about why the OP is out of luck.
The OP has “obfuscated” the sql_id in question – which is a fairly pointless exercise since it’s not possible to reverse engineer an sql_id back to the originating text. I mention this simply because the supplied “sql_id” is 6 letters long and not the 13 that every sql_id (and my fake above) should be – it’s the type of tiny detail that leads to suggestions like: “maybe the OP just didn’t copy the sql_id correctly when querying v$session”.
Take a look at the fragment of “SQL Text” reported with the sql_id:
DECLARE V_SEUIL VARCHAR2(2) :=
This is not an SQL statement, it’s the start of an anonymous PL/SQL block. This might be the reason why the sql_id is (statistically speaking) never spotted in v$session or v$active_session_history; it also tells us what the OP probably ought to be looking for … the SQL that’s executing inside the PL/SQL, which can be found by following the link that the AWR will have supplied to the corresponding entry in the “Complete List of SQL Text”. Allowing for the way that embedded SQL is “normalised” to all capitals with excess white space removed, the text of the PL/SQL will provide some text that the OP can use to search the rest of the AWR report for more appropriate SQL ids.
Just to demonstrate the point, and note some side effects of SQL running inside PL/SQL, here’s a demonstration script with three variations on the theme; followed by the critical part of the output and a separate section with the corresponding “SQL ordered by Elapsed Time” extracts from the AWR reports generated after running the test on 11.2.0.4 (because that was the version being used by the OP):
rem
rem Script: awr_plsql.sql
rem Author: Jonathan Lewis
rem Dated: Mar 2024
rem Purpose:
rem
rem Last tested
rem 11.2.0.4
rem
create table t1 as select * from all_objects where rownum <= 10000;
create table t2 as select * from t1;
execute dbms_workload_repository.create_snapshot('TYPICAL');
set timing on
set feedback off
declare
ct number;
begin
select
/*+
leading(t1 t2)
use_nl(t2)
*/
count(*)
into ct
from
t1, t2
where
t2.object_id = t1.object_id
;
dbms_output.put_line('Count: ' || ct);
end;
/
set timing off
execute dbms_workload_repository.create_snapshot('TYPICAL');
set timing on
declare
ct number := 0;
c_tot number := 0;
begin
for r in (select * from t1) loop
select count(*)
into ct
from t2
where t2.object_id = r.object_id
;
c_tot := c_tot + ct;
end loop;
dbms_output.put_line('Count: ' || c_tot);
end;
/
set timing off
execute dbms_workload_repository.create_snapshot('TYPICAL');
set timing on
declare
ct number := 0;
c_tot number := 0;
begin
for r in (
select
/*+
leading(t1 t2)
use_nl(t2)
*/
t1.object_id
from t1,t2
where t2.object_id = t1.object_id
) loop
select count(*)
into ct
from t2
where t2.object_id = r.object_id
;
c_tot := c_tot + ct;
end loop;
dbms_output.put_line('Count: ' || c_tot);
end;
/
set timing off
execute dbms_workload_repository.create_snapshot('TYPICAL');
Output
--------
Count: 10000
Elapsed: 00:00:13.70
Count: 10000
Elapsed: 00:00:10.35
Count: 10000
Elapsed: 00:00:24.81
The three anonymous blocks do a lot of pointless work in PL/SQL: the first statement forces a simple nested loop join using full tablescans over two tables of 10,000 rows, the second statement “emulates” this using a PL/SQL loop over the first table, scanning the second table once per row; the third statement combines both pieces of idiocy, driving through the nested loop then scanning the second table once per row returned. It’s not really surprising that the three times recorded are in the order of seconds, nor that we can see T3 (time for the third block) is approximately T1 + T2. But what do the separate AWR reports say:
Test 1 – nested loop join, block run time reported as 13.70 seconds:
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
13.6 1 13.63 92.3 98.1 .0 1sxdt7cswq8z0
Module: MyModule
declare ct number; begin select /*+ leading(t1 t2) use_nl(t2) */ c
ount(*) into ct from t1, t2 where t2.object_id = t1.object_id ; dbms_
output.put_line('Count: ' || ct); end;
13.6 1 13.63 92.3 98.1 .0 502tvyky9s2ta
Module: MyModule
SELECT /*+ leading(t1 t2) use_nl(t2) */ COUNT(*) FROM T1, T2 WHERE T2.OB
JECT_ID = T1.OBJECT_ID
The anonymous PL/SQL block appears at the top of the list reporting 13.6 seconds – but that’s nearly 100% of the total time reported, and the SQL statement inside the block also reports 13.6 seconds. The actual time Oracle spent in the PL/SQL execution engine was virtually zero, but the AWR report has (in effect) double counted the time. You’ll notice that the “pure” SQL has, except for the comments/hints, been converted to upper case and the “into ct” has been removed.
Test 2 – PL/SQL emulating an inefficient nested loop join
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
10.2 1 10.24 90.0 98.2 .0 201ptprw6ngpq
Module: MyModule
declare ct number := 0; c_tot number := 0; begin for r in (select * from t1)
loop select count(*) into ct from t2 where t2.object_id = r.object_id
; c_tot := c_tot + ct; end loop; dbms_output.put_line('Count: ' || c_tot); e
nd;
9.5 10,000 0.00 83.5 98.2 .0 4qwg9bknnjbr0
Module: MyModule
SELECT COUNT(*) FROM T2 WHERE T2.OBJECT_ID = :B1
Again, the total time reported exceeds that elapsed execution time from the SQL*Plus output. Again the anonymous PL/SQL block is at the top of the list, but this time the SQL accounts for noticeable less time than the PL/SQL block. The time spent in the PL/SQL engine has become visible – after all, the PL/SQL has called a SQL statement 10,000 times, and it has amended a variable value 10,000 times.
Test 3 – inefficient nested loop join driving an emulated join
-> Captured SQL account for 97.4% of Total DB Time (s): 26
-> Captured PL/SQL account for 100.1% of Total DB Time (s): 26
Elapsed Elapsed Time
Time (s) Executions per Exec (s) %Total %CPU %IO SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
24.8 1 24.81 95.3 97.2 .0 a30dkwt9at2w5
Module: MyModule
declare ct number := 0; c_tot number := 0; begin for r in ( select /*+
leading(t1 t2) use_nl(t2) */ t1.object_id from t1,t2 where t2.o
bject_id = t1.object_id ) loop select count(*) into ct from t2 where t2
.object_id = r.object_id ; c_tot := c_tot + ct; end loop; dbms_output.put_
14.3 1 14.26 54.8 97.1 .0 877jph80b0t2x
Module: MyModule
SELECT /*+ leading(t1 t2) use_nl(t2) */ T1.OBJECT_ID FROM T1,T2 WHERE
T2.OBJECT_ID = T1.OBJECT_ID
10.1 10,000 0.00 38.7 97.9 .0 4qwg9bknnjbr0
Module: MyModule
SELECT COUNT(*) FROM T2 WHERE T2.OBJECT_ID = :B1
I’ve included in this example two lines from the heading of the “SQL ordered by…” section. It’s often very helpful to check for summary comments like this – and not just in SQL sections of the AWR/Statspack report – sometimes you’ll notice that some derived total is more than (maybe much more than) 100% of the figures you’re trying to address; sometimes you may notice that the “biggest, most important number” in the detail may be pretty irrelevant because the “total accounted for” is only a small fraction of the actual work done.
This example shows another fairly common pattern – a PL/SQL block where (even if you didn’t cross-check carefully at first) you might notice that there were a few SQL statements reported a little further down that conveniently summed to the total of the PL/SQL. (There’s a pretty little example of this from a production system that I published in 2007 in one of my Analysing Statspack notes).
Again you’ll notice that there’s a little extra time in the PL/SQL line than the sum of the two SQL lines – but again we have to allow for PL/SQL calling 10,000 SQL executions and summing 10,000 results. In the last two examples there’s a slightly better chance of capturing the SQL_ID of the PL/SQL block by querying v$session. In 80 samples (driven by hand) of query by the correct SID while the last example was running I saw the sql_id of the PL/SQL block in v$session.prev_sql_id once, most of the samples showed me the sql_id of the simple tablescan as the v$session.sql_id, or which roughly half showed the sql_id of the driving loop as the v$session.prev_sql_id.
If you have an AWR report that shows a PL/SQL block as an expensive item in the “SQL ordered by …” sections you may never see its sql_id in any queries you make against v$session or v$active_session_history because the component of the work done by the PL/SQL may be incredibly tiny, but the AWR is reporting the sum of the workload due to the SQL executing inside that block as if it were part of the pure PL/SQL workload.
A big hint about this appears in the summary notes above (most of) the details reports, where there may be a couple of lines telling you what percentage of the workload/time/etc. the detail has captured. Sometimes it’s vastly more than 100% (and sometimes it’s much less – which tells you that you may be looking at things that are not part of the real problem).
How do you know how secure your Oracle databases are? How secure should your Oracle databases be? These are interesting questions that we will cover in this three part post. This first part is going to cover the high level….[Read More] Posted by P…
How do you know how secure your Oracle databases are? How secure should your Oracle databases be? These are interesting questions that we will cover in this three part post. This first part is going to cover the high level….[Read More]
Posted by Pete On 19/02/24 At 01:43 PM
My company PeteFinnigan.com Limited is 21 years old today!! It seems that time has gone so fast. When I started the company my oldest son was a baby and now he is almost 22 years old and works here in….[Read More] Posted by Pete On 12/02/24 At 11:…
My company PeteFinnigan.com Limited is 21 years old today!! It seems that time has gone so fast. When I started the company my oldest son was a baby and now he is almost 22 years old and works here in….[Read More]
Posted by Pete On 12/02/24 At 11:28 AM