CODEWITHSUNDEEP
sqloracle-database
Astora
Astora

Reputation: 725

How to divide hour in Oracle Database

I have the following query, but I have a little problem

...
    trunc(to_char(to_date(elapsed_time,'hh24:mi:ss'),'sssss')/EXECUTIONS,2)

        ERROR at line 2:
        ORA-01850: hour must be between 0 and 23
...

That is happening because the elapsed_time is the sum of all queries and the value is above 24h.

How can I divide the value?

If you need, here is two queries, one about all elapsed_time and the other is the query with the issue, the query with the issue is a query that takes the average by execution

--TOP QUERIES COM MAIOR ELAPSED_TIME
set pagesize 0 embedded on;
set linesize 3000;
col FIRST_LOAD_TIME for a20;
col LAST_LOAD_TIME for a20;
col EXECUTIONS for a10;
COL sql_id for a20;
col FETCHES for a10;
col PARSE_CALLS for a15;
col elapsed_time for a20;
SELECT * FROM
(SELECT
    sql_id,
    TO_CHAR(TRUNC((elapsed_time/1000000)/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD((elapsed_time/1000000),3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD((elapsed_time/1000000),60),'FM00') AS elapsed_time, --ESSE CAMPO É A SOMA DE TODO O TEMPO DE ESPERA, NAO DE APENAS UMA EXECUCAO
    child_number,
    disk_reads,
    to_char(executions) as executions,
    to_char(FETCHES) as FETCHES,
    to_char(PARSE_CALLS) as PARSE_CALLS,
    first_load_time,
    last_load_time
FROM    gv$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10 ---top 10
/

The query that I want to build by average time:

--MEDIA ELAPSED_TIME POR EXECUCAO
set pagesize 0 embedded on;
set linesize 3000;
col FIRST_LOAD_TIME for a20;
col LAST_LOAD_TIME for a20;
col EXECUTIONS for a10;
COL sql_id for a20;
col FETCHES for a10;
col PARSE_CALLS for a15;
col TOTAL_ELAPSED_TIME_SECS for a40;
SELECT sql_id,elapsed_time,to_char(to_date(elapsed_time,'hh24:mi:ss'),'sssss') "TOTAL_ELAPSED_TIME_SECS",
EXECUTIONS,trunc(to_char(to_date(elapsed_time,'hh24:mi:ss'),'sssss')/EXECUTIONS,2) "AVG_TIME_BY_EXECUTION_SEC" FROM
(SELECT
    sql_id,
    TO_CHAR(TRUNC((elapsed_time/1000000)/3600),'FM9900') || ':' ||
    TO_CHAR(TRUNC(MOD((elapsed_time/1000000),3600)/60),'FM00') || ':' ||
    TO_CHAR(MOD((elapsed_time/1000000),60),'FM00') AS elapsed_time, --ESSE CAMPO É A SOMA DE TODO O TEMPO DE ESPERA, NAO DE APENAS UMA EXECUCAO
    to_char(EXECUTIONS) as EXECUTIONS
FROM    gv$sql
ORDER BY elapsed_time DESC)
WHERE ROWNUM < 10 ---top 10
/

Note: If possible, I'd like to have the average time in hour as well rather than seconds, such as in the query above.

Upvotes: 1

Views: 277

Answers (1)

Popeye
Popeye

Reputation: 35900

Why are you converting the microseconds to date and then again back to the seconds?

I think the following query will fulfill your requirement:

SELECT
    SQL_ID,
    ELAPSED_TIME_CHAR, --change here
    TRUNC(ELAPSED_TIME, 2)   AS "TOTAL_ELAPSED_TIME_SECS", --change here
    EXECUTIONS,
    TRUNC(ELAPSED_TIME / EXECUTIONS, 2) "AVG_TIME_BY_EXECUTION_SEC" --change here
FROM
    (
        SELECT
            SQL_ID,
            ELAPSED_TIME / 1000000 AS ELAPSED_TIME, --change here
            TO_CHAR(TRUNC((ELAPSED_TIME / 1000000) / 3600), 'FM9900')
            || ':' || TO_CHAR(TRUNC(MOD((ELAPSED_TIME / 1000000), 3600) / 60), 'FM00')
            || ':' || TO_CHAR(MOD((ELAPSED_TIME / 1000000), 60), 'FM00') AS ELAPSED_TIME_CHAR, 
            TO_CHAR(EXECUTIONS) AS EXECUTIONS
        FROM GV$SQL
        ORDER BY ELAPSED_TIME DESC
    )
WHERE ROWNUM < 10 ---top 10
/

Cheers!!

Upvotes: 3

Related Questions