Menu de navegação

[dfguiraldelliParticipante]

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.

[airoospParticipante]

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

[dfguiraldelliParticipante]

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.

[airoospParticipante]

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

[dfguiraldelliParticipante]

Airton,

muito obrigado pela ajuda.

Vou fazer conforme você passou.

Mais uma vez obrigado.

[José Laurindo ChiappaModerador]

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

[José Laurindo ChiappaModerador]

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

[dfguiraldelliParticipante]

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!!!

[dfguiraldelliParticipante]

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

[José Laurindo ChiappaModerador]

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

[dfguiraldelliParticipante]

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.

[José Laurindo ChiappaModerador]

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

[Autor]

Posts