Unicode Oddity

1
Unicode Oddity
2
from a Unicode PeopleSoft Database

• SELECT emplid, name, LENGTH(name), BLENGTH(name)
• FROM ps_personal_data
• WHERE emplid '007
• EMPLID NAME LENGTH(NAME)
  BLENGTH(NAME)
• ------------- ---------- ------------
  -------------
• 007 Conaová,d 10
  12

3
Insert 10 characters in 11 bytes

• CREATE TABLE fred (fred10 VARCHAR2(11))
• Table created.
• INSERT INTO fred
• SELECT name
• FROM ps_personal_data
• WHERE emplid '007'
• ERROR at line 1
• ORA-01401 inserted value too large for column

4
Insert 10 characters in 12 bytes

• CREATE TABLE fred (fred10 VARCHAR2(12))
• Table created.
• INSERT INTO fred
• SELECT name
• FROM ps_personal_data
• WHERE emplid '007'
• 1 row created.

5
How does PeopleSoft create tables?

• CREATE TABLE fred
• (fred10 VARCHAR2(30) CHECK (LENGTH(fred10)lt10)
• )
• Table created.

6
So...

• Length checking constraint on EVERY character
  column in the database!
• gt500,000 user constraints
• What effect does this have on performance

7
Experiment 1

• create table test_nocons
• (id number
• ,field_01 varchar2(30)
• ,field_20 varchar2(30)
• )

• create table test_cons
• (id number
• ,field_01 varchar2(30) CHECK(LENGTH(field_01)lt30)
  
• ,field_20 varchar2(30) CHECK(LENGTH(field_01)lt30)
  
• )

8
Populate tables, trace enabled

• alter session set sql_trace true
• BEGIN
• FOR i IN 1..10000 LOOP
• INSERT INTO test_nocons
• VALUES
• (i
• ,RPAD(TO_CHAR(i),11,'.')
• ,RPAD(TO_CHAR(i),30,'.')
• )
• COMMIT
• END LOOP
• END
• /

9
Results of Experiment 1

• Insert 10000 rows
• CPU time for recursive SQL
• on my 500Mhz Laptop
• No constraints 11.08s
• With constraints 13.23s

10
Experiment 2

• Now deliberately generate different SQL
  statements, forcing parse every time.
• BEGIN
• FOR i IN 1..1000 LOOP
• EXECUTE IMMEDIATE 'INSERT INTO
  test_nocons VALUES ('i',RPAD(TO_CHAR('i'),
  11,''.''))'
• END LOOP
• COMMIT
• END
• /

11
Results of Experiment 2

• gt99 parse time
• Duration of parse CPU
• Without Constraints 41.05s
• With Constraints 156.93s

12
Conclusion

• Execution of constraints adds overhead.
• On my PC 15-20 increase in CPU consumption.
• If you have much SQL parsing this will aggravate
  the problem. In my case 4 times worse.

13
Unicode Oddity

• David Kurtz
• Go-Faster Consultancy Ltd.
• david.kurtz_at_go-faster.co.uk
• www.go-faster.co.uk