OraFAQ Forum: Performance Tuning » multiple size of db

Home » RDBMS Server » Performance Tuning » multiple size of db_block

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

multiple size of db_block [message #270203]

Wed, 26 September 2007 02:15

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?

Report message to a moderator

Re: multiple size of db_block [message #270215 is a reply to message #270203]

Wed, 26 September 2007 03:12

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: multiple size of db_block [message #270223 is a reply to message #270203]

Wed, 26 September 2007 03:34

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.

Report message to a moderator

Re: multiple size of db_block [message #270226 is a reply to message #270223]

Wed, 26 September 2007 03:39

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: multiple size of db_block [message #270228 is a reply to message #270203]

Wed, 26 September 2007 03:46

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.

Report message to a moderator

Re: multiple size of db_block [message #270241 is a reply to message #270228]

Wed, 26 September 2007 04:39

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So FTS may be the reason.

Regards
Michel

Report message to a moderator

Re: multiple size of db_block [message #270439 is a reply to message #270203]

Wed, 26 September 2007 21:49

tanmoy1048
Messages: 133
Registered: June 2007

Senior Member

So, I have to tune my query.
No way?????

Report message to a moderator

Re: multiple size of db_block [message #270452 is a reply to message #270439]

Wed, 26 September 2007 22:43

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

Report message to a moderator

Re: multiple size of db_block [message #270494 is a reply to message #270203]

Thu, 27 September 2007 03:40

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;

Report message to a moderator

Re: multiple size of db_block [message #270501 is a reply to message #270494]

Thu, 27 September 2007 03:54

Michel Cadot
Messages: 68665
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

Report message to a moderator

Re: multiple size of db_block [message #270506 is a reply to message #270203]

Thu, 27 September 2007 04:00

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

Report message to a moderator