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)
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 Go to next message
orausern
Messages: 817
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

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 Go to previous messageGo to next message
BlackSwan
Messages: 25856
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
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 Go to previous messageGo to next message
John Watson
Messages: 7261
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'));
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 Go to previous messageGo to next message
orausern
Messages: 817
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?
Re: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME [message #642194 is a reply to message #642193] Fri, 04 September 2015 11:54 Go to previous messageGo to next message
John Watson
Messages: 7261
Registered: January 2010
Location: Global Village
Senior Member
No, it is not right. You need to determine which SQLs are waiting, and why.
Re: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME [message #642195 is a reply to message #642194] Fri, 04 September 2015 12:00 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
ELAPSED_TIME - CPU_TIME = WAIT_TIME
ELAPSED_TIME = CPU_TIME + WAIT_TIME
Re: Huge difference between CPU Time and ELAPSED TIME and GV$SQL and high times on USER_IO_WAIT_TIME [message #642198 is a reply to message #642195] Fri, 04 September 2015 13:07 Go to previous message
orausern
Messages: 817
Registered: December 2005
Senior Member
Thank you both again! I will get these details and post it here tomorrow (the output of John's command).

Previous Topic: Oracle noforce view creates force view
Next Topic: Query in active session for more than 2 hours
Goto Forum:
  


Current Time: Tue Feb 20 23:58:32 CST 2018

Total time taken to generate the page: 0.02248 seconds