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 Go to next message
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 Go to previous messageGo to next message
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 [message #433427 is a reply to message #433421] Wed, 02 December 2009 05:15 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Can you post an explain plan for that query please.
Also why are you using a cardinality hint?
Re: Performance issue while pl/sql script execution (merged 3) [message #433457 is a reply to message #433421] Wed, 02 December 2009 07:27 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
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 #433549 is a reply to message #433546] Wed, 02 December 2009 22:46 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
login to sqlplus as the schema owner
CUT the lines until EXIT below & PASTE into sqlplus session

SET AUTOTRACE TRACEONLY EXPLAIN STATISTICS
select /*+ CARDINALITY(outer 1)*/ outer.participantgroup I
from sourceprocresult outer
where sprid =
(select max(sprid)
from sourceprocresult inner
where spuid = v_spuid
and sprid < v_sprid);
EXPLAIN PLAN select /*+ CARDINALITY(outer 1)*/ outer.participantgroup
from sourceprocresult outer
where sprid =
(select max(sprid)
from sourceprocresult inner
where spuid = v_spuid
and sprid < v_sprid);
SELECT * FROM table dbms_xplan.display;
ALTER SESSION SET SQL_TRACE=TRUE;
select /*+ CARDINALITY(outer 1)*/ outer.participantgroup
from sourceprocresult outer
where sprid =
(select max(sprid)
from sourceprocresult inner
where spuid = v_spuid
and sprid < v_sprid);
ALTER SESSION SET SQL_TRACE=FALSE;
EXIT

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>
post formatted contents of trace_results.txt
Re: Performance issue while pl/sql script execution (merged 3) [message #433550 is a reply to message #433546] Wed, 02 December 2009 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Before proceeding, PLEASE read section the "How to format your post?" in URL below:
http://www.orafaq.com/forum/t/88153/0/
Be sure to properly format all subsequent posts!
It is requested that you use sqlplus for all subsequent posts to your thread.
CUT & PASTE whole sqlplus session so we can see exactly what you do & how Oracle responds.
Re: Performance issue while pl/sql script execution (merged 3) [message #433556 is a reply to message #433421] Wed, 02 December 2009 23:38 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 #433737 is a reply to message #433639] Thu, 03 December 2009 22:01 Go to previous messageGo to next message
kuailingtong
Messages: 4
Registered: September 2009
Junior Member
Could you use simple sql like this.


select /*+leading(b)*/ a.participantgroup 
  from sourceprocresult a,
       ( select max(sprid) sprid,
                rownum rn 
           from sourceprocresult
          where spuid = 100000000
            and sprid < 1521
       ) b
 where a.sprid = b.sprid;

Re: Performance issue while pl/sql script execution (merged 3) [message #433754 is a reply to message #433421] Fri, 04 December 2009 01:25 Go to previous messageGo to next message
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 261 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 Go to previous messageGo to next message
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 #433772 is a reply to message #433421] Fri, 04 December 2009 03:47 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I strongly suspect this is a large part of the problem:
EXECUTE IMMEDIATE 'ALTER SESSION SET cursor_sharing=SIMILAR';

Set it to exact.
Re: Performance issue while pl/sql script execution (merged 3) [message #433819 is a reply to message #433421] Fri, 04 December 2009 06:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
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)'; 
with one index on both columns.
Re: Performance issue while pl/sql script execution (merged 3) [message #434004 is a reply to message #433421] Sun, 06 December 2009 07:28 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
lalitm_2003 wrote on Sun, 06 December 2009 13:28
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.

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:28
I 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.
Re: Performance issue while pl/sql script execution (merged 3) [message #434035 is a reply to message #433421] Sun, 06 December 2009 22:23 Go to previous messageGo to next message
DBA_SangramKeshari
Messages: 44
Registered: October 2009
Location: Mumbai
Member
Please check if you have indexes on SPUID and SPRID columns.

Re: Performance issue while pl/sql script execution (merged 3) [message #434164 is a reply to message #434035] Mon, 07 December 2009 09:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Other than the two indexes created by these lines:
EXECUTE IMMEDIATE 'CREATE INDEX spr_sprid ON sourceprocresult(sprid)';
EXECUTE IMMEDIATE 'CREATE INDEX spr_spuid ON sourceprocresult(spuid)'; 


It frequently helps to read the whole topic before commenting.
Previous Topic: retrieve deleted data of user_jobs
Next Topic: Buffer pool Hit% more than 100%
Goto Forum:
  


Current Time: Fri Dec 09 15:07:54 CST 2016

Total time taken to generate the page: 0.15389 seconds