Understanding EGDBHealth

[yockee]

Hi,

I am doing EGDBHealth check but I don't really understand some of the results. Couple of questions : 

From "Overview Context tab" pic :

1. There are 3 Criticals issues. They have different Queryelapsed times. For items 1 and 2 (AttachSizeSql and SqlSdeBintypeSize..) they have time < 100ms, why are the marked Critical ? Whereas the item #3 is Critical too with time > 100ms.

2. I find that QueryElapsedTime are italicized if they are >100ms. Why ? Is there any specific reason ?

From "Expert Context tab" pic :

3. Suppose that I do not want some parameters included in the examination of EGDBHealth. How do I omit those parameters because some of the evaluated parameters are OK ? 

4. Are there any specifics database parameters that directly associated with the values here ? 

I am using Oracle 12c and SDE 10.5

Thanks in advance

[yockee]

@DannyKrouk Maybe you can give me an advice... Many Thanks Danny. 🙂

[DannyKrouk_ps]

Hi Yockee,

I am pleased to try to help.  I'm not sure I'm tracking all of your statements/questions.

In the image of the EXPERT overview, there are many more than three findings marked as "critical"  The QueryElapsedMs has nothing to do with this classification.  If you follow the hyperlink in a row, it will show you the detailed observations associated with each finding.  The QueryElapsedMs in the overview is just to let you know how long it took to run the query to arrive at the finding.  Nothing more.

AttachSizeSql and SqlSdeBintypeSize are part of the CONTEXT Excel, not the EXPERT Excel.  They are marked in green because, in the event that there are detailed results (i.e. if there is a hyperlink), you will find Sql statements which you can execute, at your option, to understand more.  This is because executing all of the statements in this tool would be incredibly time consuming and, on average, not very productive.  But, having the statements, so you can execute them for a situation of interest to you, may have value.

When you say you do not want "some parameters included in the examination of EGDBHealth", I think you mean queries ... not parameters.  The answer is somewhere between "yes with a but" and "no with a maybe".  You can see all of the queries in the directory structure ... in plan text files (it might be pairs of texrt files ... it will be obvious when you look).  If you delete a query's text file(s), that query will not run.  However, on start-up, the application checks with ArcGIS Online to see if there are query updates.  When that happens, assuming you have an Internet connection, you will get all of the latest queries ... and this will undo your deletions of the text files by replacing them with fresh ones from ArcGIS Online.  There is not a way to disable this feature ... short of blocking your Internet connection at execution time (which would work just fine).

When you ask, "Are there any specifics database parameters that directly associated with the values here ?", I do not understand the question.  Would you try to restate it?  I'm not sure what "here" refers to, for example.

Looking forward to your replies ...

Thanks,

Danny

[yockee]

Hi,

Thanks for your kindness Danny.

For your answer about question 1-3, i get your message. Thank you very much. 🙂

For the last one, I need to supply more details. I meant, for all the values in column "Name", are there any corresponding database parameters that I can check ? eg, just in general : say "backups" or "statementsharing" are marked in color. How to see (prove, by running a query to certain oracle parameters / tables ) that "backups" or "statementsharing" are really causing the performance degradation ? After that, how can I fix that parameters / value in Oracle so that the "backups" or "statementsharing" are not considered as Critical ?

And one more thing. It is suggested that I should separate between SDE schema and User schema. How do I prove that such separation will increase performance ? Is there any such queries on Oracle that I can run to prove this ?

Some clients just want to see the prove with hard evidence or metric value. 🙂

Thanks again for your help Danny. 🙂
best regard

[DannyKrouk_ps · Accepted solution]

Hi Yockee,

I'm glad we're making progress.  On the last item, your #4 in your original email, when "Backups" (or anything else) shows up in the Name column, it does *not* indicate that backups are causing a performance problem.  Instead, it is indicating that there is *some kind of concern* with backups.  To find out the concern, you would follow the hyperlink to see the details.  Some themes (like statementsharing) mostly relate to performance (or scalability).  You can get a good sense of when this might be the case by the "category" column (to the left of the area captured in your screen capture).  There is, for example, a "Performance" category where all of the findings relate to performance.  

On your separate topic, "It is suggested that I should separate between SDE schema and User schema. How do I prove that such separation will increase performance?", I assume you mean that *someone* (not this tool) made that suggestion.  I think a good place to start is to ask that someone "why?" or "how does it increase performance?".  Maybe they don't know.  Or, maybe you don't have access to them.  So, you want to prove directly.  In principle, a time-measuring device on a client pointing to two systems whose only difference is this schema separation is all you need.  I do not know of a way to write a query on one of these system schema patterns that would tell you about its relative performance to another system schema pattern.  In general, I would be skeptical of this kind of advice.  Schemas are largely logical constructs.  Logical constructs do not have many direct tie-ins to performance.  Of course, there could be something that the recommender has in mind ... that isn't occurring to me.  That's why I would start by asking them why they hold this belief and what mechanisms they can point-to that would lead anyone else to hold that belief.

Thanks,

Danny