Search

OakieTags

Who's online

There are currently 0 users and 24 guests online.

Recent comments

Oakies Blog Aggregator

Friday Philosophy – On “Being the Expert”

Working as a recognised expert at something is a little…strange, I find.

I had an assignment this week to go visit a client, have a look at a performance issue and find out the root cause. I was also to at least come up with suggested resolutions with the ideal aim of giving them a proven fix they could implement. All to be done in two to three days. This is pretty standard fayre when you are putting yourself forward as some sort of expert in something. And it is not always an easy thing to do – for more reasons than you might expect.

When it comes to the core service you are providing you are certainly expected to know your stuff and if you are there as the expert and you don’t? Well, any pain you now suffer is self-inflicted so I have no sympathy. You might think actually being an expert is the hard part – the knowing all that stuff, remembering it, the ability to answer all the questions or look at an issue and in 5 minutes say “It’s because the CLOB settings are wrong”. ie matching the expectations of almost God-like knowledge and ability. But it is not. If you can listen to what their problem is, understand it and then explain something to them that they did not know before, it will be fine. What the client needs is to feel progress is being made. An immediate and inspired solution may occasionally be possible but on the occasions I have pulled that off, the client usually just feels uncomfortable, like they missed the obvious. Because they did. If I sort out the issue straight away that they have had for 3 weeks and that the in-house expert has looked at there is only really two possible reasons
(a) it is simple and they missed it.
(b) they ignored their expert.

The option of (c) my genius is sadly just a dream.

What I find more tricky is when they just accept what I say, when they treat everything I say as correct. Even if I say “it might be this” there can be an assumption I am being modest and it really is what I suggest. I’d like them to only believe me once there is some proof. Most of my time on such assignments is me sat at the SQL prompt trying to back up what I think is the issue/solution. Even when I have evidence, I know I could just be seeing what I want to see. I want some proof and I want them to challenge it.

There is also sometimes a tendency for the rest of the staff to regarded you as some sort of a weirdo, someone Not Like Them. After all, if you are an expert in Oracle Performance you must spend all your time looking at explain plans and 10046 traces and not doing normal people stuff. I have to say, I had a really nice (and in some ways quite worrying) complement a few years back. I was at a client site for a couple of months, plowing though what seemed like endless layers of bad code/design/decisions to make things run better. One lunch time I headed out to find some lunch with a couple of the developers. One of them turned to me and said something like “You know, I’m really glad you joined us. You’re just a normal bloke and not one of those freaky tuning experts!” He really thought all Oracle Performance people would be strange – and strange in the already bizarre context of all the other people that inhabit our profession. I wonder who else he had met?

You can also run into resentment – occasionally irrationally (fear of challenge? envy? just psychotic people?) but also for real reasons. I sort-of alluded to it earlier. You get listened to when you are “Being the Expert”. Even though you may say what Sarah had already pointed out last month, you get listened to. Sarah is not going to be happy about that. Sarah is going to be especially annoyed and resentful if she told Me, the expert, about the point I raised. In these situations I try and emulate what a friend of mine taught me about 10 years ago on “Being The Expert”. One of your jobs as an external consultant should be to tell the client to listen to their staff if their staff are getting things right. What the real problem is could well be that the client is not using the resources it already has. And you were, after all, hired to solve their problem.

The final thing I find strange that I’ll mention is this. As the expert I am constantly anxious I am going to be “found out”. I mean, right now, I am doing my final report on this assignment. I know I identified several issues, I backed them up with evidence, I moved the client forward. I found out things that they had not known. I taught some of the staff new stuff. I stressed that I will not have found everything as it was only 3 days with no access to the live system… But I worry that in 3 weeks I’ll hear that none of what I suggested worked and that the REAL issue was something I utterly missed and when they corrected that, the run time went down by a factor of a thousand. And I failed them.

I just worry about that. Because I am “Being the Expert”

Measuring Tuxedo Queuing in the PeopleSoft Application Server

Why Should I Care About Queuing?

Queuing in the application server is usually an indicator of a performance problem, rather than a problem in its own right.  Requests will back up on the inbound queue because the application server cannot process them as fast as they arrive.  This is usually seen on the APPQ which is serviced by the PSAPPSRV process, but applies to other server processes too.  Common causes include (but are not limited to):

  • Poor performance of either SQL on the database or PeopleCode executed within the application server is extending service duration
  • The application server domain is undersized for the load.  Increasing the number of application server domains or application server process may be appropriate.  However, before increasing the number of server process it is necessary to ensure that the physical server has sufficient memory and CPU to support the domain (if the application server CPU is overloaded then requests move from the Tuxedo queues to the operating system run queue).
  • The application server has too many server processes per queue causing contention in the systems calls that enqueue and dequeue requests to and from IPC queue structure.  A queue with more than 8-10 application server processes can exhibit this contention.  There will be a queue of inbound requests, but not all the server processes will be non-idle.

When user service requests spend time queuing in the application server, that time is part of the users' response time.  Application server queuing is generally to be avoided (although it may be the least worst alternative). 
What you do about queuing depends on the circumstances, but it is something that you do want to know about.

3 Ways to Measure Application Server Queuing

There are a number of ways to detect queuing in Tuxedo

  • Direct measurement of the Tuxedo domain using the tmadmin command-line interface.  A long time ago I wrote a shell script tuxmon.sh.  It periodically runs the printqueue and printserver commands on an application server and extracts comma separated data to a flat that can then be loaded into a database.  It would have to be configured for each domain in a system.
  • Direct Measurement with PeopleSoft Performance Monitor (PPM).  Events 301 and 302 simulate the printqueue and printserver commands.  However, event 301 only works from PT8.54 (and at the time of writing I am working on a PT8.53 system).  Even then, the measurements would only be taken once per event cycle, which defaults to every 5 minutes.  I wouldn't recommend increasing the sample frequency, so this will only ever be quite a coarse measurement.
  • Indirect Measurement from sampled PPM transactions.  Although includes time spent on the return queue and to unpack the Tuxedo message.  This technique is what the rest of this article is about.

Indirectly Measuring Application Server Queuing from Transactional Data

Every PIA and Portal request includes a Jolt call made by the PeopleSoft servlet to the domain.  The Jolt call is instrumented in PPM as transaction 115.  Various layers in the application server are instrumented in PPM, and the highest point is transaction 400 which where the service enters the PeopleSoft application server code.  Transaction 400 is always the immediate child of transaction 115.  The difference in the duration of these transactions is the duration of the following operations:

  • Transmit the message across the network from the web server to the JSH.  There is a persistent TCP socket connection.
  • To enqueue the message on the APPQ queue (including writing the message to disk if it cannot fit on the queue).
  •  Time spent in the queue
  • To dequeue the message from the queue (including reading the message back from disk it was written there).
  • To unpack the Tuxedo message and pass the information to the service function
  • And then repeat the process for the return message back to the web server via the JSH queue (which is not shown  in tmadmin)

I am going make an assumption that the majority of the time is spent by message waiting in the inbound queue and that time spent on the other activities is negligible.  This is not strictly true, but is good enough for practical purposes.  Any error means that I will tend to overestimate queuing.
Some simple arithmetic can convert this duration into an average queue length. A queue length of n means that n requests are waiting in the queue.  Each second there are n seconds of queue time.  So the number of seconds per second of queue time is the same as the queue length. 
I can take all the sampled transactions in a given time period and aggregate the time spent between transactions 115 and 400.  I must multiply it by the sampling ratio, and then divide it by the duration of the time period for which I am aggregating it.  That gives me the average queue length for that period.
This query aggregates queue time across all application server domains in each system.  It would be easy to examine a specific application server, web server or time period.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 85%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">WITH c AS (
SELECT B.DBNAME, b.pm_sampling_rate
, TRUNC(c115.pm_agent_Strt_dttm,'mi') pm_agent_dttm
, A115.PM_DOMAIN_NAME web_domain_name
, SUBSTR(A400.PM_HOST_PORT,1,INSTR(A400.PM_HOST_PORT,':')-1) PM_tux_HOST
, SUBSTR(A400.PM_HOST_PORT,INSTR(A400.PM_HOST_PORT,':')+1) PM_tux_PORT
, A400.PM_DOMAIN_NAME tux_domain_name
, (C115.pm_trans_duration-C400.pm_trans_duration)/1000 qtime
FROM PSPMAGENT A115 /*Web server details*/
, PSPMAGENT A400 /*Application server details*/
, PSPMSYSDEFN B
, PSPMTRANSHIST C115 /*Jolt transaction*/
, PSPMTRANSHIST C400 /*Tuxedo transaction*/
WHERE A115.PM_SYSTEMID = B.PM_SYSTEMID
AND A115.PM_AGENT_INACTIVE = 'N'
AND C115.PM_AGENTID = A115.PM_AGENTID
AND C115.PM_TRANS_DEFN_SET=1
AND C115.PM_TRANS_DEFN_ID=115
AND C115.pm_trans_status = '1' /*valid transaction only*/
--
AND A400.PM_SYSTEMID = B.PM_SYSTEMID
AND A400.PM_AGENT_INACTIVE = 'N'
AND C400.PM_AGENTID = A400.PM_AGENTID
AND C400.PM_TRANS_DEFN_SET=1
AND C400.PM_TRANS_DEFN_ID=400
AND C400.pm_trans_status = '1' /*valid transaction only*/
--
AND C115.PM_INSTANCE_ID = C400.PM_PARENT_INST_ID /*parent-child relationship*/
AND C115.pm_trans_duration >= C400.pm_trans_duration
), x as (
SELECT dbname, pm_agent_dttm
, AVG(qtime) avg_qtime
, MAX(qtime) max_qtime
, c.pm_sampling_rate*sum(qtime)/60 avg_qlen
, c.pm_sampling_rate*count(*) num_services
GROUP BY dbname, pm_agent_dttm, pm_sampling_rate
)
SELECT * FROM x
ORDER BY dbname, pm_agent_dttm
  • Transactions are aggregated per minute, so the queue time is divided by 60 at the end of the calculation because we are measuring time in seconds.

Then the results from the query can be charted in excel (see http://www.go-faster.co.uk/scripts.htm#awr_wait.xls). This chart was taken from a real system undergoing a performance load test, and we could see

Is this calculation and assumption reasonable?

The best way to validate this approach would be to measure queuing directly using tmadmin.  I could also try this on a PT8.54 system where event 301 will report the queuing.  This will have to wait for a future opportunity.
However, I can compare queuing with the number of busy application servers at reported by PPM event 302 for the CRM database.  Around 16:28 queuing all but disappears.  We can see that there were a few idle application servers which is consistent with the queue being cleared.  Later the queuing comes back, and most of the application servers are busy again.  So it looks reasonable.
Application Server Activity

SQL for Beginners : Videos and Articles

love-sqlI’ve been saying for some time I should do some more entry level content, but it’s been kind-of hard to motivate myself. I mostly write about things I’m learning or actively using, so going back and writing entry level content is not something that usually springs to mind.

Recently I’ve got involved in a number of “grumpy old man” conversations about the lack of SQL knowledge out there. That, combined with a few people at work getting re-skilled, prompted me to get off my ass and give it a go. It’s actually quite difficult trying to get yourself into the head-space of someone who is coming fresh to the subject. You don’t want to pitch it too low and sound patronizing, but then pitching it too high makes you sounds like an elitist dick.

Anyway, after completing the Efficient Function Calls from SQL series of videos, I decided to jump into a SQL for Beginners series. I’m also putting out some articles, which are essentially transcripts of the videos, to allow people to copy/paste the examples. More importantly, they have links to articles with more details about the subject matter.

Once I’ve done a quick pass through the basics, I’ll start adding a bit more depth. I’ll probably dip in and out of the series. If I stick with it too long I’ll probably go crazy from boredom. :)

If you know someone who is fresh to SQL, can you ask them to take a look and give me some feedback? It would be nice to know if they are helpful or not.

Cheers

Tim…


SQL for Beginners : Videos and Articles was first posted on September 4, 2015 at 8:54 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Histogram Tip

I’ve just responded to the call for items for the “IOUG Quick Tips” booklet for 2015 – so it’s probably about time to post the quick tip that I put into the 2014 issue. It’s probably nothing new to most readers of the blog, but sometimes an old thing presented in a new way offers fresh insights or better comprehension.

Histogram Tips

A histogram, created in the right way, at the right time, and supported by the correct client-side code, can be a huge benefit to the optimizer; but if you don’t create and use them wisely they can easily become a source of inconsistent performance, and the automatic statistics gathering can introduce an undesirable overhead during the overnight batch. This note explains how you can create histograms very cheaply on the few columns where they are most likely to have a beneficial effect.

set_column_stats

The dbms_stats package have many procedures and functions built into it that allow us to see (get) and manipulate (set) the stored statistics; in particular it holds two functions get_column_stats() and set_column_stats(), and we can use these procedures to create a histogram very cheaply whenever we want at very low cost. Here’s an example that could be modified to suit a character column in a table where you’ve previously collected some stats. It uses a copy of all_objects, limited to exactly 10,000 rows.


create table t1 as
select
	*
from
	all_objects
where
	rownum <= 10000 ; begin dbms_stats.gather_table_stats( ownname => user,
		tabname		 =>'T1',
		method_opt 	 => 'for all columns size 1'
	);
end;
/


declare

	m_distcnt		number;		-- num_distinct
	m_density		number;		-- density
	m_nullcnt		number;		-- num_nulls
	m_avgclen		number;		-- avg_col_len

	srec			dbms_stats.statrec;
	c_array		dbms_stats.chararray;

begin

	dbms_stats.get_column_stats(
		ownname		=> user,
		tabname		=> 't1',
		colname		=> 'object_type', 
		distcnt		=> m_distcnt,
		density		=> m_density,
		nullcnt		=> m_nullcnt,
		srec			=> srec,
		avgclen		=> m_avgclen
	); 

	c_array		:= dbms_stats.chararray('A', 'B', 'C', 'X', 'Y');
	srec.bkvals	:= dbms_stats.numarray (  2,   2,   2, 500, 494);
--	srec.rpcnts	:= dbms_stats.numarray (  0,   0,   0,   0,   0);
	srec.epc := 5;

	dbms_stats.prepare_column_values(srec, c_array);

	m_distcnt	:= 5;
	m_density	:= 1/(5000);

	dbms_stats.set_column_stats(
		ownname		=> user,
		tabname		=> 't1',
		colname		=> 'object_type', 
		distcnt		=> m_distcnt,
		density		=> m_density,
		nullcnt		=> m_nullcnt,
		srec			=> srec,
		avgclen		=> m_avgclen
	); 

end;
/

Key features of the code: as you can see, the two calls have identical parameters which identify the table and column name (there is an optional parameter for a (sub) partition name), and most of the basic statistics about the column. The histogram (or low and high values) are accessed through a special record type, and we can populate that record type by supplying an ordered list of values, a matching list of frequencies, and a count of how many values we have supplied.

Since my code is fixing stats on a varchar2() column I’ve declared an array of type dbms_stats.chararray to hold the list of values I want to see in a frequency histogram – there are other array types for dates, raw, number, etc. I’ve then used the structure of the stats record I had declared to hold the list of frequencies (srec.bkvals – possibly a short name for “bucket values”) and the count (srec.epc“end-point count”).

The call to dbms_stats.prepare_column_stats() takes my two arrays and massages them into the correct format for storage as a histogram that I can then write into the data dictionary with the closing call to dbms_stats.set_column_stats(). Before making that call, though, I’ve also set the “num_distinct” variable to tell the optimizer that there are 5 distinct values for the column (it makes sense, but isn’t absolutely necessary, for the num_distinct to match the number of values in the array), and set the “density” to a value that I would like the optimizer to use in it calculations if someone asks for a value that is not in my list.

I’ve included (but commented out) a line that’s relevant to the new histogram mechanisms in 12c –the srec.rpcnts (“repeat counts”) array is used in “hybrid histograms”. It’s not relevant to my example where I’m trying to create a pure frequency histogram, but if I don’t set the array I get an Oracle error: “ORA-06532: Subscript outside of limit”.

Results

There’s one important point to the method that isn’t instantly visible in the code – I created my table with 10,000 rows and there will be no nulls for object_type; but if you sum the array of frequencies it comes to exactly 1,000. This apparent contradiction is not a problem – the optimizer will compare the histogram figures to the total number of non-null entries it has recorded (in other words user_tables.num_rowsuser_tab_columns.num_nulls), and scale up the histogram accordingly. This means that a query for ‘A’ should return an estimated row count of 20 (rather than 2), ‘X’ should return 5,000 (rather than 500) and ‘D’ should return 2 (10,000 rows * 1/5000, the selectivity I had set for non-existent values).

With a little editing to save space, here’s a cut-n-paste from an SQL*Plus session running against 12c:


SQL> set autotrace traceonly explain
SQL> select * from t1 where object_type = 'A';

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    20 |  2100 |    22   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |    20 |  2100 |    22   (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='A')

SQL> select * from t1 where object_type = 'X';

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  5000 |   512K|    22   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |  5000 |   512K|    22   (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='X')

SQL> select * from t1 where object_type = 'D';

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   105 |    22   (5)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |   105 |    22   (5)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_TYPE"='D')

Conclusion

It is very easy to create truly representative histograms (if you know your data) and the resources required to do so are minimal. If you see instability due to bad luck, or bad timing, gathering a histogram then you benefit enormously from writing code to construct histograms.

Footnote

In 12c the introduction of the “approximate NDV” strategy to collecting frequency histograms, and the introduction of the “Top-frequency” histogram has made automatic gathering of histograms on columns with a relatively small number of distinct values much faster and safer – but timing may still be an issue, and the resources needed to gather a safe hybrid histogram may still justify a hand-coded approach.

 

Foreign Keys and Library Cache Locks

In this post I would like to describe a behavior of Oracle Database that, at least for me, isn’t obvious at all. Actually, it’s something that I can’t explain why it works in that way.

Let’s start by setting the scene by describing the schema I’m using for the following tests. As you can see from the image, there are three tables: one table (PARENT) that is referenced by two other tables (CHILD1 and CHILD2). Schema used for the tests In my case every table is owned by a different schema (P, C1 and C2 respectively). But, the behavior I describe is independent from that fact (i.e. it works in the same way if all tables are owned by the same schema). If you are interested, here is the SQL*Plus script I used to create them.

What I want to describe is what happens if there is a session that inserts data into CHILD2 and, concurrently, another session enables a disabled foreign key (CHILD1_PARENT_FK) on CHILD1. But, before looking at what happens when such operations are concurrently executed, let’s review which locks are taken when the two SQL statements are not concurrently executed. Note that to have a list of the locks I use event 10704 at level 16 (I don’t use a V$ or data dictionary view because I want to have a list of all locks involved, not just the locks that are held at a specific time). And, since event 10704 references the objects through their ID in hexadecimal form, here is the mapping between the table names and their ID.

SQL> SELECT object_id, to_char(object_id,'0XXXXXXX') AS object_id_hex, object_name
  2  FROM dba_objects
  3  WHERE (owner = 'P' AND object_name = 'PARENT')
  4  OR (owner = 'C1' AND object_name = 'CHILD1')
  5  OR (owner = 'C2' AND object_name = 'CHILD2');

 OBJECT_ID OBJECT_ID_HEX OBJECT_NAME
---------- ------------- --------------------
    106390  00019F96     PARENT
    106392  00019F98     CHILD1
    106399  00019F9F     CHILD2

To check what the enable foreign key constraint statement does, I use the following SQL statements:

SQL> CONNECT c1/c1
SQL> ALTER TABLE child1 DISABLE CONSTRAINT child1_parent_fk;
SQL> ALTER SESSION SET events = '10704 trace name context forever, level 16';
SQL> ALTER TABLE child1 ENABLE CONSTRAINT child1_parent_fk;

The trace file lines related to the three tables of the test schema are the following (note that the function ksqgtl gets the lock and the function ksqrcl releases it):

*** 2015-08-27 20:52:28.623
ksqgtl *** OD-00019F98-00000000-00000000-00000000 mode=4 flags=0x10400 timeout=0 ***
*** 2015-08-27 20:52:28.624
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-27 20:52:28.625
ksqgtl *** TM-00019F96-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-27 20:52:51.107
ksqrcl: TM-00019F96-00000000-00000000-00000000
*** 2015-08-27 20:52:51.107
ksqrcl: TM-00019F98-00000000-00000000-00000000
*** 2015-08-27 20:52:51.107
ksqrcl: OD-00019F98-00000000-00000000-00000000

Based on this information, three locks are taken:

  • An OD lock in shared mode (4) to prevent concurrent online DDL statements
  • A shared table lock (TM in mode 4) on PARENT
  • A shared table lock (TM in mode 4) on CHILD2

Also note that the locks are released about 22 seconds after being got. This is because the validation of the constraint took about 22 seconds and, during the validation, the locks must be hold.

To check what the INSERT statement does, I use the following SQL statements:

SQL> CONNECT c2/c2
SQL> ALTER SESSION SET events = '10704 trace name context forever, level 16';
SQL> INSERT INTO child2 SELECT 2, 1, NULL FROM child2;

The trace file lines related to the three tables of the test schema are the following (note that since the transaction is neither committed nor rolled back, the locks are not released):

*** 2015-08-27 20:47:54.437
ksqgtl *** TM-00019F96-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***
*** 2015-08-27 20:47:54.437
ksqgtl *** TM-00019F9F-00000000-00000000-00000000 mode=3 flags=0x400 timeout=21474836 ***
*** 2015-08-27 20:47:54.438
ksqgtl *** TX-0004000C-00001D1C-00000000-00000000 mode=6 flags=0x401 timeout=0 ***

Based on this information, three locks are taken:

  • A subexclusive table lock (TM in mode 3) on PARENT
  • A subexclusive table lock (TM in mode 3) on CHILD2
  • An exclusive transaction lock (TX in mode 6)

Now that I have described what locks are involved, it’s time to describe what happens when the two operations are concurrently executed. It goes without saying that even though the two operations are executed concurrently, one of the two is actually executed before the other. Hence, let’s discuss both cases:

  • INSERT statement first: the SQL statement that enables the constraint can’t be executed until the transaction that inserts the row terminates (note that the one that disables the constraint doesn’t have such a limitation). By default, the ALTER TABLE statement raises an ORA-00054. To avoid the error, it’s possible to specify a timeout through the DDL_LOCK_TIMEOUT parameter. The following video shows an example.

  • ALTER TABLE statement first: the SQL statement that inserts the row can’t be executed until the SQL statement that enables the constraint terminates. The strange thing, at least for me, is that the INSERT statement waits on a “library cache lock”. The following video shows an example.

As the previous video shows, even though the ALTER TABLE statement executed on CHILD1 can’t impact the outcome of the INSERT statement executed against CHILD2, the INSERT statement waits on a “library cache lock” until the execution of the ALTER TABLE statement terminates.
Completely avoiding such waits isn’t possible. That said there is a workaround to mitigate their impact: enable the constraint in two steps as shown by the following SQL statements:

ALTER TABLE child1 ENABLE NOVALIDATE CONSTRAINT child1_parent_fk;
ALTER TABLE child1 ENABLE CONSTRAINT child1_parent_fk;

What’s the difference from a locking point-of-view?

With the help of event 10704, let’s have a look to the locks that are got during the execution of these two SQL statements.

  • The ENABLE NOVALIDATE statement, as the following information shows, gets two shared table locks (TM in mode 4) on PARENT and CHILD1 (notice that since no validation takes place, they are immediate released):
*** 2015-08-28 03:00:48.872
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-28 03:00:48.872
ksqgtl *** TM-00019F96-00000000-00000000-00000000 mode=4 flags=0x400 timeout=0 ***
*** 2015-08-28 03:00:48.886
ksqrcl: TM-00019F98-00000000-00000000-00000000
*** 2015-08-28 03:00:48.886
ksqrcl: TM-00019F96-00000000-00000000-00000000
  • The ENABLE statement, as the following information shows, uses a three steps approach. First, it gets an OD lock lock in shared mode (4) and a subshare table lock (TM in mode 2) on CHILD1. But, notice the timestamps, it immediately releases them. Second, it validates the constraint (in this case it takes about 27 seconds). Third, it gets and immediately releases a subshare table lock (TM in mode 2) on CHILD1.
*** 2015-08-28 03:06:40.680
ksqgtl *** OD-00019F98-00000000-00000000-00000000 mode=4 flags=0x10400 timeout=0 ***
*** 2015-08-28 03:06:40.681
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=2 flags=0x400 timeout=0 ***
*** 2015-08-28 03:06:40.696
ksqrcl: TM-00019F98-00000000-00000000-00000000
*** 2015-08-28 03:06:40.696
ksqrcl: OD-00019F98-00000000-00000000-00000000
*** 2015-08-28 03:07:07.803
ksqgtl *** TM-00019F98-00000000-00000000-00000000 mode=2 flags=0x400 timeout=0 ***
*** 2015-08-28 03:07:07.821
ksqrcl: TM-00019F98-00000000-00000000-00000000
ksqrcl: returns 0

As a result, with the two-steps approach (an ENABLE NOVALIDATE followed by an ENABLE), the contention issues due to foreign key can be mitigated. The following video shows an example:

Speaking in Portland at NWOUG Monday Sept 14

Looking forward to speaking at

Please come join me !

I lived in Portland between 2006-2008 and love city. It will be fun being back.

8:15 – 9:00 Keynote “EBS Strategy and Roadmap” – Vanessa Paskill

Technical Apps Functional Vendor Oracle
9:15 10:15 Practical Tips for Oracle Business Intelligence Applications 11g Implementations (Michael Raney, Rittman Mead) Lessons learned while Integrating Procurement Cards with Internet Expense (Ashish Nagarkar, AST Corporation) Enhanced Financial Controls and Automation for Oracle (BlackLine) Oracle Database Compression Best Practices (Gregg Christman)
10:45 11:45 Moving ahead with BI Publisher on your next Cloud project (Nelly Bushing, JIBE Consulting) PBCS: FP&A in the Cloud – Time to get on board! (Steve Wyatt, Jibe Consulting) Learn Role-Based Access Controls: Improve Security in Oracle EBS and Prepare for Fusion Cloud (FulcrumWay) Business Intelligence – Big Data: The Road to Know More About Your Business (John Drahos)
12:45 1:45 Identifying High Risk SQL Before The Upgrade (Craig Shallahamer, OraPub) Creating Business Requirements You Can Use to Move Your Business to Oracle (Meryl Logue, PMP, CDK Global) Drillbridge: The Easy and Free Way to Implement Relational Drillthrough (Key Performance Ideas) EBS Applications Technology How Oracle Enables the Cloud TBA
2:15 3:15 One Click Provisioning With Enterprise Manager 12c (Josh Turner, Seattle Pacific University) 12 Subledger And EBS Analyzers For Payables In Depth Discussion and Usages (Satya Mahanti, UTI) Query Tuning with Toad Beyond Fire-Fighting (Dell Software) Business Intelligence – Metrics That Matter: Drive bottom line impact with Oracle’s Pre- Built Business Intelligence Applications (Todd LaFavers)
3:30 4:30 Ask The Experts! (Invited panel members) DBAs Double your developers productivity – Kyle Hailey

.

15922772522_9f869b5859_k

photo by sama093

 

 

Updating the Raspberry Pi Vbox Image

So one of the things I like to do is test out a lot of my python gaming code, (as far as for the coding and syntax) on my Oracle Virtualbox image of my Raspberry Pi.  Another great thing about building games on the Raspberry Pi, is a built in module called Pygame.  Well, that doesn’t exist on the Raspberry Pi image and it really limits what we can do when it comes to the VNC build we play on the Oracle Virtualbox, (that’s a mouthful… :))

Get Outside Access

The first step is to first get out to all the libraries, as the ports the image is a bit locked down.  At the top, click on Devices, Network, Network Settings.  Add a check mark for “Cable Connected” and then click on the “Port Forwarding” settings.  Update the settings to reflect what I have for the open ports, (remember, this is Oracle Virtualbox… :))

vbox_rpi1

Once you’ve updated the Host and Guest Port, click OK.

Installing Pygame

As I want to stay with Python 3 as much as possible, we’re going to install it with our Vbox image with the Python 3 build.  Let’s start by installing:

sudo apt-get install mercurial

Choose “Y” for both questions and proceed with the installation.  There are a lot of files here, (over 500) so it will take just a few minutes to install everything.  Now, we need to run the clone step:

sudo hg clone https://bitbucket.org/pygame/pygame

Once this complete, which is just a matter of seconds, again, choosing “Y” for the questions, we need to build the dependencies after we download those:

sudo apt-get build-dep pygame
sudo apt-get install python3-dev
sudo apt-get install python3-numpy
cd pygame
sudo python3 config.py

You shouldn’t run into any failures at this step, as I didn’t with my own image.  If you do, you can go back to the first step and instead run:

sudo apt-get update

If all is successful at this point, then it’s time to build and install:

sudo python3 setup.py build
sudo python3 setup.py install

Alsa Sound Libraries

Now we need the Alsa Sound Library that isn’t in this image.  These are called by the Pygame module and come by default on Raspberry Pi, but yep, not here! You can check first to verify that they aren’t there, (if you’re curious…):

apt-cache policy alsa-base libasound2 alsa-tools

Now run the install with the following command:

sudo apt-get install alsa-utils
sudo apt-get install alsamixer

You’ll now see the ALSA sounds libraries if you run the check command again.

Now you’re ready to code Pygame games on your Virtualbox image of the Raspberry Pi! :)



Tags:  , ,


Del.icio.us



Facebook

TweetThis

Digg

StumbleUpon




Copyright © DBA Kevlar [Updating the Raspberry Pi Vbox Image], All Right Reserved. 2015.

Oracle Midlands : Event #11 – Summary

oracle-midlandsLast night was Oracle Midlands event #11 with Chris Antognini.

The lead up to this event was not the best for me. I had been on the verge of a headache all day. By 14:00 I gave up, went home and went to sleep for a couple of hours. It wasn’t great, but it was just enough to take the edge off, so when the time came, I felt sort-of OK to head out for the event. The drive started to convince me this wasn’t the best move, but once I got to the event and sat down I figured I was going to make it. :)

Chris did two talks at the event.

The first talk had lots of people’s heads nodding. It’s kind-of depressing, but we’ve all seen, and continue to see, these same things happening again and again. I, like others in the audience, am convinced it is because of the lack of emphasis on database technologies in development. Too many frameworks encourage a hands-off approach to the database, hiding it behind persistence layers that end up doing a mediocre job, at best. Anyway, enough of my rambling. This session should be mandatory viewing once a month for every developer that goes near a database! :)

redstacktechThe second session was pretty neat too. I must admit I’ve become addicted to the Enterprise Manager 12c performance pages, so a couple of the things Chris mentioned took me by surprise, including the use of some V$ views that I assumed were part of the Diagnostics and Tuning Pack, but aren’t. I’m purposely going to avoid mentioning them here because I would want to confirm the status before accidentally leading someone astray, but the idea was, query the V$ view and you are good. Query the DBA_HIST_* view and you’ve sourced the information from the AWR, so you need the D&T pack. This definitely *does not* apply to all V$ views, but it’s worth checking out if you don’t have D&T, or you are working with standard edition.

I think the evening went really well. Thanks to Chris for coming to speak to us and thanks to the Oracle ACE Program for getting him across. Thanks to Red Stack Tech for sponsoring the event, allowing this to remain free. Thanks to Mike for doing a great job of keeping these events rolling. Of course, thanks to everyone for turning up after the Bank Holiday weekend. :)

Cheers

Tim…


Oracle Midlands : Event #11 – Summary was first posted on September 2, 2015 at 10:14 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

IN/EXISTS bugs

Here’s a simple data set – I’m only interested in three of the columns in the work that follows, but it’s a data set that I use for a number of different models:


execute dbms_random.seed(0)

create table t1
nologging
as
with generator as (
	select	--+ materialize
		rownum id 
	from dual 
	connect by 
		level <= 1e4
)
select
	trunc(dbms_random.value(0,1000))	n_1000,
	trunc(dbms_random.value(0,750))		n_750,
	trunc(dbms_random.value(0,600))		n_600,
	trunc(dbms_random.value(0,400))		n_400,
	trunc(dbms_random.value(0,90))		n_90,
	trunc(dbms_random.value(0,72))		n_72,
	trunc(dbms_random.value(0,40))		n_40,
	trunc(dbms_random.value(0,3))		n_3
from
	generator	v1,
	generator	v2
where
	rownum <= 1e6
;
create table t2 nologging 
as
select * from t1
;

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T1',
		method_opt 	 => 'for all columns size 1'
	);

	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'T2',
		method_opt 	 => 'for all columns size 1'
	);
end;
/

The columns I want to consider are n_3, n_400, and n_1000. As their names suggest the columns have 3, 400, and 1000 distinct values respectively and since I’ve used the dbms_random.value() function to generate the data the distinct values are fairly evenly spread across the million rows of the table.

Consider, then, the following two queries:


select
        *
from
        t1
where
        exists (
                select  null
                from    t2
                where   n_1000 = 0
                and     t2.n_400 = t1.n_400
                and     t2.n_3 = t1.n_3
        )
;


select
        *
from
        t1
where
        (t1.n_400, t1.n_3) in (
                select  t2.n_400, t2.n_3
                from    t2
                where   t2.n_1000 = 0
        )
;

The first point to check is that these two queries are logically equivalent.

Once you’re happy with that idea we can work out, informally, how many rows we should expect the queries ought to return: there are 1,200 combinations for (n_400, n_3) so each combination should return roughly 833 rows; if we pick 1,000 rows from the 1 million available we can expect to see 679 of those combinations (that’s Alberto Dell’Era’s “selection without replacement” formula that Oracle uses for adjusting num_distinct to allow for filter predicates). So we might reasonably suggest that the final number of rows as 833 * 679 = 565,607. It turns out that that’s a pretty good estimate – when I ran the query the result was actually 567,018 rows.

So what does Oracle produce for the two execution plans – here are the result from 12c (EXISTS first, then IN):


===================
Multi-column EXISTS
===================
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   920K|    34M|  1259  (11)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|      |   920K|    34M|  1259  (11)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | T2   |  1000 | 11000 |   610   (8)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | T1   |  1000K|    26M|   628  (11)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."N_400"="T1"."N_400" AND "T2"."N_3"="T1"."N_3")
   2 - filter("N_1000"=0)

===================
Equivalent IN query
===================
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   833K|    30M|  1259  (11)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT SEMI|      |   833K|    30M|  1259  (11)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | T2   |  1000 | 11000 |   610   (8)| 00:00:01 |
|   3 |   TABLE ACCESS FULL  | T1   |  1000K|    26M|   628  (11)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."N_400"="T2"."N_400" AND "T1"."N_3"="T2"."N_3")
   2 - filter("T2"."N_1000"=0)

The first thing to note is that the shape of the plans is identical, and the predicate sections are identical – but the final cardinalities are different. Clearly at least one of the cardinalities has to be wrong by a significant amount (7.5% or 10.4%, depending which way round you want to look at it). If you run the test on 11.2.0.4 you find that both plans give the same estimated row count – and it’s the 920,000 rows; so arguably 12c has “fixed” the IN subquery calculation, bringing it closer to a reasonable prediction, but it hasn’t fixed the EXISTS subquery calculation. That 833K prediction, by the way, is what you would expect to see with this data with a basic join – and a semi-join shouldn’t be able to produce more data than  a join.

But both predictions are way off the (informal) expectation, so how have they appeared ?

Working backwards it’s easy to spot that: 833K = 833 * 1,000: Oracle is behaving as if every single row identified in the subquery will produce a separate combination of (n_400, n_3). If we reverse engineer 920K we get: 920K / 833 = 1104 – it would appear that the optimizer thinks the 1,000 rows produced by the subquery will produce 1,104 distinct combinations of (n_400, n_3) so we how did the impossible 1,104 appear in the arithmetic.

If you apply the “selection without replacement” formula to picking 1,000 rows with 400 distinct values from 1,000,000 rows the expected number of distinct values (with rounding) will be 368; if you apply the formula for picking 1,000 rows with 3 distinct values from 1,000,000 rows the expected number will be 3. And 3 * 368 = 1,104. (Remember that in my original estimate I applied the formula after multiplying out the combination of distinct values). The optimizer is using its standard methods, but using internediate results in an unsuitable fashion.

It’s impossible to say what the impact of this particular code path – and the change on the upgrade – might be. The optimizer has over-estimated by 47% in one case and 62% in the other but (a) there may be something about my data that exaggerated an effect that few people will see in the wild and (b) in many cases getting in the right ballpark is enough to get a reasonable plan, and a factor of 2 is the right ballpark.

Of course, a few people will be unlucky with a few queries on the upgrade where the estimate changes – after all a single row difference in the estimate can cause the optimizer to flip between a hash join and a nested loop – but at least you’ve got a little extra information that might help when you see a bad estimate on an important semi-join.

So is there a workaround ? Given that I’ve got 12c, the obvious thing to try is to create a column group at both ends of the semi-join and see what happens. It shouldn’t really make any difference because column groups are targeted at the problems of correlated column – but we might as well try it:


execute dbms_stats.gather_table_stats(user,'t1',method_opt=>'for columns (n_400,n_3) size 1')
execute dbms_stats.gather_table_stats(user,'t2',method_opt=>'for columns (n_400,n_3) size 1')

Unfortunately when I did this the final cardinality estimate for both queries dropped to just 833 (the absence of a K on the end isn’t a typo!).

Manually unnesting got me closer:


select
        *
from
        (
        select  distinct n_3, n_400
        from    t2
        where   n_1000 = 0
        )       sq,
        t1
where   
        sq.n_400 = t1.n_400
and     sq.n_3 = t1.n_3
;

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |   649K|    33M|  1260  (11)| 00:00:01 |
|*  1 |  HASH JOIN           |      |   649K|    33M|  1260  (11)| 00:00:01 |
|   2 |   VIEW               |      |   779 | 20254 |   612   (8)| 00:00:01 |
|   3 |    HASH UNIQUE       |      |   779 |  8569 |   612   (8)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| T2   |  1000 | 11000 |   610   (8)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | T1   |  1000K|    26M|   628  (11)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("SQ"."N_400"="T1"."N_400" AND "SQ"."N_3"="T1"."N_3")
   4 - filter("N_1000"=0)

The cardinality of 649K is (allowing for rounding) 833 * 779; so we need to know where the 779 came from. It’s the optimizer standard arithmetic for “distinct” – multiply the N individual selectivities together then divide by the sqrt(2) “N-1” times. So we apply the “selection without replacement formula twice”:

  • adjusted selectivity of n_400 = 367.21
  • adjusted selectivity of n_3 = 3
  • 367.21 * 3 / sqrt(2) = 779

If you create column group statistics for (n_400, n_3) this doesn’t change the optimizer’s estimate for the number of distinct combinations after selection – maybe that’s another enhancement in the pipeline – but, at least in this case, the manual unnesting has got us a little closer to the right estimates without any statistical intervention.

Footnote:

Just for the sake of completeness, here are the plans (with yet more cardinality predictions) that you get if you block the unnesting:


select 
	*
from 
	t1 
where 
	exists (
		select	
			/*+ no_unnest */
			null  
		from	t2 
		where	n_1000 = 0 
		and	t2.n_400 = t1.n_400 
		and	t2.n_3 = t1.n_3
	)
;



---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1179 | 33012 |   766K (12)| 00:00:30 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|    26M|   632  (11)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| T2   |     1 |    11 |   638  (12)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
              "N_1000"=0 AND "T2"."N_400"=:B1 AND "T2"."N_3"=:B2))
   3 - filter("N_1000"=0 AND "T2"."N_400"=:B1 AND "T2"."N_3"=:B2)



=====================================
Unnesting blocked and subquery pushed
=====================================
select 
	*
from 
	t1 
where 
	exists (
		select	
			/*+ no_unnest push_subq */
			null  
		from	t2 
		where	n_1000 = 0 
		and	t2.n_400 = t1.n_400 
		and	t2.n_3 = t1.n_3
	)
;

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      | 50000 |  1367K|  1271  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL | T1   | 50000 |  1367K|   632  (11)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T2   |     1 |    11 |   638  (12)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST */ 0 FROM "T2"
              "T2" WHERE "N_1000"=0 AND "T2"."N_400"=:B1 AND "T2"."N_3"=:B2))
   2 - filter("N_1000"=0 AND "T2"."N_400"=:B1 AND "T2"."N_3"=:B2)

The 1179 comes from the magic of sqrt(2):  1179 = 1,000,000 / (400 * 3 / sqrt(2)).

The 50,000 is just the basic “I dunno, let’s call it 5%”.

 

Reference script: aggregate_selectivity_c.sql

 

Index Usage – 4

Here’s a thought that came to me while I was writing up a note about identifying redundant indexes a few minutes ago. Sometimes you end up supporting applications with unexpected duplication of data and indexes and need to find ways to reduce overheads. Here’s some code modelling a scenario that I’ve seen more often than I like (actually, just once would be more often than I’d like):


create table t1
nologging
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e5
)
select
        rownum                                          id,
        trunc(sysdate,'MM') + (rownum-1)/1440           date_time,
        trunc(sysdate,'MM') + trunc((rownum-1)/1440)    date_only,
        rpad('x',100)                                   padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e5 ; begin dbms_stats.gather_table_stats( ownname => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );
end;
/

I’ve got a table holding one row per minute since the start of the month; there’s a column which holds the date and time accurate to the minute, and another column which is supposed to hold just the date part. Is it possible to create a single index that allows Oracle to handles queries relatively efficiently whether they refer to date_time or date_only ? As a starting step could we get an index range scan on the same index for both of the following queries:


select
        max(id)
from
        t1
where
        date_only between sysdate-1 and sysdate
;


select
        max(id)
from
        t1
where
        date_time between sysdate-1 and sysdate
;

As Bob the Builder likes to say: “yes we can”.

There are a few lines of SQL between the table creation and the stats gathering that I didn’t show you. The first creates the constraint that describes the relationship between date_time and date_only – one is the truncated version of the other; the second defines the index we need, and the third (unfortunately) has to be there to declare the date_time column as a mandatory column:

alter table t1
        add constraint t1_trunc_date
        check(
                  date_only = trunc(date_time)
              and (   (date_only is null and date_time is null)
                   or (date_only is not null and date_time is not null)
              )
        )
;

create index t1_i1 on t1(trunc(date_time)) nologging;

alter table t1 modify (date_time not null);

(Given the requirement for date_time to be not null to get my indexing strategy to work, we could simplify the t1_trunc_date constraint to just (date_only = trunc(date_time)) if we declared date_only to be not null as well).

With the extra lines of SQL included here are the resulting execution plans for the two queries (running on 11.2.0.4, but you get the same plans on 12.1.0.2):


=======================================
date_only between sysdate-1 and sysdate
=======================================

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    21 |    92   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE               |       |     1 |    21 |            |          |
|*  2 |   FILTER                      |       |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |  4306 | 90426 |    92   (2)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |  4306 |       |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!>=SYSDATE@!-1)
   3 - filter("DATE_ONLY"<=SYSDATE@! AND "DATE_ONLY">=SYSDATE@!-1)
   4 - access(TRUNC(INTERNAL_FUNCTION("DATE_TIME"))>=SYSDATE@!-1 AND
              TRUNC(INTERNAL_FUNCTION("DATE_TIME"))<=SYSDATE@!)
=======================================
date_time between sysdate-1 and sysdate
=======================================

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    21 |    92   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE               |       |     1 |    21 |            |          |
|*  2 |   FILTER                      |       |       |       |            |          |
|*  3 |    TABLE ACCESS BY INDEX ROWID| T1    |  1442 | 30282 |    92   (2)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | T1_I1 |  4306 |       |    13   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(SYSDATE@!>=SYSDATE@!-1)
   3 - filter("DATE_TIME"=SYSDATE@!-1)
   4 - access(TRUNC(INTERNAL_FUNCTION("DATE_TIME"))>=TRUNC(SYSDATE@!-1) AND
              TRUNC(INTERNAL_FUNCTION("DATE_TIME"))>=TRUNC(SYSDATE@!))

The optimizer has managed to generate extra predicates in both cases by applying transitive closure to the critical constraint to produce queries that can be addressed (with some inefficiencies) through the single index.

Within limits, therefore, I can reduce two indexes to a single index. The strategy isn’t ideal but it may be appropriate in a few special cases. There are several problems that should be considered carefully:

  • The date_time column has to be declared not null for this optimization strategy to appear – that’s going to limit its applicability.
  • You may have more complex code where the transformation simply can’t be made to appear.
  • The introduction of the trunc() function may change the optimizer’s arithmetic in ways that cause plans to change for the worse
  • (Most important) The index range scan is always a multiple of 24 hours, with the excess data discarded after you reach the table. If you have lots of time-based queries for short time intervals (e.g. less than 8 hours) then the extra work done may outweigh the benefit of reducing the number of indexes – especially if all the excess table visits turn into randomly scattered single block reads.

Despite these drawbacks you may decide that you have a case where the strategy is “good enough” to help you reduce the workload on your system at some critical times during the day or night.