[FUN] How to tune select count(*) from t?

A user mentioned one call is faster in test than in prod. And the table has the same size. How could we tune the production to make it quick? user007@PROD> select count(*) from t; COUNT(*) ———- 19832501 Elapsed: 00:03:05.00 Let’s try in test : user007@TEST> select count(*) from t select count(*) from t * ERROR […]

What’s your favorite shell in Windows?

I just wrote one of my first powershell script yesterday, it has a pretty nice syntax actually, and no need to download anything like cygwin or other unix-like shell to your PC. PS> $stmt = “set hea off`n” PS> $stmt += “select ‘hello world’ from dual;” PS> $res = ($stmt | sqlplus -s scott/tiger) PS> […]

SQL developer for dba

Historically and semantically, SQL Developer is a developer tool. There is very little comparison with TOAD for the dba. But… Oracle is working on this! In the latest release, 3.0 EA1, you will be able to see Tablespaces, Redo logs and controlfile, finally. Still no session browser, but I am delighted that the database administrators […]

jdbc ssl

I already wrote about jdbc hello world and listener with tcps. Let’s combine both technologies ! TCPS.java import java.util.Properties; import java.security.Security; import java.sql.*; import javax.net.ssl.*; public class TCPS { public static void main(String argv[]) throws SQLException { String url = “jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(Host=dbsrv001)(Port=12345))(CONNECT_DATA=(SID=DB01)))”; Properties props = new Properties(); props.setProperty(“user”, “scott”); props.setProperty(“password”, “tiger”); props.setProperty(“javax.net.ssl.trustStore”,”cwallet.sso”); props.setProperty(“javax.net.ssl.trustStoreType”,”SSO”); Security.addProvider(new oracle.security.pki.OraclePKIProvider()); DriverManager.registerDriver(new […]

How to check if I have a pending transaction?

Pretty straightforward, check if dbms_transaction.step_id is null! SQL> select dbms_transaction.step_id from dual; STEP_ID ————— SQL> insert into t values (1); 1 row created. SQL> select dbms_transaction.step_id from dual; STEP_ID ————— 114352430549782 SQL> commit; Commit complete. SQL> select dbms_transaction.step_id from dual; STEP_ID ————— SQL> insert into t values (2); 1 row created. SQL> select dbms_transaction.step_id from […]

on my bookshelf

I just started a new job as a dba and placed some books on my desk. Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars SQL Design Patterns by Vadim Tropashko Beginning Oracle Database 11g Administration by Iggy Fernandez Expert Oracle Database Architecture by Tom Kyte Oracle 11g New Features by Brian […]