Where does this query comes from?

Question

I have a web application that runs in Tomcat, it connects to a Oracle DB through a DataSource. I've been dealing with some weird behavor, because the connection pool gets full even if there are no users connected and as soon as the application starts! When I check the queries that are being executed it always appears to be the same:

select value$ from props$ where name = 'global_db_name'

The context for the Connection is this:

<Context antiResourceLocking="true" crossContext="true" path="/taquillas">
    <Resource auth="Container" driverClassName="oracle.jdbc.OracleDriver"
              factory="org.apache.tomcat.jdbc.pool.DataSourceFactory"
              jdbcInterceptors="org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer"
              jmxEnabled="true" url="jdbc:oracle:thin:@yyy.yyy.yyy.yyy:zzzz:ANNI"
              username="xxxxxxxxx" name="jdbc/andrea" password="xxxxxxx"
              type="javax.sql.DataSource" validationInterval="30000"
              maxActive="50" minIdle="1" maxWait="10000" defaultAutoCommit="false"
              initialSize="1" removeAbandonedTimeout="60"
              removeAbandoned="true" validationQuery="SELECT 1 FROM DUAL"/>
</Context>

And the DataSource is like this:

public dbutilsHandler()
            throws ClassNotFoundException, SQLException, NamingException {
        this.gson = new GsonBuilder()
                .setDateFormat("yyyy-MM-dd")
                .serializeNulls().create();
        InitialContext cxt;
        cxt = new InitialContext();
        this.ds = (DataSource) cxt.lookup("java:/comp/env/jdbc/andrea");
        this.query = new QueryRunner(ds);
        this.con = this.query.getDataSource().getConnection();
    }

I'm making sure that no connections are left open but I still don't understand why is happening. Help!

We have no idea what this code is for, when it's called, or anything. All we can say is that it doesn't respect the Java naming conventions, and that is opens a connection but doesn't close it. — JB Nizet, Apr 07 '17 at 23:01
How are you identifying that SQL (which a quick Google suggests is common background noise) as the culprit; and how are you determining the connection pool is full - what does that mean, it's got 50 connections, or won't open any more? What code is erroring, and what error do you actually get? What does the application do at startup? — Alex Poole, Apr 07 '17 at 23:17

score 0 · Answer 1 · answered Apr 08 '17 at 13:38

prop$ is a SYS table. It holds values for database properties like DEFAULT_TEMP_TABLESPACE, NLS_LANG and, yes, GLOBAL_DB_NAME. Find out more.

The query you mention ...

select value$ from props$ where name = 'global_db_name'

... is recursive SQL, which means it's an internal statement generated by Oracle to support application SQL. Anecdotally it seems related to rogue trace processes, so it's worth checking whether you have a system level trigger executing something like this:

alter system set events ‘10046 trace name context forever,level 12’;

Where does this query comes from?

1 Answers1