Home » RDBMS Server » Performance Tuning » dbms job performance issue (solarin 5.10, oracle 10.2.0.3)
| dbms job performance issue [message #337697] |
Thu, 31 July 2008 13:33  |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
Experts,
i am in a problem, where i could not analyzed and pinpoint the performance problem.
one of the dbms_job will process some data.
dbms job will do select , update some table, insert in some tables.
it was ran fine upto 24th of this month. after 24 we are facing problem , this job is hanging particular query. and i was observed AWR report, i am able to see backup is taking long time. then i was opened SR with oracle. they are saying that due to backup you are facing I/O waits. i was killed the job and i was restarted the database. even though i will not helpful to me. when i went for server reboot, it was helped to me.
this job ran up to 28th date, again today we have backup schedule and it was taking problem, again we hit the performance problem. again we kill the problem and this job is performance if slow.
my question is :
after reboot the server this job went fine. in this case i am not able to find or pin point the problem. can any one suggest me what areas i need to look, how to investigate it.
could provide any any direction.
|
|
|
|
|
|
| Re: dbms job performance issue [message #337706 is a reply to message #337699] |
Thu, 31 July 2008 16:03  |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
experts,
i am in as senerieo where one of the query is taking several execution plan. so performance of the query is worst. here i am attaching different query plans for same query. can any one assist me,which one is better.
i want to instruct oracle to choose only one execution plan always.
is is possible?
please find execution plans
BBQISPRD:SYSTEM> select * from table(dbms_xplan.dispaly_awr('gqrvuv6r39yt4'));
select * from table(dbms_xplan.dispaly_awr('gqrvuv6r39yt4'))
*
ERROR at line 1:
ORA-00904: "DBMS_XPLAN"."DISPALY_AWR": invalid identifier
BBQISPRD:SYSTEM> select * from table(dbms_xplan.display_awr('gqrvuv6r39yt4'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID gqrvuv6r39yt4
--------------------
SELECT D.EVENTID, T.TYPE, TO_CHAR(D.GMT, 'YYYYMM') EVENT_MONTH, T.MANUFACTURER, T.MFG_PARTNO, T.PC_PARTNO,
T.CURRENCY, T.METHOD, VALUE, DAP, DAP_CURRENCY, T.RESTRICT_FLAG FROM TXN_DISPOSITION D, TXN_BB_CORRELATED T WHERE
D.STAGE_ID = :B3 AND T.ADJUSTMENT_EVENTID = :B2 AND T.PRIMARY_CARRIER_NAME = :B1 AND T.TYPE IN ('EX', 'PX','SX') AND
D.EVENTID = T.EVENTID AND D.PRIMARY_CARRIER_NAME = T.PRIMARY_CARRIER_NAME ORDER BY MANUFACTURER
Plan hash value: 131342129
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 503 | 10 (10)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 503 | 9 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_DISPOSITION | 1 | 29 | 6 (0)| 00:00:01 | ROW L | ROW L |
| 4 | INDEX RANGE SCAN | TXN_DISPOSITION_N9 | 1 | | 5 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_BB_CORRELATED | 1 | 474 | 3 (0)| 00:00:01 | ROW L | ROW L |
| 6 | INDEX UNIQUE SCAN | TXN_BB_CORRELATED_U1 | 1 | | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID gqrvuv6r39yt4
--------------------
SELECT D.EVENTID, T.TYPE, TO_CHAR(D.GMT, 'YYYYMM') EVENT_MONTH, T.MANUFACTURER, T.MFG_PARTNO, T.PC_PARTNO,
T.CURRENCY, T.METHOD, VALUE, DAP, DAP_CURRENCY, T.RESTRICT_FLAG FROM TXN_DISPOSITION D, TXN_BB_CORRELATED T WHERE
D.STAGE_ID = :B3 AND T.ADJUSTMENT_EVENTID = :B2 AND T.PRIMARY_CARRIER_NAME = :B1 AND T.TYPE IN ('EX', 'PX','SX') AND
D.EVENTID = T.EVENTID AND D.PRIMARY_CARRIER_NAME = T.PRIMARY_CARRIER_NAME ORDER BY MANUFACTURER
Plan hash value: 1285644861
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 13 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 503 | 13 (8)| 00:00:01 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | TXN_DISPOSITION | 1 | 29 | 4 (0)| 00:00:01 | ROW L | ROW L |
| 3 | NESTED LOOPS | | 1 | 503 | 12 (0)| 00:00:01 | | |
| 4 | INLIST ITERATOR | | | | | | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_BB_CORRELATED | 1 | 474 | 8 (0)| 00:00:01 | ROW L | ROW L |
| 6 | INDEX RANGE SCAN | TXN_BB1_COR_N5 | 1 | | 6 (0)| 00:00:01 | | |
| 7 | INDEX RANGE SCAN | TXN_DISPOSITION_N5 | 1 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID gqrvuv6r39yt4
--------------------
SELECT D.EVENTID, T.TYPE, TO_CHAR(D.GMT, 'YYYYMM') EVENT_MONTH, T.MANUFACTURER, T.MFG_PARTNO, T.PC_PARTNO, T.CURRENCY, T.METHOD, VALUE, DAP,
DAP_CURRENCY, T.RESTRICT_FLAG FROM TXN_DISPOSITION D, TXN_BB_CORRELATED T WHERE D.STAGE_ID = :B3 AND T.ADJUSTMENT_EVENTID = :B2 AND
T.PRIMARY_CARRIER_NAME = :B1 AND T.TYPE IN ('EX', 'PX','SX') AND D.EVENTID = T.EVENTID AND D.PRIMARY_CARRIER_NAME = T.PRIMARY_CARRIER_NAME O
RDER BY
MANUFACTURER
Plan hash value: 1598563865
--------------------------------------------------------------------------------------------------------------------------------------------
------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN
-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
------------------
| 0 | SELECT STATEMENT | | | | 21 (100)| | | | |
| |
| 1 | PX COORDINATOR | | | | | | | | |
| |
| 2 | PX SEND QC (ORDER) | :TQ10001 | 1 | 503 | 21 (10)| 00:00:01 | | | Q1,01 | P
->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 1 | 503 | 21 (10)| 00:00:01 | | | Q1,01 | P
CWP | |
| 4 | PX RECEIVE | | 1 | 29 | 4 (0)| 00:00:01 | | | Q1,01 | P
CWP | |
| 5 | PX SEND RANGE | :TQ10000 | 1 | 29 | 4 (0)| 00:00:01 | | | Q1,00 | P
->P | RANGE |
| 6 | TABLE ACCESS BY GLOBAL INDEX ROWID | TXN_DISPOSITION | 1 | 29 | 4 (0)| 00:00:01 | ROW L | ROW L | Q1,00 | P
CWC | |
| 7 | NESTED LOOPS | | 1 | 503 | 19 (0)| 00:00:01 | | | Q1,00 | P
CWP | |
| 8 | PX PARTITION RANGE ALL | | 1 | 474 | 18 (0)| 00:00:01 | 1 | 31 | Q1,00 | P
CWC | |
| 9 | INLIST ITERATOR | | | | | | | | Q1,00 | P
CWC | |
| 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TXN_BB_CORRELATED | 1 | 474 | 18 (0)| 00:00:01 | 1 | 31 | Q1,00 | P
CWP | |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
| 11 | INDEX RANGE SCAN | TXN_BB1_COR_N5 | 1 | | 18 (0)| 00:00:01 | 1 | 31 | Q1,00 | P
CWP | |
| 12 | INDEX RANGE SCAN | TXN_DISPOSITION_N6 | 1 | | 3 (0)| 00:00:01 | | | Q1,00 | P
CWP | |
--------------------------------------------------------------------------------------------------------------------------------------------
------------------
SQL_ID gqrvuv6r39yt4
--------------------
SELECT D.EVENTID, T.TYPE, TO_CHAR(D.GMT, 'YYYYMM') EVENT_MONTH, T.MANUFACTURER, T.MFG_PARTNO, T.PC_PARTNO,
T.CURRENCY, T.METHOD, VALUE, DAP, DAP_CURRENCY, T.RESTRICT_FLAG FROM TXN_DISPOSITION D, TXN_BB_CORRELATED T WHERE
D.STAGE_ID = :B3 AND T.ADJUSTMENT_EVENTID = :B2 AND T.PRIMARY_CARRIER_NAME = :B1 AND T.TYPE IN ('EX', 'PX','SX')
AND D.EVENTID = T.EVENTID AND D.PRIMARY_CARRIER_NAME = T.PRIMARY_CARRIER_NAME ORDER BY MANUFACTURER
Plan hash value: 2188212949
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 407 | 11 (10)| 00:00:01 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_DISPOSITION | 1 | 29 | 4 (0)| 00:00:01 | ROW L | ROW L |
| 3 | NESTED LOOPS | | 1 | 407 | 10 (0)| 00:00:01 | | |
| 4 | INLIST ITERATOR | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID | TXN_BB_CORRELATED | 1 | 378 | 6 (0)| 00:00:01 | | |
| 6 | INDEX RANGE SCAN | TXN_BB_COR_N5 | 1 | | 5 (0)| 00:00:01 | | |
| 7 | INDEX RANGE SCAN | TXN_DISPOSITION_N6 | 1 | | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
SQL_ID gqrvuv6r39yt4
--------------------
SELECT D.EVENTID, T.TYPE, TO_CHAR(D.GMT, 'YYYYMM') EVENT_MONTH, T.MANUFACTURER, T.MFG_PARTNO, T.PC_PARTNO,
T.CURRENCY, T.METHOD, VALUE, DAP, DAP_CURRENCY, T.RESTRICT_FLAG FROM TXN_DISPOSITION D, TXN_BB_CORRELATED T WHERE
D.STAGE_ID = :B3 AND T.ADJUSTMENT_EVENTID = :B2 AND T.PRIMARY_CARRIER_NAME = :B1 AND T.TYPE IN ('EX', 'PX','SX')
AND D.EVENTID = T.EVENTID AND D.PRIMARY_CARRIER_NAME = T.PRIMARY_CARRIER_NAME ORDER BY MANUFACTURER
Plan hash value: 2790272152
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 10 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 482 | 10 (10)| 00:00:01 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_DISPOSITION | 1 | 29 | 4 (0)| 00:00:01 | ROW L | ROW L |
| 3 | NESTED LOOPS | | 1 | 482 | 9 (0)| 00:00:01 | | |
| 4 | TABLE ACCESS BY INDEX ROWID | TXN_BB_CORRELATED | 1 | 453 | 5 (0)| 00:00:01 | | |
| 5 | INDEX RANGE SCAN | TXN_BB1_COR_N2 | 1 | | 4 (0)| 00:00:01 | | |
| 6 | INDEX RANGE SCAN | TXN_DISPOSITION_N6 | 1 | | 3 (0)| 00:00:01 | | |
--------------------------------------------------------------------------------------------------------------------------
SQL_ID gqrvuv6r39yt4
--------------------
SELECT D.EVENTID, T.TYPE, TO_CHAR(D.GMT, 'YYYYMM') EVENT_MONTH, T.MANUFACTURER, T.MFG_PARTNO, T.PC_PARTNO,
T.CURRENCY, T.METHOD, VALUE, DAP, DAP_CURRENCY, T.RESTRICT_FLAG FROM TXN_DISPOSITION D, TXN_BB_CORRELATED T WHERE
D.STAGE_ID = :B3 AND T.ADJUSTMENT_EVENTID = :B2 AND T.PRIMARY_CARRIER_NAME = :B1 AND T.TYPE IN ('EX', 'PX','SX') AND
D.EVENTID = T.EVENTID AND D.PRIMARY_CARRIER_NAME = T.PRIMARY_CARRIER_NAME ORDER BY MANUFACTURER
Plan hash value: 3444166618
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 503 | 9 (12)| 00:00:01 | | |
| 2 | NESTED LOOPS | | 1 | 503 | 8 (0)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_DISPOSITION | 1 | 29 | 5 (0)| 00:00:01 | ROW L | ROW L |
| 4 | INDEX RANGE SCAN | TXN_DISPOSITION_N4 | 1 | | 4 (0)| 00:00:01 | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_BB_CORRELATED | 1 | 474 | 3 (0)| 00:00:01 | ROW L | ROW L |
| 6 | INDEX UNIQUE SCAN | TXN_BB_CORRELATED_U1 | 1 | | 2 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID gqrvuv6r39yt4
--------------------
SELECT D.EVENTID, T.TYPE, TO_CHAR(D.GMT, 'YYYYMM') EVENT_MONTH, T.MANUFACTURER, T.MFG_PARTNO, T.PC_PARTNO,
T.CURRENCY, T.METHOD, VALUE, DAP, DAP_CURRENCY, T.RESTRICT_FLAG FROM TXN_DISPOSITION D, TXN_BB_CORRELATED T WHERE
D.STAGE_ID = :B3 AND T.ADJUSTMENT_EVENTID = :B2 AND T.PRIMARY_CARRIER_NAME = :B1 AND T.TYPE IN ('EX', 'PX','SX') AND
D.EVENTID = T.EVENTID AND D.PRIMARY_CARRIER_NAME = T.PRIMARY_CARRIER_NAME ORDER BY MANUFACTURER
Plan hash value: 4033815551
-----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| | | |
| 1 | SORT ORDER BY | | 1 | 503 | 12 (9)| 00:00:01 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID | TXN_DISPOSITION | 1 | 29 | 4 (0)| 00:00:01 | ROW L | ROW L |
| 3 | NESTED LOOPS | | 1 | 503 | 11 (0)| 00:00:01 | | |
| 4 | INLIST ITERATOR | | | | | | | |
| 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| TXN_BB_CORRELATED | 1 | 474 | 7 (0)| 00:00:01 | ROW L | ROW L |
| 6 | INDEX RANGE SCAN | TXN_BB1_COR_N5 | 1 | | 6 (0)| 00:00:01 | | |
| 7 | INDEX RANGE SCAN | TXN_DISPOSITION_N6 | 1 | | 3 (0)| 00:00:01 | | |
-----------------------------------------------------------------------------------------------------------------------------
BBQISPRD:SYSTEM> spool off
|
|
|
|
|
|
| Re: dbms job performance issue [message #341647 is a reply to message #337753] |
Tue, 19 August 2008 15:03 |
kumar.joy
Messages: 69
Registered: August 2007
Location: morrisville
|
Member |
|
|
Thanks for input,
=> don't schedule your backup during peak hour and when that job is running.
i did not schedule the backup on day time(busy time). it starts in the evening 6:00 pm and it will take around 14 hours now.
we have reduced the backup time by increasing no of streams.
still the problem exist.
one more reason is execution plan is changing dynamically, we provided hint to choose better execution plan.
now my backup is 14 hours. and dbms_job will normally take 4 hours to complete.
now i am in a situation where my bakcup and dbms_job is sharing 1 hour. that time dbms_job is taking more then 4 hours (about 6 hours).
if i change my staging job time it will give more problems for normal operation.
how do i get which is causing the slowness of dbms_job.
one stupid question i have,
will rman put tablespace in begin backup mode. and backup after end backup mode?
or
rman reads blocks from physical file and load it into the memory and then backup.
can any one suggest me right way to find the problem? 
|
|
|
|
Goto Forum:
Current Time: Thu Jun 27 21:36:40 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
