sar is showing 50% idle, but SQL is very slow.

[gbag]

Hi,
I am executing a SQL on a system that has 2 CPUs and sufficient memory. It runs very slow.
The same SQL runs very fast in a very similar another system. I checked the environment, onconfig differences. There is no difference.
When I looked the sar output, it shows 50% idle all through the SQL's execution. I strongly feel that one of the CPU is not being used. I want to use both CPUs for this.
Is there anyway of getting this. I have set the NUMCPUs as 1 as it should be equal to [actual-1].
Please help me in resolving this issue.
Any suggestions is highly appreciated.
Thanks in advance
gbag

 

[mjldba]

Hi gbag,

Do you have MULTIPROCESSOR = 1 in yout onconfig file? I think this is necessary to use the information supplied by
NUMCPUVPS.

Is your ONCONFIG env variable pointing to the correct onconfig file?

 

[gbag]

Thanks very much for the reply.
Yes, MULTIPROCESSOR is set to 1 and the ONCONFIG variable is set to correct ONCONFIG file. Any other clues??
Thanks

 

[mjldba]

Well then, it looks like I ran out of ideas rather quickly.

I suspect you're using an AIX OS so you might try running "topas" as root & verify that both processors are being utilized.

For further info on topas check the man pages or start topas and press "h".

 

[gbag]

Thanks for the replys.
No,this is not AIX, but this is SOLARIS. Any ideas.
Tahnks

 

[vpshriyan]

gbag:

Is the statistics for the tables involved in your query is latest? If not, execute:
UPDATE STATISTICS LOW FOR TABLE table_name ;

See how the optimizer plans its execution path by:
SET EXPLAIN ON;
SELECT ...

Study the sqexplain.out file, and it may provide some clue as what's going wrong. It is sometime very common that the joining columns are missed for primary or foreign key implementions, which are in turn, enforced using unique and duplicate indexes respectively.

You may post a sample of query that you are running and the explain output here, so that problem can be addressed in a different and better manner.

Regards,
Shriyan

 

[gbag]

Thanks Shriyan for the reply. The same application is performing differently in two systems. On one system, it runs fast and on another it is very slow. I don't have the SQL of the application.
My question is: when I run the sar command, it shows 50% idle and another 50% used by user. Why can't my process use this 50% idle CPU time. How can I make this to happen.
My configuration says, MULTIPROCESSOR=1, NUMCPUS=1 (Actual # of processors=2). The statistics are current.
Thanks for your help.

 

[mberni]

Hi,
concerning parallel queries you might want to have a look here:

http://docs.rinet.ru/InforSmes/ch19/ch19.htm#Heading3

Not every query can be executed parallel.

Check if the selected tables are fragmented between different dbspaces, because this will raise the chance to make use of parallel queries.

mb

 

[gbag]

Thanks MB for your reply and suggestion. It is not the parallel query issue. It is the issue with one of the processors not being used. If you look at the sar output, it says that 50% of the CPU is idle. I don't want the CPU to be idle. How can I get this done.

SCO_SV gbsat4 3.2v5.0.6 Xeon 06/27/2004

14:08:24 %usr %sys %wio %idle (-u)
14:04:22 51 0 0 49
14:06:24 50 0 0 50
14:08:26 50 0 0 49

Thanks

 

[mberni]

hmm, i am not used to SCO ... but are you using a smp-kernel so the OS will make use of your second processor?