Home » RDBMS Server » Performance Tuning » Process tuning
Process tuning [message #209704] Sun, 17 December 2006 04:23 Go to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
Dear All,

I am new to database tuning field my manager assigned me a tough task and tune one of the processes, really I do not know where to start but I have traced the session of this process and I am uploading the output, can any body help me where I have to focus,
I have tried to see those tables showing FULL table scan and found them very small tables with a few records, I am afraid this problem is related to IO, the process is taken 18 ours to generate invoices for only 25000 customer and we have sun SAN storage 6320,
And Sunfire 2900 server, I am really in bad position can any body help me on this.


[Updated on: Sun, 17 December 2006 04:26]

Report message to a moderator

Re: Process tuning [message #209706 is a reply to message #209704] Sun, 17 December 2006 05:08 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
There is nothing special in the attached trace file:

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 32 0.02 0.05 0 0 0 0
Execute 216 0.70 2.71 203 2518 10548 403
Fetch 72 0.08 0.56 27 272 336 320
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 320 0.80 3.32 230 2790 10884 723


All statements took 3.32 seconds.

The question is : is it a trace for a single customer or something else?
If it's a single customer then 3.3 sec * 25000 = ( about ) 20 hours. In that case post a process dealing with a NUMBER of customers ( 10 - 20 is enough).

Re: Process tuning [message #209707 is a reply to message #209706] Sun, 17 December 2006 05:15 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
Many Many thanks for your reply this process was run againest a few customers say 11 customers, I will try to re-run the process againest 200 customers beside I will send the statspack report for the instance runing this process.
Re: Process tuning [message #209713 is a reply to message #209704] Sun, 17 December 2006 05:36 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
If you can - post the trace for 500-600 customers and
add folloiwng in your TKPROF command line sort=prsela,fchela,exeela

Re: Process tuning [message #209714 is a reply to message #209707] Sun, 17 December 2006 05:38 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
find attached the statspack of database runing the process

I will run the process and generate the tkprof as per your instruction
Re: Process tuning [message #209718 is a reply to message #209704] Sun, 17 December 2006 06:12 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
It looks like you have a completely un-tuned system.

Check following statements(just for starters):
1. select M1.REQUEST ,M1.STATUS ,M1.ACTION_ID ,M1.PRIORITY ,
TO_CHAR(M1.ACTION_DATE,'YYYYMMDDHH24MISS') ,
M1.GMD_MARKET_ID ,
M1.SCCODE,M1.PLCODE ,M1.SWITCH_ID ,M1.CO_ID ,M1.DATA_1 ,
M1.DATA_2 ,M1.DATA_3
from MDSRRTAB M1 where WORKER_PID=:b0 order by M1.REQUEST

Executions : 405
Physical.reads : 6,303,827
Buffer Gets : 7,035,325

IMHO it performs FULL table scan of MDSRRTAB table.

Do you have an index defined on WORKER_PID column there?

If not - create one.

2. select M1.REQUEST ,M1.STATUS ,M1.ACTION_ID ,M1.CO_ID ,
M1.CUSTOMER_ID ,GM.CLEAN_UP_LEVEL ,GA.ACTION_DES
from MDSRRTAB M1 ,GMD_MPSCTAB GM ,GMD_ACTION GA
where ((M1.WORKER_PID=:b0 and GM.SCCODE = M1.SCCODE) and GA.ACTION_ID=M1.ACTION_ID)
order by REQUEST

Executions : 76
Physical.reads : 1,181,056
Buffer Gets : 1,320,915


The same problem here as well ( the previously defined index will help here as well).

3. select M1.REQUEST ,M1.STATUS ,M1.VMD_RETRY ,M1.ERROR_RETRY ,
M1.SWITCH_ID ,M1.CO_ID ,M1.CUSTOMER_ID ,GA.ACTION_ID ,
GA.ACTION_DES,GM.BYPASS_IND
from MDSRRTAB M1 ,GMD_ACTION GA ,GMD_MPDSCTAB GM
where ((M1.WORKER_PID=:b0 and M1.ACTION_ID=GA.ACTION_ID)
and M1.SCCODE=GM.SCCODE)
order by M1.REQUEST

Executions : 42
Physical.reads : 644,235
Buffer Gets : 729,725

The same problem here as well ( the previously defined index will help here as well).


IMHO you have to check all your application (the specific process is only a small part of your performance problems).

HTH.



Re: Process tuning [message #209720 is a reply to message #209714] Sun, 17 December 2006 06:20 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
This table has index on that field attached is details of the index
Re: Process tuning [message #209721 is a reply to message #209704] Sun, 17 December 2006 06:34 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Is the column WORKER_PID nullable?
2. If yes - How many rows have NULL in that column?
3. If no - What is the meaning of a SINGLE value in that column?
4. The last analyze was performed on 04/11/2006 02:22:30. Is there a reason nobody analyzed that table since?

Re: Process tuning [message #209722 is a reply to message #209720] Sun, 17 December 2006 06:50 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
This is a transit temporary table whenever process is finished the data is moved to another history table, this table is highly violated, there are many requests to add/remove/modify GSM service for thatthis table is accessd and inserted, this is the reason behind being many full table scans on this table, moreover number of records are very small do you think caching the table will help.
Re: Process tuning [message #209723 is a reply to message #209722] Sun, 17 December 2006 06:56 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
1. Is the column WORKER_PID nullable?
NO

2. If yes - How many rows have NULL in that column?
No null values

3. If no - What is the meaning of a SINGLE value in that column?
what do yo mean by this question ?

4. The last analyze was performed on 04/11/2006 02:22:30. Is there a reason nobody analyzed that table since?

most of the time we run analyze againest database each 15 days
Re: Process tuning [message #209724 is a reply to message #209704] Sun, 17 December 2006 07:02 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Please post the results of following query:

SELECT COUNT(*) cntr, COUNT(DISTINCT WORKER_PID ) pid_cntr
FROM MDSRRTAB ;

Re: Process tuning [message #209725 is a reply to message #209723] Sun, 17 December 2006 07:10 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
CNTR PID_CNTR
29 1
Re: Process tuning [message #209726 is a reply to message #209704] Sun, 17 December 2006 07:16 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
I would like to verify an assumption of mine.

Please post EXCEL file with data from dba_tables concerning the MDSRRTAB table.

Re: Process tuning [message #209728 is a reply to message #209725] Sun, 17 December 2006 07:43 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
No Message Body
Re: Process tuning [message #209729 is a reply to message #209728] Sun, 17 December 2006 07:46 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
No Message Body
Re: Process tuning [message #209730 is a reply to message #209704] Sun, 17 December 2006 07:46 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Sorry, there is a misunderstanding:

I need SELECT * FROM DBA_TABLES WHERE TABLE_NAME = 'MDSRRTAB'
Re: Process tuning [message #209731 is a reply to message #209704] Sun, 17 December 2006 07:50 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you post an EXPLAIN (or TKPROF) for all these statements:

select M1.REQUEST ,M1.STATUS ,M1.ACTION_ID ,M1.PRIORITY ,
TO_CHAR(M1.ACTION_DATE,'YYYYMMDDHH24MISS') ,
M1.GMD_MARKET_ID ,
M1.SCCODE,M1.PLCODE ,M1.SWITCH_ID ,M1.CO_ID ,M1.DATA_1 ,
M1.DATA_2 ,M1.DATA_3
from MDSRRTAB M1 where WORKER_PID=:b0 order by M1.REQUEST

select M1.REQUEST ,M1.STATUS ,M1.ACTION_ID ,M1.CO_ID ,
M1.CUSTOMER_ID ,GM.CLEAN_UP_LEVEL ,GA.ACTION_DES
from MDSRRTAB M1 ,GMD_MPSCTAB GM ,GMD_ACTION GA
where ((M1.WORKER_PID=:b0 and GM.SCCODE = M1.SCCODE) and GA.ACTION_ID=M1.ACTION_ID)
order by REQUEST

select M1.REQUEST ,M1.STATUS ,M1.VMD_RETRY ,M1.ERROR_RETRY ,
M1.SWITCH_ID ,M1.CO_ID ,M1.CUSTOMER_ID ,GA.ACTION_ID ,
GA.ACTION_DES,GM.BYPASS_IND
from MDSRRTAB M1 ,GMD_ACTION GA ,GMD_MPDSCTAB GM
where ((M1.WORKER_PID=:b0 and M1.ACTION_ID=GA.ACTION_ID)
and M1.SCCODE=GM.SCCODE)
order by M1.REQUEST
Re: Process tuning [message #209732 is a reply to message #209730] Sun, 17 December 2006 07:51 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
already I have attached it
Re: Process tuning [message #209733 is a reply to message #209704] Sun, 17 December 2006 07:57 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
My mistake.

There are 17354 blocks in that table, however, most of them are completely empty. Actaully you have there 158 rows, but in order to perform a FULL table scan of these rows Oracle reads over 17K blocks.
You have either to reorganize your table or enforc index access ( try using /*+ RULE */ or /*+ INDEX( M1 ) */ hints ).

HTH
Re: Process tuning [message #209734 is a reply to message #209732] Sun, 17 December 2006 08:00 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
No Message Body
Re: Process tuning [message #209737 is a reply to message #209704] Sun, 17 December 2006 08:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Yes.

The problem is FULL TABLE SCAN of MDSRRTAB ( over 17,000 blocks). Try implement my suggestions about reorg or index access.

Post results.

HTH.
Re: Process tuning [message #209738 is a reply to message #209734] Sun, 17 December 2006 08:07 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
unfortunately I can not add any hints since this is vendor application.

Is this mean I have to create this table with different parameters?

Re: Process tuning [message #209740 is a reply to message #209704] Sun, 17 December 2006 08:17 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
In that case your choices are rather limited:
A. Table REORG
A.1. Verify that your application does NOT stores ROWIDs of MDSRRTAB table somewhere ( ORACLE APPLICATION does such things).
A.2. If NO ROWIDs are stored :
- Stop your application from updating MDSRRTAB table.
- Perform CREATE TABLE MDSRRTAB_TEMP NOLOGGING AS SELECT * FROM MDSRRTAB ;
- Perform TRUNCATE TABLE MDSRRTAB ;
- Perform INSERT INTO MDSRRTAB SELECT * FROM MDSRRTAB_TEMP;
- COMMIT;
- Enable users to update MDSRRTAB table.

B. Use DBMS_STATS to "falsify" statistic for MDSRRTAB table in order to force optimizer into selecting index access.

HTH.
Re: Process tuning [message #209741 is a reply to message #209704] Sun, 17 December 2006 08:18 Go to previous messageGo to next message
dba_blr
Messages: 43
Registered: December 2006
Member
How many rows you have in the MDSRRTAB table?
Re: Process tuning [message #209742 is a reply to message #209738] Sun, 17 December 2006 08:23 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
As I have mentioned before this table has no constant records and it is not gradually incresing it is a transit table it is just used only to process some records then it is truncated and its' data is moved to another table

current number of records 113
Re: Process tuning [message #209743 is a reply to message #209704] Sun, 17 December 2006 08:26 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Can you try defining it as GLOBAL TEMPORARY table (if it's really used by a single process)?
Re: Process tuning [message #209744 is a reply to message #209742] Sun, 17 December 2006 08:34 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
I am afraid it cannot be defined so
Re: Process tuning [message #209745 is a reply to message #209704] Sun, 17 December 2006 08:37 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Then you have the 2 choices I already mentioned.

IMHO DBMS_STATS is a preferred choice, otherwise you will have to reorg the table almost every day.
Anyway - check if the data is TRUNCATED or simply DELETED.
Re: Process tuning [message #209747 is a reply to message #209744] Sun, 17 December 2006 08:46 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
I have get back to the previous statspack report and tried to get the hash values mentioned there for these SQLs and link them back to the user who is excuting the SQL, it is one use sofar,

on the other hand I have maked sure that our billing team delete this table in regular basis for those record with status =7 which means finished requests and inser them into another table before deleting
Re: Process tuning [message #209749 is a reply to message #209747] Sun, 17 December 2006 08:56 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
Tonight we have a bill cycle I will trace the system and attach the results , I really do not know how to thank you this session was very very useful I learend from it many thanks for your time if it possible to change this session to private i would really appreciate again thanks for your time.

see you by tomorrow if possible.
Re: Process tuning [message #209951 is a reply to message #209704] Mon, 18 December 2006 11:38 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
Your billing team has to TRUNCATE the table and NOT delete the data.
Re: Process tuning [message #209964 is a reply to message #209951] Mon, 18 December 2006 13:27 Go to previous messageGo to next message
SSALEMY2K
Messages: 17
Registered: December 2006
Location: Sana'a
Junior Member
I have attached the files
Re: Process tuning [message #209967 is a reply to message #209704] Mon, 18 December 2006 15:04 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
I don't see any changes, so IMHO nobody TRUNCATED the table.
Without TRUNCATE no chance for improvement.

Re: Process tuning [message #210195 is a reply to message #209704] Tue, 19 December 2006 17:21 Go to previous messageGo to next message
business1
Messages: 5
Registered: December 2006
Junior Member
my server is not nice what can i do?





[Updated on: Tue, 19 December 2006 19:05] by Moderator

Report message to a moderator

Re: Process tuning [message #210197 is a reply to message #209704] Tue, 19 December 2006 17:25 Go to previous messageGo to next message
BlackSwan
Messages: 25038
Registered: January 2009
Location: SoCal
Senior Member
>my server is not nice what can i do?
Give it a lump of coal for Christmas.
Re: Process tuning [message #210219 is a reply to message #210197] Tue, 19 December 2006 20:43 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
anacedent wrote on Tue, 19 December 2006 18:25
>
Give it a lump of coal for Christmas.


That's great !!!
Previous Topic: Introduce a good book for oracle performance tuning!
Next Topic: High activity of "delete from smon_scn_time" in statspack report
Goto Forum:
  


Current Time: Mon Dec 05 14:50:34 CST 2016

Total time taken to generate the page: 0.08198 seconds