Solved

run the top resource consuming query

Posted on 2014-04-04
2
363 Views
Last Modified: 2014-04-07
SQL> select sql_text,
  2   username,
  3   disk_reads_per_exec,
  4   buffer_gets,
  5   disk_reads,
  6   parse_calls,
  7   sorts,
  8   executions,
  9   rows_processed,
 10   hit_ratio,
 11   first_load_time,
 12   sharable_mem,
 13   persistent_mem,
 14   runtime_mem,
 15   cpu_time,
 16   elapsed_time,
 17   address,
 18   hash_value
 19   from
 20   (select sql_text ,
 b.username ,
 round((a.disk_reads/decode(a.executions,0,1,
 a.executions)),2)
 21   22   23   24   disk_reads_per_exec,
 25   a.disk_reads ,
 26   a.buffer_gets ,
 27   a.parse_calls ,
 28   a.sorts ,
 29   a.executions ,
 30   a.rows_processed ,
 31   100 – round(100 *
 32   a.disk_reads/greatest(a.buffer_gets,1),2) hit_ratio,
 33   a.first_load_time ,
 34   sharable_mem ,
 35   persistent_mem ,
 36   runtime_mem,
 37   cpu_time,
 38   elapsed_time,
 39   address,
 40   hash_value
 41   from
 42   sys.v_$sqlarea a,
 43   sys.all_users b
 44   where
 a.parsing_user_id=b.user_id and
 45   46   b.username not in (‘sys’,'system’)
 47   order by 3 desc)
 48   where rownum < 11
 49   /
ERROR:
ORA-01756: quoted string not properly terminated

What can be the error
0
Comment
Question by:tonydba
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
johnsone earned 500 total points
ID: 39978279
It appears that on this line:

b.username not in (‘sys’,'system’)

You have smartquotes.  Those are special characters and not actually quote characters.   The line should be:

b.username not in ('sys','system')
0
 

Author Closing Comment

by:tonydba
ID: 39984584
Thank you..
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question