Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Hibernate for Oracle DBAs

Warning: any smart developer may feel sick when reading this ;)

I am not a developer, but I like to discuss with developers: share my side of the IT (the database that we want rock stable and durable) and listen to their side (the application that they want easy to maintain and evolve). And, as I like to understand what I’m talking about, I often need to test some snippets.

Many DBAs complain about Hibernate when they come upon the queries generated by a wrong mapping. They think it was designed to be bad (who would do that?). And they are convinced that JDBC and SQL are sufficient to build applications. Actually, many DBAs I have seen are persuaded that they understand everything about coding because they have written some ugly PERL scripts to automate their job. And that anything going beyond has the only goal to break the database.

I didn’t go this way. As I like to understand before building my opinion, I have read “Hibernate In Action” and tested some object to relational mapping. I’m talking about Hibernate 3 times here. I’ve found those tests in a folder from 2008. I re-used this today for a short test and that’s the reason for this post.

Here I am showing how I’m doing those small tests with Hibernate. I’m a DBA and I cannot have an Eclipse environment taking all my screens, hiding those database top activity charts. And all my RAM is already eaten by SQL Developer and Chrome Grid Control windows, no room for Eclipse. And anyway, those mouse-focused IDEs are not friends with my carpal tunnel. I like the keyboard and tty.

So, for simple tests, I need simple things which can be reduced to a command line and 1 file that I can open with vi. The goal of this post is to show how it is easy to test some Hibernate thing in this case. Of course, any real developer will vomit when looking at this… don’t forget this is about short tests only.

Libraries

So, no Maven for me. I download the whole Hibernate .zip and build a CLASSPATH with everything I found in the required lib folder.

wget https://netix.dl.sourceforge.net/project/hibernate/hibernate-orm/5.4.3.Final/hibernate-release-5.4.3.Final.zip
unzip hibernate-release-5.4.3.Final.zip
for i in hibernate-release-5.4.3.Final/lib/required/*.jar 
do
CLASSPATH="${CLASSPATH}:$i"
done
export CLASSPATH=.:$ORACLE_HOME/jdbc/lib/ojdbc8.jar:$CLASSPATH

You can see that I’ve added the Oracle JDBC as I’ll connect to an Oracle database that I have locally (I use Oracle Cloud DBaaS here).

Compile

No Ant here. I compile the .java files I have in my folder (I don’t use packages and subfolders for simple tests). Note that, from a past admiration for makefiles, I add enough intelligence (like “test -nt”) to compile only when the code is newer than the source.

for i in *.java
do
if [ $i -nt $(basename $i .java).class ]
then
$ORACLE_HOME/jdk/bin/javac $i
fi
done

ORM Mapping

My goal was to quickly test the following mapping from @vlad_mihalcea:

The best way to map a Composite Primary Key with JPA and Hibernate - Vlad Mihalcea

So what do I have in those .java files? Testing Hibernate needs having many classes. And in Java, each class goes to its own file. But did I say that I want to open only 1 file? I use inner classes.

import java.io.*;
import java.sql.*;
import java.util.*;
import oracle.jdbc.*;
import org.hibernate.*;
import org.hibernate.cfg.*;
import javax.persistence.*;
public class Franck {
@Entity(name = "Company")
@Table(name = "company")
public class Company {
    @Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
private Long id;
    private String name;
    public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof Company)) return false;
Company company = (Company) o;
return Objects.equals(getName(), company.getName());
}
@Override
public int hashCode() {
return Objects.hash(getName());
}
}
@Embeddable
public class EmployeeId implements Serializable {
@ManyToOne
@JoinColumn(name = "company_id")
private Company company;
    @Column(name = "employee_number")
private Long employeeNumber;
    public EmployeeId() {
}
public EmployeeId(Company company, Long employeeId) {
this.company = company;
this.employeeNumber = employeeId;
}
public Company getCompany() {
return company;
}
public Long getEmployeeNumber() {
return employeeNumber;
}
@Override
public boolean equals(Object o) {
if (this == o) return true;
if (!(o instanceof EmployeeId)) return false;
EmployeeId that = (EmployeeId) o;
return Objects.equals(getCompany(), that.getCompany()) &&
Objects.equals(getEmployeeNumber(), that.getEmployeeNumber());
}
@Override
public int hashCode() {
return Objects.hash(getCompany(), getEmployeeNumber());
}
}
@Entity(name = "Employee")
@Table(name = "employee")
public class Employee {
@EmbeddedId
private EmployeeId id;
    private String name;
    public EmployeeId getId() {
return id;
}
public void setId(EmployeeId id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
}
public static void main(String[] args) throws SQLException {
SessionFactory sf=new Configuration()
.addAnnotatedClass(Employee.class)
.addAnnotatedClass(Company.class)
.setProperty("hibernate.connection.url","jdbc:oracle:thin:@//localhost/PDB1")
.setProperty("hibernate.connection.driver_class","oracle.jdbc.driver.OracleDriver")
.setProperty("hibernate.connection.username","demo")
.setProperty("hibernate.connection.password","demo")
.setProperty("hibernate.format_sql","true")
.setProperty("hibernate.show_sql","true")
.setProperty("hibernate.hbm2ddl.auto","create")
.buildSessionFactory();
}
}

The main class name (here Franck) matches the file name (Franck.java) and all my entities are inner classes here. After compilation here are my files:

-rwxr--r--. 1 oracle 4931 Jun  5 20:18 Franck.java
-rw-r--r--. 1 oracle 1304 Jun 5 20:19 Franck$Company.class
-rw-r--r--. 1 oracle 1398 Jun 5 20:19 Franck$EmployeeId.class
-rw-r--r--. 1 oracle 922 Jun 5 20:19 Franck$Employee.class
-rw-r--r--. 1 oracle 1557 Jun 5 20:19 Franck.class

Execution

$ORACLE_HOME/jdk/bin/java Franck

This generates the following:

That’s all I need to verify what my annotations generate with the Oracle 12c Dialect. Ugly on-file code, but sufficient for this goal.

Java as a Shell

Ok, now that I think that any real developer has stopped reading, I can confess that I add the following on the top of my .java file:

/*TAG-FOR-SHELL 2>/dev/null
CLASSPATH=.
# Oracle JDBC
CLASSPATH=${CLASSPATH}:$ORACLE_HOME/jdbc/lib/ojdbc8.jar
# Download Hibernate
[ -f /var/tmp/hibernate.zip ] || wget -O /var/tmp/hibernate.zip https://netix.dl.sourceforge.net/project/hibernate/hibernate-orm/5.4.3.Final/hibernate-release-5.4.3.Final.zip
# Unzip Hibernate
[ -d /var/tmp/hibernate*?/lib/required ] || unzip -d /var/tmp /var/tmp/hibernate.zip
# add libs to CLASSPATH
for l in /var/tmp/hibernate*?/lib/required/*.jar ; do CLASSPATH="${CLASSPATH}:$l" ; done ; export CLASSPATH
# compile all Java
for s in $(find -name "*.java"); do
s="$(basename $s .java)"
[ $s.java -nt $s.class ] && {
echo "Compiling $s..." >&2
$ORACLE_HOME/jdk/bin/javac $s.java || exit 1
}
done
# execute
$ORACLE_HOME/jdk/bin/java $(basename $0 .java)
exit
*/

And then I “chmod u+x” this .java file and run it as a shell. The shell part is included in Java comment so that this file can be compiled as Java source code. And it gets the libraries if not there already, builds CLASSPATH, compile what’s new in the directory, and run it as a Java program. all that with a simple:

./Franck.java

Any comments welcome on Twitter: @FranckPachot

Kscope Sunday will be awesome

Yeah yeah I know. What kind of dufus cut-pastes a giant image into their blog post. That would be ….. me Smile

But the reality is, there is just soooooo much going on this year on the Sunday before the “official” start of the Kscope conference, it was too hard to condense it into a few sentences. So I just dumped the image from the website, but you can read about it here.

It’s my first Kscope and it will be blast to be running a day of high quality but dirt cheap learning with Maria, Jeff, Steven and Blaine.

And we’ll even have some fun and games. How good is your knowledge of the Oracle Database and the Oracle community? Our Sunday quiz will let you shine! (Well, to be honest, we’ve designed the quiz so that anyone in the community has a chance to win, so come along to have some fun and maybe pick up some prizes as well!)

See you in Seattle!

 

image

So long …

… and thanks for any Douglas Adams references.

I decided on Friday that after more than 31 years using the Oracle software and more than 37 years of being self-employed in the computer industry – not to mention a further 8 years being an enthusiastic amateur – the time had finally come for a change of pace.  As from 1st June 2019 I am retired.

I have a couple of long term commitments that I’ll still be doing a little work for over the next few months, and I wouldn’t be surprised if someone occasionally tempts me into doing a few days solving interesting problems.  I’ll still be answering questions on the Oracle Developer Community forum and (if the email gets through) on the Oracle-L listserver , and I’ll still be writing blog notes about Oracle and submitting abstracts to conferences for a few years. Who knows, I may even get round to writing another book – after all, I’ve already downloaded Oracle 19 so I’ll still be experimenting, investigating, and trying to break things Some people retire to do the Times crossword or Sudoku, my retirement pastime is going to be playing with Oracle.

But now I’ve got 3 musical instruments to re-learn, a garden to re-engineer, apple trees to train, and a lot of rowing [U.S. “crew”] time to catch up on so that I can get fit enough to get back to playing squash (which might be an interesting experience now that my eyesight has been fixed). And for relaxation I’ll be sitting in the garden watching the birds, drinking fine wine and reading a good book.

Update (6th June 2019)

Thanks for all the appreciative comments to know. It’s nice to have a record, both here and on the tweet, of the fact that I have made a difference for so many people.

Ignoring Hints

One of the small changes (and, potentially big but temporary, threats) in 18.3 is the status of the “ignore hints” parameter. It ceases to be a hidden (underscore) parameter so you can now officially set parameter optimizer_ignore_hints to true in the parameter file, or at the system level, or at the session level. The threat, of course, it that some of your code may use the hidden version of the parameter (perhaps in an SQL_Patch as an opt_param() option rather than in its hint form) which no longer works after the upgrade.

But there’s more. The parameter (whether the old hidden version or the new revealed version) doesn’t make the optimizer ignore parallel() hints. But 18.3 now has a related parameter optimizer_ignore_parallel_hints to address this limitation. Here’s a quick demo – we start by creating a table and then running a query where the full tablescan is clearly the default strategy that the optimizer would take if we didn’t hint an indexed access path:

rem
rem     Script:         ignore_parallel_hints.sql
rem     Author:         Jonathan Lewis
rem     Dated:          May 2019
rem 

create table t1
as
with generator as (
        select 
                rownum id
        from dual 
        connect by 
                level <= 1e4 -- > comment to avoid WordPress format issue
)
select
        rownum                          id,
        mod(rownum,10)                  n1,
        lpad(rownum,10,'0')             v1,
        lpad('x',100,'x')               padding
from
        generator       v1,
        generator       v2
where
        rownum <= 1e4 -- > comment to avoid WordPress format issue
;

create index t1_i1 on t1(id);

set serveroutput off

prompt  =============
prompt  Baseline test
prompt  =============

select
        /*+ index(t1) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);


SQL_ID  gudnnk7j7q5bz, child number 0
-------------------------------------
select  /*+ index(t1) */  n1, sum(id) from  t1 where  id > 0 group by
n1 order by  n1

Plan hash value: 356059923

----------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |       |       |   198 (100)|          |
|   1 |  SORT GROUP BY                       |       |    10 |    70 |   198   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    | 10000 | 70000 |   196   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 | 10000 |       |    22   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ID">0)


Now we repeat the exercise with the version-specific “alter session” command below – and you should try each option with each version of Oracle if you want to do the complete test cycle – to see that the session will ignore hints and the plan will change (side note – using the underscore version  with 18.3 doesn’t raise an error, the statement is silently ignored):


alter session set "_optimizer_ignore_hints" = true;
alter session set "optimizer_ignore_hints" = true;

SQL_ID  gudnnk7j7q5bz, child number 1
-------------------------------------
select  /*+ index(t1) */  n1, sum(id) from  t1 where  id > 0 group by
n1 order by  n1

Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    27 (100)|          |
|   1 |  SORT GROUP BY     |      |    10 |    70 |    27  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |    25   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">0)

Then, of course, we have to test a query with a parallel() hint – or shared() hint, which is the internal equivalent you will notice occasionally in outlines or the “remote” statement for distributed execution plans – to show that we don’t yet ignore parallel queries – the plans following the code are from 18.3:


select
        /*+ parallel(t1 3) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);

alter session set "optimizer_ignore_parallel_hints" = true;

select
        /*+ parallel(t1 3) */
        n1, sum(id)
from
        t1
where
        id > 0
group by
        n1
order by
        n1
;

select * from table(dbms_xplan.display_cursor);




SQL_ID  7jynurdtc48kv, child number 0
-------------------------------------
select  /*+ parallel(t1 3) */  n1, sum(id) from  t1 where  id > 0 group
by  n1 order by  n1

Plan hash value: 2919148568

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |       |       |    10 (100)|          |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)     | :TQ10001 |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | P->S | QC (ORDER) |
|   3 |    SORT GROUP BY         |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND RANGE       | :TQ10000 |    10 |    70 |    10  (10)| 00:00:01 |  Q1,00 | P->P | RANGE      |
|   6 |       HASH GROUP BY      |          |    10 |    70 |    10  (10)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 10000 | 70000 |     9   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| T1       | 10000 | 70000 |     9   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   8 - access(:Z>=:Z AND :Z<=:Z) -- > comment added to avoid wordpress format issue
       filter("ID">0)

Note
-----
   - Degree of Parallelism is 3 because of table property


Session altered.


SQL_ID  7jynurdtc48kv, child number 1
-------------------------------------
select  /*+ parallel(t1 3) */  n1, sum(id) from  t1 where  id > 0 group
by  n1 order by  n1

Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |    27 (100)|          |
|   1 |  SORT GROUP BY     |      |    10 |    70 |    27  (12)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 70000 |    25   (4)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("ID">0)


tl;dr

In 18.3 Oracle exposes the parameter optimizer_ignore_hints – any code using depending on the hidden version of this parameter will no longer behave as expected. 18.3 also introduces optimiser_ignore_parallel_hints to allow you to ignore parallel hints as well.

 

When Oracle Statistic Gathering times out.

When Oracle Statistic Gathering times out — I

This first part is about running manually, killing the job, and locking the stats

In a previous post, I explained how to see where the Auto Stats job has been running and timed out:

SYS.STATS_TARGET$

I got a case where it always timed out at the end of the standard maintenance window. One table takes many hours, longer than the largest maintenance window, it will always be killed at the end. And, because it stayed stale, and staler each day, this table was always listed first by the Auto Stat job. And many tables never got their chance to get their stats gathered for … years.

In that case, the priority is to gather statistics. That can be long. Then I run the job manually:

exec dbms_auto_task_immediate.gather_optimizer_stats;

Here, it will never time-out (and the auto job will not start at maintenance window start). This manual gathering can take many days. Of course, this gives time to think about a solution, like reading Nigel Bayliss recommendations:

How to Gather Optimizer Statistics Fast!

If I want to kill the manual job, because one table takes really too long and I decide to skip it for the moment, here is my query to find it:

select 'alter system kill session '''||sid||','||serial#||',@'||inst_id||''' /* '||action||' started on '||logon_time||'*/;' "Kill me with this:" from gv$session where module='DBMS_SCHEDULER' and action like 'ORA$AT^_OS^_MANUAL^_%' escape '^';

Which gives me the kill statement, and the time when I started it:

Before killing, I’ll check the long queries from it with the goal to find a solution for it:

select executions,users_executing,round(elapsed_time/1e6/60/60,1) hours,substr(coalesce(info,sql_text),1,60) info,sql_id from gv$sql natural left outer join (select address,hash_value,sql_id,plan_hash_value,child_address,child_number,id,rtrim(operation||' '||object_owner||' '||object_name) info from gv$sql_plan where object_name is not null) where elapsed_time>1e6*10*60 and action like 'ORA$AT_OS_%' order by last_active_time,id

In this example, I can see that one table is running for 4 days:

Now I kill this statistic gathering job. What I want for the moment is to exclude this table from the automatic statistics gathering. Unfortunately, I cannot change the AUTOSTATS_TARGET at table level, then I lock the stats. And run DBMS_AUTO_TASK_IMMEDIATE.GATHER_OPTIMIZER_STATS again.

This is just to quickly resolve the gap we had on many tables. The few tables locked will need further considerations. I even got a funny case where the statistics gathering was long because… statistics where stale. It was in 11g, an IOT where the CBO decided to with ‘db file sequential reads’. I deleted the statistics and the gathering used an optimized execution plan then. When you have really bad statistics, it may be better to have no statistics (and then do dynamic sampling) rather than completely stale ones.

With and without WITH_PLSQL within a WITH SQL statement

OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title Smile. But hopefully there’s some value as well in here for you if you’re hitting the error:

ORA-32034: unsupported use of WITH clause

First some background. A cool little enhancement to the WITH clause came along in 12c that allowed PLSQL functions to be defined within the scope of the executing SQL statement. To see the benefit of this, consider the following example that I have a personal affinity with (given my surname).

Let’s say I’ve allowed mixed-case data in a table that holds names.


SQL> select surname
  2  from   names;

SURNAME
------------------------------
jones
brown
SMITH

There’s nothing inherently wrong here, but in terms of rendering that data in a report or on screen, it would be nice to have some consistency.

“No problem” you think, “I’ll just slap an INITCAP in there”


SQL> select initcap(surname)
  2  from   names;

INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith

That works fine of course until …. yours truly gets added into the mix Smile. After a couple of new rows are added, we can start to see the shortcomings of INITCAP.


SQL> select initcap(surname)
  2  from   names;

INITCAP(SURNAME)
------------------------------
Jones
Brown
Smith
Mcdonald
Johnson'S

I’d like a capital D, and letters that follow apostrophes have some nuances that might need handling. This can be solved without too much fuss – with a little bit of PLSQL I can produce a custom version of INITCAP that will handle these exceptional cases.


SQL> create or replace
  2  function MY_INITCAP(p_string varchar2) return varchar2 is
  3    l_string varchar2(1000) := p_string;
  4  begin
  5    if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
  6        null;
  7    elsif l_string like '''%' then
  8        null;
  9    else
 10      l_string := initcap(l_string);
 11      if l_string like '_''S%' then
 12         null;
 13      else
 14         l_string := replace(l_string,'''S','''s');
 15      end if;
 16    end if;
 17
 18    return l_string;
 19  end;
 20  /

SQL> select my_initcap(surname)
  2  from   names;

MY_INITCAP(SURNAME)
--------------------------
Jones
Brown
Smith
McDonald
Johnson's

But perhaps I’d like that functionality inline with the SQL so that a future maintainer can directly see what I’ve done. Yes, I could refactor the code to be 100% SQL with no reliance on PLSQL using something like this:


SQL> select
  2    case
  3      when regexp_like(surname,'(Mac[A-Z]|Mc[A-Z])') then surname
  4      when surname like '''%' then surname
  5      when initcap(surname) like '_''S%' then surname
  6      else replace(initcap(surname),'''S','''s')
  7    end ugh
  8  from names;

UGH
-------------------------------
Jones
Brown
Smith
McDonald
Johnson's

But if I’m doing this to help a future maintainer….well… that convoluted CASE statement probably isn’t such a nice remnant for them Smile. So since 12c, we’ve been able to add that PLSQL code directly within the SQL statement itself.


SQL> WITH
  2    function my_initcap(p_string varchar2) return varchar2 is
  3      l_string varchar2(1000) := p_string;
  4    begin
  5      if regexp_like(l_string,'(Mac[A-Z]|Mc[A-Z])') then
  6          null;
  7      elsif l_string like '''%' then
       ...
 17
 18      return l_string;
 19    end;
 20  select my_initcap(surname)
 21  from   names;

MY_INITCAP(SURNAME)
-----------------------------------------
Jones
Brown
Smith
McDonald

Hopefully you can now see the benefit of the feature. Now back to the topic at hand, the ORA-32034 error. If you attempt to use the feature within an INSERT, UPDATE or DELETE statement, you’ll get a surprise:


SQL> insert into TARGET_TABLE
  2  WITH
  3    function my_initcap(p_string varchar2) 
  4                return varchar2 is
  5      l_string varchar2(1000) := p_string;
  6    begin
          ...
 20    end;
 21  select my_initcap(surname)
 22  from   names;
 23  /

WITH
*
ERROR at line 2:
ORA-32034: unsupported use of WITH clause

To overcome this, you need to specify the WITH_PLSQL hint


SQL> insert /*+ WITH_PLSQL */ into TARGET_TABLE
  2  WITH
  3    function my_initcap(p_string varchar2) 
  4                return varchar2 is
  5      l_string varchar2(1000) := p_string;
  6    begin
          ...
 20    end;
 21  select my_initcap(surname)
 22  from   names;
 23  /

5 rows inserted.

I’ve never ascertained the reason precisely why the hint is needed (I’m asking around internally within the database group), but I have a hypothesis: Given that you could potentially write anything within an PLSQL function (including independent transactions), my guess is that the hint is a flag to the database to say “Have an extra careful double-check of the function code to make sure it’s not doing anything dangerous, in particular, to the table we doing the DML on”.

I might be 100% wrong here – I’ll update the post if I get more information in future.

Advice on fragmentation and shrinkage

If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if

  • that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or
  • you are performing a lot of full scan queries on that table and you want to make sure they are as efficient as possible

then there may be benefits to performing a shrink on that table to reclaim that space. One of the cool things about the segment advisor is that it will detect if there are some benefits to be gained by shrinking a segment. Here’s an example of that. I create a large table and then delete every 2nd row.


SQL> create table scott.demo_table as
  2  select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 100 ) ;

Table created.

SQL>
SQL> delete from scott.demo_table
  2  where mod(object_id,2) = 0;

4125300 rows deleted.

SQL> commit;

Commit complete.

Now I run the segment advisor and I get a nice report on what can be done to reclaim that space.



SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
  2    l_object_id     number;
  3    l_task_name     varchar2(50) := 'TEST_TASK';
  4    l_object_type   varchar2(50) := upper('TABLE');
  5    l_attr1         varchar2(50) := upper('SCOTT');
  6    l_attr2         varchar2(50) := upper('DEMO_TABLE');
  7  begin
  8    begin  DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
  9
 10    dbms_advisor.create_task (
 11      advisor_name      => 'Segment Advisor',
 12      task_name         => l_task_name);
 13
 14    dbms_advisor.create_object (
 15      task_name   => l_task_name,
 16      object_type => l_object_type,
 17      attr1       => l_attr1,
 18      attr2       => l_attr2,
 19      attr3       => NULL,
 20      attr4       => 'null',
 21      attr5       => NULL,
 22      object_id   => l_object_id);
 23
 24    dbms_advisor.set_task_parameter (
 25      task_name => l_task_name,
 26      parameter => 'RECOMMEND_ALL',
 27      value     => 'TRUE');
 28
 29    dbms_advisor.execute_task(task_name => l_task_name);
 30
 31
 32    for cur_rec in (select f.impact,
 33                           o.type,
 34                           o.attr1,
 35                           o.attr2,
 36                           f.message,
 37                           f.more_info
 38                    from   dba_advisor_findings f
 39                           join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
 40                    where  f.task_name = l_task_name
 41                    order by f.impact desc)
 42    loop
 43      dbms_output.put_line('..');
 44      dbms_output.put_line('Type             : ' || cur_rec.type);
 45      dbms_output.put_line('Attr1            : ' || cur_rec.attr1);
 46      dbms_output.put_line('Attr2            : ' || cur_rec.attr2);
 47      dbms_output.put_line('Message          : ' || cur_rec.message);
 48      dbms_output.put_line('More info        : ' || cur_rec.more_info);
 49    end loop;
 50
 51    dbms_advisor.delete_task(task_name => l_task_name);
 52  end;
 53  /
..
Type             : TABLE
Attr1            : SCOTT
Attr2            : DEMO_TABLE
Message          : Enable row movement of the table SCOTT.DEMO_TABLE and perform shrink, estimated savings is 596868412 bytes.
More info        : Allocated Space:1342177280: Used Space:745308868: Reclaimable Space :596868412:

PL/SQL procedure successfully completed.

But what if that segment sits in a manual segment space managed tablespace? We can see from the above that a shrink-style operation will yield some benefits, but there’s a problem. You cannot perform an ALTER TABLE SHRINK command unless a segment sits in an ASSM tablespace. So is the segment advisor of any use in these cases? Let’s re-run the demo to find out


SQL> select * from dba_tablespaces where tablespace_name = 'NO_ASSM'
  2  @pr
==============================
TABLESPACE_NAME               : NO_ASSM
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
MAX_SIZE                      : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : NO
SEGMENT_SPACE_MANAGEMENT      : MANUAL
DEF_TAB_COMPRESSION           : DISABLED
RETENTION                     : NOT APPLY
BIGFILE                       : NO
PREDICATE_EVALUATION          : HOST
ENCRYPTED                     : NO
COMPRESS_FOR                  :
DEF_INMEMORY                  : DISABLED
DEF_INMEMORY_PRIORITY         :
DEF_INMEMORY_DISTRIBUTE       :
DEF_INMEMORY_COMPRESSION      :
DEF_INMEMORY_DUPLICATE        :
SHARED                        : SHARED
DEF_INDEX_COMPRESSION         : DISABLED
INDEX_COMPRESS_FOR            :
DEF_CELLMEMORY                :
DEF_INMEMORY_SERVICE          :
DEF_INMEMORY_SERVICE_NAME     :
LOST_WRITE_PROTECT            : OFF
CHUNK_TABLESPACE              : N

PL/SQL procedure successfully completed.

SQL>
SQL> create table scott.demo_table tablespace no_assm as
  2  select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 100 ) ;

Table created.

SQL>
SQL> delete from scott.demo_table
  2  where mod(object_id,2) = 0;

4125300 rows deleted.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL> set serveroutput on size 1000000
SQL> set linesize 200
SQL> set verify off
SQL>
SQL> declare
  2    l_object_id     number;
  3    l_task_name     varchar2(50) := 'TEST_TASK';
  4    l_object_type   varchar2(50) := upper('TABLE');
  5    l_attr1         varchar2(50) := upper('SCOTT');
  6    l_attr2         varchar2(50) := upper('DEMO_TABLE');
  7  begin
  8    begin  DBMS_ADVISOR.delete_task(task_name => l_task_name); exception when others then null; end;
  9
 10    dbms_advisor.create_task (
 11      advisor_name      => 'Segment Advisor',
 12      task_name         => l_task_name);
 13
 14    dbms_advisor.create_object (
 15      task_name   => l_task_name,
 16      object_type => l_object_type,
 17      attr1       => l_attr1,
 18      attr2       => l_attr2,
 19      attr3       => NULL,
 20      attr4       => 'null',
 21      attr5       => NULL,
 22      object_id   => l_object_id);
 23
 24    dbms_advisor.set_task_parameter (
 25      task_name => l_task_name,
 26      parameter => 'RECOMMEND_ALL',
 27      value     => 'TRUE');
 28
 29    dbms_advisor.execute_task(task_name => l_task_name);
 30
 31
 32    for cur_rec in (select f.impact,
 33                           o.type,
 34                           o.attr1,
 35                           o.attr2,
 36                           f.message,
 37                           f.more_info
 38                    from   dba_advisor_findings f
 39                           join dba_advisor_objects o on f.object_id = o.object_id and f.task_name = o.task_name
 40                    where  f.task_name = l_task_name
 41                    order by f.impact desc)
 42    loop
 43      dbms_output.put_line('..');
 44      dbms_output.put_line('Type             : ' || cur_rec.type);
 45      dbms_output.put_line('Attr1            : ' || cur_rec.attr1);
 46      dbms_output.put_line('Attr2            : ' || cur_rec.attr2);
 47      dbms_output.put_line('Message          : ' || cur_rec.message);
 48      dbms_output.put_line('More info        : ' || cur_rec.more_info);
 49    end loop;
 50
 51    dbms_advisor.delete_task(task_name => l_task_name);
 52  end;
 53  /
..
Type             : TABLE
Attr1            : SCOTT
Attr2            : DEMO_TABLE
Message          : Perform re-org on the object DEMO_TABLE, estimated savings is 600175966 bytes.
More info        : Allocated Space:1342177280: Used Space:742001314: Reclaimable Space :600175966:

PL/SQL procedure successfully completed.

SQL>
SQL>

As you can see, the segment advisor will take that into account and adjust its recommendations accordingly. And one of the cool things with 12.2 and above, is that tables can be reorganised without an outage!


SQL> alter table scott.demo_table move online;

Table altered.

Nice!

Generic data models … generic applications … ugh

There’s a hesitation to publish this example, because publishing it may be interpreted as an endorsement of this approach and it certainly isn’t. Over the years there have been plenty of articles describing the long term pain that typically comes from generic data models. Here’s a few to whet your appetite.

https://rodgersnotes.wordpress.com/2010/09/21/muck-massively-unified-code-key-generic-three-table-data-model/

https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/

https://asktom.oracle.com/pls/asktom/asktom.search?tag=query-on-design

But I’m posting this example because it serves as a nice tutorial for DBMS_SQL, and also, there is perhaps the suggestion that the requestor is moving away from a generic data model to a more well structured one. We’ll go with the benefit of the doubt here Smile

The incoming data for this example was the “classic” generic data model where the table name, column name and column values were not defined in the data dictionary but as values within a table


SQL> CREATE TABLE data_table
  2    (
  3      row_seq      int,
  4      table_name   VARCHAR2(30),
  5      column_name  VARCHAR2(30),
  6      column_value VARCHAR2(30)
  7    );

Table created.

SQL>
SQL>
SQL>
SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_NUMBER','ORD1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','CUST_NAME','CUST1001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','INVOICE_NUMBER','INV001');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','ORDER_DATE','04/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 1,'TEST_TAB','AMOUNT','1001');

1 row created.

SQL>
SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_NUMBER','ORD1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','CUST_NAME','CUST1002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','INVOICE_NUMBER','INV002');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','ORDER_DATE','02/11/2018 15:14:00');

1 row created.

SQL> insert into data_table values ( 2,'TEST_TAB','AMOUNT','1002');

1 row created.

SQL>
SQL> commit;

Commit complete.

SQL> select * from data_table;

   ROW_SEQ TABLE_NAME                     COLUMN_NAME                    COLUMN_VALUE
---------- ------------------------------ ------------------------------ ------------------------------
         1 TEST_TAB                       ORDER_NUMBER                   ORD1001
         1 TEST_TAB                       CUST_NAME                      CUST1001
         1 TEST_TAB                       INVOICE_NUMBER                 INV001
         1 TEST_TAB                       ORDER_DATE                     04/11/2018 15:14:00
         1 TEST_TAB                       AMOUNT                         1001
         2 TEST_TAB                       ORDER_NUMBER                   ORD1002
         2 TEST_TAB                       CUST_NAME                      CUST1002
         2 TEST_TAB                       INVOICE_NUMBER                 INV002
         2 TEST_TAB                       ORDER_DATE                     02/11/2018 15:14:00
         2 TEST_TAB                       AMOUNT                         1002

10 rows selected.

The task here was to take those values and convert into INSERT statements, so with the sample data above, the aim is to insert those rows into a table called TEST_TAB. Given that the DML must generated entirely from metadata, we can use DBMS_SQL to handle it. Constructing the DML is easier than you might think due to some handy analytic SQL functions plus the ever useful LISTAGG.


SQL> select
  2    row_number() over
  3      ( partition by table_name, row_seq order by column_name ) as seq,
  4    count(*) over
  5      ( partition by table_name, row_seq ) as col_cnt,
  6    listagg(column_name,',') within group
  7      ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
  8    listagg(':'||column_name,',') within group
  9      ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
 10    column_value
 11  from data_table
 12  order by table_name, row_seq, column_name
 13  @pr
==============================
SEQ                           : 1
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 1001
==============================
SEQ                           : 2
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : CUST1001
==============================
SEQ                           : 3
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : INV001
==============================
SEQ                           : 4
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 04/11/2018 15:14:00
==============================
SEQ                           : 5
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : ORD1001
==============================
SEQ                           : 1
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 1002
==============================
SEQ                           : 2
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : CUST1002
==============================
SEQ                           : 3
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : INV002
==============================
SEQ                           : 4
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : 02/11/2018 15:14:00
==============================
SEQ                           : 5
COL_CNT                       : 5
COLS                          : AMOUNT,CUST_NAME,INVOICE_NUMBER,ORDER_DATE,ORDER_NUMBER
BINDCOLS                      : :AMOUNT,:CUST_NAME,:INVOICE_NUMBER,:ORDER_DATE,:ORDER_NUMBER
COLUMN_VALUE                  : ORD1002

Now that we have each data value, plus all the components for an INSERT statement (including binding values not using any literals), we’re good to go:


SQL> create table test_tab (
  2    order_number varchar2(10),
  3    cust_name varchar2(10),
  4    invoice_number varchar2(10),
  5    order_date varchar2(30),
  6    amount varchar2(10)
  7  );

Table created.


SQL> declare
  2    l_sql varchar2(32000);
  3    l_cur     pls_integer := dbms_sql.open_cursor;
  4    l_execute pls_integer;
  5  begin
  6    for i in (
  7      select   table_name,
  8               column_name,
  9               row_number() over ( partition by table_name, row_seq order by column_name ) as seq,
 10               count(*) over ( partition by table_name, row_seq ) as col_cnt,
 11               listagg(column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as cols,
 12               listagg(':'||column_name,',') within group ( order by column_name ) over ( partition by table_name, row_seq ) as bindcols,
 13               column_value
 14      from data_table
 15      order by table_name, row_seq, column_name
 16   ) loop
 17       if i.seq = 1 then
 18         l_sql := 'insert into '||i.table_name||'('||i.cols||') values ('||i.bindcols||')';
 19         dbms_sql.parse(l_cur,l_sql,dbms_sql.native);
 20       end if;
 21       dbms_sql.bind_variable(l_cur,i.column_name,i.column_value);
 22       if i.seq = i.col_cnt then
 23         l_execute := dbms_sql.execute(l_cur);
 24       end if;
 25   end loop;
 26   dbms_sql.close_cursor(l_cur);
 27  end;
 28  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from test_tab;

ORDER_NUMB CUST_NAME  INVOICE_NU ORDER_DATE                     AMOUNT
---------- ---------- ---------- ------------------------------ ----------
ORD1001    CUST1001   INV001     04/11/2018 15:14:00            1001
ORD1002    CUST1002   INV002     02/11/2018 15:14:00            1002

2 rows selected.

SQL>

Timestamp Oddity

[Editorial note: this is something I started writing in 2013, managed to complete in 2017, and still failed to publish. It should have been a follow-on to another posting on the oddities of timestamp manipulation.]

Just as national language support used to be, timestamps and time-related columns are still a bit of a puzzle to the Oracle world – so much so that OEM could cripple a system if it was allowed to do the check for “failed logins over the last 30 minutes”. And, just like NLS, it’s one of those things that you use so rarely that you keep forgetting what went wrong the last time you used it. Here’s one little oddity that I reminded myself about recently:

rem
rem     Script:         timestamp_anomaly.sql
rem     Author:         Jonathan Lewis
rem     Dated:          April 2013
rem
create table t1 (
        ts_tz   timestamp(9) with time zone,
        ts_ltz  timestamp(9) with local time zone
);

insert into t1 values(systimestamp, systimestamp);
commit;

alter table t1 add constraint ts_ltz_uk unique (ts_ltz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz);


Nothing terribly difficult – just a table with two variants on the timestamp data type and a unique constraint on both: except for one problem. Watch what happens as I create the unique constraints:

SQL> alter table t1 add constraint ts_ltz_uk unique (ts_ltz);

Table altered.

SQL> alter table t1 add constraint ts_tz_uk  unique (ts_tz);
alter table t1 add constraint ts_tz_uk  unique (ts_tz)
                                        *
ERROR at line 1:
ORA-02329: column of datatype TIME/TIMESTAMP WITH TIME ZONE cannot be unique or a primary key

Slightly unexpected – unless you’ve memorized the manuals, of course, which I hadn’t. I wonder if you can create a unique index on timestamp with time zone:


SQL> create unique index ts_tz_uk on t1(ts_tz);

Index created.

You can’t have a unique constraint, but you CAN create a unique index! How curious – did that really happen ?

SQL> select index_name, column_name from user_ind_columns where table_name = 'T1';

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
TS_LTZ_UK            TS_LTZ
TS_TZ_UK             SYS_NC00003$

The index is on a column called SYS_NC00003$ – which looks suspiciously like one of those “function-based-index” things:


SQL> select * from user_ind_expressions where table_name = 'T1';

INDEX_NAME           TABLE_NAME           COLUMN_EXPRESSION                        COLUMN_POSITION
-------------------- -------------------- ---------------------------------------- ---------------
TS_TZ_UK             T1                   SYS_EXTRACT_UTC("TS_TZ")                               1

Oracle has silently invoked the sys_extract_utc() function on our (free-floating) timestamp column to normalize it to UTC. This is really not very friendly but it does make sense, of course – it would be rather expensive to enforce uniqueness if there were (at least) 24 different ways of storing the same absolute value – and 24 is a conservative estimate.

 

 

PeopleSoft Adminstrator Podcast: #184 – nVision Performance

I recorded a second podcast with Dan Iverson and Kyle Benson for the PeopleSoft Administrator Podcast, this time about nVision.

(10 May 2019) #184 – nVision Performance

You can listen to the podcast on psadmin.io, or subscribe with your favourite podcast player, or in iTunes.