You need to understand the application and its data.
A recent request on OTN was for advice on making this piece of SQL run faster:
delete from toc_node_rel rel where not exists ( select * from toc_rel_meta meta where rel.rel_id = meta.rel_id ) ;
Here’s a very short list of questions to focus the mind on possible solutions. There is a column called rel_id in both tables; columns with “id” in the name tend to be a little bit special, so are these columns:
a) the primary key of one table and foreign key to the other (if so which way round)
b) the primary keys of both tables
c) both foreign keys to a shared primary key table
Until you know the answer to these questions you can’t really make progress in working out the best way to implement the requirement. And even when you have the answers that’s still only one more step in the right direction, and the precursor to the next set of questions – like “have the constraints actually been declared and enabled, are any foreign key constraints allowed to include nulls, are any primary key constraints enforced by non-unique indexes”, and we still haven’t got around to absolute data volumes, clustering patterns, and expected volume deleted.
You may want to argue about whether the possibilities listed about should or shouldn’t exist in a properly designed system. Feel free to do so; just because something is wrong in theory doesn’t stop it from happening in practice.
For a more concrete impression for the short list of questions:
For the PK/FK option (a) – imagine a very simple order processing model, are we trying to delete products for which there are no orders, or orders for which we don’t stock the product (which shouldn’t have got into the system anyway if we had implemented it properly).
For the PK/PK option (b) – imagine our simple order processing system has a seperate delivery table which clones the PK of the order table, are we trying to delete deliveries for which there is no order (again they should not exist, but who said this system had been designed and implemented well – see (a)).
For the shared FK option (c) – imagine a different order processing system that allows multiple order lines per order and clones the PK of the order line to produce a row in a deliveries table, are we trying to delete deliveries where there are no order lines for the corresponding order (yet another possibility from a badly designed and badly implemented system – but I’m sure I’m not the only one to have seen systems and code like this).
I started writing this post in early January this year and before it gets completely old and (maybe) uninteresting, I hereby just post it, wondering what you might think. Of course I wanted to be it the ultimate perfect braindump but I realize that I can’t get my head around all the issues anyway and
Here we go again – heading off for KScope12.
I’m sitting in the BA lounge at Heathrow waiting for the boarding call. 11 hours from now I land in Dallas, sit around for another three hours, then take off for San Anonio. It will be about 19:30 local time when I arrive, but more like 01:30 am tomorrow body-clock time. It’s great being at these events, but I hate getting to them.
If you’re going to be there, feel free to come and say hello – my mother always warned me about talking to strangers, but that doesn’t apply any more. I’m bringing a few (hand luggage only) copies of my book to give away, so you might even want to attend one of my presentations.
I was running through my demos for the OTN Tour of Central America and my laptop completely died!
I ran through the disk repair utility and it found (and fixed) a number of problems, but still the laptop wouldn’t boot. Time for drastic measures!
Next I started a full restore of the hard disk from Time Machine. I left it running over night and woke up this morning to find a fully functioning laptop.
I’m going to record all my live demos so if something bad happens during the tour I can present from a memory stick.
Not exactly what you want the week before you go away…
Every now and then I am asked about the availability of the presentations I have delivered. Recently somebody asked about a presentation I delivered at the OUG Scotland about multiblock reads, and I promised to make it available. I’ve now uploaded a PDF version of all my old presentations them and put them in the ‘Whitepapers and presentation’ section.
All the flights and hotels are booked, so it now feels very real. In a little over a week I will be taking part in the OTN Tour of Latin America.
I’m a little nervous because there are so many legs in such a short time. The tour lasts 13 days. In that time I’ll be taking 11 flights and visiting 6 locations.
I feel my stomach twisting as I look at the list. With so many locations in such a short time, the possibility of hiccups in transit seems rather high. Miss one connection and things get tricky…
I’m looking forward to meeting people, but I would be a liar if I said I’m happy about spending 2 weeks on a plane.
It’s been about 2 years since I switched across to VirtualBox (when the shared virtual disks feature was introduced). In that time there have been loads of updates to the product. In the same time frame, VMware Server has had zero releases. I still get a lot of people writing to me about issues with VMware Server installations. I immediately tell them to ditch it.
PS. I’ve got nothing against VMware’s paid-for offerings, which do get updates. I just don’t see the point in using them when VirtualBox is free and works great for me.
June 20, 2012 I have not had much time to respond in OTN threads recently, although I do still occasionally read threads on the forum. I was a little surprised by one of the late responses in one of the recent threads, where one of the responders suggested actually testing the problem with the assistance of [...]
As part of a recent project to remove a vulnerability in relation to CVE-2012-1675 it became apparent that there are certain misconceptions around dynamic and static listener registration which are hard to get rid of. The below is applicable for single instance Oracle only!
Now let’s start with a quiz: what does the following output imply:
$ lsnrctl status LSNRCTL for Linux: Version 18.104.22.168.0 - Production on 20-JUN-2012 11:22:01 Copyright (c) 1991, 2010, Oracle. All rights reserved. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for Linux: Version 22.214.171.124.0 - Production Start Date 15-JUN-2012 11:14:27 Uptime 5 days 0 hr. 7 min. 34 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/server1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571))) Services Summary... Service "ORA11202" has 1 instance(s). Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service... The command completed successfully
Yes, that’s right: the UNKNOWN means that there is no dynamic registration.
How does the listener register with the database?
If memory serves me right then Oracle introduced dynamic listener registration with 9i. This is the reason why there is no listener.ora file required in your $ORACLE_HOME/network/admin/ directory anymore. Simply start the listener and it will use the defaults, binding to port 1521. Of course, every attacker knows about that port number.
Any running database will try to register with the listener on port 1521-completely automatically. If the listener is instructed to start with a different port though, then there won’t be dynamic registration. That is, unless you set the local_listener parameter for a non-Oracle Restart/RAC environment where this is done for you by a HAS/CRS agent.
However, you technically don’t need to allow the database to register with the listener in single instance environments. As in the days before dynamic registration, you can statically register database services with the listener. Even today that is required for any process that cycles the database. As soon as the database is down, any dynamically registered service is removed from the listener. Examples for such processes are the data guard broker controlled switchover operation as well as RMAN duplication from active database. I’m sure there are others too but those are the two that come to mind.
The different methods to configure your listener.ora file
The most basic way to configure the listener is not to configure it at all. Change to $ORACLE_HOME/network/admin and move the listener.ore file out of the way. Then start the listener. However that’s not really recommended-as you will see on many posts on the web the fix to prevent illicit listener registration with a database is to enable the Class of Secure Transport (“COST”). This isn’t part of the defaults. Also, you might want to rethink the listener binding to port 1521. So let’s discard that idea for anything outside your own laptop play area.
A better way is to create a basic listener.ora file, either manually or via netca/netmgr. It will most likely look similar to this one:
LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)) (ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)) )
This basic variant now has to be amended with the SECURE_REGISTER_LISTENER parameter (at least). Please refer to My Oracle Support for more information about this parameter and the values it can take.
With the above listener configuration file the listener was instructed to listen on IPC, and the client-facing Ethernet card on port 1571. Now let’s see what effect that has:
$ lsnrctl start listener ... Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))) Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571))) Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)) STATUS of the LISTENER ------------------------ Alias listener Version TNSLSNR for Linux: Version 126.96.36.199.0 - Production Start Date 19-JUN-2012 19:46:36 Uptime 0 days 0 hr. 0 min. 2 sec Trace Level off Security ON: Local OS Authentication SNMP OFF Listener Parameter File /u01/app/oracle/product/11.2/dbhome_1/network/admin/listener.ora Listener Log File /u01/app/oracle/diag/tnslsnr/server1/listener/alert/log.xml Listening Endpoints Summary... (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571))) (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571))) The listener supports no services The command completed successfully
Note the line “the listener supports no services”. Since the listener doesn’t listen on port 1521, this will stay this way. Trying to connect to a database will only return the infamous “ORA-12514: TNS:listener does not currently know of service requested in connect descriptor”. Oh and yes, the tnsping will be successful!
To make the listener aware of the database service, you have two options:
Option 1 requires a change to the listener.ora to register your database. Consider this example:
LISTENER = (ADDRESS_LIST= (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)) (ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571))) # this is new SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=ORA11202) (SID_NAME=ORA11202) (ORACLE_HOME=/u01/app/oracle/product/11.2/dbhome_1/) ) ) SECURE_REGISTER_LISTENER = (IPC)
Restarting the listener now shows us the following service information:
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1571)) Services Summary... Service "ORA11202" has 1 instance(s). Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER The command completed successfully
Now here is one of the things I’d like you to take away from this post: statically registered databases will report a status of UNKNOWN. However you can connect to a statically registered database just fine without the listener knowing anything about it. Consider it as a blindfolded person on a train: the person doesn’t see what its doing, but will get there in the end regardless.
$ sqlplus a/b@server1:1571/ORA11202 SQL*Plus: Release 188.8.131.52.0 Production on Wed Jun 20 13:13:40 2012 Copyright (c) 1982, 2010, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied
To prove it, the counters in the listener output is incremented:
Services Summary... Service "ORA11202" has 1 instance(s). Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 LOCAL SERVER The command completed successfully
The second scenario involved telling the database’s pmon process how to connect to the listener. I’m using IPC here, all other registration attempts are blocked by the COST parameter:
SQL> alter system set local_listener='(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1571)))'; System altered. SQL> alter system register; System altered.
Keeping the static registration in the listener file, I get this:
Services Summary... Service "ORA11202" has 2 instance(s). Instance "ORA11202", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:1 refused:0 LOCAL SERVER Instance "ORA11202", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER Service "ORA11202XDB" has 1 instance(s). Instance "ORA11202", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:1022 state:ready DISPATCHER (ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=32637)) The command completed successfully
Looking at the output you can see that the same database is now registered a number of times. The top line, with status UNKNOWN is from the static registration.
The next lines are new, and result from the registration of the services with the listener, triggered by my “alter system register” command (I’m impatient, that registration would have happened eventually). The registration can also be seen in the listener.log:
19-JUN-2012 19:19:56 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=server1)(USER=oracle))(COMMAND=services)(ARGUMENTS=64)(SERVICE=listener)(VERSION=186647040)) * services * 0 2012-06-19 19:19:24.839000 +01:00 19-JUN-2012 19:19:24 * service_register * ORA11202 * 0 2012-06-19 19:19:43.319000 +01:00 19-JUN-2012 19:19:43 * service_update * ORA11202 * 0 2012-06-19 19:19:46.320000 +01:00
So now when you see an instance with status “READY”, you know that PMON communicates with the listener. An instance with status UNKNOWN is statically registered. Static registration is perfectly sufficient, and indeed necessary when you need to (re)start the database, or cycle the database while connected remotely such as during RMAN duplication or dgmgrl during switchover operations.