No question at this time
DBA Top 10
1 A. Kavsek 12200
2 M. Cadot 7000
3 P. Wisse 5600
4 B. Vroman 5400
5 J. Péran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 T. Boles 400
10 A. Chavan 300
10 J. Schnackenberg 300
10 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48454
Total active users1499
Act. users last 24h5
Act. users last hour0
Registered user hits last week216
Registered user hits last month950
Go up

PGA usage
Next thread: Is there anyway a PGA limitation?
Prev thread: restore database

Message Score Author Date
Hi, I have some hesitations. I came across a si...... Lauri Aug 28, 2019, 11:39
Hi Lauri, Did you take a glance at your v$pg...... Joël Péran Aug 28, 2019, 11:50
Hi, Thanks for the feedback. I identified inde...... Lauri Aug 28, 2019, 12:06
Lauri, One thing that can also cause high I/O ...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Joël Péran Aug 28, 2019, 13:56
Hi Joël, Yes, you are absolutely right, this is...... Lauri Aug 28, 2019, 14:08

Follow up by mail Click here


Subject: PGA usage
Author: Lauri, Netherlands
Date: Aug 28, 2019, 11:39, 83 days ago
Os info: Linux x86 64bit
Oracle info: 12cR2
Error info: n/a
Message: Hi,

I have some hesitations. I came across a situation I had never seen so far.
I understood that allocating a sufficient size for PGA for sorts en groupings would avoid disk I/O.
In v$pgastat I see for instance that I have maximum PGA allocated=4220996608, even thought the PGA aggregate target wat set to 8G.
Furthermore, I have bytes processed=30697310964736 bytes and extra bytes read/written=52928361178112 bytes.
So, I still have I/O despite the fact that not all the available PGA is used.
Can someone explain me why there is this extra disk I/O?

Kind Regards
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: PGA usage
Author: Joël Péran, France
Date: Aug 28, 2019, 11:50, 83 days ago
Message: Hi Lauri,

Did you take a glance at your v$pga_target_advice view ? Is there enough PGA allocated ? And ie, I have faced recently a weird error, telling me that all the maximum PGA allocated was consumed. So I have seek the query responsible for that and it was a vvvvvery long (and bad written) query. I had the culprit.
About your I/O consumption, did you find which tablespace is the most read ? which table and so on ? You could have a lack of indexes, not only PGA ...

Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-PGA usage
Author: Lauri, Netherlands
Date: Aug 28, 2019, 12:06, 83 days ago
Message: Hi,

Thanks for the feedback.
I identified indeed a query that is quite "heavy" (distinct, group by, union, and select *).
The PGA target advice shows that around 8G of PGA I get 94% Cache hit percentage (that's the current valuw of PGA I have).
When running ADDM or AWR reports, I do not notice much except fpr CPU time for this query and other queries.
The I/O consumption is what I can see in wa statistics on top Linux commnd.
I see relevant CPU time for temporary read and write in the AWR reports, that's all.
No mention on a specific table or segment.
I get the imression this is a disk latency. But can PGA replace completely I/O if I have sufficient memory?

Kind Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Re-PGA usage
Author: Joël Péran, France
Date: Aug 28, 2019, 13:56, 83 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Lauri,

One thing that can also cause high I/O consumption but also high CPU usage is a fragmented table. It may happen sometimes with tables that filled/emptied. I had the case not long ago, getting high peaks of CPU and also large amount of I/O (and of course .. PGA :)), and the culprit was a table. An empty table. Daily, the table has not many rows, but at night, a lot of rows are inserted, then deleted. And the statistics were always calculated when the table was empty. I have done a table defragmentation, added one index (that was missing), got the table's statistics fixed once the table was filled and I got alot of CPU / I/O and PGA back :)

Regards,
Joel
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-PGA usage
Author: Lauri, Netherlands
Date: Aug 28, 2019, 14:08, 83 days ago
Message: Hi Joël,

Yes, you are absolutely right, this is a point to cosider.
In my situation, the tables are not really "big", just no more than 500MB, but there are a lot of distinct, group by, union, and select * in this query (which I suspect it makes it "heavy').
The statistics (tables + indexes) seem recent enough.
That same query perform "good enough" in another database (configured the same wat, on an identical server). The only difference is that on server level, it seems one has more disk latency.

Kinf Regards
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here