I am currently blogging from my new website which is at https://curiousdba.netlify.com/. I will see you there!
I am currently blogging from my new website which is at https://curiousdba.netlify.com/. I will see you there!
EDIT: I have an updated version of this page including some more information on my new blog.
I keep having to look up how this works, so thought I would make a note here.
First the systems are kept in sysadm.pspmsysdefn.
SQL> desc sysadm.pspmsysdefn Name Null? Type ------------------------------ -------- ---------------------- PM_SYSTEMID NOT NULL NUMBER(38) DBNAME NOT NULL VARCHAR2(8 CHAR) DESCR60 NOT NULL VARCHAR2(60 CHAR) PM_BUFF_INT NOT NULL NUMBER(38) PM_MAX_BUFF NOT NULL NUMBER(38) PM_SAMPLE_INT NOT NULL NUMBER(38) PM_PING_INT NOT NULL NUMBER(38) PM_MAX_HIST_AGE NOT NULL NUMBER(38) PM_ARCHIVE_MODE NOT NULL VARCHAR2(1 CHAR) PM_MAX_TRANS_TMOUT NOT NULL NUMBER(38) GUID NOT NULL VARCHAR2(36 CHAR) PM_USER_TRACE NOT NULL VARCHAR2(1 CHAR) PM_SAMPLING_RATE NOT NULL NUMBER(38) PM_FILTER_LEVEL3 NOT NULL VARCHAR2(2 CHAR) LASTUPDOPRID NOT NULL VARCHAR2(30 CHAR) LASTUPDDTTM TIMESTAMP(6)
Interesting columns are PM_SYSTEMID, which we will need later. DBNAME seems to come from the dbname in ps.psdbowner in the monitored system. The GUID comes from SYSADM.PSOPTIONS.GUID. It can be useful to reset this if you want a refreshed system to show up as the same in the monitor.
The next table is the agents. These are defined in SYSADM.PSPMAGENT.
SQL> desc sysadm.pspmagent Name Null? Type ----------------------------- -------- ---------------------------- PM_AGENTID NOT NULL NUMBER(10) PM_AGENT_TYPE NOT NULL VARCHAR2(32 CHAR) PM_DOMAIN_NAME NOT NULL VARCHAR2(32 CHAR) PM_DOMAIN_TYPE NOT NULL VARCHAR2(2 CHAR) PM_DOMAIN_DIR NOT NULL VARCHAR2(254 CHAR) PM_DOMAIN_MONITOR NOT NULL VARCHAR2(1 CHAR) PM_HOST_PORT NOT NULL VARCHAR2(64 CHAR) PM_INSTANCE NOT NULL VARCHAR2(32 CHAR) PM_SYSTEMID NOT NULL NUMBER(38) PM_AGENT_INACTIVE NOT NULL VARCHAR2(1 CHAR)
Important columns here are pm_systemid from the table above, and the agentid. The rest of the columns describe the agent. There is one agent per website, then one per domain, one per process in the apps server and process scheduler. In a production sized system I am looking at there are 100 agents.
To look at the agents from system 5, I used:
select * from sysadm.pspmagent where pm_systemid = 5;
The records being collected go to sysadm.pspmranscurr. Once they have been collected they are moved to pspmtranshist.
Name Null? Type -------------------- -------- ---------------------------- PM_INSTANCE_ID NOT NULL NUMBER(20) PM_TRANS_DEFN_SET NOT NULL NUMBER(38) PM_TRANS_DEFN_ID NOT NULL NUMBER(38) PM_AGENTID NOT NULL NUMBER(10) PM_TRANS_STATUS NOT NULL VARCHAR2(1 CHAR) OPRID NOT NULL VARCHAR2(30 CHAR) PM_PERF_TRACE NOT NULL VARCHAR2(30 CHAR) PM_CONTEXT_VALUE1 NOT NULL VARCHAR2(254 CHAR) PM_CONTEXT_VALUE2 NOT NULL VARCHAR2(254 CHAR) PM_CONTEXT_VALUE3 NOT NULL VARCHAR2(254 CHAR) PM_CONTEXTID_1 NOT NULL NUMBER(38) PM_CONTEXTID_2 NOT NULL NUMBER(38) PM_CONTEXTID_3 NOT NULL NUMBER(38) PM_PROCESS_ID NOT NULL NUMBER(38) PM_AGENT_STRT_DTTM TIMESTAMP(6) PM_MON_STRT_DTTM TIMESTAMP(6) PM_TRANS_DURATION NOT NULL NUMBER(21,3) PM_PARENT_INST_ID NOT NULL NUMBER(20) PM_TOP_INST_ID NOT NULL NUMBER(20) PM_METRIC_VALUE1 NOT NULL NUMBER(28,4) PM_METRIC_VALUE2 NOT NULL NUMBER(28,4) PM_METRIC_VALUE3 NOT NULL NUMBER(28,4) PM_METRIC_VALUE4 NOT NULL NUMBER(28,4) PM_METRIC_VALUE5 NOT NULL NUMBER(28,4) PM_METRIC_VALUE6 NOT NULL NUMBER(28,4) PM_METRIC_VALUE7 NOT NULL VARCHAR2(128 CHAR) PM_ADDTNL_DESCR CLOB
Most of these columns are interesting. Think of a transaction moving from the web server to the application server then to the database and back. At each point the time taken is recorded.
trans_defn_set is always 1 on my system, which means peopletools.
pm_trans_defn_id is a 3 digit number which gives an idea of how far into the stack the metric is being generated. So anything in the 100 range is in the web server, and anything in the 400 range is in the application server.
pm_agent_id can be translated to an agent name using the aforementioned sysadm.pspmagent. This also gives a lot of information as to where the wait is occurring.
pm_context_id 1-3 are the labels for the relevant pm_context_value fields. They are translated by the table sysadm.pspmcontextdefn.
pm_metric_id 1-7 performs a similar task for the pm_metric_value fields, and are translated using sysadm.pspmmetricdefn.
We can also see that some of the instances are related to each other by pm_instance_id, pm_parent_instance_id and pm_top_instance_id. We can use analytic functions to display these nicely as follows:
select ind, oprid, montime, dur, typ, pm_trans_label, hostport, label1, value1, label2, value2, value7, clabel1, cvalue1, clabel2, cvalue2, clabel3, cvalue3, descr from ( select LPAD('*', 2*level-1,'-')||to_char(level) as ind, x.* from ( select ta.oprid as oprid, to_char(ta.PM_MON_STRT_DTTM,'DD/MM/yy hh24:mi:ss') as montime, ta.PM_TRANS_DURATION as dur, ta.pm_trans_defn_id as typ, td.PM_TRANS_LABEL, ta.pm_top_inst_id as topid, ta.pm_instance_id as pm_instance_id, ta.PM_PARENT_INST_ID as PM_PARENT_INST_ID, a.pm_host_port as hostport, md1.PM_METRICLABEL as label1, ta.PM_METRIC_VALUE1 as value1, md2.PM_METRICLABEL as label2, pm_metric_value2 as value2, md7.PM_METRICLABEL as label7, pm_metric_value7 as value7, cd1.pm_context_label as clabel1, ta.PM_CONTEXT_VALUE1 as cvalue1, cd2.pm_context_label as clabel2, ta.PM_CONTEXT_VALUE2 as cvalue2, cd3.pm_context_label as clabel3, ta.PM_CONTEXT_VALUE3 as cvalue3, ta.PM_ADDTNL_DESCR as descr from sysadm.pspmtranshist ta, sysadm.PSPMAGENT a, sysadm.pspmcontextdefn cd1, sysadm.pspmcontextdefn cd2, sysadm.pspmcontextdefn cd3, sysadm.pspmtransdefn td, sysadm.PSPMMETRICDEFN md1, sysadm.PSPMMETRICDEFN md2, sysadm.PSPMMETRICDEFN md7 where a.PM_AGENTID = ta.PM_AGENTID and cd1.pm_contextid = ta.pm_contextid_1 and cd2.pm_contextid = ta.pm_contextid_2 and cd3.pm_contextid = ta.pm_contextid_3 and ta.pm_top_inst_id in ( select distinct pm_top_inst_id from sysadm.pspmtranshist where PM_MON_STRT_DTTM > to_timestamp('02-06-2016 16:00','dd-mm-yyyy hh24:mi') and PM_MON_STRT_DTTM < to_timestamp('02-06-2016 17:00','dd-mm-yyyy hh24:mi') and pm_trans_duration > 10000) and td.pm_trans_defn_id = ta.pm_trans_defn_id and md1.pm_metricid = td.pm_metricid_1 and md2.pm_metricid = td.pm_metricid_2 and md7.pm_metricid = td.pm_metricid_7 ) x start with x.pm_parent_inst_id = 0 connect by prior x.pm_instance_id = x.pm_parent_inst_id );
We have Oracle Business Intelligence Enterprise Edition running on Linux. I was asked to install lineage, but while it is supported to run on Linux, it appears the install is only supported on Windows. Of course we are not licensed to run it on Windows and Oracle would not allow us to install and copy across – you need to be licensed.
However with the help of someone from Oracle I found that you can install it on linux using the following procedure.
Download the ODI companion CD from oracle edelivery, and unzip the whole thing as an oracle home. Go to misc/biee-lineage and unzip odiobilineage.zip
For version 126.96.36.199 we found we had to apply patch 17008493. Follow the instructions in the readme, but install OPatch in the home, and apply the patch using:
$ opatch apply -jre /path/to/jre
The RPD needs to be merged with the lineage rpd. This is done by converting it to UDML and uploading. Create a temporary directory and copy in odi_repository_archive_11g.rpd from the ODI companion CD under misc/biee-lineage/artifacts/11g. Then run:
ORACLE_HOME=/path/to/middleware_home . $ORACLE_HOME/instances/$inst/bifoundation/OracleBIApplication/coreapplication/setup/bi-init.sh $ORACLE_HOME/Oracle_BI1/bifoundation/server/bin/nqudmlgen \ -P Admin123 \ -R ./odi_repository_archive_11g.rpd -O lineage.udml \ -N -Q
Now copy in the RPD file and merge in the lineage UDML using:
$ORACLE_HOME/Oracle_BI1/bifoundation/server/bin/nqudmlexec\ -P Password \ -I lineage.udml \ -B original.rpd \ -O updated.rpd ---------------lineage.udml--------------- ---------------Complete Success!!---------------
Where Password is the password of the rpd file. Copy the RPD file to windows, and update the connection pool as per Oracles lineage documentation.
Find the ORACLE_ODI_REPOSITORY. Edit the connection pool to match the ODI Work repository.
This completes thework with the RPD file. It can now be uploaded back to the server.
Now we need to copy the resources from the CD to the correct location on the server. Under the CD change to misc/biee-lineage/artifacts/images and copy the two images to $ORACLE_HOME/user_projects/domains/bifoundation_domain/servers/bi_server1/tmp/_WL_user/analytics_11.1.1/7dezjl/war/res
I did it manually. The manual process needs to be run on the server because the client version had missing libraries that are required.
Make sure that X is working (Run xclock to check) and run:
cd $ORACLE_HOME/instances/$instance/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalogmanager ./runcat.sh
This brings up the catalogue manager. Select “Open Catalog” from the File menu. Select offline, then browse to the catalogue location which is:
You should see your developers directories under here. Using the online catalogue also works. Click OK.
Extract the web catalog using file->Unarchive and select
Export the web catalog report using Tools->Create Report.
The report type should be analysis. Add the following fields to the report in the following order: “Owner”, “Folder”, “Name” ,”Subject Area” ,”Formula” ,”Table”, “Column”
Save the report
It should be possible to script this step as follows:
OBIEE_WEBCAT=SampleAppLite WIN_EXP_RPD_DIR="D:\tmp" OBIEE_WEBCAT_MGR=$OBIEE_INSTANCE_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalogmanager OBIEE_WEBCAT_BASE=$OBIEE_INSTANCE_HOME/instances/instance1/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obips1/catalog $OBIEE_WEBCAT_MGR/runcat.sh -cmd report \ -offline $OBIEE_WEBCAT_BASE/$OBIEE_WEBCAT \ -forceOutputFile $ODL_TMP_DIR/webcat_doc.txt \ -distinct \ -folder /shared \ -type Analysis "Owner" "Folder" "Name" "Subject Area" "Formula" "Table" "Column"bb
Open the RPD file in the OBIEE administration tool. Select Tools->Utilities. Select Repository Documentation and click execute. Save the file.
Gather the webcat extract file, and the RPD report into a directory. Create a file to refresh lineage that looks like this:
#Properties for ODI/OBIEE Lineage #Fri Dec 13 09:45:15 EST 2013 ODI_MASTER_USER=ODI_REPO_M ODI_MASTER_PASS=firtr33s ODI_MASTER_DRIVER=oracle.jdbc.OracleDriver ODI_MASTER_URL=jdbc\:oracle\:thin\:@palin\:1532\:BIPROD ODI_SUPERVISOR_USER=SUPERVISOR ODI_SUPERVISOR_PASS=firtr33s ODI_SECU_WORK_REP=WORKREPDEV OBIEE_VERSION=11g OBIEE_WEBCAT_EXPORT_FILE=/path/to/webcat.txt OBIEE_RPD_EXPORT_FILE=/path/to/repo.txt MAPPING_FILE=/path/to/MappingData.csv INSTALL_ODI_LINEAGE=no EXPORT_OBIEE_METADATA=no
Create the mapping data file. The layout for these is on the companion CD at:
The format is:
ORACLE_SID is the oracle_sid of the database (Well the TNS connection)
DB_USERID is the username within the database (Probably DEV_BIPLATFORM)
nnnn is the ODI Model ID. This can be derived by opening the ODI tool and connecting to the master and work repository. Select the designer tab on the left, and open the model. The model id is under version.
Now it should be possible to refresh lineage. The script needs to be run from the bin directory as it has relative paths. Also JAVA_HOME needs to be set.
JAVA_HOME=/usr/java cd $odi_companion/misc/biee-lineage/bin ./refreshlineage.sh -propertyFile=$LINDIR/lineage.properties \ -mappingFile=$LINDIR/MappingData.csv
All being well, lineage will be refreshed and everything will work. Presumably the catalogue extract and rpd file extract will need to be updated periodically as they change.
I like to do a healthcheck of my Oracle system on a quarterly basis. This is because the report is hard to generate, and it is the longest timespan I thought I could get away with Monthly would be better, but until I can automate the reports generation this would take too much time.
The reason for the report is to keep management informed about what we are doing. To ensure the system is healthy and to ensure we are applying best practices in maintaining the system. It also serves as a mechanism to alert management (and myself) to potential problems.
Management will not have time to read the report, so it is important to have a summary at the top giving an overview of the most important things that have happened over the last quarter, tasks in progress, risks and any other things you want them to see.
The full healthcheck should contain the following:
Service improvements – An overview of work done since the last report.
Patch levels – Have the latest critical patches been applied to all tiers, and if not why not.
Software levels. Is all software in premier support, and if not why not. When does premier support end, and what plans are there to upgrade.
Hardware – For each hardware tier, is the hardware sufficient to cope with the application. Break this down into CPU, I/O, disc space, Memory usage, and network usage. Graphs are good here to show trends during the day, week, month year and over time.
Performance – Ideally include some measure of user visible performance. Again graphs showing trends are useful here.
For the database attach an AWR report or similar showing top SQL for each metric. I also include the top hot objects and the SQL updating them. We found a number of tuning targets this way. Database I/O has caused problems for us in the past, so I graph redo generation. I create a graph of the growth of the top 10 application tables, system tables, lobs and indexes. Again over time this can point to trends and help ask the business whether the audit data from 8 years ago is really required. I point to documentation for non standard parameters and why they are set.
Also take the opportunity to compare against some of Oracles health check/recommended practices documents. There are likely to be several to choose from, for security, recommended performance configuration etc. Document any deviations from the document and the reason for it. Equally be wary of changing the application to match Oracles recommendation without thorough testing, or understanding the reason for the changes.
I find the health check is a very valuable process for everyone concerned. The document is a little tedious to write, but automating its creation too much would reduce its effectiveness.
Applying a patch to tuxedo I got:
"Must specify the correct TUXDIR"
I think the patch installed, but I like to have a success message. I ran the uninstall and investigated.
It happened because the tuxedo directory was copied from another server. Oracle has set up a mini registry which the upgrade tries to edit, and if it can not, this error appears. The following files need to be copied:
When tuxedo is installed, you create an ORACLE_HOME directory to contain this registry information. In my installation, this was the parent directory of the TUXDIR. It contains a registrry.xml file and a log directory. These should also be copied from the same install as the TUXDIR was copied from, then edit the registry.xml file to update the host name.
Then install the patch again:
rpreleasenote = SUSE LINUX Enterprise Server 10 (x86_64) x86 for AMD64, 64bits Tuxedo
portreleasenotes= SUSE LINUX Enterprise Server 10 (x86_64) x86 for AMD64, 64bits Tuxedo
Installing server and client files...
Enter owner for patch files:
Enter group for patch files:
The patch installation finished successfully.
When I run the last program on my laptop, pretty soon the fan starts whirring and it gets hot. On the raspberry pi the program uses all the CPU it can get, the CPU graph goes completely green. Another problem is that it will run differently on different CPUs. On a fast CPU it will run faster and on a slow CPU it will run slower. This is not a real problem for this program, but if it was a game it would be really annoying to play at 10 times the proper speed.
We should share the CPU nicely with other programs, and keep the program running at the correct speed. Pygame gives us a way to do this, using the clock object. Lets write another program to demonstrate this, and we can add a little interaction as well.
Looking at the program we wrote last time, we can strip away the parts that are special for that program and end up with a skeleton which we can build on for future projects. It looks like this:
import pygame, random, sys # You have to call this to make it work. pygame.init() # Set up some variables containing the screeen size sizeX=600 sizeY=600 # Start drawing from the middle of the window. x=sizeX/2 y=sizeY/2 # Set the starting colour colour = pygame.Color('#444444') # Create the pygame window window = pygame.display.set_mode([sizeX,sizeY]) # Create the clock object clock = pygame.time.Clock() # Put a name on the window. pygame.display.set_caption("Random art") # This will loop forever. while True: #-- Program loop goes here. # Wait for the next tick. clock.tick(40) pygame.display.flip() for event in pygame.event.get(): if event.type == pygame.QUIT: sys.exit()
I have added in the clock stuff. Just two lines. The first creates the clock object, and the second one makes the program wait. It makes the loop run no more than 40 times a second, which is plenty. This means that we shouldn’t make the loop run for more than 1/40th of a second. This seems like a terriby short amount of time, but processors are really quick these days. Even the raspberry pi with its relatively slow CPU runs about 1,000,000,000 (One thousand million) instructions per second, which, if we divide that by 40 gives us 25,000,000 (twenty five million instructions) for each time the loop runs. Of course this is talking about a machine code instruction, a python instruction takes a lot of machine code instructions. Even so, we will not need this many!
This time I decided to make a spirograph. Wikipedia shows us the equations we need. Don’t worry, I have typed them in below. We need a counter, which we should initialise to zero at the start of the loop. We will also create random values for k and l, which should be between 0 and 1. These determine the shape of the spirograph pattern. We should pick a value for R which makes the spirograph fill the screen.
k=random.random() l=random.random() i=0 R=600
random.random() gives a random number between 0 and 1, which is exactly what we want.
The equations which need to go in the loop are taken from wikipedia:
newx = R*( (1-k)*math.cos(t) + l*k*math.cos((1-k)*t/k) ) newy = R*( (1-k)*math.sin(t) - l*k*math.sin((1-k)*t/k) )
Note that we are using the math module, so we need to add that to the import line at the start. When we look at the documentation for math, we see that math.sin and math.cos take an angle in radians. There are pi (3.14) radians in a circle, but 360 degrees. It would look better if we counted in degrees. The math module can convert degrees to radians for us. If we use i for the counter, we can create t as follows:
t = math.radians(i)
This goes above the lines that calculate x and y above.
Then we need to draw the line, and set up x and y as before. We should set x and y to 0 at the start of the loop and not draw if they are both zero. This means the first time a line is drawn is on the second time through the loop, when we have two points to draw a line between. Otherwise we will get a line from the top left going to the start which will look ugly.
if x==0 and y==0: pass else: pygame.draw.line(window,colour,(x,y),(newx,newy),2) x=newx y=newy
The double equals sign means check if it is equal to. The single equal sign means assign. These are two separate things and it is easy to get them confused. Fortunately python will not allow an assignment in an if statement, so it reminds you if you forget (Try it!)
And of course we must not forget to increment our counter.
The more you increment i by, the faster the line will draw, but if you increment it by too much it will start to look jagged.
If you managed to keep up, try running the program now. You will get a quarter of a spirograph like the picture on the right. This is because our window only shows values where x and y are both greater than zero.
To fix this is actually quite easy. First we should shrink the image so it can fit in the window. Change R from 600 to 300. Then in the draw line we just need to add 300 to each value like this:
This looks much better. A whole spirograph.
Next time it will be time to start adding some interaction into our program. It is more fun if you can make it do stuff!
That was quite complicated. In case you couldn’t follow everything above, here is the whole program.
Let me know if you think of a way to make my explanation clearer. Also there must be a way to make the lines eventually join up like a real spirograph. If you know what it is, leave a comment!
import pygame, random, sys, math # You have to call this to make it work. pygame.init() # Set up some variables containing the screeen size sizeX=600 sizeY=600 # Set the starting colour colour = pygame.Color('#009900') # Create the pygame window window = pygame.display.set_mode([sizeX,sizeY]) # Create the clock object clock = pygame.time.Clock() # Put a name on the window. pygame.display.set_caption("Spirograph") # Initialise more variables # k and l are numbers between 0 and 1. # k is the ratio of the distance of the small circle from the big circle l=random.random() # l is the ratio of the small circles radius (to the hole with the pen in) # to the distance from the centre of the large circle. k=random.random() # Counter - real number - not integer. i=0.0 # Scaling factor (Radius of big circle) R=300 x=0 y=0 # This will loop forever. while True: t = math.radians(i) newx = R * ((1-k) * math.cos(t) + l*k*math.cos((1-k) * t / k )) newy = R * ((1-k) * math.sin(t) - l*k*math.sin((1-k) * t / k )) if (x==0 and y==0): pass else: pygame.draw.line(window,colour,(x+R,y+R),(newx+R,newy+R),2) x=newx y=newy i=i+5 clock.tick(40) pygame.display.flip() for event in pygame.event.get(): if event.type == pygame.QUIT: sys.exit() elif event.type == pygame.KEYDOWN: if event.key == pygame.K_ESCAPE: sys.exit() sys.exit()
I was wondering why has there not been a computer like the Raspberry Pi till now. The technology was there, so why didn’t someone put it together till now?
Companies exist to make money. They sell smart phones for £500. They do not sell computers for £30. How do you make money like that?
So it took some inspired public minded people to create the computer. But what about the software? You can’t install Microsoft Windows 7 on an ARM chip. Anyway, how much would Microsoft be able to charge for it? What they needed is an operating system that already worked on an ARM chip. Ideally one that was being given away for free. Fortunately there are some inspired public minded people called Debian who make a complete operating system, together with the programs you can run on it.
At the moment, the two have only just come together. This means there are some gaps where they join. Some work still need to be done to get them working well together. People will do this work and give it away. Over the next few months
Debian only exists because of other groups of public minded people wrote software and gave it away for free. In itself, that would not be useful, because programs that run on desktops will not work on ARM chips. You need the source code.
In the 1980s a man called Richard Stallman created an organisation called the Free Software Foundation. Their goal was to create a complete operating system, and give it away complete with the source. This was almost done. They even had a name for it – GNU, but they were having one problem. It was the kernel, the central part of the operating system which talks to the hardware. Amazingly another public minded individual had created something of a group around a kernel he was writing. He was Linus Torvalds, and the kernel was called Linux.
So we have the Raspberry Pi foundation providing the computer. Linus Torvalds and his group provided the kernel. The free software foundation provided the GNU operating system, MIT providing the windows system, thousands of other people writing programs, and Debian putting them all together.
We owe these people a debt of gratitude for what they have done for us, particularly Richard Stallman for his vision to write software, and give it away under the condition that anyone who receives it and passes it on, also passes on all the rights they got. Some people call it share and share alike.
I hope this inspires you to give something back. When you write programs, put them under a free software license. Consider the GNU Affero General Public License – but read it first, so you see what it means.