OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Hard parse in RAC (10.2.0.4)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Hard parse in RAC [message #417320]

Fri, 07 August 2009 03:28

serenaender
Messages: 4
Registered: August 2009

Junior Member

(excuse my english, please)

Hi,

I have tested with AWR and statspack reports and I am confused about hard parsed levels observed in RAC enviroments.

Statspack single instance 9.2.0.6

Elapsed: 37.63 min
Transactions per second: 2.72
Parse count (hard): 6,226
SQL Area Get requests: 227,300
SQL Area Get Pct Miss: 2.2%
SQL Area Reloads: 1,279

With this metrics i can calculate SQL Area Get Miss:

SQL Area Get Miss = 227,300 * (2.2/100) = 5000.6

If I add the reloads to the calculated value:

calculated hard parse = 5000.6 + 1,279 = 6,279.6

As we can see this value is very similar to the parse count (hard) value. This relation between get requests, reloads and hard parse appears always in statspacks obtained from single instance databases and it confirms that hard parse always occurs when the lock it can't be placed in the cursor handle (get request) and when the execution plan isn't found (reload).

However this relationship doesn't occur when the database is a RAC.

AWR Two nodes RAC 10.2.0.4

Node 1:

Parse count (hard): 12,522
SQL Area Get requests: 143,738
SQL Area Get Pct Miss: 17.10%
SQL Area Reloads: 206

calculated hard parse: 24,785

The hard parse expected is 1.98 greater:

hard parse expected to real ratio = 24,785 / 12,522 = 1.98

Node 2:

Parse count (hard): 16,514
SQL Area Get requests: 180,836
SQL Area Get Pct Miss: 16.91%
SQL Area Reloads: 1,589

calculated hard parse: 32,168
hard parse expected to real ratio: 1,95

AWR Four Nodes RAC 10.2.0.4

Node 1:

Parse count (hard): 882
SQL Area Get requests: 21,782
SQL Area Get Pct Miss: 65.67%
SQL Area Reloads: 305

calculated hard parse: 14,609
hard parse expected to real ratio: 16,56

Node 2:

Parse count (hard): 380
SQL Area Get requests: 18,545
SQL Area Get Pct Miss: 65.63%
SQL Area Reloads: 2

calculated hard parse: 12,173
hard parse expected to real ratio: 32,03

Node 3:

Parse count (hard): 1,863
SQL Area Get requests: 25,159
SQL Area Get Pct Miss: 57.22%
SQL Area Reloads: 1,514

calculated hard parse: 15,910
hard parse expected to real ratio: 8.5

Node 4:

Parse count (hard): 372
SQL Area Get requests: 17,121
SQL Area Get Pct Miss: 73.10%
SQL Area Reloads: 127

calculated hard parse: 12,642
hard parse expected to real ratio: 33.98

AWR Two Nodes RAC 10.2.0.4 Only 1 node active

Parse count (hard): 1,176
SQL Area Get requests: 9,317
SQL Area Get Pct Miss: 18.69%
SQL Area Reloads: 353

calculated hard parse: 2,094
hard parse expected to real ratio: 1.78

I'm going crazy understanding this results. When the database is a RAC the hard parse real is less than the hard parse expected, and the ratio is curiously proportional to 8 (8, 16, 32) in the 4 nodes environment, and to 2 in the two nodes environment.

However this also occurs in a two nodes environment with only one active node.

Does anybody know to translate this results?

Thanks!!

Report message to a moderator

Previous Topic:	Need help to tune the query
Next Topic:	Parallelism disabled in data warehouse

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 01:02:51 CDT 2024