gajmp
asked on
Performance Issue
We have a Report and execute the same from SQL*Plus
Check the statistics
3728 recursive calls
81355 db block gets
493836 consistent gets
493579 physical reads
1548 redo size
23243 bytes sent via SQL*Net to client
9003 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
215 rows processed
I want to reduce physical reads and consistent gets (if required).Pls suggest me how to do this..and tell me the cause fr this
Please also let me know is we need to reduce recursive call..As per parameter setup cursor_sharing is EXACT
I have using WITH clause in the query
Check the statistics
3728 recursive calls
81355 db block gets
493836 consistent gets
493579 physical reads
1548 redo size
23243 bytes sent via SQL*Net to client
9003 bytes received via SQL*Net from client
16 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
215 rows processed
I want to reduce physical reads and consistent gets (if required).Pls suggest me how to do this..and tell me the cause fr this
Please also let me know is we need to reduce recursive call..As per parameter setup cursor_sharing is EXACT
I have using WITH clause in the query
ASKER
Thanks for your reply. we can reduce the logical read also (if possible) thats y i have mentioned "if required". I am sure, if we run the same query again then consistent gets will be reduced.
I am not sure recursive call will do Parsing or not. thats y i want to reduce that. if the recursive call wont do parsing again then im ok with that recursive call.
I am not sure recursive call will do Parsing or not. thats y i want to reduce that. if the recursive call wont do parsing again then im ok with that recursive call.
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
To add to what mrjoltcola said:
You really don't tune from the metrics. They are a part of the overall tuning process but you don't start the process with: How do I reduce disk reads. The answer to that is easy: Get more RAM.
You start off by identifying a 'slow' process. You then look at that is making it slow. This is where the plan comes in. It will show you if you can fix the problem with more or less indexes.
If you want to jump right in to the nuts and bolts: Trace the session and run TKPROF against it.
You really don't tune from the metrics. They are a part of the overall tuning process but you don't start the process with: How do I reduce disk reads. The answer to that is easy: Get more RAM.
You start off by identifying a 'slow' process. You then look at that is making it slow. This is where the plan comes in. It will show you if you can fix the problem with more or less indexes.
If you want to jump right in to the nuts and bolts: Trace the session and run TKPROF against it.
ASKER
@mrjoltcola
I can't create Index now. If i suggest to create index they wnt allow me. bcz client using the application for the last two years. now only they are facing performance issue. It may be because of volume of data increased. but they are fearing to create index because it will affect DML. So please suggest me some other option
@slightwv
"Get more RAM" currently DB box have 15GB RAM. but they have allocated 1GB for SGA, sort area size is 64K and db_block_size is 8k.I feel if we increase SGA and sort_area_size then it will gv fruit full result. but they have set workarea_size_policy is AUTO and pga_aggregate_target is 0. Is it do auto mem mgt?
I can't create Index now. If i suggest to create index they wnt allow me. bcz client using the application for the last two years. now only they are facing performance issue. It may be because of volume of data increased. but they are fearing to create index because it will affect DML. So please suggest me some other option
@slightwv
"Get more RAM" currently DB box have 15GB RAM. but they have allocated 1GB for SGA, sort area size is 64K and db_block_size is 8k.I feel if we increase SGA and sort_area_size then it will gv fruit full result. but they have set workarea_size_policy is AUTO and pga_aggregate_target is 0. Is it do auto mem mgt?
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
>>If i suggest to create index they wnt allow me. bcz client using the application for the last two years. now only they are facing performance issue
That's like a patient telling a doctor what to prescribe. In that case, the doctor should dismiss the patient.
The client needs to be educated, and needs to trust you as the expert. If they don't, there is a larger non-technical problem that needs to be addressed first.
That's like a patient telling a doctor what to prescribe. In that case, the doctor should dismiss the patient.
The client needs to be educated, and needs to trust you as the expert. If they don't, there is a larger non-technical problem that needs to be addressed first.
The 'if required' concerns me. What makes you think the recursive calls is 'bad'?
Please post the execution plan (or plans) for the SQL.