SQL Causes server to swap and become unresponsive does it have the same effect with other users??
*****************
WARNING - script causes my servers to not respond, requiring reboot after 30 mins
Only run on a server/vm where a bounce is ok
*****************
The script creates a table
Runs an anonymous block to put 160mb in a single clob
Runs a simple select with some REGEXP_REPLACE and REGEXP_SUBSTR
This has been raised with Oracle support.
Their conclusion is lots of single block reads are causing the server to hang.
I do not think this is the reason and would like someone else to run it, to see what they think!
Environment
on red hat 5.7 and Oracle 11.2.0.3 enterprise edition it seems to hang the server
[oracle@ ~]$ uname -a
Linux 2.6.18-274.el5xen #1 SMP Fri Jul 8 17:45:44 EDT 2011 x86_64 x86_64 x86_64 GNU/Linux
[oracle@edu-db3 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 5.7 (Tikanga)
on redhat 4.5 Oracle 10.2.0.4 - it seems to just kill the instance
[oracle@ ~]$ uname -a
Linux 2.6.9-55.ELxenU #1 SMP Fri Apr 20 16:56:53 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
[oracle@edu-db1 ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux AS release 4 (Nahant Update 5)
When running the sql oracle consumes all the memory on the server, although SGA and PGA configured well bellow os memory
SQL> show parameter sga
NAME TYPE VALUE
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 5G
sga_target big integer 5G
SQL> show parameter pga
NAME TYPE VALUE
pga_aggregate_target big integer 2G
[oracle@ scripts]$ free
total used free shared buffers cached
Mem: 8388608 7346232 1042376 0 70060 6498460
-/+ buffers/cache: 777712 7610896
Swap: 4161528 1212380 2949148
Script Output
SQL> @kill_server.sql
Table created.
To start with we see oracle using the 5g sga
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13007 oracle 25 0 5352m 216m 212m R 95.9 2.6 0:19.33 oracle
163mb clob created
MB
163
SQL Running
Oracle process using 13gb of memory!!!!!!!!!!!!!
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13007 oracle 18 0 13.4g 6.5g 210m D 7.3 81.8 0:55.42 oracle
But PGA has not grown
SQL> select * from v$pgastat;
NAME VALUE UNIT
aggregate PGA target parameter 2147483648 bytes
aggregate PGA auto target 1767444480 bytes
global memory bound 214743040 bytes
total PGA inuse 183654400 bytes
total PGA allocated 248871936 bytes
maximum PGA allocated 522175488 bytes
-----------------------------------------------
-- SCRIPT
-- WARNING (again) - script causes my servers to not respond, requiring reboot after 30 mins
-- Only run on a server/vm that does not matter!
-----------------------------------------------
DROP TABLE testing.clob_store PURGE;
--
-- Clob holding table
--
CREATE TABLE
testing.clob_store
(
clob_data CLOB
)
LOB ("CLOB_DATA") STORE AS BASICFILE (TABLESPACE "TBSCLOB" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION CACHE)
/
--
-- Make a 100mb test clob
--
DECLARE
g_clob CLOB := EMPTY_CLOB;
PROCEDURE clob_append(p_line VARCHAR2) IS
l_line VARCHAR2(2000) := p_line || CHR(10);
BEGIN
DBMS_LOB.WRITEAPPEND( g_clob, LENGTH(l_line), l_line );
END;
BEGIN
DBMS_LOB.CREATETEMPORARY(g_clob,TRUE);
DBMS_LOB.OPEN( g_clob, DBMS_LOB.LOB_READWRITE );
clob_append( '<Workbook>' );
clob_append( '<Styles>' );
FOR i IN 1..1000000 LOOP
clob_append(' <Styles><Style ss:ID="Default" ss:Name="Normal"><Alignment ss:Vertical="Bottom"/><Borders/><Font/><Interior/><NumberFormat/><Protection/></Style>');
END LOOP;
clob_append( '</Styles>' );
clob_append( '<Workbook>' );
FOR i IN 1..1000000 LOOP
clob_append('<dummy>sfsdfsdf</dummy>');
END LOOP;
clob_append( '</Worksheet>' );
clob_append( '</Workbook>' );
INSERT INTO testing.clob_store(clob_data) VALUES (g_clob);
DBMS_LOB.CLOSE(g_clob);
DBMS_LOB.FREETEMPORARY(g_clob);
COMMIT;
END;
/
--
-- Check sizes
--
select
round(DBMS_LOB.GETLENGTH (clob_data)/1024/1024) mb
from
testing.clob_store
/
--
-- Test lob set up
-- no trace and run simple select
--
column rd new_value run_date noprint
select to_char(sysdate, 'YYYY_MM_DD__HH_MI') rd from dual
/
alter session set tracefile_identifier = 'bad_sql_&run_date'
/
alter session set events '10046 trace name context forever, level 12'
/
--
-- Some regexp extracts
--
SELECT
REGEXP_REPLACE( REGEXP_REPLACE( REGEXP_SUBSTR( RPO.CLOB_DATA , ' <Styles>.*</Styles>.', 1, 1, 'n' ) , ' </?Styles>.', NULL, 1, 0, 'n' ) , ' <Style ss:ID="Default".*? </Style>.', NULL, 1, 0, 'n' ) STYLES
, REGEXP_SUBSTR(RPO.CLOB_DATA, ' <Workbook.*</Workbook>.', 1, 1, 'n') worksheet
FROM
testing.clob_store rpo
/
Extra info
SQL Trace
*** 2012-11-12 10:22:03.936
WAIT #47954520916768: nam='db file sequential read' ela= 28421 file#=12 block#=1324297 blocks=1 obj#=567174 tim=1352715723936618
WAIT #47954520916768: nam='db file sequential read' ela= 34490 file#=12 block#=1324345 blocks=1 obj#=567174 tim=1352715724000358
WAIT #47954520916768: nam='db file sequential read' ela= 59165 file#=12 block#=1324298 blocks=1 obj#=567174 tim=1352715724314041
WAIT #47954520916768: nam='db file sequential read' ela= 68224 file#=12 block#=1324346 blocks=1 obj#=567174 tim=1352715724395729
*** 2012-11-12 10:22:04.631
WAIT #47954520916768: nam='db file sequential read' ela= 34901 file#=12 block#=1324299 blocks=1 obj#=567174 tim=1352715724631454
WAIT #47954520916768: nam='db file sequential read' ela= 32349 file#=12 block#=1324347 blocks=1 obj#=567174 tim=1352715724701167
WAIT #47954520916768: nam='db file sequential read' ela= 109175 file#=12 block#=1324300 blocks=1 obj#=567174 tim=1352715725093610
WAIT #47954520916768: nam='db file sequential read' ela= 69001 file#=12 block#=1324348 blocks=1 obj#=567174 tim=1352715725194967
*** 2012-11-12 10:22:05.913
WAIT #47954520916768: nam='db file sequential read' ela= 23154 file#=12 block#=1324301 blocks=1 obj#=567174 tim=1352715725913197
WAIT #47954520916768: nam='db file sequential read' ela= 31894 file#=12 block#=1324349 blocks=1 obj#=567174 tim=1352715726090547
WAIT #47954520916768: nam='db file sequential read' ela= 29719 file#=12 block#=1324302 blocks=1 obj#=567174 tim=1352715726457981
WAIT #47954520916768: nam='db file sequential read' ela= 29533 file#=12 block#=1324350 blocks=1 obj#=567174 tim=1352715726593580
*** 2012-11-12 10:22:07.096
WAIT #47954520916768: nam='db file sequential read' ela= 121833 file#=12 block#=1324035 blocks=1 obj#=567174 tim=1352715727096799
WAIT #47954520916768: nam='db file sequential read' ela= 56658 file#=12 block#=1324131 blocks=1 obj#=567174 tim=1352715727322665
STRACE
-rw-r--r-- 1 root root 130647 Nov 12 10:29 strace_oracle.log
7 minutes after last oracle trace entry!!
brk(0x60013000) = 0x5ffef000
mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b9e772b5000
brk(0x60013000) = 0x5ffef000
mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b9e773b5000
brk(0x60013000) = 0x5ffef000
mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b9e774b5000
brk(0x60013000) = 0x5ffef000
mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b9e775b5000
brk(0x60013000) = 0x5ffef000
mmap(NULL, 1048576, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b9e776b5000
Edited by: Tom 55 on Nov 13, 2012 6:39 AM