Home » RDBMS Server » Performance Tuning » block size (oracle 10.2.0.5 solaris 64 bit)
block size [message #556289] Fri, 01 June 2012 08:23 Go to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi Gurus,

I don't have any dba privileges, can you share a scripts which can tell how many block my query is fetching with or without indexs.
How do i also get buffer hit, how can i get i/o without sql trace as i don't have access to dump_dest

I have a below query

SELECT DISTINCT ser_id AS STA_ser_id, rct_name AS STA_name
FROM sd_servicecalls, rep_codes, rep_codes_text
WHERE ser_sta_oid = rcd_oid
AND rcd_oid = rct_rcd_oid
AND rct_name IN ('New', 'Awaiting Approval', 'Approved', 'In Progress', 'Awaiting Supplier', 'Awaiting RFC', 'Awaiting Release', 'Pending Release', 'On Hold', 'Resolved', 'Implemented', 'Closed');

Does large hash value in explain plan mean more resource needed and more time to execute the query,

How can i use ADDM to suggest better solution for the above sql.
Re: block size [message #556291 is a reply to message #556289] Fri, 01 June 2012 08:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set autotrace on

("on" or "traceonly" if you don't want the result)
And execute the query.

Regards
Michel


[Updated on: Fri, 01 June 2012 08:37]

Report message to a moderator

Re: block size [message #556294 is a reply to message #556291] Fri, 01 June 2012 08:46 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Hi Michel,

How this will give me buffer hit,i/o , how many data block it is fetching with or without indexes. once i set the autotrace on and run the query. how can i see the finding what i am looking for.

Can you please answer me

Regards

Rajesh
Re: block size [message #556295 is a reply to message #556294] Fri, 01 June 2012 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
just do it as below
bcm@bcm-laptop:~$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jun 1 06:57:47 2012

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: user1/user1

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

06:57:53 SQL> set autotrace traceonly explain statistics
06:58:27 SQL> select sysdate from dual;


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
	523  bytes received via SQL*Net from client
	  2  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	  1  rows processed

06:58:37 SQL> 
Re: block size [message #556297 is a reply to message #556295] Fri, 01 June 2012 09:05 Go to previous messageGo to next message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
Still , it doesn't answer my question, I don't want number of rows i want to see how many blocks my query fetch, i want to see wether it is cousing i/o , wether my query hit buffer cache i want to know do i have enough db_cache_size. you above expalin plan is very simple and i know all those things.

Can any one plese help me with my question , i am really in very bad shape.

Regards

Rajesh
Re: block size [message #556298 is a reply to message #556297] Fri, 01 June 2012 09:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

since you refuse to do as directed, no additional assistance will be wasted on this.

http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Re: block size [message #556304 is a reply to message #556297] Fri, 01 June 2012 09:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
OK, I'll try, Guddu.
You have been told what to do: set autotrace on, and run your query. The output shows you exactly how many blocks were read from disc (physical reads) and from cache (db block gets and consistent gets). So you can calculate the buffer cache hit ratio. But do not believe that the ratio will tell you anything useful, such as whether the buffer cache is appriopriately sized. A hit ratio of 100% does not mean that the query and the database are well tuned.

And by the way, you might want to say "thank you" to the people who have advised you so far.
Re: block size [message #556307 is a reply to message #556304] Fri, 01 June 2012 09:56 Go to previous message
guddu_12
Messages: 227
Registered: April 2012
Location: UK
Senior Member
I am really very sorry if i sounded very harsh, i never meant like that. I am also like to say thanks for all the suuport and guidance provided quicky.
I just wanted to know some calculation to be done before writing a query to get exact block size my query fetch, this will help me to understand better usability of index.

I have a greate regards for you guys helping million of people.
Once again i appologies if my saying felt bad.

Regards
Previous Topic: Why delete a where rowid=:1 go to index?
Next Topic: Optimize my Query
Goto Forum:
  


Current Time: Thu Mar 28 09:13:23 CDT 2024