Tabangi ko plssss....PL/SQL Tuning Tips

[legal_cd]

---

hahahahaha.. no comment nalang.. kapiso nuon.... hehehehhehe

@zengatsu:

anyways i have a few tips on sql tuning..

- on your where clause, make sure that the ordering of ur condition statements will have an exact sequencing on ur indexes or if not pinaka duol nalang.

ex.
select *
from table1
where table1.period = parameter_period
and table1.category = parameter_category
and table1.type = parameter_type

on ur indexes:
at least naka INDEX nimo bisan period and category lang.. that would do.

- try to check on ur dbase platform na gigamit kung flexible ba cya for optimizer hint like /*+cost*/ /*+index*/... this optimizer hint is available in oracle.

- now if ur having problems on ur sql statements para sa imong reports, especially monthly and yearly reports. my advise is.. sa imong primary query na mao mo fetch sa data from ur database make it as simple as possible meaning no aggregate functions as much as possible coz using such is an additional load sa imong server.. better na get the data as it is then sa client na nimo i manipulate.

ex. Client / Server processing.

conventional way of query.

select trantable.tran_cust_id
customer.last_name,
customer.first_name
......
from trantable,
customer,
where trantable.tran_cust_id = customer.cust_id
and .....

(u could have this on ur primary query to fetch the data needed on ur report, there nothing wrong with this, the query is perfectly ok. but im stressing that the activity should be both ways.. more load on the client less load on the server in order to facilitate other user's request on the server.)

Recommend Query:

select trantable.tran_cust_id
......
from trantable
where trantable.column = aramter.column
and .......

in this query ur only fetching from one table from ur database, so it's much much faster that way, since you'll be needing the "CUSTOMER's NAME" data, you will now place a separate query within ur report
that will access the "LAST NAME", "FIRST NAME".. etc of the customer... so somwhere within ur report place a simple query like this

select customer.last_name,
customer.first_name,
customer.address,
......
into <ur variabales / data holders>
from customer
where cust_id = tran_cust_id.

note: value of the tran_cust_id is taken from the cursor that was fetch a while ago in the database.


HOPE THIS HELPS! c",) Happy Holidays!

[zengatsu]

@legal_cd:
thanks bay..yep gamit ko optimizer hints sa akong mga queries bay..oracle man sad among gamit..ok na cya actually..salamat kau bay..puno lang ni nako imong tips sa akong notes..

[goodhands]

Guys,

Just want to inform you that tuning in general doesn't focus only the way we code, either in Oracle, SQL Server, Client Side, rather it involves the business you are into. But to give you a very good reference try http://asktom.oracle.com or www.oracle.com/technology/sample_codes. just browse in (www.oracle.com).

Are you using Oracle Forms for you front end dev tool?



Goodhands

[BadDudes]

Guys,

Just want to inform you that tuning in general doesn't focus only the way we code, either in Oracle, SQL Server, Client Side, rather it involves the business you are into. But to give you a very good reference try http://asktom.oracle.com or www.oracle.com/technology/sample_codes. just browse in (www.oracle.com).

Are you using Oracle Forms for you front end dev tool?



Goodhands

yup ...until now were still mantaining our decade old forms 4.5 and forms 5.0 ahahahah