equal
deleted
inserted
replaced
314 alter user BOB quota 100M on USERS; |
314 alter user BOB quota 100M on USERS; |
315 grant CREATE SESSION, ALTER SESSION to BOB; |
315 grant CREATE SESSION, ALTER SESSION to BOB; |
316 grant CREATE PROCEDURE, CREATE TRIGGER to BOB; |
316 grant CREATE PROCEDURE, CREATE TRIGGER to BOB; |
317 grant CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE SYNONYM to BOB; |
317 grant CREATE TABLE, CREATE SEQUENCE, CREATE VIEW, CREATE SYNONYM to BOB; |
318 |
318 |
319 Profiling. |
319 Profiling |
320 ========== |
320 ========= |
321 |
321 |
322 Timing info about last queries:: |
322 Last queries' execution stats are available through ``v$sql`` view:: |
|
323 |
|
324 grant select on v_$sql to BOB; |
|
325 |
|
326 select * from v$sql; |
323 |
327 |
324 select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql |
328 select LAST_LOAD_TIME, ELAPSED_TIME, MODULE, SQL_TEXT elasped from v$sql |
325 order by LAST_LOAD_TIME desc |
329 order by LAST_LOAD_TIME desc |
326 |
330 |
327 Improved version of above code:: |
331 Improved version of above code:: |
338 select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME, MODULE, SQL_TEXT from SYS."V_\$SQL" |
342 select LAST_LOAD_TIME, to_char(ELAPSED_TIME/1000, '999,999,999.000') || ' ms' as TIME, MODULE, SQL_TEXT from SYS."V_\$SQL" |
339 where SQL_TEXT like '%BATCH_BRANCHES%' |
343 where SQL_TEXT like '%BATCH_BRANCHES%' |
340 order by LAST_LOAD_TIME desc |
344 order by LAST_LOAD_TIME desc |
341 ) where ROWNUM <= 5; |
345 ) where ROWNUM <= 5; |
342 |
346 |
343 In SQL/Plus:: |
347 In SQL/Plus query execution time (up to ):: |
344 |
348 |
345 SET TIMING ON; |
349 SET TIMING ON; |
346 -- do stuff |
350 -- do stuff |
347 SET TIMING OFF; |
351 SET TIMING OFF; |
348 |
352 |
366 Another option is:: |
370 Another option is:: |
367 |
371 |
368 delete plan_table; |
372 delete plan_table; |
369 explain plan for ... SQL statement ...; |
373 explain plan for ... SQL statement ...; |
370 select time from plan_table where id = 0; |
374 select time from plan_table where id = 0; |
|
375 |
|
376 .. note:: |
|
377 From docs: |
|
378 |
|
379 PLAN_TABLE is automatically created as a global temporary table to hold the output of an EXPLAIN |
|
380 PLAN statement for all users. PLAN_TABLE is the default sample output table into which the |
|
381 EXPLAIN PLAN statement inserts rows describing execution plans. |
371 |
382 |
372 See: |
383 See: |
373 |
384 |
374 http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm |
385 http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_2113.htm |
375 $SQL lists statistics on shared SQL area without the GROUP BY clause. |
386 $SQL lists statistics on shared SQL area without the GROUP BY clause. |
450 |
461 |
451 Show error details:: |
462 Show error details:: |
452 |
463 |
453 show errors; |
464 show errors; |
454 |
465 |
|
466 Modify output format:: |
|
467 |
|
468 set pagesize 40; |
|
469 set linesize 400; |