OraFAQ Forum: Performance Tuning » Data dictionary Cache - dc_objects

Home » RDBMS Server » Performance Tuning » Data dictionary Cache - dc_objects (Oracle 10g)

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

Data dictionary Cache - dc_objects [message #514426]

Mon, 04 July 2011 12:55

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Hi,
In order to improve the performance of our live server, I am trying to do an exhaustive comparison with our test envrioment which is quite quick in spite of the fact that we port the data from Live every month.

There are no obviously slow queries appearing in the the top SQLs of AWR, we have optimised such things already. Right now it is about general uplift rather than SQL based tuning.

I picked up random SQLs and I noticed a marked differences in the execution time. Typically they are 3 to 4 times and there are cases much more than that.

1. I observed that, while the explain plan of the queries are same, trace of the queries give a different picture. I have observed that the recursive calls, consistent gets and sorts(memory) are quite high on Live.
2. I have no solid reasons to say this but my instincts tell me that the recursive calls is the major contributing factor. It is sometimes 2000+ for an SQL.
3. On googling more on that, it finally made me compare the data dictionary on the AWR report of test and Live.

The dc_objects caught my eyes. In that 4 hour AWR, there were about 10 million get requests and the pct miss was ~10. For similar load, the test server had 5 million gets with 0.08 PCT miss for 4 hours.

Have I really caught atleast one of the spoilers? If yes, what is next?

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514427 is a reply to message #514426]

Mon, 04 July 2011 13:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Why would test server have "similar load" to Production?
What produces this "similar load"?

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514429 is a reply to message #514427]

Mon, 04 July 2011 13:11

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

The whole intent of having that particular test server is to test non-functionals. We keep deploying all the code to this instance to ensure that the changes have not had a big impact on the performance.

As far as data, we keep importing the data from production so that the volumes are similar. Everytime we import, we delete the schema, recreate it and re-import it from production.

The similar load is created by a simulation mechanisim. We make the instance deal with various operations (quantity and mixture) which is typically dealt by the production server

[Updated on: Mon, 04 July 2011 13:16]

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514433 is a reply to message #514429]

Mon, 04 July 2011 13:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>1. I observed that, while the explain plan of the queries are same, trace of the queries give a different picture.
I, for one, would like to see results from TKPROF from both for myself.

do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;

are both TEST & PROD on exact same h/w, OS & Oracle version?

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514434 is a reply to message #514429]

Mon, 04 July 2011 13:23

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

I thought this will be another useful info. The dc_object_ids also seems to be quite different. In that same 4 hours, the live touched 130 million get requests with PCT miss of 0.49. On test server, it touched 51 million gets and PCT miss says 0.00!

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514436 is a reply to message #514434]

Mon, 04 July 2011 13:32

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Hi Blackswan,

The trace results for one of the SQL. On recursive calls count is not always constant.

PRODUCTION:
recursive calls 791
db block gets 0
consistent gets 18480
physical reads 2456
redo size 232
bytes sent via SQL*Net to client 953
bytes received via SQL*Net from client 1022
SQL*Net roundtrips to/from client 2
sorts (memory) 24
sorts (disk) 0

TEST:
recursive calls 1
db block gets 0
consistent gets 15626
physical reads 0
redo size 116
bytes sent via SQL*Net to client 948
bytes received via SQL*Net from client 1022
SQL*Net roundtrips to/from client 2
sorts (memory) 1
sorts (disk) 0

The version from v$version is same for both instances.

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
"CORE 10.2.0.3.0 Production"
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

The hardware and OS are same. Even if different, Production got to be of better spec.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514488 is a reply to message #514436]

Tue, 05 July 2011 02:57

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

I'll have a go at this.
Looking at those trace results you posted (and, by the way, it would be much better to have the results of the tkprof output, formatted correctly with code tags) it seems unlikely that the execution plans for the statements are the same, because of the sort figures. I would check that first.
Can you post the AWR reports?
I wouldn't worry about your recursive call or db_object figures: they are to do with parsing rather than execution.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514538 is a reply to message #514488]

Tue, 05 July 2011 05:52

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Hi John,
Thanks. I have attached the AWR in PDF format. Please note that the SQL that I used is a random one and does not appear in the AWR. Infact AWR SQL section is able to account for just 40-50% of total DB time.

The TKPROF for that SQL, I will get it sometime in the evening.

[Attachment was removed on request. We have found a SQL table structure of one of our applications that has been uploaded to your forums and we'd like to request it to be taken down.]

[Updated on: Thu, 27 February 2020 14:07] by Moderator

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514578 is a reply to message #514538]

Tue, 05 July 2011 08:10

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

How can you compare performance between two different databases if you don't have the trace and the AWR report from each database? And why investigate a SQL that doesn't appear as a high-load statement in the AWR report?

[update:
incidentally, the report in PDF format is much harder to use than the original HTML format, because in the conversion you lose all the links for navigating within it. Also, a report covering such a laong time is not always as useful as reports over a more focussed period]

[Updated on: Tue, 05 July 2011 08:14]

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514607 is a reply to message #514578]

Tue, 05 July 2011 09:39

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I checked briefly the awr report. In my opinion there are 3 issues there: 2 sql's (s. the top 2 in the section "SQL odered by Gets", some other sql's from this section, which aren't so bad but have a lot of executions, parsing issue (a lot of hard parse calls, 139 per sec., perhaps because of cursor_sharing = similar, that can cause a lot of child cursors). The first 2 issues cause 3 top wait events. The third one stresses the shared pool and is, at least at the moment, more a potential problem. SQL Tuning should help you. Think about cursor_sharing=force too.
Regards
Leonid

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514608 is a reply to message #514607]

Tue, 05 July 2011 09:48

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please upload an awr report from another database.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514612 is a reply to message #514608]

Tue, 05 July 2011 10:02

jet_life
Messages: 1
Registered: July 2011

Junior Member

good

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514654 is a reply to message #514612]

Tue, 05 July 2011 12:47

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

do as below so we can know complete Oracle version & OS name.

Post via COPY & PASTE complete results of
SELECT * from v$version;

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514657 is a reply to message #514654]

Tue, 05 July 2011 13:29

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

LNossov,
Thanks a lot. Among the 3 issues you pointed out, here is what I feel.
1. The top 2 SQLs in the "ordered by Gets" section. Yes it is high but the executions is very less. It does affect certain operations but those operations are not performance sensitive. They can afford to be slow. Is it possible that these SQL have such a significant impact on the other SQLs?
2. Other SQLs in that section: This is where I am focussing right now. Though the elapsed/exec is not bad, the number of executions is huge and there is no way to control it. These SQLs are involved in operations which are performance sensitive. I had selected few random SQLs that fall under that category which I had used in my earlier posts.
3. Parsing issue, cursor_sharing=SIMILAR: Thanks for this, I dont know what implication it has by changing it to FORCE but I will check that.

There is a test going on in the Test instance and I will post the latest AWR tomorrow.

Blackswan,
I did paste the results from v$version in my earlier posts. I am not sure what is "via COPY & PASTE". Is it a different way of doing what i did? Sorry I am new to this forum.
BTW, I could not get the TKPROF today. Will do that tomorrow.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514660 is a reply to message #514657]

Tue, 05 July 2011 13:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> CREATE PFILE FROM SPFILE;

do above on both DBs then post content from both pfiles back here

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #514665 is a reply to message #514657]

Tue, 05 July 2011 14:35

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

You can get some performance issues, if you switch to force. But you can also win and in each case you will reduce hard parsing. With this simple sql you can check, whether the likelihood of performance degradation after switching to force is big or not:

select * from (select sql_id,count(distinct plan_hash_value) from v$sql group by sql_id having count(distinct plan_hash_value) > 1 order by 2 desc) where rownum <= 30;

If you see in the output sql's with a lot of distinct plan_hash_values, you can get problems. You can run this sql several times a day and then make a decision regarding cursor_sharing=force.

P.S.
The next 2 days I'll be on a business trip and will not be able to check your data.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #515476 is a reply to message #514657]

Mon, 11 July 2011 15:44

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Hello,

could you please give a short feedback. Did you test cursor_sharing=force?

Regards
Leonid

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #515643 is a reply to message #515476]

Tue, 12 July 2011 10:42

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Hi,
I am sorry I got busy with other things.

Blackswan,
I am sorry, I do not have the require permissions to create the pfile. It is tough to get that done.

Leonid,
I have not tried cursor_sharing=force because there are not many distinct hash plans. I fired that query you sent and the maximum is just 2.

I think the reason for hard parses is because the queries quickly go out of the shared memory. It is supported by the fact that there are a few queries which are having excessing gets per execution. Does it sound reasonable?

Coming to the difference between test and production, that sample query that I used is quite optimised but still on Production there is a good amount of disk reads compared to test. Somehow the other queries do no involve as much gets on test compared to Production.

So I am under the belief that it is the memory that is driving this situation. Any other opinions/suggestions welcome.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #515648 is a reply to message #515643]

Tue, 12 July 2011 11:41

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

If you have only 2 different plan_hash_value, it is very good for cursor_sharing=force. You misunderstood me or I was not so precise. So you can try cursor_sharing=force.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #515652 is a reply to message #515648]

Tue, 12 July 2011 12:40

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Oh Thanks Leonid, yes I think I misunderstood.

However one thing is, even on the test instance the cursor_sharing is set as SIMILAR and it is doing fine in spite of that. I am wondering if there other problems to solve.

Now if I make it FORCE and if it does improve things, all the other issues might just hide behind it. Or is there any valid reason why it is better to do it upfront?

All said, that was a good suggestion. I am definitely keeping it as a 'bonus improvement' once the other issues are cracked.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #515653 is a reply to message #515652]

Tue, 12 July 2011 12:50

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Do you have the same or a similar load on your test instance?

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #515708 is a reply to message #515652]

Wed, 13 July 2011 00:44

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Could you please run this select on your test and on your productive db:

select * from (select sql_id, count(*) from v$sql group by sql_id order by 2 desc) where rownum <= 10;

and upload the output.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #516047 is a reply to message #515708]

Thu, 14 July 2011 14:32

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Hi Leonid,
Between the test and production the load is similar but not same. But the data is ported every quarter.

Here is the Output of your query on both the instances


Production
select * from (select sql_id, count(*) from v$sql group by sql_id order by 2 desc) where rownum <= 10;

78929rkr3xrk2	742
8u1c0csm9ungb	708
ayyfcu5tq5zkg	682
dwn86jd84utjx	305
6avvw8asjb9ax	98
4dsp3ywqhf3g6	60
f6wpyw36cg5ju	54
gn2t2up306aw7	54
b2aappvv6bq03	51
bgfaxhjns122j	50

Test

a2c3t8dn7grjq	10611
g8cyg3tcuq0w2	10259
8ta6f63nx15vg	2880
7uhf9jhwpkxrm	1518
bgfaxhjns122j	1027
4dsp3ywqhf3g6	915
78929rkr3xrk2	879
9pmhr3546tauq	578
aswapt0tw3z3a	564
ctg9w5pu3a5gr	370

The list is not quite the same. Does it mean anything?

Also, the test environment is not continuously loaded. Rather we perform short bursts of 4 hour tests very often. Thought this will be a useful info.

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #516056 is a reply to message #516047]

Thu, 14 July 2011 16:28

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Perhaps the shared pool on your test db is oversized. And you didn't reboot this db for a long time. More interesting is the output from you productive db. I expected more child cursors because of cursor_sharing=similar and a lot of hard parse calls. Could you please run this sql:

select * 
from (select force_matching_signature,count(*) 
      from v$sql 
      where force_matching_signature != 0
      group by force_matching_signature order by 2 desc) 
where rownum <= 20;

[Updated on: Fri, 15 July 2011 01:47] by Moderator

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #516097 is a reply to message #516056]

Fri, 15 July 2011 01:47

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please format your queries and keep the line in 80 character width.

Regards
Michel

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #516218 is a reply to message #516097]

Fri, 15 July 2011 09:18

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Here we go.

Production

13538560611095646572	686
5686285623528052776	660
3963471950201271833	603
16430815236239810223	73
1006753777704410454	73
5717914418817762633	72
2003651678758327531	66
9606228031060786045	64
14006309773160407617	53
8823075710437569136	49
10683035948728812829	47
7947578235475271023	47
13621473836274713419	46
4393867981865795335	45

Test

8970288148961516006	11995
6389706818097714773	10675
17430978292642593113	6831
10294021585473036827	5534
3963471950201271833	4579
7707441724535347350	3893
16430815236239810223	2675
5131218936142803999	2432
2243427686041214272	2427
2221296541622703466	2329
1204351102706532171	1631
2384314941147951393	1396
7455902805350057873	1053
2077670388113894700	969
14198402827488452171	968
15817261520499778564	905
18420527874888044850	662
9752173232039910030	431
6419582721260746124	383
3188431314473538581	356

Let me know what makes you say that the shared pool is oversized in test. Can it be that the shared pool on Production is less?

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #516262 is a reply to message #516218]

Fri, 15 July 2011 14:46

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

May be cursor_sharing=similar isn't a main reason for a big number of hard parse calls on your system. In this case we should have seen a lot of child cursors. But it isn't a case for the most sql's (s. your first output).

So I supposed then, there are some sql's with literals, that cause a hard parsing. May be some sessions use cursor_sharing=exact or these sql's are recursive, I thought. This was a reason, why I asked you to upload the second output. But I could not find a lot of cursors with the same signature in this output. It means, there are a lot of sql's with totally different sqltexts. May be some of them will be generated in your programm. Can you confirm this? In this case it doesn't matter, if these sql's involve literals or bind variables. Oracle has to make a hard parse call, because the sqltext differs from others. Perhaps cursor_sharing = similar makes this situation worse.

It is difficult to say, whether you definitely get improvement with cursor_sharing=force or not, because the child cursor lists are relatively short and we didn't investigate yet, if cursor_sharing=similar is a reason for not sharing the cursors. We would have to check v$sql_shared_cursor for that for some sql_id's.

You asked about oversizing of the shared pool in your test system. I think so, because there are lot of child cursors there (in contrast to your productive system). I suppose, the load in the shared pool is not so big as in the productive system. The size of shared pool allows the cursors to stay there for a long time without flushing out.

[Updated on: Sat, 16 July 2011 00:38]

Report message to a moderator

Re: Data dictionary Cache - dc_objects [message #516288 is a reply to message #516218]

Sat, 16 July 2011 00:49

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Do you have a lot of hard parsing on your productive system permanently? Is it possible, that you got these outputs in a quiet period of time without heavy hard parsing?

Report message to a moderator

Previous Topic:	Precise I3 vs. OEM Grid
Next Topic:	How can i force a sql use index range scan

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 21:40:26 CST 2024