| SP2-0552: Bind variable "5" not declared. [message #621996] |
Wed, 20 August 2014 06:02  |
 |
Srijesh
Messages: 24
Registered: August 2014
Location: Berlin
|
Junior Member |
|
|
Hi,
User complained that after running the below sql query for a while they are getting exception error like user cancelled request.
This query is run from a java batch program.They also mentioned that they are facing problem only when this query run from
java batch program.Earlier they got the output from sqlplus but when they run teh same query nowin sqlplus it is taking more time.
When i run the same query in sqlplus,i get SP2-0552: error
SQL> Select h.execution_id as executionId, h.calib_ref_key as calibRefKey, count(*) as noOfSamples, avg(r.test_result) as average, stddev(r.test_result) as stdDev, 9.0 as upperSpecLimit, -6.0 as lowSpecLimit, 1.5 as targetResult from dat_rtqm_test_header h inner join ( (select * from dat_rtqm_test_result4) ) r on h.testdata_ref_key = r.testdata_ref_key where DATE_TIME >= :1 and DATE_TIME < :2 and h.station_id = :3 and r.test_step_id = :4 and r.test_step_rev = :5 and ( ( calib_ref_key = 8376 and date_time > :6 ) or ( calib_ref_key = 6545 and date_time > :7 ) ) group by h.execution_id, h.calib_ref_key
2 /
SP2-0552: Bind variable "5" not declared.
SQL>
Below is the explain plan for sql query.
SQL> explain plan for Select h.execution_id as executionId, h.calib_ref_key as calibRefKey, count(*) as noOfSamples, avg(r.test_result) as average, stddev(r.test_result) as stdDev, 9.0 as upperSpecLimit, -6.0 as lowSpecLimit, 1.5 as targetResult from dat_rtqm_test_header h inner join ( (select * from dat_rtqm_test_result4) ) r on h.testdata_ref_key = r.testdata_ref_key where DATE_TIME >= :1 and DATE_TIME < :2 and h.station_id = :3 and r.test_step_id = :4 and r.test_step_rev = :5 and ( ( calib_ref_key = 8376 and date_time > :6 ) or ( calib_ref_key = 6545 and date_time > :7 ) ) group by h.execution_id, h.calib_ref_key
2 /
Explained.
SQL> SELECT * FROM table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 50 | 420 (3)|
| 1 | HASH GROUP BY | | 1 | 50 | 420 (3)|
|* 2 | FILTER | | | | |
| 3 | NESTED LOOPS | | | | |
| 4 | NESTED LOOPS | | 1 | 50 | 419 (3)|
|* 5 | TABLE ACCESS BY INDEX ROWID | DAT_RTQM_TEST_HEADER | 1 | 32 | 416 (3)|
| 6 | BITMAP CONVERSION TO ROWIDS | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 7 | BITMAP AND | | | | |
| 8 | BITMAP CONVERSION FROM ROWIDS | | | | |
| 9 | SORT ORDER BY | | | | |
|* 10 | INDEX RANGE SCAN | DAT_RTQM_TEST_HEADER_I2 | 3643 | | 61 (0)|
| 11 | BITMAP OR | | | | |
| 12 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 13 | SORT ORDER BY | | | | |
|* 14 | INDEX RANGE SCAN | DAT_RTQM_TEST_HEADER_I2 | 3643 | | 119 (0)|
| 15 | BITMAP CONVERSION FROM ROWIDS| | | | |
| 16 | SORT ORDER BY | | | | |
|* 17 | INDEX RANGE SCAN | DAT_RTQM_TEST_HEADER_I2 | 3643 | | 119 (0)|
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 18 | INDEX RANGE SCAN | DAT_RTQM_TEST_RESULT4_I | 1 | | 2 (0)|
|* 19 | TABLE ACCESS BY INDEX ROWID | DAT_RTQM_TEST_RESULT4 | 1 | 18 | 3 (0)|
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(TO_DATE(:1)<TO_DATE(:2))
5 - filter("H"."STATION_ID"=:3 AND ("H"."CALIB_REF_KEY"=8376 AND "H"."DATE_TIME">:6 OR
"H"."CALIB_REF_KEY"=6545 AND "H"."DATE_TIME">:7))
10 - access("H"."DATE_TIME">=:1 AND "H"."DATE_TIME"<:2)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
14 - access("H"."DATE_TIME">:6)
filter("H"."DATE_TIME">:6)
17 - access("H"."DATE_TIME">:7)
filter("H"."DATE_TIME">:7)
18 - access("H"."TESTDATA_REF_KEY"="DAT_RTQM_TEST_RESULT4"."TESTDATA_REF_KEY" AND
"DAT_RTQM_TEST_RESULT4"."TEST_STEP_ID"=TO_NUMBER(:4))
19 - filter("DAT_RTQM_TEST_RESULT4"."TEST_STEP_REV"=TO_NUMBER(:5))
Note
-----
- 'PLAN_TABLE' is old version
44 rows selected.
Please suggest me how to fix this SP2 error and make query run faster.
Note: --The objects involved in the query belongs to DEMO user and i have pulled the explain plan of it by logging as DEMO user.
Regards,
Srijesh
[Updated on: Wed, 20 August 2014 06:42] Report message to a moderator |
|
|
|
| Re: SP2-0552: Bind variable "5" not declared. [message #622002 is a reply to message #621996] |
Wed, 20 August 2014 06:56  |
|
Srijesh
Messages: 24
Registered: August 2014
Location: Berlin
|
Junior Member |
|
|
I ran the same query in sqlplus and it return no rows in 2 minutes.
SQL> select h.execution_id as executionId,
h.calib_ref_key as calibRefKey,
count(*) as noOfSamples,
2 3 4 avg(r.test_result) as average,
5 stddev(r.test_result) as stdDev,
6 20 as upperSpecLimit,
10 as lowSpecLimit,
15 as targetResult
7 8 9 from dat_rtqm_test_header h
10 inner join (select * from dat_rtqm_test_result3) r
11 on h.testdata_ref_key = r.testdata_ref_key
12 where DATE_TIME >= TO_DATE('2014-07-10 14:00:00', 'yyyy-mm-dd hh24:mi:ss')
and DATE_TIME < TO_DATE('2014-07-10 14:30:00', 'yyyy-mm-dd hh24:mi:ss')
13 14 and h.station_id = '6A_440PEA01'
15 and r.test_step_id = 3815
and r.test_step_rev = 1
16 17 and((calib_ref_key='10650' and date_time > TO_DATE('2014-07-10 14:19:39','yyyy-mm-dd hh24:mi:ss') or (calib_ref_key = '13699' and date_time > TO_DATE('2014-07-10 14:19:30', 'yyyy-mm-dd hh24:mi:ss')))) group by h.execution_id, h.calib_ref_key;
no rows selected
Elapsed: 00:02:37.12
I ran the same query after sometime and it got completed within 1 minute.
SQL> select h.execution_id as executionId,
h.calib_ref_key as calibRefKey,
count(*) as noOfSamples,
2 3 4 avg(r.test_result) as average,
5 stddev(r.test_result) as stdDev,
6 20 as upperSpecLimit,
7 10 as lowSpecLimit,
8 15 as targetResult
9 from dat_rtqm_test_header h
10 inner join (select * from dat_rtqm_test_result3) r
11 on h.testdata_ref_key = r.testdata_ref_key
where DATE_TIME >= TO_DATE('2014-07-10 14:00:00', 'yyyy-mm-dd hh24:mi:ss')
12 13 and DATE_TIME < TO_DATE('2014-07-10 14:30:00', 'yyyy-mm-dd hh24:mi:ss')
14 and h.station_id = '6A_440PEA01'
15 and r.test_step_id = 3815
16 and r.test_step_rev = 1
17 and((calib_ref_key='10650' and date_time > TO_DATE('2014-07-10 14:19:39','yyyy-mm-dd hh24:mi:ss') or (calib_ref_key = '13699' and date_time > TO_DATE('2014-07-10 14:19:30', 'yyyy-mm-dd hh24:mi:ss')))) group by h.execution_id, h.calib_ref_key;
no rows selected
Elapsed: 00:01:36.95
Here is the Trace of the problem SQL when it is running.
TKPROF: Release 11.2.0.3.0 - Development on Wed Aug 20 19:08:40 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ORA_ora_20830.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
SQL ID: 4kvc1pjyh5n7x Plan Hash: 0
ALTER SESSION SET SQL_TRACE=TRUE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************
SQL ID: dbmdavb6ydwb4 Plan Hash: 0
ALTER SESSION SET SQL_TRACE=FALSE
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 2 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 0
2 user SQL statements in session.
0 internal SQL statements in session.
2 SQL statements in session.
********************************************************************************
Trace file: HVMES1_ora_20830.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
2 user SQL statements in trace file.
0 internal SQL statements in trace file.
2 SQL statements in trace file.
2 unique SQL statements in trace file.
37 lines in trace file.
162 elapsed seconds in trace file.
[Updated on: Wed, 20 August 2014 07:18] Report message to a moderator |
|
|
|
|
|
|
|
| Re: SP2-0552: Bind variable "5" not declared. [message #622017 is a reply to message #622015] |
Wed, 20 August 2014 08:36 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Srijesh wrote on Wed, 20 August 2014 14:30Quote:Read the performances sticky and post the requested information (if you can read).
And FORMAT your queries as required (if you can read).
I have provided enough of information.Answer me if you can.
I hope queries are formatted. You have not provided enough information.
Look at your trace file: it has two commands, neither of which is the statement.
Your queries are not formatted. If you don't know how to do it, try this,
http://www.dpriver.com/pp/sqlformat.htm
|
|
|
|
| Re: SP2-0552: Bind variable "5" not declared. [message #622018 is a reply to message #622015] |
Wed, 20 August 2014 08:40 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Srijesh wrote on Wed, 20 August 2014 14:30Quote:Read the performances sticky and post the requested information (if you can read).
And FORMAT your queries as required (if you can read).
I have provided enough of information.Answer me if you can.
No you really haven't. Read the trace file you posted above, do you think it contains anything useful?
Plus we really do expect you to provide the other information detailed in the performance tuning sticky and I'm sure we've pointed it out to you before.
Srijesh wrote on Wed, 20 August 2014 14:30
I hope queries are formatted.
Don't hope, know. The first ones aren't formatted at all, the later ones are beter but the SQLplus line numbers have messed it up.
|
|
|
|
|
|
|
|
| Re: SP2-0552: Bind variable "5" not declared. [message #622026 is a reply to message #622018] |
Wed, 20 August 2014 09:33 |
|
Srijesh
Messages: 24
Registered: August 2014
Location: Berlin
|
Junior Member |
|
|
Here is the query with actual value.
SQL> SELECT h.execution_id AS executionId,
h.calib_ref_key AS calibRefKey,
Count(*) AS noOfSamples,
2 3 4 Avg(r.test_result) AS average,
5 Stddev(r.test_result) AS stdDev,
6 20 AS upperSpecLimit,
7 10 AS lowSpecLimit,
8 15 AS targetResult
9 FROM dat_rtqm_test_header h
10 INNER JOIN (SELECT *
11 FROM dat_rtqm_test_result3) r
12 ON h.testdata_ref_key = r.testdata_ref_key
13 WHERE date_time >= To_date('2014-07-10 14:00:00', 'yyyy-mm-dd hh24:mi:ss')
14 AND date_time < To_date('2014-07-10 14:30:00', 'yyyy-mm-dd hh24:mi:ss')
15 AND h.station_id = '6A_440PEA01'
16 AND r.test_step_id = 3815
AND r.test_step_rev = 1
17 18 AND (( calib_ref_key = '10650'
AND date_time > To_date('2014-07-10 14:19:39',
19 20 'yyyy-mm-dd hh24:mi:ss')
21 OR ( calib_ref_key = '13699'
22 AND date_time > To_date('2014-07-10 14:19:30',
23 'yyyy-mm-dd hh24:mi:ss')
24 ) ))
25 GROUP BY h.execution_id,
26 h.calib_ref_key;
no rows selected
SQL>
Here is the Trace of the problem SQL when it is running.
TKPROF: Release 11.2.0.3.0 - Development on Wed Aug 20 21:29:11 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Trace file: ORCL_ora_1080.trc
Sort options: default
********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************
Trace file: ORCL_ora_1080.trc
Trace file compatibility: 11.1.0.7
Sort options: default
1 session in tracefile.
0 user SQL statements in trace file.
0 internal SQL statements in trace file.
0 SQL statements in trace file.
0 unique SQL statements in trace file.
24 lines in trace file.
0 elapsed seconds in trace file.
Please let me know what else information you need.
[Updated on: Wed, 20 August 2014 09:35] Report message to a moderator |
|
|
|
| Re: SP2-0552: Bind variable "5" not declared. [message #622027 is a reply to message #622026] |
Wed, 20 August 2014 09:46 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Ok serious suggestion - find yourself a less taxing job, you spend so little effort thinking about what you do that you're not even bad at it.
Both myself and John have pointed out that the previous trace file contains nothing useful.
The new trace file contains even less information than the first one, partly because you had it running for less than a second (I say partly since the one ran a lot longer and still didn't capture anything)
We have also asked you to read the performance tunning sticky and post the information requested. If you don't know where that is ASK, don't just ignore it because you can't be bothered to work out what we mean.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
