Home » RDBMS Server » Performance Tuning » Performance issue while pl/sql script execution (merged 3) (oracle 10.2.0.4 , Solaris sparc )
| Performance issue while pl/sql script execution (merged 3) [message #433421] |
Wed, 02 December 2009 04:57  |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi All,
I am facing one issue while executing the pl/sql procedure. The procedure was tested so many times and was running fine. But with small change in one of the query, the procedure is producing some performance issue.
During the execution, the number of runs/transactions dipped down to 400 per minute(checking by sequence.nextval value). Which estimates around 60 hours to complete that script , from original time of completion 55 minutes. But when i do the flush the shared pool during the execution, the performance of the system suddenly boost up multiple times. The number of runs/transactions increased to 44000 per minute.
I tried another way by flushing the shared pool before starting the script, but all in vain and i have to flush the shared pool in the middle. And after the shared pool flushed , performance is boost up.
The query which is showing the high resource consumption is:
select /*+ CARDINALITY(outer 1)*/ outer.participantgroup INTO v_participantgroup2
from sourceprocresult outer
where sprid =
(select max(sprid)
from sourceprocresult inner
where spuid = v_spuid
and sprid < v_sprid);
Can you please advice me what could be reason of this issue?
Thanks in advance for your valuable time and guidance.
|
|
|
|
| Performance issue while pl/sql script execution [message #433424 is a reply to message #433421] |
Wed, 02 December 2009 05:11  |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi All,
I am facing the performance issue while executing one of the pl/sql procedure. The procedure was test many times early on. But now creating performance issue after a small change in one of the query.
The number of transactions/runs dipped down to 400 per minute during the execution(counted by sequencename.nextval). And showing the estimated time of completion to 60 hours from original time of 55 minutes.
But when i did the flush shared pool, the performance boost up drastically to 44000 runs/transactions per minute.
I tried another way around by flushing the shared pool and then running the procedure. But no performance benefit and i have to flush the shared pool during execution to get the performance boost up.
The query which is showing the high cpu time consumption before flushing of shared pool:
select /*+ CARDINALITY(outer 1)*/ outer.participantgroup INTO v_participantgroup2
from sourceprocresult outer
where sprid =
(select max(sprid)
from sourceprocresult inner
where spuid = v_spuid
and sprid < v_sprid);
Its explain plan seems to be good enough:
Plan hash value: 2009955250
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID | SOURCEPROCRESULT | 1 | 11 | 4 (0)| 00:00:01 |
| 2 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 14 | | |
| 4 | TABLE ACCESS BY INDEX ROWID| SOURCEPROCRESULT | 1 | 14 | 6 (0)| 00:00:01 |
| 5 | INDEX RANGE SCAN | SPR_SPUID | 3 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Can you please guide me what could be root cause of this issue?
Thanks in advance for your valuable time and comments.
Regards
Lalit
|
|
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433457 is a reply to message #433421] |
Wed, 02 December 2009 07:27 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
The explain plan looks suspect to me.
It implies there is virtually no data in the table in which case that sql should always be lightning fast?
Is this table populated by your batch job?
Does it start empty and then get full?
If not, how is it populated and how many rows does it have?
Also you didn't answer my question about the hint, what does the explain plan look like if you remove the hint?
And next time you post code or an explain plan can you please use code tags to preserve the formatting, it makes it easier to read - see the orafaq forum guide if you're not sure how.
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433546 is a reply to message #433421] |
Wed, 02 December 2009 22:22 |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi Cookiemonster,
Thanks for your comments.
Actually that table is filled up by the script first and then the cursor opened which uses this query.
My feeling is the same that the explain plan generated for this query is not correct. That is why when i do the flush shared pool, new plan makes the things fast.
Please have a look on the number of records and explain plan with and without hint in the attached file.
(sorry i could not find code tag, so i have attached file)
SQL> select count(1) from sourceprocresult;
5733030
|
|
|
|
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433556 is a reply to message #433421] |
Wed, 02 December 2009 23:38 |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi BlackSwan,
Thanks for guiding me up for the formatting the post.
Here is the output :
SQL> SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
SQL> select /*+ CARDINALITY(outer 1)*/ outer.participantgroup I
2 from sourceprocresult outer
3 where sprid =
4 (select max(sprid)
5 from sourceprocresult inner
6 where spuid = 100000000
7 and sprid < 1521);
Elapsed: 00:00:02.23
Execution Plan
----------------------------------------------------------
Plan hash value: 2489329025
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | SOURCEPROCRESULT | 1 | 10 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 14 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| SOURCEPROCRESULT | 1 | 14 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SPRID"= (SELECT MAX("SPRID") FROM "SOURCEPROCRESULT" "INNER" WHERE
"SPRID"<1521 AND "SPUID"=100000000))
4 - filter("SPUID"=100000000)
5 - access("SPRID"<1521)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
200 bytes sent via SQL*Net to client
240 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
SQL> EXPLAIN PLAN FOR select /*+ CARDINALITY(outer 1)*/ outer.participantgroup
2 from sourceprocresult outer
3 where sprid =
4 (select max(sprid)
5 from sourceprocresult inner
6 where spuid = 100000000
7 and sprid < 1521);
Explained.
Elapsed: 00:00:04.12
SQL>
SQL> SELECT * FROM table( dbms_xplan.display);
20 rows selected.
Elapsed: 00:00:02.34
Execution Plan
----------------------------------------------------------
Plan hash value: 3013799171
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | | | | |
---------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
29 recursive calls
12 db block gets
176 consistent gets
0 physical reads
0 redo size
1708 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
20 rows processed
SQL>
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
Session altered.
Elapsed: 00:00:18.01
SQL>
SQL> select /*+ CARDINALITY(outer 1)*/ outer.participantgroup
2 from sourceprocresult outer
3 where sprid =
4 (select max(sprid)
5 from sourceprocresult inner
6 where spuid = 100000000
7 and sprid < 1521);
Elapsed: 00:00:02.08
Execution Plan
----------------------------------------------------------
Plan hash value: 2489329025
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 8 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | SOURCEPROCRESULT | 1 | 10 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 |
| 3 | SORT AGGREGATE | | 1 | 14 | | |
|* 4 | TABLE ACCESS BY INDEX ROWID| SOURCEPROCRESULT | 1 | 14 | 4 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | SPR_SPRID | 1 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SPRID"= (SELECT MAX("SPRID") FROM "SOURCEPROCRESULT" "INNER" WHERE
"SPRID"<1521 AND "SPUID"=100000000))
4 - filter("SPUID"=100000000)
5 - access("SPRID"<1521)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
69 consistent gets
0 physical reads
0 redo size
232 bytes sent via SQL*Net to client
240 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> ALTER SESSION SET SQL_TRACE=FALSE;
Session altered.
Elapsed: 00:00:00.76
SQL>
And output of the trace_result.txt file:
TKPROF: Release 10.2.0.4.0 - Production on Thu Dec 3 05:29:37 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
obiiload@oxdora61:
obiiload@oxdora61: cat trace_result.txt
TKPROF: Release 10.2.0.4.0 - Production on Thu Dec 3 05:29:37 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Trace file: dobnkiib_ora_19419.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 3 0 2
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 2
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST)
********************************************************************************
select /*+ CARDINALITY(outer 1)*/ outer.participantgroup
from sourceprocresult outer
where sprid =
(select max(sprid)
from sourceprocresult inner
where spuid = 100000000
and sprid < 1521)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 64 0 0
Fetch 2 0.00 0.00 0 5 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 69 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=69 pr=0 pw=0 time=1322 us)
1 INDEX RANGE SCAN SPR_SPRID (cr=68 pr=0 pw=0 time=1325 us)(object id 1082550)
1 SORT AGGREGATE (cr=64 pr=0 pw=0 time=1301 us)
1 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=64 pr=0 pw=0 time=1282 us)
996 INDEX RANGE SCAN SPR_SPRID (cr=5 pr=0 pw=0 time=32 us)(object id 1082550)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'SOURCEPROCRESULT' (TABLE)
1 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SPR_SPRID' (INDEX)
1 SORT (AGGREGATE)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'SOURCEPROCRESULT' (TABLE)
996 INDEX MODE: ANALYZED (RANGE SCAN) OF 'SPR_SPRID'
(INDEX)
********************************************************************************
DELETE FROM PLAN_TABLE
WHERE
STATEMENT_ID=:1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 2 0.01 0.00 0 6 6 6
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.00 0 6 6 6
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST)
Rows Row Source Operation
------- ---------------------------------------------------
0 DELETE PLAN_TABLE$ (cr=3 pr=0 pw=0 time=84 us)
0 TABLE ACCESS FULL PLAN_TABLE$ (cr=3 pr=0 pw=0 time=78 us)
Rows Execution Plan
------- ---------------------------------------------------
0 DELETE STATEMENT MODE: ALL_ROWS
0 DELETE OF 'PLAN_TABLE$'
0 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP))
********************************************************************************
EXPLAIN PLAN SET STATEMENT_ID='PLUS786352' FOR select /*+ CARDINALITY(outer 1)*/ outer.participantgroup
from sourceprocresult outer
where sprid =
(select max(sprid)
from sourceprocresult inner
where spuid = 100000000
and sprid < 1521)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN SPR_SPRID (cr=0 pr=0 pw=0 time=0 us)(object id 1082550)
0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS BY INDEX ROWID SOURCEPROCRESULT (cr=0 pr=0 pw=0 time=0 us)
0 INDEX RANGE SCAN SPR_SPRID (cr=0 pr=0 pw=0 time=0 us)(object id 1082550)
********************************************************************************
insert into plan_table (statement_id, timestamp, operation, options,
object_node, object_owner, object_name, object_instance, object_type,
search_columns, id, parent_id, position, other,optimizer, cost, cardinality,
bytes, other_tag, partition_start, partition_stop, partition_id,
distribution, cpu_cost, io_cost, temp_space, access_predicates,
filter_predicates, projection, time, qblock_name, object_alias, plan_id,
depth, remarks, other_xml )
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34,:35,:36)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 6 0.00 0.00 0 1 6 6
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.00 0 1 6 6
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST) (recursive depth: 1)
Rows Execution Plan
------- ---------------------------------------------------
0 INSERT STATEMENT MODE: ALL_ROWS
********************************************************************************
SELECT ORA_PLAN_ID_SEQ$.NEXTVAL
FROM
DUAL
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 0 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SEQUENCE ORA_PLAN_ID_SEQ$ (cr=0 pr=0 pw=0 time=30 us)
1 FAST DUAL (cr=0 pr=0 pw=0 time=3 us)
********************************************************************************
SELECT PLAN_TABLE_OUTPUT
FROM
TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', :1))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 0 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 0 0 20
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST)
Rows Row Source Operation
------- ---------------------------------------------------
20 COLLECTION ITERATOR PICKLER FETCH DISPLAY (cr=47 pr=0 pw=0 time=13240 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
20 COLLECTION ITERATOR (PICKLER FETCH) OF 'DISPLAY' (PROCEDURE)
********************************************************************************
select /*+ opt_param('parallel_execution_enabled',
'false') EXEC_FROM_DBMS_XPLAN */ * from PLAN_TABLE where 1=0
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST) (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 FILTER (cr=0 pr=0 pw=0 time=2 us)
0 TABLE ACCESS FULL PLAN_TABLE$ (cr=0 pr=0 pw=0 time=0 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FILTER
0 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP))
********************************************************************************
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
/* EXEC_FROM_DBMS_XPLAN */ id, position, depth, operation, options, object_name, cardinality, bytes, temp_space, cost, io_cost, cpu_cost , time, partition_start, partition_stop, object_node, other_tag, distribution, projection, access_predicates, filter_predicates , other, qblock_name, object_alias, nvl(other_xml, remarks), null, null, null, null, null, null, null,
null, null, null, null, null,
null, null, null, null from PLAN_TABLE where plan_id = (select max(plan_id)
from PLAN_TABLE where id=0 and statement_id = :stmt_id)
order by id
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7 0.00 0.00 0 6 7 6
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 0.00 0.00 0 6 7 6
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST) (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
6 SORT ORDER BY (cr=6 pr=0 pw=0 time=478 us)
6 TABLE ACCESS FULL PLAN_TABLE$ (cr=6 pr=0 pw=0 time=173 us)
1 SORT AGGREGATE (cr=3 pr=0 pw=0 time=66 us)
1 TABLE ACCESS FULL PLAN_TABLE$ (cr=3 pr=0 pw=0 time=49 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
6 SORT (ORDER BY)
6 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP))
1 SORT (AGGREGATE)
1 TABLE ACCESS (FULL) OF 'PLAN_TABLE$' (TABLE (TEMP))
********************************************************************************
SELECT PLAN_TABLE_OUTPUT
FROM
TABLE(CAST(DBMS_XPLAN.PREPARE_RECORDS(:B1 , :B2 ) AS
SYS.DBMS_XPLAN_TYPE_TABLE))
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.00 0.00 0 5 5 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 5 5 20
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST) (recursive depth: 1)
error during execute of EXPLAIN PLAN statement
ORA-06553: PLS-306: wrong number or types of arguments in call to
'PREPARE_RECORDS'
parse error offset: 114
********************************************************************************
SELECT /*+ opt_param('parallel_execution_enabled', 'false') */
extractvalue(xmlval, '/*/info[@type = "sql_profile"]'), extractvalue(xmlval,
'/*/info[@type = "outline"]'), extractvalue(xmlval, '/*/info[@type =
"dynamic_sampling"]'), extractvalue(xmlval, '/*/info[@type =
"row_shipping"]'), extractvalue(xmlval, '/*/info[@type = "index_size"]'),
extractvalue(xmlval,'/*/info[@type = "plan_hash"]')
from
(select xmltype(:v_other_xml) xmlval from dual)
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.01 0.00 0 36 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.00 0 36 0 1
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 1865 (OB2_TEST) (recursive depth: 2)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 FAST DUAL
********************************************************************************
ALTER SESSION SET SQL_TRACE=FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Parsing user id: 1865 (OB2_TEST)
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 8 0.00 0.00 0 0 0 0
Execute 8 0.01 0.00 0 73 6 8
Fetch 5 0.00 0.00 0 5 0 21
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 21 0.01 0.01 0 78 6 29
Misses in library cache during parse: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 5 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 1 6 6
Fetch 10 0.01 0.01 0 47 12 28
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 26 0.01 0.01 0 48 18 34
Misses in library cache during parse: 0
13 user SQL statements in session.
1 internal SQL statements in session.
14 SQL statements in session.
7 statements EXPLAINed in this session.
********************************************************************************
Trace file: dobnkiib_ora_19419.trc
Trace file compatibility: 10.01.00
Sort options: default
3 sessions in tracefile.
16 user SQL statements in trace file.
1 internal SQL statements in trace file.
14 SQL statements in trace file.
12 unique SQL statements in trace file.
7 SQL statements EXPLAINed using schema:
OB2_TEST.prof$plan_table
Default table was used.
Table was created.
Table was dropped.
166 lines in trace file.
21 elapsed seconds in trace file.
The attached file is also having the output of explain plan and tkprof.
Regards
Lalit
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433558 is a reply to message #433421] |
Thu, 03 December 2009 00:13 |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi All,
One more thing i want to share with you about my database.
The SGA_TARGET and SGA_MAX_SIZE is set to 4G. But other parameters like shared_pool_size and large_pool is set to 0.
(We are using 10g so using autmatic allocation of memory).
Not sure weather that is making some issue during the query run. And flushing the shared pool is doing the trick always.
Below is the SGA details:
SQL> show sga
Total System Global Area 4294967296 bytes
Fixed Size 2046088 bytes
Variable Size 1375733624 bytes
Database Buffers 2902458368 bytes
Redo Buffers 14729216 bytes
SQL> show parameter shared_po
shared_pool_reserved_size big integer 56M
shared_pool_size big integer 0
large_pool_size big integer 0
SQL> show parameter sga_max
sga_max_size big integer 4G
SQL> show parameter sga_target
sga_target big integer 4G
The SGA details/usage while running the script:
select COMPONENT, CURRENT_SIZE from V$SGA_DYNAMIC_COMPONENTS
shared pool 1308622848
large pool 16777216
java pool 16777216
streams pool 16777216
DEFAULT buffer cache 2919235584
KEEP buffer cache 0
This usage remains the same even after the flush shared pool.
Regards
Lalit
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433639 is a reply to message #433421] |
Thu, 03 December 2009 07:35 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Unfortunately the explains and traces Blackswan suggested you run aren't much use here because:
a) if the table starts out empty we really aren't going to be able to tell much until there's some data in it.
b) he forgot to add a set autotrace off after the first select so the 2nd explain plan is for the wrong query.
I think a trace of the batch job would probably be more useful here.
There isn't anything obviously wrong with your sga settings.
Might be the best way to fix this is to rewrite the batch job to avoid having to do this query at all but without knowing what the code looks like I have no idea how feasible that is.
|
|
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433754 is a reply to message #433421] |
Fri, 04 December 2009 01:25 |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi Cookiemonster,
The script actually filling up the table using "insert into ... Select *..". The data is selected from the external table and inserted in the normal table that is having the indexes using Execute Immediate.
EXECUTE IMMEDIATE 'insert into sourceprocresult SELECT * FROM obi_sourceprocresult';
EXECUTE IMMEDIATE 'CREATE INDEX spr_sprid ON sourceprocresult(sprid)';
EXECUTE IMMEDIATE 'CREATE INDEX spr_spuid ON sourceprocresult(spuid)';
EXECUTE IMMEDIATE 'CREATE BITMAP INDEX spa_spuid ON sourceprocresult(spaid)';
The cursor, which is using this query is opened after these statements. Even cursor is using this normal table that is filled using Execute immediate.
CURSOR cur_sourceprocresult
IS
SELECT sprid,creationdate,mutationdate,spaid,spuid,participantid,participantgroup,oldparticipantgroup,oldstartdate,oldparticipantid,
batchrunid,sprstatus,paidflag,elapsdays,errorfilecount,startdate,enddate,filename,opsbank_ii_ref
FROM sourceprocresult spr, legacy_mapping_items l
WHERE spr.spuid = l.legacy_ref
AND legacy_table_name='SOURCEPROCUNIT'
AND legacy_column_name='SPU_ID'
AND opsbank_ii_table_name='PARCEL_UNITS'
AND opsbank_ii_column_name='PARCEL_UNIT_ID'
AND spaid IN(2,6,68,71,75,77,79,82,84,125,129,135,137,164,165,174,177,182,185,196,200,206,210,216,220,233,235,278,336,338,776,778,780,782,784,
797,799,870,873,876,879,882,885,888,894,898,902,905,908,910,913,916,919,921,924,927,928,929,930,932,934,936,941,944)
ORDER BY sprid;
The procedure code is in the attached file for your reference.
Regards
Lalit
[edited to split long lines]
-
Attachment: SPR proc.txt
(Size: 22.53KB, Downloaded 1595 times)
[Updated on: Mon, 07 December 2009 09:24] by Moderator Report message to a moderator |
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433770 is a reply to message #433421] |
Fri, 04 December 2009 03:36 |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi All,
I tried kuailingtong's suggestions but no benefit. I have to flush the shared_pool to boost up the performance and finish up the procedure.
I guess the table filling in the script is have the problem. Because on another try of kuailingtong's suggestion, i flush the shared_pool 2-3 mins after scheduling the procedure. It shows good performance for the whole procedure, and proc completed in 55 Mins(Expected time).
I am trying to fill up the table before scheduling the script and remove this filling table statment from the procedure code.
Regard
Lalit
|
|
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433819 is a reply to message #433421] |
Fri, 04 December 2009 06:59 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Some other thoughts on the code:
1) The cursor loop should be a cursor for loop
2) The first select count is unecessary, remove it.
If you really want to check if the cursor found no rows stick a counter variable in the for loop and interogate it at the end.
3) You've got three queries with the same where clause - the one you say is giving you problems - merge them.
There's no point doing expensive sql more times than you have to.
The count is pointless as it'll be either 1 or 0. Do something like this:
BEGIN
select outer.participantgroup, outer.participantid INTO v_participantgroup2
from sourceprocresult outer,
( select max(sprid) sprid
from sourceprocresult
where spuid = v_spuid
and sprid < v_sprid
) inner
where outer.sprid = inner.sprid;
<rest of the code you do when the count isn't zero>
EXCEPTION WHEN no_data_found THEN
v_party_supl_prcsg_order_id:=13;
END;
It might be you can go one step further and actually merge these into the cursor you are looping over using analytical functions.
If I'm reading it right you're trying to get data from sourceprocresult for the same spuid as the current record but with highest sprid below the current value.
Is the data you select from these always from records that will already have been found by the main cursor (it's ordered by sprid after all)?
If so try storing the values in variables as you loop round so you don't have to requery them at all.
I'm not an expert on analytics but it should be possible and should significantly reduce the amount of work oracle is doing.
4) most of the select sequence from duals can be removed.
If you don't need the sequence value in multiple places then just reference it direct in the insert statement.
5) I doubt that the commit inside the loop is a good idea.
Since you're not deleting from your source table as you go you're going to have a big problem if the procedure fails after it's committed data
since if you re-run it it'll create duplicate data in your target tables.
6) I'd lose all the exception handlers.
If you want to know which table caused a problem the line number from the error message should be more than sufficient to do so
and if this is run from somewhere that doesn't read dbms_output you'll never know anything went wrong.
7) I suspect that with a bit of work you could rewrite the whole thing as a series of insert as select statements.
If you move the large IF statements into seperate functions it ought to be possible and would probably be the most efficient way of doing this.
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #433826 is a reply to message #433421] |
Fri, 04 December 2009 07:48 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
And finally:
I don't see any need to analyze the whole schema - just do sourceprocresult and its indexes.
If you're going to keep the select that's giving you grief replace these indexes:
EXECUTE IMMEDIATE 'CREATE INDEX spr_sprid ON sourceprocresult(sprid)';
EXECUTE IMMEDIATE 'CREATE INDEX spr_spuid ON sourceprocresult(spuid)';
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #434004 is a reply to message #433421] |
Sun, 06 December 2009 07:28 |
lalitm_2003
Messages: 86
Registered: May 2005
Location: delhi
|
Member |
|
|
Hi CookieMonster,
Regarding
I strongly suspect this is a large part of the problem:
Code: [Select all] [Show/ hide]
EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing=SIMILAR';
Set it to exact.
The parameter cursor_sharing was Exact when i first face this issue. So, i tried to change this parameter to SIMILAR. But that still can not resolve this issue.
Let me test the script with composite index in SPRID and SPUID.
I didn't get your first point. Can you please explain this a bit.
1) The cursor loop should be a cursor for loop
Your suggestion Point 3 can not be done. Because the our requirement is like that if the counter =0 then set the variable to 13 and use in the below part of the script.
Regards
Lalit
|
|
|
|
| Re: Performance issue while pl/sql script execution (merged 3) [message #434028 is a reply to message #434004] |
Sun, 06 December 2009 19:05 |
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
lalitm_2003 wrote on Sun, 06 December 2009 13:28Hi CookieMonster,
Regarding
I strongly suspect this is a large part of the problem:
Code: [Select all] [Show/ hide]
EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing=SIMILAR';
Set it to exact.
The parameter cursor_sharing was Exact when i first face this issue. So, i tried to change this parameter to SIMILAR. But that still can not resolve this issue.
Then i suggest you read up on what cursor sharing does because there is nothing in your code that would improve with it set to similar and a lot of stuff that would probably slow down with that setting.
There's a lot of usefull stuff about it on asktom.
lalitm_2003 wrote on Sun, 06 December 2009 13:28I didn't get your first point. Can you please explain this a bit.
1) The cursor loop should be a cursor for loop
FOR rec in cur_sourceprocresult LOOP
It makes for neater code, and less variables. Won't make any difference to the speed.
lalitm_2003 wrote on Sun, 06 December 2009 13:28
Your suggestion Point 3 can not be done. Because the our requirement is like that if the counter =0 then set the variable to 13 and use in the below part of the script.
That's exactly what my example does. So you can and should do it.
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat May 18 05:15:04 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
