Home » RDBMS Server » Performance Tuning » CPU consumption calculation for sqls (Oracle 11.2.0.4, AIX 6.1)
CPU consumption calculation for sqls [message #634830] Mon, 16 March 2015 10:19 Go to next message
saurabhshukladba
Messages: 5
Registered: February 2015
Junior Member
Hi

If AWR report was extracted for 10 hours (interval) eg. begin time 9AM and end time 7PM.
Host has only one database hosted and total CPUs are 32.

Below is the output for SQL ordered by CPU Time :

Quote:

Elapsed CPU Elap per % Total
Time (s) Time (s) Executions Exec (s) DB Time SQL Id
---------- ---------- ------------ ---------- ------- -------------
4,311 3,253 419,821 0.0 28.8 17abced4217a9


I would like to how much CPU was consumed during that time. How the calculation is done for CPU consumption?

Regards
Saurabh
Re: CPU consumption calculation for sqls [message #634841 is a reply to message #634830] Mon, 16 March 2015 11:27 Go to previous message
Michel Cadot
Messages: 65832
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From you previous topic:

Lalit Kumar B wrote on Tue, 03 February 2015 09:04
Welcome to the forum!

Please read and follow the OraFAQ Forum Guide and How to use [code] tags, to enable us to help you.
...


BlackSwan wrote on Tue, 03 February 2015 14:57
...
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


So, please read the links and use CODE tags and no QUOTE ones for code.

SQL> desc DBA_HIST_SQLSTAT
 Name                             Null?    Type
 -------------------------------- -------- ----------------------
 SNAP_ID                          NOT NULL NUMBER
 DBID                             NOT NULL NUMBER
 INSTANCE_NUMBER                  NOT NULL NUMBER
 SQL_ID                           NOT NULL VARCHAR2(13)
 PLAN_HASH_VALUE                  NOT NULL NUMBER
 OPTIMIZER_COST                            NUMBER
 OPTIMIZER_MODE                            VARCHAR2(10)
 OPTIMIZER_ENV_HASH_VALUE                  NUMBER
 SHARABLE_MEM                              NUMBER
 LOADED_VERSIONS                           NUMBER
 VERSION_COUNT                             NUMBER
 MODULE                                    VARCHAR2(64)
 ACTION                                    VARCHAR2(64)
 SQL_PROFILE                               VARCHAR2(64)
 FORCE_MATCHING_SIGNATURE                  NUMBER
 PARSING_SCHEMA_ID                         NUMBER
 PARSING_SCHEMA_NAME                       VARCHAR2(30)
 PARSING_USER_ID                           NUMBER
 FETCHES_TOTAL                             NUMBER
 FETCHES_DELTA                             NUMBER
 END_OF_FETCH_COUNT_TOTAL                  NUMBER
 END_OF_FETCH_COUNT_DELTA                  NUMBER
 SORTS_TOTAL                               NUMBER
 SORTS_DELTA                               NUMBER
 EXECUTIONS_TOTAL                          NUMBER
 EXECUTIONS_DELTA                          NUMBER
 PX_SERVERS_EXECS_TOTAL                    NUMBER
 PX_SERVERS_EXECS_DELTA                    NUMBER
 LOADS_TOTAL                               NUMBER
 LOADS_DELTA                               NUMBER
 INVALIDATIONS_TOTAL                       NUMBER
 INVALIDATIONS_DELTA                       NUMBER
 PARSE_CALLS_TOTAL                         NUMBER
 PARSE_CALLS_DELTA                         NUMBER
 DISK_READS_TOTAL                          NUMBER
 DISK_READS_DELTA                          NUMBER
 BUFFER_GETS_TOTAL                         NUMBER
 BUFFER_GETS_DELTA                         NUMBER
 ROWS_PROCESSED_TOTAL                      NUMBER
 ROWS_PROCESSED_DELTA                      NUMBER
 CPU_TIME_TOTAL                            NUMBER
 CPU_TIME_DELTA                            NUMBER
 ELAPSED_TIME_TOTAL                        NUMBER
 ELAPSED_TIME_DELTA                        NUMBER
 IOWAIT_TOTAL                              NUMBER
 IOWAIT_DELTA                              NUMBER
 CLWAIT_TOTAL                              NUMBER
 CLWAIT_DELTA                              NUMBER
 APWAIT_TOTAL                              NUMBER
 APWAIT_DELTA                              NUMBER
 CCWAIT_TOTAL                              NUMBER
 CCWAIT_DELTA                              NUMBER
 DIRECT_WRITES_TOTAL                       NUMBER
 DIRECT_WRITES_DELTA                       NUMBER
 PLSEXEC_TIME_TOTAL                        NUMBER
 PLSEXEC_TIME_DELTA                        NUMBER
 JAVEXEC_TIME_TOTAL                        NUMBER
 JAVEXEC_TIME_DELTA                        NUMBER
 IO_OFFLOAD_ELIG_BYTES_TOTAL               NUMBER
 IO_OFFLOAD_ELIG_BYTES_DELTA               NUMBER
 IO_INTERCONNECT_BYTES_TOTAL               NUMBER
 IO_INTERCONNECT_BYTES_DELTA               NUMBER
 PHYSICAL_READ_REQUESTS_TOTAL              NUMBER
 PHYSICAL_READ_REQUESTS_DELTA              NUMBER
 PHYSICAL_READ_BYTES_TOTAL                 NUMBER
 PHYSICAL_READ_BYTES_DELTA                 NUMBER
 PHYSICAL_WRITE_REQUESTS_TOTAL             NUMBER
 PHYSICAL_WRITE_REQUESTS_DELTA             NUMBER
 PHYSICAL_WRITE_BYTES_TOTAL                NUMBER
 PHYSICAL_WRITE_BYTES_DELTA                NUMBER
 OPTIMIZED_PHYSICAL_READS_TOTAL            NUMBER
 OPTIMIZED_PHYSICAL_READS_DELTA            NUMBER
 CELL_UNCOMPRESSED_BYTES_TOTAL             NUMBER
 CELL_UNCOMPRESSED_BYTES_DELTA             NUMBER
 IO_OFFLOAD_RETURN_BYTES_TOTAL             NUMBER
 IO_OFFLOAD_RETURN_BYTES_DELTA             NUMBER
 BIND_DATA                                 RAW(2000)
 FLAG                                      NUMBER

Pick up your wanted columns.

[Updated on: Mon, 16 March 2015 11:29]

Report message to a moderator

Previous Topic: Compression benefits and options
Next Topic: Union (all) and outline hints
Goto Forum:
  


Current Time: Mon Oct 15 19:11:44 CDT 2018