OraFAQ Forum: Performance Tuning » how to show the right I\O of query

Home » RDBMS Server » Performance Tuning » how to show the right I\O of query

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

how to show the right I\O of query [message #512663]

Tue, 21 June 2011 06:45

assa9009
Messages: 7
Registered: June 2011
Location: australia

Junior Member

hey guys

i use oracle 11g r2

i have created a table for objects

create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);

and inserted many values (examples below )

INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/

and( position) indexed as Rtree spatial index

now when i run spatial query such as

SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;

i do not know how many I\O accrued ?

i tried set autotrace on

but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree

can you help me to know how many I\O by a query , also the CPU time ?

please

thanks

Report message to a moderator

Re: how to show the right I\O of query [message #513510 is a reply to message #512663]

Mon, 27 June 2011 22:43

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Try running a SQL Trace and view the output with TKPROF. It might produce better results. Otherwise you might look at V$SESS_IO and/or V$SESSTAT

Ross Leishman

Report message to a moderator

Re: how to show the right I\O of query [message #513511 is a reply to message #513510]

Mon, 27 June 2011 22:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

SQL> set autotrace on statistics explain
SQL> select sysdate from dual;

SYSDATE
-------------------
2011-06-27 20:47:56


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation	 | Name | Rows	| Cost (%CPU)| Time	|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |	|     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL	 |	|     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
	  1  recursive calls
	  0  db block gets
	  0  consistent gets
	  0  physical reads
	  0  redo size
	530  bytes sent via SQL*Net to client
	524  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

SQL>

[Updated on: Mon, 27 June 2011 22:49]

Report message to a moderator

Previous Topic:	Setting flash cache initialisation parameters
Next Topic:	Local Index vs Global Index on partitioned table

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 21 17:27:15 CDT 2023