I am using the "OTN Developer Day" virtual machine (Oracle Linux 64-bit OS). Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit.
The problem I have is that I get a plan_hash_value returned by autotrace after I run a SELECT, but when I look for records having that plan_hash_value in v$sql, v$sqlstats, dba_hist_sqlstat and other views, there are no rows returned.
I was running these same commands some time ago against another installation (which I no longer have access to) and it would return rows in v$sql, etc.
All passwords are oracle or noted if otherwise.
The SQL Developer and Data Modeler scripts are in the directory:
/u01/oracle/Desktop/Database_Track/sqldevdm
and
/u01/oracle/Desktop/Database_Track/sqldev
To Reset the labs:
XML DB : /home/oracle/reset_xmldb
SQL Developer: /home/oracle/reset_sqldev
Application Express: /home/oracle/reset_apex
Database Information:
Oracle SID : orcl
Pluggable DB : pdb1
ALL PASSWORDS ARE : oracle
*** Please note that this appliace is for testing purposes only,
as such it is unsupported and should not be used as a production environment.
[oracle@localhost ~]$ sqlplus VPDTestUser/VPDTestUser@pdb1
SQL*Plus: Release 12.1.0.1.0 Production on Thu May 29 01:01:56 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Last Successful login time: Thu May 29 2014 01:00:59 -04:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
PDB1@ORCL> set timing on
PDB1@ORCL> set autotrace traceonly
PDB1@ORCL> SELECT * FROM TEST_OWNER1.BIG_TABLE_T13;
10000000 rows selected.
Elapsed: 00:04:12.13
Execution Plan
----------------------------------------------------------
Plan hash value: 939753310
--------------------------------------------------------------------------------
---
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
---
| 0 | SELECT STATEMENT | | 10M| 1192M| 49918 (1)| 00:00:0
2 |
|* 1 | TABLE ACCESS FULL| BIG_TABLE_T13 | 10M| 1192M| 49918 (1)| 00:00:0
2 |
--------------------------------------------------------------------------------
---
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SECURITY_CODE"=1 OR "SECURITY_CODE"=2 OR "SECURITY_CODE"=3
OR "SECURITY_CODE" IS NULL)
Statistics
----------------------------------------------------------
485 recursive calls
0 db block gets
837945 consistent gets
182880 physical reads
0 redo size
593326418 bytes sent via SQL*Net to client
7333869 bytes received via SQL*Net from client
666668 SQL*Net roundtrips to/from client
27 sorts (memory)
0 sorts (disk)
10000000 rows processed
PDB1@ORCL> conn sys/oracle as sysdba
Connected.
PDB1@ORCL> show user
USER is "SYS"
PDB1@ORCL> SELECT COUNT(1) FROM v$sql WHERE plan_hash_value = 939753310;
COUNT(1)
----------
0
Elapsed: 00:00:00.14
PDB1@ORCL> SELECT COUNT(1) FROM v$sqlstats WHERE plan_hash_value = 939753310;
COUNT(1)
----------
0
Elapsed: 00:00:00.09
PDB1@ORCL> SELECT COUNT(1) FROM dba_hist_sqlstat WHERE plan_hash_value = 939753310;
COUNT(1)
----------
0
Elapsed: 00:00:01.21
PDB1@ORCL>
Another thing I did was locate the sql_id in Oracle SQL Developer Sessions screen, while the query was running (query takes ~4 minutes to complete) and then look for that sql_id in v$sql which also did not work.
Any suggestions welcome. Also I apologize if I missed any details - this is the first time I post here.