Transcript db_writer_processes on NT. - Go

Unicode Oddity
from a Unicode PeopleSoft
Database
SELECT
FROM
WHERE
;
emplid, name, LENGTH(name), BLENGTH(name)
ps_personal_data
emplid = '007’
EMPLID
NAME
LENGTH(NAME) BLENGTH(NAME)
------------- ---------- ------------ ------------007
Cona¿ová,d
10
12
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
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.
How does PeopleSoft create
tables?
CREATE TABLE fred
(fred10 VARCHAR2(30) CHECK (LENGTH(fred10)<=10)
);
Table created.
So...
• Length checking constraint on EVERY
character column in the database!
• >500,000 user constraints
• What effect does this have on performance
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)<=30
)
…
,field_20 varchar2(30)
CHECK(LENGTH(field_01)<=30
)
);
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;
/
Results of Experiment 1
• Insert 10000 rows
• CPU time for recursive SQL
• on my 500Mhz Laptop
– No constraints:
– With constraints
11.08s
13.23s
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;
/
Results of Experiment 2
• >99% parse time
• Duration of parse CPU
– Without Constraints:
– With Constraints:
41.05s
156.93s
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.
Unicode Oddity
David Kurtz
Go-Faster Consultancy Ltd.
[email protected]
www.go-faster.co.uk