[Macromedia][Oracle JDBC Driver][Oracle]ORA-01003: no statement parsed

Thanks for the information. This new information means that I have to revise all my previous suggestions.

Here are new suggestions, based on the new information:

1.  As you're using the Oracle driver that ships with ColdFusion, let ColdFusion decide which version to use.

I initially thought you were using a custom Oracle JDBC driver., hence my previous suggestion to update. But it turns out you're using the Oracle driver that ships with ColdFusion. In that case, you should let ColdFusion decide which driver to use. The driver version is indeed 6.0.0.1408. However, to be sure, perform the Felix-cache fix discussed below.

2.  Update the value of the setting "Max Pooled Statements" not to 318, but to 50!

There is something odd about this setting. You said you had set it to 500. Yet your printscreen shows that the value in effect is 300, the default. Might it be that ColdFusion is failing to pick up the updated setting? If so, could this have something to do with the Felix-cache issue discussed below?

Anyway, let's march on. You say that the number of unique cfquery tags that use cfqueryparam is 318. (I am assuming there are no stored-procedure tags using cfqueryparam). Each such query corresponds to a prepared statement in the pool. On this basis, ColdFusion's documentation suggests that you use the value 318 for Max Pooled Statements.

However, we should look at this differently. Especially because of the database issues you are experiencing.

A pool of 318 statements means that ColdFusion could possibly feel free to allocate 318 cached prepared statements per database connection. That is a lot of statements for a single connection. It is unlikely that your application will ever need all 318 queries at the same time. The end result will likely be unnecessary congestion, besides it being extremely inefficient.

Hence my suggestion that the statements-pool consist of a maximum of one-sixth the number of queries. That is, one-sixth of 318, which is around 50!

With Max Pooled Statements = 50, what could the number of pooled connections then be?

That comes next. 

3.  Tick the checkboxes "Limit Connections" and "Maintain Connections", and enter the value 64 for "Restrict connections to"

These settings ensure that ColdFusion will create a database connection pool with a maximum size of 64 connections. ColdFusion will in effect:

- Open new connections as needed, up to 64;

- Reuse connections if available;

- Queue requests beyond that until a connection is free.

You can confirm that 64 is a good rule-of-thumb value. Firstly, it exceeds 50, the Max Pooled Statements, with extra pooled connections in reserve. Secondly, there is a curiosity in ColdFusion. If you leave blank the settings  "Limit Connections" and "Restrict connections to", ColdFusion will use the following as the default value for the number of connections: the "Maximum number of simultaneous Template requests". This value is 25 by default, which has same order of magnitude as 64.

4.  Check the checkbox "Enable connection validation" and enter a suitable "Validation Query", for example

SELECT 1 FROM dual

5.  Make the query more efficient by replacing the repetitive 'evtime >='.

That is, replace

select to_char(evtime, 'mm/dd/yyyy hh24:mi') AS evtime, SUM(count_) AS tcnt
from traffic_stats
WHERE evtime >= (param 1)
and evtime >= (param 2)
and evtime >= (param 3)
and node = (param 4)
and type = (param 5)
group by evtime
order by evtime

with something like

SELECT TO_CHAR(evtime, 'mm/dd/yyyy hh24:mi') AS evtime, SUM(count_) AS tcnt 
FROM traffic_stats
WHERE evtime >= GREATEST(<CFQUERYPARAM VALUE="#param1#"  CFSQLType="CF_SQL_TIMESTAMP">, <CFQUERYPARAM VALUE="#param2#"  CFSQLType="CF_SQL_TIMESTAMP">, <CFQUERYPARAM VALUE="#param3#"  CFSQLType="CF_SQL_TIMESTAMP">)
AND node = <CFQUERYPARAM VALUE="#param4#"  CFSQLType="CF_SQL_TIMESTAMP">
AND type = <CFQUERYPARAM VALUE="#param5#"  CFSQLType="CF_SQL_TIMESTAMP">
GROUP BY evtime
ORDER BY evtime

6.  Update ColdFusion 2021

Your update level is 18. Change from Update 18 to Update 20 (the latest). Though this may not be directly related to the issue, it helps to have the latest ColdFusion 2021 software in place.

7.  Delete everything from the folder {CF_INSTANCE_HOME}/bin/felix-cache, then restart ColdFusion

After you inplement an update, delete everything from the folder {CF_INSTANCE_HOME}/bin/felix-cache, then restart ColdFusion.

In fact, even if you do not yet update, I would urge you to empty the felix-cache folder and then restart. Doing so has been known to fix diverse issues with no apparent link between them. There is an even stronger reason. As the release notes for Update 17 show, clearing the Felix cache has some relation to the Oracle data source.