Liste des plus grandes consommations CPU

Ce script SQL permet d'afficher les opérations les plus gourmandes en terme de CPU
 
SET echo off
SET feedback off
SET linesize 512 

1. prompt ----------------------------------
2. prompt - plus grandes consommation CPU --
3. prompt ----------------------------------
4.  
5. COLUMN sid format 999 heading "SID"
6. COLUMN username format a20 heading "Utilisateur"
7. COLUMN command format a20 heading "Commande"
8. COLUMN osuser format a20 heading "Utilisateur OS"
9. COLUMN process format a20 heading "Processus OS"
10. COLUMN machine format a20 heading "Machine"
11. COLUMN value format 99,999 heading "Temps CPU"
12.  
13. SELECT
14. s.sid sid,
15. s.username username,
16. UPPER(DECODE(command,
17. 1,'Create Table',2,'Insert',3,'Select',
18. 4,'Create Cluster',5,'Alter Cluster',6,'Update',
19. 7,'Delete', 8,'Drop Cluster', 9,'Create Index',
20. 10,'Drop Index', 11,'Alter Index', 12,'Drop Table',
21. 13,'Create Sequencfe', 14,'Alter Sequence', 15,'Alter Table',
22. 16,'Drop Sequence', 17,'Grant', 18,'Revoke',
23. 19,'Create Synonym', 20,'Drop Synonym', 21,'Create View',
24. 22,'Drop View', 23,'Validate Index', 24,'Create Procedure',
25. 25,'Alter Procedure', 26,'Lock Table', 27,'No Operation',
26. 28,'Rename', 29,'Comment', 30,'Audit',
27. 31,'NoAudit', 32,'Create Database Link', 33,'Drop Database Link',
28. 34,'Create Database', 35,'Alter Database', 36,'Create Rollback Segment',
29. 37,'Alter Rollback Segment', 38,'Drop Rollback Segment', 39,'Create Tablespace',
30. 40,'Alter Tablespace', 41,'Drop Tablespace', 42,'Alter Sessions',
31. 43,'Alter User', 44,'Commit', 45,'Rollback',
32. 46,'Savepoint', 47,'PL/SQL Execute', 48,'Set Transaction',
33. 49,'Alter System Switch Log', 50,'Explain Plan', 51,'Create User',
34. 52,'Create Role', 53,'Drop User', 54,'Drop Role',
35. 55,'Set Role', 56,'Create Schema', 57,'Create Control File',
36. 58,'Alter Tracing', 59,'Create Trigger', 60,'Alter Trigger',
37. 61,'Drop Trigger', 62,'Analyze Table', 63,'Analyze Index',
38. 64,'Analyze Cluster', 65,'Create Profile', 66,'Drop Profile',
39. 67,'Alter Profile', 68,'Drop Procedure', 69,'Drop Procedure',
40. 70,'Alter Resource Cost', 71,'Create Snapshot Log', 72,'Alter Snapshot Log',
41. 73,'Drop Snapshot Log', 74,'Create Snapshot', 75,'Alter Snapshot',
42. 76,'Drop Snapshot', 79,'Alter Role', 85,'Truncate Table',
43. 86,'Truncate Cluster', 88,'Alter View', 91,'Create Function',
44. 92,'Alter Function', 93,'Drop Function', 94,'Create Package',
45. 95,'Alter Package', 96,'Drop Package', 97,'Create Package Body',
46. 98,'Alter Package Body', 99,'Drop Package Body')) command,
47. s.osuser osuser,
48. s.machine machine,
49. s.process process,
50. t.value value
51. FROM
52. v$session s,
53. v$sesstat t,
54. v$statname n
55. WHERE
56. s.sid = t.sid
57. AND
58. t.statistic# = n.statistic#
59. AND
60. n.name = 'CPU used by this session'
61. AND
62. t.value > 0
63. AND
64. audsid > 0
65. ORDER BY
66. t.value DESC;