[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 at line 1:
ORA-00942: table or view does not exist

Elapsed: 00:00:00.16

Wow, 00:00:00.16! This is a much quicker response time. Probably the ORA-942 was catched by the application and the user did not realise it…

Next time someone ask you to tune a count(*), just revoke access to the table to optimize the response time :twisted:

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 are not getting forgotten !

Thanks Surachart for spreading the news!

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 oracle.jdbc.OracleDriver());
    Connection conn = DriverManager.getConnection(url, props);
    ResultSet res = conn.
      prepareCall("select 'Hello SSL World' txt from dual").
      executeQuery();
    res.next();
    System.out.println(res.getString("TXT"));
  }
}

I have an auto-login wallet (cwallet.sso) with the trusted certificate from the server.

There are a few jar’s to use:

$ CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc5.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/oraclepki.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_cert.jar
$ CLASSPATH=$CLASSPATH:$ORACLE_HOME/jlib/osdt_core.jar
$ CLASSPATH=$CLASSPATH:.
$ export CLASSPATH
$ javac TCPS.java
$ java TCPS
Hello SSL World

Greatly inspired by Jean de Lavarene’s white paper : SSL With Oracle JDBC Thin Driver

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 dual;

        STEP_ID
---------------
124248035235852

SQL> rollback;

Rollback complete.

SQL> select dbms_transaction.step_id from dual;

        STEP_ID
---------------

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 Carr, John Garmany, V. J. Jain, Steve Karam and Lutz Hartmann
Advanced Oracle SQL Programming my book…

I have not worked as an 11g dba before, I will surely learn a lot in those books (and giveaway my book) !