Oracle queries perform slow SQL every day

This article is reproduced from http://blog.itpub.net/28602568/viewspace-1364844/

Preface:  Do you want to query, summarize or optimize the rows of SQL every day, the following SQL wants to help    ---query perform slow sql:select s.sql_text every day,        s.sql_fulltext,       s.sql_id,        round (Elapsed_time/ 1000000 /(case                WHEN  (Executions = 0 or nvl ( executions, 1 ) = 1)  THEN                 1                ELSE                Executions             end),              2) "Execution time ' S '",        S.executions "executionTimes ",       s.optimizer_cost" cost ",       S.SORTS,        s.module, --Connection mode (JDBC THIN CLIENT: Program)         --s.locked_total,       s.physical_read_bytes "Physical reading",        --s.physical_read_requests "Physical read request",       S.PHYSICAL_WRITE _requests "Physical Write",        --s.physical_write_bytes "Physical Write Request",        s.rows_processed      "return rows",       S. disk_reads          "Disk read",       S. direct_writes       "Direct Path write",       S.PARSING_SCHEMA_ name,       S.last_active_time  from gv$sqlarea S WHERE ROUND (Elapsed_time/ 1000000 /(case                WHEN  (executions = 0 or nvl (executions, 1 ) = 1)  then                1                ELSE                 executions              end),              2) > 5 --100 0000 μs =1s   and s.parsing_ Schema_name = user   and to_char (s.last_load_time,  ' YYYY-MM-DD ') =        to_char ( SYSDATE,  ' yyyy-mm-dd '  )    and s.command_ type in  (2 ,&Nbsp;3, 5, 6 , 189)  ORDER BY  "Execution Time '"  DESC; 
/* Command_type meaning in sql:

2:insert3:select6:update7:delete189:merge

Details can be found V$sqlcommand view */V$sqlarea official website Explanation: http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3064.htm# REFRN30259 V$sqlcommand Official website explanation: http://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3066.htm#REFRN30632

Oracle queries perform slow SQL every day