Oracle PL/SQL/System Tables Views/sql

Query SQL table

 
SQL>
SQL> select * from dual;
D
-
X
SQL> select * from DUAL;
D
-
X
SQL>
SQL> select sql_text, hash_value from v$sql where upper(sql_text) = "SELECT * FROM DUAL";
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE
----------
select * from DUAL
3649189949
select * from dual
 942515969
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE
----------
select * from dual
 942515969

3 rows selected.
SQL>
SQL> alter session set OPTIMIZER_MODE = first_rows;
Session altered.
SQL>
SQL> select * from dual;
D
-
X

select sql_text, hash_value, parsing_user_id, optimizer_mode from v$sql

 
SQL>
SQL> select * from dual;
D
-
X
SQL>
SQL> select sql_text, hash_value, parsing_user_id
  2        from v$sql
  3       where upper(sql_text) = "SELECT * FROM DUAL";
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE PARSING_USER_ID
---------- ---------------
select * from DUAL
3649189949               0
select * from dual
 942515969               0
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE PARSING_USER_ID
---------- ---------------
select * from dual
 942515969               0

3 rows selected.
SQL>
SQL> select sql_text, hash_value, parsing_user_id, optimizer_mode
  2       from v$sql
  3       where upper(sql_text) = "SELECT * FROM DUAL";
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE PARSING_USER_ID OPTIMIZER_
---------- --------------- ----------
select * from DUAL
3649189949               0 RULE
select * from dual
 942515969               0 RULE
SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HASH_VALUE PARSING_USER_ID OPTIMIZER_
---------- --------------- ----------
select * from dual
 942515969               0 FIRST_ROWS

3 rows selected.
SQL>