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 Go to next message
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 #337699 is a reply to message #337697] Thu, 31 July 2008 13:42 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/84315/74940/
Which have you tried & what were the results?

Besides Oracle, what else is running on this system?

At the OS level....
Is the system swapping?
What is run queue depth?
Is the system IO bound?
what does "top" show?
Re: dbms job performance issue [message #337706 is a reply to message #337699] Thu, 31 July 2008 16:03 Go to previous messageGo to next message
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 #337753 is a reply to message #337697] Fri, 01 August 2008 00:57 Go to previous messageGo to next message
sunil_v_mishra
Messages: 506
Registered: March 2005
Senior Member
please don't schedule your backup during peak hour and when that job is running... Very Happy

I hope you will have better life ...
Re: dbms job performance issue [message #341647 is a reply to message #337753] Tue, 19 August 2008 15:03 Go to previous message
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? Shocked
Previous Topic: Filter in Oracle
Next Topic: Query Tunning
Goto Forum:
  


Current Time: Sat Dec 03 20:21:18 CST 2016

Total time taken to generate the page: 0.16449 seconds