OraFAQ Forum: Performance Tuning » Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT

Home » RDBMS Server » Performance Tuning » Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME (11.2.0.4 on Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME [message #642189]

Fri, 04 September 2015 11:34

orausern
Messages: 826
Registered: December 2005

Senior Member

Hello Experts,

I have a sql that takes quite some time. I am working on it but in the process notice something strange. The GV$SQL shows the CPU_TIME for this sql to be much lesser than the ELAPSED_TIME. The difference is just so huge ...for example

--3 such rows of data from gv$sql , with only a few columns (for formatting reason) are:

USER_IO_WAIT_TIME	ROWS_PROCESSED	CPU_TIME	ELAPSED_TIME
663,090,981	        112	        3,456,215	668,467,709
181,480,749	        2,267	        10,088,631	188,229,345
177,175,255	        78	        564,035	        178,006,564

I have attached a screen shot of the data from the GV$SQL. Note that the queries for which this is happening involves 2 tables. These two are the most active tables having huge amount of data (one table has 20 millon rows and 14 indexes and another one has 69 millon rows and 21 indexes). I guess this may have some connection to the issue so I am mentioning this point.

My understanding is that ELAPSED_TIME should be very close to the CPU_TIME. Also one other thing seen here is that huge amount of time is shown under: USER_IO_WAIT_TIME.
I don't know that this means.

I will be thankful for any suggestions/help in this regard.

Thanks,
OrauserN

Attachment: gv_sql_screenshot.jpg
(Size: 432.94KB, Downloaded 2174 times)

Report message to a moderator

Re: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME [message #642191 is a reply to message #642189]

Fri, 04 September 2015 11:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Post the problem SQL plus requested details listed below.

http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof

Report message to a moderator

Re: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME [message #642192 is a reply to message #642189]

Fri, 04 September 2015 11:45

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

I would start by using the sql_id and child_number for the statements to get their execution plan and stats,

select * from table(dbms_xplan.display_cursor('&sql_id',&child,format=>'allstats last'));

Report message to a moderator

Re: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME [message #642193 is a reply to message #642192]

Fri, 04 September 2015 11:52

orausern
Messages: 826
Registered: December 2005

Senior Member

Thanks John and BlackSwan.

Well, my question is not so much on tuning the sql as about a conceptual clarity - that why could there be a huge difference between CPU_TIME and ELAPSED_TIME and what
does the high time on USER_IO_WAIT_TIME indicate? Now to check that, details of a specific sql id and its plan is not required I thought. Is it right?

Report message to a moderator