Hi,
I've got query which is not scaling well .
It's purpose is to get results from v$sqlarea and show in excel .
The hashvalue in sqlcache is kind of indicator if hash value was already seen in other environment (gives + sign or if newone gives '-') .
DB 9.2.0.8
the v$sqlarea is from 9.2.0.8 view
SQLCACHE:
CREATE TABLE SQLCACHE
(
DATA DATE,
SQL_TEXT VARCHAR2(1000 BYTE),
FULL_SQL CLOB,
USERNAME VARCHAR2(30 BYTE) NOT NULL,
DISK_READS NUMBER,
BUFFER_GETS NUMBER,
ROWS_PROCESSED NUMBER,
COMMAND_TYPE NUMBER,
OPTIMIZER_MODE VARCHAR2(25 BYTE),
FIRST_LOAD_TIME VARCHAR2(19 BYTE),
SHARABLE_MEM NUMBER,
PERSISTENT_MEM NUMBER,
RUNTIME_MEM NUMBER,
PARSE_CALLS NUMBER,
EXECUTIONS NUMBER,
SORTS NUMBER,
VERSION_COUNT NUMBER,
LOADED_VERSIONS NUMBER,
OPEN_VERSIONS NUMBER,
USERS_OPENING NUMBER,
USERS_EXECUTING NUMBER,
LOADS NUMBER,
PARSING_USER_ID NUMBER,
PARSING_SCHEMA_ID NUMBER,
KEPT_VERSIONS NUMBER,
ADDRESS RAW(8),
HASH_VALUE NUMBER,
MODULE VARCHAR2(64 BYTE),
MODULE_HASH NUMBER,
ACTION VARCHAR2(64 BYTE),
ACTION_HASH NUMBER,
SERIALIZABLE_ABORTS NUMBER,
ENVID VARCHAR2(64 BYTE)
)
TABLESPACE USERS
LOB (FULL_SQL) STORE AS
( TABLESPACE USERS
ENABLE STORAGE IN ROW
CHUNK 8192
PCTVERSION 10
NOCACHE
STORAGE (
INITIAL 64K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
)
NOCACHE
NOPARALLEL
NOMONITORING;
CREATE INDEX INSTALL.IHASH_VALUE ON SQLCACHE
(HASH_VALUE);
CREATE INDEX DBAWOR.IENVID ON SQLCACHE
(ENVID)
TABLESPACE USERS;
QUERY is
WITH sa AS
(SELECT /*+ rule*/
v.sql_text, users.username,
v.disk_reads, buffer_gets,
v.rows_processed, command_type,
v.optimizer_mode, first_load_time,
v.sharable_mem, persistent_mem,
v.runtime_mem, parse_calls,
v.executions, sorts,
v.version_count, loaded_versions,
v.open_versions, users_opening,
v.users_executing, loads,
v.parsing_user_id, parsing_schema_id,
v.kept_versions, address,
v.hash_value, module,
v.module_hash, action,
v.action_hash, serializable_aborts,
(select lacz (v.hash_value) from dual),
(select htm_sql_plan(v.hash_value, v.address) from dual)
FROM dba_users users, V$SQLAREA v
WHERE v.parsing_user_id = users.user_id
AND v.buffer_gets > 1000
AND v.command_type <> 47),
sc AS
(SELECT t2.hash_value
FROM sqlcache t2
WHERE t2.envid NOT LIKE 'EN%')
SELECT '+', sa.*
FROM sa
WHERE sa.hash_value IN (SELECT hash_value
FROM sc)
UNION ALL
SELECT '-', sa.*
FROM sa
WHERE sa.hash_value NOT IN (SELECT hash_value
FROM sc)
/
Plan:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 3 RECURSIVE EXECUTION OF 'SYS_LE_3_0'
2 3 RECURSIVE EXECUTION OF 'SYS_LE_3_1'
3 0 TEMP TABLE TRANSFORMATION
4 3 UNION-ALL
5 4 MERGE JOIN
6 5 SORT (JOIN)
7 6 VIEW
8 7 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6614_7B690BEE'
9 5 SORT (JOIN)
10 9 VIEW OF 'VW_NSO_1'
11 10 SORT (UNIQUE)
12 11 VIEW
13 12 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6615_7B690BEE'
14 4 FILTER
15 14 VIEW
16 15 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6614_7B690BEE'
17 14 VIEW
18 17 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6615_7B690BE
E'
Statistics
----------------------------------------------------------
882 recursive calls
13754 db block gets
17949 consistent gets
174 physical reads
9640 redo size
19372 bytes sent via SQL*Net to client
3331 bytes received via SQL*Net from client
44 SQL*Net roundtrips to/from client
36 sorts (memory)
0 sorts (disk)
14 rows processed
LACZ function is:
CREATE OR REPLACE FUNCTION .LACZ(P_HASH_VALUE NUMBER) RETURN CLOB IS
RESULT CLOB;
CURSOR C1 IS
SELECT HASH_VALUE, SQL_TEXT
FROM SYS.V_$SQLTEXT_WITH_NEWLINES
WHERE HASH_VALUE = P_HASH_VALUE
ORDER BY PIECE;
BEGIN
RESULT := '';
FOR I IN C1 LOOP
RESULT := CONCAT(RESULT, I.SQL_TEXT);
END LOOP;
RETURN RESULT;
END LACZ;
/
CREATE OR REPLACE FUNCTION HTM_SQL_PLAN(P_HASH_VALUE NUMBER,
p_address varchar2)
RETURN CLOB IS
RESULT CLOB;
CURSOR C1 IS
SELECT /*+ rule*/
id,
parent_id,
LPAD(' ', LEVEL - 1) || operation || ' ' || options operation,
object_name,
cost,
cpu_cost,
io_cost,
CARDINALITY,
OPTIMIZER,
ACCESS_PREDICATES,
FILTER_PREDICATES,
bytes
FROM (SELECT id,
parent_id,
operation,
options,
object_name,
cost,
cpu_cost,
io_cost,
t.CARDINALITY,
t.OPTIMIZER,
t.ACCESS_PREDICATES,
t.FILTER_PREDICATES,
t.BYTES
FROM sys.v_$sql_plan t
WHERE address = p_address
AND hash_value = P_HASH_VALUE
AND child_number = 0)
START WITH id = 0
CONNECT BY PRIOR id = parent_id;
v_naglowek varchar2(4000);
BEGIN
v_naglowek := '<html><body><font face="Courier" size="1"> <table border="1" width="100%" cellspacing="0" bordercolor="#008080">
<TR>
<TH width="5%"> </TH>
<TH width="5%"> </TH>
<TH width="35%"><B> OPERATION </b> </TH>
<TH width="15%"><B> OBJECT_NAME</b> </TH>
<TH width="5%"><B> COST </b> </TH>
<TH width="5%"> <B> CPU_COST </b> </TH>
<TH width="5%"> <B> IO_COST </b> </TH>
<TH width="5%"> <B> BYTES </b> </TH>
<TH width="5%"> <B> Card </b> </TH>
<TH width="10%"> <B> Opt </b> </TH>
</TR> ';
RESULT := '';
FOR I IN C1 LOOP
RESULT := CONCAT(RESULT,
'<TR> ' || '<TD width="5%"> ' || I.ID ||
' </TD>' || '<TD width="5%"> ' || I.parent_id ||
' </TD>' || '<TD width="35%"> ' || I.OPERATION ||
' </TD>' || '<TD width="15%"> ' || I.OBJECT_NAME ||
' </TD>' || '<TD width="5%"> ' || I.cost ||
' </TD>' || '<TD width="5%"> ' || I.CPU_COST ||
' </TD>' || '<TD width="5%"> ' || I.IO_COST ||
' </TD>' || '<TD width="5%"> ' || I.BYTES ||
' </TD>' || '<TD width="5%"> ' || I.CARDINALITY ||
' </TD>' || '<TD width="10%"> ' || I.OPTIMIZER ||
' </TD>' || '</TR>');
END LOOP;
if length(RESULT) > 100 then
RESULT := CONCAT(RESULT, '</table></font></body>');
RESULT := CONCAT(v_naglowek, RESULT);
RETURN RESULT;
else
RETURN null;
end if;
END HTM_SQL_PLAN;
/
I'm suspecting that scalabiity problem may come from:
v_$sql_plan (function HTM_SQL_PLAN) a lot of latching involved when selecting with that
table (guessing :)) .
Any idea how to improve that .
Regards.
Greg