How to create an excel pivot table.

Pivot table is very powerful, and very easy to use.

Whenever, our team members participate in the tuning project, we usaually use excel to manage the result of tuning. And pivot table is very useful, when we give a summary report to customers. Also,it’s very easy to use.
Below is the standard format for tuning report. It has a very detailed information, but it’s hard to see using the Module Code and Status.

 

 

 

 

 

So, customers want to see the statistic report through Module Code and Status. At a time like this, we use the pivot table.

1) select all cells
2) Menu-> Insert -> Pivot table
3) Locate Field to proper Area like this

4) It’s all over, Below is the result of pivot table.

Excel Macro for analyzing the v$sql_plan_statistics_all view.

In my previous article, I described how to get delta values from v$sql_plan_statistics_all view. And, I made the excel macro for more convenient tuning life. The macro provides delta values, as well as IO statistics about objects and operations. The sample output is as follows:
To use the macro, just input {TNSNAME, USREID, PASSWD, SQLID} then click “Click” button. If you want to use this macro, you can download from here.