forked from bobbydurrett/PythonDBAGraphs
-
Notifications
You must be signed in to change notification settings - Fork 0
/
README
187 lines (120 loc) · 5.29 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
# PythonDBAGraphs
This is a Python program that displays graphs that
are helpful for Oracle database performance tuning.
PythonDBAGraphs includes these graphs:
ashcpu.py - Shows cpu usage within an Oracle database
by various parts of the application.
onewait.py - Shows the average time for an Oracle wait
event and the number of events per period.
simplesqlstat.py - Show average elapsed versus executions
for one SQL statement.
allsql.py - Show average elapsed versus executions
for all SQL statements.
groupsigs.py - Show average elapsed versus executions
for SQL statements whose force matching
signature is in a specified group of
signatures.
sigscpuio.py - Show total elapsed, CPU, and IO time
for SQL statements whose force matching
signature is in a specified group of
signatures.
sigselapctcpu.py - Show total elapsed for SQL statements
whose force matching signature is in a
specified group of signatures. Show
percent CPU used on same graph.
sigfour.py - Four metrics for SQL statements
whose force matching signature is in a
specified group of signatures. These are
number of executions, average elapsed time,
CPU percent used, and average single block
read time.
ashcount.py - For a time range show the number of active
sessions on CPU and total number active.
sqlstatwithplans.py - Show execution time of a single
SQL statement by plan.
sessioncounts.py - Plot the number of connected sessions.
segstat.py - Segment usage statistics for one segment
ashonewait.py - Show the number of sessions active on one particular wait.
sqlstatcpuio.py - Plots total elapsed, cpu, and io seconds for a single sql_id.
iosummary.py - Shows overall I/O information for one database.
hostcpu.py - Shows host level CPU information for one instance
onesysstat.py - Shows a single system statistic for one instance
space.py - Shows overall tablespace usage over time
Command line help:
usage: ashcpu.py [-h] {file,screen} [database] {Y,N} {Y,N}
Database CPU by Application Area
positional arguments:
{file,screen} Where to send the graph
database Name of the database
{Y,N} Show SQL that was executed (Y or N)
{Y,N} Show data returned by query (Y or N)
optional arguments:
-h, --help show this help message and exit
See README for more detailed help.
Example:
python onewait.py screen ORCL N N
This creates the onewait.py graph on the ORCL database and sends the output to
the screen. It does not display the SQL used and it does not output the
data returned by the query.
Two other included scripts:
colorsquares.py - shows a range of colors. I used this to pick the colors
for the graphs.
Example:
python colorsquares.py r .5
Sets red at 0.5 and shows range of colors varying blue and green between 0.0 and 1.0.
show_saved.py - Displays a graph based on data saved from an earlier run.
Prompts for the name of the file that was previously saved.
Example:
python show_saved.py
Enter name of data file to be restored: D:\temp\All SQL statements on DBATEST2 database.txt
Requirements:
The author primarily uses this application in the following environment:
Windows 7
32 bit Python 3.6.1
These packages:
cx-Oracle (6.2.1)
cycler (0.10.0)
matplotlib (2.0.2)
numpy (1.12.1)
pip (9.0.1)
pyparsing (2.2.0)
python-dateutil (2.6.0)
pytz (2017.2)
setuptools (28.8.0)
six (1.10.0)
wxPython (4.0.0a2)
I had to do the following steps to configure Matplotlib to use wxPython:
Download s.py from this URL:
https://gist.github.com/bobbydurrett/a65c9c457f155a0ed38a9643b05ce8f5#file-s-py
Run this command:
python s.py --verbose-helpful
Output includes a line like this:
loaded rc file C:\Users\myuser\AppData\Local\Programs\Python\Python36-32\lib\site-packages\matplotlib\mpl-data\matplotlibrc
I edited the matplotlibrc to use wx by setting backend to WXAgg. Here is the diff from the original matplotlibrc:
diff matplotlibrc.original matplotlibrc
38c38
< backend : TkAgg
---
> backend : WXAgg
Configuration:
The program uses certain directories. The paths to these directories
are stored in the file directories.txt which is kept in the same
location as the util.py script. The directories are the configuration,
password, and output directories.
The configuration directory contains the file userandfilenames.txt
which has the Oracle user name and the name of the password file.
The password directory contains the password file. It has lines in this
format:
Database name:user name:password
The output directory is where image files are written if you choose
"file" as the destination for the graph.
You can test these scripts interactively through IDLE by setting the arguments
at the top of the script. So, if you want to test ashonewait.py put these lines
at the top of the file:
import sys
sys.argv = ['x','screen','YOURDB','N','N']
Once you are done testing remove these lines and run the script from the command line.
Related blog posts:
http://www.bobbydurrettdba.com/?s=Python
Contact: