| Process tuning [message #209704] |
Sun, 17 December 2006 04:23  |
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  |
michael_bialik
Messages: 621
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 |
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 #209714 is a reply to message #209707] |
Sun, 17 December 2006 05:38 |
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 |
michael_bialik
Messages: 621
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 #209721 is a reply to message #209704] |
Sun, 17 December 2006 06:34 |
michael_bialik
Messages: 621
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 |
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 |
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 #209731 is a reply to message #209704] |
Sun, 17 December 2006 07:50 |
michael_bialik
Messages: 621
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 #209733 is a reply to message #209704] |
Sun, 17 December 2006 07:57 |
michael_bialik
Messages: 621
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 #209738 is a reply to message #209734] |
Sun, 17 December 2006 08:07 |
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 |
michael_bialik
Messages: 621
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 #209742 is a reply to message #209738] |
Sun, 17 December 2006 08:23 |
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 #209745 is a reply to message #209704] |
Sun, 17 December 2006 08:37 |
michael_bialik
Messages: 621
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 |
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 |
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.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
