Classic AskTom Post

Everyone should read this one at least once. Yes even you. The question and his initial answer, at least - AskTom's "perfect example" post

Logging DDL in Oracle

I could have sworn I had drilled into my head (from studying for the Oracle 10g OCA exam) that DDL is audited in the alert log. I had a need for this today but it wasn't showing up.

Turns out, DDL in the alert log is a new feature in 11g, and to enable it you'll want to run:

ALTER SYSTEM SET enable_ddl_logging=TRUE SCOPE=BOTH;

For older versions you'll have to audit another way. One way that worked for my purposes today is a simple DDL trigger: http://www.dba-oracle.com/t_ddl_triggers.htm

Pgpool-II Load Balancing

I've been trying, so far unsuccessfully, to get load balancing working in a pgpool-II replication environment with two nodes. This is supposed to spread SELECT queries across multiple nodes, according to the nodes' values of backend_weight. Presumably it does so in a round-robin manner, and if the two nodes had the same weight, query 1 would go to one node, query 2 would go to the other node, query 3 would go back to the first node, and so on...However, my queries are all being sent to one node.

This table, taken from the pgpool-II docs, shows whether a SELECT query will replicate, be sent to master only, or be load balanced based on three conditions:

SELECT is inside a transaction block	Y	Y	Y	N	N	N	Y	N
replicate_select is true	Y	Y	N	N	Y	Y	N	N
load_balance_mode is true	Y	N	N	N	Y	N	Y	Y
results(R:replication, M: send only to master, L: load balance)	R	R	M	M	R	R	M	L

It's absolutely clear that I have replicate_select set to false and load_balance_mode set to true. I seem to be getting a result of "M", which must mean my SELECT is in a transaction block. It doesn't appear to be though, acc. to my pg logs - at least, I don't see any BEGIN before the SELECT. Hmm!

If I fiddle with my two values of backend_weight and restart pgpool, it will switch to the node with the greater weight. Doesn't help me much, but it shows it's doing...something.

Stay tuned...