cancel
Showing results for 
Search instead for 
Did you mean: 

PlanViz - need to understand the tool - Overview Tab

Former Member
0 Kudos

Hello Gurus,

Indeed the tool is very useful, however I have few more queries to better understand the tool.

I would like to emphasize on Overview Tab first since this is designed to give us overview of the performance (This is what I think ).

I shall put across my queries on Executed Plan Tab later. I hope that is okay.

1) Time Section

a) What is it compiling? The object is already in activated state. Why is it taking 143 ms to compile? Does this time is included in Execution Time?

b) Execution time is 98.7 s. However, on fetch of 200 records of the same query it takes around 1 min to execute. What this execution time signifies?


2) Context Section


a) Why the system is mentioned here. This is not the actual system name.

b) Memory Allocation: Is this the dynamic memory allocated to each query when it is fired by the user for each execution?

3) Data Flow Section


a) Number of Tables Used: I am certainly NOT using 246 in my model. This is not the real count. Many temp tables which are created while execution of the query can be seen in 'Tables Used Tab'.

b) Maximum Rows Processed: I never tried counting the number records, however, will that soon. (Still would like to know .. to reduce my efforts .. )

4) Distribution Section


a) I have always seen the Number of Nodes as 1. Does it signify non-scaled out environment?

b) Number of Network Transfers is always 0. Is this a count of cross tenant requests?



Shirish.

Accepted Solutions (1)

Accepted Solutions (1)

lucas_oliveira
Advisor
Advisor
0 Kudos

Hello Shirish,

Let me try answering your questions:


1) Time Section

a) What is it compiling? The object is already in activated state. Why is it taking 143 ms to compile? Does this time is included in Execution Time?

That's the common preparation for the query. As you execute a query there's an optimization step that defines which parts of the kernel apis needs to be used depending on the optimization decisions. Only after the preparation this plan is actually executed.


b) Execution time is 98.7 s. However, on fetch of 200 records of the same query it takes around 1 min to execute. What this execution time signifies?

In your case, the execution took around a minute 98.7s (meaning preparing and actually executing the query). While the fetch (transfer result set from backend to front end) took 38ms.


2) Context Section

a) Why the system is mentioned here. This is not the actual system name.

I can't see your image but the system is ought be the same. I don't see a reason why this would be different.


b) Memory Allocation: Is this the dynamic memory allocated to each query when it is fired by the user for each execution?

Yes, this is counted dynamically and for each execution.


3) Data Flow Section

a) Number of Tables Used: I am certainly NOT using 246 in my model. This is not the real count. Many temp tables which are created while execution of the query can be seen in 'Tables Used Tab'.

Indeed it's not 'Number of persistent tables used'

So that counts temporary tables that were created during execution as well.


b) Maximum Rows Processed: I never tried counting the number records, however, will that soon. (Still would like to know .. to reduce my efforts ..  )

I agree that the term 'Rows' here is misleading. It can actually mean two things: actual vector of rowids (which could be naively translated to Rows) and vector of valueids from dictionary selection.

Please take a look at my old blog post as it comments on that as well:


4) Distribution Section

a) I have always seen the Number of Nodes as 1. Does it signify non-scaled out environment?

Hm, not really. It just means your query didn't need data from other nodes. If you had a scale-out scenario and your query requested data from more than one node, then definitely that number would have increased.


b) Number of Network Transfers is always 0. Is this a count of cross tenant requests?

Good question. Big chance it will  but need to be tested to confirm

Best Regards,

Lucas de Oliveira

Former Member
0 Kudos

Hi Lucas,

Apologies for the delayed reply. (I was on long vacation .. )

The explanation is certainly helpful. Still have some points doubts.

- Don't you think the Point 3 a is misleading!  How this information is useful to us (with the temp table counts) ? It should be the count of the tables used. In the overview section we should get overview of the performance.

- Point 4 a and b. I shall check that and confirm very soon.

Thanks and Regards,

Shirish.

lbreddemann
Active Contributor
0 Kudos

I'd say Point 3a is not misleading anymore, since now you know what it means. Also: I definitively would want to know if my model hits hundreds of temp tables.

Concerning points 4a+b: the network transfer and the no. of nodes relate to scale out scenarios only.

Answers (2)

Answers (2)

Former Member
0 Kudos

Hello Gurus,

  

Awaiting for the reply...

former_member200930
Participant
0 Kudos

This message was moderated.