Oracle Tips and Tricks — David Fitzjarrell

May 26, 2015

Stop That!

Filed under: General,Performance — dfitzjarrell @ 13:05

"It seems to me that almost everything is a waste of time."
- Norton Juster, The Phantom Tollbooth 

Jonathan Lewis has coined a term and its definition that, I believe, we’ll be using quite a bit:



Stoptimisation - the art of optimisation by not doing the things you don't need to do.

It’s a term we’ve needed for some time now, given the pendulum swing back to the days of yore (meaning pre-9i) with respect to database, application and query tuning. I’m seeing more and more posts in forums and newsgroups asking about tasks that I thought were long since relegated to the ‘gee, that’s really useless’ pile. Let’s look at a few of them and see why you probably shouldn’t be doing them,



Regular Index Rebuilds

Index rebuilds, for the most part, are busy work for a DBA. Yes, they can ‘clean house’ with respect to empty index blocks buried in the index tree but since the index will simply grow again to the size before the rebuild and the empty index blocks will again be scattered across the index tree I see no point in performing regularly scheduled index rebuilds. In fact, for non-partitioned indexes, I see no real reason to rebuild at all since it:



*	Locks the table and index until the rebuild is finished
*	Invalidates cursors using the associated table
*	Serves no useful purpose

Yes, there are times when local indexes on a partitioned table will need to be rebuilt, but those times are usually due to DDL against the partitioned table (dropping partitions, adding/splitting partitions, swapping partitions). The listed DDL actions can make local index partitions unusable, requiring them to be rebuilt. It’s not a ‘size’ thing, it’s not a ‘performance’ thing, it’s because the unusable index interferes with table access and thus production processes. What is troubling is the number of DBAs asking about regularly scheduled index rebuilds based on antiquated ‘criteria’ such as B-tree depth or index size. Even MOS has taken down it’s original document on when to rebuild an index and replaced it with a more responsible version that better addresses the topic. Still, there are DBAs firmly convinced that scheduling index rebuilds once a week, once a month through cron or DBMS_SCHEDULER must certainly improve the performance of queries and production processes. It doesn’t, and it really just inconveniences the end users while the index rebuilds are in progress. Since most application tables have more than one index in place, rebuilding indexes for a given table can take hours, and lock the table for the entire time, essentially shutting down production for the duration of the rebuilds. I can’t see how that improves performance.



Continually 'Tweaking' Database Parameters

There are DBAs who can’t seem to be satisfied with the performance of the database, and constantly hunt for ‘problem’ areas whether or not they actually affect performance. This is commonly referred to as Compulsive Tuning Disorder (henceforth referred to as CTD). CTD creates an endless cycle of ‘tune this, now tune that, now tune something else’, all based on the most minute deviationsa in wait statistics. CTD is one of the biggest time wasters for a DBA; it’s like Ponce De Leon’s search for the Fountain Of Youth. Not every wait statistic needs to be perfectly adjusted, and it’s impossible to do in the real world. It all boils down to what the DBA considers performance versus what the end-users consider performance. In the throes of CTD EVERYTHING is a potential ‘problem’ even if it doesn’t affect the end-user experience. On the end-user side a performance problem exists when processes take longer than they expect. Once the end-user problem has been addressed tuning should stop since further adjustments won’t provide any additional benefit. Wait statistics will never be ideal in a production system; multiple users accessing and modifying data will cause concurrency waits, no matter how small, and it’s useless to address such waits to get the response time perfect. CTD turns the most reliable of DBAs into a micro-manager of epic proportions and the efforts expended to ‘tweak’ parameters that have no bearing on the actual system performance are, honestly, useless.



Table Reorganization

As tables get larger and larger queries accessing data can gradually take longer to complete, notably if the query relies on a full table scan to fetch that data. There are still some DBAs who are convinced that reorganizing the table data will go a long way in improving performance. One of those reorganizations involves sorting the table data to improve the clustering factor. Yes, the clustering factor for an index is calculated based on the ‘location’ of the table data with respect to the sorted index keys, but ‘improving’ the clustering factor for one index usually makes the clustering factor worse for every other index against that table. Another point to make is that these are heap tables, with no real data order established; inserted data can go anywhere there is room in any data block associated with that table. Sorting the data only lasts until the first insert afterwards, after which index keys again become scattered through the table data. Years ago vendors argued that sorting the data was the best way to improve performance, and ‘suggested’ that their product be used on a regular basis to maintain this wonderful sorted order. The problems with that idea are that the data can be sorted for one index built against that table, not all and that the data will gradually return to its unsorted order as the end-users process data. This becomes, like CTD, a never-ending cycle of ‘sort, sort, sort, sort, sort’ all under the misguided notion that it’s making things better. It can’t, since every time the data is sorted the production system is rendered unavailable. If the end-users can’t work it doesn’t matter how fast the queries run AFTER the sort process has run. Unfortunately once the DBA has installed such blinders he or she is unaware of the inconvenience such processes create. The end-users need to work, not wait for some DBA to sort a pile of data only to have it return to being an unsorted pile a day or two down the road. The DBA should be working to create meaningful and lasting changes to improve performance; query tuning, plan stability and index analysis are worthwhile tasks that can produce tangible benefits that last far longer than the tasks it took to create them.

There are probably other time-wasters for the DBA that aren’t listed here; knowing what SHOULD be done versus what CAN be done is the hallmark of a great DBA. Looking again at the term introduced in this post should direct you toward useful tuning tasks that provide long-term benefit and that don’t require constant attention or repeated execution. Nothing is perfect, especially your database, so don’t try to make it so because you’ll only end up on The Endless Tuning Treadmill suffering from Compulsive Tuning Disorder. “Stoptimization” is what the DBA needs to be concerned with, and that’s not doing the things you don’t need to do. There is plenty of REAL work for the DBA to accomplish day-to-day. Don’t get wrapped up in the minutiae; it’s simply not worth the effort.


"For there's always something to do to keep you from what you really should be doing, ..."
- Norton Juster, The Phantom Tollbooth 

Ain’t that the truth.

May 20, 2015

That Depends

Filed under: General — dfitzjarrell @ 11:00

“They all looked very much like the residents of any small valley to which you've never been.” 
― Norton Juster, The Phantom Tollbooth 

In every universe there are questions that either:


Have no answer 
or 
Have an answer that is situation-dependent

and that includes the Oracle universe. Sometimes it’s difficult to get that concept across to the less knowledgeable. Let’s go back to a simple example from a previous post to illustrate this.

In Resurrecting The Dead flashback operations were discussed as an option to restore table data to before an errant update. The post started with this:

Occasionally an end-user does the unthinkable, and changes data permanently (or so it appears) without checking the results before issuing a commit. Ooops. 
It used to be that to restore that data one had basically two choices:

A) Restore the data from a known good copy made before the changes were enacted
B) Restore the table from the last known good export

Yes, there was a third choice, an incomplete tablespace recovery but that presumed the database to be in ARCHIVELOG mode and, gasp, there are databases 
running in NOARCHIVELOG mode where this wouldn't work.

We have a situation where data was updated and now needs to be reverted back with the question being “How do I do that?” And, this is one of those situation-dependent answers, the situation being the version of Oracle in use. If the user was using any version prior to 9.0.x there wasn’t much choice except to follow the path laid out at the start of the post. If that situation changed, and the version was either 9.0.x or 9.2.x, another solution presented itself:

So, what is a DBA to do? If you're running 9i you can enable flashback query by using the dbms_flashback package, which was nice but rather cumbersome 
in implementation, as it only allowed you to query the flashback data while enabled so updates to existing tables (to restore known good data) were 
somewhat convoluted in execution; it was necesary to enable flashback mode, open a cursor, then disable flashback mode to effect the updates:
declare
  --
  -- Cursor to fetch the required data
  --
  cursor c1 is
  select flerg, snerm, anguplast
  from snangpiester;

  --
  -- A place to stash each row of the returned data
  --
  c1_rec c1%rowtype;

begin

  --
  -- We flashback the data image to before the problem began
  --
  dbms_flashback.enable_at_time(to_timestamp('16-SEP-2007 06:53:00 AM','DD-MON-RRRR HH:MI:SS AM'));

  --
  -- Open the cursor while in flashback mode
  --
  open c1;

  --
  -- Disable flashback mode so we can update the problem table
  --
  dbms_flashback.disable;

  --
  -- Go fetch the good data and update the problem table
  --
  loop
          fetch c1 into c1_rec;
          exit when c1%notfound;  -- say goodbye when there's no more data
          update snangpiester
          set anguplast = c1_rec.anguplast
          where flerg = c1_rec.flerg
          and snerm = c1_rec.snerm;
  end loop;

  --
  -- Commit the changes
  --

  commit;

end;
/

So another possible solution presents itself. And there is a third solution, for those using version 10.1.x or greater, that was even more convenient. Let’s leave it to you to follow the provided link and read the original post. The point to be made is that some seemingly simple questions aren’t so simple to answer, since they depend on any number of conditions. This is why it is so important to provide the Oracle version in use as it may change a seemingly stock answer. Providing incomplete information can generate the dreaded response: “It depends.”

Questions that fall into this category include, but are not limited to, those on backup and recovery, databasec cloning, data replication and tuning. Each release of Oracle improves on the previous release, including how the optimizer works. Upgrades can improve or degrade performance based on the changes made to the optimizer so questions like “how can I make this query faster?” have no hard-and-fast answer, even though on the face of it the question seems simple.

I can’t count how many times I’ve seen or given the “It depends.” answer and I’m fairly certain that the person asking the question believes that response is forum-speak for “I don’t know.” Nothing could be further from the truth since “It depends.” is usually followed by the responder requesting more information so he or she has a fighting chance of giving a correct answer. Providing relevant details ‘up front’ makes it easier for forum participants to assess the situation and provide a usable response. “It depends.” isn’t a ‘cop-out’ (to use an ancient colloquialism coined before the dawn of time), it’s an indication that the question needs more information before it can be answered. It also means the forum participants care enough to give you an accurate answer, one you can actually use. Remember that they are taking time to participate in the forum; you should also take time to think through your question and provide as much detail as possible. Every little bit helps.

“How do I …” Well, it depends …

May 18, 2015

Perception Is Everything

Filed under: General,pl/sql — dfitzjarrell @ 08:22

"There is much worth noticing that often escapes the eye."
- Norton Juster, The Phantom Tollbooth

Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn’t prepared, ‘hide’ any errors that occur in the bulk processing list. A ‘plain vanilla’ EXCEPTION handler may not report all errors that are thrown. Let’s look at an example intentionally set up to fail inserts based on data from the EMP table. Table M is created with the EMP columns slightly re-ordered so the data types don’t match up to the source:


SQL> CREATE TABLE M
  2  	    (EMPNO NUMBER(4) NOT NULL,
  3  	     MGR NUMBER(4),
  4  	     JOB VARCHAR2(9),
  5  	     ENAME VARCHAR2(10),
  6  	     HIREDATE DATE,
  7  	     SAL NUMBER(7, 2),
  8  	     COMM NUMBER(7, 2),
  9  	     DEPTNO NUMBER(2));

Table created.

SQL>

The ENAME column is now fourth in the list, rather than second, If an attempt is made to simply ‘shove’ the EMP data into M it’s certain to generate a number of ‘invalid number’ errors, but if the PL/SQL loop and exception handler aren’t coded to take advantage of the BULK COLLECT error you’ll be trapping at most one of the many errors generated and that error alone will be reported:


SQL> DECLARE
  2  	type emp_tbl is table of emp%rowtype;
  3  	emp_data emp_tbl;
  4  	cursor EMPINFO is
  5  	select * from emp;
  6
  7
  8  BEGIN
  9  OPEN EMPINFO;
 10  LOOP
 11
 12  	FETCH EMPINFO BULK COLLECT INTO emp_data LIMIT 200;
 13  	EXIT WHEN emp_data.count = 0;
 14  	BEGIN
 15  	     DBMS_OUTPUT.PUT_LINE('Request rows ' || emp_data.COUNT);
 16  	     FORALL i IN 1..emp_data.COUNT
 17  		 INSERT INTO m VALUES emp_data(i);
 18
 19  	EXCEPTION
 20  	       WHEN others THEN -- Now we figure out what failed and why.
 21  		     -- Output desired error message
 22  		     dbms_output.put_line('-20999: Sumthin'' bad happened -- error stack follows');
 23  		     -- Output actual line number of error source
 24  		     dbms_output.put(dbms_utility.format_error_backtrace);
 25  		     -- Output the actual error number and message
 26  		     dbms_output.put_line(dbms_utility.format_error_stack);
 27  	END;
 28
 29  END LOOP;
 30  END;
 31  /
Request rows 18
-20999: Sumthin' bad happened -- error stack follows
ORA-06512: at line 16
ORA-01722: invalid number


PL/SQL procedure successfully completed.

SQL>

Taking another route the PL/SQL code is changed to provide a user-defined exception and variables to hold the error messages and numbers generated for each insert error. BULK COLLECT is also instrumented to save all of the exceptions generated. Running the modified code provides the following output:


SQL>
SQL> DECLARE
  2  	type emp_tbl is table of emp%rowtype;
  3  	emp_data emp_tbl;
  4  	cursor EMPINFO is
  5  	select * from emp;
  6  	errorCnt     number;
  7  	errString    varchar2(4000);
  8  	errCode      number;
  9  	dml_errors   exception;
 10  	pragma exception_init(dml_errors, -24381);
 11
 12
 13  BEGIN
 14  OPEN EMPINFO;
 15  LOOP
 16
 17  	FETCH EMPINFO BULK COLLECT INTO emp_data LIMIT 200;
 18  	EXIT WHEN emp_data.count = 0;
 19  	BEGIN
 20  	     DBMS_OUTPUT.PUT_LINE('Request rows ' || emp_data.COUNT);
 21  	     FORALL i IN 1..emp_data.COUNT SAVE EXCEPTIONS
 22  		 INSERT INTO m VALUES emp_data(i);
 23
 24  	EXCEPTION
 25  	       WHEN dml_errors THEN -- Now we figure out what failed and why.
 26  		     errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
 27  		     errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
 28  		     dbms_output.put_line(errString);
 29
 30  		     FOR i IN 1..errorCnt LOOP
 31  			 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE > 0
 32  			 THEN
 33  			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 34  			     dbms_output.put_line(errString);
 35  			 ELSE
 36  			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
 37  			     dbms_output.put_line(errString);
 38  			     RAISE;
 39  			 END IF;
 40  		     END LOOP;
 41  	END;
 42
 43  END LOOP;
 44  END;
 45  /
Request rows 18
Number of statements that failed: 18

Error #1
Error message is ORA-01722: invalid number

Error #2
Error message is ORA-01722: invalid number

Error #3
Error message is ORA-01722: invalid number

Error #4
Error message is ORA-01722: invalid number

Error #5
Error message is ORA-01722: invalid number

Error #6
Error message is ORA-01722: invalid number

Error #7
Error message is ORA-01722: invalid number

Error #8
Error message is ORA-01722: invalid number

Error #9
Error message is ORA-01722: invalid number

Error #10
Error message is ORA-01722: invalid number

Error #11
Error message is ORA-01722: invalid number

Error #12
Error message is ORA-01722: invalid number

Error #13
Error message is ORA-01722: invalid number

Error #14
Error message is ORA-01722: invalid number

Error #15
Error message is ORA-01722: invalid number

Error #16
Error message is ORA-01722: invalid number

Error #17
Error message is ORA-01722: invalid number

Error #18
Error message is ORA-01722: invalid number

PL/SQL procedure successfully completed.

SQL>

There are 18 rows in this slightly modified data set so 18 exceptions were generated. All of the exceptions were reported by the modified exception handler. Listing the changes made to the original code shows that three variables were declared (errorCnt, errString and errCode) and a user-defined exception was provided (dml_errors, initialized to error code -24381 (invalid number). The real ‘magic’ lies in the exception handler itself:


   	EXCEPTION
   	       WHEN dml_errors THEN -- Now we figure out what failed and why.
   		     errorCnt := SQL%BULK_EXCEPTIONS.COUNT;
   		     errString := 'Number of statements that failed: ' || TO_CHAR(errorCnt);
   		     dbms_output.put_line(errString);
   
   		     FOR i IN 1..errorCnt LOOP
   			 IF SQL%BULK_EXCEPTIONS(i).ERROR_CODE > 0
   			 THEN
   			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
   			     dbms_output.put_line(errString);
   			 ELSE
   			     errString := CHR(10) || 'Error #' || i || CHR(10) || 'Error message is ' ||  SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE);
   			     dbms_output.put_line(errString);
   			     RAISE;
   			 END IF;
   		     END LOOP;
   	END;

Each time the error is thrown in the loop the exception handler goes to work reporting the record number generating the error, the error number and the error text. Using SQL%BULK_EXCEPTIONS() array it’s possible to extract the error code and, by a call to SQLERRM, the associated error message. The handler can process both positive and negative error codes, and on negative error codes makes a call to RAISE to stop normal execution. Since no negative errors were thrown in this example the loop execution continued until all rows were processed, reporting every error encountered during the run.

Coding exception handlers correctly (to provide useful, rather than confusing, messages) is key in being able to let users report errors they encounter. Being able to see which records in a bulk collection are generating errors makes it even easier to diagnose and correct problems in bulk data processing, and knowing how to write such exception handlers provides that information to the end users. Using this template makes that task easier (hopefully) so the development team won’t be ‘shooting in the dark’ when a bulk loop process fails to process all of the desired records.

Using bulk processing when it’s appropriate can save time and effort; writing a proper exception handler can help tremendously in troubleshooting any errors that may arise.

It all depends upon how you look at things.

May 14, 2015

Some People’s Kids

Filed under: General — dfitzjarrell @ 13:39

"Expectations is the place you must always go to before you get to where you're going. Of course, 
some people never go beyond Expectations, but my job is to hurry them along whether they like it or not."
- Norton Juster, The Phantom Tollbooth

From time to time I find a situation where a developer believes that:


	He or she believes referential integrity is not necessary
or
	He or she is better at enforcing referential integrity than the database

In the first case they seem to ignore logic and let the chips fall where they may, which in my mind is a mistake. In the second case they code intricate sections generating unique ids based on the maximum value of the id column. They then use this ‘unique’ key to also enforce referential integrity in child tables. Unfortunately referential integrity isn’t transactional, which is one reason these attempts fail. Let’s look at an example and see how the situation can be improved by using the built-in features of Oracle.

An example table was created and populated, then a process written to insert new unique values in the table by using the max() of the intended key column:


SQL> --
SQL> -- Create test table
SQL> --
SQL> create table ins_test(keycol number, keyval varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Add primary key constraint
SQL> --
SQL> alter table ins_test add constraint ins_test_pk primary key (keycol);

Table altered.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> insert all
  2  into ins_test
  3  (keycol, keyval)
  4  values
  5  (1, 'First value')
  6  into ins_test
  7  (keycol, keyval)
  8  values
  9  (2, 'Second value')
 10  into ins_test
 11  (keycol, keyval)
 12  values
 13  (3, 'Third value')
 14  into ins_test
 15  (keycol, keyval)
 16  values
 17  (4, 'Fourth value')
 18  into ins_test
 19  (keycol, keyval)
 20  values
 21  (5, 'Fifth value')
 22  into ins_test
 23  (keycol, keyval)
 24  values
 25  (6, 'Sixth value')
 26  select * from dual;

6 rows created.

SQL>
SQL> --
SQL> -- Commit changes
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Start questionable process to
SQL> -- insert more data based upon
SQL> -- current max(keycol) value
SQL> --
SQL>
SQL> --
SQL> -- Set a variable to reuse the returned
SQL> -- max value
SQL> --
SQL> --
SQL> -- Rather dumb idea as the max() from
SQL> -- any query will miss any new values
SQL> -- inserted by other sessions and not
SQL> -- yet committed
SQL> --
SQL> column maxval new_value next_key
SQL>
SQL> --
SQL> -- Return current max(keycol)
SQL> --
SQL> -- Not reliable in the least
SQL> -- unless there is only one user
SQL> -- ever on the system, and it's
SQL> -- you
SQL> --
SQL> select max(keycol) maxval from ins_test;

    MAXVAL
----------
         6

SQL>
SQL> --
SQL> -- Use the saved value for the next insert
SQL> --
SQL> -- Not a good idea
SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keycol, keyval)
  2  values (&next_key + 1, 'Next value in line');
old   2: values (&next_key + 1, 'Next value in line')
new   2: values (         6 + 1, 'Next value in line')

1 row created.

SQL>

So far, so good, but let’s look at the second session:


SQL> @ins_w_var_second_sess.sql

    MAXVAL
----------
         7

old   2: values (&next_key + 1, 'Next value in line')
new   2: values (         7 + 1, 'Next value in line')
insert into ins_test (keycol, keyval)
*
ERROR at line 1:
ORA-00001: unique constraint (SYS.INS_TEST_PK) violated

PL/SQL procedure successfully completed.


Commit complete.

SQL>

The second session was waiting for the row-level lock to be released before processing the insert, which failed. This is the first reason performing such tasks in the application is a bad idea, it relies on transactions to generate key values, which, as you can see, tried to generate the same key in a second session as was generated in the first. This is also the reason why referential integrity cannot be enforced in the application code — the current transaction can’t see beyond itself, so any other key inserts to the table won’t be recognized and child inserts can then fail. This also makes for a very poorly performing application.

Let’s try this again with Oracle at the helm:


SQL>
SQL> --
SQL> -- Create test table
SQL> --
SQL> create table ins_test(keycol number, keyval varchar2(20));

Table created.

SQL>
SQL> --
SQL> -- Add primary key constraint
SQL> --
SQL> alter table ins_test add constraint ins_test_pk primary key (keycol);

Table altered.

SQL>
SQL> --
SQL> -- Add a sequence, and a trigger, to populate the key column
SQL> --
SQL> create sequence ins_test_seq
  2  start with 1
  3  increment by 1
  4  nomaxvalue
  5  nocycle
  6  order;

Sequence created.

SQL>
SQL> create or replace trigger pop_ins_test_pk
  2  before insert on ins_test
  3  for each row
  4  begin
  5          select ins_test_seq.nextval
  6          into :new.keycol
  7          from dual;
  8  end;
  9  /

Trigger created.

SQL>
SQL> --
SQL> -- Insert data
SQL> --
SQL> insert all
  2  into ins_test
  3  (keyval)
  4  values
  5  ('First value')
  6  into ins_test
  7  (keyval)
  8  values
  9  ('Second value')
 10  into ins_test
 11  (keyval)
 12  values
 13  ('Third value')
 14  into ins_test
 15  (keyval)
 16  values
 17  ('Fourth value')
 18  into ins_test
 19  (keyval)
 20  values
 21  ('Fifth value')
 22  into ins_test
 23  (keyval)
 24  values
 25  ('Sixth value')
 26  select * from dual;

6 rows created.

SQL>
SQL> --
SQL> -- Commit changes
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> insert into ins_test (keyval)
  2  values ('Next value in line');

1 row created.

SQL>
SQL> --
SQL> -- Wait for a minute so a second insert of this
SQL> -- same type can occur from a different session
SQL> --
SQL>
SQL> exec dbms_lock.sleep(60);

PL/SQL procedure successfully completed.

SQL>

Checking the second session we see it isn’t blocked and inserts a unique key value successfully:


SQL> --
SQL> -- Using a sequence and a trigger is far
SQL> -- more reliable and recommended
SQL> --
SQL> insert into ins_test (keyval)
  2  values ('Next value in line');

1 row created.

SQL>
SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>

Verifying that both inserts were successful and did, indeed, insert unique values:


SQL> --
SQL> -- Commit the changes, or try to
SQL> --
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- Select the data and verify keys are unique
SQL> --
SQL>
SQL> select * From ins_test;

    KEYCOL KEYVAL
---------- --------------------
         8 Next value in line
         1 First value
         2 Second value
         3 Third value
         4 Fourth value
         5 Fifth value
         6 Sixth value
         7 Next value in line

8 rows selected.

SQL>

Using the built-in functionality of Oracle provided a solution that does not rely on a transaction completing and truly generates unique data. Moving on to the referential integrity part of the discussion you will need to successfully insert every parent key before you can reference it in the child tables. Using the transactional method this cannot occur and you will regularly see:


SQL> insert into fk_test
  2  values (9, 'Next foreign key reference')
  3  /
insert into fk_test
*
ERROR at line 1:
ORA-02291: integrity constraint (GRIBNAUT.FK_TEST_FK) violated - parent key
not found


SQL>

This is not an occurrence you want to witness in any environment, especially production. And no matter how hard the developers try to get it right with their own code, it’s going to fail at some point due to the concurrency mechanism Oracle employs or because of transaction locks blocking other sessions.

As I said before, referential integrity isn’t transactional, which means any transactional method used to try and enforce it will eventually fail. This is why Oracle provides sequences and foreign key constraints, so that you won’t have to rely on the application trying to generate unique key values from a transactional operation. Maybe, just maybe, developers will stop trying to make that work in their code after reading this post.

Hope springs eternal.

May 1, 2015

Too Much Time

Filed under: General — dfitzjarrell @ 12:44
"It's bad enough wasting time without killing it." 
 Norton Juster, The Phantom Tollbooth 

AWR reports were a tremendous improvement over Statspack reports, primarily due to the depth and breadth of the sample data collected. A more detailed report, with extended metrics, provides a better look at what the database is doing during a given interval. Unfortunately this detail can create unusual information, especially in the SQL Elapsed Time section. For ‘normal’ queries (read that as ‘non-parallel’) the elapsed time is usually pretty accurate. Add in parallel query slaves and the overal elapsed time can grow to several times the actual clock time the query or statement consumed. Let’s set up an example, running in parallel, and see what the AWR report provides.

Let’s create a copy of EMP that has a non-0 degree of parallelism:

SQL> CREATE TABLE EMP
  2         (EMPNO NUMBER(4) NOT NULL,
  3          ENAME VARCHAR2(10),
  4          JOB VARCHAR2(9),
  5          MGR NUMBER(4),
  6          HIREDATE DATE,
  7          SAL NUMBER(7, 2),
  8          COMM NUMBER(7, 2),
  9          DEPTNO NUMBER(2))
 10  parallel 4;

Table created.

SQL>

Now let’s make it a fairly large table:

SQL> begin
  2  for i in 1..20 loop
  3     insert into emp
  4     select * from emp;
  5  end loop;
  6
  7  commit;
  8
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select count(*)
  2  from emp;

  COUNT(*)
----------
  14680064

SQL>

Verifying that parallel is being used to process queries:


SQL> select *
  2  from emp
  3  where empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
...
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

1048576 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |  1073K|    89M|  6543   (1)| 00:01:19 |        |      |            |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |  1073K|    89M|  6543   (1)| 00:01:19 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |  1073K|    89M|  6543   (1)| 00:01:19 |  Q1,00 | PCWC |            |
|*  4 |     TABLE ACCESS FULL| EMP      |  1073K|    89M|  6543   (1)| 00:01:19 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("EMPNO"<7400)

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
         17  recursive calls
          0  db block gets
     173077  consistent gets
      86341  physical reads
          0  redo size
   18036545  bytes sent via SQL>*Net to client
     769454  bytes received via SQL>*Net from client
      69907  SQL>*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
    1048576  rows processed

SQL>

Now generate an AWR report for the interval in question:

SQL ordered by Elapsed Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
  • %Total – Elapsed Time as a percentage of Total DB time
  • %CPU – CPU Time as a percentage of Elapsed Time
  • %IO – User I/O Time as a percentage of Elapsed Time
  • Captured SQL account for 98.0% of Total DB Time (s): 18
  • Captured PL/SQL account for 0.6% of Total DB Time (s): 18
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
10.36 1 10.36 58.70 19.57 73.06 avrt3mgztynzh SQL*Plus select /*+ parallel(12) */ * f…
4.96 1 4.96 28.12 96.15 1.89 572fbaj0fdw2b sqlplus.exe select output from table(dbms_…
0.20 1 0.20 1.11 103.08 0.00 bunssq950snhf insert into wrh$_sga_target_ad…
0.15 16 0.01 0.87 111.50 0.08 3s1hh8cvfan6w SQL*Plus SELECT PLAN_TABLE_OUTPUT FROM …
0.15 1 0.15 0.87 91.92 0.00 dayq182sk41ks insert into wrh$_memory_target…
0.15 1 0.15 0.86 92.16 0.00 bm2pwrpcr8ru6 select sga_size s, sga_size_fa…
0.14 16 0.01 0.82 108.24 0.08 g3f3cw3zy5aat SQL*Plus SELECT PLAN_TABLE_OUTPUT FROM …
0.13 16 0.01 0.72 122.57 0.00 f0a1c90fu7jtd SQL*Plus SELECT /*+ opt_param(‘parallel…
0.12 121 0.00 0.69 12.75 0.00 772s25v1y0x8k select shared_pool_size_for_es…
0.09 1 0.09 0.52 101.63 0.00 1bx8mgs8by25x sqlplus.exe select coalesce( p1s.parameter…

Notice that in this AWR report the elapsed time for our query is 10.36 seconds. The actual elapsed time, reported when timing is on, and the execution plan is:


Elapsed: 00:04:29.31

Execution Plan
----------------------------------------------------------
Plan hash value: 2873591275

--------------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |  1073K|    89M|  2181   (1)| 00:00:05 |        |      |         |
|   1 |  PX COORDINATOR      |          |       |       |            |          |        |      |         |
|   2 |   PX SEND QC (RANDOM)| :TQ10000 |  1073K|    89M|  2181   (1)| 00:00:05 |  Q1,00 | P->S | QC (RAND)  |
|   3 |    PX BLOCK ITERATOR |          |  1073K|    89M|  2181   (1)| 00:00:05 |  Q1,00 | PCWC |         |
|*  4 |     TABLE ACCESS FULL| EMP      |  1073K|    89M|  2181   (1)| 00:00:05 |  Q1,00 | PCWP |         |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("EMPNO"<=7400)

So why the discrepancy? Why does AWR report a longer elapsed time than actually occurred? Oracle Support addresses this in MOS note 1956033.1 and reports that this behavior is not a bug. According to Oracle Support the elapsed time (taken from the V$SQL view) includes time for the Query Coordinator and the parallel query slaves operating on that query. In a non-parallel execution such aggregation produces results very close, if not exactly matching, the wall clock time. In this case the elapsed time computed for the AWR report is over twice as long as the actual execution time. This doesn’t mean that there were two parallel query slaves in use; the query was hinted to provide a parallelism of 12. Thus, the sum of the elapsed time for the Query Coordinator and the elapsed time for each of the 12 parallel query slaves adds up to 10.36 seconds. If we set the degree for the EMP table to 1 and run a non-hinted query to get a serial execution time the result is:


Elapsed: 00:02:04.08

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  1073K|    89M| 23573   (1)| 00:04:43 |
|*  1 |  TABLE ACCESS FULL| EMP  |  1073K|    89M| 23573   (1)| 00:04:43 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMPNO"<=7400)

And another AWR report shows:

SQL ordered by Elapsed Time

  • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
  • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
  • %Total – Elapsed Time as a percentage of Total DB time
  • %CPU – CPU Time as a percentage of Elapsed Time
  • %IO – User I/O Time as a percentage of Elapsed Time
  • Captured SQL account for 97.1% of Total DB Time (s): 3
  • Captured PL/SQL account for 37.2% of Total DB Time (s): 3
Elapsed Time (s) Executions Elapsed Time per Exec (s) %Total %CPU %IO SQL Id SQL Module SQL Text
1.62 1 1.62 48.69 93.32 2.70 19duabgggdwaj SQL*Plus select * from emp where empno …
1.07 0 32.07 65.73 18.91 1uk5m5qbzj1vt SQL*Plus BEGIN dbms_workload_repository…
0.22 1 0.22 6.75 104.07 0.00 bunssq950snhf insert into wrh$_sga_target_ad…
0.18 1 0.18 5.55 75.93 0.14 32b51bs6x2ux7 SQL*Plus EXPLAIN PLAN SET STATEMENT_ID=…
0.17 2 0.08 4.98 47.02 14.20 gpf6csqpdaymp SQL*Plus select * From dba_scheduler_jo…
0.16 1 0.16 4.81 97.32 0.00 dayq182sk41ks insert into wrh$_memory_target…
0.16 1 0.16 4.77 98.25 0.00 bm2pwrpcr8ru6 select sga_size s, sga_size_fa…
0.10 1 0.10 2.88 0.00 74.78 6ajkhukk78nsr begin prvt_hdm.auto_execute( :…
0.07 66 0.00 2.23 0.00 96.67 5h7w8ykwtb2xt INSERT INTO SYS.WRI$_ADV_PARAM…
0.06 1 0.06 1.94 0.00 93.15 f0dtmk7ap93vx DBMS_SCHEDULER DECLARE object_type varchar2(4…
0.06 1 0.06 1.72 27.31 87.39 350myuyx0t1d6 insert into wrh$_tablespace_st…
0.04 2 0.02 1.13 83.07 1.25 3s1hh8cvfan6w SQL*Plus SELECT PLAN_TABLE_OUTPUT FROM …
0.04 2 0.02 1.08 87.09 1.31 g3f3cw3zy5aat SQL*Plus SELECT PLAN_TABLE_OUTPUT FROM …
0.04 34 0.00 1.06 0.00 0.00 772s25v1y0x8k select shared_pool_size_for_es…

In this report the elapsed time for the serial query (1.62 seconds) closely aligns with the reported elapsed time from SQL*Plus (2.04 seconds).

The question remains: how to get a fairly accurate elapsed time for queries executing in parallel? As demonstrated here using:


set timing on

is one way to get the elapsed time. Another, more accurate, way is to generate a 10046 trace for the query in question. The trace file, and the formatted output from tkprof, will provide the correct elapsed time for the query:


select /*+ parallel(12) */ *
from emp
where empno <= 7400

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          0          1          0           0
Execute      1      0.00       0.18          0         15          0           0
Fetch    69907      2.23       2.36          0          0          0     1048576
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    69909      2.23       2.58          0         16          0     1048576

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 184
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
   1048576    1048576    1048576  PX COORDINATOR  (cr=15 pr=0 pw=0 time=1086677 us)
         0          0          0   PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us cost=2181 size=93396936 card=1073528)
         0          0          0    PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=2181 size=93396936 card=1073528)
         0          0          0     TABLE ACCESS FULL EMP (cr=0 pr=0 pw=0 time=0 us cost=2181 size=93396936 card=1073528)

The elapsed time reported by the 10046 trace is less than that reported by ‘set timing on’ and that’s probably due to the time spent sending output to the display. The actual database elapsed time was under 3 seconds, with the elapsed time reported by SQL*Plue as 4.29 seconds. This is an acceptable discrepancy given that displaying results to a terminal screen takes time. I wouldn’t expect the times to match as two different mechanisms are in use to generate elapsed time between the two tools. They are close enough to know that the AWR report, aggregating time from V$SQL, is reporting inflated figures because of the parallel execution.

Parallel query execution can save time in some cases by using a ‘divide an conquer’ approach to result set processing. It is unfortunate tha the V$SQL performance view contains inflated elapsed times for queries run in parallel. Since Oracle Support does not consider this a bug it’s not going to be changed any time soon. Knowing this makes it easier to understand elapsed times in AWR reports when parallelism is used, and now you have at least two ways to generate fairly accurate elapsed execution times for such queries. It makes it a bit more involved to tune queries using parallism but the effort is worth the time and trouble.

Tims’s a wastin’.

Blog at WordPress.com.