Monthly Archives: December 2010

Oracle: Basic Performance Tuning

This is a one page list of simple guidelines to tune SQL without much DBA knowledge.
This is all you need to know to tune some reports from 2 hours (or more) to 20 seconds (or less). I have compiled this list during a report tuning assignment
where I achieved that kind of runtime improvements repeatedly.
There are many more tips but they tend to be more difficult to understand and there are many good books already written about this. 

  • Understand the data. Look around table structures and data. Get a feel for the data model and how to navigate it.
  • If a view joins 3 extra tables to retrieve data that you do not need, don’t use the view!
  • When joining 2 views that themselves select from other views, check that the 2 views that you are using do not join the same tables!
  • Avoid multiple layers of view. For example, look for queries based on views that are themselves views. It may be desirable to encapsulate from a development point of view. But from a performance point of view, you loose control and understanding of exactly how much task loading your query will generate for the system.
  • Look for tables/views that add no value to the query. Try to remove table joins by getting the data from another table in the join.
  • WHERE EXISTS sub-queries can be better than join if can you reduce drastically the number of records in driver query. Otherwise, join is better.
  • WHERE EXISTS can be better than join when driving from parent records and want to make sure that at least on child exists. Optimizer knows to bail out as soon as finds one record. Join would get all records and then distinct them!
  • In reports, most of the time fewer queries will work faster. Each query results in a cursor that Reports has to open and fetch. See Reports Ref Manual for exceptions.
  • Avoid NOT in or NOT = on indexed columns. They prevent the optimizer from using indexes. Use where amount > 0 instead of where amount != 0.
  • Avoid writing where project_category is not null. nulls can prevent the optimizer from using an index.
  • Consider using IN or UNION in place of OR on indexed columns. ORs on indexed columns causes the optimizer to perform a full table scan.
  • Avoid calculations on indexed columns. Write WHERE approved_amt > 26000/3 instead of WHERE approved_amt/3 > 26000.
  • Avoid this: SUBSTR(haou.attribute1,1,LENGTH(‘:p_otc’)) = :p_otc). Consider this: WHERE  haou.attribute1 like :p_otc||’%’
  • Talk to your DBA. If you think that a column used in a WHERE clause should have an index, don’t assume that an index was defined. Check and talk to your DBA if you don’t find any.
  • Consider replacing outer joins on indexed columns with UNIONs. A nested loop outer takes more time than a nested loop unioned with another table access by index.
  • Consider adding small frequently accessed columns (not frequently updated) to an existing index. This will enable some queries to work only with the index, not the table.
  • Consider NOT EXISTS instead of NOT IN.
  • If a query is going to read most of the records in a table (more than 60%), use a full table scan.
  • Try to group multiple sub queries into one.

Beyond the Simple stuff …

  • If you want to actually understand what you are doing, here are a few things that you need to start playing with:
  • Get into EXPLAIN_PLAN. There are multiple way of doing this. The less user friendly is to simply issue this in SQL*Plus: explain plan set statement_id = ‘HDD1’ for ;
  • Look at the trace from Oracle Reports. It tells you how much time it spends on each query. With r25: C:ORANTBINR25RUN32.EXE module=p:oldbcmtrka1_hdd.rdf userid=opps/opps@new tracefile=p:trace3.txt trace_opts=(trace_all)
  • Use the SQL Trace by issuing an alter session set sql_trace=true; then look at it with TKPROF .trc .lis sort=(EXECPU).

If you remember nothing else …

  • Don’t apply these guidelines blindly, EXPERIMENT: compare one method to another. Do NOT expect that one trick will work all the time.
  • Educate yourself: read, read, read. It SAVES time!

Reference: http://www.iherve.com/oracle/tune100.htm


What is the difference between Linux and Unix?

Linux and Unix 

Unix is popular operating system, developed by AT&T in 1969 and it has been very important to the development of the Internet. It is a multi-processing, multi-user, family of operating systems that run on a variety of architechtures. UNIX allows more than one user to access a computer system at the same time.
A widely used Open Source Unix-like operating system kernel. Linux was first released by its inventor Linus Torvalds in 1991. Combining the Linux kernel with the GNU software forms the basis of the operating system family generally known as ‘Linux‘. There are distributions of GNU/Linux for almost every available type of computer hardware from desktop machines to IBM mainframes. The inner workings of GNU/Linux are open and available for anyone to examine and change as long as they make their changes available to the public, as set out in the terms of the GNU General Public License. Because of its robustness and availability, Linux has won popularity in the Open Source community as well as among commercial application developers.
Here is more input:

  • Unix requires a more powerful hardware configuration. It will work in large mainframe computers but will not work in an x86 based personal computer. Linux however, (which is built on the concept of Unix) has small hardware requirements and it will work on both a large mainframe computer and an x86 based personal computer.
  • Unix is an Operating System developed in olden days in which the kernel, the heart of the OS, interacts directly with the hardware. (note: this is the definition of what a kernel is). Because UNIX treats everything as a file, it provides greater security for users. An example of a UNIX distribution is posix. (note: actually POSIX is a set of standards for interoperability of applications between UNIX and UNIX-like systems). Linux uses a the UNIX architecture as its basis and provides more facilities and applications. Linux could be considered to be a GUI to the UNIX core. (note: this is plain wrong. GNU/ Linux was rewritten from scratch using UNIX as a guide. GNOME and KDE are GUIs for GNU/Linux). Examples of Linux distributions are Redhat, Fedora, Susee, Mandriva, and Ubuntu. Solaris OS also uses the UNIX kernal almost all UNIX commands will work on solaris in addition to 500 Solaris specific commands. (note: Solaris is also a rewrite of UNIX for x86, and does not use any original UNIX code). Both UNIX and LINUX are Open source. (note: UNIX is proprietary, Linux is open source)
  • Unix is the foundation for a number of operating systems, with Linux being the most popular one. Novell and Free BSD are 2 other commonly used Unix varients.(note: Again, the BSD family are based on another rewrite of UNIX for x86, UNIX is not their foundation in the sense implied here)
  • UNIX is an operating system created in the early days of computers. More recently, Linux was created as an open-source, freeware operating system. (note: Linux is free software, not freeware. Free software is open source that insists any developer reusing code releases their own work as free software. Freeware is proprietary software distributed at no cost [gratis]) It is “UNIX-LIKE”, meaning that it uses many UNIX constructs but also departs from traditional UNIX in many ways. Like UNIX, Linux is faster than many of the other commercially available operating systems. (note: This is a sweeping generalization and depends on the hardware used, and what servers and applications are running) It appears to also be far more robust than any of the Microsoft products. Linux is being used in many time critical applications because of it’s speed. It is also used in many applications that need to maintain uptime because Linux, like UNIX, can run for months at a time without rebooting. While the typical method of solving Microsoft problems is to “reboot”, that particular requirement does not seem to be appropriate in a Linux/Unix environment. While UNIX has created a windows-like work environment, Linux has improved greatly on that concept. Linux has become a real player in the consumer operating system market… and it’s free. While you may want to pay for a Linux distribution, the actual code is free and you are allowed to load it on as many machines as you want. You can get Linux for free if you wish to load it across the internet. 

Some other way to express difference 


Unix :

A Operating System developed in olden days in which kernel interacts directly with the hardware.kernal is consider to be heart of this os.
In this Operating system everything is considered as a file,provides greater security.
Ex: posix

Linux :

Linux is an Operating system which use unix as its base and gives further more facilities and applications.
Merely speeking GUI is made in linux having unix as its core.
Ex: Redhat, Fedora, Susee, Mandrake . . .
Solaris OS also using the same unix kernal all the unix commands will work on solaris and have 500 more solaris specific commands. Differnent organizations used the UNIX kernal and added their own essence to form their own version of linux.
Both UNIX and LINUX are Open source



What Is GPRS and How GPRS Works? (GPS vs GPRS)

This article will discuss the technology behind GPRS to help give you a better understanding on how GPRS works.

GPS vs GPRS

GPS and GPRS are far different when it comes to functionality even though there’s only one letter that differ. As a technology, GPS stands for Global Positioning System which can track different sites on Earth as it receives data from satellites, while GPRS connects with the cell sites for signals to provide service for cellular phones.

So what does GPRS stands for?

What is GPRS?

GPRS stands for General Packet Radio Service. It is a non-voice wireless Internet technology that is very popular due to the fact that it can support both phone calls and Internet data transmission. Some GSM, or Global System for Mobile Communications, mobile phones can even handle both phone communication and Internet access at the same time. It transmits data packets on GSM systems where cell phone channels are shared.

How Does GPRS Work?

In GSM cell phone systems, there will always be idle radio capacity. This is the capacity of a network provider that is not being used and it stays unused until other cell phone users decide to make phone calls. GPRS uses this idle radio capacity to establish a data network to be used for data transmission. If a network provider’s idle radio capacity decreases, which means a lot of phone calls are being serviced, data transmission and speed decreases as well. Cell phone calls have a higher priority than Internet data transmission in cell phone network providers.

Packets

Due to its packet-based nature, which means it transmits data as a series of data packets routed through multiple paths across a GSM network, GPRS does not require a network to have continuous data transmission. This data transmission can share network radio channels easily and efficiently. These network radio channels have 8 time slots each and their maximum data transmission is 13.4 kbps. Every time slot that is not being used can be used by GPRS to transmit data packets. In a best case scenario, user can experience a GPRS data rate of approximately 170 kbps per network radio channel. In this scenario, only data transmissions are using the network channels without voice calls hogging the network.

Radio Channels

A GSM radio transceiver uses time division multiplexing to make 8 time slots possible for each radio channel. These channels are used by cell phone users for voice calls and data transmission in maximum effect if conditions are right like if there is no interference from nearby GSM cell towers, there are enough radio transceivers and there is a strong radio infrastructure. GPRS uses idle capacity in these radio channel time slots to accommodate data transmission.

How Does GPRS Work to Optimize Bandwidth?

When multiple channels are being used by voiced calls, GPRS combines these channels to make use of the unused capacity. This joining of multiple voice channels gives users higher bandwidth for data connections. The number of channels and the amount of capacity of a cell phone network remains the same but the bandwidth can now be allowed to reach its full potential by servicing both voice calls and data transmission all at the same time using the same channels.

GPRS technology is an elegant solution for network providers to offer additional services with minimal effort. Cell phone network providers only need to set up a few new infrastructure nodes plus some software upgrades while still using their existing GSM network infrastructure to add an overlay GPRS network to their cell phone service.

Reference: http://www.brighthub.com/electronics/gps/articles/73072.aspx