Hi,
Keep up the good work guys. I come across this problem regularly when trying to tune perf issues. I get a query from V$SQL or AWR and it has "--" comments within it. Oracle knows (knew?) how to parse it because of the carriage returns which were present at parse time but now the carriage returns are lost when the SQL_TEXT is stored in the database. My advice to developers is always to use "/* */" comment notation to make comments clear but I was wondering if there is a way to reconstruct the original SQL layout to know what the query was.

For example:
A developer includes this statement in a procedure:

select /*EXAMPLE 1*/ count(*)
from dba_objects
where object_type = 'TABLE'
and owner <> 'SYS'
and created < sysdate - 50
and status = 'VALID';

Then the DEV decides the owner predicate is not so important to the query and comments out line 4:

select /*EXAMPLE 2*/ count(*)
from dba_objects
where object_type = 'TABLE'
--and owner <> 'SYS'
and created < sysdate - 50
and status = 'VALID';

The counts returned are now clearly different. Then we see a perf issue with Example 2 and study the AWR or search for the problem query in V$SQL:

select sql_id, sql_text
from v$sql
where sql_text like '%EXAMPLE%';

And we get this:

select /*EXAMPLE 2*/ count(*) from dba_objects where object_type = 'TABLE' --and owner <> 'SYS' and created < sysdate - 50 and status = 'VALID';

If you cut and pasted the query text then you would get a different count again with only one predicate.

How can we know what the original format was and how many predicates (1,2 or 3?) were commented out by the "--"?

As I said above, "/* */" would make it clear:
select /*EXAMPLE 3*/ count(*) from dba_objects where object_type = 'TABLE' /* and owner <> 'SYS' */ and created < sysdate - 50 and status = 'VALID';

But do you have any suggestions for the real world?

Thanks,
Tony.

select /*EXAMPLE 1*/ count(*) 
from dba_objects
where object_type = 'TABLE'
and owner <> 'SYS' 
and created < sysdate - 50
and status = 'VALID';

select /*EXAMPLE 2*/ count(*)
from dba_objects
where object_type = 'TABLE'
--and owner <> 'SYS' 
and created < sysdate - 50
and status = 'VALID';

set long 1000000
select sql_id, sql_fulltext
from v$sql
where sql_text like '%EXAMPLE%'
and   sql_text not like '%not this%';

SQL_ID         SQL_FULLTEXT                                                                                                                                      
80jgrj0m9phwt  select /*EXAMPLE 1*/ count(*) 
from dba_objects
where object_type = 'TABLE'
and owner <> 'SYS' 
and created < sysdate - 50
and status = 'VALID'   
br5p55p6dgmhm  select /*EXAMPLE 2*/ count(*)
from dba_objects
where object_type = 'TABLE'
--and owner <> 'SYS' 
and created < sysdate - 50
and status = 'VALID'