Home » RDBMS Server » Performance Tuning » multiple size of db_block
multiple size of db_block [message #270203] Wed, 26 September 2007 02:15 Go to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
using oracle 10g on solaris.
I have a query with lots of join and some table scan with lob object.
when i execute that with 8K block size..then after executing 3 times ,I got physical read 0.

but I moved them in a 16K block size.and physical read is not reduced. its always 23000 to 25000.

so ..what can I do to reduce the physical read?
Re: multiple size of db_block [message #270215 is a reply to message #270203] Wed, 26 September 2007 03:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't read it. Cool

What is 23000 pio? nothing if you only get them once.
It does not even worth to spend time changing or searching anything.

Regards
Michel
Re: multiple size of db_block [message #270223 is a reply to message #270203] Wed, 26 September 2007 03:34 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
thanx michel..but i didnt understand ur answer.
i executed it multiple times but its giving same result.

I get 23000 using autotrace on.
Re: multiple size of db_block [message #270226 is a reply to message #270223] Wed, 26 September 2007 03:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, I didn't understand what you said.
It seems your 16k buffer is too small or you now use a FTS or... many reasons for this.
But as I said return back to your initial case and don't care about the 2X000 pio.


Regards
Michel
Re: multiple size of db_block [message #270228 is a reply to message #270203] Wed, 26 September 2007 03:46 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
Mystry is here.
I gave 512M to db_16k_cache_size .
and then i gave 1G to db_16k_cache_size .but...the result is same.
Re: multiple size of db_block [message #270241 is a reply to message #270228] Wed, 26 September 2007 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So FTS may be the reason.

Regards
Michel
Re: multiple size of db_block [message #270439 is a reply to message #270203] Wed, 26 September 2007 21:49 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
So, I have to tune my query.
No way?????
Re: multiple size of db_block [message #270452 is a reply to message #270439] Wed, 26 September 2007 22:43 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello tanmoy1048,

tanmoy1048 wrote
I have a query with lots of join and some table scan with lob object.


Can you post your query and its explain/execution plan or tkprof here?

To tune a query I understand that the best way is to figure out the query and its execution plan.

Regards,


mson77
Re: multiple size of db_block [message #270494 is a reply to message #270203] Thu, 27 September 2007 03:40 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
this s the query,
SELECT DISTINCT lb.form_id AS formId, DECODE(lb.type_medical, 0, 'logbook.hyphen', 'logbook.Y') AS health,
DECODE(lb.type_notification, 0, 'logbook.hyphen', 'logbook.Y') AS notes,
DECODE(lb.type_review, 0, 'logbook.hyphen', 'logbook.Y') AS followup,
DECODE(lb.type_behavior, 0, 'logbook.hyphen', 'logbook.Y') AS behavior,
DECODE(lb.type_contact, 0, 'logbook.hyphen', 'logbook.Y') AS contact,
DECODE(lb.type_approval, 0, 'logbook.hyphen', 'logbook.Y') AS general, pgm.name AS program, site.name AS site,
DECODE(lb.individual_id, NULL, '', client.last_name || ', ' || client.first_name) AS client,
rep.last_name || ', ' || rep.first_name || DECODE(st.title, NULL,'', ' / ' || st.title) AS enteredBy, lb.summary AS s
ummary,
lb.created AS edate, lb.notif_level AS notifLevel,
DECODE(stat.read_date, NULL,'logbook.unread', 'logbook.read') || DECODE(lb.deleted, 1, '.deleted', null) AS status,
lb.created AS sdate, lb.tz AS tz
FROM lb_log_book lb
JOIN program pgm ON (lb.pgm_id = pgm.id AND lb.prov_id=100)
JOIN site ON (pgm.site_id = site.id)
LEFT JOIN client ON (lb.individual_id=client.id)
JOIN login rep ON (lb.submitter_id=rep.id)
LEFT JOIN title st ON (rep.title_id=st.id)
LEFT JOIN (SELECT lb_id, read_date FROM lb_status WHERE login_id=755) stat ON (stat.lb_id=lb.form_id)
JOIN pgm_role pr on (( (pr.role in (51, 80) AND lb.deleted =0) OR (pr.role=112)) AND pr.login_id = 755)
JOIN form_program fp ON (lb.form_id = fp.form_id) WHERE (((fp.client_id = pr.client_id OR fp.client_id IS NULL) AND f
p.pgm_id= pr.pgm_id)
AND (((lb.created_int >= 20070701) AND (lb.created_int <= 20070801) AND (lb.test != 1))))
ORDER BY formId DESC;
Re: multiple size of db_block [message #270501 is a reply to message #270494] Thu, 27 September 2007 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: multiple size of db_block [message #270506 is a reply to message #270203] Thu, 27 September 2007 04:00 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
and the explain plan is:(sorry for its size)
----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 303 | 580 (2)| 00:00:07 |
| 1 | SORT UNIQUE | | 1 | 303 | 579 (2)| 00:00:07 |
|* 2 | HASH JOIN | | 1 | 303 | 578 (2)| 00:00:07 |
|* 3 | HASH JOIN | | 178 | 51620 | 558 (2)| 00:00:07 |
|* 4 | INDEX FAST FULL SCAN | SYS_C0022611 | 2718 | 19026 | 20 (5)| 00:00:01 |
| 5 | MERGE JOIN CARTESIAN | | 219 | 61977 | 538 (2)| 00:00:07 |
|* 6 | HASH JOIN | | 53 | 14363 | 379 (2)| 00:00:05 |
| 7 | NESTED LOOPS | | 48 | 12288 | 342 (2)| 00:00:05 |
|* 8 | HASH JOIN OUTER | | 43 | 9804 | 256 (2)| 00:00:04 |
| 9 | NESTED LOOPS | | 43 | 8772 | 237 (2)| 00:00:03 |
| 10 | NESTED LOOPS OUTER | | 43 | 7869 | 194 (3)| 00:00:03 |
|* 11 | HASH JOIN | | 43 | 7095 | 153 (3)| 00:00:02 |
|* 12 | HASH JOIN | | 43 | 6278 | 142 (3)| 00:00:02 |
| 13 | NESTED LOOPS OUTER | | 43 | 5289 | 133 (2)| 00:00:02 |
|* 14 | TABLE ACCESS BY INDEX ROWID | LB_LOG_BOOK | 43 | 3956 | 47 (5)| 00:00:01 |
| 15 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 16 | BITMAP AND | | | | | |
| 17 | BITMAP CONVERSION FROM ROWIDS| | | | | |
| 18 | SORT ORDER BY | | | | | |
|* 19 | INDEX RANGE SCAN | LB_CREATED_INT | 8488 | | 12 (0)| 00:00:01 |
| 20 | BITMAP CONVERSION FROM ROWIDS| | | | | |
|* 21 | INDEX RANGE SCAN | LB_PROV | 8488 | | 15 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | LB_STATUS | 1 | 31 | 2 (0)| 00:00:01 |
|* 23 | INDEX UNIQUE SCAN | LB_STATUS_LB_LOGIN | 1 | | 1 (0)| 00:00:01 |
| 24 | TABLE ACCESS FULL | PROGRAM | 2920 | 67160 | 8 (0)| 00:00:01 |
| 25 | VIEW | index$_join$_004 | 2011 | 38209 | 11 (10)| 00:00:01 |
|* 26 | HASH JOIN | | | | | |
| 27 | INDEX FAST FULL SCAN | SYS_C0022856 | 2011 | 38209 | 4 (0)| 00:00:01 |
| 28 | INDEX FAST FULL SCAN | SYS_C0022857 | 2011 | 38209 | 6 (0)| 00:00:01 |
| 29 | TABLE ACCESS BY INDEX ROWID | CLIENT | 1 | 18 | 1 (0)| 00:00:01 |
|* 30 | INDEX UNIQUE SCAN | SYS_C0022677 | 1 | | 0 (0)| 00:00:01 |
| 31 | TABLE ACCESS BY INDEX ROWID | LOGIN | 1 | 21 | 1 (0)| 00:00:01 |
|* 32 | INDEX UNIQUE SCAN | SYS_C0022811 | 1 | | 0 (0)| 00:00:01 |
| 33 | VIEW | index$_join$_010 | 3499 | 83976 | 18 (0)| 00:00:01 |
|* 34 | HASH JOIN | | | | | |
| 35 | INDEX FAST FULL SCAN | SYS_C0022864 | 3499 | 83976 | 6 (0)| 00:00:01 |
| 36 | INDEX FAST FULL SCAN | SYS_C0022865 | 3499 | 83976 | 11 (0)| 00:00:01 |
|* 37 | INDEX RANGE SCAN | FRM_PGM_FORMID_CL_PGM | 1 | 28 | 2 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | PROGRAM_CLIENT | 26548 | 388K| 36 (0)| 00:00:01 |
| 39 | BUFFER SORT | | 4 | 48 | 501 (1)| 00:00:07 |
| 40 | TABLE ACCESS BY INDEX ROWID | CASELOAD_ACCESS | 4 | 48 | 3 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | CA_LOGINID | 4 | | 1 (0)| 00:00:01 |
| 42 | TABLE ACCESS FULL | CASELOAD_DETAILS | 14468 | 183K| 19 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
627 recursive calls
0 db block gets
184048 consistent gets
24978 physical reads
116 redo size
1773779 bytes sent via SQL*Net to client
8796 bytes received via SQL*Net from client
759 SQL*Net roundtrips to/from client
15 sorts (memory)
0 sorts (disk)
11356 rows processed
Re: multiple size of db_block [message #270508 is a reply to message #270494] Thu, 27 September 2007 04:01 Go to previous messageGo to next message
tanmoy1048
Messages: 133
Registered: June 2007
Senior Member
sorry michel....
but...was there any way to do that???
Re: multiple size of db_block [message #270511 is a reply to message #270508] Thu, 27 September 2007 04:04 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
To do what? To format? Yes, read the guide.

Regards
Michel
Previous Topic: slow perf with Bulk collect and FORALL
Next Topic: Help: Identifying Problem in the query
Goto Forum:
  


Current Time: Fri Dec 02 16:25:25 CST 2016

Total time taken to generate the page: 0.38307 seconds