block size [message #556289] |
Fri, 01 June 2012 08:23 |
|
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 #556304 is a reply to message #556297] |
Fri, 01 June 2012 09:45 |
John Watson
Messages: 8938 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 |
|
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
|
|
|