AnyDB parameter so that all cpu work in parallel

Hi
I have a java program which runs in multithread and interact with DB(Oracle).
Th DB Server side only a few of the CPUs are in action rest are all resting. Is there any parameter in the DB (Oracle) which shall be set so that all the CPUs share the load in parallel?

The below parameters I see as a result of "show parameters", will investigate what are these for.
parallel_threads_per_cpu                    integer     2
parallel_xxx

If any one of you have an idea on this kindly do share.
Chandan PratihastAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I would start with the online documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/vldbg/using-parallel.html#GUID-3E2AE088-2505-465E-A8B2-AC38813EA355

I will also say that it isn't automatic that parallel means faster.  There are times where a non-parallel query will actually run faster.
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
"Is there any [single] parameter in the DB (Oracle) which shall be set so that all the CPUs share the load in parallel?"
No.

And you certainly don't want all Oracle SQL statements to be executed in parallel!  Some Oracle SQL statements can perform faster with parallel execution, but this depends greatly on your data volumes and on your server and storage system hardware.
0
Chandan PratihastAuthor Commented:
Understood and thank you.
For the point that "it depends on hardware parameter", our DB server has 6 cpu and 24 logical cpu, next the disk is 6. Would you share which hardware parameter I should check?

Otherwise I understand it is not much in my control.

Thank you in advance.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

slightwv (䄆 Netminder) Commented:
How the database runs "parallel" is 100% under DBA control.  Well, there may be some policies that limit that ability but over all, it is the DBAs job.  In a good organization the DBA works directly with developers and system administrators to come up with the optimal solution.  Some shops, not so much.

Depending on the shop you are in, the DBA might not have imput into how the underlying disk subsystem are configured.

Personally, I want EVERYTHING I ever run on  Solid State drives.  The reality is, that rarely happens.
1
Chandan PratihastAuthor Commented:
Thank you. SSD is what we all wish to be there, right! And we also do not have it. Anyway I will talk to my DBA. Thank you.
0
Mark GeerlingsDatabase AdministratorCommented:
If you have six disk drives, it depends on how those are configured and on your Oracle tablespaces.  First, are those six independent disks, or they in one, two or three RAID groups?  And if RAID groups, which RAID level?

If you have at least different RAID groups (or if you actually have six independent disks) then you may be able to take advantage of Oracle parallelism for some operations *IF* your tablespaces have datafiles in more than one disk group.  Is that true?  That is, do your tablespaces have data files in multiple disk groups?

This discussion of parallelism though may not be the area where you get the most performance improvement from your system.  In most Oracle systems, adjusting the SQL is the way to get the most performance improvement.  Sometimes you can add indexes to improve performance.  Just remember that these may help dramatically for some queries, but they will make all inserts, updates and deletes at least a bit slower.
1
Chandan PratihastAuthor Commented:
The first two paragraph was something I was not aware. Thank you for sharing it. Regarding tablespaces, in one environment we have data and indexes on different tablespaces and in another environment it is in same tablespaces.

About indexes we have tuned most of the queries. Using profiling agents like Glowroot.
0
Chandan PratihastAuthor Commented:
Hi Mark

One more thing what could be the reason that some cpu in my database server is having wait and that wait is around 30%?

If you can enlighten about that, may be that can also help.

Thank you in advance.
0
Chandan PratihastAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.