Funções Analíticas do Oracle (Rank)

Bom dia.

Hoje precisei efetuar um select  e listar os top 10 de uma tabela.

Me lembrei que já tinha utilizado isso em uma procedure bem antiga. Só não lembrava com havia feito, então devido a isso eu decidi estudar novamente sobre esta função chamada RANK.

Bom o que eu desejava era coletar os top 10 sql’s da v$sql. então vamos em busca do que queremos.

O melhor exemplo que encontrei na internet foi este

select … analytic-function (…) over (partition by …) ..
select … analytic-function (…) over (order by …) ..
select … analytic-function (…) over (partition by … order by …) ..

Porem quais os valores que posso colocar no campo (analytic-function)?

Tipos de funções analíticas.
Estas funçoes consistem em seis determinadas funções, são elas:
Rank, dense_rank, row_number, ntile, percent_rank e cume_dist.

Porem vou falar apenas de “rank”

Basicamente você deverá fazer um select com um subselect na clausula from. Criando assim uma nova tabela.
Observe:

select t.cpu_time,t.sql_id,rank_cpu_time from
(select sql_id,CPU_TIME,rank() over(order by (CPU_TIME) desc) rank_cpu_time from v$sql) t
 where t.rank_cpu_time <=10
/

CPU_TIME SQL_ID RANK_CPU_TIME
---------- ------------- -------------
4482727521 65vbsw4sm55h8 1
1621341540 2q4g2nvrrpk00 2
 600729614 cxzmpgfk7cwh4 3
 529071569 dj323cckk5kj2 4
 448367823 8v6fbnhachy3k 5
 240868287 982jkg5yhtp4h 6
 235849090 4yffxmsh6n03q 7
 225380750 cqwfdp2kgp1y6 8
 217441016 g6abmb7dqkjp5 9
 196707015 3x08fx3p8ct32 10

Neste caso eu estou listando os 10 maiores SQL_ID’s que ocorreram na base.

Podemos fazer um rank por agrupamentos também, no caso a seguir será qual sql_id que executou mais vezes na base de dados.

select t.* from
 (select count(sql_id),sql_id,rank() over(order by count(sql_id) desc) rank_cpu_time
 from v$sql group by sql_id) t
 where t.rank_cpu_time <=10

COUNT(SQL_ID) SQL_ID        RANK_CPU_TIME
------------- ------------- -------------
           14 8shxk3nr2kuty             1
            8 fsdh8dn2tut06             2
            7 3ktacv9r56b51             3
            7 8rgw5q94paqsg             3
            7 7ng34ruy5awxq             3
            7 c32pzbvhr5cyj             3
            7 1gu8t96d0bdmu             3
            6 5tkhvsjxc30x0             8
            6 74anujtt8zw4h             8
            6 0wru3t0nnw5ny             8
            6 8swypbbr0m372             8
            6 8kprhp28v9yyd             8
            6 6szwtf0ncc8jh             8
            6 7burv2u085x0v             8
            6 36g2n4sj5vtbn             8
            6 1gfaj4z5hn1kf             8
            6 1sht4g2x631sc             8

Observe que no primeiro rank que fiz eu setei mais valores e setei uma clausula where, simplesmente porque os dados necessários eram outros.
Um eu quis ver qual mais demorou e o outro quis ver qual mais vezes aconteceu. Observe Também que como há empate de quantas vezes ocorreu cada sql_id ele trouxe todos os resultados do rank 8.

Para testar estes valores basta rodar os seguintes comandos.
No teste para verificar o CPU time.

SQL> select sql_id,cpu_time from v$sql where sql_id='65vbsw4sm55h8';

SQL_ID          CPU_TIME
------------- ----------
65vbsw4sm55h8 4482727521

No segundo teste para verificar a quantidade de vezes executadas.

SQL> select count(sql_id),sql_id from v$sql 
where sql_id='8shxk3nr2kuty' group by sql_id;

COUNT(SQL_ID) SQL_ID
------------- -------------
           14 8shxk3nr2kuty

Porem estes comando acima farão um full scan na tabela e não trarão apenas os 10 maiores valores, conforme necessitamos.

Muitas vezes a aplicação já faz esta filtragem para o usuário final, porem pra isso ela faria um full scan na tabela, esta função de rank já faz isso e com um custo bem menor ao banco de dados.

Isto se aplica para 9i, 10G e 11G.

Referencias:

http://www.adp-gmbh.ch/ora/sql/analytical/index.html

http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions141.htm#i1269223

%name Funções Analíticas do Oracle (Rank)

Autor: Leandro Lana

Trabalho com banco de dados Oracle desde 2006, já trabalhei com as plataformas 9i, 10G, 11G e 12C.

Trabalhando atualmente como consultor Oracle na MigraTI Soluções em TI como administrador de banco de dados Oracle, SQL-Server e DB2.

Contato: leandro.lana@migrati.com.br

Fone: (47) 9191-6052 / (47) 3328 0996

Certificações:

OCA 10G.

OCP 10G.

OCE Linux.

OCE RAC/Cluster.

MCP SQL-Server 2008.

MCITP SQL-Server 2008.

DB2 Fundamentals.