No question at this time
DBA Top 10
1 A. Kavsek 10900
2 M. Cadot 6200
3 P. Wisse 5000
4 B. Vroman 4500
5 T. Boles 2200
6 . Lauri 2000
7 J. Schnackenberg 1300
8 J. PĂ©ran 1100
9 R. Pattyn 700
10 T. P 500
10 G. Graham 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48425
Total active users1512
Act. users last 24h8
Act. users last hour1
Registered user hits last week172
Registered user hits last month818
Go up

Is there anyway a PGA limitation?
Next thread: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Prev thread: PGA usage

Message Score Author Date
Hi, I came across this article: https://logica...... Lauri Aug 28, 2019, 14:03
Lauri, There's no date in the article or versio...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Philip Wisse Aug 28, 2019, 17:10
Hi, to give you direct reply on your question f...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Aug 29, 2019, 01:25
Hi, Thanks for the answers. Always interesting...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Lauri Aug 29, 2019, 09:03
More reading: https://asktom.oracle.com/pls/ask...... Philip Wisse Aug 29, 2019, 09:18

Follow up by mail Click here


Subject: Is there anyway a PGA limitation?
Author: Lauri, Netherlands
Date: Aug 28, 2019, 14:03, 22 days ago
Os info: All
Oracle info: 12c and higher
Message: Hi,

I came across this article:
https://logicalread.com/oracle-wait-event-explained-direct-path-read-temp-jk01/#.XWZqnnduIy8

Is it true that "pga_aggregate_target: The total work area cannot exceed 200 megabytes of RAM because of the default setting for the hidden parameter_pga_max_size.".

I wasn't aware of such a limitation.

Thanks for sharing your knowledge.

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

Subject: Re: Is there anyway a PGA limitation?
Author: Philip Wisse, Netherlands
Date: Aug 28, 2019, 17:10, 22 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Lauri,

There's no date in the article or version information but I believe the information is still valid if you manually set the PGA.

Personally I like the 'memory_target' (in which Oracle regulates the PGA dynamicaly) very much.

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

Subject: Re: Is there anyway a PGA limitation?
Author: Ales Kavsek, Slovenia
Date: Aug 29, 2019, 01:25, 22 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

to give you direct reply on your question from a subject?

> Is there anyway a PGA limitation?

Of course there is a limit and it is a good thing. Limits are good thing on any multi user (process) system.

Is it really 200MB? Can you check what it (_pga_max_size) is on your system? Is it 200MB? No? ;)

If you search the net you'll find "famous" Don vs. Jonathan "fight" on the subject, but here is some good (quick) intro, to make you understand that Oracle is more flexible than forcing 200MB (per process) to everyone, it all depends on other "numbers"...

https://fr.slideshare.net/Enkitec/fatkulin-hotsos-2014

Regards,
Ales

p.s.

btw. always check that article/blogpost has a timestamp, as Philip pointed out, if timestamp is missing, just skip the trash - it's not worth wasting your time, no matter how good the article looks.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re-Is there anyway a PGA limitation?
Author: Lauri, Netherlands
Date: Aug 29, 2019, 09:03, 22 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

Thanks for the answers.
Always interesting to start or follow a discussion on this site.

@Philip:
I agree with you, I also like more memory_target because the Oracle engine is managing the different memory buffers, and of course Oracle tuned this functionality/architecture.
But I inherit sometimes from systems from which it is different... and there might be (or not) reasons for it. It is takes time to find out if a given configuration is accurate or should be changed.

@Ales:
Maybe I didn't formulate my question clearly enough... of course I knew there was "limit" for PGA, but my mind was: will all sorts and hashes be done in memory if we increase as much a we can the PGA?
The PGA Target Advice shows that it is not the case.
Then I came across to this article, and indeed there was no publication/update date for it... so I was wondering if it was relevant.
So, this article teached something true.

But there is somthing I do not understand well.
suppose you wish to have most of sorts and hashes done in memory.
How would you achieve this? Furthermore, is it wise to do so? Does playing with DOP will tackle sorts and hashes?

Thanks ans 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: Is there anyway a PGA limitation?
Author: Philip Wisse, Netherlands
Date: Aug 29, 2019, 09:18, 22 days ago
Message: More reading:

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:47466211228419
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here