Pular para o conteúdo
  • Este tópico contém 11 respostas, 3 vozes e foi atualizado pela última vez 7 anos, 2 meses atrás por Avatar de José Laurindo ChiappaJosé Laurindo Chiappa.
Visualizando 12 posts - 1 até 12 (de 12 do total)
  • Autor
    Posts
  • #108537
    Avatar de dfguiraldellidfguiraldelli
    Participante

      Olá a todos,
      gostaria de uma ajuda com uma tarefa.
      Tem uma rotina que executamos aqui na empresa que é um relatório de fechamento.
      Esse relatório ou demora muito para concluir ou trava e não finaliza.
      Me pediram para enviar para os programadores o monitoramento do banco com:
      – SQLs Top Waiting com os events Type da Rotina.
      – Plano de execução da Rotina.
       
      Estou tentando fazer mas não consegui montar o script para capturar os SQL da sessão especifica com o SID.
      E estou lendo sobre o plano de execução mas não entendi como aplicar para uma sessão usando o SID.

      Desde já agradeço.

      #108538
      Avatar de airoospairoosp
      Participante

        Opa boa tarde,

        Você tem acesso a todas as querys que o relatório executa? Se sim, você pode separar a consulta por partes e analisar o que esta ocorrendo. Falo isso, pois aqui na empresa, consigo pegar as consultas dos relatórios, fazer a análise usando o plano de execução e identificar as tabelas que fazem full scan.
        Para estas tabelas, crio índices e também verifico se as estatísticas estão atualizadas.
        O ambiente aqui é Windows, Oracle 10g e PL/SQL Developer.

        Obrigado.

        Airton

        #108539
        Avatar de dfguiraldellidfguiraldelli
        Participante

          Opa,

          então, não domino muito essa parte de SQL. Tenho alguns scripts que um amigo me ajudou a fazer para ver sessões ativas, inativas, bloqueadas e tal, mas sei como fazer para pegar de um usuário especifico a rotina que ele está executando.

          Consigo pegar por exemplo que o User1 está conectado com SID 960 por exemplo.

          Queria fazer script que eu coloque esse SID e veja o que esta sendo executado por ele no banco. Assim eu consigo pegar o Select completo do relatório que esta sendo gerado.

          Outra coisa é como fazer para criar o plano de execução desse relatório, para ver se esta usando um índice ou se esta fazendo full scan nas tabelas.

          #108540
          Avatar de airoospairoosp
          Participante

            Boa tarde,

            Você utiliza alguma ferramenta para trabalhar com o SQL? A Oracle tem o SQL Developer que ajuda bastante, dê uma olhada no site da Oracle.

            Com a query abaixo, é possível identificar as linhas do SQL conforme a sessão informada:

            select b.sid, t.piece, s.hash_value stmtid, t.sql_text sqltxt,
            sum(s.disk_reads) dr, sum(s.buffer_gets) bg, sum(s.rows_processed) rp,
            sum(s.buffer_gets)/greatest(sum(s.rows_processed),1) rpr,
            sum(s.executions) exe,
            sum(s.buffer_gets)/greatest(sum(s.executions),1) rpe
            from v$sql s, v$sqltext t, v$session b
            where s.command_type in ( 2,3,6,7 )
            and s.hash_value = t.hash_value
            and t.address = b.sql_address
            and t.hash_value = b.sql_hash_value
            and b.sid = &1 <—- informe o SID da sessão
            group by b.sid, t.piece, s.hash_value, t.sql_text
            order by t.piece

            A coluna SQLTXT contém o SQL, estará quebrada em mais de uma linha, a ordenação é feita pela coluna PIECE, que já esta no ORDER BY.

            Depois de pegar o SQL, e organizar o código, você executa:

            EXPLAIN PLAN FOR
            SQL (código obtido acima)

            Commit

            E depois o SQL abaixo para ver as informações do plano de execução:

            select plan_table_output
            from table(dbms_xplan.display('plan_table',null,'All'));

            A saida do SELECT acima irá conter informações para análise.

            Acho que já deve ajudar um pouco, pelo menos um caminho a seguir. Os colegas poderão ajudar com mais informações sobre o assunto.

            Dê uma olhada neste link, https://blogs.oracle.com/optimizer/entry/displaying_and_reading_the_execution_plans_for_a_sql_statement

            Obrigado.

            Airton

            #108542
            Avatar de dfguiraldellidfguiraldelli
            Participante

              Airton,

              muito obrigado pela ajuda.

              Vou fazer conforme você passou.

              Mais uma vez obrigado.

              #108544
              Avatar de José Laurindo ChiappaJosé Laurindo Chiappa
              Moderador

                Tudo jóia ? Então, não é mau vc analisar a query do report em questão (ainda mais que normalmente reports são compostos de uma query só , por vezes complexa, com vários joins mas uma query só) – porém, eu vou Recomendar que vc faça sim o trace, pois :

                => a análise do plano de execução feita fora da tool de Report NÂO vai te mostrar ineficiências do modo como a tool foi programada (por exemplo, SQLs sem binds, falta de array processing, etc)

                => se o problema Não For de plano de execução e sim for externo (por exemplo, está havendo espera por LOCKs) isso NÂO VAI APARECER no plano de execução

                Por causa de coisas assim, eu recomendo SIM um trace completo da sessão, pois aí Além do plano vc obtém esperas e demais indicadores de eficiência…

                Pra fazer isso, realmente como vc supunha o procedimento normal era tentar localizar o SID e o SERIAL da sessão (pesquisando pelas colunas de LOGON, de PROGRAM, de USERNAME, etc) e informar isso pra um dos comandos de trace – isso envolve porém trabalho manual, não é o melhor jeito : no seu caso eu ** IMAGINO ** que vc tem o código-fonte desse report, e que o pode alterar, né ? Aí vc simplesmente coloca logo no início do ser relatório um comando de iniciar o trace….
                Vou exemplificar botando os comandos no sqlplus, mas poderia ser feito em qualquer linguagem/tool cliente…. E no caso vou usar a package DBMS_MONITOR , mas há a possibilidade de usar ALTER SESSION (via alter session set events ’10046 trace name context forever,level 16′), ou outras packages do sistema…

                IMPORTANTE : no meu exemplo, estou SUPONDO que as variáveis de ambiente estão setadas, que o parâmetro TIMED_STATISTICS está (como é default) setado como TRUE, etc…

                ==> primeira coisa, normalmente um usuário comum não tem acesso a essa package, então (conectado como DBA) vou dar acesso ao usuário HR, que vai ter a sua sessão tracejada :

                SYS:AS SYSDBA@orcl:SQL>grant execute on dbms_monitor to hr;

                Grant succeeded.

                ==> o arquivo de trace vai ser gerado num diretório específico, pra saber qual consulto o parâmetro de user dump :

                SYS:AS SYSDBA@orcl:SQL>show parameter user_dump_dest

                NAME TYPE VALUE
                ———————————— ———– ——————————
                user_dump_dest string /home/oracle/app/oracle/diag/r
                dbms/orcl/orcl/trace
                ==> aciono a minha “tool de report” e conecto no banco :

                [oracle@localhost ~]$ sqlplus hr/hr

                SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 6 14:30:54 2016

                Copyright (c) 1982, 2010, Oracle. All rights reserved.

                Connected to:
                Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options

                ==> OPCIONALMENTE, vc pode indicar o nome do arquivo de trace que vai ser gerado com o ALTER SESSION, assim :

                HR:@orcl:SQL>alter session set tracefile_identifier=’trace_teste_user_hr’;

                Session altered.

                ==> ok, agora é só executar a chamada à package antes de enviar as queries e processar o report – repito, estou simulando via sql*plus, faça de conta de estou na tool de report, na mesma sessão que vai mandar as queries :

                HR:@orcl:SQL>EXEC DBMS_MONITOR.session_trace_enable;

                PL/SQL procedure successfully completed.

                HR:@orcl:SQL>EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>TRUE);

                PL/SQL procedure successfully completed.

                => pronto : agora é so a tool de report mandar os SQLs/queries todos pro banco nessa sessão que estartou o trace :

                HR:@orcl:SQL>select * from employees;

                EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID
                ———– ——————– ————————- ————————- ——————– ——— ———-
                SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
                ———- ————– ———- ————-
                100 Steven King SKING 515.123.4567 17-JUN-87 AD_PRES
                24000 90

                ……

                206 William Gietz WGIETZ 515.123.8181 07-JUN-94 AC_ACCOUNT
                8300 205 110

                107 rows selected.

                HR:@orcl:SQL>select sysdate from dual;

                SYSDATE
                ———
                06-DEC-16

                => automaticamente quando vc desconecta / sai da ferramenta o trace file é fechado :

                HR:@orcl:SQL>exit
                Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
                With the Partitioning, OLAP, Data Mining and Real Application Testing options
                [oracle@localhost ~]$

                => veja que o arquivo de trace contendo todos os waits, binds, sqls com seus planos de execução, etc, foi gerado no diretório de dump E com o nome que indiquei :

                [oracle@localhost ~]$ ls -ltr /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/*trace_teste_user_hr*.trc
                -rw-rw—- 1 oracle oracle 27041 Dec 6 14:51 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3429_trace_teste_user_hr.trc
                [oracle@localhost ~]$

                ==> a Oracle fornece um utilitário que extrai os dados e os formata em tabelinhas agrupadas, esse utilitário se chama tkprof, vou acioná-lo :

                [oracle@localhost ~]$ tkprof /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3429_trace_teste_user_hr.trc result.txt

                TKPROF: Release 11.2.0.2.0 – Development on Tue Dec 6 14:56:30 2016

                Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                [oracle@localhost ~]$ cat result.txt

                TKPROF: Release 11.2.0.2.0 – Development on Tue Dec 6 14:56:30 2016

                Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

                Trace file: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3429_trace_teste_user_hr.trc
                Sort options: default

                ********************************************************************************
                count = number of times OCI procedure was executed
                cpu = cpu time in seconds executing
                elapsed = elapsed time in seconds executing
                disk = number of physical reads of buffers from disk
                query = number of buffers gotten for consistent read
                current = number of buffers gotten in current mode (usually for update)
                rows = number of rows processed by the fetch or execute call
                ********************************************************************************

                SQL ID: 6w4m25bfumhbm Plan Hash: 0

                BEGIN DBMS_MONITOR.session_trace_enable; END;

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 0 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 1
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 1 0.00 0.00 0 0 0 1

                Misses in library cache during parse: 0
                Optimizer mode: ALL_ROWS
                Parsing user id: 240

                Elapsed times include waiting on following events:
                Event waited on Times Max. Wait Total Waited
                —————————————- Waited ———- ————
                SQL*Net message to client 1 0.00 0.00
                SQL*Net message from client 1 18.26 18.26
                ********************************************************************************

                SQL ID: 2sjdx920hrdfn Plan Hash: 0

                BEGIN DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>TRUE); END;

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 1
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 2 0.00 0.00 0 0 0 1

                Misses in library cache during parse: 0
                Optimizer mode: ALL_ROWS
                Parsing user id: 240

                Elapsed times include waiting on following events:
                Event waited on Times Max. Wait Total Waited
                —————————————- Waited ———- ————
                SQL*Net message to client 1 0.00 0.00
                SQL*Net message from client 1 59.69 59.69
                ********************************************************************************

                SQL ID: 718d4y9b3fqtz Plan Hash: 1807565214

                update CRC$_RESULT_CACHE_STATS set NAME = :1, VALUE = :2
                where
                CACHE_ID = :3 and STAT_ID = :4

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 10 11 10
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 2 0.00 0.00 0 10 11 10

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                0 0 0 UPDATE CRC$_RESULT_CACHE_STATS (cr=10 pr=0 pw=0 time=159 us)
                10 10 10 INDEX UNIQUE SCAN CRC$_RCSTATSPK (cr=10 pr=0 pw=0 time=33 us cost=0 size=29 card=1)(object id 5994)

                Elapsed times include waiting on following events:
                Event waited on Times Max. Wait Total Waited
                —————————————- Waited ———- ————
                log file sync 1 0.00 0.00
                ********************************************************************************

                SQL ID: f34thrbt8rjt5 Plan Hash: 1445457117

                select *
                from
                employees

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 0
                Fetch 9 0.00 0.00 0 14 0 107
                ——- —— ——– ———- ———- ———- ———- ———-
                total 11 0.00 0.00 0 14 0 107

                Misses in library cache during parse: 0
                Optimizer mode: ALL_ROWS
                Parsing user id: 240
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                107 107 107 TABLE ACCESS FULL EMPLOYEES (cr=14 pr=0 pw=0 time=2 us cost=3 size=7383 card=107)

                Elapsed times include waiting on following events:
                Event waited on Times Max. Wait Total Waited
                —————————————- Waited ———- ————
                SQL*Net message to client 9 0.00 0.00
                Disk file operations I/O 1 0.00 0.00
                SQL*Net message from client 9 7.77 7.79
                ********************************************************************************

                SQL ID: 7h35uxf5uhmm1 Plan Hash: 1388734953

                select sysdate
                from
                dual

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 0
                Fetch 2 0.00 0.00 0 0 0 1
                ——- —— ——– ———- ———- ———- ———- ———-
                total 4 0.00 0.00 0 0 0 1

                Misses in library cache during parse: 0
                Optimizer mode: ALL_ROWS
                Parsing user id: 240
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                1 1 1 FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)

                Elapsed times include waiting on following events:
                Event waited on Times Max. Wait Total Waited
                —————————————- Waited ———- ————
                SQL*Net message to client 2 0.00 0.00
                SQL*Net message from client 2 2.63 2.63
                ********************************************************************************

                SQL ID: bk33kbnq03qwy Plan Hash: 3802009415

                delete from invalidation_registry$
                where
                regid = :1

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 1 4 1
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 2 0.00 0.00 0 1 4 1

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                0 0 0 DELETE INVALIDATION_REGISTRY$ (cr=1 pr=0 pw=0 time=183 us)
                1 1 1 INDEX RANGE SCAN I_INVALIDATION_REGISTRY$ (cr=1 pr=0 pw=0 time=21 us cost=1 size=5 card=1)(object id 5386)

                ********************************************************************************

                SQL ID: 6cqsht9pn1wc7 Plan Hash: 959609949

                select queryid,
                from
                List from chnf$_queries where queryid IN (select unique(queryId) from
                chnf$_reg_queries where regid = :1)

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 0
                Fetch 1 0.00 0.00 0 1 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 3 0.00 0.00 0 1 0 0

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                0 0 0 MERGE JOIN SEMI (cr=1 pr=0 pw=0 time=1 us cost=2 size=78 card=1)
                0 0 0 TABLE ACCESS BY INDEX ROWID CHNF$_QUERIES (cr=1 pr=0 pw=0 time=1 us cost=0 size=65 card=1)
                0 0 0 INDEX FULL SCAN I2_CHNF$_QUERIES (cr=1 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 5419)
                0 0 0 SORT UNIQUE (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
                0 0 0 VIEW VW_NSO_1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=13 card=1)
                0 0 0 TABLE ACCESS BY INDEX ROWID CHNF$_REG_QUERIES (cr=0 pr=0 pw=0 time=0 us cost=1 size=26 card=1)
                0 0 0 INDEX RANGE SCAN I2_CHNF$_REG_QUERIES (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 5412)

                ********************************************************************************

                SQL ID: 9a4sm4kdwmfuj Plan Hash: 4154967531

                delete from chnf$_reg_queries
                where
                regid = :1

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 1 0 0
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 2 0.00 0.00 0 1 0 0

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                0 0 0 DELETE CHNF$_REG_QUERIES (cr=1 pr=0 pw=0 time=4 us)
                0 0 0 INDEX RANGE SCAN I2_CHNF$_REG_QUERIES (cr=1 pr=0 pw=0 time=2 us cost=1 size=26 card=1)(object id 5412)

                ********************************************************************************

                SQL ID: 8ngh5ms3xddy6 Plan Hash: 3890562903

                delete from CRC$_RESULT_CACHE_STATS
                where
                CACHE_ID = :1

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 1 31 10
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 2 0.00 0.00 0 1 31 10

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                0 0 0 DELETE CRC$_RESULT_CACHE_STATS (cr=1 pr=0 pw=0 time=158 us)
                10 10 10 INDEX RANGE SCAN CRC$_RCSTATSPK (cr=1 pr=0 pw=0 time=16 us cost=1 size=8 card=1)(object id 5994)

                ********************************************************************************

                SQL ID: 0vv5mmymsz3p6 Plan Hash: 94788420

                select user#
                from
                reg$ where location_name = :1 and (subscription_name != :2 or namespace !=
                :3)

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 0 0 0
                Fetch 1 0.00 0.00 0 2 0 1
                ——- —— ——– ———- ———- ———- ———- ———-
                total 3 0.00 0.00 0 2 0 1

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                1 1 1 TABLE ACCESS FULL REG$ (cr=2 pr=0 pw=0 time=27 us cost=2 size=216 card=6)

                ********************************************************************************

                SQL ID: gmm593quv2jyz Plan Hash: 4090815212

                delete from reg$
                where
                subscription_name = :1 and namespace = :2

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 1 4 1
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 2 0.00 0.00 0 1 4 1

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                0 0 0 DELETE REG$ (cr=1 pr=0 pw=0 time=686 us)
                1 1 1 INDEX RANGE SCAN REG$_IDX (cr=1 pr=0 pw=0 time=23 us cost=1 size=38 card=1)(object id 85504)

                ********************************************************************************

                SQL ID: f711myt0q6cma Plan Hash: 0

                insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,
                userhost,terminal,action#,returncode, logoff$lread,logoff$pread,
                logoff$lwrite,logoff$dead, logoff$time,comment$text,spare1,clientid,
                sessioncpu,proxy$sid,user$guid, instance#,process#,auditid,dbid)
                values
                (:1,:2,:3,SYS_EXTRACT_UTC(SYSTIMESTAMP), :4,:5,:6,:7,:8, :9,:10,:11,
                :12, cast(SYS_EXTRACT_UTC(systimestamp) as date),:13,:14,:15,:16,:17,
                :18, :19,:20,:21,:22)

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 1 0.00 0.00 0 0 0 0
                Execute 1 0.00 0.00 0 1 2 1
                Fetch 0 0.00 0.00 0 0 0 0
                ——- —— ——– ———- ———- ———- ———- ———-
                total 2 0.00 0.00 0 1 2 1

                Misses in library cache during parse: 0
                Optimizer mode: CHOOSE
                Parsing user id: SYS (recursive depth: 1)
                Number of plan statistics captured: 1

                Rows (1st) Rows (avg) Rows (max) Row Source Operation
                ———- ———- ———- —————————————————
                0 0 0 LOAD TABLE CONVENTIONAL (cr=1 pr=0 pw=0 time=78 us)

                ********************************************************************************

                OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 3 0.00 0.00 0 0 0 0
                Execute 4 0.00 0.00 0 0 0 2
                Fetch 11 0.00 0.00 0 14 0 108
                ——- —— ——– ———- ———- ———- ———- ———-
                total 18 0.00 0.00 0 14 0 110

                Misses in library cache during parse: 0

                Elapsed times include waiting on following events:
                Event waited on Times Max. Wait Total Waited
                —————————————- Waited ———- ————
                SQL*Net message to client 14 0.00 0.00
                SQL*Net message from client 14 59.69 88.38
                log file sync 2 0.00 0.00
                Disk file operations I/O 1 0.00 0.00

                OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

                call count cpu elapsed disk query current rows
                ——- —— ——– ———- ———- ———- ———- ———-
                Parse 8 0.00 0.00 0 0 0 0
                Execute 8 0.00 0.00 0 15 52 23
                Fetch 2 0.00 0.00 0 3 0 1
                ——- —— ——– ———- ———- ———- ———- ———-
                total 18 0.00 0.00 0 18 52 24

                Misses in library cache during parse: 0

                Elapsed times include waiting on following events:
                Event waited on Times Max. Wait Total Waited
                —————————————- Waited ———- ————
                log file sync 1 0.00 0.00

                4 user SQL statements in session.
                8 internal SQL statements in session.
                12 SQL statements in session.
                ********************************************************************************
                Trace file: /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3429_trace_teste_user_hr.trc
                Trace file compatibility: 11.1.0.7
                Sort options: default

                1 session in tracefile.
                4 user SQL statements in trace file.
                8 internal SQL statements in trace file.
                12 SQL statements in trace file.
                12 unique SQL statements in trace file.
                550 lines in trace file.
                88 elapsed seconds in trace file.

                [oracle@localhost ~]$

                ===>> é claro, no exemplo acima usei tudo default : se vc executar um tkprof sem argumentos vc verá os argumentos que permitem Ordenar as tabelas geradas , excluir alguns resultados, etc….

                Para mais detalhes da análise das tabelas de resultado do tkprof, dá uma lida no manual de Tuning, veja as boas refs (como http://facedba.blogspot.com.br/2013/11/sql-trace-10046-analysis-of-tkprof.html e http://hungrydba.com/reading_tkprof_trace_files.html) e os bons livros de referência, como o “Oracle SQL High-Performance Tuning” do autor Guy Harrison ou o “Effective Oracle by Design” do autor Tom Kyte…

                []s

                Chiappa

                #108545
                Avatar de José Laurindo ChiappaJosé Laurindo Chiappa
                Moderador

                  Ah, adicionalmente xo te dar a dica : para analisar/validar planos de execução de um SQL, além da Documentação e dos livros e sites indicados, um livro Excelente que quero indicar é o “Cost-Based Oracle Fundamentals (Expert’s Voice)”, do autor Jonathan Lewis – imho essa é a “Bíblia” para quem está tunando/analisando SQLs baseados em Cost-Based Optimizer, como é o default….

                  []s

                  Chiappa

                  #108546
                  Avatar de dfguiraldellidfguiraldelli
                  Participante

                    Ola, tudo bem?

                    então, eu cuido dos servidores da empresa, meu conhecimento em Oracle é básico. O sistema é de uma empresa terceira. Como estamos tendo problema com esse relatório me pediram essas informações. Não tenho acesso ao código da consulta.

                    Como eu faria o Trace usando o SID e Serial?

                    Valeu!!!

                    #108547
                    Avatar de dfguiraldellidfguiraldelli
                    Participante

                      Detalhe, é um usuário no Oracle que é usado pela aplicação.

                      #108548
                      Avatar de José Laurindo ChiappaJosé Laurindo Chiappa
                      Moderador

                        Bom, esses procedimentos que estamos passando não são tão complexos mas se vc não tem segurança por não trabalhar como DBA, talvez seja o caso de contratar um especialista pra te ajudar aí enquanto vc não faz seu treinamento… OK, se realmente nem vc nem nenhum dos desenvolvedores tem acesso ao fonte do report aí fica mais difícil, mas tá bem – se não dá, não dá…

                        Antes de mostrar como se faz pra achar o SID e o SERIAL de uma sessão, uma outra possibilidade é vc programar para que o próprio banco abra um trace quando o usuário se conectar : isso dá super certo ** SE ** tiver como vc mais ou menos combinar com os usuários da aplicação pra rodar o report numa hora que existam poucas (ou talvez NENHUMA) sessão mais aberta com esse usuário de banco…. Pra isso, vc conecta como SYSDBA e cria um trigger que inicie o trace quando o usuário em questão conectar – exemplo com o usuário HR (adapte/altere para o usuário de banco que a tua aplicação usa) :

                        SYS:AS SYSDBA@orcl:SQL>create or replace trigger HR_Startsqltracing after logon on HR.schema
                        begin
                        execute immediate ‘alter session set tracefile_identifier=’ || chr(39) || ‘trace_teste_user_hr’ || chr(39);
                        execute immediate ‘alter session set max_dump_file_size=unlimited’;
                        execute immediate ‘alter session set events ”10046 trace name context forever, level 16”’;
                        end;
                        /

                        Trigger created.

                        SYS:AS SYSDBA@orcl:SQL>

                        ==> OK, agora a tool de programação conecta no banco, a trigger dispara automaticamente :

                        [oracle@localhost ~]$ sqlplus hr/hr

                        SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 6 17:25:26 2016

                        Copyright (c) 1982, 2010, Oracle. All rights reserved.

                        Connected to:
                        Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options

                        HR:@orcl:SQL>select * from departments;

                        DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID


                               10 Administration                        200        1700
                               20 Marketing                             201        1800
                               30 Purchasing                            114        1700
                               40 Human Resources                       203        2400
                               50 Shipping                              121        1500
                               60 IT                                    103        1400
                               70 Public Relations                      204        2700
                               80 Sales                                 145        2500
                               90 Executive                             100        1700
                              100 Finance                               108        1700
                              110 Accounting                            205        1700
                              120 Treasury                                         1700
                              130 Corporate Tax                                    1700
                              140 Control And Credit                               1700
                              150 Shareholder Services                             1700
                              160 Benefits                                         1700
                              170 Manufacturing                                    1700
                              180 Construction                                     1700
                              190 Contracting                                      1700
                              200 Operations                                       1700
                              210 IT Support                                       1700
                              220 NOC                                              1700
                              230 IT Helpdesk                                      1700
                              240 Government Sales                                 1700
                              250 Retail Sales                                     1700
                              260 Recruiting                                       1700
                              270 Payroll                                          1700
                             9999 depto 9999
                        

                        28 rows selected.

                        HR:@orcl:SQL>exit
                        Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options
                        [oracle@localhost ~]$

                        ==> automagicamente quando a sessão desconectou o trace file foi gerado :

                        [oracle@localhost ~]$ ls -ltr /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/trace_teste_user_h.trc
                        -rw-rw—- 1 oracle oracle 139888 Dec 6 17:25 /home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3184_trace_teste_user_hr.trc
                        [oracle@localhost ~]$

                        ==> pronto , agora seria só fazer o tkprof do arquivo…. okdoc ??

                        CASO por qualquer motivo vc Também não possa criar uma trigger de logon, aí sim vc vai ter que apelar por método mais grosseiro, que é imediatamente após o report conectar no banco e começar a processar, vc identificar o SID e o SERIAL# pesquisando na V$SESSION…. No exemplo abaixo, quero localizar o SID e o SERIAL de uma conexão via sqlplus com um usuário SCOTT, pra isso conecto numa tool cliente como SYSDBA e pesquiso a V$SESSION :

                        [oracle@localhost ~]$ sqlplus sys/oracle as sysdba

                        SQL*Plus: Release 11.2.0.2.0 Production on Tue Dec 6 17:34:36 2016

                        Copyright (c) 1982, 2010, Oracle. All rights reserved.

                        Connected to:
                        Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – Production
                        With the Partitioning, OLAP, Data Mining and Real Application Testing options

                        SYS:AS SYSDBA@orcl:SQL>alter session set NLS_DATE_FORMAT=’dd/mm/yyyy hh24:mi:ss’;

                        Session altered.

                        SYS:AS SYSDBA@orcl:SQL>select sid, serial#, program, module, osuser, machine, terminal, action, port, logon_time from v$session where username=’SCOTT’;

                        SID SERIAL# PROGRAM MODULE OSUSER MACHINE TERMINAL ACTION PORT LOGON_TIME


                        30       31 sqlplus@localhost.localdomain (TNS V1-V3) SQL*Plus       oracle    localhost.localdomain  pts/1                0 06/12/2016 17:32:53
                        47       53 SQL Developer                             SQL Developer  jlchiappa DELL15RSE                       unknown 31676 06/12/2016 15:45:10
                        

                        ==> analisando o resultado da pesquisa (principalmente a data exata do logon), vejo que é o SID 30 e SERIAL# 31, seto o evento de trace nessa sessão, remotamente :

                        SYS:AS SYSDBA@orcl:SQL>exec dbms_system.set_ev(30, 31, 10046, 16, ”);

                        PL/SQL procedure successfully completed.

                        SYS:AS SYSDBA@orcl:SQL>

                        ==> aí automaticamente quando a sessão de SID=30 serial#=31 desconectar o trace file será gerado, só fazer o tkprof nele….

                        []s

                        Chiappa

                        #108550
                        Avatar de dfguiraldellidfguiraldelli
                        Participante

                          Boa tarde Chiappa,

                          só tenho a agradecer pela ajuda.
                          Vou fazer fora do horário comercial usando a Trigger. Mas vou fazer com a opção de SID e Serial também para poder aprender.

                          Já estou pesquisando para comprar o livro que você indicou e pretendo estudar para aumentar o conhecimento da ferramenta.

                          #108556
                          Avatar de José Laurindo ChiappaJosé Laurindo Chiappa
                          Moderador

                            Blz, fico contente de poder ter ajudado… Veja que não é só um livro, penso que TODOS os que indiquei vão ser úteis e necessários pra vc, cada um deles em um tópico diferente…

                            []s

                            Chiappa

                          Visualizando 12 posts - 1 até 12 (de 12 do total)
                          • Você deve fazer login para responder a este tópico.
                          plugins premium WordPress