http://www.oracle.com/technology/products/database/application_express/html/3.1_and_xe.html (interessante)
http://www.oracle.com/technology/products/database/application_express/download.html
http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/install.htm#CHDHIJGE
http://download.oracle.com/docs/cd/E10513_01/doc/install.310/e10496/post_inst.htm#BHAFCGBC
http://download.oracle.com/docs/cd/B19306_01/install.102/b14312/gblsupp.htm
Este script que funciona para linux e windows calcula o IO dispendido por device.
SELECT nome.short_name “Device”,
sum(phyrds) “Leituras”,
sum(phywrts) “Escritas”,
sum((phywrts+phyrds)) “Total”,
s.segundos “Tempo(s)”,
sum(round((phywrts+phyrds)/(s.segundos),2)) “IO/s”
FROM
(SELECT /*+ RULE */
file# file_id,
phywrts,
phyrds,
readtim/10 readtim,
writetim/10 writetim,
0 contents
FROM v$filestat f
UNION ALL
SELECT /*+ RULE */
file# file_id,
phywrts,
phyrds,
readtim readtim,
writetim writetim,
1 contents
FROM v$tempstat) io,
(SELECT /*+ RULE */
ts#,
d.file# file_id,
d.name file_name,
SUBSTR(d.name, 1, INSTR(d.name, ”, 2)+INSTR(d.name,’/’,2)-1) short_name
FROM v$datafile d
UNION ALL
SELECT /*+ RULE */
ts#,
d.file# + p.value file_id,
d.name file_name,
SUBSTR(d.name, 1, INSTR(d.name, ”, 2)+INSTR(d.name,’/’,2)-1) short_name
FROM v$tempfile d, v$parameter p
WHERE d.blocks >0
AND p.name = ‘db_files’) nome,
(SELECT round(((sysdate-startup_time)*86400)) segundos
from v$instance) s
WHERE io.file_id = nome.file_id
GROUP BY nome.short_name, s.segundos
Use sempre a $ORACLE_HOME/rdbms/admin/ultrp.sql
sqlplus / as sysdba
sql>@$ORACLE_HOME/rdbms/admin/ultrp.sql;
Conectado a:
Oracle Database 10g Express Edition Release 10.2.0.1.0 – Production
SQL> @C:oraclexeapporacleproduct10.2.0serverRDBMSADMINutlrp.sql
TIMESTAMP
—————————————————————————–
COMP_TIMESTAMP UTLRP_BGN 2008-03-15 16:01:59
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like ‘UTL_RECOMP_SLAVE_%’;
DOC>#
Procedimento PL/SQL concluφdo com sucesso.
TIMESTAMP
—————————————————————————–
COMP_TIMESTAMP UTLRP_END 2008-03-15 16:02:05
Procedimento PL/SQL concluφdo com sucesso.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
——————-
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
—————————
0
Procedimento PL/SQL concluφdo com sucesso.
AdvertΩncia: VocΩ nπo estß mais conectado ao ORACLE.
SQL>
Conectado.
SQL> select count(*) from user_objects;
COUNT(*)
———-
195
SQL> select count(*) from user_objects where status = ‘INVALID’;
COUNT(*)
———-
0
SQL> select count(*) from user_objects where status = ‘INVALID’;
COUNT(*)
———-
0
SQL>
Como todos sabem, as funções de agrupamento não levam em consideração os atributos que estão com valor NULL nas linhas. Assim fizemos um teste para comprovar a atuação do count(*). O resultado era esperado que quando utilizassemos a função de agrupamento count(*) ele entao iria verificar que a linha estava nula e nao contabilizasse essa linha. Mas o Oracle armazena duas pseudo colunas que são: ROWID e ROWNUM, e quando executo count(*) estas colunas sao contempladas para efeito de calculo e o retorno de total de linhas inseridas com valor nulo é retornado.
SQL> create table TOTO (CODIGO NUMBER);
Tabela criada.
SQL> insert into TOTO VALUES(NULL);
1 linha criada.
SQL> /
1 linha criada.
SQL> /
1 linha criada.
SQL> /
1 linha criada.
SQL> /
1 linha criada.
SQL> select * from toto;
CODIGO
———-
SQL> select count(*) from toto;
COUNT(*)
———-
5
SQL>
SQL> select count(codigo) from toto;
COUNT(CODIGO)
————-
0
SQL> select rowid, rownum, codigo from toto;
ROWID ROWNUM CODIGO
—————— ———- ———-
AAAMrXAABAAAOpaAAA 1
AAAMrXAABAAAOpaAAB 2
AAAMrXAABAAAOpaAAC 3
AAAMrXAABAAAOpaAAD 4
AAAMrXAABAAAOpaAAE 5
SQL>
Através da consulta abaixo podemos ter as consultas que estao mais demoradas.
select sql_text, elapsed_time/1000000 elapsed_sec, executions, disk_reads, buffer_gets
from v$sqlarea
order by elapsed_time desc;
No arquivo de configuração httpd.conf existem vários parametros dentre eles podemos destacar os parametros:
No UNIX e Linux:
StartServers
MaxClients
MaxSpareServers / MinSpareServers
MaxRequestsPerChild
No Windows NT:
ThreadsPerChild
Em todos os sistemas operacionais:
KeepAlive
KeepAliveTimeout
MaxKeepAliveRequests
Para cada um deles, os parametros adequados são:
StartServers: configura o numero de processos filhos criados quando o Oracle HTTP Server é iniciado. O default é 5.
MaxClients: Limits the number of requests that are handled simultaneously. The default value is 150.
MaxRequestsPerChild: Each child version handles this number of requests and then dies. If the value is 0, the process lasts until the machine is rebooted. The default value is 30.
MaxSpareServers: No more than this number of child servers should be left running and unused. The default value is 10.
MinSpareServers: At least this number of child servers should be kept functional. The default value is 5. If fewer than this number exist, new ones are started at an increasing rate each second until the rate defined by MAX_SPAWN_RATE is reached (default: 32).
For example, if the number of requests to be handled simultaneously is 100, and the number of child server processes created is 10, then the maximum number of requests to be handled per child can be 10
Dicas interessantes sobre o OHS
ThreadsPerChild: This directive sets the number of threads created by each child process. The child creates these threads at startup and never creates more.
If you are using an MPM like mpm_winnt, where there is only one child process, this number should be high enough to handle the entire load of the server.
If you are using an MPM like mpm_worker, where there are multiple child processes, the total number of threads should be high enough to handle the common load on the server.
MinSpareThreads: This value is only used with mpm_worker. Since Oracle Access Manager plug-in initialization is deferred until the first request, there is minimal advantage of keeping high value for this directive.
However, it is useful to keep this parameter as high as possible.
MaxSpareThreads: This value is only used with mpm_worker. The value for MaxSpareThreads must be greater than or equal to the sum of MinSpareThreads and ThreadsPerChild or the Apache HTTP Server automatically corrects it.
Recommendation: Keep the value high. For a dedicated server this will not be a problem.
MaxSpareServers: With Apache v2, this is used only with the prefork MPM model. To preserve as much state as possible in the server, set the MaxSpareServers to a high value. Setting this value to the maximum of 255 keeps all Apache worker-processes available indefinitely, but it does not provide an opportunity for worker-process recycling during low-load periods.
MinSpareServers: With Apache v2, this is used only with the prefork MPM model. Since Oracle Access Manager plug-in initialization is deferred until the first request, using a high value for the MinSpareServers parameter provides minimal advantage.
However, it is useful to keep this parameter as high as possible. For dedicated Web server systems, this should pose no great burden.
MaxClients: With IHS v2 and the worker MPM, MaxClients restricts the total number of threads that will be available to serve clients.
For hybrid MPMs, the default value is 16 (ServerLimit) multiplied by a value of 25 (ThreadsPerChild). To increase MaxClients to a value that requires more than 16 processes, you must also raise ServerLimit.
Os valores abaixo sao os recomendados para a maioria dos sites:
Recommended values that are sufficient for most sites:
MinSpareServers 5
MaxSpareServers 10
StartServers 5
Na grande maioria das instalacoes a recomendaçao é definir keepalive como OFF
KeepAlive OFF
Para usar o dcmctl teremos que incluir a pasta, ORACLE_HOME/dcm/bin na variavel PATH e com ele podemos:
Podemos também, para fins de criação de novos scripts chamar o dcmctl a partir de um modo em lote.
dcmctl shell -f scriptdcm.dcm
Conteudo do arquivo scriptdcm.dcm:
createcomponent -ct oc4j -co oc4j_novo
O opmnctl é um comando de linha para o OPMN. Utilize sempre o Application Server Control ou o comando de linha, opmn para iniciar e parar os processos do OAS.
Exemplos de comandos opmn:
O opmn se encontra na pasta ORACLE_HOME/opmn/bin lembre-se de adicionar este caminho a sua variavel de ambiente PATH.
export PATH=$PATH:$ORACLE_HOME/opmn/bin
O OPMN é um gerenciador de processos que centraliza certas operações sobre os demais processos do servidor de aplicação. O OPMN gerencia todos os compontentes do servidor de aplicacao, com exceção do repositório de metadados.
O OPMN é composto por:
O PM usa o ONS para:
Outro utilitário interessante no application server control é o printlogs, que podemos com ele consultar e mostrar o conteúdo do repositório de logs do application server control. Assim, podemos até mesmo carrega-lo através de scripts para que de tempos em tempos o monitoramento seja automatizado. O Log Viewer está presente na interface html do application server control, onde voce poderá acessa-lo para que assim, você consiga visualizar os ultimos logs mais recentes encontrados nas suas aplicacoes ou processos do servidor.